Use MySQL Workbench to create the following Schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex...

 

      

Transcribed Image Text:

Use MySQL Workbench to create the following Schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation WORKS ON Essn Pno Hours Dnum DEPENDENT Essn Dependent_name Sex Bdate Relationship One possible database state for the COMPANY relational database schema (Fig. 5.6) EMPLOYEE Fname Minit Lname Ssn John B Smith 123456789 Franklin T Wong 333445555 Alicia J Zelaya 999887777 Jennifer S Wallace 987654321 Ramesh K Narayan 666884444 Joyce A English Ahmad V Jabbar James 1955-12-08 638 Voss, Houston, TX 1968-01-19 3321 Castle, Spring, TX 1941-06-20 291 Berry, Bellaire, TX 1962-09-15 975 Fire Oak, Humble, TX 453453453 1972-07-31 5631 Rice, Houston, TX 987987987 1969-03-29 980 Dallas, Houston, TX E Borg 888665555 1937-11-10 450 Stone, Houston, TX DEPARTMENT Dname Research Administration Headquarters Bdate Address Sex Salary Super_ssn Dno 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 M 40000 888665555 5 F 25000 987654321 4 43000 888665555 4 F Dnumber 5 4 1 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 M F M 38000 333445555 25000 333445555 25000 987654321 4 55000 NULL 1 DEPT_LOCATIONS Dnumber 1 4 5 LO 5 LO LO 5 5 LO 5 Dlocation Houston Stafford Bellaire Sugarland Houston One possible database state for the COMPANY relational database schema - continued (Fig. 5.6) PROJECT WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Pno 1 2 3 1 2 2 3 10 20 30 10 10 30 30 20 20 Hours 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 15.0 NULL DEPENDENT Essn Pnumber Plocation 1 Bellaire 2 Sugarland 3 Houston Computerization 10 20 Reorganization Newbenefits 30 333445555 333445555 333445555 Joy 987654321 123456789 123456789 123456789 Pname ProductX ProductY ProductZ Dependent_name Sex F M F M M Alice Theodore Abner Michael Alice Elizabeth F Stafford Houston Stafford Dnum 5 5 5 4 1 4 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure 5.7 (Fig. 6.1) CREATE TABLE EMPLOYEE (Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno PRIMARY KEY (Ssn), CREATE TABLE DEPARTMENT (Dname Dnumber Mgr_ssn Mgr_start_date VARCHAR(15) CHAR, VARCHAR(15) CHAR(9) DATE, CREATE TABLE DEPT_LOCATIONS VARCHAR(30), CHAR, DECIMAL (10,2), CHAR(9), INT VARCHAR(15) INT CHAR(9) DATE, PRIMARY KEY (Dnumber), UNIQUE (Dname), FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)); INT NOT NULL, VARCHAR(15) NOT NULL, NOT NULL, NOT NULL, (Dnumber Dlocation PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber) ); NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, continued on next slide SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure 5.7 (Fig. 6.1)-continued CREATE TABLE PROJECT (Pname Pnumber Plocation CREATE TABLE WORKS_ON Dnum PRIMARY KEY (Pnumber), UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)); CREATE TABLE DEPENDENT (Essn VARCHAR(15) VARCHAR(15), Dependent_name INT Sex Bdate INT (Essn Pno Hours PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)); CHAR(9) INT DECIMAL (3,1) CHAR(9) VARCHAR(15) CHAR, DATE, VARCHAR(8), Relationship PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)); NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, What to do? Using MySQL Workbench write at least 50 SQL queries (of your choice and your prior query experience- make sure you include all the categories "query_select, query_count, query_join, query_null, JOIN query, IN query subquery, DROP query, VIEW query,,etc) Do this: Using MySQL Workbench, add at least five more rows of data to each of your Then, using MySQL Workbench, create and execute advanced queries of your choice that demonstrate each of the following: The use of a GROUP BY clause, with a HAVING clause, and one or more group functions The use of UNION operator The use of a subquery The use of an outer join Then create and execute at least one SQL UPDATE and at least one SQL DELETE query. Apply at least one IN query Apply one DROP command Finally, create and use an SQL view and in a SELECT query. Submit the following outputs of your SQL script file processing (note: screenshots are accepted), in this order and appropriately labeled, in a single Word file: At the top of your Word file include your name, and the date. tables. Use MySQL Workbench to create the following Schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation WORKS ON Essn Pno Hours Dnum DEPENDENT Essn Dependent_name Sex Bdate Relationship One possible database state for the COMPANY relational database schema (Fig. 5.6) EMPLOYEE Fname Minit Lname Ssn John B Smith 123456789 Franklin T Wong 333445555 Alicia J Zelaya 999887777 Jennifer S Wallace 987654321 Ramesh K Narayan 666884444 Joyce A English Ahmad V Jabbar James 1955-12-08 638 Voss, Houston, TX 1968-01-19 3321 Castle, Spring, TX 1941-06-20 291 Berry, Bellaire, TX 1962-09-15 975 Fire Oak, Humble, TX 453453453 1972-07-31 5631 Rice, Houston, TX 987987987 1969-03-29 980 Dallas, Houston, TX E Borg 888665555 1937-11-10 450 Stone, Houston, TX DEPARTMENT Dname Research Administration Headquarters Bdate Address Sex Salary Super_ssn Dno 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 M 40000 888665555 5 F 25000 987654321 4 43000 888665555 4 F Dnumber 5 4 1 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 M F M 38000 333445555 25000 333445555 25000 987654321 4 55000 NULL 1 DEPT_LOCATIONS Dnumber 1 4 5 LO 5 LO LO 5 5 LO 5 Dlocation Houston Stafford Bellaire Sugarland Houston One possible database state for the COMPANY relational database schema - continued (Fig. 5.6) PROJECT WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Pno 1 2 3 1 2 2 3 10 20 30 10 10 30 30 20 20 Hours 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 15.0 NULL DEPENDENT Essn Pnumber Plocation 1 Bellaire 2 Sugarland 3 Houston Computerization 10 20 Reorganization Newbenefits 30 333445555 333445555 333445555 Joy 987654321 123456789 123456789 123456789 Pname ProductX ProductY ProductZ Dependent_name Sex F M F M M Alice Theodore Abner Michael Alice Elizabeth F Stafford Houston Stafford Dnum 5 5 5 4 1 4 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure 5.7 (Fig. 6.1) CREATE TABLE EMPLOYEE (Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno PRIMARY KEY (Ssn), CREATE TABLE DEPARTMENT (Dname Dnumber Mgr_ssn Mgr_start_date VARCHAR(15) CHAR, VARCHAR(15) CHAR(9) DATE, CREATE TABLE DEPT_LOCATIONS VARCHAR(30), CHAR, DECIMAL (10,2), CHAR(9), INT VARCHAR(15) INT CHAR(9) DATE, PRIMARY KEY (Dnumber), UNIQUE (Dname), FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)); INT NOT NULL, VARCHAR(15) NOT NULL, NOT NULL, NOT NULL, (Dnumber Dlocation PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber) ); NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, continued on next slide SQL CREATE TABLE data definition statements for defining the COMPANY schema from Figure 5.7 (Fig. 6.1)-continued CREATE TABLE PROJECT (Pname Pnumber Plocation CREATE TABLE WORKS_ON Dnum PRIMARY KEY (Pnumber), UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)); CREATE TABLE DEPENDENT (Essn VARCHAR(15) VARCHAR(15), Dependent_name INT Sex Bdate INT (Essn Pno Hours PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)); CHAR(9) INT DECIMAL (3,1) CHAR(9) VARCHAR(15) CHAR, DATE, VARCHAR(8), Relationship PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)); NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, NOT NULL, What to do? Using MySQL Workbench write at least 50 SQL queries (of your choice and your prior query experience- make sure you include all the categories "query_select, query_count, query_join, query_null, JOIN query, IN query subquery, DROP query, VIEW query,,etc) Do this: Using MySQL Workbench, add at least five more rows of data to each of your Then, using MySQL Workbench, create and execute advanced queries of your choice that demonstrate each of the following: The use of a GROUP BY clause, with a HAVING clause, and one or more group functions The use of UNION operator The use of a subquery The use of an outer join Then create and execute at least one SQL UPDATE and at least one SQL DELETE query. Apply at least one IN query Apply one DROP command Finally, create and use an SQL view and in a SELECT query. Submit the following outputs of your SQL script file processing (note: screenshots are accepted), in this order and appropriately labeled, in a single Word file: At the top of your Word file include your name, and the date. tables.