examstyle
Wednesday 28 April 2018
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/
1. This question is based on the following relational database tables.
Teacher(staffNo, name, address)
Course(courseNo, name, textbook, roomNumber references Room.number,
roomBuilding references Room.building)
Teaches(teacher references Teacher.staffNo, course references Course.courseNo)
Student(matric, name, address, dateOfBirth)
Attends(student references Student.matric, course references Course.courseNo)
Exam(course references Course.courseNo, examNo)
Room(number, building, phone, size)
Primary keys are underlined and the word ‘references’ refers to foreign keys.
(a) Write a SQL statement to find the names of all the students on the SPM course in
alphabetical order. The course Software Project Management is recorded as SPM
in the database.
[4]
(b) Write a SQL statement to return the names of all students who attend at least one
course taught by Ron Poet
[4]
(c) Write a SQL statement to return the names of all students where every course
they take is taught by a different teacher. You may assume that all students
attend more than one course.
[4]
2. (a) Create an ER diagram which describes the schema of a database to hold data for
the following application:
A local authority maintains a number of libraries. Each library lends books to
borrowers, who are registered with the library, as are the books. The librarian is
an employee of the library who interacts with the borrowers and whose work is
supported by the system. A borrower can reserve a book that is not currently
available in the library, so that when it is returned that person is notified. The
reservation can be cancelled by the borrower explicitly, and is also cancelled
when the borrower checks out the book. The data and time when a book is
loaned, returned and reserved are all recorded. The date, start time and stop time
of each librarian’s shift is recorded for the last two years.
[6]
Summer Diet 2 /END
(b) Turn the ER diagram from part (a) into a set of relations, indicating the primary
and foreign keys.
[6]
(c) Explain with examples the terms Fan Traps and Chasm Traps.
[4]
3. (a) Use the following problem definition to identify a set of attributes and a list of
functional dependencies connecting the attributes. Express the attributes as a
universal relation. Clearly state any assumptions that you have made.
An invoice contains a list of parts purchased by a customer. The customer
information contains the customer’s name, address and postcode, as well as the
customer’s email address, which is unique. Each invoice has a unique invoice
number and also a list of parts that have been purchased. Each part has a unique
part number, a description, quantity, the price of one part and the price for all
parts. Finally, the invoice contains the total price and the date issued.
[6]
(b) Convert the universal relation in part (a) to a set of relations that are in second
normal form, clearly stating the properties of relations in second normal form.
[4]
(c) Convert these relations to a new set of relations that are in third normal form,
clearly stating the properties of relations in third normal form.
[4]
4. (a) Describe how information stored in a relational database can be secured.
[4]
(b) What mechanisms are often put in place to avoid data loss when a relational
database crashes?
[4]
(c) What are the benefits and disadvantages of distributed databases? Describe one
issue that makes implementing distributed databases hard.
[4]
(d) Explain how relational databases support concurrent multi-user access.
[6]