Consider a one-relation database with the following attributes: Employee number

Question

Consider a one-relation database with the following attributes:

Employee number (emp_no), Date hired (date), Job title (job), Phone number (phone_no), Office number (office_no), Area (area), Salary (sal), project number (proj_no), Project budget (p_budget), Department number (dep_no), Department budget (d_budget), and Department manager employee number (mgr_emp_no).

The following business rules apply:

• No employee can manage more than one department at a time.

• No employee can work in more than one department at a time.

• No employee can work on more than one project at a time.

• No employee can have more than one office at a time.

• No employee can have more than one phone at a time.

• No employee can have more than one job at a time.

• No project can be assigned to more than one department at a time.

• No office can be assigned to more than one department at a time.

• Department numbers, employee numbers, project numbers, office numbers, and phone numbers are all “globally” unique.

The following functional dependencies also apply:

• emp_no g phone, emp_no g office_no, emp_no g dep_no, emp_no g proj_no

• {emp_no, date} g job, {emp_no, date} g sal

• phone_no g office_no, office_nog area, office_no g dep_no

• proj_no g dep_no, proj_no g p_budget

• dep_no g mgr_emp_no, dep_no g d_budget

• mgr_emp_no g dep_no

Transform this relation into 3 NF. Justify any decomposition.

Explanation / Answer

Employee(emp_no, date, job,phone_no, office_no, area, sal,proj_no, p_budget, dep_no, d_budget, mgr_emp_no)

The relation is in First normal form as there seems to be atomic values for all attributes.

For the relation to be in Second Normal Form the non key attributes should be fully functionally dependent on the primary key emp_no. But here it is not the case . Also the transitive dependencies needs to be removed to normalize the re;ation to third normal form.

As per constraints and functional dependencies following tables can be formed from the original relation.

Employee(emp_no,phone_no,office_no,dep_no,proj_no)

No employee can have more than one job at a time.

EmployeeJob(emp_no,date,job,sal)

remove transitive dependencies

OfficePhone(phone_no,office_no)  

Office(office_no,area,dep_no)

No project can be assigned to more than one department at a time.

Project(proj_no,dep_no,budget)

Department(dep_no,mgr_emp_no,d_budget)

No employee can manage more than one department at a time.

DepartmentManager(mgr_emp_no,dep_no)

underlined are primary keys

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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