Relational Model (32 points) Answer all four questions given the following datab
ID: 3747182 • Letter: R
Question
Relational Model (32 points)
Answer all four questions given the following database schema and relational database instance containing three relations:
A. List the primary keys for each table.
B. List the foreign keys for each table.
C. For each table, construct a tuple that does not violate any constraints.
D. Which records can be removed from ITEM without violating any constraints?
Explanation / Answer
Answer:
A)Primary Keys in the Tables:
A Primary key will not allow any duplicate data into the table. The values are unique if a column is set to Primary key.
ITEM.I_ID(Primary Key)
CUSTOMER.C_ID(Primary Key)
These are the two Primary keys available in the Database.
B)Foreign Keys in the Tables.
A Foreign Key is used to interconnect the Related Tables by using Referencing
ITEM.I_ID(Primary Key)------------>PURCHASE.P_CustID(Foreign Key)
CUSTOMER.C_ID(Promary Key)----------->PURCHASE.P_ItemID(Foreign Key)
C)Tuples That does not violate any Constraints.
ITEM Table:
Insert into ITEM values(6,"Barbie Doll",30);
CUSTOMER Table:
Insert into CUSTOMER values(4,"Dr.Sammy","Florida");
PURCHASE Table:
Insert into PURCHASE values(4,2,"1/1/2018");
D)Records that can be removed from ITEM without violating any Constraints.
We can remove the records which are not available in PURCHASE table. There will be restriction in deleting those. In that case I-ID of 2 can be deleted from ITEM Table.
Delete from ITEM where I_ID = '2';