Consider the following relations for a database that keeps track of automobile s

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.

Explanation / Answer

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.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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