A. Write the correct SQL codes according to the following specifications to implement three tables shown in the graph.

a. Pick the appropriate data type for each column.

b. Implement the referential integrity.

c. Implement data integrity accoding the following requirments: 1. When EmployeeID has been changed, the EmployeeID in Certificate Table is set to null; 2. When CourseID has been changed in Course Table, the corresponding CourseID in Certificate Table will also change to match.

d. DBMS will automatically generate EmployeeID every time a new record is added to the Employee Table. The EmpoyeeID should start with 35, end at 100, increment by 2, and be a unique value.

e. The default value for DateCompleted column is the system date.

B. Write the correct SQL code to add your information into Employee Table. Use 159 as your Employee ID.

C. Write ONE correct SQL code to add your project group members into Employee Table and MIS 320 (CourseID)/Database Management Systems (CourseTitle) to Course Table. Feel free to pick EmployeeID for your group members.

D. Write the correct SQL code to add a new column DepartmentID to Employee Table with a default value of 002.

E. Wrote the correct SQL code to create a table Department with three columns DepartmentID, DepartmentName, and Branch. DepartmentName is required for each record. There are only three possible values for column Branch, i.e. Des Moines, Ames, and Iowa City, with a default value of Des Moines. However, not all departments belongs to a branch. There are one-to-many relationship between Department and Employee. One employee can only belong to one department while each department have many employees.

As a database analyst and designer, you are given the following logical schema. Based on the logical schema and requirements, write the correct SQL codes to implement the database. EMPLOYEE mployeelDEmployeNamepoeBirthDate CERTIFICATE EmployeelD CourslD DateCompleted COURSE CourselD CourseTitle