The dataset below shows list of employees of an organization and the departments
ID: 3797163 • Letter: T
Question
The dataset below shows list of employees of an organization and the departments that the employees are working in. The EMPLOYEEL_ID column of the dataset uniquely identifies an employee of the organization. The DEAPARTMENT_ID column of the dataset uniquely identifies a department of the organization. An employee of this organization can work only in one department of the organization. Based on the information provided, please normalize the given dataset. Your response should clearly indicate resultant tables after the normalization along with their colums. Also please indicate all the primary keys and applicable foreign keys.Explanation / Answer
Normalization:
Commonly used forms are:
- First normal form(1NF)
- Second normal form(2NF)
- Third normal form(3NF)
- Boyce & Codd normal form (BCNF)
First normal form(1NF):
As per 1NF, the table of cloumn should not have multiple values i.e each column hold signle values. so given table is in 1NF,
emplyee_id employee_first_name employee_last_name department_id deparment_name
100 scotty Jordan 1 Accounting
110 Michael pippen 2 HR
113 kobe nash 3 IT
115 steve bryant 2 HR
Second normal form(2NF):
It includes,
- The table should be in 1NF
- Non-prime attribute is not dependent on the subset of primary key of the table.
An attribute which is not the part of the primary key is called Non-prime attributes.
As per given table,
primary key is emplyee_id, and non-prime attributes are employee_first_name and employee_last_name. And deparment_name is depend on the department_id.
department_id deparment_name
1 Accounting
2 HR
3 IT
2 HR
here we have multiple rows with same information, so we can compress this like,
department_id deparment_name
1 Accounting
2 HR
3 IT
Third normal form(3NF):
It includes,
- Table must be in 2NF
- Non-prime attributes should not depends on the other non-prime attributes
Here employee_first_name and employee_last_name are non-prime attibutes, and deparment_name is another non-prime attibute. so we need to split into two tables,
emplyee_details:
emplyee_id employee_first_name employee_last_name
100 scotty Jordan
110 Michael pippen
113 kobe nash
115 steve bryant
and
department_details:
department_id deparment_name
1 Accounting
2 HR
3 IT
In emplyee_details table emplyee_id is primary key or candidate key. In department_details table department_id is primary key. so we can compress like,
emplyee_id employee_first_name employee_last_name department_id
100 scotty Jordan 1
110 Michael pippen 2
113 kobe nash 3
115 steve bryant 2
department_id is the foreign key of emplyee_details table.
Boyce & Codd normal form (BCNF):
As per above 3NF table of emplyee_details, the relation should be declared as,
Functional dependencies:
emplyee_id -> {employee_first_name, employee_last_name}
department_id -> deparment_name
Primary Keys:
For emplyee_details table: emplyee_id
For department_details table: department_id
Foreign Keys:
For emplyee_details table: department_id
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.