Topic: Entity Relational Model and Relational Model

Assignment

Due time:19/08/2024

Contents

TOC o "1-3" h z u 1. Overview: PAGEREF _Toc173760557 h - 2 -1.1 Assignment Background PAGEREF _Toc173760558 h - 2 -1.2 Academic integrity and plagiarism PAGEREF _Toc173760559 h - 2 -Section 1: PAGEREF _Toc173760560 h - 3 -1.1 PAGEREF _Toc173760561 h - 3 -1.2 PAGEREF _Toc173760562 h - 4 -1.3 PAGEREF _Toc173760563 h - 5 -Section 2: PAGEREF _Toc173760564 h - 6 -2.1 PAGEREF _Toc173760565 h - 6 -2.2 PAGEREF _Toc173760566 h - 9 -

Note:

All notation standards for relational mapping must be followed by following statements:

For creating the ER model must be followed by following statements:

1. Overview:1.1 Assignment Background

This assignment has two sections. The first section of this assignment will test your knowledge on conceptual modelling via the Entity Relationship (ER) model and ability to apply it for capturing important aspects of a system that need to be stored in a database. The second section of this assignment tests your knowledge on the relational model and your ability to map an ER model to a relational schema.

1.2 Academic integrity and plagiarism

In particular, you are not permitted to use generative AI tools to help you complete this assessment task.

Section 1:1.1 Using Draw.io to create an Entity Relational (ER) diagram or Extended Entity Relational (EER) diagram based on the following scenarios.

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.

Employee Permissions System: SecureSoft Solutions is a high-security software development company that specializes in creating secure software for government agencies. Due to the sensitive nature of their work, SecureSoft maintains a comprehensive employee management system to ensure strict access control and secure handling of internal data.

In this system, each employee is identified by their employeeID, and their full name (consisting of their first and last name), email, and date of birth (DOB) is recorded. Administrative employees, who are a special type of employee, also have their administration level and their privilege types stored in the database. Every employee must provide a credential (password) to log on to their internal systems. Each employee's credentials are uniquely identified by a credentialID. A hashed password and the timestamp of the last password change is also stored. SecureSoft Solutions is organised into multiple departments, each identified by a unique departmentID and name. Each employee must work in one or more departments.

Administrative employees have the authority to assign roles to other employees. Each role is uniquely identified by an ID and includes a name and brief description that outlines their job functions and responsibilities within the organisation. The system records a timestamp of when each role is granted.

Additionally, roles are defined to include specific access rights for data within particular departments. This means that each role can have different permissions for different departments, ensuring granular control over what actions an employee can perform in each department they are associated with. Each permission is uniquely identified by a permission ID and includes a grant type (such as view, edit, or delete)

1.2 Using Draw.io to create an Entity Relational (ER) diagram or Extended Entity Relational (EER) diagram based on the following scenarios.

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.

Ecommerce Pro is an online merchandising retailer that provides a diverse range of products and takes pride in maintaining competitive prices. To ensure their orders are received, packaged and delivered promptly, Ecommerce Pro employ a robust database system to manage their suppliers and customer base.

In their system, customers who purchase from their platform are identified by their username, and their first and last name, email, and phone number are recorded. The customers physical address details are also stored in the database; each address is uniquely identified by a combination of an address ID and the customers username. The state, suburb, postcode and street of the address is also stored in their systems.

Customers create orders in the system, which are uniquely identified by an order number and by the username of the customer. The subtotal and GST of an order are automatically computed and a shipping address is referenced for each order. Orders consist of one, or many, in-stock items. Each item is uniquely identified by it's Stock Keeping Unit (SKU), and relevant details such as product dimensions (including length, width and height), the items weight, description, and unit price. The system records the quantity of each item included in the customers order. Additionally, the platform identifies and maintains records of similar items that can serve as substitutes if an item is out of stock.

Given the highly competitive nature of the retail market, Ecommerce Pro will engage many suppliers to inventory the items in their store. Each supplier has their name, email, and phone number stored in the database and can be uniquely identified by a supplier ID. The system tracks the quantity of items provided by each supplier to ensure optimal stock levels and availability.

1.3 In order to sufficiently prepare for any future pandemics, Queensland Health is planning to implement their own system to manage and track vaccine trials. They have created the entity relationship diagram below for capturing the data requirements of the program.

Evaluate the following statements and provide your opinion on whether each statement (from A to F) is correct or incorrect. Please include a brief justification for each of your responses.

Example: Each vaccine must have a second dose or a preceding dose.

Example Answer: The statement is incorrect. The recursive WITH relationship on VACCINE allows for vaccines to be recorded as being either the first or second vaccine as part of a two-shot program. However, participation in this recursive relationship from both sides is partial, meaning that a vaccine does not need to be recorded as being part of a two-shot program.

A: Each participant with a healthcare condition is overseen by a health worker during vaccination.

B: Each participant is administered a vaccine from a unique batch.

C: A vaccine is uniquely identified by the combination of the vaccines name, brand and ID.

D: A participant can both have a health condition and be in a special age category.

E: Two different batches of vaccines can have the same batch number.

F: A participant can be overseen be several healthcare workers.

Section 2:2.1 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 from A to F. 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. Additionally, as this assignment is based on 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.

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.

ER Diagram:

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:

Question and example: Insert (12345, Elaine Feng, I am not a date., NULL) into Customer.

Will this operation cause an integrity constraint violation? Yes / No

If yes, name the integrity constraint(s) which will be violated:

Domain Constraint Violation