Question

Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#, comission_rate, discount) In addition to the functional dependency implied by the primary key (car#), assume the following functional dependencies: date_sold discount salesperson commission_rate Explain why this relation is not in Boyce-Codd Normal Form (BCNF). a. b. Show how you would normalize this relation to achieve Boyce-Codd Normal Form by drawing a new relational schema. Highlight all primary keys by underlining them and use arrows to show foreign key relationships

Answer 1

R (car#, date Sold, sales person , commission - roste, discount) Assume as R (a,b,c,d,e) siven 'alis Pk a a ache (aste abcde an bcde bate cad (date-sold (salesperson discount) Commission rate) Check for candidate keys .. Ta' is one ck 1 (from given data) (bc)* = bced * so no other possible. ck's are iii 'a' is only 'ck'. not in BCNE BENE LHS of every a) Relation According FD should be to Superkey, but date. sold Salesperson violating BCNF t » discount Commission-rate BNF properties,

Convesting into ENE! R (a,b,c,d,e) as bede violating 3NF properties. buse cod s BNF 6 either L145 is 7 FD every in to According Superkey or RHS is prime attribute i bta be ct acd. - Rcarbia.de) boe C->d a sbc. superkey. So gida iste In every relation relation is in ECONF LHS is also . ATA PER a-be bre cod

date sold I discount Sales periooCommission rate Car date sold Sales percon