Blog

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.