Consider the following relations, and identify the highest normal form for each,

Question

Consider the following relations, and identify the highest normal form for each, as given, stating any assumptions you need to make.

a. Work1 (empId, empName, dateHi red, jobTitle, jobLevel)

b. Work2 (empId, empName, jobTitle, rating Date raterName rating)

c. Work3 (empId, empName, projectNo, projectName, proj Budget, empManager, hoursAssigned)

d. Work4 (empId, empName, school Attended, degreeAwarded, graduation Date)

e. Work5 (empld, empName, social SecurityNumber, dependent Name dependent Address relationToEmp)

For each of the relations in above, identify a primary key and:

a. if the relation is not in third normal Form, find a 3NF lossless join decomposition that preserves dependencies.

b. if the relation or resulting set of relations is not in Boyce-Codd Normal form, find a lossless join decomposition that is in BCNF. Identify any functional dependencies that are not preserved

Consider the following relations, and identify the highest normal form for each, as given, stating any assumptions you need to make. a. Worki (empId, empName, dateHi red, jobTitle, jobLevel) b. Work2 (empId, empName, jobTitle, rating Date raterName rating) c. Work3 (empId, empName, projectNo, projectName, proj Budget, empManager, hoursAssigned) d. Work4 (empId, empName, school Attended, degreeAwarded, graduation Date) e. Work5 (empld, empName, social SecurityNumber, dependent Name dependent Address relationToEmp) For each of the relations in Exercise 6.5, identify a primary key and, a. if the relation is not in third NF lossless normal form, find a join decomposition that preserves dependencies Boyce-Codd b. if the relation or resulting set of relations is not in Normal Form, find a lossless join decomposition thatis in BONE Identify any functional dependencies tha are not preserved

Explanation / Answer

Given relations are:

a. Work1 (empId, empName, dateHi red, jobTitle, jobLevel)
b. Work2 (empId, empName, jobTitle, rating Date raterName rating)
c. Work3 (empId, empName, projectNo, projectName, proj Budget, empManager, hoursAssigned)
d. Work4 (empId, empName, school Attended, degreeAwarded, graduation Date)
e. Work5 (empld, empName, social SecurityNumber, dependent Name dependent Address relationToEmp)

A primary key is nothing but a candidate key that is main reference key for the table.

primary keys:

work1-----> empId
work2-----> empId
work3-----> ProjectNo
work4-----> empId
work5-----> social SecurityNumber

3NF:
....
In 3NF every non-prime attribute of table must be dependent on primary key.

BCNF:
........
In BCNF for every functional dependency X->Y, X should be the super key of the table.

By decomposing above tables to satisfy 3NF and BCNF:

Work1 (empId, empName, dateHi red,jobTitle)

job( jobTitle, jobLevel)

Work2 (empId, empName, jobTitle, rating)

rating(rating Date, raterName, rating)

Work3 (empId, empName,empManager, projectNo)

project (projectNo, projectName, proj Budget, hoursAssigned)

Work4 (empId, empName, school Attended)

degree(school Attended, degreeAwarded, graduation Date)

Work5 (empld, empName, social SecurityNumber)

ssn(social SecurityNumber, dependent Name dependent Address relationToEmp)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---