Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
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.