Application Overview
The Space Cat art supply store is a small business that provides unique art supplies to contemporary artists. They offer a huge variety products for such a small store, and because this variety is always expanding, it is important for them to become more organized when managing their inventory. Like many small businesses, Space Cat has the ultimate goal of expanding to further their success and provide their products to a wide variety of artists.
To start, they have just opened a few new locations, which calls for further organization and a larger volume of information. The location, item availability, and contact information for these stores must be stored in the database.
The focal point of this database project is that Space Cat would like to begin to offer online ordering. They would like to implement an ordering kiosk for customers in-store and encourage customers to use their online ordering system. Maintaining a database will facilitate this implementation immensely. The in-store ordering kiosk will not only allow customers to place an order for items not at the current location, but it will also allow them to find items at another store to hold for pick-up. Order status, shipping dates, and invoice information should be stored in the database.
The database will consist of information on items (including new items and current special promotions), employees, and the different stores of the Space Cat company.
Mission Overview
Mission Statement
The purpose of the database for Space Cat Art Supplies is to help the company organize their inventory, keep up with products and promotions, organize information about employees and store locations, keep up with shipments, and manage orders placed by customers.
Mission Objectives
To maintain (add, update, and delete) data on products.
To maintain (add, update, and delete) data on current promotions.
To maintain (add, update, and delete) data on inventory in store locations.
To maintain (add, update, and delete) data on customers.
To maintain (add, update, and delete) data on orders placed by customers.
To perform searches on products.
To perform searches on current promotions.
To perform searches on store locations.
To perform searches on customers.
To perform searches on orders placed by customers.
To track the status of products.
To track the status of current promotions.
To track the status of orders placed by customers.
To report on products.
To report on current promotions.
To report on store locations.
To report on customer orders.
Database Scope
This database covers information on stores and orders. It is also used to track inventory by number only. It is not necessary to store any financial information, employee information, payroll, or specific status on individual product units (e.g. damaged).
Requirements Specification
Data Requirements
Products
With such a wide variety of products, it is important that Space Cat keeps them well-organized within its database. Product information includes item number, description, and retail price.
Promotions
Often times, there will be certain products with special discounts and promotions. In the database, promotions include a promotional code and description. In a separate table, items currently on promotion will be presented with an item code in correspondence with a promotional code.
Stores
Store information will consist of a location number, location, and contact number. There will also be information stored on the amount of each product currently in stock at each store.
Orders
Order information will consist of order numbers, order dates, customer number, estimated ship date, and total price. On a separate table, the order number will be coordinated with item numbers.
Customers
Customer information includes a customer number, name, address, and phone number.
Transaction Requirements (Sample)
Data Entry
Add information on a new product (e.g. “24-pack colored pencils”).
Add a new customer order.
Add a new customer.
Add a new promotion type.
Data Update/Deletion
Delete a promotion type.
Remove discontinued items.
Update the discount for a promotion type.
Data Queries
Find a currently out-of-stock item at a different store.
Data Dictionary
Entity | Description | Aliases | Occurrence |
Product | Products that are stocked in the store. | Item, Part | Products can have promotions, be part of an order, or be in stock at a store. |
Promotion | Discounts and specials on products. | Discount, Special | Promotions are placed on items where applicable. |
Store | A physical location within the company. | Location, Branch | Each store has a different set of employees and contains items in its stock. |
Orders | Orders placed by customers. | None | An order contains items as well as information about customers. |
Customer | A customer or rewards member. | None | Each customer has an order or reward membership. |
Entity-Relationship Diagram
Relational Model
1. Derivation of Relations
Product (ItemID text, Description text, RetailPrice currency, Promotion yes/no)
Promotion (PromoCode text, Description text)
PromoProduct (ItemID text, PromoCode text)
Store (StoreNum text, StoreAddress text, StoreCity text, StoreState text, StoreZip text, StorePhone text)
StoreProduct (ItemID text, StoreNum text, InStock number)
Orders (OrderNum text, CustomerID text, OrderDate date/time, date EstShip date/time, TotalPrice currency)
OrderDetail (OrderNum text, ItemNum text, Quantity number)
Customer (CustomerID text, CustomerLast text, CustomerFirst text, Address text, City text, State text, Zip text)
2. Identification of Functional Dependencies
Product Schema:
FD3: {ItemID, Description} → RetailPrice
FD4: {ItemID, StoreNum} → InStock
Customer Schema:
FD5: {CustomerLast, CustomerFirst, Address} → CustomerID
FD6: {CustomerID, Address} → CustomerPhone
SQL Queries (Sample)
1. Query name: DeleteProduct
Data Requirement: Remove a discontinued product from the database.
DELETE FROM Product
WHERE ProductID = '225';
2. Query name: PromotionYes
Data Requirement: Update new sale item to have promotion status of “yes”.
UPDATE Product
SET Promotion = YES
WHERE ProductID = [Input ID of new promotion product.];
3. Query name: FindItemDesc
Data Requirement: Find a currently out-of-stock product at a different store (by description).
FROM StoreProduct, Product
WHERE Product.ItemID = StoreProduct.ProductID
AND Product.Description = [Input item description.]
AND InStock > 0;
4. Query name: DeletePromotion
Data Requirement: Remove a sale/promotion discount from a product.
DELETE FROM PromoProduct
WHERE ItemID = [Input item ID to remove];
5. Query name: PromotionNo
Data requirement: Set promotion status of a product to “no”.
UPDATE Product SET Promotion = no
WHERE ProductID = [Input ID of promotion to remove];
6. Query name: RemoveCustomer
Data requirement: Remove a customer.
WHERE CustomerID = [Input customer ID to delete];
Database Use Documentation
This database is intended for use by employees and managers to facilitate work as well as encourage customers to use the in-store ordering kiosk and online ordering system.
Employee Entrance
The employee logs in to the computer at the register with an employee ID and password as per typical retail store use. In addition to normal register features, they are provided with multiple options on-screen such as “Find item at different location”. They can also place an order for a customer.
Manager Entrance
When logged in, the manager has all of the options available to employees, but in addition, the manager is given the ability to manage products currently on promotion, as well as add or delete products from inventory.
Customer Entrance
There is no need for the customer to log in to the in-store ordering kiosk. The customer will be presented with the options “place order”, “browse”, and “find item at another store”. It is essentially a catalog that allows the customer to view products, place an order, and find another location that might have the item they are looking for.