examstyle
Thursday, 27 April 2017
2.00 pm – 3.30 pm
(Duration: 1 hour 30 minutes)
DEGREES of MSc in Information Technology, MSc in Software Development
Database Theory and Applications
(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 Ovelfeaf/
1. A system is to be built which allows individuals to provide and answer questions
which can be collated to form tests. A test has an ID, a difficulty level and a score
which represents the number of answers which a user has to get right to pass. A
user must select a unique username upon registration and has to provide their
name and an e-mail address. Users can add questions to the database, as well as
answer questions. A question has a unique question ID, a title, an expected
answer and can belong to a number of topics. Each question has a set difficulty
level such as easy, medium or difficult. A user can also answer questions,
whether their answer is correct or incorrect should be recorded, as should the
time taken to answer the question. Users can take a test, which is a collection of
questions, and the date on which they take the test should be recorded.
(a) Design a database using an E-R diagram which models the scenario above.
[10]
2. A delivery company which delivers flowers has a relational database. The
database has to store information such as information on the flowers they stock,
the orders they take and the customers who place orders. The schema for the
database is as follows:
Flowers(flowerID ,name,type,colour,costPerFlower)
FlowersInOrders(flowerID REFERENCES Flowers.flowerID, quantity, orderID
REFERENCES Orders.orderID)
Orders(orderID, customerID REFERENCES Customer.customerID, orderDate,
deliveryAddress)
Customer(customerID,name,address, postcode)
You can assume all IDs are integers, as is quantity in the FlowersInOrders table. You can
assume orderDate is of type date.
(a) Explain what the following SQL does
(i) SELECT DISTINCT colour, flowerID FROM Flowers WHERE
flowerID NOT IN (SELECT flowerID FROM
FlowersInOrders);
[2]
(ii) SELECT name FROM Customer AS c INNER JOIN Orders AS o
ON c.customerID=o.customerID WHERE orderDate =
‘26/06/2017’;
[2]
(iii) SELECT DISTINCT name FROM Customer AS c
INNER JOIN Orders AS o ON c.customerID=o.customerID
INNER JOIN FlowersInOrders AS fo ON fo.orderID=o.orderID
Summer Diet 2 Continued Ovelfeaf/
INNER JOIN Flowers AS f ON f.flowerID=fo.flowerID
WHERE quantity=12 AND f.name=’rose’ AND f.colour=’red’;
[4]
(b) Write SQL to return the following
(i) The number of orders placed per customer along with the customer’s
name, ordered by the most orders to the least
[4]
(ii) The name of the flower which has been ordered most frequently
[4]
(iii) The names of flowers which have yet to be ordered
[4]
3. Consider a relational schema for a company who runs spas which provide spa
treatments such as massage and facials. The head office is located in Edinburgh,
and often has to search for the manager and spa information for spas in different
cities. The head office also has to search for clients who have spent the most
money. The company owns spas throughout the country. The system comprises
the following three relations:
Spa(spaNo, city, managerName, phone_number, address)
Client(clientNo, totalSpent, name, address)
Appointments(appointmentNo, spaNo REFERENCES Spa.spaNo, clientNo
REFERENCES Client.clientNo, date, time, treatment, therapist)
Whilst clients do occasionally book appointments in multiple spas, typically they
use just one. The types of query which are frequently performed include each spa
accessing the details of clients and who are currently booked for an appointment
with them on a given date as well as who the therapist is and what treatment the
client is having. The spa also updates the totalSpent value for a client after a visit.
(a) For each relation, indicate whether you would suggest horizontal or vertical or no
fragmentation
[3]
(b) For each relation, indicate why you suggested horizontal or vertical or none as
above
[3]
(c) Re-write the schema to show the fragments you would create and where you
would allocate each to. You can assume there are currently 3 cities which have
spas – Stirling, Dundee, and Glasgow.
[6]
Summer Diet 3 Continued Ovelfeaf/
4. A craft fair which has a number of craft stalls selling all manner of craft products
moves around the country. Note that not all information must be modelled for
each stall, but that each stall should have an id, the owner and stall name at a
minimum. An XML document which models the stalls within the fair, the owners
of the stall, and the dates each stall is available is as follows:
(a) Write a DTD which represents the XML. Note any assumptions you have to
make.
[8]
Summer Diet 4 /END
5. A company tracks job application interviews including the information shown in
the following table. An applicant can have multiple interviews on the same day
with different or the same interviewer. There are often multiple interviews on the
same day at the same time, but in different rooms with different interviewers.
Applicants are only interviewed by a single interviewer at a given time.
Applicant
ID
Applicant
name
Interview
date
Interview
time
Interviewer
ID
Room Room
Phone
Extension
53 Alex
Vauss
2/3/17 10:00
14:00
515
619
R101
R202
101
202
62 Sam
Black
2/3/17 10:00
12:00
619
619
R202
R202
202
202
70 Patricia
Clark
3/3/17 10:00 619 R101 101
(a) Write the above in first normal form
[4]
(b) Normalise to second normal form
[3]
(c) Normalise to third normal form
[3]