CS 148 Database Systems 1 Spring 2018 Instructor: Prof. P. T. Chung Midterm Exam
ID: 3739054 • Letter: C
Question
CS 148 Database Systems 1 Spring 2018 Instructor: Prof. P. T. Chung Midterm Exam. (Total: 100 Points) Please write your name Hh Hand in your Exam Sheet when you finished the Exam Show intermediate steps to get your final answers, if applied. Problem 1. (Basic Database Concepts) (15 Points) Consider the following relation INVOICE Product ID Umit Prie Order ID Order Datc Customer ID Customer Name (IA). Identify what you think should be various candidate keys, and write up your owi (1.B). Please specify the primary key or assumptions under which cach candidate key would be vald. 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 Secial no, Model, Manufactune, Price) Price) OPTION Serial no.Option m SALE/Salesperson.id Secial no Date, Sale price) First, specify the forelgn 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:
1 and 2 is done, please repost others.
1)
a)
The candidate keys will be:
Ordered_ID should be the candidate key since it is defining the table in a unique way under the assumption that for every order there will be a separate invoice.
Customer_ID and Product_ID can also be the candidate keys, maybe clubbed together under the assumption that for every customer separate invoice is being created.
b)
Primary key would be Order_ID.
2)
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. :)