Database Design For all the parts below: Consider the following relations for a
ID: 3869445 • Letter: D
Question
Database Design
For all the parts below: Consider the following relations for a database that keeps track of auto sales in a car dealership: 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.Explanation / Answer
Answer for Question 1:
The list of foreign keys in relations are
Point 1) Attribute "Car-VIN" is a foreign key in relation OPTIONAL
EQUIPMENT, references the primary key of relation CAR
Point 2) Attribute "Car-VIN" is a foreign key in relation DEAL
, references the primary key of relation CAR
Point 3) Attribute "Salesperson-ID" is a foreign key in relation DEAL
, references the primary key of relation Salesperson.
Answer for Question 2:
Consider Salesperson have below 3 samples of tuples
Salesperson : ID, Name, Phone
Here ID is primary key which should not be NULL and empty
123, Lal, 87989
324, SAM, 78589
235, HARI,78952
Consider CAR have the below 3 samples of tuples
CAR: CAR-VIN, Model, Manufacturer, Sticker-Price
Here CAR-VIN is primary key which should not be NULL and empty
1, AX5, Toyato, 788
2, AX7, Maruthi, 586
3, AX9, Toyato, 789
Consider OPTINAL_EQUIPMENT have the below 3 samples of tuples
OPTINAL_EQUIPMENT: CAR-VIN, OptionName, Price
Here CAR-VIN is Foreign key which should is primary key of Relation CAR
2, Audio, 787
1, Video, 897
2, Galo, 457
The above list of tubles satisfy the Foreign and Primary Key Relationship
Consider DEAL have the below 3 samples of tuples
DEAL: Salesperson-ID,CAR-VIN, DateofTrans, FinalPrice
Here CAR-VIN is Foreign key which should is primary key of Relation CAR
Here Salesperson-ID is Foreign key which should is primary key of Relation Salesperson
123, 2, Audio, 787
324, 1, Video, 897
123, 2, Galo, 457
The above list of tubles satisfy the Foreign and Primary Key Relationship
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.