Use master
Go
If exists
	(Select [name] from sys.databases
		where [name]='SoftwareTracker')
	Begin
		Drop Database SoftwareTracker
	End
Create Database SoftwareTracker
GO
Use SoftwareTracker
Go
Create Table Software
(
	SoftwareID nchar(5) primary Key,
	SoftwareName nvarchar(50),
	SoftwareCompany nvarchar(50),
	SoftwareLicenseType nchar(10),
	SoftwareTotalUnits int,
	SoftwareAvailableUnits int,
	SoftwareLicensePrice money
)
Go
Create table Location
(
	LocationID nchar(5) primary key,
	LocationName nvarchar(100),
	LocationBuilding Nchar(20)
)
Create table Computer
(
	ComputerID nchar(5) primary Key,
	ComputerMake nvarchar(50),
	ComputerCPU Nvarchar(20),
	LocationID nchar(5) Foreign Key references Location(LocationID),
	DatePurchased datetime,	
	DateRetired DateTime
)
GO
Create table Employee
(
	EmployeeID Nchar(5) primary key,
	EmployeeLastName nvarchar(50),
	EmployeeFirstName nvarchar(30),
	EmployeeDept Nchar(10),
	EmployeePhone Nchar(10),
	EmployeeSupervisor Nchar(5)
)
Go
Create table SoftwareComputer
(
	SoftwareID Nchar(5) Foreign Key references software(softwareID),
	ComputerID Nchar(5) Foreign Key references Computer(ComputerID),
	Primary key (SoftwareID, ComputerID),
	InstallationDate datetime,
	RemovalDate datetime		
)
GO
Create table UserComputer
(
	ComputerID nchar(5) foreign key references Computer(ComputerID),
	EmployeeID Nchar(5) foreign Key references
Employee(EmployeeID),
	Primary Key (ComputerID, EmployeeID)	
)
--inserts into employee
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00001', 'Stafford', 'Robert', 'IT', '00003')

Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00002', 'Lamier', 'Tracy', 'IT', '00003')

Insert into Employee(EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00003', 'Martin', 'Jeff', 'IT', null)

Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00004', 'Anderson', 'William', 'ACC', null)

Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00005', 'Sanders', 'Jennifer', 'ACC', '00004')

Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00006', 'Carlson', 'Jeremy', 'PSN', '00007')

Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00007', 'Stafford', 'Robert', 'IT', null)

--Inserts for Locations
Insert into location (LocationID, LocationName, LocationBuilding)
values ('M200', 'Accounting office', 'Main')

Insert into location (LocationID, LocationName, LocationBuilding)
values ('M201', 'IT office', 'Main')

Insert into location (LocationID, LocationName, LocationBuilding)
values ('W100', 'Warehouse office', 'Warehouse')

--Inserts for computer
Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23001','Dell','Pentium 4','4/24/2004',null,'M201')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23002','Dell','Pentium 4','5/09/2004',null, 'M201')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23003','Gateway','Pentium 2','2/20/2001','6/1/2004','M200')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23004','MAC','G4','1/24/2003',null, 'M200')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23005','Gateway','Pentium 3','4/24/2002',null,'M201')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23006','Compact','486','2/13/2000','11/2/2002','M200')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23007','Dell','Pentium 4','4/24/2004',null, 'M200')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23008','Dell','Pentium 4','1/1/2005',null, 'M201')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23009','Dell','Pentium 4','5/05/2004',null, 'M201')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23010','Dell','Pentium 4','3/01/2005',null,'M200')

Insert into Computer (ComputerID, ComputerMake, ComputerCPU, 
DatePurchased, DateRetired, locationID)
values('23011','HP','Pentium 2','4/24/2002','3/2/2005','M200')



--Inserts for Software
Insert into software (SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType, SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
Values ('W2000','Windows 2000 workstation', 'Microsoft','site',10,null, 800.95)


Insert into software(SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
Values ('W2Srv','Windows 2000 Server', 'Microsoft','Seat',1,0, 2400.00)

Insert into software(SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
Values ('WOFFC','Microsoft Office 2003', 'Microsoft','site',10,null, 750.95)

Insert into software(SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
Values ('DRWVR','DreamWeaver XP', 'Macromedia','seat',2,1, 299.95)

Insert into software(SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
Values ('PHTOS','PhotoSho', 'Adobe','seat',1,0, 665.50)

Insert into software(SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
Values ('PCHTR','Peachtree Accounting', 'Peachtree','seat',3,0, 400.95)

Insert into Software(SoftwareID, SoftwareName, SoftwareCompany, 
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits, 
SoftwareLicensePrice)
values ('SQLSV', 'SQL Server 2000', 'Microsoft', 'seat',1,0,1750.90)

--Inserts for User Computer

Insert into UserComputer(ComputerID, EmployeeID)
Values('23004','00006')

Insert into UserComputer(ComputerID, EmployeeID)
Values('23001','00004')

Insert into UserComputer(ComputerID, EmployeeID)
Values('23002','00003')

Insert into UserComputer(ComputerID, EmployeeID)
Values('23005','00007')

Insert into UserComputer(ComputerID, EmployeeID)
Values('23008','00001')

Insert into UserComputer(ComputerID, EmployeeID)
Values('23009','00002')

Insert into UserComputer(ComputerID, EmployeeID)
Values('23010','00005')



--inserts for SoftwareComputer
Insert into SoftwareComputer (SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('W2Srv','23007', '5/1/2002',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('SQLSV','23007', '5/1/2002',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('W2000','23001', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('WOFFC','23001', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('PCHTR','23001', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('W2000','23010', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('WOFFC','23010', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('PCHTR','23010', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('PHTOS','23004', '5/1/2002',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('DRWVR','23004', '5/1/2002',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('W2000','23008', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('W2000','23009', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('W2000','23002', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('WOFFC','23008', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('WOFFC','23009', '5/1/2004',null)

Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate)
Values('WOFFC','23002', '5/1/2004',null)