The following is a relational schema consisting of five tables. Primary keys in

Question

The following is a relational schema consisting of five tables. Primary keys in each table are in bold CUSTOMER (CustomerlID, FName, LName, Address, Phone, DoB) RENTAL (CustomerID, MovieID, Date_Rented, Date_Due) MOVIE (MovieID, Title, Audience, Duration, DirectorID) PRODUCER (CompanyID, CompanyName, Country) Write SQL commands to do the following: a) Create RENTAL table. Enforce data integrity on this table as well. b) Enter the following record into CUSTOMER table. [3 marks] CustomerID FName LNameAddressPhone DoB 820 AhmadAli MMU 06-255300 24 May 1990 [1 mark] c) Add a new column named Category to MOVIE table. [1 mark]

Explanation / Answer

a)

CREATE TABLE RENTAL (

CustomerId int NOT NULL,

MovieId int NOT NULL,

Date_rented Date,

Date_due Date,

PRIMARY KEY (CustomerId, MovieId),

FOREIGN KEY (CustomerId) REFERENCES CUSTOMER(CustomerId),

FOREIGN KEY (MovieId) REFERENCES MOVIE(MovieId)

);

b)

insert into CUSTOMER VALUES(820, 'Ahmad', 'Ali', 'MMU', '06-255300', '24-05-1990');

c)

alter TABLE MOVIE

ADD Category VARCHAR(200);

d)

update CUSTOMER

set Phone='06-242000'

where FName='Ahmad' and LName='Ali'

e)

select MOVIE.Title

from MOVIE, CUSTOMER, RENTAL

where MOVIE.MovieId = RENTAL.MovieId

and RENTAL.CustomerId = CUSTOMER.CustomerId

and CUSTOMER.FName='Ahmad' and CUSTOMER.LName='Ali'

f)

select MOVIE.Title, CUSTOMER.FName

from MOVIE, CUSTOMER, RENTAL

where MOVIE.MovieId = RENTAL.MovieId

and RENTAL.CustomerId = CUSTOMER.CustomerId

and MONTH(RENTAL.Date_rented) = 1 AND YEAR(RENTAL.Date_rented) = 2014

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.