Relationship and Relational Modelling: Conceptual and Logical Database Design DBDM3025

1Section 1: Conceptual Modelling with ER/EER Diagrams

1.1 ER Diagram for Employee Permission System

An entity relationship diagram is created for the employee permission system at Secure Soft Solutions. This employee permission system for Secure Soft Solutions is used to capture detailed information about the employees personal details, permissions, roles, and departments. The entity relationship diagram for the employee permission system will be created with the help of Draw.io. The created ER diagram for Secure Soft Solutions is demonstrated in Figure 1.

Figure 1: ER diagram

According to the ER diagram in Figure 1, it is noted that there are six entities that are created for the employee permission system at secure soft solutions, such as Employee, employee credentials, administrative employees, department, roles, and permission.

1.2 ER Diagram for an E-commerce Platform

Next, it is required to create an entity relationship diagram for the e-commerce pro. This robust database system for e-commerce pro is used to capture detailed information about the customers information, orders, address, items, and suppliers. The entity relationship diagram for the robust database system will be created with the help of Draw.io. The created ER diagram for the E-commerce Pro is depicted in Figure 2.

According to the ER diagram in Figure 2, there are five entities that are created for the robust database system of e-commerce pro such as, customers information, orders, address, items, and suppliers.

1.3Six Statement Evaluation

Here, it is time to evaluate the provided six statements about the provided ER diagram for Queensland Health's vaccine tracking system, and determine if the provided six statements are correct or incorrect.

A: Each participant with a healthcare condition is overseen by a health worker during vaccination.
Answer: The statement is correct, because, the health workers oversee heath condition of each participant during vaccination.

B: Each participant administered a vaccine from a unique batch.
Answer: This is an incorrect statement, because each participant administered a vaccine based on the number of batches. For making sure that the data gets a larger scope, a participant will not be provided multiple doses from the same batch to administer the doses.

C: A vaccine is uniquely identified by the combination of the vaccines name, brand and ID.
Answer: This is a correct statement, because a vaccine is identified uniquely based on its name, brand, the year it was created, and based on how the vaccines are trialled in combination with the other vaccines for different doses.

D: A participant can both have a health condition and be in a special age category.
Answer: The above statement is correct. A participant can both have a health condition and be in a special age category or range. Because, depending on the participants health conditions, they could be categorized into risk category.

E: Two different batches of vaccines can have the same batch number.
Answer: The statement is incorrect, because one vaccine can contain various batches of doses that each batch can produced. So, as a result, each batch must have a unique number to identify its batch. Therefore, two different batches of vaccines cannot have same batch number.

F: A participant can be overseen be several healthcare workers.
Answer: It is an incorrect statement, because every single participant having health conditions is assigned to a health worker, so they get personalised care depending on their trial jab. Hence, a participant cannot oversee several healthcare workers.

2Section 2: Relational Modelling and Integrity Constraints

2.1Answer the Questions

A: Insert (EEDD, 2346, Frozen, PG-13) into Movie.

Will this operation cause an integrity constraint violation? Yes

Name the integrity constraints: Domain constraints

Domain constraints Explanation:

This tuple contains PG-13 on the rating attributes for the Movie table. As per the provided movie data, it is clear that the domain for the rating attribute contains only a G, PG, M, MA15+ or R18+. The provided rating value is PG-13, which is not in the provided options. Hence, this operation would violate domain constraint.

B: Insert (96721, NULL, 4895, 2022-02-22 06:14:56) into Previews.

Will this operation cause an integrity constraint violation? Yes

Name the integrity constraints: Key constraints
Domain constraints Explanation: The key constraints does not allow the duplicate value of the primary key value. This tuple already consists of 96721 rows in the preview table. So, it is not allowed. Because all the rows must be unique. Hence, this operation would violate the key constraints.

C. Modify the tuple (10234, ABCD, 1234, 2022-02-20 19:12:56) in the Previews table and change it to (10234, ABCD, 1234, 2022-02-20 23:12:56).

Will this operation cause an integrity constraint violation? No

D. Delete any tuple in Previews where the timestamp is after 2022-02-20 23:12:56.

Will this operation cause an integrity constraint violation? No.

E. Modify the tuple (10235, Zhihui Wang, 2003-04-26, NULL) in the Customer table and change it to (10234, Zhihui Wang, 1998-09-12, 24444).

Will this operation cause an integrity constraint violation? Yes

Domain constraints Explanation: The key constraints does not allow the duplicate value of the primary key value. This tuple already consists of 10234 rows in the customer table. So, it is not allowed. Because all the rows must be unique. Hence, this operation would violate the key constraints.

F. Insert the tuple (, , UP, R18+) into Movie.

Will this operation cause an integrity constraint violation? Yes

Name the integrity constraint: Entity Integrity Constraints

Domain constraints Explanation: The entity integrity constrains does not allow a primary key to have null values, because it is used to identify individual rows in the table uniquely. Based on insert the tuple, it inserts the null value in the primary key. Therefore, this operation would violate entity integrity constraint.

2.2 Relational Schema

In this section, the ER diagram will be converted into a relational schema as demonstrated below.
Post (ID, Price, Comments, Picture)
post references user.id
User (ID, Bio, DOB, FirstName, LastName, email)
User (ID, Trial, EndDate)
User (ID, Inactive, code, LastActive)
User (ID, Premium, chat)
User references chat.id
Chat (ID, Name, Timestamp, Description)
Chat references user.id
Chat (ID, Public, secure)

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back!
Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects. Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank