Relational Database Model Assignment

Part B: Relational Database Model 

This section contains the schema and a database instance for the Employee database that stores employee data for an organization. The data includes items such as personal info (e.g., name, phone, salary), departments of the organization (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 *.  

QUESTION 4.1: Does the database schema ensure that there is a job associated with each employee? Explain your answer.  
QUESTION 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.

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.  
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 not, 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.  

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 the 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 the 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 on 1 July 2021, and he works in the Human Resources department. Other information about Aladdin’s appointment will be finalized later. Your SQL statement must be valid for SQLite Studio environment, free of any errors, and compatible with your answer to the previous question.