Question

Normalisation

Consider the following relation schema about project meetings:

PMG(projID, title, type, manager, jobID, start-date, end-date, contractor, contractNo)

Some notes on the semantics of attributes are as follows:

• Each project has a unique project ID (projID) and also has a title, type and manager. Each manager has a specialty project type.

• A project often contracts jobs to contractors with start-date and end-date. Contracts are identified by contract numbers (contractNo), but contract details are out of the scope of the database.

FDs based on business rules are given as follows:

• projID → title, type, manager

• manager → type

• jobID → projID, start-date, end-date, contractor

• projID, title, jobID → contractNo

• contractNo → jobID, contractor, start-date, end-date

• jobID → contractNo

Answer questions below:

2.1. The given FDs have redundancies. Give the minimal basis for the given FDs.

2.2. The PMG relation is not in BCNF or 3NF. Explain why. Your explanation must be based on the functional dependencies in Question 2.1.

2.3. Decompose the PMG relation into relations in BCNF or 3NF. Your decomposition must keep all functional dependencies and must be lossless. For each resultant relation, discuss if it is in BCNF or 3NF and indicate the primary key (underline) and any foreign keys (*).

Note that relations must be written in the form as shown in the examples below:

Student(sno, name, address)

Course(cno, title)

Take(sno*, cno*, grade)

Answer 1

ANSWER:

CPONSIDERING THE CONDITIONS AND REQUIREMENTS FROM THE QUESTION.

2.1)

Here let's rename the attributes for simplicity

projID(A) → title(B), type(C), manager(D)

manager(D) → type(C)

jobID(E) → projID(A), start-date(F), end-date(G), contractor(H)
projID(A), title(B), jobID(E)→ contractNo(I)

contractNo(I) → jobID(E), contractor,(H) start-date(F), end-date(G)

jobID(E) → contractNo(I)

Now after renaming the Functional dependencies above can be represented as

A ----> BCD

D----->C

E----->AFGH

ABE---> I

I -----> EHFG

E-----> I

For minimising the attributes we can foolow the given steps

Step1 : - Split the FD's on RHS into single attributes

eg: A ---> BC === A---> B and A---> C

Step 2:- Find the redundant FD's and delete them

Step 3:- Find the redundant attributes on LHS and delete them

eg: AB---> C , here A can be deleted if B+ contains A

For the above FD's following the above steps

After step 1

{ A--> B , A--> C , A--> D ,

D-->C,

E-->A , E-->F , E-->G , E-->H ,

ABE --> I ,

I-->E , I-->H , I-->F , I-->G ,

E-->I }

Now ,I-->H , I-->F and I-->G can be deleted as after deleting these FD' we can still derive them as I+ will still be E

After Step 2

{ A--> B , A--> C , A--> D ,

D-->C,

E-->A , E-->F , E-->G , E-->H ,

ABE --> I ,

I-->E ,

E-->I }

Now ABE can also be deleted as E still can alone derive I using E--> I

After Step 3

{ A--> B , A--> C , A--> D ,

D-->C,

E-->A , E-->F , E-->G , E-->H ,

I-->E ,

E-->I }

Therefore after minimisation

{ A---> BCD

D---> C

E---> AFGH

I ---> E

E--->I }

solution 2.2

Base on functional dependency given in 2.1, we can say

Clearly E+ = ABCDEFGHI and I+ = ABCDEFGHI

Also from the above point we can say E and I are the only 2 candidate keys

And for a functional dependency X---->Y to be in 3NF

1) X should be a super key

OR

2) Y should be a prime attribute

But the function dependency D---> C doesn' t satisfy any one of the above condition therefore the relation is not in 3NF and if a relation is not in 3NF then it will not be in BCNF also

solution 2.3

The table can be broken into following manner(and FD's applicable for each table are also written under each table)

table0(AD*) |table1(ABC) | table2(DC*) | table3(EA*) | table4(EFGH) |table5(IE*)

A--->D | A-->BC | D---> C | E---> A | E-->FGHI | I--->E

Now each relation in each decomposed table follows the BCNF property as the LHS attribute is a super key for the respective table and if table is in BCNF then it is also in 3NF. And we can also see that all of the FD' s can be derived by the decomposed table as well , therefore FD' s are conserved too the table can be merged in the order of table number i.e table0 merge with table1 then merge resultant with table2 and so on and we will at last obtain a lossless table as there is a common attribute between table0 and table1 and after they get merge there is common attribute(D) in table(0+1) and table(2) and so on.

NOTE: PLEASE UPVOTE ITS VERY MUCH NECESSARY FOR ME A LOT.