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)
1) Find the hotels that are located at Sydney and have bookings with cost greater than
$1000 with checkInDate 2024-0**01. (2 marks)
2) Find all the customer name who have stayed in a hotel with gym and meeting room
on the day of 2024-0**01. (2 marks)
3) 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)
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. 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:
1. If you have problems with your submission,
2. 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.
For UNSW policies, penalties, and information to help avoid plagiarism, please see:
https://student.unsw.edu.au/plagiarism.
For guidelines in the online ELISE tutorials for all new UNSW students.
請加QQ：99515681 郵箱：99515681@qq.com WX：codinghelp
