This assignment contains two sections:

Section 1 focuses on Conceptual Modelling and has 3 parts, each containing a brief UoD that provides contextual information regarding a system or organisation. Please note that all UoDs 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.

Section 1 – Conceptual Modelling                                                 (12 Marks)  1.1 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.

On a large online retail platform, different users can log in using their unique identifiers and shop for various items. Each of these items comes with a unique identification code, name, price, and detailed description.  Users, apart from their unique  identifiers, also  provide their name, email, and shipping address. These users can make multiple purchases on different dates, with each transaction assigned an identification code unique to the user, along with recording the date of the transaction and the total cost. Each transaction can consist of one or more items, and the same item could be part of various transactions made by the same or different users.

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.

In a busy medical facility, individuals are treated and identified by a unique code, and their names and home addresses are kept on record. There are two types of individuals, one who stays in the facility and is assigned a room number (Inpatient), and the other who visits the facility and is given a date for their next appointment (Outpatient). Additionally, the medical facility keeps medical records for all patients. Each record is associated with a unique number, the date it was created, and a diagnosis.

The medical facility also maintains a detailed catalog of health professionals, each with their unique identifiers  and  specialty  fields.  These  professionals  are  involved  in  the  administration  of  various treatments, each of which is given a unique code, a name, and a detailed description. A treatment can be administered by a health professional to an individual at a given date and time.

1.3. Consider the entity relationship diagram below for a new Fish and Chips store in the St Lucia campus at UQ.

Evaluate the following 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 item on the menu has a unique number but item names are not unique

1.3.a. Chips are offered in multiple sizes and can have multiple seasonings.

1.3.b. The system stores a recommended fish to go along with some drink items.

1.3.c. Alcoholic drink can only be mixed with a non-alcoholic drink.

1.3.d. The combo deal includes a fish, a drink and chips.

1.3.e. Discounts can be given on combos that only have a drink and a fish.

1.3.f. Discounts are captured as a negative number representing amount to be saved

Section 2 – The relational data model                                           (13 Marks) 2.1. Data analytics projects need to consider the integrity or the correctness of the data that they are provided. Errors may be introduced in case integrity constraints are not implemented in the databases their data is sourced from. The question below provides contextual information, relational schema and instance data provided for an example related to Olympic games. For each part of the question, determine whether executing the provided query would trigger an integrity constraint violation. Note: Do not take into consideration changes which may have been made by operations in earlier questions.

Contextual Information:

Our database represents a subset of data related to the Olympic Games. It captures essential details about countries, sports, athletes, events, and the medals won in these events.

1.   Countries table lists all the participating countries in the Olympic games. Each country has a unique identifier (CountryID), a name (CountryName), and is associated with a specific

geographical region (Region).

2.   Sports table contains information about various sports played in the Olympics. Each sport has a unique identifier (SportID) and a name (SportName).

3.   Athletes table records all athletes participating in the games. Each athlete is uniquely identified by an AthleteID, and they also have a name (AthleteName). They are associated with a specific country, represented by the CountryID, which links to the Countries table. An athlete can only

compete in one sport.

4.   Events table represents the events held in different sports. Each event is uniquely identified by an EventID and is associated with a specific sport, represented by the SportID, which refers to  the Sports table.

5.   Medals table archives the allocation of medals. Each entry, denoting a medal, is uniquely

associated with a particular athlete (AthleteID) and a specific event (EventID), and it documents the category of the medal (MedalType - Gold, Silver, or Bronze) won by the athlete. The

AthleteID references the Athletes table, and the EventID refers to the Events table.

Relational Schema:

Countries [CountryID, CountryName, Region]

Sports [SportsID, SportName]

Athletes [AthleteID, AthleteName, CountryID]

Events [EventID, SportID]

Medals [AthleteID, EventID, MedalType]

Foreign Keys:

Athletes.CountryID references Countries.CountriesID

Events.SportID references Sports.SportID

Medals.AthleteID references Athletes.AthleteID

Medals.EventID references Events.EventID

Instance Data (clean data examples):

Describe what happens if you attempt the given DB modifications:

.     Write “success” if there is no integrity constraint violation.

.     Otherwise, state which constraint will be violated with reference to the attributes, for example “A

foreign key constraint will be violated because XYZ is not a primary key in table T.”

Example: Insert (1, 'Liam Johnson', 1) into Athletes.

2.1.a. Insert (1, 1, 'Platinum') into Medals.

2.1.b. Delete (2, China, Asia) from Countries.

2.1.c. Update (3, 3, Bronze) in Medals to (2, 3, Bronze)

2.1.d. Update (3, 3, Bronze) in Medals to (3, 2, Bronze)

2.1.e. Update (5, 5, Bronze) in Medals to (5, Null, Bronze)

2.2. The description and ER diagram below capture information about UQ’s new Got Talent show, which is being launched in 2023 to bring some fun and competition to campus. Convert the given ER to a relational schema including any foreign keys which were created in the process.

Contestants are assigned an ID to go with their name & group size when they register to the competition. Their acts are classified as either Variety, Music, or both. Variety acts record the specific type of act, as well as any props required, while the music acts record the style, song, and song length for scheduling purposes. UQ’s Got Talent staff have an ID, name and phone number recorded in the system. As this is a large event, there will be both paid and volunteer staff. Each act will have staff assigned to assist them with the process in several different roles. Paid staff will be responsible for managing the event and volunteers, or they will be specialist performers engaged as coaches for the musical acts. Each volunteer staff member will have one member of administrative staff assigned to be their manager. Each musical contestant will have a single coach assigned to them