**COMP9311 24T2: Assignment 1**
Deadline: Sunday 21:59:59 16 June
Question 1 (8 marks)
Suppose we are helping an online retail store design a database to keep track ofinformation about its sales, customers, products, etc. They have provided the followingnformation about their business. You are required to draw an ER diagram based on theinformation provided.Do not add any entities or attributes to the ER diagram unless they are explicitlymentioned in the question. If you need to make any assumptions, state them clearly. Usethe ER diagram notations presented in the lectures.
1、The store sells multiple products to its customers. Each product has a uniqueproduct ID, a product name, a product description, a price, a brand, and a category.Each product has at least one product instance.
2、Each product instance has an instance ID, a production date, and a status (e.g., instock, sold). Each product instance is associated with only one product. Theinstance ID is unique within the same product but not unique across differentproducts.
3、 The store has several warehouses in different locations. Each warehouse has aunique warehouse ID, a location, and a capacity.
4、 The store keeps track of the inventory of each product in each warehouse.Specifically, the store records which product instances are stored in whichwarehouse and when the product instance was added to the warehouse. The storelso keeps track of the quantity of each product in each warehouse, which can beobtained from the product instance records.
5、The store has multiple suppliers who supply products to the store. Each supplierhas a unique supplier ID, supplier name, contact number, and address. Eachsupplier can supply one or more products, and each product can only be suppliedby one supplier.
6、The store has multiple customers who purchase products from the store. Eachcustomer has a unique customer ID, a name (first name and last name), a contactnumber, one or more delivery addresses, and a membership level. Each customercan place zero or more orders, and each order is placed by one customer.
7、Each order has a unique order ID, total price, payment method, shipping address,and the date and time the order was placed. Each order may contain one or moreproducts. Each product may be contained in multiple orders. For better customerservice, the store keeps track of which specific product instances were part of eachorder and the quantity of each product in the order (the quantity can be obtainedfrom the product instances in the order).
8、The store has multiple employees who are responsible for managing warehouses,products, and orders. Each employee has a unique employee ID, name (first nameand last name), department, contact number, and date of hire. Each employee canmanage a maximum of one warehouse, multiple products, and multiple orders.Each warehouse and product is managed by at least one employee. Each order ismanaged by one employee.
Question 2 (6 marks)
Convert the below ER-diagram into a relational data model. Use the notations/modeltaught in the lecture.
Question 3 (10 marks)
Consider the following schema for the hotel management database:
Customer (customerID, name, phone)
Hotel (hotelID, name, city, address)
Room (hotelID, roomID, type, price, capacity)
Booking (bookingID, customerID, hotelID, roomID, checkInDate, checkOutDate, cost)
Facility (facilityID, facilityName)
HotelFacility (hotelID, facilityID)
Write the relational algebra expressions for the following queries: (Use theoperators/notations taught in the lecture)
1) Find the hotels that are located at Sydney and have bookings with cost greater than
$1000 with checkInDate 2024-01-01. (2 marks)
2) Find all the customer name who have stayed in a hotel with gym and meeting roomon the day of 2024-01-01. (2 marks)
3) Find the names of customers who have stayed at over 10 different hotels withprovides rooms with capacity less or equal to 3. (3 marks)
4) Find the names and phone numbers of customers who have ever stayed at all the
hotels in Sydney that contains all the facilities. (3 marks)
Assignment Submission
You are required to submit an electronic version of your answers via Moodle. Ifyour submission is handwritten, please ensure they are clear and legible. We only accept the .pdf format. Please name your files in the following format:
ass1_zID.pdf (e.g., ass1_z5000000.pdf).
Note:
1. If you have problems with your submission, please emailjunhua.zhang@unsw.edu.au.
2. If there are issues with Moodle, send your assignment to the above email with thesubject title “ COMP9311 Ass1 Submission”.
Late Submission Penalty
5% of the max mark (24 marks) will be deducted for each additional day. Submissions that are more than five days late will not be marked.
PlagiarismThe work you submit must be your own work. Submission of work partially or completelyderived from any other person or jointly written with any other person is not permitted.The penalties for such an offence may include negative marks, automatic failure of thecourse and possibly other academic discipline.
All submissions will be checked for plagiarism. The university regards plagiarism as aform of academic misconduct and has very strict rules. Not knowing the rules will not beconsidered a valid excuse when you are caught.