Review the Orders Relational Model provided below.

The Orders Relational Model

Deliveries (DeliveryID, DeliveryDate, DeliveryCost)

Orders (OrderID, CustID*, DeliveryID*, DeliveryDate*)

Customers (CustID, CustName)

Ordered_Items (OrderID, ItemName, Quantity)

Items (ItemName, Price)

Which relation's can be further corrected to make them fully normalised?

Question

Review the Orders Relational Model provided below.

The Orders Relational Model

Deliveries (DeliveryID, DeliveryDate, DeliveryCost)

Orders (OrderID, CustID*, DeliveryID*, DeliveryDate*)

Customers  (CustID, CustName)

Ordered_Items (OrderID, ItemName, Quantity)

Items (ItemName, Price)

Which relation's can be further corrected to make them fully normalised?

BlackTom AI · Accepted Answer

Question restatement: The task presents a simplified Orders Relational Model and asks which relations can be further corrected to make them fully normalised.

Option A: Ordered_Items and Deliveries
- Deliveries (DeliveryID, DeliveryDate, DeliveryCost) currently seems to be in a single relation with no repeating groups and no transitive dependencies within its attributes. Since DeliveryID is the key and the other attributes describe the delivery, this relation appears already in a simple 3NF form. However, potential concerns could arise if DeliveryDate or DeliveryCost depended on something else not captured here; but with the given schema, there is no clear redundancy or multi-valued dependency inside Deliveries. As a result, this option would only be a candidate if Deliveries exhibited a hidden dependency, which the provided schema does not show.
- Ordered_Items (OrderID, ItemName, Quantity) is a join-like relation between Orders and Items. If the primary key is (OrderID, ItemName), there could be potential transitive issues if ItemName determines Price from Items, creating a concern about redundancy of ItemName and Quantity with respect to an item. In a fully normalised design, one would typically separate item details into the Items relation (ItemName, Price), leaving Ordered_Items with just OrderID, ItemName, Quantity, ensuring that non-key attributes depend only on the key. Given the current structure, there is a plausible normalization improvement by ensuring ItemName is a foreign key to Items and not carrying any derived data in Ordered_Items. This makes Ordered_Items a candidate for normalisation, but Deliveries does not present an obvious normalisation issue under the given schema.

Option B: Only Ordered_Items
- Focusing on Ordered_Items alone as needing correction implies that Deliveries is perfectly normalised in its current form. The concern with Ordered_Items is that it includes ItemName, which already exists in Items with Price, implying a potential transitive dependency: if Price is involved, it is not stored in Ordered_Items but ItemName in Ordered_Items could imply a dependence on Item properties via Items. If the design intends to have Quantity depend only on the composite key (OrderID, ItemName) and ItemName be a foreign key to Items, then Ordered_Items would be the primary candidate for normalisation changes. However, asserting that only Ordered_Items requires correction ignores any subtle issues in Deliveries that could be revealed by a deeper functional dependency analysis, such as whether DeliveryDate depends on DeliveryID alone or if DeliveryCost should be derived from DeliveryDate. Thus this option is partially plausible but incomplete if Deliveries also harbours hidden issues.

Option C: Orders and Ordered_Items
- Orders (OrderID, CustID*, DeliveryID*, DeliveryDate*) shows several starred attributes, suggesting optionality or multiple dependencies. If DeliveryDate is present in Orders but also in Deliveries, there is a duplication of delivery date information across Orders and Deliveries, indicating potential redundancy and a transitive dependency path. A fully normalised design would typically avoid storing DeliveryDate in Orders and instead reference Deliveries via DeliveryID, relying on Deliveries for delivery-related attributes. Similarly, DeliveryID* in Orders hints that a delivery may be optional, which complicates the key structure and could introduce partial dependencies if parts of the composite key determine non-key attributes. Therefore, both Orders and Ordered_Items could benefit from normalisation: Orders to remove DeliveryDate and ensure Delivery specifics are in Deliveries, and Ordered_Items to ensure proper foreign key relationships to Items.

Option D: Ordered_Items and Items
- This option pairs Ordered_Items with Items, which would address the dependency where ItemName in Ordered_Items references ItemName in Items to obtain Price. While this helps remove Price redundancy in Ordered_Items, it does not address potential duplication of DeliveryDate and DeliveryID between Orders and Deliveries, nor does it directly rectify issues within Orders itself, such as carrying DeliveryDate or potential partial dependencies related to DeliveryID in Orders. Hence, focusing solely on Ordered_Items and Items improves the handling of item details but leaves other structural assumptions about Orders and Deliveries unresolved.

Overall assessment:
- The model as given has multiple potential normalization concerns: Orders contains DeliveryDate*, and Delivery information is split across Deliveries and Orders, which can lead to duplication or update anomalies if DeliveryDate is stored in both relations. Also, Ordered_Items contains ItemName and Quantity, with ItemName referencing Items for Price, which is a typical scenario where referencing a separate Items table supports normalization.
- Given these observations, the combination that best targets the identified concerns across the schema is Orders and Ordered_Items, since it directly addresses the potential partial or transitive dependencies involving the Orders table structure and the relationship between orders and their items, while also enabling proper linkage to Items and Deliveries through foreign keys rather than duplicating attributes.
- The other pairings either omit addressing issues in Orders (Option D and some aspects of Option B), fail to comprehensively tackle the cross-table redundancies between Orders and Deliveries (Option A and Option B), or do not simultaneously resolve the primary concerns in both Orders and Ordered_Items (Option C).

In summary, the reasoning suggests that combining Orders and Ordered_Items for normalisation targets the most significant redundancy and dependency issues present in the given schema, by isolating delivery details into Deliveries and item details into Items, while ensuring Orders and Ordered_Items capture their relationships cleanly. Note: ensure that any final implementation enforces that non-key attributes depend only on the key within each relation and that foreign keys appropriately reference the related tables to preserve referential integrity.

IDBF001 Tutorial Quiz 6 |LA006 - Requires Respondus LockDown Browser

View Explanation

Log in for full answers

Similar Questions

What would be the best normalised relational model for the Entity E? Entity: E (A, B, C, D) Functional Dependencies : A, B → C, D C → B

What is the highest Normal Form of the following table? Each Girl can grow more than one Flower and lives at one Address.

What is the highest Normal Form of the following table? Each Owner can own one or more PetTypes. Each PetType may have one or more Owners. Each Owner has one Address and one Phone Number

When Normalisation creates a foreign key, what type of dependency does it correct?

What is the highest Normal Form of the following table ? Each Owner has only one Pet with one PetColour and PetAge. Each Pet has only one Vet.

Using the Entity Relationship Diagram you have drawn on your piece of paper for the Final Subjects Results Table answer the following question. Which answer below shows the Normalised Relational Model of the Final Subjects Results Table.

More Practical Tools for Students Powered by AI Study Helper

Homework AI Solver

Stylized AI Paper Writer

Plagiarism Checker Assistant

Citation AI Academic Writing Tool

In-Class Translation Assistant

AI Note Generator

AI Quiz Answers

Past Exam Questions from University Test Bank

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler