CSC 455
Fall 2009
Lab 14 SQL
Part 2
Continue working on the Company database but this time, you will use the web interface for MySql called phpMyAdmin.
Log into phpmyadmin at http://otherone.cis.uncw.edu/phpmyadmin/ Ignore any security certificate warnings and proceed. (Note: if you are doing this from an off-campus site, you may need a VPN connection. See http://uncw.edu/itsd/help/instructions/VPN.html for details)
In the upper left corner, select the database that corresponds to your userid. The other choice is the database for your MeTube project.
Download the following two files to your local machine: createcompany.sql and companydata.sql
You should have the EMPLOYEE and DEPARTMENT tables already.
Create the DEPT_LOCATIONS table directly: with your database selected, go to the Structure tab if necessary. Create the table described below. Note that you can’t specify the foreign key here. You’ll do that in the next step. You can set the primary key and the INNODB engine type.
CREATE TABLE DEPT_LOCATIONS (
Dnumber integer(4),
Dlocation varchar(15),
primary key (Dnumber,Dlocation),
foreign key (Dnumber) references DEPARTMENT(Dnumber)) type = INNODB;
Now you can execute an Alter command to enforce the referential integrity for the DEPT_LOCATIONS table. Select the DEPT_LOCATIONS table, and click on the SQL tab. Enter the SQL statement: alter table DEPT_LOCATIONS add constraint foreign key (Dnumber) references DEPARTMENT(Dnumber) Click GO in the lower right corner.
Open the createcompany file with Notepad or Wordpad. It contains the SQL commands to create the remaining tables in the company database.
Create the other 3 tables either as above or by copying and pasting (one command at a time) from the .sql file into the SQL tab in phpmyadmin. The InnoDB type specification has been included in the given create table commands.
Check to make sure referential integrity is being enforced. On the SQL tab, enter the following command: Insert into DEPENDENT(Essn, Dependent_Name) values (33344555, 'Kelly'); The insert should return an error. Return to the database without completing the insertion.
Now try: Insert into DEPENDENT(Essn, Dependent_Name) values (222222201, 'Kelly'); This should work because that employee already exists.
Another way to execute SQL commands is to import them from an existing file. We will use this method to populate the existing tables as follows:
Go to the SQL tab and enter the SQL commands to do the following
queries. Once the query
is working, copy and paste the SQL into a document. Print the queries and hand in. If you want to save the query for review
later, you can bookmark it at the bottom of the screen.
1.
Show
all employee data for employees who earn more than $50,000.
2.
Retrieve
the name and address of all employees who work for the ‘Research’ department.
3.
List the
names of all employees in department 5 who work more than 10 hours per week on
the ProductX project.
4.
List
the first and last names of employees and their supervisors renaming the
columns for the supervisor name to be SupervisorFirst
and SupervisorLast.
The schema is:
EMPLOYEE (Fname,
Minit, Lname, Ssn, Bdate, Address, Sex,
Salary, Super_ssn, Dno)
foreign key (Super_ssn)
references EMPLOYEE(Ssn),
foreign key (Dno) references
DEPARTMENT(Dnumber)
DEPARTMENT (Dname,
Dnumber, Mgrssn, Mgrstartdate)
foreign key (Mgrssn) references
EMPLOYEE(Ssn)
DEPT_LOCATIONS (Dnumber, Dlocation)
foreign key (Dnumber) references DEPARTMENT(Dnumber)
DEPENDENT (Essn, Dependent_name, Sex, Ddate, Relationship)
foreign
key (Essn) references EMPLOYEE(Ssn)
PROJECT (Pname, Pnumber,
Plocation, Dnum)
foreign key (Dnum)
references DEPARTMENT(Dnumber)
WORKS_ON (Essn, Pno,
Hours)
foreign key (Essn) references EMPLOYEE(Ssn),
foreign key (Pno) references PROJECT(Pnumber)