Consider the following six relations for an order-processing database applicatio
ID: 3876278 • 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#, OrdAmt)
ORDERITEM (Order#, Item#, Qty)
ITEM (Item#, Unitprice)
SHIPMENT (Order#, Warehouse#, Shipdate)
WAREHOUSE (Warehouse#, City)
Here, OrdAmt refers to total dollar amount of an order; Odate is the
date the order was placed; Shipdate is the date an order is shipped
from the warehouse. Assume that an order can be shipped from several
warehouses
Give an update operation to a database state that
violates an integrity constraint that is neither of the previous
kinds of constraints, stating any assumptions you make.
Explanation / Answer
Given Six relations for an order-processing database are
CUSTOMER (Cust#, Cname, City)
-- Here Cust# (customer number) is the primary key. That means it should adire to unique and not null constraints. In simply word we can say primary key can not contain any duplicate value or null value.
ORDER (Order#, Odate, Cust#, OrdAmt)
-- Here Order# (order number) is the primary key. And Cust# is the foreign key, which is referencing cust# of CUSTOMER relation. Foreign key can contain duplicate values but strictly it should contain values which is already present in the primary key which it is referencing. That means cust# of ORDER can only contain values which is present in cust# of CUSTOMER relation. Also null value is not allowed in foreign key.
ORDERITEM (Order#, Item#, Qty)
--Here order# and item# both are foreign key which is referencing ORDER and ITEM relation respectively.
ITEM (Item#, Unitprice)
-- Here Item# (item number) is the primary key.
SHIPMENT (Order#, Warehouse#, Shipdate)
--Here order# and Warehouse# both are foreign key which is referencing ORDER and WAREHOUSE relation respectively.
WAREHOUSE (Warehouse#, City)
-- Here Warehouse# (Warehouse number) is the primary key.
Give an update operation to a database state that violates an integrity constraint that is neither of the previous kinds of constraints, stating any assumptions you make.
Explanation:
In order to violate any constraints we have below 2 kind of operations
A) Violating primary key constraint:
a) Inserting any records with null value in primary key field/attribute.
example-insert into customer(cname,ccity) values('Danial','New York')
as we are not providing cust id value it will try to insert null and the statement will fail.
b) Inserting any records with a primary key value which is already present in the relation.
example-insert into customer(cust,cname,ccity) values(1001,'Danial','New York') . This statement will violate primary key constraint if already a customer available with customer id 1001.
B) Violating foreign key constraint:
a) Inserting any records with null value in foreign key field/attribute.
example-insert into ORDER(Order, Odate, OrdAmt) values(5656,'12/4/2017',456)
as we are not providing order id value it will try to insert null and the statement will fail.
b) example-insert into ORDER(Order, Odate,cust, OrdAmt) values(5656,'12/4/2017',1005,456). If customer 1005 is not present in customer relation then this sttement will violate foreign key constraint or referencial integrity constraints.