The vegetable database: You have to create a database for use by the employees of vegetables.com, an online store which

sells many dierent vegetables, including onions, tomatoes, carrots, potatoes, squash, eggplants and zuchini. The customer

information which is kept is the customer number customerid, which is unique, and the customer's name and zipcode.

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

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

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

3. Get a count of the number of orders which included both eggplant and potatoesi.

4. Get a count of the number of customers in the 06333 zip code who bought both tomatoes and cucumbers in a single

order.

5. List the names of all customers who bought both carrots and onions (possibly in dierent orders).

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

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

8. List the names of all customers who bought zuchini but did not buy any other vegetable.

9. List the names of all customers who bought at least 5 dierent vegetables (possibly in dierent orders).

10. From which zipcode were the largest number of orders placed?

11. The basic information about customers (custome 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.

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 nal output table (produced by the DBMS) on the query.

You also need to submit 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 specication for queries 11 and 12.