Consider the following relation schema for table R: R(ENo, DNo,PNo,EName, DName,
ID: 3842080 • Letter: C
Question
Consider the following relation schema for table R:
R(ENo, DNo,PNo,EName, DName,PName,Edob, Dmanager,PCity,PCountry, hours,rate)
Attributes starting with “E” refer to Employees, those starting with “D” refer
to Departments, and those with “P” to Projects. Employees, Departments, and
Projects are identified by unique numbers. Each department has only one manager,
but an employee can be managers for different departments. A department
manager is represented by his/her name. The number of hours and hourly rate
of pay for an employee to carry out a project are determined by himself/herself
and the project. There may be multiple projects that are conducted in a department
and multiple departments can be involved in a single project as well.
Any employee can be affiliated with a few different departments and work in
multiple projects at the same time. A project may involve multiple employees.
Names for employees, managers, departments and projects are not generally
unique. A project will only be conducted in a single city. Multiple cities from
the same country may appear in the table, however, cities are uniquely named
within and across all countries.
Answer the following questions:
(a) Identify the Functional Dependencies in R. Be sure to only include functional
dependencies that satisfy the following 4 rules: 1) Only include
non-trivial FDs; 2) Minimize the determinant (LHS), that is, only include
full FDs; 3) Maximize the RHS; and 4) Only include FDs that cannot be
derived from other FDs using Armstrongs axioms.
Explanation / Answer
To form functional dependencies initially we must need to convert given relational schmema into normalization database that satisfies minimum of 3NF.
Given relational schmema:
R(ENo, DNo,PNo,EName, DName,PName,Edob, Dmanager,PCity,PCountry, hours,rate).
1NF:
As per First Normal Form, no two Rows of data must contain repeating group of information
The given relation schmea is already in 1NF.
2NF: As per the Second Normal Form there must not be any partial dependency of any column on primary key.
By appling 2NF then the table should result as:
Employee(Eno,Ename,Edob,Dno)
Department(Dno,Dname,Danager,Pno)
Project(Pno,Pname,Pcity,PCountry,hours,rate)
3NF:
Third Normal form applies that every non-prime attribute of table must be dependent on primary key.
The above resulting schema satisfiess 3NF.
Employee(Eno,Ename,Edob,Dno)
Department(Dno,Dname,Danager,Pno)
Project(Pno,Pname,Pcity,PCountry,hours,rate)
Functionnal dependencies are:
{ENo}----->{Ename}
{ENo}----->{Ename,Edob}
{DNO}----->{DName,DManager,PNo}
{DManager}-------->{PNo}
{PNO}------------->{PName,Pcity,Pcountry}
{Pname}------->{hours}