ISYS2095-Assignment 1 Database Design Solved

The objective of this assignment is to measure your understanding of the basic concepts in the relational database model and using entity-relationship model for database design. The assessment is in two parts, split into four tasks which cover Basic ER Modelling and Basic Relational Modelling. The tasks are as follows. 

Part A: Entity-Relationship Modelling  
1.  Design and plan for the implementation of a database system, diagramming the design to a high standard using UML notation through the diagramming tool Lucidchart. 

2.  Model the activities of an organisation and present the model as an Entity-Relationship (ER) diagram. Analyse this ER diagram, and possibly modify it, based on additional client requirements. 

3.  Map an ER diagram into a relational database schema, showing every step of the mapping. 

Part B: Relational Database Model  
4. Answer a series of short questions about a Relational Database model. 

To complete this assessment, you must be familiar with Lucidchart, which is covered during the Week 14 activities. 


 

Assessment details 

Part A: Entity-Relationship Modelling
Task 1: Designing an Entity-Relationship Model
The House Transact Mate (HTM) Case Study 

House Transact Mate (HTM) is a real-estate agency that has offices throughout Melbourne. The following are the requirements for managing data about staff, offices, and properties for HTM.  

Offices

HTM has branch offices throughout Melbourne. Each branch office is allocated several staff members, including a manager who manages the operations of the office. The data describing a branch office includes a unique branch number, address (i.e., street, suburb, and postcode), up to 3 telephone numbers, and the name of the staff member who currently manages the office. Additional data about managers includes the date that the manager started the position at the current branch office and a monthly bonus payment based on performance.

Staff

Staff members with the role of supervisor are responsible for the day-to-day activities of a team of staff called agents. Each supervisor is responsible for up to a maximum of 10 agents at any one time. Not all staff members are assigned to a supervisor. The data for each staff member includes a unique staff number, name, address, position, salary, name of supervisor (where applicable), and the details of the branch office where a staff member is working.

Properties

Each branch office offers a range of properties for rent. The data stored for each property includes a unique property number, address (i.e., street, suburb, postcode), type, number of rooms, and weekly rent. The management of a property is assigned to an agent, and an agent manages a maximum of 100 properties at any one time. When a property is available for rent, the property details will be displayed on the HTM website and, when necessary, as advertisement on other websites.

Based on the given description, model the business rules of HTM, 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 Lucidchart to 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
Attention: Correction in v2.1 

A typo is corrected in item 2 of the case study below. The correction is highlighted. 
Part A: Initial Design

Deliveraa Case Study 

Deliveraa is a logistics and transport company. You are asked to design a database for scheduling their transport jobs. Requirements for the database are as follows:

1.      Each truck has a unique registration number. It also has a type and the year it is made.

now not
2.      Each customer has a unique ABN. It also has a name, and several contact information (e.g., mobile, landline, fax, email etc). There are no duplicates in contact information, that is, for example, the system must  allow the same email address to be recorded twice.

3.      Delivery jobs are identified by a unique job number. They also have “from” and “to” destinations, and estimated distance to travel.

4.      Drivers are identified by a unique employee ID. They also have first name, last name, address, and phone number.  

5.      A customer may book several delivery jobs. A single job is for only one customer who “owns” the job.

6.      A driver can be assigned several delivery jobs, but a job has only one driver. Some description about the job is also recorded. Each job has a Status (e.g., open, complete, etc).

7.      A job is assigned only one truck, but a truck can be assigned to several jobs.
Based on the given description, model the business rules of Deliveraa, and present your model as an EntityRelationship (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 Lucidchart to 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 Deliveraa management, you are asked if it can be used to perform the following additional task.

8.      Each driver can accept or reject a job that is assigned to him/her. The management wants to be able to produce a report showing the history of all the jobs that a particular driver refused to accept.  

9.      When customers use the services of Deliveraa for some time, they might wish to nominate certain drivers as their favourite drivers.

Explain, referring to your ER model, how to produce the requested list. If it is not possible to produce the list from the model, state why, modify the model, provide the modified ER diagram (in addition to your original ER diagram), and explain how it can accommodate this requirement.

 

Task 3: Mapping an ER Model to a Relational Database Schema
Attention: Correction in v2 

The ER diagram in Figure 1 is slightly updated to include an assumption, which must be considered when completing the question. 
Consider the following ER diagram, which shows aspects of performances of shows on cruise ships by various performers.  

 

Figure 1: Cruise Performance ER Diagram 

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. 

Part B
Task 4: Relational Database Model
This section contains the schema and a database instance for the Employee 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. 

 

Figure 2: Employee Database Instance 

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.  

  

Figure 3: Keys 

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 job associated with each employee? Explain your answer.

Questions 4.2: Can an employee work for two departments at the same time? Can an employee take two jobs at the same time? Explain your answer.

QUESTION 4.3: The Human Resource department has recently changed to have three sub-departments (i.e., Ongoing Staff Department, Casuals Department, and External Contractors Department). Now, each sub-department is supposed to have a separate manager. Temporarily and until the new managers are hired, Joseph has been assigned to the management of all three sub-departments.

The following SQL statements are intended to record all the changes required in the database instance. Will they work? Explain your answer.

INSERT INTO Departments VALUES(3, ‘Human Resource’, 18, 30); 

INSERT INTO Departments VALUES(3, ‘Human Resource’, 18, 30); 

QUESTION 4.4: The employee named Jonny Deans has recently been promoted to Senior Programmer. The following SQL statement intends to make the required changes in the database instance to reflect Adam’s promotion.

UPDATE Departments SET empjob_id=45 WHERE employee_id=10; 

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 database instance will be.

 UPDATE LOCATIONS SET country_id=1,2 WHERE location_id=20; 

QUESTION 4.6: Write an SQL statement to create the Jobs table including all the constraints. Make reasonable assumptions for the data type associated with each field. Your SQL statement must be valid for SQLite Studio environment and free of any errors.  

QUESTION 4.7: Write an SQL statement to create the Employees table including all the constraints, assuming all the tables that Employees depends on already exist in the database. Make reasonable assumptions for the data type associated with each field. Your SQL statement must be valid for SQLite Studio environment and free of any errors.

QUESTION 4.8: A new employee has been hired. You are asked to update the given database instance so that it also includes the new employee. The new employee’s ID is 100, his name is “Aladdin Brown”, his contract will start at 1 July 2021, and he works in the Human Resources department. Other information about Aladdin’s appointment will be finalised later. Your SQL statement must be valid for SQLite Studio environment, free of any errors, and compatible with your answer to the previous question.