SALESPERSON (Salesperson-ID, Salesperson-name, Salesperson-phone) CAR (Car-VIN,
ID: 3869474 • Letter: S
Question
SALESPERSON (Salesperson-ID, Salesperson-name, Salesperson-phone)
CAR (Car-VIN, Model, Manufacturer, Sticker-price)
OPTIONAL_EQUIPMENT (Car-VIN, Option-name, Price)
DEAL (Salesperson-ID, Car-VIN, Date-of-transaction, Final-price)
b. Please list all the foreign keys in relations that have them in this schema. For each one, please state the foreign key name, the relation in which it is the primary key, and the relation in which it is a foreign key. A sample statement can be: “Attribute X which is a foreign key in relation K, references the primary key of relation J.”
c. Please populate the relations with three sample tuples for each relation. These sample tuples should conform to the constraints of the relational data model and the referential integrity constraints, as defined by you in (1b). Please state clearly which tuples are for which relation. Please answer this part in the form of four tables, one for each relation. For each table, please include the table name and column names besides the three tuples.
d. Please create a new and complete record to be inserted into the DEAL table created for (c). This insertion is one that WILL VIOLATE the referential integrity constraints. You do not need to add the record to the table drawn for (c). Just indicate the new record here, in the format, such as a sample here: (“S9898”, “879398VBY7678”, “12/05/2013”, 23000).
e. Please create a new and complete record to be inserted into the OPTIONAL_EQUIPMENT table created for (c). This insertion is one that will NOT violate the referential integrity constraints. This question is independent of (d). Similarly, you do not need to add this new record to the table for (c). Just indicate the new record clearly here.
Explanation / Answer
given Relations are
SALESPERSON (Salesperson-ID, Salesperson-name, Salesperson-phone)
CAR (Car-VIN, Model, Manufacturer, Sticker-price)
OPTIONAL_EQUIPMENT (Car-VIN, Option-name, Price)
DEAL (Salesperson-ID, Car-VIN, Date-of-transaction, Final-price)
b):
they are the two foreign key attributes in the given four relations. they are
1)Salesperson_ID
"Attribute Salesperson_ID which is a foreign key in relation DEAL references the primary key of relation SALESPERSON"
2)Car-VIN
"Attribute Car-VIN which is a foreign key in relation OPTIONAL_EQUIPMENT references the primary key of relation CAR"
C)
D)
creating a new record means to insert the new record to the table with help of insert command.
("S101","1INAM3566783B0057","22/02/2017",5000) into the table DEAL
the above insertion violates the foreign key integrity (like the S101 person was not there in the salesperson list)
e)
inserting the new record to the relation OPTIONAL_EQUIPMENT. without violating the referential integrity as follows
( "1INAM3566783B0057","battery",55)
the above record follows the reference key from relation CAR.
SALESPERSON Salesperson-ID Salesperson-name Salesperson-phone S100 JOHN N 9393939393 S105 SUNNY K 9995553322 S108 KALYAN 9888787787Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.