Consider the following six relations for an order-processing database applicatio

Question

Consider the following six relations for an order-processing database application in a company:

CUSTOMER (Cust#, Cname, City)

ORDER (Order#, Odate, Cust#, Ord_Amt)

ORDER_ITEM (Order#, Item#, Qty)

ITEM (Item#, Unit_price)

SHIPMENT (Order#, Warehouse#, Ship_date)

WAREHOUSE (Warehouse#, City)

Choose and describe(using sql syntax), the appropriate action(reject, cascade, set to NULL or set to default) for each referential constraint:

1. for deletion of a referenced tuple and

2. For update of a primary key attribute value in a referenced tuple

Explanation / Answer

1.

ORDER (Cust#) à CUSTOMER(Cust#)

CASCADE on both DELETE or UPDATE: If a CUSTOMER is deleted or the value of Cust# is updated, the deletion or change is automatically propagated to referencing ORDER tuples.

ORDER (Order#) à ORDER_ITEM(Order#)

CASCADE on both DELETE or UPDATE: If an ORDER_ITEM is deleted or the value of Order# is updated, the deletion or change is automatically propagated to referencing ORDER tuples.

ORDER_ITEM (ITEM#) àITEM(Item#)

CASCADE on both DELETE or UPDATE: If an ITEM is deleted or the value of Item# is updated, the deletion or change is automatically propagated to referencing ORDER_ITEM tuples.

ORDER (Order#) à SHIPMENT(Order#)

REJECT on DELETE: The SHIPMENT tuple should not be deleted because it has an existing ORDER tuple that reference the SHIPMENT.

CASCADE on UPDATE: When the ORDER is updated the change should be propagated automatically to all referencing ORDER tuples.

SHIPMENT (Warehouse#) à WAREHOUSE (Warehouse#)

CASCADE on both DELETE or UPDATE: If an WAREHOUSE is deleted or the value of Warehouse# is updated, the deletion or change is automatically propagated to referencing SHIPMENT tuples.

2.

For update of a primary key attribute the SET TO DEFAULT case is used based on their datatype.

The primary key value cannot be NULL. So, SET TO NULL cannot be used.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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