You have been hired as a database administrator for a small startup company. You will be responsible for designing and implementing a relational database for this real-world case scenario.

Your task is to design and implement a relational database to manage the store's products, customers, orders, and inventory. The database should be able to handle basic operations such as inserting, updating, and retrieving data, as well as enforcing data integrity constraints.

PROJECT TASKS:

Business Rules (5%): Define your business rules and assumptions in preparation for the creation of an ER (Entity-Relationship) diagram.

Deliverable:

Business rules in Word document.

Database Design (15%): Create an ER (Entity-Relationship) diagram to model the different entities (e.g., products, customers, orders, inventory) and their relationships. Specify the attributes, primary keys, and foreign keys for each entity (Crow's Foot Notation).

Deliverable:

ERD in Word document. Draw.io is recommended to create the graph. Sample ERDLinks to an external site.

Database Implementation (30%): Use a relational database management system (RDBMS) to create the database schema and implement the tables, relationships, and constraints based on your ER diagram. SQL Express is strongly recommended. At least five tables are required. Ensure that Numeric, Character, and Date data types are present in the attributes you are defining.

Deliverable:

DDL commands used for database implementation in Word document. See Sample Files section for reference.

Data Population (30%): Populate the database with sample data to reflect a realistic scenario for an online store. Include products with different attributes, customers with different contact information, and orders with various statuses. At least 15 customer records and 10 product records are required.

Deliverable:

DML commands used for database population in Word document. See Sample Files section for reference.

SELECT * FROM [Table] screenshots for each table in your database.

Querying and Reporting (15%): Write SQL queries to retrieve data from the database, such as product listings, customer orders, and inventory status. At least the following are required:

A SQL statement with SELECT, FROM, WHERE, ORDER BY, and aliases

A SQL statement with a JOIN

A SQL statement with ALTER TABLE that demonstrates adding an attribute to a table.

A SQL statement with INSERT VALUES INTO that demonstrates adding values to the newly created attribute(s) above.

Deliverable:

DML commands used for querying and reporting in Word document, along with a brief description of what the command is doing.

Future Enhancements and Recommendations (5%): Based on the designed and implemented online store database, identify potential areas for improvement and propose future enhancements or recommendations for the hypothetical company. This could include suggestions for expanding the database schema to support new features or functionality, optimizing the database for better performance, or addressing potential scalability or security concerns. At least a couple of paragraphs.

Deliverable:

Suggestions in word document.

SUBMISSION:

Single Word document with project deliverables.

Backup file (.bak) of your database.