Normalization Questions - Full Solution
Easy Level - Question 1
Given Table:
| Student_ID | Student_Name | Course1 | Course2 |
|------------|--------------|---------|---------|
|1 | Alice | Math | Physics |
|2 | Bob | English | Math |
Step 1: 1NF
Split multivalued columns Course1 and Course2 into separate rows.
New Table (1NF):
| Student_ID | Student_Name | Course |
|------------|--------------|--------|
|1 | Alice | Math |
|1 | Alice | Physics |
|2 | Bob | English |
|2 | Bob | Math |
Primary Key: (Student_ID, Course)
Prime Attributes: Student_ID, Course
Non-prime Attribute: Student_Name
Step 2: 2NF
Separate Student and Course information to remove partial dependency.
Student Table:
| Student_ID | Student_Name |
Enrollment Table:
Normalization Questions - Full Solution
| Student_ID | Course |
Step 3: 3NF
No transitive dependency. Already in 3NF.
Easy Level - Question 2
Given Table:
| Employee_ID | Employee_Name | Department |
|-------------|---------------|------------|
| 1001 | John | HR |
| 1002 | Emma | IT |
Already in 1NF, 2NF, and 3NF.
Primary Key: Employee_ID
Prime Attribute: Employee_ID
Non-prime Attributes: Employee_Name, Department
Medium Level - Question 3
Given Table:
| Order_ID | Customer_Name | Product_Name | Product_Price |
|----------|---------------|--------------|---------------|
| 201 | Sarah | Laptop | 60000 |
| 202 | Sarah | Mouse | 1000 |
| 203 | John | Mobile | 25000 |
Step 1: 1NF
Already atomic.
Normalization Questions - Full Solution
Step 2: 2NF
Product price depends on Product, not Order_ID. Separate Customers and Products.
Customer Table:
| Customer_ID | Customer_Name |
Product Table:
| Product_ID | Product_Name | Product_Price |
Order Table:
| Order_ID | Customer_ID | Product_ID |
Step 3: 3NF
No transitive dependency.
Medium Level - Question 4
Given Table:
| Book_ID | Book_Title | Author_Name | Author_Email |
|---------|------------|-------------|--------------|
|1 | DBMS Basics| John Smith | john@email.com |
|2 | AI World | John Smith | john@email.com |
Step 1: 1NF
Atomic values.
Step 2: 2NF
No partial dependency.
Step 3: 3NF
Author_Name functionally determines Author_Email. Decompose.
Normalization Questions - Full Solution
Book Table:
| Book_ID | Book_Title | Author_Name |
Author Table:
| Author_Name | Author_Email |
Hard Level - Question 5
Given Table:
| Student_ID | Student_Name | Course_ID | Course_Name | Instructor_Name |
Step 1: 1NF
Atomic values.
Primary Key: (Student_ID, Course_ID)
Step 2: 2NF
Partial dependencies exist.
Student Table:
| Student_ID | Student_Name |
Course Table:
| Course_ID | Course_Name | Instructor_Name |
Enrollment Table:
| Student_ID | Course_ID |
Step 3: 3NF
No transitive dependencies.
Normalization Questions - Full Solution
Hard Level - Question 6
Given Table:
| Supplier_ID | Supplier_Name | City | Product_ID | Product_Name | Product_Category |
Step 1: 1NF
Atomic values.
Primary Key: (Supplier_ID, Product_ID)
Step 2: 2NF
Partial dependencies exist.
Supplier Table:
| Supplier_ID | Supplier_Name | City |
Product Table:
| Product_ID | Product_Name | Product_Category |
Supply Table:
| Supplier_ID | Product_ID |
Step 3: 4NF
No multivalued dependencies remain.
Extra Challenge - Question 7
Given Table:
| Hospital_ID | Hospital_Name | Doctor_ID | Doctor_Name | Patient_ID | Patient_Name |
Normalization Questions - Full Solution
Step 1: 1NF
Atomic values.
Primary Key: (Hospital_ID, Doctor_ID, Patient_ID)
Step 2: 2NF and 3NF
Hospital, Doctor, and Patient tables separated.
Hospital Table:
| Hospital_ID | Hospital_Name |
Doctor Table:
| Doctor_ID | Doctor_Name | Hospital_ID |
Patient Table:
| Patient_ID | Patient_Name | Hospital_ID |
Now fully normalized up to BCNF and 4NF.
