Database Theory and Application M
COMPSCI 5076
(Answer All 4 Questions)
This examination paper is an open book, online
assessment and is worth a total of 60 marks
Summer Diet 1 Continued Overleaf/
Part A: Relational Modelling & Normalisation [20 Marks]
Question 1.
Consider the relation R(A, B, C, D, E) with the following Functional Dependencies (FDs):
FD1: {A, B, C, D} E
FD2: E {B, C, D}
(a) Identify the super key(s) of the relation R and find the candidate key. Explain your
answer.
[4]
(b) Decompose the relation R into a set of relations in BCNF and identify their Primary
and Foreign Keys. Explain your answer.
[3]
Question 2.
Consider the relation Employee(NumberPlate, GUID, Surname), where it stores information
about employees of the University of Glasgow. The NumberPlate is a unique identifier of a
car, while GUID is a unique identifier of an employee. The pair {GUID, NumberPlate} is
unique for each tuple in the relation with the following additional constraint: for an employee
X (with a unique GUID) and a car Y (with a unique number plate) it holds true that: the
employee X is associated with only one car Y, and that car Y belongs only to the
corresponding employee X.
E.g., if the employee X with GUID ‘1234’ has the car Y with number plate ‘SC69BBZ’, then,
there is neither another employee with the same car (‘SC69BBZ’) nor another car belonging to
the employee X (‘1234’).
The SQL CREATE statement for the relation Employee is defined by a data analyst as:
CREATE TABLE EMPLOYEE(
Surname VARCHAR(30) NOT NULL,
GUID CHAR(10) NOT NULL UNIQUE,
NUMBERPLATE CHAR(8) NOT NULL UNIQUE,
);
(a) Based on the above-mentioned constraint, list up to three super keys, define a Primary
Key (PK) for the relation Employee, and update the CREATE TABLE statement
to accommodate the PK constraint. Explain your answer.
[4]
(b) Identify the Functional Dependencies (FDs) in the relation. Is the relation in
BCNF Explain your answer.
[4]
(c) Assume that 90% of the employees do not have a car. In this case, identify potential
challenges that can be raised due to the schema of the relation Employee and
propose an alternative relational schema that can cope with these challenges. In the
alternative relational schema, identify the Primary Keys, Foreign Keys (if exist),
Summer Diet 2 Continued Overleaf/
the Unique attributes (if exist) and the FDs. Do the FDs from Question 2(b) hold
true in your proposed alternative relational schema Explain your answer.
