CSCI 2215 Databases 13 Fall Course Project Due December 13

Csci 2215 Databases 13 Fall Course Project Due 12413 Wednesd

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 zucchini. Your database should be capable of handling queries such as listing customer details in specific zip codes, identifying customers who bought certain quantities of vegetables, counting orders with specific vegetable combinations, listing customers based on their purchase behaviors, and generating reports on sales and customer activity. You must design the database with appropriate assumptions, create an ER diagram, develop a relational schema with primary and foreign keys, and implement it using a DBMS of your choice. You should generate a comprehensive set of tables with meaningful data, ensuring all queries return results. For each query, provide the English description, SQL implementation, explanations of any views or subqueries, and the resulting output. Additionally, prepare forms for entering customer data and reports summarizing vegetable sales by zip code. Turn in both paper and digital copies of your database schema, sample data, SQL queries, views, forms, and reports. You may use Access or other relational DBMSs, and should include relationship diagrams and relational instances that satisfy all query requirements. Email submissions are not accepted.

Paper For Above instruction

Designing and implementing a vegetable sales database for vegetables.com requires a structured approach encompassing analysis, design, and implementation phases. This project aims to develop a comprehensive database system capable of supporting complex queries related to customer purchases, order analytics, and vegetable sales reports, ultimately aiding the online vegetable store in strategic decision-making.

Introduction

The core objective is to design a relational database that captures customer information, order details, the vegetables involved in each order, and sales data. The process begins with understanding the requirements, making essential assumptions, and creating a conceptual ER diagram. Subsequently, this ER model is translated into a relational schema, indicating primary and foreign keys. The implementation involves populating tables with relevant data to satisfy all queries, ensuring data richness and variety. This extensive data allows for meaningful results in queries, validations, and reporting.

Assumptions and Design Decisions

Several assumptions underpin the database design. First, each customer has unique identification, and their personal details, including zip code, are stored in a dedicated customer table. Orders are linked to customers via a foreign key, with each order containing multiple line items for different vegetables. We assume that a purchase can include multiple vegetables, captured through an order_items table with quantity and price details. The vegetables are stored in a separate vegetable table with attributes like name, type, and unit price.

Furthermore, to facilitate the queries, the database architecture enables tracking of order contents, including the vegetable types and quantities. We assume customers can have multiple orders, and some customers may purchase multiple vegetables across different orders. For data integrity, all relationships are enforced through foreign keys with cascading updates/deletions where necessary.

ER Diagram

The ER diagram contains the following entities:

• Customer: customer_id (PK), name, zipcode
• Order: order_id (PK), customer_id (FK), order_date
• Vegetable: vegetable_id (PK), name, type, unit_price
• OrderItem: order_item_id (PK), order_id (FK), vegetable_id (FK), quantity

The relationships depict that each customer can place multiple orders; each order can contain multiple vegetables; and each order item links a specific vegetable to an order, capturing the quantity purchased.

Relational Schema

The relational schema translates the ER model into tables, with primary and foreign keys explicitly defined:

Primary keys are underlined, foreign keys are marked with (FK). The schema supports complex queries by storing detailed information on orders and vegetables.

Implementing the Database

Due to the scope, sample data must be inserted into each table to generate meaningful query outputs, especially for the specific scenarios outlined in the requirements. Examples include customers in zip code 06222, those purchasing more than 7 lbs of eggplants, and orders containing multiple vegetables.

Sample Queries and Reports

For each of the twelve queries, the process is as follows:

1. Provide the natural language description, exactly replicating the project sheet.
2. Write the SQL query to accomplish the task, considering the schema.
3. If views or subqueries are used, detail their purpose in plain English, show their SQL definitions, and the tables they produce.
4. Display the final output table as returned by the database system.

For example, to list customer IDs and names from zip code 06222, the SQL might be:

SELECT customer_id, name
FROM Customer
WHERE zipcode = '06222';

Similarly, other queries involve joins, aggregations, and subqueries, carefully constructed to yield the required data.

Forms and Reports

A form for entering customer data should include fields for customer ID, name, and zipcode, with layout designed for ease of input. The report summarizing vegetable sales by zip code involves grouping vegetables sold per zip code and presenting total quantities or revenue.

Both the forms and reports are created using the DBMS's tools, with printouts demonstrating user interfaces and report layouts, as specified.

Documentation and Submission

All required documentation should be prepared, including assumptions, ER diagram, relational schema with keys, relationship diagrams (if using Access), sample data tables, the SQL code for queries, and the forms/reports. These should be printed and submitted physically. In addition, on a CD-ROM, include: the relational instance (sample data), SQL scripts for all queries and views, and the form/report files.

Final notes emphasize ensuring all queries produce non-empty result sets, verifying data integrity, and adhering strictly to submission guidelines.

Conclusion

This project demonstrates the integration of database design principles with practical implementation, supporting complex queries for an online vegetable store. Proper planning, detailed data modeling, and thorough testing of queries ensure the system will meet user requirements and facilitate informed managerial decisions regarding sales performance, customer behavior, and inventory management.

References

• Elmasri, R., & Navathe, S. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
• Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management. Thomson Course Technology.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill Education.