1. No category

Sample Furniture Inventory Tracking System
MTS Seating
7100 Industrial Drive
Temperance, MI 48182
Raymond Cata
Nathaniel Curtis
Jeffrey Hayton
Anthony Taylor
IS-421
Executive Summary .......................................................................................................... 3
Methodology/Tool Section................................................................................................ 4
Interviewees .................................................................................................................... 5
Websites.......................................................................................................................... 5
Software .......................................................................................................................... 5
Organization and SWOT Analysis .................................................................................. 6
Strengths ......................................................................................................................... 8
Weaknesses ..................................................................................................................... 9
Opportunities................................................................................................................. 10
Threats........................................................................................................................... 10
History of the IT/IS & Database Development Projects ............................................. 11
Work Breakdown Structure (WBS).............................................................................. 13
Database Development Process (SDLC) ....................................................................... 14
Plan ............................................................................................................................... 14
Analysis......................................................................................................................... 15
Entity Relationship Diagram for MTS Seating Sample Order Tracking.................. 15
Business Rules .......................................................................................................... 15
Business Rules .......................................................................................................... 16
Logical Design .............................................................................................................. 17
Relational Schema .................................................................................................... 17
Physical Design............................................................................................................. 21
Main Menu................................................................................................................ 21
Forms ........................................................................................................................ 22
Implementation ............................................................................................................. 29
Data ........................................................................................................................... 29
SQL statements ......................................................................................................... 34
Reports ...................................................................................................................... 48
Appendix.......................................................................................................................... 50
2
Executive Summary
MTS Seating is a small manufacturing company located in Temperance Michigan.
Profitability and growth has established the need for continued expansions in IT
resources. Information systems are tightly integrated within a number of its business
processes. Production planning, sales management and finance systems are a few
examples of IS employed. MTS has a firm understanding of the benefits that
information systems provide.
A Just In Time (JIT) inventory system for production is currently in place. Raw
materials for production are ordered as needed. By implementing a lean
manufacturing concept, MTS hasn’t had a need for an inventory tracking system.
MTS does however maintain a small stock of inventory. Sample furniture is kept on
hand as a means of providing customers a way to view product. The sample furniture
is also displayed in trade shows that MTS participates in throughout the year.
Due to the lean manufacturing philosophy in place, inventory tracking software is not
part of MTS’s information systems. We propose a simple, easy to use database to be
utilized for tracking the customer sample furniture. The use of automated tools is
essential in maintaining efficiencies in business operations.
3
Methodology/Tool Section
As part of the project requirements, a relational database will be used to input, store
and retrieve information for our database system. Oracle 9i will be the DBMS we will
be using. Oracle 9i will enable the creation of forms, queries, and reports that will be
associated with the database project. The Oracle software is also very powerful and
flexible. It is capable of growing, adapting, and changing along with needs of the
business. This is important because the enterprise we are focusing on is a growing
business with new customers being acquired frequently. We will also use software to
assist in the initial logical design phase the database. Software such as RF Flow and
Microsoft Project will be extremely useful in the completion of this project.
We will follow the Systems Development Life Cycle (SDLC) methodology. In the
planning phase we will gather information about the users and research possible
entities and attributes that we will use in the database. We may also possibly meet and
interview some employees of the company specifically in the IT department of MTS
Seating. In the analysis phase we will create an ERD and we will clearly define all
entities, attributes, and unique identifiers.
In the logical design phase we will develop the relational database model for the
business. In the physical design phase we will define technical specifications and
define the database to the DBMS. When we get to the implementation phase we will
enter all related data into our database. Finally in the maintenance phase we will test
the system and make any necessary changes to ensure easy adaptation by the users.
4
Interviewees
Nancy VanDevelde
Quality Assurance Manager
(734) 847-3875
Rodney Schell
IT Manager
(734) 847-3875
Websites
http://www.mtsseating.com
http://www.uscc.gov/
Software
Microsoft Project 2002
Oracle 9i database
Rfflow
5
Organization and SWOT Analysis
Mission Statement
"Our Goal is to provide all products and services in the committed time,
competitively priced, at the desired quality; costs effectively manufactured and
forever improve our performance."
MTS Seating is a privately owned and operated corporation. MTS is a manufacturer of
seating for the hospitality industry. With 325 employees MTS’s annual sales are $55
million.
MTS was started by Dean Curtis and Paul Swy who incorporated their business under a
small barn in Bedford, Michigan. At the time the company’s main business involved
strictly metal fabrication, production of steel chair and table legs, and other parts for the
automotive and aircraft markets.
In the early 1960s, finished chair and table frames became a major part of the company’s
product line, and by mid-decade MTS was assembling complete chairs. By the early
1970s, upholstery was done in house at the factory and no longer had to be outsourced.
This enabled the company to introduce the MTS seating line.
Today MTS has a 300,000 square foot, state-of-the-art, manufacturing facility located in
Temperance, Michigan. MTS has built a reputation for producing the best-built, best-
6
backed chairs in the industry. For over forty-five years MTS Seating has been a leading
provider of seats to the restaurant and hotel industry. MTS is committed to providing the
hospitality and food-service industry with total seating solutions. MTS can produce made
to order chairs, tables, and other furniture for its many customers in the hospitality
industry. Recent customers include City Grill, Quiznos Classic Subs, Newark
International Airport, Mancinos Pizza, and Denny’s.
A customer can select from a wide range of seating designs and mix and match,
upholstery, finish, and materials. MTS’s online catalog makes it easy for a customer to
get an idea of possible matching combinations. On the company’s website,
mtsseating.com, a potential customer can see seating displays for restaurants,
bars/lounges, guest rooms, and banquets. Customers can also see pictures of MTS’s
products in restaurants and other businesses. MTS has forged itself into a leading
manufacturer and supplier of hospitality seating in Michigan and across the country. This
is primarily the reason we choose MTS for our project.
President
Vice President
General Mgr
Sales Dept
(hotels)
Sales Dept
(food service)
Chief Financial
Officer
VP Sales
(hotels)
VP Sales
(food service)
Manufacturing
Dept
Supporting
Operations
Information
Systems Dept
7
Strengths
1. Custom Made Products
The size of the company puts MTS at a distinct advantage. MTS possesses
the capability of producing custom made furniture. A majority of the
competition both large and small are unable to recreate such proficiency in a
large scale. MTS has a substantial selection of fabrics, frames, and other
materials that customers can choose from. The information entered into the
production system reflects customer preference.
2. Just In Time (JIT) inventory
Lean manufacturing is a management philosophy that centers on the idea of
eliminating waste. A JIT inventory system is one process that follows the lean
manufacturing concept. Customer orders are ran in the production schedule a
week following orders. This enables MTS to use JIT and eliminate costs
associated with maintaining inventory.
3. Product Warranties
MTS Seating strongly backs its products. Extensive stress testing is
conducted on all products offered. As a result, MTS has strong confidence in
the furniture that is sold to the customer. Warranties on products last up to 12
years. If a customer is not satisfied with their product, it can be returned.
4. Utilization of Information Systems
Many small firms do not utilize an Information Systems department. MTS is
profitable enough to justify an IT department. Information systems are
utilized to support a majority of the businesses functions.
8
Weaknesses
1. Employing Specialized Labor
Individuals possessing special trade skills are a human resources issue that can
be problematic. Specialized laborers command higher salaries and are
sometimes hard to find. MTS employs a number of specialized laborers such
as welders and fabricators. This is not a major issue for MTS; however the
usage of specialized labor creates a weakness for the company.
2. Aged Machine Presses
A large array of equipment is used in the manufacturing process. MTS
currently uses state of art equipment; such as an automated machine that
paints metal frames. Machinery of this caliber is easily justified by its ROI.
MTS also utilizes machine presses that are as old as the company. These
presses are not used very often so justifying the purchase of replacement
presses is an issue.
3. Tracking of Customer Sample Furniture
MTS has space set aside in its manufacturing area where sample furniture is
stored. These items represent some of the models that are produced. The
sample furniture is sent to trade shows that MTS participates in throughout the
year. MTS’s customers may also request sample furniture to determine if it
suits their needs. MTS currently lacks an automated process of tracking the
sample furniture. A customer may request a sample from a sales coordinator
who must physically walk out to the sample area to see if the item is in stock.
This manual process is time consuming and inefficient.
9
Opportunities
1. blabla
2. blabla
3. blabla
Threats
1. Energy Costs
Rising costs of energy is of concern to everybody and MTS is not an
exception. MTS is impacted as its expenses associated with running its
business increases along with the costs of energy.
2. Cheap overseas labor
Labor costs in some of the other parts of the world are substantially lower than
those in the U.S. Manufacturers overseas are then able to produce similar
products cheaper than their American counterpart.
3. Subsidized steel
For more than a couple of decades, discussion on imported steel has remained
a topic of debate. There is worldwide excess production capacity for steel.
Dumping and government subsidized steel sent overseas to exporting markets
consequently becomes an issue. An example includes China’s current
government subsidized steel exporting practices. U.S. steel producing firms
benefit in the short run from low costs and domestic production of steel is
harmed. In the long run, these same firms are
10
History of the IT/IS & Database Development Projects
Current IT Environment
MTS Seating utilizes UNIX based servers in order to ensure reliable service for their
production scheduling systems. Their database applications are written in COBOL and
accessed from Microsoft Windows based desktops via Telnet over a TCP/IP network.
Business Problem
The company possesses sample furniture which is allows potential buyers to examine the
product. Samples can be taken to trade shows, on sales presentations, sent to clients for
evaluation or stored in showroom in company’s main offices. Currently the inventory of
sample furniture is not tracked. Because the inventory is not tracked, MTS Seating
cannot easily determine what samples are in stock. If a client requests a sample,
employees must physically go to the showroom and try to locate the samples visually.
Proposed Solution
We propose to develop an inventory tracking system that will allow MTS Seating to keep
track of furniture samples. Tracking will include where the sample is located and if the
sample has been sent out. This new system will also allow samples to be reserved.
Reserving will allow sales associates to request that a sample be sent to a customer.
Reservations will assist the shipping department in planning shipments to trade shows.
11
The system shall generate a report of overdue samples. This will allow sales associates to
follow-up with their clients. This also allows time to resolve reservation issues when
needed furniture is not returned on time and thus unavailable to fill the reservation.
The system will track specific quantities of various models/variations of furniture as well
as the specific pieces of furniture. Periodic physical inventories can be taken of samples
located in the showroom and an exception report noting discrepancies with system
maintained inventory.
The system will allow the user to remove products from inventory. Some reasons for
removal may include: damaged, lost, sold, donation, discontinued, etc.
12
Work Breakdown Structure (WBS)
It is a listing of the works/tasks involved in the project
It defines the total scope of the project
It is the foundation document in project management because it provides the basis for
planning, managing schedules, costs and changes
Enter all tasks and schedule into MS Project ( a hardcopy required)
13
Database Development Process (SDLC)
Plan
14
ModelNum
Desc
Image
UnitPrice
Qoh
Inventory
ShipNum
ModelNum
QShip
ShipmentLine
OrderNum
ModelNum
QResv
ShipNum
OrderNum
CarrierId
Shipment
shipped
on
OrderNum
CustNum
EmpNum
shipped
Reserved
Returned
on
SampleOrder
ordered
for
ReturnItem
ReturnLine
OrderLine
Returns
ReturnNum
CustNum
OrderNum
ReturnNum
ModelNum
DateRet
QRet
Carrier
Employee
Customer
CarrierId
Company
AccountNum
EmpNum
FName
LName
Title
Ext
CustNum
BusName
Address
City
State
Zip
CustPhone
Analysis
Entity Relationship Diagram for MTS Seating Sample Order Tracking
15
Business Rules
1. An EMPLOYEE may reserve one or more SAMPLEORDER(s). A
SAMPLEORDER is reserved by one and only one EMPLOYEE.
2. A CUSTOMER may order one or more SAMPLEORDER(s). A
SAMPLEORDER is ordered for one and only one CUSTOMER.
3. A SHIPMENT is shipped for one and only one SAMPLEORDER. A
SAMPLEORDER is shipped on one or more SHIPMENT(s).
4. A SHIPMENT is shipped with one and only one CARRIER. A CARRIER
ships one or more SHIPMENTS.
5. INVENTORY may be a shipment line for one or more SHIPMENT(s). A
SHIPMENT contains shipment lines for one or more items in INVENTORY.
6. A SAMPLEORDER contains order lines for one or more items in
INVENTORY. INVENTORY may be an order line for one or more
SAMPLEORDER(s).
7. A RETURNITEM contains return lines for one or more INVENTORY items.
An INVENTORY item may be a return line for one or more
RETURNITEM(s).
8. A RETURNITEM is returned on only one SAMPLEORDER. A
SAMPLEORDER may be returned on one or more RETURNITEM(s).
9. A CUSTOMER may return one or more RETURNITEM(s). Each
RETURNITEM is returned by one and only one CUSTOMER.
16
Logical Design
Relational Schema
1. Structure
Carrier (CarrierId, Company, AccountNum)
Customer (CustNum, BusName, Address, City, State, Zip, CustPhone)
Employee (EmpNum, FName, LName, Title, Ext)
Inventory (ModelNum, Desc, Image, UnitPrice, Qoh)
ReturnItem (ReturnNum, CustNum, OrderNum)
ReturnLine (ReturnNum, ModelNum, DateRet, QRet)
SampleOrder (OrderNum, CustNum, EmpNum)
OrderLine (OrderNum, ModelNum, QResv)
Shipment (ShipNum, OrderNum, CarrierId)
ShipmentLine (ShipNum, ModelNum, QShip)
2. Domains
Attribute
Logical Domain
Physical Domain
AccountNum
Carrier Account Number
Character (7)
Address
Customer Address
Character (20)
BusName
Customer Business Name
Character (15)
CarrierId
Carrier Identification Number
Character (3)
City
Customer City
Character (15)
Company
Carrier Name
Character (10)
CustNum
Customer Identification Number
Character (7)
17
CustPhone
Customer Phone Number
Character (10)
DateRet
Date Sample Item is Returned
Character (8)
Desc
Brief Physical Description
Character (15)
EmpNum
Employee Identification Number
Character (7)
Ext
Employee Phone Extension
Character (4)
FName
Employee First Name
Character (10)
Image
Digital Image of Item
BLOB
LName
Employee Last Name
Character (15)
ModelNum
Inventory Item Model Number
Character (5)
OrderNum
Sample Order Number
Character (10)
Qoh
Inventory Quantity On-Hand
Integer (3)
QResv
Inventory Quantity Reserved
Integer (3)
QRet
Quantity of Items Returned
Integer (2)
QShip
Quantity of Items Shipped
Integer (2)
ReturnNum
Return Identification Number
Character (7)
ShipNum
Shipment Identification Number
Character (7)
State
Customer State
Character (2)
Title
Employee Job Title
Character (15)
UnitPrice
Inventory Item Unit Price
Integer (5)
Zip
Customer Zip Code
Character (5)
18
3. Constraints
a. Structural Constraints
i. Primary Keys
CarrierId in CARRIER may not be null.
CustNum in CUSTOMER may not be null.
EmpNum in EMPLOYEE may not be null.
ModelNum in INVENTORY may not be null.
ReturnNum in RETURNITEM may not be null.
OrderNum in SAMPLEORDER may not be null.
ShipNum in SHIPMENT may not be null.
ii. Cardinalities
Maximum Cardinalities
Each RETURNITEM is returned by only one CUSTOMER
Each RETURNITEM is returned on only one SAMPLEORDER
Each SAMPLEORDER is reserved by only one EMPLOYEE
Each SAMPLEORDER is ordered for only one CUSTOMER
Each SHIPMENT is shipped using only one CARRIER
Each SHIPMENT is shipped on only one SAMPLEORDER
Minimum Cardinalities
ModelNum in INVENTORY must exist in ModelNum in RETURNLINE
ModelNum in INVENTORY must exist in ModelNum in
SHIPMENTLINE
19
ModelNum in INVENTORY must exist in ModelNum in ORDERLINE
ReturnNum in RETURNITEM must exist in ReturnNum in
RETURNLINE
ShipNum in SHIPMENT must exist in ShipNum in SHIPMENTLINE
OrderNum in SAMPLEORDER must exist in OrderNum in ORDERLINE
iii. Referential Integrity
CarrierId in SHIPMENT must exist in CarrierNum in CARRIER
CustNum in RETURNITEM must exist in CustNum in CUSTOMER
CustNum in SAMPLEORDER must exist in CustNum in CUSTOMER
EmpNum in SAMPLEORDER must exist in EmpNum in EMPLOYEE
ModelNum in RETURNLINE must exist in ModelNum in INVENTORY
ModelNum in ORDERLINE must exist in ModelNum in INVENTORY
ModelNum in SHIPMENTLINE must exist in ModelNum in INVENTORY
OrderNum in RETURNITEM must exist in OrderNum in SAMPLEORDER
OrderNum in SHIPMENT must exist in OrderNum in SAMPLEORDER
OrderNum in ORDERLINE must exist in OrderNum in SAMPLEORDER
ReturnNum in RETURNLINE must exist in ReturnNum in RETURNITEM
ShipNum in SHIPMENTLINE must exist in ShipNum in SHIPMENT
b. Operational Constraints
An inventory item can not be reserved unless that model is in stock.
20
Physical Design
Main Menu
21
Forms
22
23
24
25
26
27
28
Implementation
Data
Carrier
CarrierId
Fed
Ups
Yel
Rdw
Company
Federal Express
United Parcel Service
Yellow Truck
Roadway
AccountNum
1003214
1003215
1013216
1023218
Customer
CustNum
BusName
1007000
Applebees
1007001
Big Boys
1007002
Craker Barrel
1007003
Days Inn
2005000
Denny's
2005001
Chuck E Cheese
2005002
Chilis
2005003
Tavern on the Main
2006000
Butterfield
2006001
Marriot
Address
1156
Water St
2211 Pine
Dr
699
Belleville
Rd
987
Durham
Rd
8976 12
Mile Rd
7867 Bay
Ridge
73
Springfield
Rd
170
Walnut
Ridge
8109
Renfrew
Rd
52 Corning
St
City
State
Zip
CustPhone
Alpeena
MI
49707 7155558943
Ann Arbor
MI
48103 7343426689
Belleville
MI
48111 7345554978
West
Orange
NJ
17052 1236559090
Farmington
MI
Hills
48322 2488804322
Rochester
NH
3867 5679382214
Newport
NH
3773 9148760944
Atlanta
GA
31211 6562317632
Greensboro NC
27409 2345543098
Lock
Haven
17340 6178953333
PA
29
Employee
EmpNum
100998888
FName
Mark
Lname
Meyers
101223333
Shelia
Hernandez
102334444
103445555
104556666
105667777
105778888
105889999
Brian
Sarah
Ryan
Janet
Lisa
Edward
Zhang
Calson
Courtlandt
Park
Hummer
Cole
Inventory
ModelNum
210
211
311
315
CI17R4
1424base
808
808-30
813
813-30
814
814-30
933
933-30
941
941-30
Desc
Bumper Edge Table Tops
Bumper Edge Table Tops
Wood Edge Tops
Wood Edge Tops
Base, Cast Iron, round series
Base, Valencia
Chair, Design Classics
Barstool, model 808 matching item
Chair, Luckhardt, Design Classics
Barstool, Model 813 luckhardt
matching item
Chair, Loos, Design Classics, beige
Barstool, Model 814 Loos matching
item
Chair, Americana Collection, black,
stipes
Barstool, model 933 matching item
Chair, Americana Collection
Barstool, model 941 matching item
Title
Ext
President
3777
TradeShow
5261
Mgr
Salesperson
5788
Salesperson
8977
Salesperson
6722
Salesperson
8355
Production
8878
Programmer
2213
Image
UnitPrice
Qoh
210-top.jpg
2.25
211-top.jpg
4.75
311-top.jpg
7.25
315-top.jpg
6.50
CI_17R_2LS15.65
base.jpg
1424-base.jpg
14.50
808_Nouveau.jpg
10.75
808_30.jpg
12.50
813_Luckhardt.jpg
11.45
813_30.jpg
814_Loos.jpg
814_30.jpg
933.jpg
933_30
941.jpg
941_30.jpg
10
10
15
15
20
25
20
25
20
13.85
15
9.45
15
11.80
15
8.25
10
12.75
9.35
14.25
25
15
10
30
ReturnItem
ReturnNum
9999912
9999913
9999914
9999915
9999916
9999917
9999918
9999919
9999920
CustNum
1007000
2005001
2005001
1007002
2006001
1007001
2005003
1007003
1007000
OrderNum
ReturnLine
ReturnNum
9999912
9999912
9999912
9999913
9999914
9999915
9999915
9999916
9999917
9999917
9999918
9999919
9999920
9999920
ModelNum
210
808
808-30
933
941
210
808
941
210
814
933
941-30
210
814
DateRet
06141999
06141999
06141999
06171999
06171999
06201999
06201999
06201999
06221999
06221999
06221999
06251999
06251999
06251999
SampleOrder
OrderNum
888101
888102
888103
888104
888105
888106
888107
CustNum
1007000
2005001
2005001
1007002
2006001
1007001
2005003
888109
888103
888102
888104
888105
888106
888107
888108
888101
Qret
1
4
4
1
1
1
4
2
1
2
1
1
2
8
EmpNum
105778888
104556666
104556666
102334444
105667777
102334444
105889999
31
888108
888109
OrderLine
OrderNum
888101
888101
888102
888102
888102
888103
888104
888104
888105
888106
888106
888107
888108
888109
888109
888109
888110
888110
888110
888110
888110
888110
Shipment
ShipNum
7771234
7771235
7771236
7771237
7771238
7771239
7771240
7771241
7771242
1007003
1007000
ModelNum
814
210
941
CI17R4
808
933
210
808
941
210
814
933
941-30
210
808
808-30
311
808
808-30
933
933-30
1424base
OrderNum
888101
888102
888103
888104
888105
888106
888107
888108
888109
102334444
105778888
Qresv
8
2
1
1
4
1
1
4
2
1
2
1
1
1
4
4
2
2
2
2
2
2
CarrerId
Fed
Ups
Fed
Fed
Ups
Fed
Fed
Fed
Ups
32
ShipmentLine
ShipNum
7771234
7771234
7771235
7771235
7771235
7771236
7771237
7771237
7771238
7771239
7771239
7771240
7771241
7771242
7771242
7771242
7771243
7771243
7771243
7771243
7771243
7771243
ModelNum
814
210
941
CI17R4
808
933
210
808
941
210
814
933
941-30
210
808
808-30
311
808
808-30
933
933-30
1424base
Qship
8
2
1
1
4
1
1
4
2
1
2
1
1
1
4
4
2
2
2
2
2
2
33
SQL statements
applications, stored procedures, functions and triggers
DROP TABLE carrier CASCADE CONSTRAINTS;
DROP TABLE customer CASCADE CONSTRAINTS;
DROP TABLE employee CASCADE CONSTRAINTS;
DROP TABLE inventory CASCADE CONSTRAINTS;
DROP TABLE return_item CASCADE CONSTRAINTS;
DROP TABLE return_line CASCADE CONSTRAINTS;
DROP TABLE sample_order CASCADE CONSTRAINTS;
DROP TABLE order_line CASCADE CONSTRAINTS;
DROP TABLE shipment CASCADE CONSTRAINTS;
DROP TABLE shipment_line CASCADE CONSTRAINTS;
CREATE TABLE carrier
(carrier_id VARCHAR2(10),
company VARCHAR2(50),
account_num VARCHAR2(20),
CONSTRAINT carrier_carrier_id_pk PRIMARY KEY (carrier_id));
CREATE TABLE customer
(custnum NUMBER(10),
busname VARCHAR2(50),
address VARCHAR2(50),
city VARCHAR2(50),
state CHAR(2),
zip VARCHAR2(10),
custphone VARCHAR2(10),
CONSTRAINT customer_custnum_pk PRIMARY KEY(custnum));
CREATE TABLE employee
(emp_num NUMBER(10),
f_name VARCHAR2(50),
l_name VARCHAR2(50),
title VARCHAR2(50),
ext VARCHAR2(10),
CONSTRAINT employee_emp_num_pk PRIMARY KEY (emp_num));
CREATE TABLE sample_order
(order_num NUMBER(10),
custnum NUMBER(10),
emp_num NUMBER(10),
CONSTRAINT sample_order_order_num_pk PRIMARY KEY(order_num),
CONSTRAINT sample_order_custnum_fk FOREIGN KEY (custnum) REFERENCES
customer(custnum),
34
CONSTRAINT sample_emp_num_fk FOREIGN KEY (emp_num) REFERENCES
employee(emp_num));
CREATE TABLE inventory
(model_num VARCHAR2(10),
description VARCHAR2(200),
image VARCHAR2(50),
unit_price NUMBER(10,2),
qoh NUMBER(7),
CONSTRAINT inventory_model_num_pk PRIMARY KEY (model_num));
CREATE TABLE return_item
(return_num NUMBER(10),
custnum NUMBER(10),
order_num NUMBER(10),
CONSTRAINT return_item_return_num_pk PRIMARY KEY(return_num),
CONSTRAINT return_item_custnum_fk FOREIGN KEY (custnum) REFERENCES
customer(custnum),
CONSTRAINT return_item_order_num_fk FOREIGN KEY (order_num)
REFERENCES
sample_order(order_num));
CREATE TABLE shipment
(shipment_num NUMBER(10),
order_num NUMBER(10),
carrier_id VARCHAR2(10),
CONSTRAINT shipment_order_shipment_num_pk PRIMARY KEY(shipment_num),
CONSTRAINT shipment_carrier_id_fk FOREIGN KEY (carrier_id) REFERENCES
carrier(carrier_id),
CONSTRAINT shipment_order_num_fk FOREIGN KEY (order_num) REFERENCES
sample_order(order_num));
CREATE TABLE order_line
(order_num NUMBER(10),
model_num VARCHAR2(10),
q_resv NUMBER(10),
CONSTRAINT order_line_order_num_pk PRIMARY KEY(order_num, model_num),
CONSTRAINT order_line_model_num_fk FOREIGN KEY (model_num)
REFERENCES
inventory(model_num),
CONSTRAINT order_line_order_num_fk FOREIGN KEY (order_num) REFERENCES
sample_order(order_num));
CREATE TABLE return_line
(return_num NUMBER(10),
model_num VARCHAR2(10),
35
date_ret DATE,
q_ret NUMBER(10),
CONSTRAINT return_line_return_num_pk PRIMARY KEY(return_num, model_num),
CONSTRAINT return_line_model_num_fk FOREIGN KEY (model_num)
REFERENCES
inventory(model_num),
CONSTRAINT return_line_return_num_fk FOREIGN KEY (return_num)
REFERENCES
return_item(return_num));
CREATE TABLE shipment_line
(shipment_num NUMBER(10),
model_num VARCHAR2(10),
q_ship NUMBER(10),
CONSTRAINT shipment_line_shipment_num_pk PRIMARY KEY(shipment_num,
model_num),
CONSTRAINT shipment_line_model_num_fk FOREIGN KEY (model_num)
REFERENCES
inventory(model_num),
CONSTRAINT shipment_line_shipment_num_fk FOREIGN KEY (shipment_num)
REFERENCES
shipment(shipment_num));
--Populate data for table carrier(CarrierId, Company, AccountNum)
--dbms_output.put_line('Inserting Carrier...');
INSERT INTO carrier VALUES
('FED', 'FedEX','1003214' );
INSERT INTO carrier VALUES
('UPS','United Parcel Service','1003215');
INSERT INTO carrier VALUES
('YEL','Yellow Truck', '1023216');
INSERT INTO carrier VALUES
('RDW','Roadway', '1023218');
-- Populate data for table Customer(CustNum, BusName, Address, City, State, Zip,
CustPhone)
INSERT INTO customer VALUES
('1007000', 'Apple Bees', '1156 Water Street','Alpena', 'MI', '49707',
'7155558943');
INSERT INTO customer VALUES
36
('1007001', 'Big Boys', '2211 Pine Drive', ' Ann Arbor', 'MI', '48103',
'7343426689');
INSERT INTO customer VALUES
('1007002', 'Craker Barrel', '699 Belleville Rd', 'Belleville', 'MI', '48111',
'7345554978');
INSERT INTO customer VALUES
('1007003', 'Days Inn', '987 Durham Rd', 'West Orange', 'NJ', '07052',
'6559090');
INSERT INTO customer VALUES
('2005000', 'Denny''s', '8976 12 Mile Rd.', 'Farmington Hills', 'MI', '48322',
'2488804322');
INSERT INTO customer VALUES
('2005001', 'Chuck E Cheese', '7867 Bay Ridge', 'Rochester', 'NH', '03867',
'5679382214');
INSERT INTO customer VALUES
('2005002', 'Chilis', '73 Springfield Rd', 'Newport', 'NH', '03773',
'9148760944');
INSERT INTO customer VALUES
('2005003', 'Tavern on The Main', '170 Walnut Ridge', 'Atlanta', 'GA', '31211',
'6562317632');
INSERT INTO customer VALUES
('2006000', 'Butterfield', '8109 Renfrew Rd', 'Greensboro', 'NC', '27409',
'2345543098');
INSERT INTO customer VALUES
('2006001', 'Marriot', '52 Corning St', 'Lock Haven', 'PA', '17340',
'6178953333');
--Populate data for table Employee(EmpNum, FName, LName, Title, Ext)
INSERT INTO employee VALUES
('100998888', 'Mark', 'Myers', 'President', '3777');
INSERT INTO employee VALUES
('101223333', 'Shelia', 'Hernandez', 'TradeShow Mgr', '5261');
INSERT INTO employee VALUES
('102334444', 'Brian', 'Zhang', 'Salesperson', '5788');
37
INSERT INTO employee VALUES
('103445555', 'Sarah', 'Calson', 'Salesperson', '8977');
INSERT INTO employee VALUES
('104556666', 'Ryan', 'Courtlandt', 'Salesperson', '6722');
INSERT INTO employee VALUES
('105667777', 'Janet', 'Park', 'Salesperson', '8355');
INSERT INTO employee VALUES
('105778888', 'Lisa', 'Hummer', 'Production Clerk', '8878');
INSERT INTO employee VALUES
('105889999', 'Edward', 'Cole', 'Programmer', '2213');
--Populate data for table inventory(ModelNum, Desc, Image, UnitPrice, Qoh)
INSERT INTO inventory VALUES
('210', 'Bumper Edge Table Tops', '210-top.jpg', '2.25', '10');
INSERT INTO inventory VALUES
('211', 'Bumper Edge Table Tops', '211-top.jpg', '4.75', '10');
INSERT INTO inventory VALUES
('311', 'Wood Edge Tops', '311-top.jpg', '7.25', '15');
INSERT INTO inventory VALUES
('315', 'Wood Edge Tops', '315-top.jpg', '6.50', '15');
INSERT INTO inventory VALUES
('CI17R4', 'Base, Cast Iron, round series', 'CI_17R_2LS-base.jpg', '15.65',
'20');
INSERT INTO inventory VALUES
('1424base', 'Base, Valencia', '1424-base.jpg', '14.50', '25');
INSERT INTO inventory VALUES
('808', 'Chair, Design Classics', '808_Nouveau.jpg', '10.75', '20');
INSERT INTO inventory VALUES
('808-30', 'Barstool, model 808 matching item', '808_30.jpg', '12.50', '25');
INSERT INTO inventory VALUES
('813', 'Chair Luckhardt, Design Classics', '813_Luckhardt.jpg', '11.45', '20');
INSERT INTO inventory VALUES
38
('813-30', 'Barstool, model 813 Luckhardt matching item', '813_30.jpg', '13.85',
'15');
INSERT INTO inventory VALUES
('814', 'Chair, Loos, Design Classics, beige', '814_Loos.jpg', '9.45', '15');
INSERT INTO inventory VALUES
('814-30', 'Barstool, model 814 Loos matching item', '814_30.jpg', '11.80',
'15');
INSERT INTO inventory VALUES
('933', 'Chair, Americana Collection, black, striped', '933.jpg', '8.25', '10');
INSERT INTO inventory VALUES
('933-30', 'Barstool, model 933 matching item', '933_30.jpg', '12.75', '25');
INSERT INTO inventory VALUES
('941', 'Chair, Americana Collection', '941.jpg', '9.35', '15');
INSERT INTO inventory VALUES
('941-30', 'Barstool, model 941 matching item', '941_30.jpg', '14.25', '10');
--Populate data for table sample_order(OrderNum, CustNum, EmpNum)
INSERT INTO sample_order VALUES
('888101', '1007000', '105778888');
INSERT INTO sample_order VALUES
('888102', '2005001', '104556666');
INSERT INTO sample_order VALUES
('888103', '2005001', '104556666');
INSERT INTO sample_order VALUES
('888104', '1007002', '102334444');
INSERT INTO sample_order VALUES
('888105', '2006001', '105667777');
INSERT INTO sample_order VALUES
('888106', '1007001', '102334444');
INSERT INTO sample_order VALUES
('888107', '2005003', '105889999');
INSERT INTO sample_order VALUES
('888108', '1007003', '102334444');
39
INSERT INTO sample_order VALUES
('888109', '1007000', '105778888');
INSERT INTO sample_order VALUES
('888110', '2006000', '102334444');
--Populate data for table order_line(OrderNum, ModelNum, QResv)
INSERT INTO order_line VALUES
('888101', '814', '8');
INSERT INTO order_line VALUES
('888101', '210', '2');
INSERT INTO order_line VALUES
('888102', '941', '1');
INSERT INTO order_line VALUES
('888102', 'CI17R4', '1');
INSERT INTO order_line VALUES
('888102', '808', '4');
INSERT INTO order_line VALUES
('888103', '933', '1');
INSERT INTO order_line VALUES
('888104', '210', '1');
INSERT INTO order_line VALUES
('888104', '808', '4');
INSERT INTO order_line VALUES
('888105', '941', '2');
INSERT INTO order_line VALUES
('888106', '210', '1');
INSERT INTO order_line VALUES
('888106', '814', '2');
INSERT INTO order_line VALUES
('888107', '933', '1');
INSERT INTO order_line VALUES
40
('888108', '941-30', '1');
INSERT INTO order_line VALUES
('888109', '210', '1');
INSERT INTO order_line VALUES
('888109', '808', '4');
INSERT INTO order_line VALUES
('888109', '808-30', '4');
INSERT INTO order_line VALUES
('888110', '311', '2');
INSERT INTO order_line VALUES
('888110', '808', '2');
INSERT INTO order_line VALUES
('888110', '808-30', '2');
INSERT INTO order_line VALUES
('888110', '933', '2');
INSERT INTO order_line VALUES
('888110', '933-30', '2');
INSERT INTO order_line VALUES
('888110', '1424base', '2');
--Populate data for table return_item(ReturnNum, CustNum, OrderNum)
INSERT INTO return_item VALUES
('9999912', '1007000', '888109');
INSERT INTO return_item VALUES
('9999913', '2005001', '888103');
INSERT INTO return_item VALUES
('9999914', '2005001', '888102');
INSERT INTO return_item VALUES
('9999915', '1007002', '888104');
INSERT INTO return_item VALUES
('9999916', '2006001', '888105');
41
INSERT INTO return_item VALUES
('9999917', '1007001', '888106');
INSERT INTO return_item VALUES
('9999918', '2005003', '888107');
INSERT INTO return_item VALUES
('9999919', '1007003', '888108');
INSERT INTO return_item VALUES
('9999920', '1007000', '888101');
--Populate data for table return_line(ReturnNum, ModelNum, DateRet, QRet)
INSERT INTO return_line VALUES
('9999912', '210', TO_DATE('06/14/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999912', '808', TO_DATE('06/14/1999', 'MM/DD/YYYY'), '4');
INSERT INTO return_line VALUES
('9999912', '808-30', TO_DATE('06/14/1999', 'MM/DD/YYYY'), '4');
INSERT INTO return_line VALUES
('9999913', '933', TO_DATE('06/17/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999914', '941', TO_DATE('06/17/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999915', '210', TO_DATE('06/20/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999915', '808', TO_DATE('06/20/1999', 'MM/DD/YYYY'), '4');
INSERT INTO return_line VALUES
('9999916', '941', TO_DATE('06/20/1999', 'MM/DD/YYYY'), '2');
INSERT INTO return_line VALUES
('9999917', '210', TO_DATE('06/21/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999917', '814', TO_DATE('06/22/1999', 'MM/DD/YYYY'), '2');
INSERT INTO return_line VALUES
42
('9999918', '933', TO_DATE('06/22/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999919', '941-30', TO_DATE('06/25/1999', 'MM/DD/YYYY'), '1');
INSERT INTO return_line VALUES
('9999920', '210', TO_DATE('06/25/1999', 'MM/DD/YYYY'), '2');
INSERT INTO return_line VALUES
('9999920', '814', TO_DATE('06/25/1999', 'MM/DD/YYYY'), '8');
--Populate data for table shipment(ShipNum, OrderNum, CarrierId)
INSERT INTO shipment VALUES
('7771234', '888101', 'FED');
INSERT INTO shipment VALUES
('7771235', '888102', 'UPS');
INSERT INTO shipment VALUES
('7771236', '888103', 'FED');
INSERT INTO shipment VALUES
('7771237', '888104', 'FED');
INSERT INTO shipment VALUES
('7771238', '888105', 'UPS');
INSERT INTO shipment VALUES
('7771239', '888106', 'FED');
INSERT INTO shipment VALUES
('7771240', '888107', 'FED');
INSERT INTO shipment VALUES
('7771241', '888108', 'FED');
INSERT INTO shipment VALUES
('7771242', '888109', 'UPS');
INSERT INTO shipment VALUES
('7771243', '888110', 'YEL');
--Populate data for table shipment_line(ShipNum, ModelNum, QShip)
43
INSERT INTO shipment_line VALUES
('7771234', '814', '8');
INSERT INTO shipment_line VALUES
('7771234', '210', '2');
INSERT INTO shipment_line VALUES
('7771235', '941', '1');
INSERT INTO shipment_line VALUES
('7771235', 'CI17R4', '1');
INSERT INTO shipment_line VALUES
('7771235', '808', '4');
INSERT INTO shipment_line VALUES
('7771236', '933', '1');
INSERT INTO shipment_line VALUES
('7771237', '210', '1');
INSERT INTO shipment_line VALUES
('7771237', '808', '4');
INSERT INTO shipment_line VALUES
('7771238', '941', '2');
INSERT INTO shipment_line VALUES
('7771239', '210', '1');
INSERT INTO shipment_line VALUES
('7771239', '814', '2');
INSERT INTO shipment_line VALUES
('7771240', '933', '1');
INSERT INTO shipment_line VALUES
('7771241', '941-30', '1');
INSERT INTO shipment_line VALUES
('7771242', '210', '1');
INSERT INTO shipment_line VALUES
('7771242', '808', '4');
INSERT INTO shipment_line VALUES
44
('7771242', '808-30', '4');
INSERT INTO shipment_line VALUES
('7771243', '311', '2');
INSERT INTO shipment_line VALUES
('7771243', '808', '2');
INSERT INTO shipment_line VALUES
('7771243', '808-30', '2');
INSERT INTO shipment_line VALUES
('7771243', '933', '2');
INSERT INTO shipment_line VALUES
('7771243', '933-30', '2');
INSERT INTO shipment_line VALUES
('7771243', '1424base', '2');
COMMIT;
45
--Packing slip SQL
SELECT ALL CUSTOMER.CUSTNUM, CUSTOMER.BUSNAME,
CUSTOMER.ADDRESS, CUSTOMER.CITY,
CUSTOMER.STATE, CUSTOMER.ZIP, SAMPLE_ORDER.ORDER_NUM,
SHIPMENT_LINE.MODEL_NUM,
SHIPMENT_LINE.Q_SHIP, SHIPMENT_LINE.SHIPMENT_NUM,
EMPLOYEE.F_NAME, EMPLOYEE.L_NAME, EMPLOYEE.EMP_NUM,
EMPLOYEE.EXT
FROM CUSTOMER, EMPLOYEE, SAMPLE_ORDER, SHIPMENT_LINE,
SHIPMENT
WHERE SAMPLE_ORDER.ORDER_NUM = :Order_desc
AND ((CUSTOMER.CUSTNUM = SAMPLE_ORDER.CUSTNUM)
AND (SAMPLE_ORDER.ORDER_NUM = SHIPMENT.ORDER_NUM)
AND (SHIPMENT_LINE.SHIPMENT_NUM = SHIPMENT.SHIPMENT_NUM)
AND (EMPLOYEE.EMP_NUM = SAMPLE_ORDER.EMP_NUM))
--User Defined Perameters
SELECT sample_order.order_num
FROM sample_order
--Unreturned Orders SQL
SELECT ALL SHIPMENT.SHIPMENT_NUM, CUSTOMER.CUSTNUM,
EMPLOYEE.EMP_NUM, RETURN_LINE.RETURN_NUM,
RETURN_LINE.MODEL_NUM,
RETURN_LINE.Q_RET, SHIPMENT_LINE.Q_SHIP,
SAMPLE_ORDER.ORDER_NUM,
CUSTOMER.BUSNAME, CUSTOMER.CUSTPHONE, EMPLOYEE.EXT,
EMPLOYEE.L_NAME
FROM CUSTOMER, EMPLOYEE, SHIPMENT, SHIPMENT_LINE,
SAMPLE_ORDER, RETURN_ITEM, RETURN_LINE
WHERE ((RETURN_ITEM.ORDER_NUM = SAMPLE_ORDER.ORDER_NUM)
AND (RETURN_ITEM.RETURN_NUM = RETURN_LINE.RETURN_NUM)
AND (CUSTOMER.CUSTNUM = SAMPLE_ORDER.CUSTNUM)
AND (EMPLOYEE.EMP_NUM = SAMPLE_ORDER.EMP_NUM)
AND (SHIPMENT.ORDER_NUM = SAMPLE_ORDER.ORDER_NUM)
AND (SHIPMENT.SHIPMENT_NUM = SHIPMENT_LINE.SHIPMENT_NUM)
AND NOT (RETURN_LINE.Q_RET = SHIPMENT_LINE.Q_SHIP))
--PL/SQL
function CF_items_outstandingFormula return Number is
outstanding NUMBER;
begin
outstanding := :q_ship - :q_ret;
return(outstanding);
end;
46
--Inventory Count List SQL
SELECT model_num, description, unit_price, qoh
FROM inventory
--Inventory SQL
SELECT model_num, description, unit_price, qoh
FROM inventory
47
Reports
48
49
Appendix
Note: CD and student evaluations in sleeve of project binder
Meeting Minutes
Meeting 2-17-2006 7:00 pm
IS 421 Project meeting
Agenda
Decide what case to do.
Present
Jeff Hayton
Nathaniel Curtis
Ray Cata
Tony Taylor
Absent
None
Discussion to pursue MTS Seating Inc. case if possible, otherwise come up with some
alternatives before 3-10-2006.
Set next meeting for 3-10-2006.
Adjourned
Meeting 3-10-2006 7:00 pm
IS 421 Project meeting
Agenda
Discuses the case
Discus proposal
Decide which system we want to develop
Divide the work for the proposal
Present
Jeff Hayton
Nathaniel Curtis
Ray Cata
Tony Taylor
Absent
50
None
Discussion of the case that we had selected.
Decided to develop an inventory tracking system for MTS Seating Inc.
Project introduction
Current IT environment
Business Problem/Solution
Methodologies and tools/sources
Users involved
Ray
Jeff
Jeff
Nathan
Tony
Adjourned
Meeting 3-24-2006 7:00 pm
IS 421 Project meeting
Agenda
Talk about answers to questions from vender?
Dividing the first faze of the project
1. ERD
2. Swat analysis
3. Business Rules
Discuss ERD
Present
Jeff Hayton
Nathaniel Curtis
Ray Cata
Tony Taylor
Absent
None
Talked about answers
Discussed ERD
Divided the work
1. ERD
2. Swat analysis
3. Business rules
Every One
Tony
Every One
Adjourned
51
Meeting 3-31-2006 7:00 pm
IS 421 Project meeting
Agenda
Discuss ERD
1. Finalizing schema
Present
Jeff Hayton
Nathaniel Curtis
Ray Cata
Tony Taylor
Absent
None
Discussed ERD
Divided the work
4. Finalizing schema
Adjourned
Meeting 4-7-2006 7:00 pm
IS 421 Project meeting
Agenda
4. Talk about
a. Forms
b. Tables
c. Reports
5. Dividing the SQL coding
6. What we have left to do
Present
Jeff Hayton
Nathaniel Curtis
Ray Cata
Tony Taylor
Absent
None
52
1. Talk about
a. Forms
i. Enter Inventory Adjust
ii. Lookup
iii. Create order
iv. View Orders
v. Create shipment
vi. Return Items
b. Tables
c. Reports
i. Physical inventory Count sheet
ii. Over due Samples
iii. Packing List
2. Dividing the SQL coding
Adjourned
53