1

INFS1200/7900 Information Systems
Assignment - Part 1 (15% of Final Grade)
Part 1 Due: 13 September 2019 @ 5.00 PM

Assignment Part 1 consists of the design, which includes an Entity-Relationship
diagram, mapping to the relational schema and further normalization of the relational
schema if required. The necessary information for schema design is given in the
Universe of Discourse (UoD) described below. This part must be done in a group of
three students.

In Part 2 of the assignment, a schema will be provided to be implemented in MySQL.
Assignment Part 2 will be released following the due date of Part 1. The details will
be released after the submission of the design part. This part is done individually by
all students.
1. Assignment Brief

The following describes the Universe of Discourse for the assignment:

With the current popularity of video streaming services like ‘Netflix’ and ‘Amazon
Prime’, you have been tasked with creating a database for a new up-and-coming
streaming service called ‘Home Cinema’.

Each product on the site is classified as either a movie or TV show. For each product,
the site stores it’s title, a unique code, a brief synopsis and a collection of tags related
to it (Action, Adventure, Romance, etc.). Additionally, the audio language options and
subtitle language options are recorded.

For movies, the runtime and date of release is stored. If the movie has a sequel then
that information is also visible in the system. In the case of a TV show, each episode
of the show can be identified via a season number and episode number in conjunction
with the code of the show. Each episode also records the runtime and date of release.

Cast members of products are also recorded on the site, which stores their unique ID,
name and nationality. One actor has only one name and one nationality. If a cast
member acts in a product then the name of their role is recorded. It is possible for a
cast member to have multiple roles on the same product.

Accounts on ‘Home Cinema’ are required in order to use the service. When a user
registers for an account, the system stores their name, username, email, birthdate and
registration date, along with their subscription tier (either ‘Free’ or ‘Premium’). The
username or email identifies the account. When an account (of either tier) watches a
movie or TV show episode the information is stored in the system. If they stop
2

watching partway through a movie or episode, the timestamp of where they stopped
at is stored.

‘Premium’ accounts have some additional functionality ‘Standard’ accounts do not
have access to. Such accounts may become friends with other ‘Premium’ accounts.
They may also create multiple playlists containing any product on ‘Home Cinema’. The
name of each playlist must be unique within that account.

Based on the above UoD, you are required to undertake the following design tasks
and then document them in the provided template.
ER Diagram
Create an ER diagram using the notation taught in lectures to represent the conceptual
schema described by the Universe of Discourse. Depending on your design choices,
you may need to use the extended ER diagram. Please follow the naming conventions
taught in this course.

State clearly any assumptions you make regarding your design approach on your ER
Diagram. Please note that you cannot make assumptions to simplify or compromise
the completeness of the Universe of Discourse. If there are any points that need
clarification, in the specification of the Universe of Discourse as given above, you must
first attempt to clarify them with your tutor and/or lecturer.
Mapping
Map the ER diagram created above to a relational schema. Document the final schema
in the designated space in the provided template. Remember to include ALL your
foreign keys!
Functional Dependencies & Normalisation
List all non-trivial functional dependencies in your relational schema. Then based on
the FDs, identify any relations that are not in BCNF. Finally, decompose any relations
that are not in BCNF, to achieve BCNF.
2. Assessment
Each part of the assignment is allocated 100 points which will be converted into 15
course marks.

Part 1 Design

ER Diagram 50 points
Mapping 30 points
Functional Dependencies 10 points
Normalization 10 points
3


This assignment targets the following assessment criteria of this course:
1. Analyse, extract and structure information system requirements from a variety
of organizational contexts
2. Reason with the foundations of the relational data model to correctly
undertake relational database design
3. Express queries using the SQL language to provide correct and secure retrieval
of data from relational databases
4. Construct a small-scale information system in a relational database
management system
5. Perform information systems analysis and design in a group setting

3. Submission
Submissions will be done via Blackboard. Marking will be done through an electronic
marking tool called Gradescope, which will also be used for providing feedback.

Please use the supplied answer template for all answers. Your work must fit in the
predefined sections or it cannot be marked

Submit your assignment electronically via the provided links on Blackboard under the
Assessment folder.

For Part 1, submission should be made by ONE team member only. Extra submissions
will attract penalties.

4. Collaboration and Plagiarism
Database design is best done in teams, not only to share the workload but also to
share and exchange ideas and discuss design choices. Therefore, you are required to
work in a group of three. All students working in a group will be given a common mark.
Note that Part 2 of the assignment is done individually.

If you have any questions regarding acceptable level of collaboration with your peers,
please see either the lecturer or your tutor for guidance. Remember that ignorance is
not a defence!

The University has strict policies with regard to collusion and plagiarism. Penalties for
engaging in unacceptable behaviour can range from cash fines or loss of grades in a
course, through to expulsion from the University. You are required to read and
understand the policies on academic integrity and plagiarism in the course profile and
uq.edu.au/integrity. Note: Any posts on Piazza containing assignment specific
solutions/hints will be reported to the school.
4

5. Late Submissions
Late submissions will not be accepted at all, or if accepted, will incur a penalty of 20%
reduction each date late. Students who believe they have sound reasons for late
submission should refer to the course profile (section 5.3 on Late Submissions).