Assignment 1
Please make sure that you always use notations consistent with lecture notes.
Different notations will not be accepted. The deadline for assignment 1 is:
Fri 12 Mar, 5:00 pm
Question 1 (6 marks)
A car dealership organization hires you to design a small database, and gives you the following
requirements:
A dealership is uniquely identified by its ID. For each dealership, we also record its
name, contact number, email, and address. The address is composed of a suburb and
a street. Each dealership must have one or more employees.
An employee is uniquely identified by his/her ID. For each employee, we also record
his/her name, phone number and email. An employee works in one or more
dealerships. In addition, an employee can have multiple working time periods
available.
A vehicle is uniquely identified by its ID. For each vehicle, the make, model, build
date, and description will be recorded. In this organization, each vehicle will be sold
by exactly one employee.
An employee can sell zero or more vehicles.
A dealership owns zero or more vehicles. Each vehicle must be owned by exactly one
dealership.
There are some showrooms. A showroom is uniquely identified by its ID. For each
showroom, the name, the contact person, and the contact number will be recorded.
Each showroom has one or more vehicles, and a vehicle is parked in zero or one
showrooms.
A customer is uniquely identified by his/her ID. For each customer, we also record
his/her name, phone number and email. A customer may not buy any vehicle but can
also buy multiple vehicles. A vehicle may not be sold yet. If sold, a vehicle must be
bought by exactly one customer.
When a customer purchases a vehicle, we also record the date and sales price.
Draw an ER diagram to represent the scenario, clearly state the assumptions you make if any.
Question 2 (6 marks)
Convert your ER-diagram from Question 1 into a relational data model.
Question 3 (8 marks)
Consider the following relational schemas:
Department (dID, dName, location)
Employee (eID, eName, dID, gender)
Project (pID, pName, cost, startTime, endTime)
WorkOn (eID, pID)
Host (dID, pID)
Write relational algebra expression to answer the following questions:
1) Find the names of the employees who work for the sales department. (2 marks)
2) Find the names of the projects which has no employee from the human resource
department and involves at least one employee from each of the rest of the departments.
(2 marks)
3) Find the names of the projects which only have male employees or only have female
employees. (2 marks)
4) Find the names of the departments which have hosted some high-cost projects (>= 1
million), but their employees do not participate in the projects hosted by any other
departments. (2 marks)
Note that, only the following operators can be used in your answer: Select, Project, Union,
Intersection, Difference, Cartesian Product, Join, and Divide. Any name may not be unique,
different departments, projects or employees can have a same name. For duplicate entity names in
two different relations, you may use relation.entity to specify the entity, e.g., 𝑅1 ⋈𝑅1.𝑒𝐼𝐷=𝑅2.𝑒𝐼𝐷 R2
Assignment Submission
• Students must submit an electronic copy of their answers to the above questions to
the course website in Moodle.
• Only .doc or .pdf file is accepted. The file name should be ass1_studentID.doc or
ass1_studentID.pdf (e.g., ass1_z5100000.doc or ass1_z5100000.pdf).
Note:
1. For any problems in submissions, please email to comp9311unsw@gmail.com
2. All submissions will be checked for plagiarism.
3. We do not accept e-mail submissions.
Warning: Before submission, please keep a copy in your university account or other reliable
cloud servers (such as dropbox or google drive). If you are not sure how, please have a look at
taggi. Usually, the submission should be successful. In case it fails, we do not accept backups
from your own computers as the modification time can be edited.
The university regards plagiarism as a form of academic misconduct and has very strict rules
regarding plagiarism. For UNSW policies, penalties, and information to help avoid
plagiarism, please see: https://student.unsw.edu.au/plagiarism as well as the guidelines in the
online ELISE tutorials for all new UNSW students:
https://subjectguides.library.unsw.edu.au/elise
Late Submission Penalty
0 mark.
Assignment 1