Management Database Systems (COP 5725)
(Spring 2012)
Instructor
Dr. Markus Schneider
TA
Nam Nguyen
Exam 2
Solutions
Name:
UFID:
Email Address:
Pledge (Must be signed according to UF Honor Code)
On my honor, I have neither given nor received unauthorized aid in doing this
assignment.
_______________________________________________
Signature
For scoring use only:
Question 1
Question 2
Question 3
Question 4
Total
Maximum
20
25
35
20
100
1
Received
Question 1 (SQL and Relational Algebra)
[20 points]
(a) Let R(A:D1, B:D2, E:D3) and S(C:D1, H:D4) be two table schemas in which A, B,
C, E, and H are attributes, D1, D2, D3, and D4 are data types, and F is a condition.
Provide equivalent (standard) Relational Algebra expressions for the following two SQL
queries.
1. [5 points]
SELECT DISTINCT A
FROM
R
WHERE A IN (SELECT DISTINCT C
FROM S
WHERE F)
Solution:
The only Relational Algebra operator that allows us to bring two tables in relationship to
each other is the Cartesian Product operator. An equivalent Relational Algebra expression
is: A(A=C(R F(S)))
2. [8 points]
SELECT DISTINCT A
FROM
R
WHERE A > ALL(SELECT C
FROM S
WHERE F)
Solution:
The strategy is to form a Cartesian Product between the A-Values in R and the selected
C-values in S and then to select those tuples of the Cartesian Product for which A C
holds. We then project on A. This means that we get all A-values from R that are less or
equal to some C-value from S. A-values from R that are larger than all C-values from S
are not part of the intermediate result. To get them we subtract the intermediate result
from the projection of R on A. We obtain: A(R) A(AC(A(R) C(F(S))))
(b) [7 points] The Cartesian Product is one of the five basic Relational Algebra
operators. The Division (Quotient) operator is a derived relational algebra operator. It is
considered to be the most challenging of all Relational Algebra operators. Is the
statement The Division operator is the opposite of the Cartesian Product operator
correct or incorrect? Please argue in either direction.
Solution:
The statement is correct. Consider the following tables R, S, and T given as
2
Obviously, T = R S holds. All tuples of R are concatenated with all tuples of S.
Therefore, we obtain
3
Question 2 (QBE)
[25 points]
Consider the following relational schemas of the COMPANY database:
EMPLOYEE (SSN, Name, Bdate, Address, Salary, DeptId)
DEPARTMENT (DeptId, Dname, Office, Mng-SSN)
PROJECT (Code, Name, Budget, DeptId)
JOIN (ESSN, PCode, StartDate)
EMP-DEPENDENT (ESSN, Dependent-Name, Bdate, Relationship)
(1) Find the social security numbers of employees who have joined project number 3 or
project number 4. [3 points]
JOIN ESSN PCode StartDate
P._x
3
P._y
4
(2) Find the social security numbers of employees who take part in both project number 3
and project number 4. [4 points]
JOIN ESSN PCode StartDate
P._x
3
_x
4
(3) Find the names of the employees who do not join any project. [4 points]
EMPLOYEE SSN Name Bdate Address Salary DeptId
_y
P.
JOIN ESSN PCode StartDate
_y
(4) Find the employees name, dependents name and their relationship for all the
employees in department number 1. Show the result in a user-defined relation. [5 points]
EMPLOYEE SSN Name Bdate Address
Salary DeptId
_x
_y
EMP-DEPENDENT
1
ESSN Dependent-Name Bdate Relationship
_x
_z
_t
RESULTS Name Dependent-Name Relationship
P.
_y
_z
_t
4
(5) List SSN, Name, Bdate of all employees in department number 1. Order the list first
by the values of the attribute Name in ascending order then by the values of the attribute
Bdate in descending [4 order. points]
EMPLOYEE SSN Name
Bdate
Address Salary DeptId
P._x P.AO(1)._y P.DO(2)._z
1
(6) Find the names of the employees who join every project. [5 points]
EMPLOYEE SSN Name Bdate Address Salary DeptId
_y
P.
JOIN ESSN PCode
G._y
StartDate
CNT.UNQ._z
PROJECT Code Name Budget DeptId
_x
CONDITIONS
CNT.UNQ._z = CNT.UNQ._x
5
Question 3 (SQL)
[35 Points]
Consider the following schema:
Movie (movieID, director_PersonID, name, budget, gross, runtime, year)
Person (personID, name, yearofBirth)
Acts (personID, movieID, salary)
Write SQL queries for the following questions. [7 points each]
(a) Find the movies that are directed by Krzysztof Kieslowski and have positive
revenue.
(b) Find the names of the directors who have directed more short films (<60 minutes)
than full length movies (60 minutes).
(c) Find the movies which have spent less than 50% of their budget for their actors and
actresses.
(d) Find the movies with their directors who also act in that movie.
(e) Find the movies which were directed by the oldest directors at the time a movie was
shot.
Solution:
(a)
select m.Name
from
Movie as m, Person as p
where p.Name=' Krzysztof Kieslowski' and p.PersonID=m.Director_PersonID and
m.Gross>m.Budget
(b)
select
from
where
T1.pname
(select
p.PersonID as personid, p.Name as pname,
count(m.MovieID) as no_short_movies
from
Movie as m, Person as p
where
m.Runtime<60 and m.Director_PersonID=p.PersonID
group by p.PersonID, p.Name) as T1,
(select
p.PersonID as personid, count(m.MovieID) as no_long_movies
from
Movie as m, Person as p
where
m.Runtime>=60 and m.Director_PersonID=p.PersonID
group by p.PersonID) as T2
T1.personid = T2.personid and T1.no_short_movies>T2.no_long_movies
6
(c)
select Name
from
Movie,
(select
MovieID, sum(Salary) as SumActorsSal
from
Acts
group by MovieID) as SalPerMovie
where Movie.MovieID=SalPerMovie.MovieID and SumActorsSal<0.5*Budget
(d)
select distinct (m.Name, p.Name)
from
Movie as m, Person as p, Acts as a
where m.MovieId=a.MovieId and a.PersonId=p.PersonId and
p.PersonId =m.Director_PersonID
(e)
select m.Name
from
Movie as m, Person as p
where m.Director_PersonID=p.PersonID
m.year-p.YearofBirth = (select
from
where
and
max(year-YearofBirth)
Movie, Person
Director_PersonID=PersonID)
7
Question 4 (Relational Algebra and SQL) [20 Points]
Consider the following relational schemas:
Employee (person_name, street,city)
Company (company_name, city)
Works (person_name, company_name, salary)
Manages (person_name, manager_name)
Write the following queries in Relational Algebra and SQL.
1. Find the names of all managers who earn more than $10,000. [4 points]
select distinct w.person_name
from works w, manages m
where w.person_name=m.manager_name and w.salary>10000
2. Find the names of employees who work for some company in Boston. [4 points]
"
"
select distinct w.person_name
from works w, company c
where w.company_name=c.company_name and c.city=Boston
3. Find the streets of employees who work for all companies in Boston. [8 points]
select
from
where
and
,
"
"
distinct e.street
employee e, works w
e.person_name=w.person_name
not exists (( select company_name from company
where city=Boston)
except
(select company_name from works w1
where w1.person_name=w.person_name))
8
4. Find the names of managers who manage all employees working for Company A.
[4 points]
,
"A"
select m.manager_name
from manages m
where not exists((select person_name from works
where company_name=A)
except
(select m2.person_name from manages m2
where m.manager_name=m2.manager_name))
9