Hi! I need help with this normalization/SQL database task. I`m not sure HOW to n

Question

Hi! I need help with this normalization/SQL database task. I`m not sure HOW to normalize it. (I´ll make sure to give you a "thumbs up"):

Consider the database below:

a) Is it on first normal form? If not, normalize it

b) Is it on second normal form? If not, normalize it

c) Is it on third normal form? If not, normalize it.

In the tasks a) to c) you need to motivate (prove) your answer and not just write YES/NO.

The table DEPT:

The table EMP

DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Explanation / Answer

a) Is it on first normal form?

Yes it is in first normal form.

First Noraml Form:

First Normal Form is defined in the definition of relations (tables) itself. This rule defines that all the attributes in a relation must have atomic domains. The values in an atomic domain are indivisible units.

Example:

We re-arrange the relation (table) as below, to convert it to First Normal Form.

As from the given tables each domain have single value ,hence it is in FirstNoramlForm.

b)Is it on second normal form?

Yes it is in Second Normal form

SecondNormalForm:

Every non-prime attribute should be fully functionally dependent on prime key attribute.

Example:Empt_Dept table

We see here in Empt_Dept relation that the prime key attributes are EMPNO and DEPTNO. According to the rule, non-key attributes, i.e. EName and DName must be dependent upon both and not on any of the prime key attribute individually. But we find that EName can be identified by EMPNO and DName can be identified by DEPTNO independently. This is called partial dependency, which is not allowed in Second Normal Form.

EMP:

Dept:

As per from the given tables there is no partial dependency

Emp table and Dept table are in 2ND Normal form

c)Is it on third normal form?

Yes it is in Third Normal Form

ThirdNormalForm

For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy

X is a superkey or,

A is prime attribute.

Emp:

We find that in the above Emp relation, EMPNO is the key and only prime key attribute. We find that DNAME can be identified by EMPNO as well as DEPTNO itself. Neither DEPTNO is a superkey nor is DNAMEa prime attribute. Additionally, EMPNO DEPTNO DNAME, so there exists transitive dependency.

To bring this relation into third normal form, we break the relation into two relations as follows

Emp:

DEPT:

From the given tables it is in 2nd Normal form and there is no transitive dependency.

JOB DEPTNO CLERK 20,10 ANALYST 20,20

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.