Database Design Assessment Answer

TASK 1

An Entity-Relationship Model (ERM) needs to be created in order for Exercise Central, an Australian fitness company, to effectively manage data regarding staff, clients, and exercise plans. Each exercise plan should have a unique plan number and a description, and the system should be able to track the various general exercises in each plan. Additionally, staff members should be able to create individualized exercise plans with specific intensities, repetitions, sets, intervals, and days per week, and their specialties should be recorded. Each customer ought to have a distinct number, name, address, and phone number in order to enroll in courses, which are made up of specific exercise plans. Customers can enroll in courses. The beginning date of each course ought to likewise be recorded.

Relationships:

Exercise Plan and Staff: One-to-Many Relationship

• Multiple exercise plans can be created by staff
• Only one member of staff can develop an exercise plan(Academy., 2020).

Exercise Plan and Course: Many-to-Many Relationship

• There may be many exercise plans in a course.
• Many courses can include an exercise plan.

Staff and Course: Many-to-Many Relationship

• A Staff part can oversee many Specific Activity Plans that are essential for various Courses
• A member of staff can oversee numerous Specialized Exercise Plans.
• There may be numerous specialized exercise plans for a course(Sparks, 2021).
• A Specific Activity Plan can be overseen by many Staff individuals.

Course and Customer: Many-to-Many Relationship

• Numerous Clients may enroll in a Course.
• A customer can sign up for multiple Courses

TASK 2

PART A

Assumptions:

• EmployeeID is one of a kind for every specialist.
• Each vehicle has its own unique registration number.
• The same issue can arise repeatedly for the same vehicle or multiple vehicles
• The same component can be offered by multiple suppliers at distinct prices and descriptions.
• PartNo is unique to every component(Fowler, 2011).

Explanation:

• Because multiple issues can be discovered during a service, the relationship between the Service entity and the Issue entity is one-to-many.
• Because a single vehicle can have multiple services, the Vehicle entity has a one-to-many relationship with the Service entity.
• The Service entity has a one-to-one relationship with the Mechanic entity, as one mechanic can only perform one service at a time.
• Because a part can be installed to fix multiple issues, the Issue entity and the Part entity have a one-to-many relationship.

PART B

A few mechanics might be engaged with the help of a vehicle:

We must modify the Service_Mechanic_Issue entity to record which mechanic installed each component and resolved each issue during the service in order to meet this requirement.

The installation fee as well as the cost of the parts must be recorded by the system:

The initial design already took into account this requirement, as the Issue_Part entity has an attribute called Installation_fee (Biscobing, 2021).

Some mechanics may supervise other mechanics because of their seniority:

The Supervisor_id and Date_began_supervising attributes of the Mechanic entity can be used to represent this relationship.

During a variety of vehicle maintenance procedures, the same issue may present with a distinct description or outcome.

The requirement that the Issue entity be able to store multiple instances of the same issue with distinct descriptions and outcomes was already taken into consideration in the initial design (Ravi, 2020).

TASK 3

ER diagram to Relational diagram

ProjectManager (employeeID, name)

Client (ID, name, employeeID*)

Contract (contractno, startDate, endDate, totalFee, ID*)

SubContract (task, fee, balance, contractno*, ABN*)

BankAccount (BSB, AccountNo, ID*)

ConstructionProject (address, roomcount, noOfFloors, contractNo*)

Contractor (ABN, phoneNo, task*)

Mapping

TASK 4

Answer 4.1:

Can't be sure if there is a manager for each department because the manager_id column of the departments table must not be null in order to ensure that there is a manager for each department. This constraint is applied when the table is created. By noticing the example table it very well may be accepted that since there is no invalid worth in the manager_id segment the no invalid requirement is applied except for not without a doubt (School, 2020).

Answer 4.2:

In Department Table job_id foreign key constraint is missing.

Foreign key Primary key

Departments.Job_id --------> Job.job_id

Schema:

Departments(department_id, department_name, manager_id*, location_id*, job_id*)

Answer4.3:

The provided SQL statements are insufficient to capture all necessary modifications. A database instance requires the following modifications:

• Establishing brand-new subordinate departments.
• Adding managers to the newly established departments.
• Adding a single, unified director for the new departments to which the manager will be accountable(Academy., 2020).

The SQL statement fulfills the first two requirements.

Answer4.4:

The date ought to be enclosed in quotation marks, such as "6/6/2020." The revised SQL proclamation is: UPDATE Employees: SET job_id=45, hire_date='6/06/2020,' with first and last names of "Jonny" and "Deans"; Because there is no table specifically dedicated to storing past contracts, the request "find all the past contracts that Jonny Deans used to have" cannot be completed using the provided database schema after running the aforementioned query (Techopedia, 2023). As explained in Questions 4.6 and 4.7, a brand-new table called "JobHistory" needs to be created.

Answer4.5:

The row in the "Locations" relation with a "location_id" attribute equal to 10 will be deleted by running the SQL statement "DELETE FROM LOCATIONS WHERE location_id=10." All foreign keys that refer to this row will remain dangling, which could lead to problems with referential integrity. To effectively execute this assertion, all unfamiliar keys referring to this line ought to be refreshed or erased first.

Answer4.6:

CREATE TABLE JobHistory (

employee_id INTEGER,

start_date DATE,

end_date DATE,

job_id INTEGER,

department_id INTEGER,

PRIMARY KEY (employee_id, start_date),

FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),

FOREIGN KEY (job_id) REFERENCES Jobs(job_id),

FOREIGN KEY (department_id) REFERENCES Departments(department_id)

);

Answer4.7:

CREATE TABLE Jobs (

job_id INTEGER PRIMARY KEY,

job_title VARCHAR(10),

min_salary INTEGER,

max_salary INTEGER

);

Answer4.8:

• The following SQL statement can be used to update the database instance to reflect Adam Smith's rehiring with a new salary of $90,000 on "01/01/2021":

Update Employees by setting salary to $90,000 and employee_id to 22; INSERT INTO THE VALUES OF JobHistory (22, "01/01/2021," NULL, 2, 1);

• Adam Smith's salary is now $90,000 in the Employees table, and a new record is added to the JobHistory table indicating that he was hired again on January 1, 2021, for the same position in department 1. He is currently working at this job, as the end date is set to NULL.

References

Academy., C., 2020. https://www.codecademy.com/articles/what-is-rdbms-sql. [Online]
Available at: https://www.codecademy.com/articles/what-is-rdbms-sql
[Accessed 16 November 2021].

Ahmad, T. I. J. A. A. T. D. &. P. I., 2019. Model-based testing using UML activity diagrams: A systematic mapping study.. Computer Science Review, pp. 98-112.

Biscobing, J., 2021. Entity Relationship Diagram. Search Data management.

Fowler, M., 2011. OMG Unified Modeling Language (OMG UML),. Superstructure, 2(4), p. 507.

Ravi, S. J. R. &. A. S., 2020. A Study on Comparison of UML and ER Diagram.. [Online]
Available at: https://67.209.122.217/archives/V7/i5/IRJET-V7I51425.pdf

School, w., 2020. SQL Introduction. [Online]
Available at: https://www.w3schools.com/sql/sql_intro.asp

Sparks, G., 2021. "Database Modeling in UML".

Techopedia, 2023. Structured Query Language (SQL).. [Online]
Available at: https://www.techopedia.com/definition/1245/structured-query-language-sqlm

You Might Also Like:-

Computer Science Assignment Help

What is the Difference Between Coding and Scripting?

Artificial Intelligence in Healthcare Assessment Answer

Looking For Complete Assignment Sample Get It On WhatsApp