The Script:

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE Customers; IF OBJECT_ID('dbo.Tasks', 'U') IS NOT NULL DROP TABLE Tasks; IF OBJECT_ID('dbo.Materials', 'U') IS NOT NULL DROP TABLE Materials; IF OBJECT_ID('dbo.Projects', 'U') IS NOT NULL DROP TABLE Projects; IF OBJECT_ID('dbo.WorkOrders', 'U') IS NOT NULL DROP TABLE WorkOrders; IF OBJECT_ID('dbo.WorkOrderTasks', 'U') IS NOT NULL DROP TABLE WorkOrderTasks; IF OBJECT_ID('dbo.MaterialsRequired','U') IS NOT NULL DROP TABLE MaterialsRequired; CREATE TABLE Customers (CustID INT NOT NULL PRIMARY KEY IDENTITY(1,1), CustName VARCHAR(50) NOT NULL, CustPhone VARCHAR(50) NULL, CustWebAddress VARCHAR(100) NULL, CustStAddress VARCHAR(50) NULL, CustCity VARCHAR(50) NOT NULL, CustState CHAR(2) NOT NULL, CustZip VARCHAR(20) NOT NULL, CustContactLName VARCHAR(50) NULL, CustConstactFName VARCHAR(50) NULL); CREATE TABLE Tasks (TaskID INT NOT NULL PRIMARY KEY IDENTITY(1,1), TaskName VARCHAR(50) NOT NULL, TaskHrRate SMALLMONEY NOT NULL); CREATE TABLE Materials (MatID INT NOT NULL PRIMARY KEY IDENTITY(1,1), MatName VARCHAR(50) NOT NULL, MatDescript VARCHAR(50) NULL, MatUnitCost VARCHAR(100) NOT NULL, MatStock INT NULL); CREATE TABLE Projects (ProID INT NOT NULL PRIMARY KEY IDENTITY(1,1), ProName VARCHAR(50) NOT NULL, ProStartDate SMALLDATETIME NULL, ProCompDate SMALLDATETIME NULL, ProValue MONEY Null, ProStAddress VARCHAR(50) NOT NULL, ProCity VARCHAR(50) NOT NULL, ProState CHAR(2) NOT NULL, ProZip VARCHAR(20) NOT NULL, CustID INT NOT NULL CONSTRAINT FK_CustID FOREIGN KEY REFERENCES Customers(CustID) ON UPDATE CASCADE); CREATE TABLE WorkOrders (WrkOrNum INT NOT NULL PRIMARY KEY IDENTITY(1,1), WrkOrCreateDate SMALLDATETIME NOT NULL, WrkOrTargCompDate SMALLDATETIME NOT NULL, WrkOrActCompDate SMALLDATETIME NULL, ProID INT NOT NULL CONSTRAINT FK_ProID FOREIGN KEY REFERENCES Projects(ProID) ON UPDATE CASCADE); CREATE TABLE WorkOrderTasks (WrkOrTaskID INT NOT NULL PRIMARY KEY IDENTITY(1,1), WrkOrNum INT NOT NULL CONSTRAINT FK_WorkOrNum FOREIGN KEY REFERENCES WorkOrders(WrkOrNum) ON UPDATE CASCADE, TaskID INT NOT NULL CONSTRAINT FK_TaskID FOREIGN KEY REFERENCES Tasks(TaskID) ON UPDATE CASCADE, WrkOrTaskStatus VARCHAR(20) NOT NULL, WrkOrTaskEstHours SMALLINT NOT NULL, WrkOrTaskActHours SMALLINT NULL, WrkOrTaskCompDate SMALLDATETIME NULL); CREATE TABLE MaterialsRequired (WrkOrTaskID INT NOT NULL, MatID INT NOT NULL, MatEstQuant INT NOT NULL, MatActQuant INT NULL, CONSTRAINT PK_MaterialsRequired PRIMARY KEY(WrkOrTaskID, MatID), CONSTRAINT FK_WrkOrTaskID FOREIGN KEY(WrkOrTaskID) REFERENCES WorkOrderTasks(WrkOrTaskID), CONSTRAINT FK_MatID FOREIGN KEY(MatID) REFERENCES Materials(MatID)); 

Part II SOL Statements (12 points l. Write the SQL statement to add a new table named Employee to store data about employees. Data for employees that must be stored include empID, name, home address, phone number, start date, and salary Empld uniquely identifies each employee. Add data for two employees in the table created 2. Infrastructure Inc.'s management has decided to assign a project manager to manage each project. Each employee may manage zero or more projects; however, each project is managed by at most one employee. Write the SQL statement(s) necessary to implement this requirement 3. Write SQL statement to assign an employee as project manager for one of the projects in the project table 4. Company has decided to increase the salary paid to all employees by 7% Write the necessary SQL statement to change salary values for all employees. 5. Write SQL statement(s) to create a non-clustered index for the foreign key column(s) in the project table. 6. Write SQL statement to display the following information for all projects: project ID, project name, project description, project completion date, project contract value, and name of the customer associated with the project. Part II SOL Statements (12 points l. Write the SQL statement to add a new table named Employee to store data about employees. Data for employees that must be stored include empID, name, home address, phone number, start date, and salary Empld uniquely identifies each employee. Add data for two employees in the table created 2. Infrastructure Inc.'s management has decided to assign a project manager to manage each project. Each employee may manage zero or more projects; however, each project is managed by at most one employee. Write the SQL statement(s) necessary to implement this requirement 3. Write SQL statement to assign an employee as project manager for one of the projects in the project table 4. Company has decided to increase the salary paid to all employees by 7% Write the necessary SQL statement to change salary values for all employees. 5. Write SQL statement(s) to create a non-clustered index for the foreign key column(s) in the project table. 6. Write SQL statement to display the following information for all projects: project ID, project name, project description, project completion date, project contract value, and name of the customer associated with the project