Question 1. (20 marks) A previous intern at the Brave New World Corporation designed a small database, with the following two tables, to record data about the departments and employees in that company:

Attribute	Data type	Description of the attribute
EmpName	character of at most length 30	Employee name
EmpID	character of length 4	Employee ID number
DeptID	character of length 4	The Department ID that the employee belongs to

Attribute

DeptName DeptID

Data type

TABLE 1

TABLE 2

Description of the attribute

Department name Department ID number

character of at most length 30 character of length 4

Note that each employee belongs to exactly one department. A department can have many employees, but a department can also become defunct, which means that it no longer has any employee.

The previous intern left the corporation. You are the new intern, and your supervisor asks you about the following: (Note: For all the SQL-related questions, avoid unnecessary join operations, as using excessive join operations increase runtime.)

(a) (5 marks) Write SQL statements so that you can create the above tables.

2. (b) (5 marks) Write an SQL statement so that you can list the names of all the departments that have at least one employee.

3. (c) (10 marks) Your boss tells you that with a new management policy being implemented, it is now possible for an employee to belong to more than one department.

   1. (i) With the new policy in mind, you are asked to modify the existing database in order

      to avoid e.g. update anomalies. Draw the new relational schema. (Hint: What is the

      ER diagram?)

   2. (ii) Based on your answer in (i), write an SQL statement so that you can list all the

      employees (employee IDs and employee names) who belong to two or more departments.