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

日期：2023-10-04 10:45

COMP9311 23T3: Assignment 1

Deadline: Fri 16:59:59 6th October (Sydney Time)

Question 1 (8 marks)

An organizer commissioned us to design a database for an inventory

management system. They provide a summary of requirements as below.

• A supplier is identified by his/her id. We also record his/her name, phone

number and supplier type. A supplier may have several supplier types.

• A product is identified by its id. We also record its name, price, production

date and availability. A supplier must provide one or many products. Each

product must be provided by one supplier.

• A manager is identified by his/her id. We also record his/her name, phone

number, working hours and job description. Each manager is hired by one

supplier. A supplier may hire zero or many managers.

• A manager can oversee zero or many products. Each product can be

overseen by zero or many managers.

• An order is identified by its id. We also record its order date, status and

price. We are interested in the number of products in each order as well.

• An order must involve one or many products. Each product is involved in

at most one order.

• Each shipping is identified by its id. We also record the shipping address,

shipping company and status. The shipping address is composed of street

number, street name, city and country.

• An order can have at most one shipping and each shipping must

correspond to an order.

• Each order is associated with zero or many invoices. An invoice is

identified by a combination of the invoice id and the order id. It must exist

along with the order. We also record its date, billing address, payment

method and tax information.

Your task: draw an ER diagram to represent the scenario, clearly state any

reasonable assumptions that you choose to make. Reasonable relation

names are acceptable. Please keep to the notations taught in the lecture.

Question 2 (6 marks)

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 a movie database:

Player (pID, pName, nationality)

Game (gID, gName, price, platform)

Genre (gID, genre)

Play (pID, gID, duration)

Your task: write the relational algebra expressions for the following queries:

1) Find the names of the games that are in the role-playing genre and have

prices lower than $200. (2 marks)

2) Find the names of the players who have played more than 5 games

where duration of each is more than 3 hours. Only consider the games

that are on the Windows Platform. (2 marks)

3) Find the names of the games that are in both Adventure and Action

genres at the same time and have been played by German players. (3

marks)

4) Find the names of the Italian players who have only played games that

are on the PSP platform and have never played any game that are in the

Fighting genre. (3 marks)

Note: Attributes that are not part of the primary key are not unique. Please keep

to the operators/notations taught in the lecture.

Assignment Submission

• You must submit an electronic copy of their answers to the above

questions to the course website in Moodle. You can handwrite your

answers and scan or take a photo. Make sure they are legible.

• 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 junhua.zhang@unsw.edu.au or yiheng.hu@unsw.edu.au.

If there are issues with Moodle, send your assignment to the above email

with the title “<zid> 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

• 5% of the max assessment mark will be deducted for each day late (1

second late is considered as one day late), up to 5 days (5*24 hours).

• Submissions that are more than five days late will not be marked.