1

1.9kviews

Convert the following tables to suitable Normal Form.

**1 Answer**

1

1.9kviews

Convert the following tables to suitable Normal Form.

2

157views

written 2.0 years ago by | • modified 2.0 years ago |

- 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.
are some of the normal forms in SQL.*1NF, 2NF, 3NF, BCNF, 4NF, 5NF, and 6NF*

- 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:

- 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:

ADD COMMENT
EDIT

Please log in to add an answer.