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”.
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.