COMP9311代做、代写Java/c++程序

日期：2024-06-14 10:33

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-01-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-01-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.