Barems: Q1-25%, Q2-15% each sub question

Objectives: H5SD - SQL skills

Problem Descriptions:

You work as a Database Developer for ABC Inc. The company uses RDBMS for project management. The fragments of database schema are given in figure below. As shown, the Project database contains four tables: Projects, Employee, Project_Modules and Work_Done.

  

Each project is assigned to an employee called Project Manager and there can be only one Project Manager for a project.

For better planning and management, each project is divided into smaller modules and responsibility of each module is assigned to an Employee.

Reporting is done on a daily basis and employees report the work done on their modules as well as other employees’ modules (if any).

You remove the project information after three months of project completion.

Beside the fact that a Project Manager is an ABC Inc employee, we have some other assumptions on the table fields as below:

ProjectCompletedOne: the date project was completed.

ProjectModulesDate: the due date for the owner employee to complete the module

ProjectModulesCompletedOn: the actual date that the owner employee has completed the module.

WorkDoneDate: the date when the relevant employee has completed the Work, it would be NULL if the Work is still in progress

SupervisorID: the ID of mentioned employee’s supervisor, who is also an ABC Inc employee.

Questions

1.Update the database schema above to reflect the business logics, design

2.Write :

Create the tables (with the most appropriate field/column constraints & types) and add at least 3 records into each created table.

Write a stored procedure (with parameter) to print out the modules that a specific employee has been working on.

Write a user function (with parameter) that return the Works information that a specific employee has been involving though those were not assigned to him/her.

Write the trigger(s) to prevent the case that the end user to input invalid Projects and Project Modules information

(Project_Modules.ProjectModulesDate < Projects.ProjectStartDate, Project_Modules.ProjectModulesCompletedOn > Projects.ProjectCompletedOn).