School of Computer Science
COMP9120 Database Management Systems
Assignment 1: Conceptual Modelling & Logical DB Design
Group assignment (12%)
The purpose of this assignment is to provide you with experience in conceptual and relational database modelling.
You are given a domain description for the Sydney Entertainment & Theatres (SET). There are 2 high level tasks in
• Create an Entity Relationship Diagram (ERD) that captures the business concepts and requirements conveyed in
• Translate your ER diagram into a logical database design including relational database schema creation, key
constraints and integrity constraints.
This is a group assignment for teams of 3 people per group. You must be enrolled in an assignment group on Canvas.
You must inform the unit coordinator if you have not formed a group by Week 6.
Please also keep an eye on your email and Ed for any announcements that may be made.
The submission of your solution is due at 11:59pm on Friday 30/04/2021 (end of Week 8). You must submit the items
for submission (detailed below) via Canvas.
Items for submission
Please submit your solution to Assignment 1, in the ’Assignment’ section of the unit’s Canvas site by the deadline,
including EXACTLY THREE files:
1. Firstly, you are required to submit your conceptual model in the form of an E-R diagram using the lecture notation,
formatted as a PDF document (.pdf file suffix). Please justify your choices for entity types, relationship
types, attributes, primary keys, constraints and design specialities.
2. Secondly, you should submit an SQL file (.sql file suffix) containing all DDL statements necessary to fully
instantiate a working database based upon your ER diagram, and DML statements to populate each relation.
Your file should run without errors in PostgreSQL 9.5. You can annotate your statements using ‘--‘ at the start of
lines for comment. You should group your statements for ease of reading (e.g. by keeping all table constraints
within the relevant CREATE TABLE statement rather than declaring them externally, if possible).
3. Thirdly, you should submit another pdf document (.pdf file suffix) including the relational model (RM) diagram
that provides a visual model of your database schema. The figure below summarises the syntax to use for the RM
keyA attrib1 fkey
COMP9120 Assignment 1
Task 1: Domain Description for Entity Relationship Diagram (ERD) Modelling
The Sydney Entertainment & Theatres (SET) is a locally owned theatre operator and production investor, with
numerous theatre venues scattered across Australia. As part of its recovery from COVID-19 pandemic and in order to
enhance its digital footprint, SET has assigned you the task of developing a new database system, starting from a
conceptual model described in an ER diagram that captures all the concepts described below.
SET participates in the Dine & Discover, a major COVID-19 stimulus program which the Government recently kicks off
to encourage the community to get out and about, supporting entertainment and dining businesses and stimulating
spending in the economy. The program provides four vouchers of $25, worth a total of $100, to every adult to spend
on entertainment and dining, including theatres, cinemas, restaurants, theme parks, and most recreational activities.
SET organises a series of theatre productions like Disney’s Frozen, Romeo and Juliet, and The Lion King, which are
performed across their collection of theatres. Customer interested in booking to see an upcoming performance of one
of these productions should be able to check seating availability for their desired performance, and make a booking for
one or more available performance seats.
Each theatre production at SET has: a name, description, production date and "production seat cost"; and also has a
set of scheduled performances. Each performance will have planned start and end dates/times, and will take place in
a certain theatre. Each theatre has a name, address, postcode, capacity, and a description, and is made up of 1 or
more seating sections, where a seat section is composed of 1 or more seats. Different theatre seat sections will have
distinctive views and comfort levels, and hence will have a different "section seat cost" associated with it.
Customers who wish to book one or more performance seats with the SET must provide details about themselves
including their first name, last name, date of birth, mobile number, and email address. A booking placed by a customer
represents an association between the customer, a chosen performance, and the available seats selected by the
customer for that performance. The performance date and time chosen by the customer must be an event occurring in
the future i.e. after the booking date/time. The total cost of the booking will be calculated as the sum of the cost of
each seat in the booking, and the cost of a seat in the booking is calculated as the sum of the "section seat cost" and
the "production seat cost" described earlier. SET should always be able to justify and indicate how they calculated the
total cost of each booking, including how section seat cost and production seat cost are used to calculate it.
To complete a booking, customers must provide information about the payment method they wish to use for their
booking. The total booking cost amount can be split across more than one payment methods. Depending on which
payment method is selected for how much, the following details must be provided: (a) For a Dine & Discover voucher:
the voucher unique code and voucher expiry date, (b) For a credit card: the card number, cardholder name, card
expiry date, and CVV, (c) For a SET gift card: the 15-digit card number and PIN. Dine & Discover vouchers are single
use, and if less than the $25 value per voucher is used, the remaining value is forfeited and can no longer be used
later nor reimbursed by SET.
Task 2: Relational Database Design & Modelling
Your second task is to design and create a relational database schema based on the Entity Relationship Diagram
(ERD) modelled from the first task. In particular, your solution should include:
• Tables and attributes with appropriate data types to capture all information in the model (please use the
same names as in your ER diagram for naming tables and attributes);
• Appropriate PRIMARY KEY, UNIQUE, FOREIGN KEY constraints for all tables;
• Correct foreign key specifications including ON DELETE clauses where suitable;
• Appropriate additional integrity constraints expressed by means of NOT NULL or CHECK clauses;
• INSERT statements to populate each relation with at least one record, to demonstrate a database instance
consistent with the ER model.
COMP9120 Assignment 1
In addition to the model captured through your ER diagram, the following details apply:
1. Fields in a tuple related to dates and times should always have values.
2. All fields in a tuple relating to details about a name (e.g. customer name, theatre production name, etc.) and
payment methods should always have values.
3. The total cost of a booking, "section seat cost" and "production seat cost" should always have values greater
4. Customers must have a specified mobile number.
5. The address postcode of a theatre should be between 800 to 9999.
Escaping PostgreSQL keywords in DDL
If you need to escape PostgreSQL keywords like “Table”, you will need to use double quotes.
e.g. CREATE TABLE “Table” (…);
Q: How to draw the link from foreign key in a table to its referenced candidate key in another table if the foreign key
contains more than one attributes?
A: You should draw it in a similar way to the following RM diagram (specifically, see the Sell table).
This assignment is worth 12% of your final grade for the unit of study. Your group’s submission will be marked
according to the attached rubric (see last section of this assignment description).
Group member participation
If members of your group do not contribute sufficiently, you should alert the unit coordinator as soon as
possible. The course instructor has the discretion to scale the group’s mark for each member as follows:
Level of contribution Proportion of final grade received
No participation. 0%
Full understanding of the submitted work. 50%
Minor contributor to the group’s submission. 75%
Major contributor to the group’s submission. 100%
COMP9120 Assignment 1
Your submissions will be marked according to the following rubric, with a maximum possible score of 12 points.
Novice (0 – 0.5 pt) Competent (1 – 1.5 pts) Proficient (2 pts)
ERD Notation & Core
Big mistakes in the usage of
Less than competent model of
the given scenario.
Good usage of E-R notation
with a few mistakes.
Some entities, relationships, or
attributes cannot be correctly
captured by the model.
Proficient usage of the E-R
The core model was very well
designed, and all the main
entities, relationships and
attributes can be correctly
captured by the model.
ERD Constraints Many constraints are
incorrectly captured in the
model. No constraints captured
Some constraints (key / total
participation constraints on
relationship types, etc.) are
correctly included in the model,
but with minor mistakes
All appropriate constraints are
Majority of design specialities
used are inappropriate or
incomplete. No design
specialities are used.
At least one useful ISA, weak
entity or aggregation used
appropriately. Minor or no
mistakes on design specialities
All design specialities are used
Relational Mappings Less than competent schema
of the given scenario
All main entities and
relationship mapped correctly
to relations, with reasonable
choice of data type for most
The core model was very well
mapped to a relational schema
and good choice of data types
for all attributes
Key Constraints &
Major issues with key
constraints, or no key
constraints captured at all.
Major issues with integrity
constraints, or no integrity
Primary keys and foreign keys
were defined appropriately, but
with minor mistakes.
Integrity constraints such as
CHECK or NOT NULL were
defined correctly, but with
All necessary primary keys and
foreign keys were defined
correctly, including appropriate
ON DELETE clauses.
All necessary integrity
constraints for the model were
Example Data & RM
No example data given or
yielded multiple errors.
No RM diagram submitted, or
major issues with the RM
Some table example data
missing or generated an error.
RM diagram does not exactly
match the relational schema
created by the submitted SQL
Database fully populated with a
consistent and correct set of
RM diagram exactly matches
the relational schema created by
the submitted SQL file (Note:
semantic constraints and
example data are not required in
the RM diagram). 学霸联盟