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

COMP9311 24T1: Assignment 1

Deadline: Fri 16:59:59 8th March (Sydney Time)

Question 1 (8 marks)

An organizer commissioned us to design a database for auction platform. They provide a summary of requirements as below.

• An auction is identified by its auction id. It is characterized by its name, location, and duration. The duration is composed of start time, and end time. At least one manager is required to oversee each auction, and managers may oversee multiple auctions.

• A manager is identified by her/his manager id. We also record her/his name, phone number, job description and email. Note that a manager may have multiple emails.

• A buyer is identified by her/his buyer id. We also record the name, phone number, address, and payment method. The address is composed of country, city, and postcode. Note that a buyer may use multiple payment methods.

• A buyer must be invited by at least one manager. Moreover, each manager can invite zero or more buyers.

• Buyers place orders to purchase products. Each buyer can place zero or more orders. Each order is identified by its id. We also record the order’s timestamp and total price. Each order must be placed by one buyer.

• An order must contain one or more purchased items. Thus, we also need to record the number of items in an order.

• An item is identified by its item id. Every item in the auctions is unique. We also record its name, price, and description. The description is composed of the produce year, ‘who made it’ and previous auction price.

• Each item must be displayed in one or more auctions. We store the available status to avoid conflict. An auction can display one or more items. Thus, we also need to record the number of items displayed.

• Every item in the auction is provided by the seller, but the seller may not want to disclose personal information to the public. A seller can provide one or more items. Each item must be provided by one seller. The seller is identified by seller id and item id. We also record her/his name and visible status.

• In order to ensure that every order is authentic, valid and error-free. Each order is required to be signed by managers who handle the specific sold items. Every manager can sign zero or more orders and be responsible for zero or more items. Each item must be handled by a single manager.

Your task: draw an ER diagram to represent the scenarios, 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 car sales database:

Customer (cusID, cusName, phone)

Make (makID, makName, foundedYear, country)

Car (carID, makID, model, year, bodyType, status (available/sold))

Sale (carID, cusID, salpID, saleYear, salePrice)

Salesperson (salpID, salpName, rate)

Service (carID, serID, sYear, sCost)

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

1) Find the model of available cars which manufactured prior to the year 2000 and made in Germany. (2 marks)

2) Find the names of salespersons with a rating exceeding 4.5 who sold over 15 German cars priced above $100,000 in the year 2021. (2 marks)

3) Find the names of customers who have purchased cars that are both sedan (bodyType) and were manufactured by companies with a history of more than 50 years from now at the same time. Besides, the cars have undergone servicing more than 10 times in last five years from now. (3 marks)

4) Find the names of high-rated salesperson (with a rating over 4.8) who only sold German cars this year and have never sold any SUVs previously. Note: “SUV” refers to the body type of the cars. (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 are required to submit an electronic version of your answers via Moodle. While we accept handwritten submissions, please ensure they are scanned or photographed clearly to ensure legibility.

• 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 relating to your submission, please email to xingyu.tan@un sw.edu.au.

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: https://subjectguides.library.unsw.edu.au/elise/plagiarism.