Ecommerce SQL Database

May 2024 - June 2024

Design and implementation of a relational SQL database for a fictional crystal shop that tracks products, current inventory, transactions, reviews for products, and users who signed up for an account.

For this project, I worked with Ken to design and implement a relational SQL database for a fictional crystal shop called The Dragon's Hoard in order to develop our database skills. We wanted to track products, current inventory, transactions, reviews for products, and users who signed up for an account on the fictional shop's website.

Features

• List which products we currently have in inventory
• Create new products
• Modify the amount of a particular product that we have in inventory
• Delete a product from inventory
• Get a list of the most popular products for a given time range
• Get a list of the least popular products for a given time range
• Get a list of users who haven't purchased something in a few months to send promotional emails to, this should also include products that these users normally purchase

Design

We began by analyzing and collecting requirements. As part of this process, we developed a glossary which defined entities, naming conventions, and descriptions of various properties. We then developed an entity relationship diagram (ERD) using Crow's Foot notation to plan our logical model, defining each entity, its properties, and its relationships with other entities (eg, one to many). After normalization, we worked on the final table diagram to define precisely how to implement each table in SQL, creating additional join tables for many to many relationships.

Implementation

Once the design was complete, we split up the tasks to write SQL to implement each table, allowing us to make progress on different independent tables at the same time. We also wrote SQL to insert data into each table. Finally, we developed SQL to create procedures and triggers. Once done, we combined our separate files, fixed bugs, and ensured everything worked together and successfully created the database when run in the correct order.

Application

We then created a Java application which calls into the locally running database, using print statements for a menu and gathering user input in the terminal to demonstrate the functionality of the database.

Thanks for checking out my project!

Back to Portfolio