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: 1. If you have problems relating to your submission, please write to the course email at
[email protected]. 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 • A hefty 20% of your final mark will be deducted for each additional day (24hr) after the specified submission time and date. • Submissions that are more than five days late will not be marked. 欢迎咨询51作业君
