Question

database managament systems

Weight Price Product Name Milk Amount bought 2 1 lt 5 Coffee 100 gr Customer Customer Bar ID Name Code 111 Asli 112233 Kaya Asli 223344 Kaya 122 Melis 112233 Çelik 133 Mesut 223344 Demir 6 Milk 1 lt 5 4 Coffee 100 gr 6 2 a) List all the functional dependencies for the above table. b) What is the primary key of the above table? Why? Explain. c) Is the above table in BCNF? Explain your answer. If it is not in BCNF, convert it into BCNF.

Answer 1

a) The functional dependencies for the above tableare Customerld CustomerName BarCode ProductName BarCode Weight BarCode Price Custonerid, Barcode Amountbought That is, CustomerName is functionally dependent on Customerld, Product Name Price and weight dependent on Barcode: Amount bought is dependent on Customerld and Barcode b) The primary key for this table will be a composite primary key. No single attribute uniquely identifies each row. Customerld and BarCode both are needed together to identify each row uniquely. So, the Primary key is (Customerld, BarCode) c) The given table is not in BCNF. For being in BCNF the relation should already be in 3NF. This condition does not satisfy here. We clearly notice from the functional dependencies that all the attributes are not fully functionally dependent on primary key. For example, CustomerName dependent on Customerld which is a subset of the Primary key (Customerld, BarCode) A relation schema R is in BCNF if and only for any functional dependencies A →Bin R the following holds: A B is a trivial functional dependency (Bis a subset of A) A is a superkey in R

To bring the table in BCNF we decompose it in the following way

Customer Table

CustomerId	CustomerName

Product Table

BarCode	ProductName	Weight	Price

Sales Table

CustomerName	BarCode	Amount_bought