The Cocoa Shop - Creating a SQL database for business expansion

The Cocoa Shop is a family-owned chocolate boutique in Mandeville, Virginia. The company has experienced phenomenal growth over the past few years and are looking for ways to expand their business and their reach.

One of the first ways is to expand their business to a new retail shop in Christiana, Virginia. They want to also eventually expand their business globally through programs such as QVC or Home Shopping Network and through their online store.

The main issue of the expansion is trying to handle the influx of new customers and how to keep detailed records and tracking the purchases and interests of their customers. The Cocoa Shop also need a way to analyze all this information and be able to evaluate their data to be able to better know their target audience and to serve those customers.

They want to grow their business through an online store and will need a system that allows their customers to place orders from the website, be able to take payments from their customers and have clear information about these online transactions. So, they may ship their delicious chocolates worldwide to their customers.

One of the functions that the website needs is a way for customers to view their previous transactions.
Once a customer logs in, they are able to view their past purchases.
Along with that, they are able to check how much they spent in all over time.

The customer needs the ability to review their order before confirming and paying for their order. This form allows customers to view what they ordered with pictures associating with the products for easier viewing. There is also a way for customers to select their payment method and put in their information.

A listing of the Cocoa Shops chocolates & respective categories

• Displaying
• Product ID
• Product Name
• Chocolate Category
• Sorted descending order by Chocolate Code

This query gives us insight into which product the customer enjoys the most and can help for future decisions about quantities needed on-hand at the specific locations.

Another way this could be utilized is helping to customize emails or flyers promoting the specific product that customer loves most, giving them a personalized feel to their experience.

A list for customers who have been with TCS three for more years

Displaying the following:

• The customer's ID number
• First and Last name 
• Customer’s Preferred Location
• Email Address

A query to display the in-store and online revenue by day displaying:

• Transaction Date
• Location
• Total Product Cost
• Total Product Revenue
• Total Shipping Fees
• Total

This provides a list sorting total sales for each store location in descending order beginning with the highest sales generated.

This can be great for recognizing which store is performing best to gain useful ideas about why they may be performing better than others.

This section displays:

• Store name
• City
• Total Sales

The Cocoa Shop requested two reports for their management team to analyze. These reports build on two queries that were previously discussed.

First we have the customer revenue report, which is based on the customer revenue query. It accesses the Customer Last Name, Customer first name, total product revenue, total product cost, and total revenue per customer. After each customer and their information is listed, a grand total for total product revenue, total product cost, and total revenue is provided. The UNION command was used to add the grand total row. This report informs staff on customer revenue.

The second report is the daily detailed revenue report, and it is based on the daily revenue and shipping costs query. The report displays the transaction date, location, total product cost, total product revenue (this is quantity multiplied by price), total shipping fees, and total revenue for each transaction in the system. The results are grouped by both transaction date and location. For each location, a subtotal of the total product cost, total product revenue, total shipping fees, and total revenue is provided, and a grand total at the end. Grouping the sales by location allows the management team to determine which store is earning the most profit.

What is a trigger? According to google, A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

Triggers can be very useful because of this automatic process. Instead of going to each individual table within the database you can add the following trigger to update, change, or delete records automatically if certain conditions are met.

In our case, The trigger on our slide automatically updates a table we have created called customer_audit_table. While keeping all the information we already had on our customer table, we have also added a way to keep track of which audit this one is in numerical order and when the change was made at the end.

Here we have a record that needs to be updated. A customer needs to have their phone number and location updated and two new customers also had to be added to our records.

These are our update statements. These statements only update the Customer table but because of the trigger shown on our previous slide, the customer audit table is automatically updated to show what was changed and when it was changed.

As you can see, we have a total of 5 records. Two for the addition of two customers and 3 for the changes in phone number, location preference, and chocolate preference. However, the chocolate preference did not need to be changed so in the next slide

•A mistake was made and the customer's chocolate preference did not need to be changed.Here we can verify that the chocolate preference was changed back to the original preference and when that update was made in the final row

In Conclusion, The Cocoa shop now has a system that can be fully integrated into its daily operations. We can now view the following:

• How many customers the business has
• How many transactions have been made
• Forms to make online purchases
• Previous transactions
• A member loyalty list
• Individual customer chocolate and location preference
• Daily revenue and shipping costs
• Customer Revenue Reports
• And much, much more.

Ultimately, this makes the handling of daily operations much easier so the business can continue to expand without having to keep paper records and finding storage for them. The addition of the forms online makes it easier to reach customers globally and the database can also continue to be improved on if necessary.

Ultimately, this makes the handling of daily operations much easier so the business can continue to expand without having to keep paper records and finding storage for them. The addition of the forms online makes it easier to reach customers globally and the database can also continue to be improved on if necessary.