IST 331 Database SystemsDue: Th. Jan 25

Spring 2007Homework 1

V. Matos

In this homework you will write and test a number of SQL queries. You need to begin the process by downloading the MS-Access COMPANY database from the IST331 web page (use the link Lecture Notes: Examples of SQL code using the Company Database operate on the database sample named: CompanyXP-Clean).

The database schema consists of the following tables

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno) KEY: ssn

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) KEY: dnumber.

PROJECT (pname, pnumber, plocation, dnum) KEY: pnumber.

WORKS_ON (essn, pno, hours) KEY: (essn, pno)

DEPENDENT (essn, dependent-name, sex, bdate, relationship) KEY: (essn, dependent-name)

PART I

STEP1 – Add New Data to the COMPANY database

  1. Create a new DEPARTMENT record. Its number should be 7 and the name is “Automation”. The manager’s SSN is 123456789 and his starting date is September 15, 2005.
  2. Add yourself to the EMPLOYEE table. Assign yourself to department 7 and make 987654321 be your supervisor. Fill the rest of the fields (use the SSN 111-22-3333).
  3. Create a new PROJECT record. The project number should be 7 and its name is “Euclid Corridor”. The location is Cleveland and the department in charge is 7.
  4. Modify the WORKS_ON table. Add yourself to project 7 with a weekly assignment of 3 hours.
  5. Add your (real or fictional) spouse and two children to the DEPENDENT table.

STEP2 – Show Data

  1. For each of the five database tables write a SQL statement to retrieve all the data in each table.
  2. Copy/Paste the five queries and corresponding output tables (ALT PrtScrn. Ctrl V) into a MS-WORD file (No more than ONE page please - saving trees!).

What to turn in?

  1. Floppy disk with the new (compressed) database
  2. Printout of the MS-WORD document with each of the SQL queries and output tables

PART II

Use the MS-Access COMPANY database populated in the previous step. Use SQL to formulate the following queries (note: some of them may produce empty results):

Q01. Get the last name of the married female managers.

Q02. Get the last name of married employees who have at least one daughter and one son.

Q03. Get the last name of married employees who have no children.

Q04. Get the last name of married employees who only have daughters.

Q05. Get the last name and salary of each employee as well as the name and salary of their corresponding (direct) supervisor.

Q06. Get the last name of employees who work on two or more projects.

For each query print

(a) SQL code (copy-paste to a MS-Word file – minimize the number of pages!),

(b) Screen-shots showing the output (if any).

V. Matos – IST 331 – Spring 2007 - Homework 1 – Page 1