1. Why Do We Need Normalization?
Normalization is the process of organizing data in a database to reduce redundancy and
improve data integrity. It involves dividing large tables into smaller, more manageable pieces
and defining relationships between them. The primary goals of normalization are:
Minimize Data Redundancy: By eliminating duplicate data, normalization reduces
storage requirements and ensures consistency.
Improve Data Integrity: Normalization helps maintain accurate and consistent data by
enforcing constraints and relationships.
Simplify Database Maintenance: Well-structured databases are easier to update, query,
and maintain.
Avoid Anomalies: Normalization prevents insertion, update, and deletion anomalies that
can occur in poorly designed databases.
Example: Consider a table Student with columns StudentID, Name, Course, and Instructor. If
a student enrolls in multiple courses, the same student's name and ID will be repeated for
each course, leading to redundancy. Normalization would split this into two tables: Student
(with StudentID and Name) and Enrollment (with StudentID, Course, and Instructor).
2. Primary Key, Foreign Key, and Composite Primary Key
Primary Key (PK): A column or set of columns that uniquely identifies each row in a
table. It cannot contain NULL values and must be unique.
Example: In a Student table, StudentID can be the primary key.
Foreign Key (FK): A column or set of columns in one table that refers to the primary key
in another table. It establishes a relationship between the two tables.
Example: In an Enrollment table, StudentID can be a foreign key referencing the
Student table.
Composite Primary Key: A primary key that consists of two or more columns to uniquely
identify a row.
Example: In an OrderDetails table, OrderID and ProductID together can form a
composite primary key.
3. Difference Between Primary Key and Unique Column
Aspect Primary Key Unique Column
Uniqueness Must be unique Must be unique
NULL Values Cannot contain NULL Can contain one
values NULL value
(depending on DBMS)
Purpose Uniquely identifies a Ensures no duplicate
row in a table values in a column
Number per Table Only one primary key Multiple unique
allowed columns allowed
Example: In a Student table, StudentID is the primary key, while Email can be a unique column
to ensure no two students have the same email.
4. Identifying Relationships Between Tables
One-to-One (1:1): Each row in Table A relates to exactly one row in Table B, and vice
versa.
Example: A User table and a UserProfile table, where each user has one profile.
One-to-Many (1:M): A row in Table A can relate to multiple rows in Table B, but a row in
Table B relates to only one row in Table A.
Example: A Department table and an Employee table, where one department can
have many employees.
Many-to-Many (M:M): Rows in Table A can relate to multiple rows in Table B, and vice
versa. This requires a junction table.
Example: A Student table and a Course table, where students can enroll in multiple
courses, and courses can have multiple students. A junction table Enrollment is used
to manage this relationship.
5. Impact of Relationship Type on Foreign Key Placement
One-to-One: The foreign key can be placed in either table.
Example: In User and UserProfile, the foreign key can be in either table.
One-to-Many: The foreign key is placed in the "many" side table.
Example: In Department and Employee, the foreign key (DepartmentID) is placed in
the Employee table.
Many-to-Many: A junction table is created, and foreign keys from both tables are placed
in the junction table.
Example: In Student and Course, the Enrollment table contains foreign keys
(StudentID and CourseID).
6. Normalization Forms
1NF (First Normal Form):
Eliminate duplicate columns.
Ensure each column contains atomic (indivisible) values.
Example: A table with a PhoneNumbers column containing multiple phone numbers
violates 1NF. Split it into separate rows.
2NF (Second Normal Form):
Meet 1NF requirements.
Remove partial dependencies (ensure all non-key attributes depend on the entire
primary key).
Example: In an OrderDetails table, if ProductName depends only on ProductID and
not on the entire composite key (OrderID + ProductID), it violates 2NF.
3NF (Third Normal Form):
Meet 2NF requirements.
Remove transitive dependencies (ensure non-key attributes depend only on the
primary key).
Example: In a Student table, if InstructorName depends on Course and Course
depends on StudentID, it violates 3NF.
BCNF (Boyce-Codd Normal Form):
A stronger version of 3NF.
Every determinant must be a candidate key.
Example: If a table has two candidate keys and a non-key attribute depends on one
of them, it violates BCNF.
7. ACID Properties in Database Design
ACID properties ensure reliable transaction processing in databases:
Atomicity: Ensures that a transaction is treated as a single unit. Either all operations in
the transaction are completed, or none are.
Example: In a bank transfer, both the debit and credit operations must succeed or fail
together.
Consistency: Ensures that a transaction brings the database from one valid state to
another, maintaining data integrity.
Example: A transaction that transfers funds must ensure the total balance remains
consistent.
Isolation: Ensures that concurrent transactions do not interfere with each other.
Example: Two users transferring money from the same account should not see
inconsistent balances.
Durability: Ensures that once a transaction is committed, it remains permanent, even in
the event of a system failure.
Example: After a successful transfer, the changes must persist even if the database
crashes.