Exercise No: 1 Normalize the following table up to 3rd normal form.
(Show the normalization process step by step)
Table Name: Employee_Projects
Employee_I Employee_Na Departme Phon Project_I Project_Na Start_Da End_Dat Project_Manag Manager_Email Hours_Work
D me nt e D me te e er ed
555-
123- Payroll 2023-07- 2023-08- jane@[Link]
2001 John Smith HR 4567 3001 System 01 30 Jane Doe m 160
555-
987- Inventory 2023-07- 2023-09- mark@example.c
2002 Jane Doe IT 6543 3002 App 10 15 Mark Johnson om 220
555-
789- Payroll 2023-07- 2023-08- jane@[Link]
2003 Bob Johnson Marketing 1234 3001 System 01 30 Jane Doe m 180
555-
987- Customer 2023-08- 2023-10- mark@example.c
2002 Jane Doe IT 6543 3003 Portal 01 31 Mark Johnson om 300
Solution:
First Normal Form (1NF)
1NF requires that all columns contain atomic values, and each row contains a unique identifier. In the given table, each row is
already uniquely identifiable by Employee_ID and Project_ID together.
Primary Key: (Employee_ID , Project_ID)
Second Normal Form (2NF)
The table should be in 1NF and that all non-key attributes should be fully functionally dependent on the primary key (there should
not be any partial dependency). Here, the primary key is the composite key (Employee_ID, Project_ID)
Partial Dependencies in the Table:
1. Employee_Name, Department, and Phone are dependent only on Employee_ID.
2. Project_Name, Start_Date, End_Date, Project_Manager, and Manager_Email are dependent only on Project_ID.
Original Table Decomposed into following tables:
1. Employees (Employee_ID, Employee_Name, Department, Phone)
2. Projects (Project_ID, Project_Name, Start_Date, End_Date, Project_Manager, Manager_Email)
3. Employee_Projects (Employee_ID, Project_ID, Hours_Worked)
Third Normal Form (3NF)
3NF requires the table to be in 2NF and that all attributes are only dependent on the primary key. We need to ensure that there are
no transitive dependencies.
In the Projects table, Manager_Email is dependent on Project_Manager (This is transitive Dependency we have to further
decompose the project table as following.
Projects (Project_ID, Project_Name, Start_Date, End_Date, Project_Manager)
Manager (Project_Manager, Manager_Email)
Note: (Project_Manager will be the Primary key in Manager Table and foreign key in Projects table.
Final Normalized Tables
Employees Table:
Employee_ Employee_Na Departme Phone
ID me nt
2001 John Smith HR 555-123-
4567
2002 Jane Doe IT 555-987-
6543
2003 Bob Johnson Marketing 555-789-
1234
Projects Table:
Project_I Project_Nam Start_Dat End_Date Project_Manag
D e e er
3001 Payroll System 2023-07-01 2023-08- Jane Doe
30
3002 Inventory App 2023-07-10 2023-09- Mark Johnson
15
3003 Customer 2023-08-01 2023-10- Mark Johnson
Portal 31
Employee_Projects Table:
Employee_ Project_I Hours_Work
ID D ed
2001 3001 160
2002 3002 220
2003 3001 180
2002 3003 300
Managers Table:
Project_Manag Manager_Email
er
Jane Doe jane@[Link]
m
Mark Johnson mark@example.c
om
Exercise No: 2 Normalize the following table up to 3rd normal form. (Show the normalization process step by step)
Table Name: Books_Authors
Publication_Y Author_I Author_Na Nationali Birth_Ye
ISBN Book_Title ear Genre D me ty ar
9781234567 "Example
89 Book 1" 2022 Fiction 1001 John Smith USA 1985
9789876543 "Sample
21 Novel" 2021 Fiction 1002 Jane Doe Canada 1990
9785555555 "Book of Fantas
55 Wonders" 2023 y 1001 John Smith USA 1985
9789998887 "Mystery Myster Bob
77 Revealed" 2020 y 1003 Johnson UK 1978
"Science
9783334445 Unleashed Scienc
55 " 2023 e 1004 Emily White Australia 1988
"Historical
9787777777 Chronicles Histor Michael
77 " 2022 y 1005 Brown USA 1980
Solution:
First Normal Form (1NF)
1NF requires that all columns contain atomic values, and each row contains a unique identifier. In the given table, each row is
already uniquely identifiable by ISBN and Author_ID together.
Primary Key: (ISBN, Author_ID)
Second Normal Form (2NF)
The table should be in 1NF and all non-key attributes should be fully functionally dependent on the primary key (there should not be
any partial dependency). Here, the primary key is the composite key (ISBN, Author_ID).
Partial Dependencies in the Table:
1. Book_Title, Publication_Year, and Genre are dependent only on ISBN.
2. Author_Name, Nationality, and Birth_Year are dependent only on Author_ID.
Original Table Decomposed into Following Tables:
1. Books (ISBN, Book_Title, Publication_Year, Genre)
2. Authors (Author_ID, Author_Name, Nationality, Birth_Year)
3. Books_Authors (ISBN, Author_ID)
Third Normal Form (3NF)
3NF requires the table to be in 2NF and that all attributes are only dependent on the primary key. We need to ensure that there are
no transitive dependencies.
In the given tables, there are no transitive dependencies because all non-key attributes are dependent directly on their respective
primary keys.
Final Normalized Tables
Books Table:
ISBN Book_Title Publication_Ye Genre
ar
9781234567 Example Book 1 2022 Fiction
89
9789876543 Sample Novel 2021 Fiction
21
9785555555 Book of Wonders 2023 Fantasy
55
9789998887 Mystery Revealed 2020 Mystery
77
9783334445 Science 2023 Science
55 Unleashed
9787777777 Historical 2022 History
77 Chronicles
Authors Table:
Author_I Author_Name Nationalit Birth_Yea
D y r
1001 John Smith USA 1985
1002 Jane Doe Canada 1990
1003 Bob Johnson UK 1978
1004 Emily White Australia 1988
1005 Michael Brown USA 1980
Books_Authors Table:
ISBN Author_I
D
97812345678 1001
9
97898765432 1002
1
97855555555 1001
5
97899988877 1003
7
78333444555 1004
97877777777 1005
7