Project 1: Using a Relational Database

Start date 14 September, due beginning of class 25 September.

Introduction

Each student is expected to do this project individually. You are going to use Oracle to perform some queries and create views for a company-type database. The schema and sample data of the database are provided. The project can be done from CS or ITAP machines, or anywhere else that you can connect to the CS department database server. For instructions on connecting, see instructions at:
http://www.cs.purdue.edu/oracle/. If you want to use a client other than SQL*Plus, feel free - but be advised that I'll be testing your queries by treating them as a SQL*Plus script.

You can now start typing in sqlplus commands or SQL statements. Be sure to terminate every SQL statement with semicolon (;). If you have a query file, say ttr.sql, you can run it by command
SQL>@ttr

You may find the Oracle documentation helpful, in particular the SQL Reference and SQL*Plus Quick Reference.

Your assignment

The tables you will use are in schema jtli. You may find it more convenient to create synonyms in your own schema:
create synonym foo for jtli.foo;
Such synonyms will exist in the testing account, and you can set them up by running this script.

You will want to look at the schema as described in the table creation script, as well as a SQL sample. (I.e., like what you are expected to turn in, but a different set of queries.)

Queries

Write SQL queries to do the following and run them on the Oracle system. If you are making any assumptions, state them clearly, and document your queries. You may find that some will be hard to do with a single query, but try to do so (or explain why you can't.)

1. Print the name and price of all products ordered by customers in Stuttgart.
2. Warning: Depending on your print settings, the following line may be truncated when you print it.
   Don't forget that SQL project by default uses multiset semantics - relational algebra is set semantics. You might want to think about the formal meaning of this query.
3. Compute the gross compensation for all employees in September 1992 (assume that commission is payable when an order is placed, and that the salary field is monthly salary.) Print the last name, first name, and compensation ordered by last name.
4. Print the name of the manager and name of the department for the two departments with the highest salary budget.
5. Determine if slow response (defined as taking more than two weeks to ship an order) reduces the likelihood of repeat business. You can probably think of a way to represent this as a single number (e.g., percent of new orders where previous order took less than two weeks / percent where previous order was less than one week), which means you should be able to do it in a single query.
6. Increase the credit rating by one notch (e.g., from 'POOR' to 'FAIR') for all customers who have placed at least three cash orders. (Note: To test this you'll have to create your own copy of the table to be updated.)
7. The company has a rule that each department has a single manager. Write the functional dependency this produces, discuss if it is possible to violate this dependency given the database, and give a query to check if the dependency holds on the database.
8. Give a query that validates if the multivalued dependency manager_id →→ phone holds on the table s_warehouse. As a minimum, the query should return nothing if the dependency holds, and return something if the dependency does not hold. Better would be to have the query return the rows that are missing, and would have to be added to the table to make the dependency hold. Think about (but do not turn in) the following:
   • What would it mean if we had this MVD on the data, and no other non-trivial MVD (other than the obvious one that can be derived from this one)?
   • Given this MVD, is s_warehouse in 4NF?
   • Does id being a primary key for the relation make any of these questions irrelevant/uninteresting?
   • Could we use views to provide an external schema that is identical to the physical schema (tables given) except that the MVD is guaranteed to hold, even if it doesn't hold in the underlying tables? Why might this be an interesting thing to do?

Views

Create two views and show the contents of them:

1. RegionalSummary shows region name, number of warehouses, and the number of customers located in that region.
2. BikeWarehouse shows the address, city, state, country, zip_code, and phone of warehouses that contain every bicycle product offered (products that have the word bicycle in the name or short_desc.)

What to turn in

The result of the work is a file named your_career_login.sql which contains all the SQL statements you used in this assignment. Use "rem" statements to put in answers that are not in the form of an SQL query (or, if you want to be clever, write an SQL statement that prints out your text.)

Please add these lines to the beginning of your .sql file:
rem CS 541 Project 1
rem your_first_name Your_last_name
rem your_email_address

Turning in the project

Email the file as an attachment to cs541@mentor.ics.purdue.edu.