A database is an organized collection of related data stored electronically so it can be easily accessed, managed, and updated. Databases are designed to handle large amounts of information efficiently and are widely used in applications such as banking systems, social media platforms, e-commerce websites, hospitals, and universities.
Key characteristics of a database
- Structured data storage (tables, rows, columns)
- Efficient data retrieval using queries
- Data consistency and integrity
- Multi-user access
- Security and backup support
Database Design
What is Normalization?
Normalization is a systematic process of organizing data in a database to reduce redundancy (duplicate data) and eliminate anomalies (insertion, update, and deletion problems).
The main goals of normalization are:
- To make the database easier to maintain and update
- To store data logically
- To avoid unnecessary duplication
- To ensure data integrity and consistency
Normalization is carried out in steps called Normal Forms (NF).
1. First Normal Form (1NF)
A table is in 1NF if:
- Each record can be uniquely identified
- Each field contains atomic (indivisible) values
- There are no repeating groups or multi-valued attributes
2. Second Normal Form (2NF)
A table is in 2NF if:
- It is already in 1NF
- All non-key attributes are fully dependent on the entire primary key
This mainly applies to tables with composite primary keys.
Problem solved: Partial dependency
3. Third Normal Form (3NF)
A table is in 3NF if:
- It is already in 2NF
- There is no transitive dependency (non-key attributes should not depend on other non-key attributes)
Problem solved: Indirect/Transitive dependency
Transaction in DBMS
A transaction is a logical unit of work performed on a database that consists of one or more SQL operations (such as INSERT, UPDATE, DELETE, or SELECT). A transaction ensures that the database moves from one consistent state to another.
Example of a Transaction
Transferring money from Account A to Account B:
- Deduct amount from Account A
- Add amount to Account B
Both steps must succeed together; otherwise, the database must be restored to its original state.
Transaction Control Commands
BEGIN TRANSACTION– Starts a transactionCOMMIT– Saves all changes permanentlyROLLBACK– Undoes all changes since the last commitSAVEPOINT– Marks a point within a transaction for partial rollback
ACID Properties
ACID is a set of four properties that guarantee reliable and safe execution of database transactions.
1. Atomicity
Atomicity means “all or nothing.”
A transaction must be fully completed or fully undone.
If any operation in a transaction fails, all changes are rolled back.
Example:
If money is deducted from Account A but the system crashes before crediting Account B, the deduction is undone.
2. Consistency
Consistency ensures that a transaction brings the database from one valid state to another, following all rules such as:
- Primary key constraints
- Foreign key constraints
- Data type rules
No transaction can violate database integrity constraints.
3. Isolation
Isolation ensures that multiple transactions can run concurrently without interfering with each other.
Intermediate results of one transaction are not visible to others until it is committed.
Example:
Two users booking the last seat simultaneously — isolation prevents double booking.
4. Durability
Durability guarantees that once a transaction is committed, its changes are permanently saved, even if:
- Power failure occurs
- System crashes
Committed data is stored in non-volatile memory.
ACID Properties Summary Table
| Property | Meaning | Purpose |
|---|---|---|
| Atomicity | All or nothing | Prevents partial updates |
| Consistency | Valid state | Maintains integrity |
| Isolation | Independent execution | Avoids interference |
| Durability | Permanent changes | Ensures data safety |
BASE Theorem
The BASE theorem is a principle used in distributed database systems, especially NoSQL databases, to describe a model that focuses on availability and performance rather than strict consistency.
BASE stands for:
- Basically Available
- Soft state
- Eventual consistency
It is often considered an alternative to ACID in large-scale, distributed systems.
1. Basically Available
Basically Available means:
- The system guarantees availability
- Every request receives a response, even if it may not contain the most recent data
The system may return partial, stale, or approximate data instead of failing.
Example:
A shopping website still shows product information even if one server is temporarily down.
2. Soft State
Soft state means:
- The system’s state may change over time
- Changes can happen without direct user input
Data is not required to be instantly consistent at all times.
Example:
Cached data may expire or be updated automatically in the background.
3. Eventual Consistency
Eventual Consistency means:
- The system does not guarantee immediate consistency
- If no new updates occur, all replicas will eventually become consistent
Temporary inconsistencies are allowed, but data will synchronize over time.
Example:
A social media post appears immediately on one device but takes a few seconds to appear on another.
BASE vs ACID (Comparison)
| Feature | ACID | BASE |
|---|---|---|
| Consistency | Strong | Eventual |
| Availability | Limited | High |
| Scalability | Moderate | High |
| Fault tolerance | Low | High |
| Usage | RDBMS | NoSQL systems |
