COMP9311 23T3: Assignment 1
Deadline: Fri 16:59:59 6th October (Sydney Time)
Question 1 (8 marks)
An organizer commissioned us to design a database for an inventory
management system. They provide a summary of requirements as below.
• A supplier is identified by his/her id. We also record his/her name, phone
number and supplier type. A supplier may have several supplier types.
• A product is identified by its id. We also record its name, price, production
date and availability. A supplier must provide one or many products. Each
product must be provided by one supplier.
• A manager is identified by his/her id. We also record his/her name, phone
number, working hours and job description. Each manager is hired by one
supplier. A supplier may hire zero or many managers.
• A manager can oversee zero or many products. Each product can be
overseen by zero or many managers.
• An order is identified by its id. We also record its order date, status and
price. We are interested in the number of products in each order as well.
• An order must involve one or many products. Each product is involved in
at most one order.
• Each shipping is identified by its id. We also record the shipping address,
shipping company and status. The shipping address is composed of street
number, street name, city and country.
• An order can have at most one shipping and each shipping must
correspond to an order.
• Each order is associated with zero or many invoices. An invoice is
identified by a combination of the invoice id and the order id. It must exist
along with the order. We also record its date, billing address, payment
method and tax information.
Your task: draw an ER diagram to represent the scenario, clearly state any
reasonable assumptions that you choose to make. Reasonable relation
names are acceptable. Please keep to the notations taught in the lecture.
Question 2 (6 marks)
Your task: convert the above ER-diagram into a relational data model, and please
only keep to the notations/model taught in the lecture.
Question 3 (10 marks)
Consider the following schema for a movie database:
Player (pID, pName, nationality)
Game (gID, gName, price, platform)
Genre (gID, genre)
Play (pID, gID, duration)
Your task: write the relational algebra expressions for the following queries:
1) Find the names of the games that are in the role-playing genre and have
prices lower than $200. (2 marks)
2) Find the names of the players who have played more than 5 games
where duration of each is more than 3 hours. Only consider the games
that are on the Windows Platform. (2 marks)
3) Find the names of the games that are in both Adventure and Action
genres at the same time and have been played by German players. (3
marks)
4) Find the names of the Italian players who have only played games that
are on the PSP platform and have never played any game that are in the
Fighting genre. (3 marks)
Note: Attributes that are not part of the primary key are not unique. Please keep
to the operators/notations taught in the lecture.
Assignment Submission
• You must submit an electronic copy of their answers to the above
questions to the course website in Moodle. You can handwrite your
answers and scan or take a photo. Make sure they are legible.
• We only accept .doc or .pdf files.
• Please name your files in the following format: ass1_studentID.doc or
ass1_studentID.pdf (e.g., ass1_z5100000.doc or ass1_z5100000.pdf).
Note:
1. If you have problems relating to your submission, please write to the
course email at junhua.zhang@unsw.edu.au or yiheng.hu@unsw.edu.au.
If there are issues with Moodle, send your assignment to the above email
with the title “ COMP9311 Ass1 Submission”.
2. All submissions will be checked for plagiarism. The university regards
plagiarism as a form of academic misconduct and has very strict rules. Not
knowing the rules will not be considered a valid excuse when you are
caught.
a. For UNSW policies, penalties, and information to help avoid plagiarism,
please see: https://student.unsw.edu.au/plagiarism.
b. For guidelines in the online ELISE tutorials for all new UNSW students:
https://subjectguides.library.unsw.edu.au/elise/plagiarism.
Late Submission Penalty
• 5% of the max assessment mark will be deducted for each day late (1
second late is considered as one day late), up to 5 days (5*24 hours).
• Submissions that are more than five days late will not be marked.