Use Master
Go
If exists
	(Select [name]
		From sys.databases
		where [name]='ProjectDatabase')
Begin
	Drop Database ProjectDatabase
	
End
go
Create Database ProjectDatabase
Go
Use ProjectDatabase
Go
Create Table Employee
(
	EmployeeKey int identity(1000,1) primary key,
	SocialSecurityNo nchar(9) not null,
	LastName nvarchar(255) not null,
	FirstName nvarchar(255),
	Address	nvarchar(255),
	City nvarchar(255),
	State nchar(2) default 'WA',
	ZipCode nchar(10),
	Phone nchar(10),
	HireDate DateTime not null
	
)
Go
Create Table Department
(
	DepartmentKey int identity(10,1) primary Key,
	DepartmentName nvarchar(255) not null,
	OfficeNumber nchar(4) not null,
	OfficePhone nchar(10) not null
)
Go
Create Table DepartmentEmployee
(
	DepartmentKey int Foreign Key references Department(DepartmentKey),
	EmployeeKey int Foreign Key references Employee(EmployeeKey),
	Primary Key (DepartmentKey, EmployeeKey),
	Manager bit 
	
)
Go
Create Table Project
(
	ProjectKey int identity(1,1) primary Key,
	ProjectName nvarchar(255) not null,
	Description nvarchar(255),
	StartDate DateTime,
	FinishDate DateTime
)
Go
Create table ProjectEmployee
(
	ProjectKey int Foreign Key references Project(ProjectKey),
	EmployeeKey int Foreign Key references Employee(EmployeeKey),
	Primary Key (ProjectKey, EmployeeKey)
)
Go
Insert into Employee(SocialSecurityNo, LastName, FirstName, Address, City, Zipcode, Phone, HireDate)
Values('456551234','Sanderson','Tracy','1001 HereOrThere','Seattle','98100','2065551234','2/1/2007')
Insert into Employee(SocialSecurityNo, LastName, FirstName, Address, City, Zipcode, Phone, HireDate)
Values('234567890','Landor','Bill','1010 Elsewhere','Seattle','98100','2065552345','11/1/2007')
Insert into Employee(SocialSecurityNo, LastName, FirstName, Address, City, Zipcode, Phone, HireDate)
Values('098765432','Patterson','Jody','1011 Broad','Bellevue','98100','3605553456','5/1/2008')
Insert into Employee(SocialSecurityNo, LastName, FirstName, Address, City, Zipcode, Phone, HireDate)
Values('567890121','Able','Robert','200 NorthSouth Street','Seattle','98100','2065554567','7/1/2008')
Insert into Employee(SocialSecurityNo, LastName, FirstName, Address, City, Zipcode, Phone, HireDate)
Values('478652890','Masters','Laura','101 lakeEast','Seattle','98100','2065556789','3/1/2009')

Insert into Department(DepartmentName, OfficeNumber, OfficePhone)
Values('Human Resources', 'B201', '2065550987')
Insert into Department(DepartmentName, OfficeNumber, OfficePhone)
Values('Information Technology', 'C300', '2065559876')

Insert into DepartmentEmployee(DepartMentKey, EmployeeKey, Manager)
Values(10,1000, 0)
Insert into DepartmentEmployee(DepartMentKey, EmployeeKey, Manager)
Values(10,1001, 1)
Insert into DepartmentEmployee(DepartMentKey, EmployeeKey, Manager)
Values(11,1002, 0)
Insert into DepartmentEmployee(DepartMentKey, EmployeeKey, Manager)
Values(11,1003, 0)
Insert into DepartmentEmployee(DepartMentKey, EmployeeKey, Manager)
Values(11,1004, 1)

Insert into Project(ProjectName, Description, StartDate)
Values('Killer App', 'Creating the next great killer application', '1/4/2009')
Insert into Project(ProjectName, Description, StartDate)
Values('hyperBrowser', 'New Browser technology', '1/4/2009')

Insert into ProjectEmployee(ProjectKey, EmployeeKey)
Values(1, 1002)
Insert into ProjectEmployee(ProjectKey, EmployeeKey)
Values(1, 1003)
Insert into ProjectEmployee(ProjectKey, EmployeeKey)
Values(2, 1002)
Insert into ProjectEmployee(ProjectKey, EmployeeKey)
Values(2, 1003)
Insert into ProjectEmployee(ProjectKey, EmployeeKey)
Values(2, 1004)