Question

Consider the following un-normalized relational table on an online retail store orders and payments information:

An online retail store would like to create a database to keep track of its sales activities. Information recorded in the database supposed to include customer number that identifies each customer, customer’s first name, last name, unique order number of orders a customer made, the date when an order was made, unique product number of products included in orders, product description, sequence number listing the sequence when a product is included in each order as well as the quantity for each product made in each order. The retail store would also like to store the payment information such as a credit card number of credit card that was being charge to, payment date and the amount paid. The online retail store allows customer to pay using any one of the credit cards the customers own, as long as the credit card is valid.

A database designer created the following relational schema:

CUSTORDER(custNum, custFName, custLName, orderNum, orderDate, prodNum, prodDesc, itemNum, quantity, cCardNum, paymentDate, amountPaid)

Decompose the relational schema into the smallest number of relational schemas each one in forth normal form (4NF) and to explain or justify that each schema is in 4NF. To justify that the relational schemas obtained from the decomposition are in 4NF you must find all functional and multivalued dependencies valid in each relational schema, you must find the minimal keys, and then apply the definitions to support your justification. Note, that a relational schema is in 4NF when it is in BCNF and it does not have any nontrivial multivalued dependencies. It means, that first, you have to prove that a schema is in BCNF and later show that it has no nontrivial multivalued dependencies. Please keep in mind that the smallest number of 4NF relational schemas is expected

Answer 1

After studying the whole table, it could be found out that the main foreign key for the database created in 1NF, 2NF, 3NF and 4NF is the cusNum and its relative foreign key is productNum.

For the table product information, the primary foreign key is prodNum, and its relative foreign key is the itemNum.

All the tables are close to each other, which provides excellent help to the company to keep in track of the customer details, order details and product details.

In 1NF :

• All the data items need to be filled separately to maintain the atomicity.
• For this reason, the data in CUSTORDER where two products, orderID or itemnum are present in the table.
• Finally, all the tables have a single attribute and value stored in the column.

In 2NF:

• The first rule of the 2NF where the CUSTORDER table from 1NF is dependent for each candidate key they are,itemnum,Cusnum, prodNum and orderNum
• The 2NF does not provide a restriction on the non-prime to non-prime attribute dependency which needs to be addressed in the 3NF
• The main table separates into three forms they are customer information and product information

In 3NF :

• The database schema had been created for promoting the relational database, which would utilise all the relative normalising principles to lower the duplication of the data, avoiding anomalies in data and simplification of data management.
• For this reason, the table had been separated into three unique forms: product information, customer information and order details.
• The separation of the table will help the online store to avoid duplication in the data, and they are able to track the product and customers orders easily.

In BCNF :

• It is a more improved version of the 3NF which provides a unique character to the database schema.
• The item details had been included in the BCNF to track the name of the products with the product information table.
• The prodDesc could be easily found through the foreign keys prodNum and itemNum.

In 4NF :

• The 4NF offers the level of database normalisation where all the non-trivial multivalued dependencies other than the presence of candidate key.
• The above table is 4NF because of all the table relatable with the help of keys like Itemnum,Productnum ,CustNum and OrderNum.
• All of these primary and foreign keys would help the online store to find out missing information, insert information or delete the information as per the requirement.
• Another table had been separated which is tracking order date, quantity and payment information where the online store could easily track the order details with the help of foreign key orderNum.
• The product details could be found using the prodNum and itemname.

1NF :

CUSTORDER:

2NF :

Customer Information :

Product information :

3NF :

Order Details :

Customer Information :

Product Information :

BCNF :

Order Details :

Customer Information :

Product Information :

Item Information :

4NF :

Order Details :

Tracking order:

Customer Information :

Product Information :

Product Information :