DRAFT: This module has unpublished changes.

Application Overview

Staples has currently opened up a new online computer parts ordering system specifically for Easy Tech. Easy Tech is the computer repair center inside Staples where they take in customers computers. Due to the increased number of computer issues, computer parts are being ordered more often than before. With the amount of parts coming in, Staples would require a new type of database designed to specifically keep track of that area. Parts would be ordered online, in addition to the database showing parts that are currently on hand. There will also be a separate category for damaged parts.

Each Tech associate would be required to login in order to order parts. Tech associates will have their First Name, Last Name, and Associate ID Number recorded into the database. This way the store can keep track of which associate orders what and to which customer it's for.

 

Mission Overview

 

Mission Statement

The purpose of this Tech Parts database application is to maintain a database of parts being ordered and make available the parts that are currently on hand or damaged including keeping track of associate orders.

 

Mission Objective

  • To maintain (add, update, and delete) data on associates.
  • To maintain (add, update, and delete) data on orders.
  • To maintain (add, update, and delete) data on parts.
  • To maintain (add, update, and delete) data on customers.
  • To perform searches on associates.
  • To perform searches on orders.
  • To perform searches on parts.
  • To perform searches on customers.
  • To track the status of associates.
  • To track the status of orders.
  • To track the status of parts.
  • To report on associates.
  • To report on orders.
  • To report on parts.
  • To report on customers.

Database Scope

The purpose of this database is to allow General Managers to administrate and watch over what parts are being ordered, for which customer, and who's ordering it. Also this database allows the associates to see which parts they have onhand, what are in damages, and which customer needs which part. This database does not cover pricing of any kind. Address, city, zipcode, etc are also all irrelevant.

 

Requirements Specification

 

Associates

Staples has 4 associate that will be doing the ordering. Each associate will have their first name, last name, and their associate number recoreded down when ordering.

 

Orders

Orders will be the parts that are needed and ordered by the associates. This section will contain associate number, order number, part number, quantity.

 

Parts

The parts database will allow associates to see what parts they have on hand or in damages before making a new order. This database will only contain parts on hand, part number, part name, and amount in damages.

 

Customers

Customers database will contain the customer's number, first name, last name, part needed, and which associate is currently working with them.

Transaction Requirements (Sample)

 

Data Entry

  • Add the details of a new associates (such as Johnny Tran).
  • Add the details of a Part (such as "Power Supply").
  • Add the details of an Orders (such as associate ID 1234 ordering part number 5678 quantity 1 customer Andy order number 10).
  • Add the details of a customer (such as Henry Solo part needed Power Supply).
  • Update/delete the details of an associate.
  • Update/delete the details of an order.
  • Update/delete the details of a part.
  • Update/delete the details of a customer.
  • List the names and ID numbers of associates employed.
  • List the parts required for a customers.
  • List the parts ordered by an associate.
  • Identify the number of damaged parts.
  • List the names of customers.
  • List the current parts on hand.

Data Update/Deletion

Data Queries

 

Data Dictionary.

The purpose of the data dictionary is to clearly define and delineate the terms used for the entities (along with possible aliases) as well as identify the organizational rules and relationships between these terms.

Entity

Description

Aliases

Occurrence

Associate

Term describing the people hired by Staples to create orders.

Associate, Employee

All Associates create orders and all have the same expertise level

Order

Term describing the orders being placed by the associates.

None

Each order is made up of different part requirements.

Part

Term describing the course materials used by the associates for the customers.

None

Each part can be for as many customers as the parts currently on hand minus damages.

Customer

Term describing the people who come to staples for repairs, specifically parts.

None

Each customer will require at least one part for repairs.

 

Normalized Relations 

Associates (associateNumber text, firstName text, lastName text)

Orders (orderNumber text, partNumber text, quantity num, associateID references Associate)

Parts (partNumber text, partName text, onHand num, Damaged num)

Customers (customerNumber text, firstName text, lastName tex, associateID text references Associate)

 

SQL Queries (Sample)

The following queries were selected to reflect a portion of the transaction requirements. They were formed using Structured Query Language (SQL) and are some examples of the queries that I created for my database.

Query name: EmployeeList
Data Requirement: List the details of all employee.

SELECT a.associateNumber, a.firstName, a.lastName
FROM Associates a

Query name: PartList
Data Requirement: List the details of all parts on hand.

SELECT p.partNumber, p.partName, p.onHand, p.Damaged
FROM Parts p

Query name: CustomerList
Data Requirement: List the details of all customers.

SELECT c.firstName, c.lastName, c.partNeeded, c.associateNumber
FROM Customer c 

 

Database Use Documentation

The database is intended for use by associates and general managers. When you first enter the database, you are presented with a start up screen. After clicking on the "OK" button, you are presented with the Main Switchboard that serves as the gateway to the database. You proceed from here depending on what user group you represent. If you are a associate, you click on the Associate Entrance.

A prompt appears asking for your associate number. Associate numbers take the following format: "AS001." However, you are asked to drop the "AS" when entering your number. (Currently, valid associate numbers are 001, 002, 003, and 004). Entering "001" will provide you with a record that has the most data attached to it.

Associate Entrance
In the associate view, you are unable to make any changes to your personal information or number. You are able to check your orders (a list of your previous orders), your assigned customer (a list of the customers you are currently assigned to) and Parts(all the accumulated parts, specifically extras or damaged).

GM Entrance
To access the GM area, you must enter a valid GM number. These numbers take the following format: GM101. However, again, you must drop the "GM" to enter your number. (Since there is only one general manager in a store, the only valid GM number is 101.) Entering 101 will provide you with a record that has the most data attached to it.

Exit Database
This button will close down the database.

 

DRAFT: This module has unpublished changes.