Question

Consider the following relations for a database that keeps track of automobile sales in a car delarship (OPTION refers to some optional equipment installed on an automobile); CAR (Serialno, Model, Manufacturer, Price) OPTION (Serialno, optionname, Price) SALE (Salesperson_id, Serialno, Date, Sale_Price) SALESPERSON (Salespersn_id, Name, Phone)

First specify the foreign keys for this schema, stating any assumptions you make. Next, populate the relations with a few sample tuples, and then give an example of an insertion in the SALE and SALESPERSON relations that violates the referential integrity constraints and of another insertion that does not.

Answer 1

The foreign ids are Salesperson_id and Serialno in SALE table.

VIOLATING INSERTION:-

INSERT INTO SALESPERSON(Salesperson_id,'Name','Phone') VALUES(1,'Rajesh','9832511251');

INSERT INTO SALE(Salesperson_id,Serialno,'Date','Sale_price') VALUES(2,1,'2015-02-12','5000');

NON-VIOLATING INSERTION:-

INSERT INTO SALESPERSON(Salesperson_id,'Name','Phone') VALUES(1,'Rajesh','9832511251');

INSERT INTO SALE(Salesperson_id,Serialno,'Date','Sale_price') VALUES(1,1,'2015-02-12','5000');

REASON:- The reason is that the foreign key value must be a subset of the primary key values. The primary key values set can be either same as the foreign key values set or a superset of it for referential integrity constraints to hold true.