Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

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 [email protected] or [email protected]. If there are issues with Moodle, send your assignment to the above email with the title “ 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.