Construct a Relational Schema For The ER-Diagram. Correctly Translate Specialization and Many-to-Many Relationships.

Question. 

8 marks 

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 man ager, 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.

Please refer to Page 26 of the Module 10 lecture notes for the details of the above requirements. 

(b) Identify the super keys of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the keys. Inter mediate steps in this process should be summarised. There may be many different super keys of R. It would be great if you can present all of them in a smart way rather than enumerating them one by one. 

(c) Assume that R is in 1NF. Now normalise the relation to 2NF, 3NF, and BCNF. Be sure to indicate the FDs you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not sufficient. 

While it is recommended to answer this question on just one page, you should use no more than two pages for this 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. Please refer to Page 26 of the Module 10 lecture notes for the details of the above requirements.

ANSWER 2: PART a)

Functional dependencies are:

 {ENo} —–> {Ename}

 {ENo} —–> {Ename, Edob}

 {DNo} —–> {Dname, DManager, PNo}

 {DManager} —-> {PNo}

 {PNo} —–> {PName, PCity, PCountry}

 {ENo, PNo} —-> {hours, rate}

b) Identify the super keys of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the keys. Intermediate steps in this process should be summarised. There may be many different super keys of R. It would be great if you can present all of them in a smart way rather than enumerating them one by one

PART b)

ENo+ = EName, EDob because {ENo} —–> {Ename, Edob}

hence ENo is super key.

(R – ENo+) U ENo = ENo DNo PNo PName DManager PCity PCountry Hour Rate is super key of R.

DNo+ = DNo DName DManager because {DNo} —–> {Dname, DManager}

hence DNo is super key.

(R – DNo+) U DNo = DNo PNo EName PName Edob PCity PCountry Hour Rate is super key of R.

PNo + = PNo PName PCountry PCity

hence PNo is super key.

(Rate – PNo) U PNo = PNo ENo DName Edob DManager House rate is a super key.

ENo PNo + = ENo EName Edob PNo PName PCity PCountry

Hence {ENo PNo} is a super key

Assume that R is in 1NF. Now normalise the relation to 2NF, 3NF, and BCNF. Be sure to indicate the FDs you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not sufficient.

PART c)

Conversion of given relationship into a normalized database that satisfies 3NF.

R(ENo, DNo, PNo, EName, DName, PName, Edob,  Dmanager, PCity, PCountry, hours, rate)

1NF: According to first normal form, no two rows of data must contain repeating group of information.

Given relation is already in first normal form.

2NF: According to second normal form, there must not be any partial dependency of any column on the primary key.

When 2NF is applied then tables are

Employee (ENo, EName, Edob, Dno)

Department (Dno, DName, DManager, Pno)

Project (Pno, Pname, Pcity, PCountry, hours, rate)

3NF: According to third normal form, every non-prime attribute of the table must be dependent on primary key.

When 3NF is applied then tables are:

Employee (ENo, EName, Edob, Dno)

Department (Dno, DName, DManager, Pno)

Project (Pno, PName, PCity, PCountry)

EmployeeProject { ENo, PNo, hours, rate}