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.