Project Assignment #2

Spring 2005

Professor: Yan Chen

TAs: Yi Qiao and Earl Wagner

Due: 11:59pm Friday June 3, 2005

1. Introduction

By now you have grasped the knowledge needed to design the schemes in a given relational database and you familiarized yourselves with a particular database environment – Microsoft Access.

The purpose of this project is for you to practice the issues related to manipulating relational data. In class, we presented the two most prominent formalisms (albeit not in a same “time-quantity”) – Relational Algebra (RA) and Relational Calculus (RC), which are foundational models for the techniques used in data manipulation and, as we mentioned, have the same expressive power Typically, a particular foundation is used as a bases for developing an actual implementation (a tool) that can be used to specify y the desired operation. Recall that RA is the basis for SQL (Structured Query Language – industry standard) and (again, only mentioned) RC is a basis for QBE (Query-By-Example).

As you are aware, different vendors will offer different implementations of a particular tool (i.e., fancy GUI as opposed to a plain text-based interaction with the system). This has a two-fold impact on the nature of this project:

On one hand, we have the industry standard SQL with well-defined syntactic elements (e.g. SELECT, DISTINCT, etc.)
On the other hand, we have an environment with which we are somewhat familiar…

Due to the timing constraints, we will stick with the Microsoft Access and use its capabilities for specifying queries in an SQL-like manner, although its “genetic” purpose was to be a QBE-based tool, when it comes it data manipulation. However, for the purpose of practicing SQL in its generic form, the project will have another (corresponding) part.

Goal

In this project, you will be given a complete description of a database scheme (i.e., a set of relation schemas) and you will generate data manipulation statements in a formal SQL language, which will enable answering a desired set of queries (where the queries will be described in a narrative English).

2. Database Scheme

We will transfer ourselves to the settings of managing the information pertaining to different flights. Below is the description of the scheme of each of the relations involved. For brevity, the type of each attribute is specified inside the schema (note that the type corresponds to the types that Microsoft Access offers you when you design tables…):

Flights(fltno:Number, origin:Text, destination:Text, departure:Date/Time, arrival:Date/Time, aid:Number, price:Number)

Aircrafts(aid:Number, abrand:Text, acruisingrange:Number)

Certified(empid:Number, aid:Number)

Employees(empid:Number, ename:Text, esalary:Number)

The attributes have their obvious meaning…

In reality, the information will be stored in the respective tables in the database. For the purpose of this project, however, you will have to populate some data (see Section 3 of this document).

3. Queries

For this project, you must create the following queries:

Q1: Find the origins and the destinations of all flights the duration of which exceeds 5 hours
Q2: Find the abrands of all aircrafts which operate flights from Chicago to New York
Q3: Find the names of all pilots whose salary is less than the price of 50 flights from Los Angeles to Honolulu
Q4: Find the names of pilots who are certified to operate a Boeing aircraft
Q5: Find the flights which can be operated by pilots who earn more than $100,000
Q6: Find the flights which can be operated by every pilot who earns more than $80,000
Q7: Find the ename of all pilots who can operate plans with a cruising range of at least 3,000 miles, but are not certified to operate any Boeing aircrafts
Q8: Find all the fltno’s and their respective departure times for a customer who wants to depart from Chicago and arrive in New York no later than 6:00PM
Q9: Find the enames of all employees who have a salary greater than the average salary for all pilots

You can assume that all entries in the Employees table are pilots, and that there is only one airport in each city, so the arrival and destination fields in the Flights table can be city names or unique airport codes.

You can write the SQL text by hand, or use Access’ query design tools, but must submit the SQL text itself when turning the project in.

4. What to Turn In

There are two main things that you need to turn in, once you have completed this project:

1. A file with the syntax of all the queries Q1 – Q9 above. DO NOT use a text processor (e.g. Microsoft Word) to generate the file – use a plain editor instead (e.g. Notepad). The main reason is that a text processor inserts a lot of control characters and it will prevent the TA from cutting and pasting your syntax in the test database. This part will be tested on a uniform (same for all) database (non-Microsoft Access one) to verify the correctness of your syntax. Please use CAPITAL letters for SQL keywords.

2. A sample database + the queries that you will generate yourselves in Microsoft Access, which should contain the tables and queries described above. The values for the respective attributes are somewhat hinted in the queries’ specification in Section 2. DO NOT overpopulate the tables. They should have enough entries so that you can verify that your queries indeed retrieve the information that they are supposed to (friendly suggestion – putting approximately 10 tuples in each table should do the trick).

BOTH PARTS should be turned in ONLY electronically and NO hard copy is required for this part of the project. If needed, you should also put a plain textual README.txt file that will explain anything you may find particular for your submission.

Submission Instructions

Compress the text file with the SQL queries and the sample database to a Zip file, and name it Project2_Lastname_Firstname_ID.zip where ID is your anonymous ID used for grading. Email the Zip file to cs317project2@gmail.com and include your full name and grading ID in the subject line.

Hints

Although your textbook gives very brief examples of how to specify queries in Microsoft Access – don’t be afraid, it’s straightforward…Once you have specified the tables and populated them with actual data (and saved them under a particular name, say, my_flights.mdb), you can go on and use the Queries Option from the main active window. The Create Query in Design View is aesthetically more pleasant and it will allow you (for every query) to select the tables involved in it AND fill in the necessary info in the rows at the bottom part of the active window (e.g., a check-box if the value(s) of a particular attribute is needed in the output). In case you are reluctant to use the visual parts though (AND, since Access implements a portion of QBE only) feel free to flip back and forth between the visual representation and the SQL-like environment (Access will allow you to do it) paying attention to save the intermediate stages and going on the “edit the next refinement”. The SQL-like option is actually a simple text-editor based environment. Feel free to name the queries as they appear in the specification of this document (e.g., Q1, Q2, etc.).

IMPORTANT NOTE: Due to timing constraints, we cannot give any extensions on the due date for submission of the project. Thus this is a firm deadline = Friday June 3^rd.