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. 

(b)     Provide a formal definition of a Foreign Key in a relation.

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.

(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.

(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.

(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.

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.

(b)     Write a query that shows the name(s) of the actor(s) who played the Character

(c)     For those actors born in 1978, write a query that shows the number of actors per play title.

(d)     Write a query that shows the title of the plays, which involved the Character ‘Juliet’ or ‘Tybalt’ .

(e)     Show the name(s) of actor(s) who played more than one characters in the same

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.