Q1. Basic database design and queries (6 marks)
A small shop wants to create a simple database to keep track of its merchandise prices, buyers and purchase
orders. For simplicity, a merchandise will be recorded for its name and price, a buyer will be recorded for
its name, and order will be recorded for its date and all the items included in the order.
i. Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the
ER diagram for your design. Indicate on the ER diagram the primary keys and the relationship
multiplicities. By minimum we mean that you don't need to add anything that is not explicitly stated in
the requirements, unless it's one of your artificially generated keys. For instance, you don't need to
include a telephone number or email address for the entity corresponding to a buyer. (1.5 marks)
ii. Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above, indicating
all the primary keys and foreign keys. The GRD should be in a form similar to Figure 17.9 (page 554
or 516 for edition 5) of the textbook, but all the attributes should be kept there too. (1 mark)
iii. Write an SQL script (of statements) that generates all the tables for your designed database. (1 mark)
iv. Write an SQL script to insert sufficient records into your tables. Each table should contain no less than
3 records. At least 2 orders should each contain 2 or more items of the ordered merchandise.
Screenshots are required for the records of all the tables. (1 mark)
v. List all all buyers whose name contains your own family name as a substring. Insert sufficient records
into your table/s so that your query returns at least 2 records. (screenshot required). (0.5 marks)
vi. For a given order (number), write an SQL statement to list all the item names and their corresponding
prices for the order (screenshot required). (0.5 marks)
vii. List all the orders by their order number, date, and the name of the buyer who places that order
(screenshot required). (0.5 marks)
Q2. More on SQL queries (3 marks)
A DreamHome database has been created according to a case study for a property rental business (see §11.4
at pages 381401,or 347367 for edition 5, of the textbook for the case study). Its (simplified) database
schemas (§6.3 at page 197 or 189 for edition 5) and the relation diagram are given as.
i. Draw an ER diagram to represent the above tablelinking
diagram (which is essentially what we would
call a relation diagram). The ER diagram should bear fewer entity types than the number of tables in
the above displayed diagram. That is, the table or tables that essentially represent relationships should
be represented as relationships on the ER diagram, not as entities. (0.5 marks)
ii. Create this set of tables and fill the records by excuting this given SQL script dreamhome.sql. Then
use an UPDATE statement in SQL to modify the staff member "Julie Lee" into your own name and
modify the his date of birth (13/6/1965) into a date after 1990s. If you have a team member for this
assignment, then also UPDATE the staff record for staffNo="SL21" by replacing the name "John
White" there by that of your team partner. (0.5 marks)
iii. Write an SQL statement to list all the client names, the maximum rent they are willing to pay, and their
telephone numbers (screenshot required). (0.5 marks)
iv. Write an SQL statement to list staff name, position, and the postcode of their branch. The listing
should be ordered according to postcode, and within the same postcode, ordered alphabetically
according to the last name (screenshot required). (0.5 marks)
v. Write an SQL statement to list all area postcode, propertyNo, and the staff name responsible for the
management of the property. Sort the output according to postcode (screenshot required). (0.5 marks)
vi. Write an SQL statement to list all the properties that have been viewed by one or more clients. More
precisely, list the postcode, propertyNo, the street of the property, last name of the staff responsible for
this property, client's last name, and the viewing date. Order the output first by the postcode, then by
the street (screenshot required). (0.5 marks)
Note that the screenshots you provide should again conform to the requirements indicated in the previous
question. For students working in a team of 2 members, the student name contained in the screenshots for
this question should be different from the one in the previous question. Otherwise the person whose name is
neither on the screenshots of this question nor on those of the previous one may lose the corresponding
marks.
Q3. Database modelling case
study (3 marks)
In this question, you are required to construct a Swimming Database for a swimming club, so that the
database can be used to maintain the listing of excellent performers for different swimming events. The
database will record which top swimmers specialise on which events, and these events include for instance
100m Free, 200m Butterfly, 400m Medley, and 1500m Back for both men and women. The performance
listing data kept in the database should include gender, performance time, swimmer's name, date of birth,
title of the swimming competition, date of the competition, venue, and the ordinal position in that
competition. A typical entry of such a listing might contain the data similar to the following
Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER
diagram for your design. Indicate on the ER diagram the primary keys and the relationship multiplicities.
You must use the same notation scheme for the ER diagram as the textbook, and the ER diagram should be
strictly in the sense the textbook uses. List the schemas for all the derived tables for this database, marking
out the primary keys and foreign keys if any.
Q4. Selected Additional Exercises (3 marks)
i. Complete Questions α in the Additional Exercises for Practical 5. (1 mark)
ii. Complete Questions β and γ in the Additional Exercises for Practical 6. (1 mark)
iii. Complete Questions α in the Additional Exercises for Practical 7. (1 mark)