could you please help me figure the question out and give explaniation for each

Question

could you please help me figure the question out and give explaniation for each answer? Thanks!

3. Normalization. Consider the relation schema SCHEDULE P(roject), V (enue), T(ime), C(oordinator)) that lists the venue, time, and coordinator of projects. The following set ? of functional dependencies defines the business rules of the domain. TV-P, PT ? V, and (a) Determine all keys implied by , and argue that these are all keys [5 marks] (b) Compute a lossless Boyce-Codd Normal Form decomposition of SCHEDULE with re- spect to the given set ? of functional dependencies. Show all details of your compu- 4 marks) tation (c) Compute a lossless Third Normal Form decomposition of SCHEDULE with respect to the given set ? of functional dependencies. Show all details of your computation. 4 marks (d) Argue which of the two decompositions in (b) and (c) is better 5 marks (e) Write down a relation over SCHEDULE with two tuples that contain a redundant data value occurrence. What are the projections of this relation to the decomposed schema 5 marks in (b)? What has changed?

Explanation / Answer

a) Functional dependencies are,

P-> C

TV -> P

PT -> V

CT -> P

Candidate keys are    =>      TP,TV,TC

in the above functional dependency, attribute T is not given in any of the RHS of dependencies. So T must be there in candidate key. To find key, we can take closure of attributes containing T.

first take T alone,

T+ = T      ( here only T can get, so T only can’t be candidate key.)

TP+ = TPCV           (it can be candidate key)

TV+ = TVPC           (it can be candidate key)

TC+ = TCPV           (it can be candidate key)

                From TP,TV,TC we can generate all attributes of the table. Therefore candidate keys are TP,TV,TC

b & c )  BCNF Normalization and 3rd Normal form

here i am giving answers of both b and c. because after 3rd NF only we can reach at BCNF.

we can check from 1st normalization onwards to reach at BCNF.

1st normal form

            By default every table is in 1st normal form.

2nd Normal form

             Rule : the table must be in 1st normal form and there should not be any partial dependency between attributes. That is every non-prime attribute must be fully dependent on candidate key.

Here one partial dependency is there, P -> C. so decompose the table to make this in 2nd NF.

R1 ( PC )

R2 ( TVP )

         R1 and R2 are the tables after decomposition.

3rd Normal Form

Rule : the table must be 2nd normal form and for a non-trivial functional dependency X -> Y , it should satisfy either of the following two conditions,

R1 ( PC )       :-   Functional dependency of this table is P->C  

                           P is candidate key here.

R2 ( TVP )     :-   Functional dependency of this table is TV -> P and PT -> V

Here LHS of all functional dependencies are Candidate keys , therefore it is Super key. So both tables are in 3rd NF.   

BCNF

Rule : the table must be 3rd normal form and for a non-trivial functional dependency       X -> Y , it should satisfy the following two conditions,

          We have already proved that LHS of all dependencies are Super keys. Therefore the two tables are in BCNF.

The tables in BCNF are,        R1(PC) and R2(TVP)

d) Adequate normal form is 3rd NF. most of the 3rd NF tables are free from update,insert and delete anomalies. and upto 3rd NF lossless dependency and dependency preserving is guaranteed. but in case of BCNF, dependency preserving is not guaranteed. so we can assume that 3rd NF is better.

*******************END******************PLS GIVE ME GOOD RATING********************

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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