Consider a project management system in which each employee is assigned to a spe
ID: 3693597 • Letter: C
Question
Consider a project management system in which each employee is assigned to a specific department and employees from several departments often are assigned to special project teams, however, when a new product is launched or for major marketing events. Note that the project hours are the number of hours that employees charge to their assigned projects. The following is an example of an un-normalized PROJECT-DATA table for two projects.
PROJECT-DATA
Project Number
Project Name
Start Date
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
2000
Project MARS
01/10/2012
89
Smith
Manager
1001
Web Design
150
18
Jones
Engineer
1001
Web Design
100
95
Mike
Technician
1005
Desktop
150
2010
Project PDRS
04/15/2012
36
Catherine
Programmer
1020
Software
1500
89
Smith
Manager
1001
Web Design
90
Perform the normalization process to convert the above un-normalized table to:
1. First normal form (1NF).
2. Second normal form (2NF).
3. Third normal form (3NF).
Please show all your work. Please show each step along the way and identify primary keys, if any, in each table in each step.
Project Number
Project Name
Start Date
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
2000
Project MARS
01/10/2012
89
Smith
Manager
1001
Web Design
150
18
Jones
Engineer
1001
Web Design
100
95
Mike
Technician
1005
Desktop
150
2010
Project PDRS
04/15/2012
36
Catherine
Programmer
1020
Software
1500
89
Smith
Manager
1001
Web Design
90
Explanation / Answer
Problem Without Normalization
Without Normalization, it becomes difficult to handle and update the database, without facing data loss. Insertion, Updation and Deletion Anamolies are very frequent if Database is not Normalized.
Normalization Rule
Normalization rule are divided into following normal form.
First Normal Form (1NF)
In First Normal Form, no two Rows of data must contain repeating group of information, That means each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.
The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form
Project Number
Project Name
Start date
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
2000, 2010
Project Mars
01/10/2012
89
Smith
Manager
1001
Web Design
150, 90
2000
18
Jones
Engineer
1001
Web Design
100
95
Mike
Technician
1005
Desktop
150
2010
Project PDRS
04/15/2012
36
Catherine
Programmer
1020
Software
1500
In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.
Project Table following 1NF will be:
Project Number
Project Name
Start date
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
2000
Project Mars
01/10/2012
89
Smith
Manager
1001
Web Design
150
2000
18
Jones
Engineer
1001
Web Design
100
95
Mike
Technician
1005
Desktop
150
2010
Project PDRS
04/15/2012
36
Catherine
Programmer
1020
Software
1500
2010
89
Smith
Manager
1001
Web Design
90
Second Normal Form (2NF)
As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.
In example of First Normal Form there are two rows for smith, to include multiple working hours that he has opted for. While this is searchable, and follows First normal form, it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {project name, employee name}, working Hours of employee only depends on project coloumn, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the project name names as foreign keys.
The project name with Employee name
Project Number
Project Name
Start date
Employee Number
Employee Name
Job Title
Department Number
Department Name
2000
Project Mars
01/10/2012
89
Smith
Manager
1001
Web Design
18
Jones
Engineer
1001
Web Design
95
Mike
Technician
1005
Desktop
2010
Project PDRS
04/15/2012
36
Catherine
Programmer
1020
Software
2010
89
Smith
Manager
1001
Web Design
New Project Table introduced for 2NF will be:
Project Number
Project Name
Start date
Employee Number
Job Title
Department Number
Department Name
Project Hours
2000
Project Mars
01/10/2012
89
Manager
1001
Web Design
150
18
Engineer
1001
Web Design
100
95
Technician
1005
Desktop
150
2010
Project PDRS
04/15/2012
36
Programmer
1020
Software
1500
2010
89
Manager
1001
Web Design
90
Third Normal Form (3NF)
Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.
Project-Detail Table
Project Number
Project Name
Start date
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
In this table Project number is Primary key, but employee name, job title, Dept No, name, working hours depends on employee number. The dependency between employee number and other fields is called transitive dependency. Hence to apply 3NF, we need to move the employee name, job title, dept number, name, project hours state to new table, with Employee number as primary key.
Project Table
Project Number
Project Name
Start date
Employee Number
Employee Table
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
The advantage of removing transitive dependency is,
Project Number
Project Name
Start date
Employee Number
Employee Name
Job Title
Department Number
Department Name
Project Hours
2000, 2010
Project Mars
01/10/2012
89
Smith
Manager
1001
Web Design
150, 90
2000
18
Jones
Engineer
1001
Web Design
100
95
Mike
Technician
1005
Desktop
150
2010
Project PDRS
04/15/2012
36
Catherine
Programmer
1020
Software
1500
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.