Normalization Exercises 1: Normalize the tables below. Show the resulting tables

Question

Normalization Exercises 1:

Normalize the tables below. Show the resulting tables.

Exercise 1:

You are given the following table:

EMP_PROJ(ssn, pnumber, hours, ename, pname, plocation)

And you are given the following functional dependencies:

(i)(ssn, pnumber) hours

This means: hours in dependent on ssn and pnumber.

(ii)(ssn) ename

This means: ename in dependent on ssn.

(iii)pnumber (pname, plocation)

Exercise 2:

You are given the following table:

EMP_DEPT(ename, ssn, bdate, address, dnumber, dname, dmgrssn)

And you are given the following function dependencies:

(i)ssn (ename, bdate, address, dnumber)

(ii)dnumber (dname, dmgrssn)

Explanation / Answer

Exercise 1:

step1: First Normal Form

we will assume that no column in the table has multiple values and therefore is in First Normal Form

step2:Second Normal Form

No partial dependency is allowed in 2-nd Normal Form.ie every non-prime attribute should be fully dependent on prime key attributes. But in the table pname,plocation can be identified by pnumber alone and ename can be identified by ssn alone so we have to split table.

EMP(ssn,ename,pnumber)=>ssn(primary key)

EMP_PROJ(pnumber,pname,plocation)=>pnumber(primary key)

EMP_HOUR(ssn,pnumber,hour)=>ssn and pnumber (minimal super key)

step 3 Third Normal Form

Above split up is also in Third Normal Form since no tables have non-prime attributes transitively dependent on prime attribute. so we end up three table.

Exercise 2:

step 1 First Normal Form

we will assume that no column in the table has multiple values and therefore is in First Normal Form

step 2 Second Normal Formal

No partial dependency is allowed in 2-nd Normal Form.ie every non-prime attribute should be fully dependent on prime key attributes. But in the table ename,bdate,address,dnumber can be identified by ssn alone and dname,dmgrssn can be identified by dnumber alone. so we have to split table.

EMP(ssn,ename,bdate,address,dnumber)=>ssn(primary key)

DEPT(dnumber,dname,dmgrssn)=>dnumber(primary key)

step3 Third Normal Form

No tables above have dependency between non-prime attributes. so we achieve 3 normal form and end up with 2 tables.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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