Basic Concepts
What is the main goal of normalization in databases?
To eliminate data redundancy and ensure data integrity.
What is the main requirement for a table to be in First Normal Form (1NF)?
All attributes must contain only atomic (indivisible) values.
What is a functional dependency?
A relationship where one attribute uniquely determines another (e.g., A → B means B is functionally dependent on A)
Table: Employees(EmpID, Name, Department)
FDs:
EmpID → Name, Department
Answer: 1NF
📌 Explanation: The table is in 1NF (atomic values, unique rows). No partial or transitive dependencies since EmpID is the key.
What is normalization?
This process reduces data redundancy and improves data integrity by organizing data into multiple related tables.
True or False: Normalization increases data duplication.
A: False
📌 Explanation: Normalization reduces data duplication by organizing data into related tables.
What additional requirement must be met for a table to move from 1NF to Second Normal Form (2NF)?
It must eliminate partial dependencies (every non-key attribute must depend on the whole primary key).
In the dependency StudentID → StudentName, which is the determinant?
StudentID
Table: Orders(OrderID, ProductID, ProductName)
FDs:
OrderID, ProductID → ProductName
Answer: 1NF
📌 Explanation: Repeating groups may exist (multiple products per order), but the table only meets 1NF. Not in 2NF because ProductName depends only on ProductID.
What is denormalization?
This process combines tables to improve read performance at the cost of increased redundancy.
Who introduced the concept of normalization?
Edgar F. Codd
What type of dependency does Third Normal Form (3NF) eliminate?
Transitive dependency
What is a partial dependency?
When a non-prime attribute is dependent on part of a composite primary key, not the whole key.
Table: Students(StudentID, CourseID, Instructor)
FDs:
StudentID, CourseID → Instructor
CourseID → Instructor
Answer: 1NF
📌 Explanation: The table violates 2NF due to a partial dependency: Instructor depends only on CourseID, not the full composite key.
What is data redundancy?
Normalization helps avoid this problem, where the same piece of data is stored in multiple places
What does 1NF (First Normal Form) primarily eliminate?
Repeating groups and non-atomic values in a table.
In Boyce-Codd Normal Form (BCNF), what must be true for every functional dependency X → Y?
X must be a super key.
What is a transitive dependency?
This dependency exists when a non-prime attribute depends on another non-prime attribute.
Table: Books(BookID, Title, AuthorID, AuthorName)
FDs:
BookID → Title, AuthorID
AuthorID → AuthorName
Answer: 2NF
📌 Explanation: All non-key attributes are fully dependent on BookID, so it's in 2NF. But AuthorName transitively depends on BookID via AuthorID, violating 3NF.
What are joins?
Normalized databases usually have more of these, which can increase complexity in querying.
What are the consequences of having a poorly normalized database?
Data anomalies such as insertion, update, and deletion anomalies.
True or False: Every table in BCNF is also in 3NF, but not every 3NF table is in BCNF.
✅ A: True
📌 Explanation: BCNF is a stricter version of 3NF.
What is a multivalued dependency?
These dependencies involve multiple values for a single key and must be eliminated to reach Fourth Normal Form (4NF).
Table: Classroom(RoomNo, CourseID, Instructor)
FDs:
RoomNo, CourseID → Instructor
Instructor → CourseID
Answer: 3NF
📌 Explanation: The table is in 3NF but not in BCNF because Instructor → CourseID, and Instructor is not a superkey.
What is performance vs. data consistency?
This is a common trade-off when choosing denormalization over normalization.