Tables : Customer ( CustomerRef , CustomerFirstName , CustomerSurname , Customer

Question

Tables :

Customer (CustomerRef , CustomerFirstName , CustomerSurname , CustomerAddress , CustomerTel)

OrderItemNo(OderItemNo, OderNo, PartNo, OrderQty)

Orders (OrderNo, CustomerRef, OrderDate)

PartTypes (PartTypeCode, PartTypeDescription)

PurchaseOder(PONo , PODate , PartNo , POQty)

Supplier(SupplierRef, SupplierName, SupplierAddress, SupplierContact, SupplierTel)

Part(PartNo, PartName, PartUnit, SupplierRef, StockLevel, MinStockLevel, CosttPrice, SalePrice, Location, PartType)

Q : normalise the relation to 1NF to BCNF, 2NF, 3NF . Be sure to indicate the FDs you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not sufficient.

Explanation / Answer

In 1NF, an attribute (column) of a table

All of the tables are having a primary key, and none of the attribute will be repeating.

Hence, all tables are in 1NF (Provided address and telephone numbers are not repeating for Supplier and Customer)

Functional dependencies:

CustomerRef -> (CustomerFirstName , CustomerSurname)

CustomerRef -> CustomerAddress

CustomerRef -> CustomerTel

OderItemNo -> (OderNo, PartNo)

(OderNo, PartNo) -> OrderQty

OderItemNo -> OrderQty

OrderNo -> (CustomerRef, OrderDate)

PartTypeCode -> PartTypeDescription

PONo -> (PODate , PartNo , POQty)

SupplierRef -> SupplierName

SupplierRef -> SupplierAddress

SupplierRef -> SupplierContact

SupplierRef -> SupplierTel

PartNo -> PartName, PartUnit, SupplierRef, StockLevel, MinStockLevel, CosttPrice, SalePrice, Location, PartType

2NF: a relation must be in first normal form and relation must not contain any partial dependency

Here, Part table is not in 2NF

Splitting Part tables

Updated functional dependencies:

PartNo -> PartName, PartUnit, PartType

PartNo,Location -> SupplierRef, StockLevel, MinStockLevel, CosttPrice, SalePrice

Tables:

Part(PartNo, PartName, PartUnit, PartType )

PartDetails(PartNo,Location,SupplierRef, StockLevel, MinStockLevel, CosttPrice, SalePrice)

3NF: For a relation to be in 3NF:

Here OrderItemNo is not in 3NF

Hence splitting the table to hold the functional dependencies

OderItemNo -> (OderNo, PartNo)

(OderNo, PartNo) -> OrderQty

OderItemNo -> OrderQty

Tables:

OderItemNo(OderItemNo ,OderNo, PartNo)

OrderQty(OderItemNo,OrderQty)

Now all tables

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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