Computer Science – 8.1 Database Concepts | e-Consult

This set of tables is not in 3NF. The primary violation occurs in the Orders table. Specifically, the CustomerID in the Orders table is a non-key attribute that is functionally dependent on the primary key (OrderID). Since CustomerID is not part of the primary key, and it's not fully functionally dependent on the entire primary key (OrderID), this violates 3NF.

Furthermore, the Orders table also contains a non-key attribute, TotalAmount, which is functionally dependent on the combination of OrderID and Quantity and UnitPrice from the OrderItems table. This is another violation of 3NF. The OrderItems table contains a non-key attribute, ProductName, which is functionally dependent on the ProductID in the Products table. This is a violation of 3NF as well.

To achieve 3NF, the tables should be normalized as follows:

• Customers (CustomerID, CustomerName, CustomerEmail, City) - Remains in 3NF
• Orders (OrderID, CustomerID, OrderDate) - TotalAmount should be calculated from OrderItems.
• OrderItems (OrderItemID, OrderID, ProductID, Quantity, UnitPrice) - Remains in 3NF
• Products (ProductID, ProductName, Description, Price) - Remains in 3NF

A possible solution would be to create a new table, OrderTotals, to store the TotalAmount for each order:

This eliminates the redundancy and ensures that the TotalAmount is calculated from the OrderItems table, which is the correct approach.