DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)
Solution:
CREATE TABLE DEPARTMENT(
DepartmentName Char(35) NOT NULL,
BudgetCode Char(30) NOT NULL,
OfficeNumber Char(15) NOT NULL,
Phone Char(12) NOT NULL,
CONSTRAINT DepartmentPK PRIMARY KEY(DepartmentName)
);
========================================================================================== 7.5 Write a CREATE TABLE statement for the EMPLOYEE table. Email is required and is an alternate key, and the default value of Department is Human Resources. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE. • EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)
• Department in EMPLOYEE must exist in DepartmentName in DEPARTMENT
• EmployeeNumber is a surrogate key that starts at 1 and increments by 1.
Solution:
CREATE TABLE EMPLOYEE(
EmployeeNumber Int NOT NULL IDENTITY(1,1),
FirstName Char(25) NOT NULL,
LastName Char(25) NOT NULL,
Department Char(35) NOT NULL DEFAULT ‘Human Resources’,
Phone Char(12) NULL,
Email Char(100) NOT NULL,
CONSTRAINT EmployeePK PRIMARY KEY(EmployeeNumber),
CONSTRAINT DepartmentFK FOREIGN KEY(Department)
REFERENCES DEPARTMENT(DepartmentName)
ON UPDATE CASCADE
ON DELETE NO ACTION,
CONSTRAINT EmployeeAK UNIQUE(Email)
);
=========================================================================================

7.6 Write a CREATE TABLE statement for PROJECT table. The default value for MaxHours is 100. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE. • PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate)
• Department in PROJECT must exist in DepartmentName in DEPARTMENT
• ProjectID is a surrogate key that starts at 1000