if object_id('EmployeeProject', 'U') is not null 
drop table EmployeeProject 
go 
if object_id('Employee', 'U') is not null 
drop table Employee 
go 
if object_id('Office', 'U') is not null 
drop table Office 
go 
if object_id('Project', 'U') is not null 
drop table Project 
go 
 
 
create table Project( 
	projectId nchar(5) not null 
		constraint CK_project_projectid 
		check(projectId like '[A-Z][A-Z][0-9][0-9][0-9]') 
		primary key, 
	projectName nvarchar(20) not null unique, 
	budget money null
) 
go 
 
create table Office ( 
	officeNr smallint primary key, 
	[type] nvarchar(20) not null 
		constraint ck_office_type  
		check([type] in ('Internal', 'Window', 'Corner'))
) 
go  

CREATE TABLE Employee (  
     empId nchar(4) not null  
          constraint CK_employee_empId  
          check(empId like '[0-9][0-9][0-9][0-9]')  
          primary key,  
     empGrade nvarchar(15) not null  
          constraint CK_employee_empGrade  
          check(empGrade in ('Associate', 'Manager', 'Executive')),  
     officeNr smallint null
		  constraint FK_officeNr
	      foreign key references Office(officeNr)
		  on delete set null,
     empName nvarchar(30) not null,  
     street nvarchar(30) null,  
     city nvarchar(30) null,  
     zipCode nchar(5) null  
)  
go  

-- Handle jointly nullable city/state/zipCode 
ALTER TABLE Employee  
add constraint CK_Employee_street_city_zipCode  
check((street is null and city is null and zipCode is null) or (street is not null and city is not null and zipCode is not null))  


create table EmployeeProject( 
	empId nchar(4) not null 
		constraint ck_employeeproject_empid  
		check(empId like '[0-9][0-9][0-9][0-9]') 
		constraint fk_empID  
		foreign key references Employee(empId), 
	projectId nchar(5) not null  
		constraint ck_employee_projectid 
		check (projectID like '[A-Z][A-Z][0-9][0-9][0-9]')  
		constraint fk_projectId  
		foreign key references Project(projectId)
		on delete cascade, 
	primary key(empId, projectId)
) 
go