PROBLEMS As a database analyst and designer you are given th

PROBLEMS 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 EmployeelDEmployeeNameEm ployeeBirthDate CERTIFICATE EmployeelDCorselD CourselDDateCompleted COURSE CourselD CourelD CourseTitle

Solution

A

a. Data type for each column

EMPLOYEE

CERTIFICATE

COURSE

b. Implement refrential integrity

c

ALTER TABLE EMPLOYEE
ADD CONSTRAINT [FK_EMPLOYEE_CERTIFICATE]
FOREIGN KEY (EmployeeID)REFERENCES CERTIFICATE(EmployeeID)
ON UPDATE SET NULL;

ALTER TABLE COURSE
ADD CONSTRAINT [FK_COURSE_CERTIFICATE]
FOREIGN KEY (CourseID)REFERENCES CERTIFICATE(CourseID)
ON UPDATE CASCADE

d. ALTER TABLE EMPLOYEE
MODIFY EmployeeID integer IDENTITY(35,2) NOT NULL;

e   ALTER TABLE CERTIFICATE

ADD DEFAULT SysDate() FOR DateCompleted;

B

Insert into EMPLOYEE values(159,\'Chris\',\'12/01/1977\');

C

Insert into COURSE values(\'MIS 320\',\'Database Management Systems\');

D.

Alter table EMPLOYEE

add default 002 for DepartmentID;

E.

Create table DEPARTMENT

{

DepartmentID int not null,

Departmentname varchar(20) not null,

Branch varchar(30) ,

EmployeeID int ,