Consider the following six relations for an order-processing database applicatio
ID: 653533 • Letter: C
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.