University of Liverpool Department of Computer Science
COMP518 – Database & Information Systems
Coursework 1
Weighting: 25%
Deadline: Thursday 1st November 2018 at 12:00
Submit your coursework as a PDF via the drop-box provided on Vital. Please be aware of the University guidelines on plagiarism and collusion.
Marking will be based on the grade descriptors for level M (FHEQ 7) modules published in the Postgraduate Student Handbook. Penalties for late submission will be applied as outlined in the University’s Code of Practice on Assessment.
Part A – Relational Algebra (50%)
For each of these questions you should make reasonable assumptions about the domain and typical values of each attribute. For example, the ‘restaurant’ attribute contains the name of the restaurant as text.
- 1) Consider a database with the following schema:
Person ( name, age, gender ) Visits ( name, restaurant )
Eats ( name, dish )
Serves ( restaurant, dish, price )Write relational algebra expressions for the following queries. The mark for each part is shown in square brackets.
- a) Find all restaurants who served anyone under the age of 18. [2]
- b) Find the names of all females who eat either Carbonara or Bolognese (or both). [3]
- c) Find the names of all males who eat both Pizza and Curry. [4]
- d) Find all restaurants that serve at least one dish that Tom eats, for less than £20. [6]
- e) Find all restaurants that are visited by only males or only females. [7]
- 2) Consider the following relational database schema.
Academic ( aID, name, title, address ) Student ( sID, name, course )
Module ( mID, name, aID ) Enrolment ( mID, sID )Write relational algebra expressions for the following queries.
- a) FindtheIDsofallstudentswhoarenotenroledonanymoduletaughtbyMikeHodges. [7]
- b) Find the ID and name of any students who have the same name as an academic. [7]
- c) Find the names of modules which are taken by all students of Computer Science. [7]
- d) Find the names of students who take all the modules taught by Sheila Gibney. [7]
Part B – Entity-Relationship Modelling (50%)
Carefully read the background information below, and then draw an Enhanced ERD that models as many concepts and constraints as possible. You should use the notations introduced in the lectures and covered in the textbook.
- You can make common sense assumptions about the situation. If you want to add an attribute that isn’t mentioned in the text, you can do so. Describe the assumptions you’ve made.
- Some of the information can’t be captured by the ERD model, or is irrelevant to the design. Discuss any information that you can’t capture or have decided to ignore.
- There will be multiple (equally good) solutions following different design choices. Provide justification for your design and make sure it’s consistent with the background information.
Background Information
You will be modelling the data for a typical app store, its users, and their devices. This fictional scenario looks very much like the app stores offered by Apple and Google.
Each user has an account, which has a unique ID plus some necessary information such as phone number, email address, date of birth, and so on. There are two types of account: customers and developers. A customer can download products (music, books, movies and apps). A customer account will have details such as their address and payment details. A developer makes the products and puts them on the app store. A developer account will have details such as their office address, website, and banking details so they can be paid for their products. Crucially, a developer can also be a customer because they might want to download products from other developers.
Each customer has a device (such as a phone, tablet or laptop) that they can download products onto. Each device has an operating system (OS) and version. It also has a unique ID, which might be based on its MAC address or generated via its hardware. Some products are only compatible with particular versions of an OS or device, and this should be recorded in the database. Some products are free, but others have to be paid for. When a product is downloaded (whether paid or free) this transaction is also recorded in the database, storing the time and price of the download.
Each product has its own information. Apps have a name and file size, movies have a title and age rating, books have a title and author, and music has a genre and artist. Products also have globally unique IDs that are generated using the UUID algorithm. You can add other attributes if you feel it necessary.