Question

Project Steps: 1. Database Design 1.1. Design and create a database in third normal form based on the following requirements: • Each Job is for a specific customer and there can be more than one job per customer. • The name and address must be tracked for each customer. • Each job is described by up to 2000 characters. • Each job has a status of ‘open, ‘in process’, or ‘complete’. • Each job has a start date and end date. • Multiple materials can be used on each job. • Multiple quantities of the same material can be used on each job. • Materials can be used on multiple jobs. • Each material is purchased by the company via a Vendor at a fixed cost per unit. • Each Vendor of the materials must be tracked with the vendor name and address • The number of hours worked by each worker on each job must be tracked. • The name, hire date, and hourly rate, and skills must be tracked for each worker. • Workers can have more than one skill. 1.2. Create foreign key constraints to enforce referential integrity for all relationships. 2. Add Data 2.1. Populate each table with test data. Make sure that you have sufficient data to test all indicated Updates, Deletes, and Queries. 3. Update and Deletes (2 points) 3.1. Create SQL to update the address for a specific customer. Include a select statement before and after the update. 3.2. Create SQL to increase the hourly rate by $2 for each worker that has been an employee for at least 1 year. Include a select before and after the update. Make sure that you have data so that some rows are updated and others are not. 3.3. Create SQL to delete a specific job that has associated work hours and materials assigned to it. Include a select before and after the statement(s). 4. Queries (3 points each) 4.1 Write a query to list all jobs that are in process. Include the Job ID and Description, Customer ID and name, and the start date. Order by the Job ID. 4.2 Write a query to list all complete jobs for a specific customer and the materials used on each job. Include the quantity, unit cost, and total cost for each material on each job. Order by Job ID and material ID. Note: Select a customer that has at least 3 complete jobs and at least 1 open job and 1 in process job. At least one of the complete jobs should have multiple materials. If needed, go back to your inserts and add data. 4.3 This step should use the same customer as in step 4.2. Write a query to list the total cost for all materials for each completed job for the customer. Use the data returned in step 4.2 to validate your results. 4.4 Write a query to list all jobs that have work entered for them. Include the job ID, job description, and job status description. List the total hours worked for each job with the lowest, highest, and average hourly rate. The average hourly rate should be weighted based on the number of hours worked at that rate. Make sure that your data includes at least one job that does not have hours logged. This job should not be included in the query. Order by highest to lowest average hourly rate. 4.5 Write a query that lists all materials that have not been used on any jobs. Include Material ID and Description. Order by Material ID. 4.6 Create a query that lists all workers with a specific skill, their hire date, and the total number of jobs that they worked on. List the Skill ID and description with each row. Order by Worker ID. 4.7 Create a query that lists all workers that worked greater than 20 hours for all jobs that they worked on. Include the Worker ID and name, number of hours worked, and number of jobs that they worked on. Order by Worker ID. 4.8 Create a view that includes the labor costs associated with each job. Include Customer ID and Name. 4.9 Use the View from 4.8 to create a query that includes the total labor cost for each customer. Order by Customer ID. 4.10 Write a query that lists all customers who are located on 'Main Street'. Include the customer Id and full address. Order by Customer ID. Make sure that you have at least three customers on 'Main Street' each with different house numbers. Make sure that you also have customers that are not on 'Main Street'. 4.11 Write a query to list completed jobs that started and ended in the same month. List Job, Job Status, Start Date and End Date. 4.12 Create a query to list workers that worked on three or more jobs for the same customer. 4.13 Create a query to list all workers and their total # of skills. Make sure that you have workers that have multiple skills and that you have at least 1 worker with no skills. The worker with no skills should be included with a total number of skills = 0. Order by Worker ID. 4.14 Write a query to list the total Charge to the customer for each job. Calculate the total charge to the customer as the total cost of materials + total Labor costs + 30% Profit. 4.15 Write a query that totals what is owed to each vendor for a particular job.

Answer 1

Create table CREATE TABLE Work INTEGER strFirst_name VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL DATE NOT NULL INTEGER CONSTRAINT WorkMK PRIMARY KEY(1ntWorku) CREATE TABLE Statustable INTEGER NOT NULI VARCHAR(30) NOT NULL CONSTRAINT StatustableRK PRIMARY KEY( CREATE TABLE Skill table INTEGER NOT NULI VARCHAR(30) NOT NULL CONSTRAINT Skillastable.PK PRIMARY KEY( İntidsku.) CREATE TABLE Material table INTEGER NOT NULIL VARCHAR(30) NOT NULL MONEY NOTNULL

CREATE TABLE lob table INTEGER NOT NULI INTEGER VARCHAR(2000) NOT NULI DATE DATE CONSTRAINT Job table PK PRIMARY KEY( intlobD) CREATE TABLE Job matetial INTEGER NOT NULL INTEGER NOT NULI INTEGER CONSTRAINT Lob material PK PRIMARY KEY(intlob DintMaterlD) CREATE TABLE Customer table INTEGER NOT NULIL INTEGER NOT NULI strFirst _name VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL PRIMARY KEY( intlobD)

CREATE TABLE Workskillpro intWokll intIdskil ·CONSTRAINT WorkskillproRK PRIMARY KEY intwork.12.intldskill ) INTEGER NOT NULL CREATE TABLE Jobworkertable intlob ID INTEGER NOT NULL INTEGER NOT NULL INTEGER NOT NULL CONSTRAINT ob.Workertable PK PRIMARY KEY (intJobIWintWokID) ALTER TABLE Work ADD CONSTRAINT Work workskilpro EK FOREIGN KEY intWotkI) REFERENCES workskillpr intWor D ALTER TABLE Statustable ADD CONSTRAINT Statustable iob tableFK FOREIGN KEY (intlob)REFERENCES Tiobs (intob. statuslR) ALTER TABLE Skilltable ADD CONSTRAINT Skill tableWorkskillpro E FOREIGN KEY intldskil) REFERENCES Workskil intldskill)

ALTER TABLE Material.table ADD CONSTRAINT Material table Lob material FK FOREIGN KEY (in MatstID ) REFERENCES Jot.matetial ( İntMateilR ) ALTER TABLE ob.tabl ADD CONSTRAINT Job.table lob materialE FOREIGN KEY (intlobI) REFERENCES Job material (intloI) ALTER TABLE Customer table ADD CONSTRAINT lob table Customer table FK FOREIGN KEY (intlobID) REFERENCES Tlobs (intob. tD) ALTER TABLE ob.table ADD CONSTRAINT Lob.table lok wotkertable EK FOREIGN KEY Lintlob-IR ) REFERENCES Jobawotkettable ( intJoLLR )

Add data populate each table to test the data information VALUES (11,1alith, hony, 01/01/2014 1000) (22, toy, rord', 01/01/2014,15.00) 33, mano',lim, 02/01/2013',11.00) 44, vino','eull', 03/01/2014,12.00) 055,'dad, watty, 04/01/2014,11.00) 66,mil,jili, 05/01 20143,12.00) VALUES (1, Open process) 2, program) 33,'Complete) INSERT INTO Skill table (intldskill. VALUES (11, paint street) Destiskill) 22, Sand process) 33,Paint bedroom) (44, Draw table) VALUES (11, Wood process 200) 22, paint, 100) 33,silver,50) 44, unbo, 10000)

Add data populate each table to test the data information VALUES (11,1alith, hony, 01/01/2014 1000) (22, toy, rord', 01/01/2014,15.00) 33, mano',lim, 02/01/2013',11.00) 44, vino','eull', 03/01/2014,12.00) 055,'dad, watty, 04/01/2014,11.00) 66,mil,jili, 05/01 20143,12.00) VALUES (1, Open process) 2, program) 33,'Complete) INSERT INTO Skill table (intldskill att estu skill) VALUES (11, paint street) 22, Sand process) 33,Paint bedroom) (44, Draw table) VALUES (11, Wood process 200) 22, paint, 100) 33,silver,50) 44, unbo, 10000)

INSERT INTO Job table ( VALUES (11.11.Add Kitchen', 02 02/2013, 11/01/2015) (22,22,De barn, 01/02/2012, 12/01/2012 (33,33, Master closet, 02/02/2011, 02/10/2012) (44,11, bedrooms, 03/01/2013, 11/01/2013) VALUES i1,11,11) 22, 22, 22) 33, 33, 22) 044, 44, 11) 55, 55, 11) INSERT INTO Customer_ table intCus_id,intJob_ID,strFirst_name,strLast name,strAddre,strCity1,strStatel,str ZCode) VALUES(11,11,'dela, truns,'11 Main Street, katter, 'Ottyo',35212) (22,22, Mayla', Janani, 22 Main Street, mery, Ottyo, 25223) VALUES 11.11) 22, 22) 33, 33) VALUES l1,11,0) 22, 22, 50) 33, 33, 60) 44, 44, 20)

COPYABLE CODE

Create table

CREATE TABLE Work

(

    intWork_ID                                             INTEGER        

    ,strFirst_name                                           VARCHAR(30)     NOT NULL

    ,strLast_name                                           VARCHAR(30)     NOT NULL

    ,dteHire_date                                            DATE            NOT NULL

    ,hourrate                                          INTEGER

    ,CONSTRAINT Work_prk PRIMARY KEY( intWork_ID )

)

CREATE TABLE Statustable

(

    intJob_statusID                                          INTEGER         NOT NULL

    ,strJob_stat                                          VARCHAR(30)     NOT NULL

    ,CONSTRAINT Statustable_PK PRIMARY KEY( intJob_statusID )

)

CREATE TABLE Skill_table

(

    intIdskill                                              INTEGER         NOT NULL

    ,strDescripskill                                    VARCHAR(30)     NOT NULL

    ,CONSTRAINT Skill_table_PK PRIMARY KEY( intIdskill )

)

CREATE TABLE Material_table

(

    intMaterID                                           INTEGER         NOT NULL

    ,strMaterName                                        VARCHAR(30)     NOT NULL

    ,monthlyMatCost                                        MONEY           NOT NULL

    ,CONSTRAINT Material_table_PK PRIMARY KEY( intMaterID )

)

CREATE TABLE Job_table

(

    intJob_ID                                                INTEGER         NOT NULL

    ,intJob_statusID                                         INTEGER        

    ,strJob_descrp                                      VARCHAR(2000)   NOT NULL

    ,dteSdate                                           DATE            NOT NULL

    ,dteEdate                                            DATE            NOT NULL

    ,CONSTRAINT Job_table_PK PRIMARY KEY( intJob_ID )

)

CREATE TABLE Job_material

(

    intMaterID                                           INTEGER         NOT NULL

    ,intJob_ID                                               INTEGER         NOT NULL

    ,intMater_quantity                                    INTEGER        

    ,CONSTRAINT Job_material_PK PRIMARY KEY( intJob_ID,intMaterID )

)

CREATE TABLE Customer_table

(

    intCus_id                                           INTEGER         NOT NULL

    ,intJob_ID                                               INTEGER         NOT NULL

    ,strFirst_name                                           VARCHAR(30)     NOT NULL

    ,strLast_name                                            VARCHAR(30)     NOT NULL

    ,strAddre                                             VARCHAR(30)     NOT NULL

    ,strCity1                                               VARCHAR(30)     NOT NULL

    ,strState1                                               VARCHAR(30)     NOT NULL

    ,strZcode                                            VARCHAR(30)     NOT NULL

    ,CONSTRAINT Customer_table_PK PRIMARY KEY( intJob_ID )

)

CREATE TABLE Workskillpro

(

    intWork_ID                                             INTEGER         NOT NULL

    ,intIdskill                                             INTEGER         NOT NULL

    ,CONSTRAINT Workskillpro_PK PRIMARY KEY( intWork_ID,intIdskill )

)

CREATE TABLE Job_workertable

(

    intJob_ID                                                INTEGER         NOT NULL

    ,intWork_ID                                            INTEGER         NOT NULL

    ,intTothours                            INTEGER         NOT NULL

    ,CONSTRAINT Job_workertable_PK PRIMARY KEY( intJob_ID,intWork_ID )

)

ALTER TABLE Work ADD CONSTRAINT Work_workskillpro_FK

FOREIGN KEY ( intWork_ID ) REFERENCES workskillpro ( intWork_ID )

ALTER TABLE Statustable ADD CONSTRAINT Statustable_job_table_FK

FOREIGN KEY ( intJob_ID ) REFERENCES TJobs ( intJob_statusID )

ALTER TABLE Skill_table ADD CONSTRAINT Skill_table_Workskillpro_FK

FOREIGN KEY ( intIdskill ) REFERENCES Workskillpro ( intIdskill )

ALTER TABLE Material_table ADD CONSTRAINT Material_table_Job_material_FK

FOREIGN KEY ( intMaterID ) REFERENCES Job_material ( intMaterID )

ALTER TABLE Job_table ADD CONSTRAINT Job_table_Job_material_FK

FOREIGN KEY ( intJob_ID ) REFERENCES Job_material ( intJob_ID )

ALTER TABLE Customer_table ADD CONSTRAINT Job_table_Customer_table_FK

FOREIGN KEY ( intJob_ID ) REFERENCES TJobs ( intJob_ID )

ALTER TABLE Job_table ADD CONSTRAINT Job_table_Job_workertable_FK

FOREIGN KEY ( intJob_ID ) REFERENCES Job_workertable ( intJob_ID )

Add data

populate each table to test the data information

INSERT INTO work (intwork_ID,strFirst_name,strLast_name,dteHire_date,hourrate)

VALUES      (11,'lalith','hony','01/01/2014',10.00)

            ,(22,'toy','rord','01/01/2014',15.00)

            ,(33,'mano','lim','02/01/2013',11.00)

            ,(44,'vino','eull','03/01/2014',12.00)

            ,(55,'dad','watty','04/01/2014',11.00)

            ,(66,'mill','jill','05/01/20143,12.00)

INSERT INTO Statustable (intJob_statusID,strJob_stat)

VALUES      (11,'Open process')

            ,(22,'program')

            ,(33,'Complete')

INSERT INTO Skill_table (intIdskill,strDescripskill)

VALUES      (11,'paint street')

            ,(22,'Sand process')

            ,(33,'Paint bedroom')

            ,(44,'Draw table)

           

           

           

INSERT INTO Material_table (intMaterID,strMaterName,monthlyMatCost)

VALUES      (11,'Wood process',200)

            ,(22,'paint',100)

            ,(33,'silver',50)

            ,(44,'unbo',10000)

           

INSERT INTO Job_table (intJob_ID,intJob_statusID,strJob_Descrip,dteSdate,dteEdate)

VALUES      (11,11,'Add Kitchen','02/02/2013','11/01/2015')

            ,(22,22,'De barn','01/02/2012','12/01/2012')

            ,(33,33,' Master closet','02/02/2011','02/10/2012')

            ,(44,11,'bedrooms','03/01/2013','11/01/2013')

INSERT INTO TJob_material (intJob_ID,intMater_ID,intMater_quantity)

VALUES      ( 11 , 11 , 11 )

            ,( 22, 22, 22 )

            ,( 33 , 33 , 22 )

            ,( 44 , 44 , 11 )

            ,( 55 , 55, 11)

           

INSERT INTO Customer_table(intCus_id,intJob_ID,strFirst_name,strLast_name,strAddre,strCity1,strState1,strZCode)

VALUES      (11,11,'dela','truns','11 Main Street','katter','Ottyo','35212')

            ,(22,22,'Mayla','Janani,'22 Main Street','mery','Ottyo','25223')

INSERT INTO workskillpro (intWorkerID,intIdskill)

VALUES      ( 11 , 11 )

            ,( 22 , 22 )

            ,( 33 , 33 )

INSERT INTO Job_workertable (intJob_ID,intwork_ID,intTothours)

VALUES      ( 11 , 11 , 0)

            ,( 22, 22 , 50)

            ,( 33 , 33 , 60)

            ,( 44, 44, 20)