Consider the following database schema (the primary keys of the tables are under
ID: 642077 • Letter: C
Question
Consider the following database schema (the primary keys of the tables are underlined).
Customer (ID, Name, Address)
Movie (ID, Title, Year)
Rental (CustomerID, MovieID, CheckoutDate, DueDate)
Rental(CustomerID) references Customer(ID)
Rental(MovieID) references Movie(ID)
Assume the number of rows in each table are as follows: Customer (500
rows), Movie (1000 rows), and Rental (10000 rows). For each of the SQL
query given below, list the attribute names and state the minimum and
maximum number of possible rows in the query result. If the query result
returns two attributes with the same name, make sure you indicate the
name of the table from which the column is obtained, e.g., Customer.ID or
Movie.ID.
(a)
SELECT *
FROM Customer, Movie;
Attributes:
Number of tuples: Min = Max =
(b)
SELECT C.Name, M.Title
FROM Customer C, Movie M, Rental R
WHERE C.ID = R.CustomerID and M.ID = R.MovieID;
Attributes:
Number of tuples: Min = Max =
(c)
SELECT DISTINCT M.Id, M.Title
FROM Movie M, Rental R
WHERE M.ID = R.MovieID;
Attributes:
Number of tuples: Min = Max =
(d)
SELECT Name
FROM Customer
WHERE ID NOT IN
(SELECT DISTINCT CustomerID FROM Rental );
Attributes:
Number of tuples: Min = Max =
(e)
SELECT DISTINCT C.ID, C.Name
FROM Customer C, Rental M1, Rental M2
WHERE C.ID = R1.CustomerID and C.ID = R2.CustomerID and R1.MovieID <> R2.MovieID;
Attributes:
Number of tuples: Min = Max =
Explanation / Answer
A. Attributes: Customer.ID, Name, Address,Movie.ID.Title, Year
Number of tuples: Min = 1500 Max = 1500
B. Attributes: Customer.Name,Movie.Title
Number of tuples: Min = 00 Max = 500
C. Attributes: Movie. ID , Title
Number of tuples: Min = 00 Max = 500
Assuming that there may be a possibility that no movie are given on rental to any customer
D.Attributes: Customer.Name
Number of tuples: Min = 00 Max = 500
Cause there may be a case where all the customers are taking movies on rent as min = 0,When no customer takes any movie max =500
E.Attributes: Customer.ID,Name
Number of tuples: Min = 00 Max = 500