Consider the following database schema for a video store. State the primary key
ID: 639601 • Letter: C
Question
Consider the following database schema for a video store. State the primary key and referential integrity constraints by underlining the primary key attributes and expressing the referential integrity as
R1(A) references R2(B),
where R1 and R2 are the referencing and referenced relations.
MOVIE (ID, Title, Year)
MOVIE GENRE (MovieID, Genre)
MOVIE LOCATION (MovieID, CopyNumber, AisleNumber)
CUSTOMER (ID, Name, Address, PhoneNumber, AccountBalance)
RENTAL (CustomerID, MovieID, CopyNumber, CheckoutDate, DueDate, ReturnDate)
The schema was created based on the following assumptions:
(a) A movie can have more than one genre.
(b) There can be multiple copies of a movie at the video store.
Explanation / Answer
primary keys are
MOVIE (ID, Title, Year)
MOVIE GENRE (MovieID, Genre)
MOVIE LOCATION (MovieID, CopyNumber, AisleNumber)
CUSTOMER (ID, Name, Address, PhoneNumber, AccountBalance)
RENTAL (CustomerID, MovieID, CopyNumber, CheckoutDate, DueDate, ReturnDate)
constriants
movie id ,customerId, id must be unique
ccount balance nust be integer
date format checking
phone number 10 digit
address not more than 150 words
year 4 letter