assigned to Aroka

CSCI 6622 — Databases — 15 Fall — Course Project,

. THE DUE DATE OF THE PROJECT WILL BE ANNOUNCED LATER: it will probably be around two weeks before the final exam.

The tree database: You have to create a database for use by the employees of trees.com, an online store which sells many different types of trees, including spruce, maple, pine, oak and elm. Trees are classified as deciduous (maple, oak) or evergreen (spruce, pine, elm). The customer information which is kept is the customer number cnum, which is unique, and the customer’s name, zipcode and phone number.

Among the queries your database should be capable of handling are:

1. List the names and phone numbers of the customers in the 10022 zip code who bought a pine tree.

2. List the names and phone numbers and zip codes of the customer who bought at least one deciduous tree.

3. List the names and phone numbers of all the customers who bought at least 3 or more elms in a single order.

4. List the cnum and zip code of all the customers who bought elms, pines, and oaks in a single order.

5. List the names of the customer who bought at least one deciduous tree and at least one evergreen tree (possibly in the same order, possibly in different orders).

6. How many maple trees were sold in total.

7. List the names of the customer who bought more than 20 spruce trees (this could have been done over different orders).

8. How many orders included both spruce and oaks?

9. Get a count of the number of customers who bought one or more elms but did not buy any other type of tree.

10. Get a count of the number of customers who bought at least four different types of trees (possibly in different orders).

11. The basic information about customers (name, zip code etc.) has to be entered using forms. For this query, you have to also give a printout showing what the form looks like i.e. what would it look like to the user who is using the form to enter the customer data.

12. You have to print out a report containing a list of all the trees bought by a customer, for each customer.

Please turn over to the other side for further instructions.

You need to turn in a hard copy (paper copy) of the following:

1. A description of any assumptions you are making in designing this database.

2. An E.R. diagram of your database.

3. The relational schema with all primary and foreign keys similar to Elmasri Figure 5.7.

4. If you are using Access, a printout of the relationships.

5. A printout of all the tables in your relational instance. Make sure your instance has enough information content to have meaningful results for all 12 queries. In particular, none of your queries should return an empty table.

6. For each of the queries :

(a) For each query, copy the query in English from this project sheet to the top of the page of what you turn in.

(b) Show the SQL to implement the query. You may not need to do this for queries 11 and 12 if you did not use SQL.

(c) If you are using views or subqueries or intermediate tables

• explain in English what the subquery is doing. • show the SQL to create the views or subqueries or intermediate tables • show the tables for these views or subqueries or intermediate tables.

(d) Show what is the final output table (produced by the DBMS) on the query.

You also need to submit on blackboard the database PROJECT containing

1. The relational instance

2. For each of the queries 1 through 10 , the saved SQL queries, clearly labelled as query1, query2 etc.

3. If you are using views or subqueries or intermediate tables, these should also be here, clearly labelled.

4. The form and report specification for queries 11 and 12.

Notes:

• You can implement your database in either Microsoft Access or another relational DBMS of your choice.

• If a particular SQL construct is not available in the DBMS you are using, you may have to use a different SQL construct.

• Feel free to create views or to save queries (to help answer a more complex query) or to create other tables (eg: using the INTO command discussed in class) if that helps you.

• Please note that what is turned in on blackboard should exactly match what is being turned in on the hard copy. So, for example, if you have some queries on the database you are submitting on blackboard, those should be an exact match with what is being turned in on the hard copy.

• If the hard copy is submitted on time but the blackboard submission is done after the start of class on the due date, the assignment/problem will be marked late.

• If the the blackboard submission is done on time but hard copy is submitted after the start of class on the due date, the assignment/problem will be marked late.

• Email submissions will not be accepted.

2