1
1.9kviews
Convert the following tables to suitable Normal Form.

Convert the following tables to suitable Normal Form.

1 Answer
2
155views

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:

3NF


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:

2NF

Please log in to add an answer.