Suppose that there are four tables in a database, i$.$e$.,$ Staff, Project, Participation, Department.

Staff $($ID$,$ name, email, no$)$

Project $($no$,$ name, budget$)$

Participation $($ID$,$ no$,$ hours$)$

Department $($no$,$ name, manager$\_$ID$)$

The Staff table stores information about the ID$,$ name, email address and the department

number which the staff belongs to$.$

The Project table stores information about the number, name and budget of a project.

The Participation table stores information about the participation of staffs in the projects, and

the number of hours that a staff uses for a particular project.

The Department table stores information about the number, the name of each department, and

the staff ID of the manager of the department. The manager of a department is also a staff

belonging to this department.

$($a$)$ Decide the primary key$($s$)$ and foreign key$($s$)$ of each table. For a foreign key, you need to

specify which table it refers to$.$

$(4$ marks$)$

$($b$)$ Write data definition language $($DDL$)$ to create the table Participation and Department. You

can choose the proper data type for each attribute by yourself.

$(4$ marks$)$

$($c$)$ Write a SQL statement to add an extra column age in table Staff. You can decide a proper

data type for this new column by yourself.

$(2$ marks$)$

$($d$)$ Write a SQL statement to list out the no$.$ of each department and the total amount of hours of

all its staff working for all the projects.

$(3$ marks$)$

$($e$)$ Write a SQL statement to delete all the data recorded in Participation for the staff who are the

managers of some departments.

$(3$ marks$)$

$($f$)$ Write a SQL statement to list out the names of all managers who are in charge of a

department in which at least one staff participates in a project with the budget not smaller

than $300.$ The same name must appear only once.

$(4$ marks$)$