INFS1200 / INFS7900 Assignment 1
Due: Thursday 28 March, 2024 @ 04:00 PM Version: 1.0
Weighting: 25%
1. Overview
The purpose of this assignment is to test your ability to use conceptual modelling, such as ER and EER diagrams, and the relational model, to capture important aspects of a system which need to be stored in a database. The assignment comprises two sections:
- Section 1 of this assignment will test your knowledge on conceptual modelling via the Entity Relationship (ER) model and ability to apply it to capture important aspects of a system which need to be stored in a database (Module 1).
- Section 2 of this assignment tests your knowledge of integrity constraints in the relational model and your ability to map an ER model to a relational schema (Module 2).
This assignment must be completed individually.
2. Submission
All submissions must be made through an electronic marking tool called Gradescope, which will also be used for marking and providing feedback.
You must record all your answers in the spaces provided in this document and upload it to Gradescope in pdf format. Altering the format or layout of this document in anyway will attract penalties. You may however add landscape images in the submission boxes without changing the orientation of the page.
All submissions must have the name and ID boxes filled out in order to be identified.
3.Academic Integrity
The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour can range from cash fines or 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!
Generative AI: you are permitted to use generative AI tools to help you complete this assessment task. However, if you do, please provide complete copies of your interactions with the AI tool as a separate upload on Gradescope (a second submission portal will be provided).
Please note that if you use generative AI but fail to acknowledge this by submitting your interactions, it will be considered misconduct as you are claiming credit for work that is not your own.
4. Task
This assignment contains two sections:
Section 1 focuses on Conceptual Modelling and has 3 parts.
- The first two contain a brief UoD that provides contextual information regarding a system or organisation, and your task is to produce an ER diagram capturing the UoD.
- The third contains an ER diagram with questions testing your ability to interpret its meaning.
Please note that all UoDs and ER diagrams included in this assignment are fictional. If the UoD is unclear regarding specific aspects of the brief you may note assumptions on your ER diagram. However, please beware that your assumptions must not conflict with or violate any aspects of the UoD.
Section 2 focuses on the Relational Model, and has two parts.
- The first 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.
- The second part provides you with an ER diagram, and 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.
5. Marking
Assignment 1 is worth 25% of your final course mark:
. Section 1 is worth 12 marks, and
. Section 2 is worth 13 marks.
Grading and feedback will be provided through GradeScope.
Late Submission: As per the ECP, assessment items received after the deadline will be subject to a late penalty of 10% per 24 hours of the maximum possible mark for the assessment item, up to 7 periods of 24 hours. Assessment items received more than 7 periods of 24 hours after the deadline will be subject to a 100% penalty. All deadlines are hard.
Section 1 – Conceptual Modelling (12 Marks)
Question 1.1 This question contains a brief UoD that provides contextual information regarding a system or organisation. You need to create an Entity Relational (ER) diagram, or Extended Entity Relational (EER) diagram based on the UoD. If the UoD is unclear regarding specific aspects of the brief you may note assumptions on your ER diagram. However, please beware that your assumptions must not conflict with or violate any aspects of the UoD.
Business contacts database: Terrific Triple Glazing are a wholesale supplier of triple-glazed windows and doors for advanced thermal and acoustic performance applications. Their customers are other companies that install glazing in commercial or large-scale developments. As such, they employ a team of dedicated sales agents to handle customer relationships and have a sales contact database to store relevant data. (Note that this is only a business contacts DB, and that orders are processed through in a separate purchasing system.)
In this database, Terrific Triple Glazing sales agents' name, date of birth, phone number are recorded, and each agent is identified by their unique staff ID. Similarly, customers' business name, business address, and main office phone number are recorded, and they are given a unique customer ID.
Because the best business relationships are person-to-person, for every business customer at least one contact person's details are also collected, which includes a personal contact first name, second name, and gender, their personal contact email and contact phone number. These personal contacts are uniquely identified by their business’ customer ID and their own name. Terrific Triple Glazing sales agents are then linked to the business contacts that they have established a sales relationship with.
Question 1.2. This question contains a brief UoD that provides contextual information regarding a system or organisation. You will need to create an Entity Relational (ER) diagram, or Extended Entity Relational (EER) diagram based on the UoD. If the UoD is unclear regarding specific aspects of the brief you may note assumptions on your ER diagram. However, please beware that your assumptions must not conflict with or violate any aspects of the UoD.
Tennis Lessons: Sam Tennyson’s Tennis Centre (STTC) welcomes players of all ages and abilities, providing them with tailored coaching programs to enhance their skills on the court. Experienced players often naturally progress to become coaches, with some members coaching and playing at the centre for many years. Because it is a large operation, STTC uses a database to keep track of its players, coaches, courts and coaching sessions.
Players’ and coaches’ name, consisting of their first and last name, age, contact number, email, and a unique member ID are recorded. The database also stores a player’s skill level and preferred hand. Coaches have their experience level recorded (i.e. junior or senior coach), as well as a list of any specialisations they have (e.g. beginner coaching, high-performance sessions, doubles coaching, etc). As a sporting facility, STTC is also required to store emergency contact details comprising a name and phone number for all players and coaches.
STTC coaching sessions are run on one or more courts with one or more coaches, depending on demand for sessions. Each coaching session is characterised by a unique session ID, a start time and an end time, a session type (beginner, intermediate, advanced) and the maximum number of players that can attend. Every coaching session can have multiple players allocated to it, and a player can attend multiple coaching sessions. Courts have a unique court number, and their surface type, lighting availability, rental rate, availability status, and whether it is an indoor or outdoor court is also stored.
Question 1.3. Consider the entity relationship diagram below for a tour operator:
Evaluate the following six statements and provide your opinion on whether each statement is correct or incorrect. Please include a brief justification for each of your responses.
Example: Each Package has a duration of a unique number of days length.
|
The statement is incorrect. The text in the oval representing the attribute "numDays " is not underlined, indicating it is not unique or identifying.
|
1.3.a. Packages involve trips to multiple destinations.
1.3.b. Customers can choose one or more destinations.
1.3.c. Tours are shared by different packages.
1.3.d. Destinations are uniquely identified by their place name.
1.3.e. Customers can only be identified by their CID (customer ID).
1.3.f. Customers must have at least one phone number recorded.