1 School of Computer Science COMP9120 Database Management Systems Assignment 1: Conceptual Modelling & Logical DB Design Group assignment (12%) Introduction The purpose of this assignment is to provide you with experience in conceptual and relational database modelling. You are given a domain description for the Sydney Entertainment & Theatres (SET). There are 2 high level tasks in this assignment: Create an Entity Relationship Diagram (ERD) that captures the business concepts and requirements conveyed in this description, Translate your ER diagram into a logical database design including relational database schema creation, key constraints and integrity constraints. This is a group assignment for teams of 3 people per group. You must be enrolled in an assignment group on Canvas. You must inform the unit coordinator if you have not formed a group by Week 6. Please also keep an eye on your email and Ed for any announcements that may be made. Submission Details The submission of your solution is due at 11:59pm on Friday 30/04/2021 (end of Week 8). You must submit the items for submission (detailed below) via Canvas. Items for submission Please submit your solution to Assignment 1, in the ’Assignment’ section of the unit’s Canvas site by the deadline, including EXACTLY THREE files: 1. Firstly, you are required to submit your conceptual model in the form of an E-R diagram using the lecture notation, formatted as a PDF document (.pdf file suffix). Please justify your choices for entity types, relationship types, attributes, primary keys, constraints and design specialities. 2. Secondly, you should submit an SQL file (.sql file suffix) containing all DDL statements necessary to fully instantiate a working database based upon your ER diagram, and DML statements to populate each relation. Your file should run without errors in PostgreSQL 9.5. You can annotate your statements using ‘–‘ at the start of lines for comment. You should group your statements for ease of reading (e.g. by keeping all table constraints within the relevant CREATE TABLE statement rather than declaring them externally, if possible). 3. Thirdly, you should submit another pdf document (.pdf file suffix) including the relational model (RM) diagram that provides a visual model of your database schema. The figure below summarises the syntax to use for the RM diagram. RelationA keyA attrib1 fkey RelationB keyB attrib2 COMP9120 Assignment 1 2 Task 1: Domain Description for Entity Relationship Diagram (ERD) Modelling The Sydney Entertainment & Theatres (SET) is a locally owned theatre operator and production investor, with numerous theatre venues scattered across Australia. As part of its recovery from COVID-19 pandemic and in order to enhance its digital footprint, SET has assigned you the task of developing a new database system, starting from a conceptual model described in an ER diagram that captures all the concepts described below. SET participates in the Dine & Discover, a major COVID-19 stimulus program which the Government recently kicks off to encourage the community to get out and about, supporting entertainment and dining businesses and stimulating spending in the economy. The program provides four vouchers of $25, worth a total of $100, to every adult to spend on entertainment and dining, including theatres, cinemas, restaurants, theme parks, and most recreational activities. SET organises a series of theatre productions like Disney’s Frozen, Romeo and Juliet, and The Lion King, which are performed across their collection of theatres. Customer interested in booking to see an upcoming performance of one of these productions should be able to check seating availability for their desired performance, and make a booking for one or more available performance seats. Each theatre production at SET has: a name, description, production date and “production seat cost”; and also has a set of scheduled performances. Each performance will have planned start and end dates/times, and will take place in a certain theatre. Each theatre has a name, address, postcode, capacity, and a description, and is made up of 1 or more seating sections, where a seat section is composed of 1 or more seats. Different theatre seat sections will have distinctive views and comfort levels, and hence will have a different “section seat cost” associated with it. Customers who wish to book one or more performance seats with the SET must provide details about themselves including their first name, last name, date of birth, mobile number, and email address. A booking placed by a customer represents an association between the customer, a chosen performance, and the available seats selected by the customer for that performance. The performance date and time chosen by the customer must be an event occurring in the future i.e. after the booking date/time. The total cost of the booking will be calculated as the sum of the cost of each seat in the booking, and the cost of a seat in the booking is calculated as the sum of the “section seat cost” and the “production seat cost” described earlier. SET should always be able to justify and indicate how they calculated the total cost of each booking, including how section seat cost and production seat cost are used to calculate it. To complete a booking, customers must provide information about the payment method they wish to use for their booking. The total booking cost amount can be split across more than one payment methods. Depending on which payment method is selected for how much, the following details must be provided: (a) For a Dine & Discover voucher: the voucher unique code and voucher expiry date, (b) For a credit card: the card number, cardholder name, card expiry date, and CVV, (c) For a SET gift card: the 15-digit card number and PIN. Dine & Discover vouchers are single use, and if less than the $25 value per voucher is used, the remaining value is forfeited and can no longer be used later nor reimbursed by SET. Task 2: Relational Database Design & Modelling Your second task is to design and create a relational database schema based on the Entity Relationship Diagram (ERD) modelled from the first task. In particular, your solution should include: Tables and attributes with appropriate data types to capture all information in the model (please use the same names as in your ER diagram for naming tables and attributes); Appropriate PRIMARY KEY, UNIQUE, FOREIGN KEY constraints for all tables; Correct foreign key specifications including ON DELETE clauses where suitable; Appropriate additional integrity constraints expressed by means of NOT NULL or CHECK clauses; INSERT statements to populate each relation with at least one record, to demonstrate a database instance consistent with the ER model. COMP9120 Assignment 1 3 Additional details In addition to the model captured through your ER diagram, the following details apply: 1. Fields in a tuple related to dates and times should always have values. 2. All fields in a tuple relating to details about a name (e.g. customer name, theatre production name, etc.) and payment methods should always have values. 3. The total cost of a booking, “section seat cost” and “production seat cost” should always have values greater than zero. 4. Customers must have a specified mobile number. 5. The address postcode of a theatre should be between 800 to 9999. Escaping PostgreSQL keywords in DDL If you need to escape PostgreSQL keywords like “Table”, you will need to use double quotes. e.g. CREATE TABLE “Table” (…); Q&A Q: How to draw the link from foreign key in a table to its referenced candidate key in another table if the foreign key contains more than one attributes A: You should draw it in a similar way to the following RM diagram (specifically, see the Sell table). Marking This assignment is worth 12% of your final grade for the unit of study. Your group’s submission will be marked according to the attached rubric (see last section of this assignment description). Group member participation If members of your group do not contribute sufficiently, you should alert the unit coordinator as soon as possible. The course instructor has the discretion to scale the group’s mark for each member as follows: Level of contribution Proportion of final grade received No participation. 0% Full understanding of the submitted work. 50% Minor contributor to the group’s submission. 75% Major contributor to the group’s submission. 100% COMP9120 Assignment 1 4 Marking Rubric Your submissions will be marked according to the following rubric, with a maximum possible score of 12 points. Novice (0 – 0.5 pt) Competent (1 – 1.5 pts) Proficient (2 pts) ERD Notation & Core Model Big mistakes in the usage of ER notation. Less than competent model of the given scenario. Good usage of E-R notation with a few mistakes. Some entities, relationships, or attributes cannot be correctly captured by the model. Proficient usage of the E-R notation. The core model was very well designed, and all the main entities, relationships and attributes can be correctly captured by the model. ERD Constraints Many constraints are incorrectly captured in the model. No constraints captured at all. Some constraints (key / total participation constraints on relationship types, etc.) are correctly included in the model, but with minor mistakes All appropriate constraints are modelled correctly ERD Design Specialities Majority of design specialities used are inappropriate or incomplete. No design specialities are used. At least one useful ISA, weak entity or aggregation used appropriately. Minor or no mistakes on design specialities used. All design specialities are used appropriately Relational Mappings Less than competent schema of the given scenario All main entities and relationship mapped correctly to relations, with reasonable choice of data type for most attributes The core model was very well mapped to a relational schema and good choice of data types for all attributes Key Constraints & Semantic Constraints Major issues with key constraints, or no key constraints captured at all. Major issues with integrity constraints, or no integrity constraints given. Primary keys and foreign keys were defined appropriately, but with minor mistakes. Integrity constraints such as CHECK or NOT NULL were defined correctly, but with minor mistakes. All necessary primary keys and foreign keys were defined correctly, including appropriate ON DELETE clauses. All necessary integrity constraints for the model were defined correctly. Example Data & RM Diagram No example data given or yielded multiple errors. No RM diagram submitted, or major issues with the RM diagram. Some table example data missing or generated an error. RM diagram does not exactly match the relational schema created by the submitted SQL file. Database fully populated with a consistent and correct set of data. RM diagram exactly matches the relational schema created by the submitted SQL file (Note: semantic constraints and example data are not required in the RM diagram).
