The University of Queensland
Brisbane QLD 4072 Australia
ABN: 63 942 912 684
CRICOS PROVIDER NUMBER 00025B

INFS1200 Module 2 Assignment
Due: April 22nd, 2022 @ 01:30 PM AEST
Weighting: 15%
Full Name Student ID

1. Overview
The purpose of this assignment is to test your ability to apply relational model principles, such as identifying integrity
constraint violations in common database operations and mapping from ER to relational schema. The assignment is
split into three sections. Sections A and B are to be complete individually using the assignment template provided.
Section C (the RiPPLE Component) is to be completed individually on the RiPPLE platform.
2. Submission
All submissions for Section A and Section B must be made through an electronic marking tool called Gradescope,
which will also be used for providing feedback. You must record all your answers in the spaces provided in this
document. Altering the format or layout of this document in anyway will attract penalties. All submissions must have the
above boxes filled out in order to be identified.

Section C is to be completed through the RiPPLE platform (link available on Blackboard).
3. Marking
This assignment is worth 15% of your course mark, with Section A worth 6.25%, Section B worth 6.25% and Section
C worth 2.5%.
4. Plagiarism
The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour range from
loss of grades in a course through to expulsion from UQ. You are required to read and understand the policies on
academic integrity and plagiarism in the course profile (Section 6.1).

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!
5. Task
This assignment contains three different sections, each worth 7.5%. Section A contains a relational model schema
along with other contextual information. Based on this information, you will need to analyse different database
operations. For each, identify which, if any, integrity constraint(s) would be violated by these operations and how they
violate said integrity constraint. An example has been provided below. Section B provides you with three ER diagrams
based off sample solutions from Assignment 1. For each of these diagrams, your task is to perform relational mapping
and state only the final relational schema for the ER diagram, including any foreign keys which were created during the
process. Finally in Section C you will need to complete some specified tasks on the RiPPLE platform.



CRICOS Provider No: 00025B 2

Section A – Integrity Constraints
The following is an extract from a postal tracking system. The original ER diagram has been provided to highlight
additional key constraints which are not intuitive from the relational schema. Using the ER diagram, contextual
information, relational schema and instance data provided, answer the following questions. An example of how to
answer the question is provided below. You may make assumptions on domain based on the instance data.
Note: Do not take into consideration changes which may have been made by operations in earlier questions.
• You may assume that all attributes in the database have NULL as an acceptable value in their domain except
in the cases where the contextual information indicates otherwise.
• You should not assume or imply other semantic constraints which are not explicitly mentioned/implied in the ER
diagram, UoD or relational mapping.
• As this assignment is assessing your knowledge of the relational model, you should not base your
understanding of what would/would not qualify as a constraint violation on how a DBMS (such as MySQL) would
handle the operation.
• You may assume there are no cascading actions which occur update or deletion of foreign key values.

ER Diagram:










Contextual Information:
A relational database has been setup to track customer browsing activity for an online movie streaming service. Movies
may be identified by a unique code which consists of a four-character prefix and four-digit suffix. Additionally, each
movie is assigned a content rating which must be one of the following options: “G”, “PG”, “M”, “MA15+” or “R18+”. A
user may preview a movie before they stream it however, they cannot preview a movie after they have started to stream
it. Finally, the database should also not allow for the same customer to stream two movies at the same time. (This
ensures no two streaming events overlap with each other). You may assume “Duration” refers to the time in seconds a
customer spent streaming a particular movie after the “Timestamp”.



Relational Schema:
Customer [id, name, bob, bestFriend]
Customer.bestFriend references Customer.id
Movie [prefix, suffix, name, rating]
Previews [customer, moviePrefix, movieSuffix, timestamp]
Previews.customer references Customer.id
Previews.{moviePrefix, movieSuffix} reference Movie.{prefix, suffix}
Streams [customer, moviePrefix, movieSuffix, timestamp, duration]
Streams.customer reference Customer.id
Streams.{moviePrefix, movieSuffix} reference Movie.{prefix, suffix}

INSTANCE DATA ON NEXT PAGE




CRICOS Provider No: 00025B 3

Instance Data:















Example question: Insert (12345, ‘Elaine Feng’, ‘I am not a date.’, NULL) into Customer.




















SECTION A QUESTIONS START ON NEXT PAGE
Customer
id name dob bestFriend
10234 Grace Smith 1998-09-12 NULL
23444 Jack Smith 2000-01-01 25321
24444 Levi Ramos 2001-08-23 26789
10235 Zhihui Wang 2003-04-26 NULL
25321 Yeseul Seo 1999-09-23 23444
26789 Samantha King 2002-12-06 24444
96721 Yumi Kim 1976-03-19 NULL
Previews
customer moviePrefix movieSuffix timestamp
10234 ABCD 1234 2022-02-20 19:12:56
23444 CDEF 2345 2022-02-20 19:42:08
24444 ABCD 1234 2022-02-20 19:45:31
10235 MILK 4895 2022-02-20 19:12:56
10235 ABCD 1234 2022-02-21 00:32:16
10234 CDEF 2345 2022-02-21 01:47:12
25321 CDEF 2345 2022-02-21 09:29:23
96721 RTEH 4895 2022-02-21 10:49:30
Streams
customer moviePrefix movieSuffix timestamp duration
10234 ABCD 1234 2022-02-20 20:03:42 1623
25321 CDEF 1234 2022-02-20 20:04:11 4593
96721 CDEF 1234 2022-02-20 20:04:11 4593
24444 ABCD 1234 2022-02-20 20:05:31 967
23444 MILK 4895 2022-02-20 21:57:59 1243
24444 ABCD 2345 2022-02-20 22:42:01 2190
10235 MILK 4895 2022-02-21 03:45:02 1523
10234 ABCD 1234 2022-02-21 03:45:02 567

Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:
Domain Constraint Violation

If yes, explain how the integrity constraint(s) would be violated:

Domain Constraint Violation: This tuple contains a non-date value for the dob attribute,
specifically ‘I am not a date.’ Based on the instance data provided, it is clear the domain for the
attribute is date values and hence this operation would violate domain constraint.






Movie
prefix suffix name rating
ABCD 1234 UP PG
CDEF 1234 WALL-E G
CDEF 2345 Titanic M
ABCD 2345 Iron Man M
EEDD 2345 Speed Racer PG
MILK 4895 Inception M
RTEH 4895 The Notebook M




CRICOS Provider No: 00025B 4

A.1 Insert (‘EEDD’, 2345, ‘Frozen’, NULL) into Movie.



















A.2 Insert (NULL, ‘ABCD’, 4895, ‘2022-02-22 06:14:56’) into Previews.

















A.3 Insert (‘RTEH’, 2345, ‘The Notebook’, ‘MA15+’) into Movie.















Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:

Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:
Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:





CRICOS Provider No: 00025B 5

A.4 Modify the tuple (25321, ‘CDEF’, 1234, ‘2022-02-20 20:04:11’, 4593) in the Streams table and change
it to (25321, ‘CDEF’, 2345, ‘2022-02-20 20:04:11’, 4593).



















A.5 Modify the tuple (10234, ‘Grace Smith’, ‘1998-09-12’, NULL) in the Customer table and change it to
(10235, ‘Grace Jeon’, ‘1998-09-12’, 24444).

















A.6 Modify the prefix attribute and set it to NULL for all the tuples in Movie relation with name =
“Notebook”.

Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:


Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:


Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:





CRICOS Provider No: 00025B 6

A.7 Delete any tuple in Streams where the duration is greater than or equal to 1700.



















A.8 Delete any tuple in the Customer table with a dob after ‘2001-08-17’.



















A.9 Modify the tuple (10234, ‘ABCD’, 1234, ‘2022-02-21 03:45:02’, 567) in the Streams table and change
it to (10234, ‘MILK’, 4895, ‘2022-02-20 20:29:42’, 567.1).




















Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:


Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:


Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:





CRICOS Provider No: 00025B 7

A.10 Insert the tuple () into Movie.

























SECTION B QUESTIONS START ON NEXT PAGE

Will this operation cause an integrity constraint violation? Yes / No


If yes, name the integrity constraint(s) which will be violated:


If yes, explain how the integrity constraint(s) would be violated:





CRICOS Provider No: 00025B 8

Section B – Relational Mapping
The following three questions all contain ER or EER diagrams from the Module 1 Assignment solutions. For each
diagram, complete ER to relation mapping and state your final relational schema, including any foreign keys which were
created in the process.
• Your answers must follow the format guide posted on blackboard.
• All foreign key definitions must directly follow the table for which they are defined.

B.1

































CRICOS Provider No: 00025B 9

B.2































































CRICOS Provider No: 00025B 10

B.3


























































CRICOS Provider No: 00025B 11

Section C – RiPPLE Task
Using the RiPPLE online software, you must complete the following activities before the assignment due date:
• Resource Creation: Create one or more effective resource. For a learning resource to be considered as
effective it needs to pass a moderation process which is administered by your peers and the teaching team.
Teaching staff will be spot-checking to review moderations performed by just peers and change the outcome if
necessary.

• Resource Moderation: Moderate 4 or more resources effectively. An effective moderation means that you
have completed the moderation rubric and have provided a detailed justification for your judgement as well as
constructive feedback on how the resource can be improved. Simply saying a resource is “good” does not
qualify. Again, teaching staff will be spot-checking the quality of moderations and change the outcome when
necessary.

• Answering Questions: Answer 10 or more questions correctly. To answer a resource correctly your first
response must be correct. You can attempt as many questions as you want, and incorrect answers do not
count against you. Only answers from the Practice tab are counted. Answering in-class RiPPLE activity
questions does not count towards questions answers.
These tasks are to be completed through the RiPPLE platform, via the link available on Blackboard.
Note: For the above three activities, the resources you create, moderate and answer must be in the following
categories on RiPPLE:
• Relational-model

• Map-ER-Schema
Creating, moderating or answering questions from other categories will not be counted towards your mark for the
RiPPLE component of this assignment.