Digication ePortfolio :: T Chasse's ePortfolio :: Database Design Requirements Doc

DRAFT: This module has unpublished changes.

Tom Chasse

Database Design I

CSC*231

Professor Amjad Mahmood

Due Date: 04/09/2014

Final Project Preparation

Final Project Document

Application Overview

The TC Manufacturing Company is located in Farmington, Connecticut. It was founded in 1990 and is a family owned business. It was originally created to manufacture various customized parts for the Aircraft Industry. The Company has diversified and expanded over the years and today it is broken down into 2 divisions: Plant 1 which is the Connecticut Division and still manufactures aircraft parts. Plant 2 is the Massachusetts Division which manufactures various rings for Wind Turbines sold throughout the United States.

Plant 1 started with a 1,000 sq ft building and a handful of employees, today the building has grown to over 12,000 sq ft and houses over 25 employees 10 of which are office staff and Plant 2 is a 5, 000 sq ft factory with over 15 employees and 5 of which are office staff.

The companies produce and manufacture’s different parts for different applications. To make it possible to do they have a procedure for each Plant and the procedure is as follows:

RFQ received via e-mail from customer with a part number and quantity.

- Search 2 spreadsheets that have over 2000 lines combined to determine the material and size of the part (each spreadsheet contain part numbers, type of material, size of part and location of manufacturing - Plant 1 or Plant 2)

- Once material and size is determined, they look at another spreadsheet to determine the pricing

- Search accounting system to see if part has been manufactured before – if so, verify and update pricing and lead time to send to customer.

- If the part is a new part – then BOM and drawings go to plant mgr and engineering for review and pricing.

Once pricing and lead time has been determined a quote is sent back to the customer via e-mail or fax.

This procedure has become more time consuming with new customers and part numbers being added constantly. The owner has asked to create a database for each Plant to help keep the process easier and more efficient.

Mission Overview

Mission Statement

The purpose of the TC Manufacturing database application is to centralize all parts in one location, to maintain a lookup of each part, tracking of new and developed parts and to be more efficient in the procedures already developed.

Mission Objective

To maintain (add, update, and delete) data on staff
To maintain (add, update, and delete) data on parts
To maintain (add, update, and delete) data on pricing
To maintain (add, update, and delete) data on location
To maintain (add, update, and delete) data on drawings
To maintain (add, update, and delete) data on material

To perform searches on staff
To perform searches on parts
To perform searches on pricing
To perform searches on location
To perform searches on drawings
To perform searches on material

To track the parts
To track the location
To track the new parts
To track the pricing
To track the drawings
To track the material

To report on parts
To report on new parts
To report on location
To report on pricing
To report on drawings
To report on material

Database Scope

The scope diagram shows the operations or functions that the database projects will address and identifies the functions that fall outside of this application

The TC Manufacturing database purpose is to centralize the parts in one location and to maintain an efficient procedure to maintain lookups, parts, pricing, sales and material. This database scope is not to determine the factory scope of the procedures; it is just for the offices of Plant 1 and Plant 2.

Requirements Specification

Data Requirements

Existing Part

TC Manufacturing has numerous parts for different RFQ (Request for Quotes) from customers. The parts are created for the order that is requested. The parts are checked against the database to see if they were manufactured before. If they were then the order is filled and the request is sent to the customer with an expected delivery date. If the part is going to be a new part the request is sent to the Engineer and management for further evaluation to determine the price and expected delivery date. The evaluation consists of the material the customer is asking for and the size and cost of the process.

Plant

TC Manufacturing has two plants called Plant01 and Plant02. Plant01 is located in Connecticut and Plant02 is located in Massachusetts. The customer determines what plant is going to manufacture the part. If the customer is GE Plant02 is the manufacturer of the part. If the customer is from Sikorsky then Plant01 is the manufacturer.

Office Staff

TC Manufacturing has a total of 15 office employees. Ten office employees work in Plant01 and five office employee’s work in Plant02. There are two employees responsible for the RFQ’s that come in for each plant. The rest of the employees make up the Engineers, Managers and two owners. The Owners, Managers and Engineers are the final say for new parts.

New Part

When a RFQ comes in from a customer asking for a part that hasn’t been manufactured yet it gets sent to the Engineers for development and the managers and owners determine the price and lead time for the part.

Customers

The customers consist of the companies that TC Manufacturing has a close relationship and a solid working relationship. The customers are GE, Pratt and Whitney, Northeast Utilities, Sikorsky, General Motors.

Part Size

The size of the part is determined by the customer’s request. The size is for the old parts that are already manufactured and are in stock and is also determined if it is a new part by the Engineers and Management.

Material Type

The material types used for different parts are Bronze, Steal, Stainless Steel, Copper and Nickel.

Drawings

The drawings or schematics are for the new parts that are manufactured. It consists of the size, diagram and material type.

Transaction Requirements (Sample)

Data Entry

Add the details of a new office staff member (such as John Smith)
Add the details of a new part (such as 32 inch turbine ring)
Add the details of a new drawing (such as part number 0001- 32 inch T-Ring drawing)
Add the details of the lead time (such as New Part Manufactured)
Add the details of a new price ( such as Size, Material and Lead Time)

Data Update/Deletion

Update/Delete the details of a part
Update/Delete the details of a new part
Update/Delete the details of a customer
Update/Delete the details of drawings
Update/Delete the details of an Office Staff

Data Queries

List the part numbers for customer
List the new part numbers manufactured and price for customers
List the drawings for part number (ex. 0001)
Identify the type used for customer
List the plant number for all parts manufactured for customer (Pratt and Whitney)

Data Dictionary

The purpose of the dictionary is to clearly define 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
Existing Part	Term describing the parts already manufactured by company	Old Parts, Manufactured Parts, Parts	These are parts that have been manufactured by the company and has a price and lead time
New Part	Term describes parts not yet manufactured by company	New Parts	These are parts that have not yet been produced by company need price, lead time and material type
Plant	Term determines Plant01 – CT or Plant02 – Mass.	Location, 01, 02	This is the location of the two different divisions of the company
Office Staff	Term describes the staff in Plant01 and Plant02 office	Staff, office worker	These are the 15 office workers in Plant01 and Plant02
Customers	Term used to determine the customer sending the RFQ to the staff for the parts	None	These are the customers who need the old and new parts
Part Size	Term used to determine the size of the part for the price	Size	This is the measurement for the part that the customer needs
Material Type	Term used for the type of material used to manufacture the parts	Material, Type	This is the material used for to manufacture the parts. Type is: Bronze, Steel, Stainless Steel, Copper and Nickel.
Drawings	Term used for the engineers to develop the new part for the factory to manufacture	Schematics	This is the drawings for the new parts to determine the price and lead time.

Entity-Relationship Diagram

Relational Model

The relational model is derived from the entity-relationship diagram to display the entities, attributes and relationships of the database project in a textual format. The resulting relations serve as the tables that make up the final database.

1. Derivation of Relations from ERD

Plant (PlantNum text, PlantLocation text)

OfficeStaff (EmployeeID text, FirstName text, LastName text, emailAddress text)

NewPart (NewPartNum text, Description text, MaterialType text, PartSize text, LeadTime text, Quantity text, Price decimal, EmployeeID text references EmployeeID to process the LeadTime, Size text, Price decimal)

Customer (CustomerNum text, CustomerName text, EmplyeeID text references EmployeeID to process order from customer)

MaterialType (MaterialTypeID text, Description text)

Drawings (DrawingNum text, Description text, NewPartNum text references NewPart to determine the PartSize, LeadTime and Price of the New Part)

ExistingPart (ExistingPartNum text, Description text, PartSize text, OnHand numeric, Price Decimal)

PartSize (SizeNum text, Description text)

2. Identification of Functional Dependencies

The first step in the normalization process is the identification of functional dependencies. Discovering the functional dependencies in the relational tables will eliminate redundant data and create better formed tables.

NewPart Schema

The New Part is in 2NF because the attributes are not part of the Primary Key. To determine the new part we need the LeadTime, MaterialType PartSize which equals the Price. It isn’t part of the primary key.

FD1 - NewPartNum à (LeadTime, MaterialType, PartSize)

FD2 – Price à (LeadTime, MaterialType, PartSize)

Drawings Schema

FD1 – DrawingNum à Description

FD2 – NewPartNum à (LeadTime, PartSize, Price)

3. Discussion of Normalization Process

a.) NewPart Schema

To repair this we need to create different entities for each

NewPart (NewPartNum text, Description)

MaterialType (MaterialTypeID text, Description text)

PartSize (SizeNum text, Descrption text)

b.) Drawings Schema

The Drawing Num is dependent on the description of the part and the New Part Num which is determined by the Leadtime and Price of the New Part Entity.

This Drawing Schema should be separate from the New Part Num it should be written out as follows,

Drawings (DrawingNum, Description)

4. Normalization Relations

Plant (PlantNum text, PlantLocation text)

OfficeStaff (EmployeeID text, FirstName text, LastName text, emailAddress text)

NewPart (NewPartNum text, Description text, MaterialTypeID text, PartSize text, LeadTime text, Price currency, Quantity text)

Customer (CustomerNum text, CustomerName text, EmplyeeID text)

MaterialType (MaterialTypeID text, Description text)

Drawings (DrawingNum text, Description text)

ExistingPart (ExistingPartNum text, Description text, PartSize text, OnHand text, Price currency)

PartSize (SizeNum text auto-generate, Description text)

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: PartNumber

Data Requirements: List the part number in the database to see if it is an existing part or a part that has not been manufactured.

SELECT ExistingPartNumber, Description

FROM ExistingPart

Query Name: EmpLocation

Data Requirements: List the employees First Name, Last Name the location they work at and there email addresses.

SELECT FirstName, LastName, PlantLocation, emailAddress

FROM OfficeStaff, Plant

Database Use Documentation

The database is intended for use by office staff, management, engineers and owner. When you first enter the database, you are presented with a startup screen.

After clicking “OK” button, you are presented with the Main Screen that serves as the gateway to the database. You proceed from here by selecting the Office Staff Entrance.

A prompt appears for your employee ID Number which consists of CT001 for Connecticut and MA01 for Massachusetts.

Office Staff

In the office staff’s view, you are unable to make changes to your personal information. You are able to look up existing parts and new parts manufactured along with the price and lead times for delivery of the parts.

Managers and Owner

In the manager’s view, you will not be able to make changes to your personal information. You are able to look up existing parts and new parts manufactured along with the price and lead time however you will be able to change the price and lead time for the parts. In the owner’s view you will have full access to change and update all tables in the database.