Description
Question 1 (4 marks)
Woolworths Online is an online shopping service provided by the Woolworths company. The
Woolworths company hires you to design a small database to store the information about its
online shopping service. You’re given the following requirements:
A customer is identified by his/her email address, and for each customer, we want to
record his/her name, phone number, and address. The address is composed of street
and suburb.
A warehouse is identified by its warehouse ID and we also record its contact number.
A commodity is identified by its commodity ID, and we also need to know the name,
price, and supplier. A commodity may have multiple suppliers.
A commodity must be
stored in at least one warehouse and a warehouse must store at least one commodity.
The stock level of a commodity in a warehouse is needed.
An order is uniquely identified by its order ID. An order must be created by one
customer and a customer must have created at least one order. The time of an order
created is needed.
An order is composed of at least one commodity and the quantity of
each ordered commodity is required. Some commodities may not be included in any
orders. We also want to know the total amount of an order.
An order is sent to exactly one warehouse and a warehouse can process multiple
orders.
A staff is identified by his/her staff ID. The name, birth date, phone number is also
needed. A staff is either a manager or a team member. A team member must be
supervised by exactly one manager and a manager can supervise multiple team
members.
There should be at least one staff works in a warehouse, and a staff should work in
exactly one warehouse. There must be exactly one manager at a warehouse and a
manager must manage exactly one warehouse.
Draw an ER diagram to represent this scenario, and clearly state the assumptions you make if any.
Question 2 (2 marks)
Convert your ER-diagram from Question 1 into a relational model.
Question 3 (4 marks)
Consider the following relational schemas:
Student (studentID, name, age, gender)
Course (courseID, courseName, faculty)
Enrolment (courseID, studentID)
JobRequirement (job, courseID)
Write relational algebra expression to answer the following questions:
1) Find the name of female students who have enrolled in at least one course required by the
“designer” job.
2) Find the name of students who have enrolled in all the courses needed by the “designer”
job and never enrolled in any courses offered by the “law” faculty.
3) Find the name of courses in which the students are all male students or all female students.
Note that, only the following operators can be used in your answer: Select, Project, Union,
Intersection, Difference, Cartesian Product, Join, and Divide.
Assignment Submission
We accept electronic submissions only. Please submit your assignments as follows:
• Ensure that you are in the directory containing the file to be submitted. (note: we only
accept files with .pdf extension)
• Type “give cs9311 ass1 ass1.pdf” to submit.
• Please keep a screen capture (including timestamp and the size of submitted file) for
your submissions as proof in case that the system is not working properly. If you are
not sure how, please have a look here.
Note:
1. If the size of your pdf file is larger than 2MB, the system will not accept the submission.
If you face this problem, try converting to compressed pdf.
2. If you have any problems in submissions, please email to kai.wang@unsw.edu.au.
3. We do not accept e-mail submissions, and the submission system will be immediately
closed after the deadline.
Late Submission Penalty
Zero mark
