Question

2. Consider the Productinfo table that can be found here. This table is not in BCNF. Note that categoryDescription column is a description field and is considered single valued in this table. ai tea $5.20 | Productinfo productID productName unitPrice categoryID categoryName categoryDescription 1 Chai tea 1 2 Beverages soft drinks, coffee, tea, water, etc 2 black tea $3.50 2 Beverages soft drinks, coffee, tea, water, etc 5 salt $3. 62 1 Condiments seasonings 6 pepper $1.580 1 Condiments seasonings 13 milk 1 litre $4. 59 3 Dairy Products (milk, cream, cheese 14 cream 10% $3. 00 3 Dairy Products milk, cream, cheese 16 rolled oats $10.59 5 Cereals cereal 17 corn flakes $5.25 5Cereals cereal a. In the body of the email, explain why the table is not in BCNF. b. Create and run 5 MS Access queries to Perform a lossless decomposition of Productinfo into two tables Product and Category where both are in BCNF. Name your queries makeProduct and makeCategory. Set the Pks of Product and Category. Name the queries - set ProductPK and setCategory PK Establish RI between Product and Category. Name the query - set ProductFK

the table which is needed to be open is shown in the same picture as productinfo
thank you in advance

Answer 1

a)

The Table ProductInfo is not in BCNF because, It failed 3NF, as it holds transitive Dependency

Reason:

It is 1NF as no multivalued attribute and it is also in 2NF because No partial Dependency.

But it is not in 3NF as below functional depencies CategoryName is dependent on CategoryID and Category_ID is dependent on ProductID, Which is called Transitive Dependency.

productID -> productName

productID -> UnitPrice

productID -> CategoryID

CategoryID -> CategoryName

CategoryID -> CategoryDescription

b)

To bring ProductInfo into 3NF we have to decompose the ProductInfo as two tables like below:

PRODUCT

Product_ID

ProductName

UnitPrice

CategoryID

CATEGORY

CategoryID

CategoryName

CategoryDescription

Create above two tables in MS Access with

Product_ID as primary key in PRODUCT Table

CategoryID as primary key in CATEGORY Table

CategoryID as RI(Referential Integrity) Foreign key in PRODUCT Table

Please do comment for any doubts or queires