3. Consider the following schema for a movie database (the primary keys of the tables are...

Transcribed Image Text:

3. Consider the following schema for a movie database (the primary keys of the tables are underlined). Foreign key constraints are also listed under the schema of each table. Person (ID, FirstName, LastName, Birthdate) Movie (ID, Title, Release Year, Director) Movie(Director) references Person(ID) Acts (ActorID, MovieID) Acts(ActorID) references Person (ID) Acts(MovieID) references Movie(ID) 4. For this question, you will use the same database schema as in the previous question. Assume that the Director attribute in the Movie table is not null (i.e., every movie must have a director). Assume the number of rows in each table are as follows: Person (200 rows), Movie (150 rows), and Acts (1000 rows). For each of the SQL query given below: • Explain (in plain English) what the query tries to return. How many columns are there in the query result? What is the minimum and maximum number of possible rows that may appear in the query result? (a) SELECT Title FROM Movie WHERE Release Year = 2017; (b) SELECT P.First Name, P.LastName FROM Person P, Movie M; WHERE P.ID M.Director AND M.Title= 'Star Wars'; (c) SELECT P.LastName, P.FirstName FROM Person P, Movie M, Acts A WHERE P.ID = A.ActorID and M.ID = A.MovieID and M.Director = P.ID; (d) SELECT DISTINCT P.FirstName, P.LastName FROM Person P, Acts A1, Acts A2 WHERE P.ID = A1.ActorID and P.ID = A2.ActorID and A1.MovieID A2.MovielD; 3. Consider the following schema for a movie database (the primary keys of the tables are underlined). Foreign key constraints are also listed under the schema of each table. Person (ID, FirstName, LastName, Birthdate) Movie (ID, Title, Release Year, Director) Movie(Director) references Person(ID) Acts (ActorID, MovieID) Acts(ActorID) references Person (ID) Acts(MovieID) references Movie(ID) 4. For this question, you will use the same database schema as in the previous question. Assume that the Director attribute in the Movie table is not null (i.e., every movie must have a director). Assume the number of rows in each table are as follows: Person (200 rows), Movie (150 rows), and Acts (1000 rows). For each of the SQL query given below: • Explain (in plain English) what the query tries to return. How many columns are there in the query result? What is the minimum and maximum number of possible rows that may appear in the query result? (a) SELECT Title FROM Movie WHERE Release Year = 2017; (b) SELECT P.First Name, P.LastName FROM Person P, Movie M; WHERE P.ID M.Director AND M.Title= 'Star Wars'; (c) SELECT P.LastName, P.FirstName FROM Person P, Movie M, Acts A WHERE P.ID = A.ActorID and M.ID = A.MovieID and M.Director = P.ID; (d) SELECT DISTINCT P.FirstName, P.LastName FROM Person P, Acts A1, Acts A2 WHERE P.ID = A1.ActorID and P.ID = A2.ActorID and A1.MovieID A2.MovielD;

Answer rating: 100% (QA)

A It will printDisplay the titles from the Movie table which is Release Year is 2017 It will Display ... View the full answer