DRAFT: This module has unpublished changes.

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



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.



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.



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.



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.



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







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.


Discounts and specials on products.

Discount, Special

Promotions are placed on items where applicable.


A physical location within the company.

Location, Branch

Each store has a different set of employees and contains items in its stock.


Orders placed by customers.


An order contains items as well as information about customers.


A customer or rewards member.


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.


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).

SELECT StoreNum, InStock

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.





DRAFT: This module has unpublished changes.