Question:

We basically have a relational schema - R. The relation has the following attributes:

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

Attributes Eno, Ename, Edob - belongs to the employee entity.

Attributes DNo, DName belong to the Department entity.

Attributes PNo, PName, PCity, PCountry - belong to project entity

The relational Schema also contains of attributes hours and rate

Employees, Departments, and Projects are identified by unique numbers. The number of hours and hourly rate of pay for an employee to carry out a project is 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, 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. There are no NULL values existing in the table.

Please complete the following tasks:

(a) Identify the Functional Dependencies in relational schema R. Be sure to only include functional

dependencies that satisfy the following 4 rules:

1) Only include non-trivial Functional Dependencies(FDs);

2) Minimize the determinant (LHS), that is, only include full FDs;

3) Maximize the RHS;

4) Only include FDs that cannot be derived from other FDs using Armstrongs axioms.

(b) Identify the candidate key(s) of R based on the Functional Dependencies.

You need to use the concept of attribute closure to identify the key(s).

Intermediate steps in this process should be detailed.

(c) Assume that R is in 1st Normal Form. Now normalise the relation to 2nd Normal Form, 3rd Normal Form, and Boyce-Codd Normal Form(BCNF). Be sure to indicate the Functional Dependencies you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not sufficient.

 
 
 

Transcribed Image Text:

1. a) Provide a state diagram (automata) that takes in input, sequentially, one by one, bits, and outputs 1 if the last three read bits are 000, 110, 101 or 011 and outputs 0 otherwise. Of course, O is outputed when less than 2 bits are read. b) Provide 4 entry sequences (time 1 to time 10) that gives the outputs specified below with the automata specified in a) time 12 3 4 S 57 89110 Entry 1 Entry 2 Entry 3 Entry 4 Outputs 0 1 11 1 1 111 1. a) Provide a state diagram (automata) that takes in input, sequentially, one by one, bits, and outputs 1 if the last three read bits are 000, 110, 101 or 011 and outputs 0 otherwise. Of course, O is outputed when less than 2 bits are read. b) Provide 4 entry sequences (time 1 to time 10) that gives the outputs specified below with the automata specified in a) time 12 3 4 S 57 89110 Entry 1 Entry 2 Entry 3 Entry 4 Outputs 0 1 11 1 1 111