The purpose of this assignment is to normalize a set of attributes obtained from

Question

The purpose of this assignment is to normalize a set of attributes obtained from case information. Scenario: We have a mini universe where a company is interested in keeping track of how many hours each employee works on projects. Employees are hired, assigned an employee number and the company collects information regarding their name, address and birth date. Employees are assigned to one and only one department when hired. Each department has a number, name and manager. The department manager number is the same as the employee number. Each project is worked on by more than one employee and one employee can work on several projects. Each project is assigned a number,

Explanation / Answer

1. Functional dependencies:

eno -> ename

eno -> manager

eno -> bdate

eno -> eaddress

eno -> dno

dno-> dname

dno -> dmngno

pno -> phours

pno -> ploc

2. Normalization

EMPLOYEE(eno,ename,bdate,eaddress,dno,dname,dmngno,pno,phours,ploc)

The Relation Employee is in 1st normal form as all theattribute values are atomic.

Primary key --- eno

But all the non key attributes are not fully functionally dependent on the primary key eno. So the relation is not in the second normal form

Use functional dependencies to create 3 tables from this relation EMPLOYEE

EMPLOYEE(eno,ename,bdate,eaddress,dno)

primary key --- eno

DEPARTMENT(dno,dname,dmngno)

primary key --- dno

PROJECT(pno,phours,ploc)

primary key --- pno

There is no transitive dependency in these tables. So the tables are in 3rd normal form.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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