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 of information about its sales, customers, products, etc. They have provided the following information about their business. You are required to draw an ER diagram based on the information provided. Do not add any entities or attributes to the ER diagram unless they are explicitly mentioned in the question. If you need to make any assumptions, state them clearly. Use the ER diagram notations presented in the lectures. The store sells multiple products to its customers. Each product has a unique product ID, a product name, a product description, a price, a brand, and a category. Each product has at least one product instance. Each product instance has an instance ID, a production date, and a status (e.g., in stock, sold). Each product instance is associated with only one product. The instance ID is unique within the same product but not unique across different products. The store has several warehouses in different locations. Each warehouse has a unique warehouse ID, a location, and a capacity. The store keeps track of the inventory of each product in each warehouse. Specifically, the store records which product instances are stored in which warehouse and when the product instance was added to the warehouse. The store also keeps track of the quantity of each product in each warehouse, which can be obtained from the product instance records. The store has multiple suppliers who supply products to the store. Each supplier has a unique supplier ID, supplier name, contact number, and address. Each supplier can supply one or more products, and each product can only be supplied by one supplier. The store has multiple customers who purchase products from the store. Each customer has a unique customer ID, a name (first name and last name), a contact number, one or more delivery addresses, and a membership level. Each customer can place zero or more orders, and each order is placed by one customer. 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 more products. Each product may be contained in multiple orders. For better customer service, the store keeps track of which specific product instances were part of each order and the quantity of each product in the order (the quantity can be obtained from the product instances in the order). The store has multiple employees who are responsible for managing warehouses, products, and orders. Each employee has a unique employee ID, name (first name and last name), department, contact number, and date of hire. Each employee can manage a maximum of one warehouse, multiple products, and multiple orders. Each warehouse and product is managed by at least one employee. Each order is managed by one employee. Question 2 (6 marks) Convert the below ER-diagram into a relational data model. Use the notations/model taught 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 the operators/notations taught in the lecture)
- Find the hotels that are located at Sydney and have bookings with cost greater than $1000 with checkInDate 2024-01-01. (2 marks)
- Find all the customer name who have stayed in a hotel with gym and meeting room on the day of 2024-01-01. (2 marks)
- Find the names of customers who have stayed at over 10 different hotels with room price higher than $200 in the year 2023. Only consider hotels that only provides rooms with capacity less or equal to 3. (3 marks)
- 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. If your 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:
- If you have problems with your submission,
- If there are issues with Moodle, send your assignment to the above email with the subject 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. Plagiarism The work you submit must be your own work. Submission of work partially or completely derived 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 the course and possibly other academic discipline.
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. WX：codinghelp
