Problem 2. (Data Integrity) (20 Points) Consider the following relations for a d

Question

Problem 2. (Data Integrity) (20 Points) Consider the following relations for a database that keeps track of automo- bile sales in a car dealership (OPTION refers to some optional equipment installed on an automobile: CAR(Serial no, Model, Manufacturer, Price) OPTION Serial no, Option name, Price) SALE n id. SerialL no, Date, Sale_price) SALESPERSON Salesperson 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

Solution:

The foreign keys are:

Serial_no in OPTION table.

Salesperson_id in SALE table

Suppose the SALESPERSON table already contains

1 Peter 123

2 Parker 354

3 Tony 456

4 Stark 567

So now if we insert below details in the SALE table there will be a insersion violation because of insertion anomaly.

7 33 22/03/2018 100

since there is no Salesperson_id as 7 in the SALESPERSON table, this means that this is a violation

and if we enter

1 78 23/03/2018 500

then there is no issue while inserting.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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