Friday 26 April 2019
2.00 pm – 3.30 pm
(Duration: 1 hour 30 minutes)
DEGREES OF MSc, MSci, MEng, BEng, BSc,MA and MA (Social Sciences)
Database Theory and Applications M
(Answer All Questions)
This examination paper is worth a total of 60 marks
The use of a calculator is not permitted in this examination
INSTRUCTIONS TO INVIGILATORS
Please collect all exam question papers and exam
answer scripts and retain for school to collect.
Candidates must not remove exam question papers.
Summer Diet 1 Continued Overleaf/
Part A: Relational Modelling & Normalisation [20 Marks]
1. (a) Provide a formal definition of the Super Key and the Primary Key in a relation R.
[2]
(b) Provide a formal definition of a Foreign Key in a relation.
[2]
(c) Can a Foreign Key be NULL? Explain your answer and provide an example.
[2]
2. (a) Consider that the Functional Dependency (FD): A → B holds true in a relation
R(A, B) and this is the only known FD in relation R. When could the FD: B → A
hold true in the relation R? Explain your answer.
[2]
(b) Consider the relation R(A, B, C, D, E) with the following FDs:
• FD1: A → E
• FD2: B → D
• FD3: {A, B} → C
(i) Which are the possible candidate keys in the relation R? Explain your answer.
[2]
(ii) Which is the maximum Normal Form (NF) of the relation R? Explain your answer.
[2]
(iii) Decompose the relation R into a set of relations in Boyce-Codd NF (BCNF) and
show the process of this decomposition in your answer.
[4]
(c) Explain why the relation R(A, B, C, D, E) with the FDs: A → {B, E}, C → D is
not in the BCNF. Normalize the relation to BCNF and mention the primary and
foreign keys in the possibly new relations.
[4]
Summer Diet 2 Continued Overleaf/
Part B: SQL [20 Marks]
3. Consider the following relational schema:
Actor(ActorID, Name, YearBorn)
Play(PlayID, Title, YearWritten)
Role(ActorID, Character, PlayID)
Actor is a table of actors storing their names and the years they were born. Each
actor has a unique ActorID. Play is a table of theatre plays with a specific title and
year of written. The relation Role records which actors have performed which roles
(Characters) in which plays. It is possible for a single actor to play more than one
character in the same play.
(a) For each relation, identify the Primary Key and Foreign Keys (if any) and explain
your answer.
[1]
(b) Write a query that shows the name(s) of the actor(s) who played the Character
‘Elektra’.
[1]
(c) For those actors born in 1978, write a query that shows the number of actors per
play title.
[2]
(d) Write a query that shows the title of the plays, which involved the Character ‘Juliet’
or ‘Tybalt’.
[2]
(e) Show the name(s) of actor(s) who played more than one characters in the same
play.
[5]
4. Consider the relation Pupil(NINO, SchoolID, GPA), where NINO is the National
Insurance Number (NINO) of the pupil, which is the Primary Key in the relation,
GPA is the Grade Point Average, and SchoolID is the ID of the school.
(a) Write a query that lists the average GPA of those pupils in the school with ID = 1.
[2]
(b) Write a query that lists the school IDs of the schools having more than 200 pupils.
[2]
(c) For each school with more than 200 pupils, count the total number of pupils whose
GPA is greater than 10.
[5]
Summer Diet 3 /END
Part C: Relational Algebra & Heuristic Optimization [20 Marks]
5. Consider the relational schema, where the Primary Keys are underlined:
Supplier(SID, name, address)
Product(PID, color)
Catalog(SID, PID, cost)
with Catalog.SID references Supplier.SID and Catalog.PID references Product.PID.
Express in Relational Algebra a query that lists the SIDs of suppliers who supply
blue products.
[5]
6. Assume the relations: Employee(SSN, SUPER_SSN, Name) and
Department(DNO, MGR_SSN), where SSN is the Social Security Number (SSN)
of the employee (Primary Key in relation Employee), SUPER_SSN is the SSN of
the employee’s supervisor, DNO is the unique identifier of the department (Primary
Key in relation Department) and MGR_SSN corresponds to the SSN of an
employee who is manager at a department. Consider the following query:
SQL1:
SELECT E1.NAME
FROM EMPLOYEE E1, EMPLOYEE E2, DEPARTMENT D
WHERE E1.SUPER_SSN = E2.SSN
AND E2.SSN = D.MGR_SSN AND D.DNO = 5
(a) What does the SQL1 query return?
[3]
(b) Write the SQL1 query in a Relational Algebra Expression.
[3]
(c) Draw the canonical Relation Algebra Tree of the SQL1 query without applying
heuristic optimization rules.
[3]
(d) Draw the optimal Relation Algebra Tree of the SQL1 query after applying
heuristic optimization rules. Provide a brief explanation on the heuristic rules you
adopted.
[6]