1. Database Design 1.1. Design and create a database in third normal form based
ID: 3825666 • Letter: 1
Question
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 employeefor 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.
Explanation / Answer
As per given requirement the relation ships are as follows:
Customer table:
CREATE TABLE Customer (
cumstomer_id int NOT NULL,
first_name varchar2(255);
last_name varchar2(255);
job_id int not null,
PRIMARY KEY (customer_ID),
FOREIGN KEY (job_id) REFERENCES Customer
(cumstomer_id)
);
CREATE TABLE Job(
job_id int not null,
job_desc varchar2(2555);
status varchar2(255);
start_date DATE;
end_date DATE;
material_id int not null,
PRIMARY KEY (job_id),
FOREIGN KEY (material_id) REFERENCES Material
(m_id)
);
CREATE TABLE Material(
m_id int not null,
material_desc varchar2(2555);
availability_status varchar2(255);
quantity number;
cost number;
company_id int not null,
PRIMARY KEY (m_id),
FOREIGN KEY (company_id) REFERENCES Company
(comp_id)
);
CREATE TABLE Company(
comp_id int not null,
company_name varchar2(255);
vendor_id int not null,
PRIMARY KEY (comp_id),
FOREIGN KEY (vendor_id) REFERENCES Vendor
(vendor_id)
);
CREATE TABLE Vendor(
vendor_id int not null,
vendor_name varchar2(255);
hours_worked DATE;
rate_per_hour number;
skill varchar2(255);
address varchar2(255);
hire_date DATE;
PRIMARY KEY (vendor_id),
);
2. ADD date:
................
INSERT INTO Customer VALUES (101,'ravi','kanala',11);
INSERT INTO Customer VALUES (102,'ravi1','kanala1',12);
INSERT INTO Customer VALUES (103,'ravi2','kanala2',11);
INSERT INTO Customer VALUES (104,'ravi3','kanala3',12);
INSERT INTO Customer VALUES (105,'ravi4','kanala4',13);
INSERT INTO Customer VALUES (106,'ravi5','kanala5',14);
INSERT INTO Job VALUES (11,'admin','open',15_feb_2017,'28_feb_2017',501);
INSERT INTO Job VALUES (12,'admin','Inprocess',19_feb_2017,'25_feb_2017',502);
INSERT INTO Job VALUES (13,'admin','closed',14_feb_2017,'21_feb_2017',501);
INSERT INTO Job VALUES (14,'admin','open',26_feb_2017,'28_feb_2017',503);
INSERT INTO Material VALUES (501,'bag','available',100,2000,1001);
INSERT INTO Material VALUES (502,'pen','available',100,1000,1002);
INSERT INTO Material VALUES (501,'pencil','available',50,500,1001);
INSERT INTO Material VALUES (501,'scale','outofstock',100,2000,1002);
INSERT INTO Company VALUES (1001,'ravi_stationary','1000001');
INSERT INTO Company VALUES (1002,'must_stationary','1000002');
INSERT INTO Company VALUES (1003,'har_stationary','1000001');
INSERT INTO Vendor VALUES (1000001,'Musthafa',45,100,dotnet,13/128_yendada,15_apr_2016);
INSERT INTO Vendor VALUES (1000002,'pooja',45,200,java,14/156_varahagiri,15_apr_2016);
INSERT INTO Vendor VALUES (1000003,'Hema',45,100,sap,13/256_rishikonda,18_apr_2016);
INSERT INTO Vendor VALUES (1000004,'Janaki',45,500,dotnet,18/198_hill,15_apr_2016);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.