Question
USE the following Database schema for the question: BOLDED are primary keys Movie( title, year, director, budget, earnings) Actor( stagename , realname, birthyear) ActedIn (stagename,
USE the following Database schema for the question: BOLDED are primary keys
Movie(title, year, director, budget, earnings)
Actor(stagename, realname, birthyear)
ActedIn(stagename, title, year, pay)
CanWorkWith(stagename, director)
----
Question 1 [8 marks] For each of the following pairs of queries, determine whether or not the queries are equivalent. You must explain your answer. Think carefully to be equivalent, the queries must provide exactly the same answer for every possible set of valid data.
a) [2 marks]
(i) stagename(pay > earnings - budget (ActedIn Movie))
(ii) SELECT A.stagename FROM ActedIn A, Movie M WHERE A.title = M.title AND A.year = M.year AND A.pay > M.earnings M.budget
b)
(i) (M1(title t1, year y1, director d1, budget b1, earnings e1), Movie)
(M2(title t2, year y2, director d2, budget b2, earnings e2), Movie)
t1,y1 (M1 e1 e2 M2) (ii) (M1(title t1, year y1, director d1, budget b1, earnings e1), Movie)
(M2(title t2, year y2, director d2, budget b2, earnings e2), Movie)
t1,y1 M1 t1,y1 (M1 e1 > e2 M2)
c) (i) SELECT C.stagename, COUNT(C.director) FROM CanWorkWith C WHERE C.stagename IN ( SELECT A.stagename FROM Actor A ) GROUP BY C.stagename
(ii) SELECT A.stagename, COUNT(C.director) FROM Actor A, CanWorkWith C WHERE A.stagename = C.stagename GROUP BY A.stagename
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started