Computer Science Assignment - Database Project

CSCI 2215 — Databases — 13 Fall — Course Project, Due: 12/4/13 Wednesday

DUE 12/4/13, LATE SUBMISSIONS ACCEPTED TILL START OF CLASS ON 12/11/13

NOT ACCEPTED AFTER START OF CLASS ON 12/11/13

The vegetable database: You have to create a database for use by the employees of vegetables.com, an online store which sells many different vegetables, including onions, tomatoes, carrots, potatoes, squash, eggplants and zuchini. Among the queries your database should be capable of handling are:

1. List the customerid and names of all customers in the 06222 zip code.

2. List the names of all customers who bought more than 7 lbs of eggplants in a single order.

3. Get a count of the number of orders which included both squash and zuchini.

4. Get a count of the number of customers in the 06513 zip code who bought both carrots and cucumbers in a single order.

5. List the names of all customers who bought both tomatoes and potatoes (possibly in different orders).

6. List the names and zipcode of all customers who spent more than $300 (this could have been done over different orders).

7. List the name of the customer or customers who spent the most money in a single order.

8. Get the customerid, names and zipcode of all customers who did not buy anything.

9. Get a count of the number of customers who placed at least one order.

10. List the zipcodes in which at least one customer bought onions.

11. The basic information about customers (customer id, name and zipcode ) 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, for each zip code, a list of all the vegetable sold in that zip code.

Please turn over to the other side for further instructions.

1

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 3.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 infor- mation 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 need to turn in a CD-ROM (labelled with your name and what DBMS you are using) 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.

• Email submissions will not be accepted.

2