Database SQL Lab

DROP TABLE department; CREATE TABLE department ( dname varchar(25) not null, dnumber integer(4), mgrssn char(9) not null, mgrstartdate date, primary key (dnumber), key (dname) );

DROP TABLE works_on; CREATE TABLE works_on ( essn char(9), pno integer(4), hours decimal(4,1), primary key (essn,pno), foreign key (essn) references employee(ssn), foreign key (pno) references project(pnumber) );

DROP TABLE project; CREATE TABLE project ( pname varchar(25) not null, pnumber integer(4), plocation varchar(15), dnum integer(4) not null, primary key (pnumber), unique (pname), foreign key (dnum) references department(dnumber) );

DROP TABLE dept_locations; CREATE TABLE dept_locations ( dnumber integer(4), dlocation varchar(15), primary key (dnumber,dlocation), foreign key (dnumber) references department(dnumber) );

source department.sql source employee.sql source project.sql source dept_locations.sql source dependent.sql source worksOn.sql source load-department.sql source load-employee.sql source load-project.sql source load-dloc.sql source load-dependent.sql source load-worksOn.sql

LOAD DATA LOCAL INFILE "worksOn.dat" INTO TABLE works_on FIELDS ENCLOSED BY "\"" TERMINATED BY "," ;

There are three files for each table in this directory. For example for the DEPARTMENT table the three files are: <ol> <li> department.sql: contains the SQL Create Table statement to create the table. <li> department.dat: contains data for the table to be loaded using the MySQL load command. <li> load-department.sql: contains the LOAD command to load data into the table. </ol> <P> The file, source.sql, contains MySQL "source" commands to execute the "create table" and "load" commands for each table. <P> After signing in to MySQL and changing to "company" database, simply run the following command to create and populate the tables: <P> mysql> source source.sql

Research	5	333445555	1978-05-22
Administration	4	987654321	1985-01-01
Headquarters	1	888665555	1971-06-19
Software	6	111111100	1999-05-15
Hardware	7	444444400	1998-05-15
Sales	8	555555500	1997-01-01

1	Houston
4	Stafford
5	Bellaire
5	Houston
5	Sugarland
6	Atlanta
6	Sacramento
7	Milwaukee
8	Chicago
8	Dallas
8	Miami
8	Philadephia
8	Seattle

LOAD DATA LOCAL INFILE "dloc.dat" INTO TABLE dept_locations FIELDS ENCLOSED BY "\"" TERMINATED BY "," ;

This lab focuses on the design, build, and use of a relational database. Use MySQL and complete the following: This is not an essay.

Complete an EER diagram of the company database described in chapter 4. You may use Visio, PowerPoint, MySQL Workbench, or gliffy.com Review Chapter 4 of the Fundamentals of Database Systems 7E book on how to create an EER diagram.

1. Create a database based on your design and provide the Create Table Statements. Write the Create table statements (even if you create the tables in MS Access, you need to provide the Create Table statements). Keep in mind keys and referential integrity requirements

2. Use the Company SQL data files provided and load the database into MySQL and provide the output for following querries. Please review Database Example file and the attach SQL and DAT files for reference.

· Prepare the following queries:

1. Select * from each table

2. Complete a query with at least 2 joins

3. Complete a query with 2 joins and the use of an aggregate function like average or sum

4. Complete a query using an outer join

3. You should submit your EER diagram, DDL statements, Load DDL, and queries in a single document.

LOAD DATA LOCAL INFILE "dependent.dat" INTO TABLE dependent FIELDS ENCLOSED BY "\"" TERMINATED BY "," ;

ProductX	1	Bellaire	5
ProductY	2	Sugarland	5
ProductZ	3	Houston	5
Computerization	10	Stafford	4
Reorganization	20	Houston	1
Newbenefits	30	Stafford	4
OperatingSystems	61	Jacksonville	6
DatabaseSystems	62	Birmingham	6
Middleware	63	Jackson	6
InkjetPrinters	91	Phoenix	7
LaserPrinters	92	LasVegas	7

LOAD DATA LOCAL INFILE "department.dat" INTO TABLE department FIELDS ENCLOSED BY "\"" TERMINATED BY "," ;

333445555	Alice	F	1976-04-05	Daughter
333445555	Theodore	M	1973-10-25	Son
333445555	Joy	F	1948-05-03	Spouse
987654321	Abner	M	1932-02-29	Spouse
123456789	Michael	M	1978-01-01	Son
123456789	Alice	F	1978-12-31	Daughter
123456789	Elizabeth	F	0000-00-00	Spouse
444444400	Johnny	M	1997-04-04	Son
444444400	Tommy	M	1999-06-07	Son
444444401	Chris	M	1969-04-19	Spouse
444444402	Sam	M	1964-02-14	Spouse

LOAD DATA LOCAL INFILE "employee.dat" INTO TABLE employee FIELDS ENCLOSED BY "\"" TERMINATED BY "," ;

123456789	1	32.5
123456789	2	7.5
666884444	3	40.0
453453453	1	20.0
453453453	2	20.0
333445555	2	10.0
333445555	3	10.0
333445555	10	10.0
333445555	20	10.0
999887777	30	30.0
999887777	10	10.0
987987987	10	35.0
987987987	30	5.0
987654321	30	20.0
987654321	20	15.0
888665555	20	0.0
111111100	61	40.0
111111101	61	40.0
111111102	61	40.0
111111103	61	40.0
222222200	62	40.0
222222201	62	48.0
222222202	62	40.0
222222203	62	40.0
222222204	62	40.0
222222205	62	40.0
333333300	63	40.0
333333301	63	46.0
444444400	91	40.0
444444401	91	40.0
444444402	91	40.0
444444403	91	40.0
555555500	92	40.0
555555501	92	44.0
666666601	91	40.0
666666603	91	40.0
666666604	91	40.0
666666605	92	40.0
666666606	91	40.0
666666607	61	40.0
666666608	62	40.0
666666609	63	40.0
666666610	61	40.0
666666611	61	40.0
666666612	61	40.0
666666613	61	30.0
666666613	62	10.0
666666613	63	10.0

DROP TABLE dependent; CREATE TABLE dependent ( essn char(9), dependent_name varchar(15), sex char, bdate date, relationship varchar(8), primary key (essn,dependent_name), foreign key (essn) references employee(ssn) );

DROP TABLE employee; CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), superssn char(9), dno integer(4), primary key (ssn), foreign key (superssn) references employee(ssn), foreign key (dno) references department(dnumber) );