COMP9311 22T1: Assignment 1
Deadline: Fri 16:59:59 11th March (Sydney Time)
Question 1 (9 marks)
To better facilitate their operation of ingredient sourcing and allocation etc., A
local restaurant group has enlisted your help in transitioning from old-school
book-keeping to a modern database format. They provide a summary of
requirements as below.
A supplier is a person or company identified by their supplier id and has a
license number. They are characterized by their year of founding and
country of origin. We keep multiple emails for each supplier for reasons.
A product is an ingredient used by restaurants for service. It has a unique
product ID and is characterized by its weight per unit, price per unit, a
short description and availability status.
A supplier offers many products, but each product is offered by one
supplier. The same product can be ordered by multiple restaurants. We
record the delivery date whenever a product is delivered to a restaurant.
A restaurant is identified by its restaurant id. We note its main cuisine
type. We record its star rating, its phone number, and a detailed address
which is made up of a street address, a postcode, and a country.
Restaurants regularly place orders for products; a restaurant is allowed to
order multiple products.
A head chef is uniquely identified by their chef license id. We store the
employment date and his/her multiple signature dishes. Each restaurant
must have one head chef. Each head chef must work for exactly one
restaurant.
We also have a large group of wait staff (i.e., waiters/waitresses) across all
restaurants. Each wait staff works for a restaurant and is identified by a
combination of the restaurant id and a wait staff number. For each wait
staff, we store their date of birth and start year.
Each wait staff is only allowed to work for one restaurant, but a restaurant
instance may have zero or more wait staff. We record the number of wait
staff working at each restaurant.
Your task: draw an ER diagram to represent the scenario, clearly state any
reasonable assumptions that you choose to make. Please keep to the
notations taught in the lecture.
Question 2 (6 marks)
The last admin was supposed to finish this project on updating a hospital
database. You found the approved ER-diagram.
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 an online book repository:
Book (bID, bName, page, year)
Genre (bID, genre)
Reader (rID, rName)
Author (aID, aName, nationality)
Reads (rID, bID, year)
Writes (aID, bID)
Your task: write the relational algebra expressions for the following queries:
1) Find the names of the books written by Shaun Tan after 2000. (2 marks)
2) Find the names of the readers who have read more than 50 books during
the year 2021. (2 marks)
3) Find the names of the books whose genre are not Tragedy and Novella at
the same time and is co-written with author John Steinbeck. (3 marks)
4) Find the names of the French authors who have only written full-length
books (>400 pages) but have never written any book read by more than
1000 readers. (3 marks)
Note: we only allow the operators used in the lecture slides.
Note: attributes that are not part of the primary key are not unique. To elaborate,
the name of the books and the name of the authors or readers etc. should not be
treated as unique.
Assignment Submission
You must submit an electronic copy of their answers to the above
questions to the course website in Moodle.
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:
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.