Download - Powerpoint
Database System for an Ordering Process for Ford
Motor Company
BUSA 310 – Information SystemsMay 14, 2008
Nicole Ferrin & Joey Bottcher
Ford Background
Design, develop, manufacture, sales and service of cars, trucks and service parts
Vehicles include cars for the small, medium, and large and premium segments, trucks, buses, vans, full-size pickups, sports utility vehicles, and vehicles for the medium and heavy segments
Provides products and services through the operations of Ford North America, Ford South America, Ford Europe, Premier Automotive Group, and Ford Asia Pacific and Africa/Mazda segments
The Ordering Process
Developed a database that includes the orders being made and the warehouses, suppliers and parts involved.
Helps identify certain characteristics through queries such as the speed or quality of suppliers
Benefits of the database: Help make quicker decisions Minimize redundancy Inevitably improve efficiency
Order Table
3005/28/20081081004105
10007/27/20081671002104
8006/12/20081381003103
5505/8/20081201001102
7008/8/20081111005101
p_quantityo_delivery datep_idw_ido_id
Warehouse Table
764-397-185629683KAKansas City
123rd Street1005
154-293-629668387TXSan Antonio
14923 Grand Street
1004
354-624-316268354TXDallas3rd Avenue1003
645-398-197886352CALos Angeles
14262 Maple Drive
1002
482-376-153449345OHCleveland72nd street1001
w_phonew_zipw_statew_cityw_streetw_id
Part Table
7243$500.00Electronic Products196
7243$300.00Overhead Systems188
7013$220.00Molded Plastics187
7243$150.00Seat systems178
7967$600.00Fuel Tanks167
7194$140.00Stampings166
7967$130.00Engine Coolant Components
145
7967$700.00Instrument Panels138
7243$120.00Door Panels136
7113$100.00Consol120
7378$180.00Seat Systems112
7378$540.00Electronic Products111
7195$160.00Stampings108
s_idp_pricep_namep_id
Supplier Table
54248-297-710448111MIVan Buren TownshipOne Village Center DriveVisteon7967
78248-447-150048086MISouthfield21557 Telegraph RoadLear7378
85253-563-958748254WASeattle1683 East Ridge LaneContinental Automotive7307
93414-524-120053201WIMilwaukee5757 North Green Bay AvenueJohnson Controls7243
58404-699-202230331GAAtlanta3550 Atlanta Industria ParkwaySanderson Industries7195
95248-675-600048377MINovi27175 Haggerty RoadTower Automotive7194
88248-730-010049002MIPortage6715 South Sprinkle RoadSummit Polymers Inc.7113
79651-407-570055069MNRush City920 Field Avenue SouthPlastech7013
s_qualitys_speeds_phones_zips_states_citys_streets_names_id
Queries
Managers use this query table to see which suppliers are supplying the most products to each warehouse. It helps management identify which suppliers do the most business with Ford. This query uses information from the warehouse, supplier, and order tables.
Most Frequently Used Suppliers at Warehouses
1004Sanderson Industries7195
1002Visteon7967
1003Visteon7967
1001Summit Polymers Inc.7113
1005Lear7378
w_ids_names_id
Supplier Quality and Speed Greater or Equal to 7
This query table lists suppliers with the highest quality and speed ratings. This information assists managers in the decision making process of choosing the suppliers who offer the best products and service. This query is formed using information from only the supplier table.
79Plastech
88Summit Polymers Inc.
78Lear
s_qualitys_speeds_name
Orders with Part Quantities of Greater or Equal to 700
Managers use this query to see which orders are asking for parts with quantities of 700 or more. Managers can expect to know how many units will be arriving. This query is formed using the orders and product table.
1000Fuel Tanks104
800Instrument Panels103
700Electronic Products101
p_quantityp_nameo_id
Products Received from Suppliers with Speed Rates of 6 or Less
Managers can use this query to see which products are being received from suppliers with a low speed rate of 6 or less. This query provides information that may assist managers in deciding what suppliers they may potentially weed out. This query is formed from information from the part and supplier tables.
4VisteonEngin Coolant Components
4VisteonFuel Tanks
4VisteonInstrument Panels
3Johnson ControlsElectronic Products
3Johnson ControlsOverhead Systems
3Johnson ControlsDoor Panels
3Johnson ControlsSeat systems
5Tower AutomotiveStampings
s_speeds_namep_name
Companies Supplying Seat Systems
Managers use this very specific query to identify the companies supplying seat systems and for what price. This query provides value to the firm because they can use it to compare prices and ultimately reduce the cost of inputs. This query uses information from the supplier and part tables.
$180.00Seat SystemsLear
$150.00Seat systemsJohnson Controls
p_pricep_names_name