Normalization

• Normalization organizes the data in the database.
• Normalization is used to minimize the redundancy from relations and eliminate undesirable features such as Insertion, Update, and Deletion Anomalies.
• Normalization divides the larger table into smaller and links them using relationships.
• 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, and 6NF are some of the normal forms in SQL.

a] The EMP_DEPT Table

• The EMP_DEPT table contains Ename, Ssn, Bdate, Address, Dnumber, Dname, and Dmgr_ssn. In that Ssn is a primary key.
• All the columns of the table contain atomic values hence the given table is already in 1NF.
• All non-key attributes are fully functionally dependent on the primary key, there is no partial dependency in relation that exists between non-key and key attributes hence the given table already in 2NF.

The functional dependencies table contains are as follows:

$$Ssn → Ename$$

• This is not a transitive functional dependency because there is no set of attributes X was $Ssn → X$ and $X → Ename$
• But,

$$Ssn → Dmgr\_ssn$$

• This is a Transitive functional dependency because Dmgr_ssn is also derived from the below transitive nature of FD:

$$Ssn → Dnumber$$ $$Dnumber → Dmgr\_ssn$$

• Therefore, this violates the basic condition for the 3NF.
• That is in the third normal form, no transition dependency exists for non-prime attributes.
• That means there is no transitive dependency in relation exists between non-key and key attributes.

Based on these primary key and functional dependencies information the given relational table normalized into 3NF form as follows:

b] The EMP_PROJ Table

• The EMP_PROJ table contains Ssn, Pnumber, Hours, Ename, Pname, and Plocation as columns or attributes.
• In that Ssn is a primary key.
• All the columns of the table contain atomic values hence the given table is already in 1NF.

The functional dependencies table contains are as follows:

• Employee ssn and project number determine the hours that the employee works on the project.

$$\{Ssn, Pnumber\} → Hours$$

• This shows full functional dependency because it can not hold

$$ Ssn → Hours$$ $$Pnumber → Hours$$

• But, Employee ssn and project number can not determine employee name

$$\{Ssn, Pnumber\} → Ename$$

• Because this shows partial dependency because it also holds

$$Ssn → Ename$$

• Therefore, this violates the basic condition for the 2NF.
• That is in the second normal form, all non-key attributes are fully functional dependent on the primary key.
• That means there is no partial dependency in relation exists between non-key and key attributes.

Based on these primary key and functional dependencies information the given relational table normalized into 2NF form as follows: