Feel free to edit the code below to answer questions 1-13

Please note for question 2-8, this the error message am getting (20:52:54 INSERT INTO PROJECT VALUES('ProductA', 4, 'Bellaire', 2) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`companyrr`.`project`, CONSTRAINT `project_ibfk_1` FOREIGN KEY (`dnum`) REFERENCES `department` (`dnumber`) ON DELETE CASCADE) 0.156 sec)

Instructions and questions

For each of the following provide the SQL code (this should be done in Workbench) and a screenshot of the results for each question below. Create the Company Database Schema.

1. Create each table clearly identifying the primary key and the Foreign Keys.

Populate each table with the data given. Show the script used to populate the tables N.B.

A table can have more than one foreign keys.

2. Insert into EMPLOYEE.

3. Insert into PROJECT.

4. Insert <453453453, John, M, 1970-12-12, SPOUSE>into DEPENDENT.

5. Delete the EMPLOYEE tuple with SSN = 987654321.

6. Delete the PROJECT tuple with PNAME = ProductX.

7. Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER = 5 to 123456789 and 1999-10-01, respectively.

Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN = 999887777 to 94377543.

9. Retrieve all employees whose address is in Houston, Texas.

10. Find all employees who were born during the 1950s.

11. Show the resulting salaries if every employee working on the ProductX project is given a 10 percent raise.

12. Retrieve the name of each employee who works on all the projects controlled by department number 5.

12. Retrieve the names of employees who have no dependents.

13. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.

Database created already to answers questions above.

create database COMPANYRR; use COMPANYRR;

CREATE TABLE EMPLOYEE ( fname varchar(15) DEFAULT NULL, minit varchar(1) DEFAULT NULL, lname varchar(15) DEFAULT NULL, ssn char(9) NOT NULL, bdate date DEFAULT NULL, address varchar(50) DEFAULT NULL, sex char DEFAULT NULL, salary decimal(10,2) DEFAULT NULL, superssn char(9) DEFAULT NULL, dno int(4) DEFAULT NULL, PRIMARY KEY (ssn)); ALTER TABLE Employee ADD ( FOREIGN KEY (dno) REFERENCES department(dnumber), FOREIGN KEY (superssn) REFERENCES employee(ssn) on delete cascade);

INSERT INTO Employee VALUES ('John','B','Smith','123456789','1955-01-09','731 Fondren, Houston, TX','M',30000,'333445555',5); INSERT INTO Employee VALUES ('Alicia','J','Zelaya','999887777','1958-07-19','3321 Castle, Spring, TX','F',25000,'987654321',4); INSERT INTO Employee VALUES ('Ramesh','K','Narayan','666884444','1952-09-15','971 Fire Oak, Humble, TX','M',38000,'333445555',5); INSERT INTO Employee VALUES ('Joyce','A','English','453453453','1983-07-31','5631 Rice Oak, Houston, TX','F',25000,'333445555',5); INSERT INTO Employee VALUES ('Ahmad','V','Jabbar','987987987','1979-03-29','980 Dallas, Houston, TX','M',25000,'987654321',4); INSERT INTO Employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO Employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO Employee VALUES ('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1);

SELECT * FROM EMPLOYEE;

CREATE TABLE DEPARTMENT ( dname varchar(25) DEFAULT NULL, dnumber int(4) NOT NULL, mgrssn char(9) DEFAULT NULL, mgrstartdate date DEFAULT NULL, PRIMARY KEY (dnumber), FOREIGN KEY (mgrssn) REFERENCES employee(ssn) on delete cascade); INSERT INTO Department VALUES ('Research', 5, '333445555', '1978-05-22'); INSERT INTO Department VALUES ('Administration', 4, '987654321', '1985-01-01'); INSERT INTO Department VALUES ('Headquarters', 1, '888665555', '1971-06-19');

SELECT * FROM DEPARTMENT;

CREATE TABLE DEPT_LOCATIONS ( dnumber int(4) NOT NULL, dlocation varchar(15) NOT NULL, PRIMARY KEY (dnumber, dlocation), FOREIGN KEY (dnumber) REFERENCES department(dnumber)on delete cascade);

INSERT INTO Dept_locations VALUES (1,'Houston'); INSERT INTO Dept_locations VALUES (4,'Stafford'); INSERT INTO Dept_locations VALUES (5,'Bellaire'); INSERT INTO Dept_locations VALUES (5,'Sugarland'); INSERT INTO Dept_locations VALUES (5,'Houston');

SELECT * FROM DEPT_LOCATIONS;

CREATE TABLE WORKS_ON ( essn char(9) NOT NULL, pno int(4) NOT NULL, hours decimal(4,1) DEFAULT NULL, PRIMARY KEY (essn, pno), FOREIGN KEY (essn) REFERENCES employee(ssn), FOREIGN KEY (pno) REFERENCES project(pnumber) on delete cascade);

INSERT INTO Works_on VALUES ('123456789',1, 32.5); INSERT INTO Works_on VALUES ('123456789',2, 7.5); INSERT INTO Works_on VALUES ('666884444',3, 40.0); INSERT INTO Works_on VALUES ('453453453',1, 20.0); INSERT INTO Works_on VALUES ('453453453',2, 20.0); INSERT INTO Works_on VALUES ('333445555',2, 10.0); INSERT INTO Works_on VALUES ('333445555',3, 10.0); INSERT INTO Works_on VALUES ('333445555',10,10.0); INSERT INTO Works_on VALUES ('333445555',20,10.0); INSERT INTO Works_on VALUES ('999887777',30,30.0); INSERT INTO Works_on VALUES ('999887777',10,10.0); INSERT INTO Works_on VALUES ('987987987',10,35.0); INSERT INTO Works_on VALUES ('987987987',30, 5.0); INSERT INTO Works_on VALUES ('987654321',30,20.0); INSERT INTO Works_on VALUES ('987654321',20,15.0); INSERT INTO Works_on VALUES ('888665555',20,null);

SELECT * FROM WORKS_ON;

CREATE TABLE PROJECT ( pname varchar(25) DEFAULT NULL, pnumber int(4) NOT NULL, plocation varchar(15) DEFAULT NULL, dnum int(4) NOT NULL, PRIMARY KEY (pnumber), FOREIGN KEY (dnum) REFERENCES department(dnumber)on delete cascade) ;

INSERT INTO Project VALUES ('ProductX',1,'Bellaire',5); INSERT INTO Project VALUES ('ProductY',2,'Sugarland',5); INSERT INTO Project VALUES ('ProductZ',3,'Houston',5); INSERT INTO Project VALUES ('Computerization',10,'Stafford',4); INSERT INTO Project VALUES ('Reorganization',20,'Houston',1); INSERT INTO Project VALUES ('Newbenefits',30,'Stafford',4);

SELECT * FROM PROJECT;

CREATE TABLE DEPENDENT( essn char(9) NOT NULL, dependent_name varchar(15) NOT NULL, sex char DEFAULT NULL, bdate date DEFAULT NULL, relationship varchar(8) DEFAULT NULL, PRIMARY KEY (essn, dependent_name), FOREIGN KEY (essn) REFERENCES employee(ssn) ON DELETE cascade);

INSERT INTO Dependent VALUES ('333445555','Alice','F','1996-03-01','Daughter'); INSERT INTO Dependent VALUES ('333445555','Theodore','M','1993-05-12','Son'); INSERT INTO Dependent VALUES ('333445555','Joy','F','1970-07-08','Spouse'); INSERT INTO Dependent VALUES ('987654321','Abner','M','1958-02-25','Spouse'); INSERT INTO Dependent VALUES ('123456789','Michael','M','1978-01-01','Son'); INSERT INTO Dependent VALUES ('123456789','Alice','F', '1978-12-08','Daughter'); INSERT INTO Dependent VALUES ('123456789','Elizabeth','F','1957-05-07','Spouse');

SELECT * FROM DEPENDENT;

INSERT INTO EMPLOYEE VALUES('Robert', 'F', 'Scott', '943775543', '1972-06-21', '2365 Newcastle Rd, Bellaire, TX', 'M', '58000', '888665555', '1'); SELECT * FROM EMPLOYEE;

INSERT INTO PROJECT VALUES('ProductA', 4, 'Bellaire', 2); SELECT * FROM PROJECT;

INSERT INTO DEPENDENT VALUES('453453453','John' ,'M', '1970-12-12', 'Spouse'); SELECT * FROM DEPENDENT;

DELETE FROM project WHERE pname='productx';

UPDATE DEPARTMENT SET MGRSSN='123456789' ,MGRSTARTDATE='1999-10-01' WHERE DNUMBER=5; SELECT * FROM DEPARTMENT;

UPDATE Employee SET superssn='94377543' WHERE ssn=999887777;

SELECT Lname, Fname, Address FROM EMPLOYEE WHERE EXISTS ( SELECT * FROM WORKS_ON, PROJECT WHERE Essn=Ssn AND Pno=Pnumber AND Plocation='Houston') AND NOT EXISTS ( SELECT * FROM DEPT_LOCATIONS WHERE DNO=DNUMBER AND DLOCATION='Houston');

select emp.fname, emp.lane,9emp.salary*0.1 + emp.salary as raise from employee emp inner join department dp on emp.ssn = dp.mgrssn inner join project pj on dp.dnumber = pj.dnum where pj.pname="productz";