CSC 316: Database Design For The PC Store Project

Trident University
Anuj Kainth
Module 2 SLP
CSC 316 – Database Systems I
Professor: Michael J. Pelosi
7 February 2014

"Database Design for the PC Store Project"
Introduction
The database design for the PC Store project needs to fulfill several requirements the management has to oversee the sales, operations and profits of the store. The requested data will include Sales Transactions List, Inventory Status List, Monthly Profit Reports, Monthly Report of PCs not selling and Inventory Status Query by Product. This will be accomplished using a series of tables that are relational and work with a unique primary key in each table that allows the data to be linked and expanded when needed.
Tables
Now that we have identified the
Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key (Refsnes Data, 2014).
The implementation of a primary key such as item ID would provide for easier linking of table within the data base. The information for this table can be entered into the system as items arrive in store and can be managed by linking this table to the sales table. The sales table will contain information from the POS terminal to include item id, date, time, method of payment, number sold, price, tax and customer information. The customer will be prompted to input their data at the POS terminal to facilitate this process. The table will also accommodate for returns and exchanges by adding items back into the inventory and updating with the new quantity. Linking this table to the inventory report will provide the ability to generate profit reporting table and monthly computer sales reports that determine which items are not
A sale can consist of a sales order which may contain more than one order item. Creating a join table is used when there is a need to join the primary keys of two tables to facilitate a potential many-to-many relationship. It could be argued that an item in the computer inventory may exist in many purchase orders and a purchase order may contain many items from the computer inventory table. We cannot store multiple inventory IDs in the sales table because no single column within a table can hold multiple values. This would violate first normal form. Therefore, an additional table is added. The sales table still holds general information about the sale, such as a primary key, the total sale amount, the payment type, and the total tax. Then there will be a sale_item or purchase_item table that will hold the primary key of the specific sale along with the primary key of the computer inventory item sold. The combination of the sale_item primary key and the purchase_item primary key will be the complex primary key on this join