IST474 Coyote Corp DB

For this lab exercise, you will deal with four tables for the CoyoteCorp DB.

Table Name Attributes Primary key Description
EMPLOYEES empNo An individual who works in CoyoteCorp
empNo Unique id, format 9999
fname First name of the employee
lname Last name of the employee
address Home address of the employee
sex Gender of the employee F-female or M-male
salary Yearly salary of the employee, format 999999, salary cannot be lower than $12,000
position Job role of the employee in CoyoteCorp - clerk, programmer, manager, sale representative, account representative, dba
deptNo Department number that this employee works for, format 99
An employee can work in only one department and a department can have more then one employee.
Every employee must work in a department.
Every department must have at least employee.
Not every employee will manage a department.
An employee need not work on a project.
But every project must have at least one employee working on it.
DEPARTMENTS deptNo A functional division within CoyoteCorp
deptNo Unique id, format 99
deptName Name of the functional division - IT, Sales, Accounting, Marketing, Administration
Mgr Employee number of the manager of the department.
A department must have a manager.
PROJECTS projNo Piece of planned work or an activity that is finished over a period of time
projNo Unique id, format 99
projName Name of planned work/activity - Computeration, ProductX, ProductY, etc
deptNum Department that controls the project - format 99
A department controls many projects but a project can be controlled by only one department.
A department need not control a project.
Every project must be controlled by a department.
EMP_PROJ (empNo, projNo) Details of the hours worked by the employee on each project
empNo Unique id, format 9999
projNo Unique id, format 9999
hourSpent Number of hours spent by the employee in the project

EMP Data

empNo fname lname address sex salary position deptNo
1000 Steven King 731 Fondren, Houston, TX M 30000 Programmer 60
1007 Diana Lorentz 638 Voss, Bellaire, TX F 24000 Clerk 20
2002 Pat Fay 3321 Castle, Spring, TX F 15000 Sales Representative 80
1760 Jonathan Taylor 561 Rice, Houston, TX M 60000 Manager 20
1740 Ellen Abel 890 Stone, Houston, TX F 65000 Manager 60
2060 William Gietz 450 Berry, Bellaire, TX M 65000 Manager 80
2000 Jennifer Whalen 980 Fire Oak, Humble, TX F 28000 Clerk 60
1444 Peter Vargas 975 Dallas, Houston, TX M 20000 Sales Representative 80

DEPT Data

deptNumber deptName Mgr
20 Marketing 1760
60 IT 1740
80 Sales 2060

PROJ Data

projNumber projName deptNum
10 Product X 20
20 Product Y 20
30 Computerization 60
40 Product Z 80
50 Mobile Apps 60

EMP_PROJ Data

empNo projNo hoursWorked
1000 30 32.5
1000 50 7.5
2002 10 40.0
1444 20 20.0
1760 10 5.0
1760 20 10.0
1740 50 15.0
2060 40 12.0

What to do

  • Log on to livesql.oracle.com
  • Create the four tables EMP, DEPT, PROJ, EMP_PROJ with the appropriate constraints except FOREIGN KEY constraints. For filename, use CREATE.sql accordingly.
  • Use http://convertcsv.com/csv-to-sql.htm create insert statements using supplied data.
  • Import data using insert statements created in Step 3 to EMP, DEPT, and PROJ tables.
  • Check that all data for EMP, DEPT, and PROJ tables has been imported.
  • In sqllive add the FOREIGN KEY constraints for each of the EMP, DEPT, PROJ and EMP_PROJ tables using Alter statement. For filename, use ALTER.sql accordingly.
  • Use the substitution & method of INSERT command to populate EMP_PROJ table. INSERT INTO EMP_PROJ VALUES ('&empNo', '&projNo', &hoursWorked); Note: enclose &empNo in ' ' if the datatype is a string - VARCHAR2 or CHAR if empNo is NUMBER datatype then do not enclose &empNo in ' '!
  • Check that all data for EMP_PROJ table has been altered.
  • Use the alter table to add a new column named email in the employees tables.
  • Try to execute the following insert statement and explain what is happening. INSERT INTO EMPLOYEES VALUES (1172, 'joe', 'Calvert', '672 White Pine, Austin, TX', 'X', 10000, 10);
  • Please necessary corrections on the insert statement above.
Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.