Question

Consider the schemas and instances below. #prod should be prodid #dep should be depid. Schemas - Produc #prod, pane, rioe) - Depot(#dep,addr, volume) - Stock(#prod, #cdep,quantity) Instances Product #prod pl p2 p3 price pname 25 tap tv ver Depot #dep ad dl d2 d4 volume New Yor Syracuse New York 2000 9000 6000 Stoek #dep | quantity dl d2 d4 #prod pl pl pl 1000 -100 1200 p d1 di d2 E00 CO 2000 The primary key of product is #prod . The primary key primary key of stock is #prod, #dep f depot is #dep. The (composite) #prod in stock is a foreign key referring the primary key #prod in product. #dep in stock is a foreign key referring the primary key #dep in depot. EE32282

Answer 1

Exercise 1

Stock(#prod, #dep, pname, quantity)

Functional dependencies

#prod, #dep ---> quantity  

It means #prod and #dep can functionally determine quantity.

To find the quantity of the product in stock, we need its product id as well as depot id (or) with both product id and depot id we can find the stock available.

#prod ---> pname

It means #prod can functionally determine pname.

To find the pname of the product in stock, we need its product id (or) given product id we can find its name.

Exercise 2

Plane(#plane, type, constructor, capacity, owner)

Functional dependencies

#plane ----> type, constructor, capacity, owner

It means #plane can functionally determine type, constructor, capacity, owner. Plane number will give the plane's type, constructor, capacity and owner (or) to find a particular plane's type or constructor or capacity or owner, we need its plane id.

Exercise 3

F = {XZ --> ZYB, YA --> GC, C --> W, B --> G, XZ --> G}

attribute closure of XZA

(XZA)⁺ --> XZAYBGCW

Is the dependency XZA --> YB implied by F?

Yes, it is implied. XZ --> ZYB => XZ --> YB (Decomposition)

=> XZA --> YBA (Axiom of augmentation)

=> XZA --> YB   (Decomposition)

Exercise 4

Is stock in 3NF?

For a relation to be in 3NF, it should be in 2NF and should not have transitive dependencies.

#prod, #dep ---> pname, quantity  

#prod ---> pname

The relation in not in 2NF as there is partial dependency.

=> not in 3NF

Is plane in 3NF?

For a relation to be in 3NF, it should be in 2NF and should not have transitive dependencies.

#plane ----> type, constructor, capacity, owner

It is in 2NF and there is no transitive dependencies

=> in 3NF

Is stock in BCNF?

For a relation to be in BCNF, it should be in 3NF and should not have transitive dependencies.

#prod, #dep ---> pname, quantity  

#prod ---> pname

The relation in not in 2NF

=> not in BCNF

Exercise 5

apply BCNF decomposition algorithm on Stock

make two tables T1(#prod, #dep, quantity) andT2 (#prod, pname)

T1 : #prod, #dep ---> pname, quantity candidate keys - (#prod, #dep)

T2 : #prod ---> pname   candidate keys - #prod

Both the tables are in 3NF now. To be in BCNF the determinant should be a candidate key which is it in both.

=> in BCNF