DECEMBER 2019 EXAMINATION DIET
SCHOOL OF COMPUTER SCIENCE
MODULE CODE: IS5102
MODULE TITLE: Database Management Systems
EXAM DURATION: 2 hours
EXAM INSTRUCTIONS: (a) Answer three questions.
(b) Each question carries 20 marks.
(c) Answer questions in the script book.
PERMITTED MATERIALS: Non-programmable calculator
YOU MUST HAND IN THIS EXAM PAPER AT THE END OF THE EXAM.
DO NOT TURN OVER THIS EXAM PAPER UNTIL
YOU ARE INSTRUCTED TO DO SO.
Page 1 of 6
1. A database designer was given the scenario below:
WeDeliverBooks, an online bookstore, needs a database to manage de-
tails about company’s books, customers, and book orders. They store books
in various formats: some books in paperback, some in hard cover, and some
in audio versions. Customers want to know the number of pages for pa-
perback and hard cover editions, and the run time for audio books. Orders
need to be recorded for delivery of books to customers, and should record
the order date. Information on customer order value and order quantity is
desirable. They also need to know customer details.
(a) The designer created the following E-R model:
Customer
name
email
phone
type
number

address
street
city
Book
id
title
author
genre
publisher
Edition
type
price
Edition of
Order
id
delivery address
street
city
date ordered
date delivered
Contains
Places
Point out four mistakes they made in the E-R model, with short justifications.
[6 marks]
(b) Convert the E-R diagram from Question 1 (a) into a relational schema, being sure
to specify sensible attribute types, and primary key, foreign keys and non-null
constraints as appropriate. [6 marks]
Page 2 of 6
(c) Write relational algebra queries over the relational model of Question 1 (b) for
the following queries:
(i) Customer names and email of those who live in the city of Glasgow.
[2 marks]
(ii) Cities where the company delivered an order of a hardback edition of “War
and Peace”. [2 marks]
(d) Design a collection schema (such as in a NoSQL database) to represent the data
model from Question 1 (a). [4 marks]
[Total marks 20]
Page 3 of 6
2. The university wants a database to record data about hazard assessments of laborato-
ries and lecture spaces. The following relation schemas have been defined:
building = (building id, name, street address)
room = (building id, floor, room no, floor space,
max capacity, no of doors, chemical hazards)
inspection = (inspection id, date, building id, room no,
inspector id, issues noted)
inspector = (inspector id, name, office phone, email)
(a) Write suitable SQL DDL statements to create the tables as above. Include
attribute types, primary and foreign key constraints. [6 marks]
(b) Write SQL DML queries over the tables defined in Question 2 (a) to find:
(i) Rooms in the university together with the date they were last inspected,
ordered from most capacity to least. [2 marks]
(ii) The average number of issues noted in inspections per building. [2 marks]
(iii) The names of inspectors ordered by the number of inspections they have
done in 2018. [2 marks]
(c) Write an SQL statement to add a “number of windows” attribute to rooms, with
a default value of zero. [2 marks]
(d) We want a class of users called auditors who need to access the database and get
a list of rooms inspected, the dates inspected and the issues noted. However,
auditors should not be able to get any details on inspectors who performed
those inspections. Explain how views and authorisation can be used to solve this
problem, together with the SQL statement(s) to achieve this. [6 marks]
[Total marks 20]
Page 4 of 6
3. The Student Advising Center has built a database recording their student contacts.
They have defined the following two tables in SQL:
student(matric id char(7), student name varchar(20), date of birth date,
program id char(6), program name varchar(20),
primary key(matric id));
contact(contact id char(6), date date,
student id char(7), student name varchar(20),
advisor id char(7), advisor name varchar(20),
advisor phone number(7),
primary key (contact id),
foreign key (student id) references student,
foreign key (student name) references student);
(a) Write a SQL query to list the students seen in order of number of contacts, largest
first. [2 marks]
(b) A student has recently legally changed their name. Write an update statement
in SQL to change the name of the student record with student id ’1300201’.
[2 marks]
(c) After the change done in Question 3 (b), the contact table has the wrong name
for the student. How would cascading constraints be used to fix the name
automatically? [2 marks]
(d) If an advisor has had no contacts with students, we cannot find their details in
this database. To avoid this anomaly, define a trigger in SQL so that a contact
cannot be deleted if that is the only contact for a particular advisor. Either ISO
SQL syntax or MariaDB syntax for triggers is allowed. [6 marks]
(e) Give an example of an update anomaly in the above table design. [2 marks]
(f) Perform normalisation on the above tables to remove anomalies such as in
Question 3 (e). You should make sure the result of your normalisation is actually
in Third Normal Form. [6 marks]
[Total marks 20]
Page 5 of 6
4. An online food delivery service approaches you to sort through their data on sales.
They hold data on menus (restaurant name, address, list of items and prices), and
sales (date of sale, restaurant, items sold, customer address and email).
(a) Sketch out a relational data model for this scenario. Make sure to specify your
data types, primary keys and foreign key constraints. State any assumptions you
need to make. [6 marks]
(b) Write SQL queries over the relational models defined in Question 4 (a) to find:
(i) Email and postal addresses of all customers who have ordered 3 or more
times from ‘Pizza Star’. [2 marks]
(ii) The restaurants ordered by total sale values. [2 marks]
(iii) The five top selling menu items in the month of October 2019. [3 marks]
(c) Suppose the company would like to find “similar” customers. One idea is to
identify pairs of customers who order from the same restaurant, ordered by the
number of common orders.
(i) Write SQL over your model from Question 4 (a) to find such pairs.
[3 marks]
(ii) How might you restructure the data model (using relational or non-relational
models) to make finding such pairs easier? [4 marks]
[Total marks 20]
*** END OF PAPER ***