Task 1: Designing an Entity-Relationship Model

Exercise Central Study

Exercise Central ( EC) is an Australian fitness company. The following are the requirements for managing data about staff, clients, and exercise plans for EC.

Exercise Plans

EC is responsible for managing exercise plans for customers in Australia. Each exercise plan   has a unique plan number and also      has a description such as      ' building core strength' . The system keeps track of various general exercises performed as part of an exercise p l a n, recording the uni q ue name of the exercise, its description, and the muscl e groups that it targets. For example a ‘squat' which involves ' lowering and raising your body by bending your knees on a single spot' which targets your ' legs' and your 'core' muscle groups.

Staff

Staff develop specialised exercise p l a n s that are an      instance of an exercise and      plan with a specific intensity, for example a ' high'  intensity squat ( which is part of building      core strength) .  It records the number of     repetitions,    sets,     intervals    and    number  of    days    per    week    that    the    exercise    should    be performed (for a given              intensity level). For example, a low intensity squat exercise plan might have lower repetition, sets, intervals and days per week, than a high intensity squat plan.

A staff member can develop many such plans, but only one staff member is responsible for developing the plan.  Specialised exercise plans can be managed by many different staff    ( not necessarily the staff member who developed them), and a staff member can manage   many such plans.

The system records the staff members id, name, and speciality.

Course

Customers can enrol in many courses, with each course composed of several specific         exercise plans (eg a low intensity squat plan and a high intensity bicep curl plan) .  Plans can be a part of many courses. A course has a course id, a fee and a duration (in weeks).

Customer

Each Customer has a unique number and has a name, address and phoneNo. The date that customers begin each course is recorded.

Based on the given description, model the given business rules, and present your model as an Entity- Relationship ( ER) diagram. Carefully state any assumptions    that you make. In your ER diagram, you must properly denote all applicable        concepts, including weak or strong entities, keys, composite or multi- valued      attributes, relationships and their cardinality and participation constraints.

If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model.

You must use UML notation and the diagramming tool Lucidchartto draw your diagram. Your diagram must be drawn to a high standard with minimal clutter.  You are not required to map the ER model to relational model.

A special note: This is an open- ended question with many different models that can be derived. Your model is assessed based on how accurately it represents      business rules described above.

Task 2: Designing an Entity-Relationship Model

Part A: Initial Design

Quick Service Case Study

Quick Service is an Australian vehicle repair center.

You are asked to design a database for managing the vehicle service details.  Requirements for the database are as follows:

Vehicles are brought in for service. For each vehicle the database records the     registration number, make, model and year.

The date, time, duration and fee of the service are also recorded, along with the details of the mechanic who performed the service (employee id and           name).

During the service a log of issues that were identified are recorded.  For each issue, the name, description, and outcome are stored.

For each issue identified, the system records the parts that were installed. The parts have a partNo, supplier, description, and cost. Note that the same part may be available from     more than one supplier. The same part may be used as part of different issues.

Based on the given description, model the business rules of Quick Service, and present your model as an Entity- Relationship ( ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote    all applicable    concepts,    including weak    or    strong    entities,    keys,                composite or    multi-valued    attributes, relationships and their cardinality and participation constraints.

If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model.       Avoid introducing unnecessary artificial keys.

You must use UML notation and the diagramming tool Lucidchartto draw your diagram. Your diagram must be drawn to a high standard with minimal clutter.  You are not required to map the ER model to relational model.

A special note: This is an open- ended question with many different models that can be derived. Your model is assessed based on how accurately it represents     business rules described above.

Part B: Client Adjustments

After presenting your ER model to Quick Service management,  you are asked if it can be used to perform the following additional tasks.

Several mechanics may be involved in the service of a vehicle. The system  needs to be able to identify which mechanic installed each part and resolved each issue during the service.

The system needs to record not just the cost of parts but also the installation fee.

Some mechanics have higher seniority and may supervise other mechanics. Mechanics cannot have more than one supervisor. The date when they began supervising others is recorded.

The same issue could come up with a different description or outcome during various services of vehicles.

For each one of the tasks specified above, explain how your ER diagram is supporting it. If it is   not possible to achieve any of the tasks above given your current design, state why,  modify the model,  provide the modified ER diagram (in addition to your original ER diagram), and explain    how the new model achieves the missing requirements.

Task 3: Mapping an ER Model to a Relational Database Schema

Consider the following ER diagram, which shows aspects of a construction project management system.

You are requested to map the above ER diagram into a relational database schema. Show every step of the mapping. No marks are awarded to the final schema if you do not show the partially built schema at the end of each step. Clearly indicate the primary key (underlined) and foreign keys (with an asterisk) in each relation.

Task 4: Relational Database Model

This section contains the schema and a database i n s t a n c e  for the    E m p l o y e e  database that stores employee data for an organisation. The data includes items such as personal info (e.g. , name, phone, salary) ,     departments of the organisation (e.g., name and location of each department, who the manager is) , jobs (e. g. , titles, salary range) , and a history for past contracts with each employee. A database instance is shown in Figure 2 followed by the database schema.

The database schema is shown below, and the meaning of most attributes is

self-explanatory. “Job History” is simplified as “JobHistory” . Primary keys are underlined, and foreign keys are annotated with a *.

Employees (employee_id, first_name, last_name, phone_number, hire_date,

empjob_id, salary, department_id)

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

Jobs (job_id, job_title, min_salary, max_salary)

Locations (location_id, street_address, postal_code, city, state_province,

country_id*)

Countries (country_id, country_name)

JobHistory (employee_id*, start_date, end_date, job_id*, department_id*)

The following table further clarifies the connection between the keys across multiple tables.

The following questions must be answered based on the given database        schema and instance. Where explanation is required, each answer should be a SHORT passage of at most several lines.

QUESTION 4. 1 :     Does the database schema ensure that there is a manager associat ed with each depart ment? Explain your answer.

Questions 4.2 : What foreign key constraints are missing from Figure 3 ?   Write down the missing constraints in the format shown in figure 3. Show the updated schema for the corresponding relations.

QUESTION 4.3: The IT Services department has recently changed to have      two sub-departments (i.e., IT Support, and Software Devlopment) .        Now, each       sub-department       is supposed to have a separate manager.          Additionally, the managers    of    all    IT    Services    sub-departments         now    must    report    to    a    single    Director    (i.e.,    “Director    of         Support Services”) . Temporarily and until the new managers are hired,         Adam Jones has been appointed to the management of both

sub- departments as well to the single role of Director of Support Services. His salary range is between $130,000 and 160,000.

The following    SQL    statements    are    intended to    record    all    the changes    required    in    the    database    instance. Will they work?     If they     are     sufficient     to     achieve     the     requirements     specified above,     explicitly     mention     they     are sufficient.    If there are any    shortcomings, identify them and briefly justify your answer.

UPDATE Departments SET department_name= ' IT Support ' WHERE

department_ id= 1 ;

INSERT INTO Departments VALUES (4, 'Software Development ', 12,

10);

QUESTION 4.4: The employee named Jonny Deans changed his job to

become a Senior Programmer on 6/06/2020. The following SQL

statement intends to make the required changes to reflect Jonny’s

promotion.

UPDATE Employees SET empjob_id=45 , hire_date= 6 /0 6/2 02 0 WHERE

empjob_id=33 ;

Explain if there any issues with the outcome of the update and how it         should be fixed? After running the above query, consider the     request      “find    all the    past    contracts that Jonny     Deans     used to    have” .        Can this request     be completed using the given database schema and after the above statement is run? If yes, explain how the request can be                 answered. If no, explain what is missing and how it should be fixed.

QUESTION 4.5: Explain what the result of executing the following SQL

statement on the data base i nstance will be . DELETE FROM

LOCATIONS WHERE location id= 1 0 ;

Identify all changes that must be completed to allow this to successfully execute.