Chris Avery Jonathan Cooper

1

• Presented by
• Team 1
• Chris Avery Jonathan Cooper
• Matt Garman Evelyn Ellington
• Rachel Lee Jeremy Thielemier

2
History

• Deere Company was founded in 1837
• One-man blacksmith shop into a worldwide
  corporation
• Is in 160 countries and employs approx 40,000
  people worldwide
• Four manufacturing divisions
• Agricultural Equipment
• Commercial and Consumer Equipment
• Construction and Forestry Equipment
• John Deere Power Systems

3
History Cont

• Provide world-class financial solutions, hand
  tools through John Deere Credit
• Health offers quality health care benefits at a
  reasonable cost.
• John Deere parts provide quality parts and
  service to John Deere equipment owners, as well
  as owners of commercial brands of equipment.

4
Introduction

• This is an international database environment
  research project. John Deere has a very large
  database.
• The international parts differ somewhat but are
  basically the same. Models are continuously
  subsequent to change.
• John Deere has numerous finance offers both
  online and offline.
• The John Deere web site gives good specific
  detailed map and driving directions for locating
  nearby dealerships.
• They provide links to the dealerships and to
  various other products that they offer.

5
John Deere Object Oriented Diagrams

• Combines
• Mowers
• Tractors

6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
E-R EER Diagrams

• Combines
• Mowers
• Tractors

10
(No Transcript)
11
(No Transcript)
12
(No Transcript)
13
John Deere Enhanced ER Diagram
14
(No Transcript)
15
(No Transcript)
16
Normalization -1st form
17
Normalization -2nd form
18
Normalization -2nd form
19
Normalization -2nd form
20
Normalization -3rd form
21
Normalization -3rd form
22
Normalization -3rd form
23
Normalization -3rd form
24
Normalization -3rd form
25
Normalization -3rd form
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
John Deere Access Database
30
Customer Table
31
Customer Table cont
32
Combine Table
33
Combine Table cont
34
Mower Table
35
Mower Table cont
36
Tractor Table
37
Tractor table cont
38
John Deere Dealer Table
39
John Deere Dealer Table cont
40
Customer Order Table
41
Customer Order Table cont
42
Payment Table
43
Payment Table cont
44
Financial Method Table
45
Financial Method Table cont
46
SQL
Here is part of the Oracle SQL commands and
queries
47
Using Create

• SQL CREATE TABLE TRACK
• (MODEL CHAR(12),
• 3 VIN CHAR(10),
• 4 STANDARD_TIRES CHAR(20),
• 5 HYDRAULICS CHAR(20))
• Table created.
• SQL CREATE TABLE UTILITY
• 2 (MODEL CHAR(12),
• 3 VIN CHAR(10),
• 4 OPERATOR_STATION CHAR(25),
• 5 FINAL_DRIVE CHAR(25))

• SQL CREATE TABLE SPECIALTY
• 2 (MODEL CHAR(12),
• 3 VIN CHAR(10),
• 4 PERFORMANCE CHAR(20),
• 5 FUEL_USE CHAR(25))
• Table created.
• SQL CREATE TABLE COMPACT_UTILITY
• 2 (MODEL CHAR(12),
• 3 VIN CHAR(10),
• 4 MISCELLANEOUS CHAR(20),
• 5 ENGINE CHAR(15))
• Table created.

48
Using Insert

• SQL INSERT INTO TRACK
• 2 VALUES
• 3 ('8120T 170hp', '100001', '16"(406mm)',
  '33.5(127)')
• 1 row created.
• SQL INSERT INTO TRACK
• 2 VALUES
• 3 ('8220T 190hp', '100002', '16"(406mm)',
  '33.5(127)')
• 1 row created.
• SQL INSERT INTO ROW_CROP
• 2 VALUES
• 3 ('7210 95hp', '400001','116.9 (2269)', '108.13
  (2747)')
• 1 row created.
• SQL INSERT INTO ROW_CROP

• SQL INSERT INTO UTILITY
• 2 VALUES
• 3 ('5220 53hp', '500001', 'Straddle', 'Inboard
  Planetary')
• 1 row created.
• SQL INSERT INTO UTILITY
• 2 VALUES
• 3 ('5520 89hp', '500002', 'Flat', 'Inboard
  Planetary')
• 1 row created.
• SQL INSERT INTO FOUR_WHEEL_DRIVE
• 2 VALUES
• 3 ('9120 280hp', '300001', '24/6 PowrSnyc
  (MST)', '1.75-in. (44.5)')
• 1 row created.

49
Using Describe

• SQL DESCRIBE TRACK
• Name Null?
  Type
• -----------------------------------------
  -------- ---------------------
• MODEL
  CHAR(12)
• VIN
  CHAR(10)
• STANDARD_TIRES CHAR(20)
• HYDRAULICS
  CHAR(20)
• SQL DESCRIBE SPECIALTY
• Name Null?
  Type
• -----------------------------------------
  -------- ---------------------
• MODEL
  CHAR(12)
• VIN
  CHAR(10)
• PERFORMANCE
  CHAR(20)
• FUEL_USE
  CHAR(25)

• SQL DESCRIBE FOUR_WHEEL_DRIVE
• Name Null?
  Type
• -----------------------------------------
  -------- ------------------
• MODEL
  CHAR(12)
• VIN
  CHAR(10)
• TRANSMISSION CHAR(25)
• PTO
  CHAR(20)
• SQL DESCRIBE ROW_CROP
• Name Null?
  Type
• -----------------------------------------
  -------- ------------------
• MODEL
  CHAR(12)
• VIN
  CHAR(10)
• TWO_WD_DIMENSION CHAR(20)
• MFWD_DIMENSION CHAR(20)

50
SQL DESCRIBE COMMANDS

• SQL DESCRIBE CUSTOMER_ORDER
• Name
  Null? Type -----------------------------------
  ------ -------- ----------------------------
  ORDER_NO
  CHAR(7) PICK_UP_DATE
  DATE AMOUNT
  DECIMAL(7,2) ORDER_DATE
  DATE MODEL_NO
  CHAR(12)
  CUSTOMER_INFORMATION CHAR(25)
  DEALER_INFORMATION
  CHAR(25)SQL DESCRIBE PAYMENT Name
  Null? Type
  -----------------------------------------
  -------- ---------------------------- INVOICE_NO
  CHAR(5)
  CUSTOMER_ID CHAR(9) AMOUNT
  DECIMAL(7,2)
  PAYMENT_METHOD CHAR(11)

51
Queries in SQL
52
Queries in SQL

• ALTER TABLE PAYMENT
• ADD CC_TYPE CHAR(7), CC_NUMB INTEGER, EXP_DATE
  DATE
• ALTER TABLE FINANCE_METHOD
• ADD BALANCE DECIMAL(7,2)

53
Using Select

• SQL SELECT
• 2 FROM TRACK
• MODEL VIN STANDARD_TIRES HYDRAULICS
• ------------ ---------- --------------------------
  --- -------------------
• 8120T 170hp 100001 16"(406mm)
  33.5(127)
• 8120T 170hp 100001 16"(406mm)
  33.5(127)
• 8220T 190hp 100002 16"(406mm)
  33.5(127)
• 8320T 215hp 100003 16"(406mm)
  33.5(127)
• 8420T 235hp 100004 16"(406mm)
  33.5(127)
• 8520T 255hp 100005 16"(406mm)
  33.5(127)
• SQL SELECT
• 2 FROM FOUR_WHEEL_DRIVE
• MODEL VIN TRANSMISSION
  PTO
• ------------------ ---------------
  --------------------------------
  -------------------
• 9120 280hp 300001 24/6 PowrSnyc (MST)
  1.75-in. (44.5)
• 9220 325hp 300002 24/6 PowerSnyc (MST)
  1.75-in. (44.5)

54
Using Select

• SQL SELECT
• 2 FROM ROW_CROP
• MODEL VIN TWO_WD_DIMENSION
  MFWD_DIMENSION
• ------------ ---------- --------------------
  --------------------
• 7210 95hp 400001 116.9 (2269)
  108.13 (2747)
• 4710 105hp 400002 116.9 (2969)
  108.1 (2747)
• 7610 120hp 400003 114.4 (2906)
  110.2 (2800)
• 7710 135hp 400004 114.4 (2906)
  110.2 (2800)
• SQL SELECT
• 2 FROM UTILITY
• MODEL VIN OPERATOR_STATION
  FINAL_DRIVE
• ----------------- -------------
  ---------------------------------
  -------------------------
• 5220 53hp 500001 Straddle
  Inboard Planetary
• 5520 89hp 500002 Flat
  Inboard Planetary
• 5420 81hp 500003 Flat
  Inboard Planetary

55
Summary

• We researched only a small portion of the
  business and found that is truly a giant
  corporation.
• Some of the local dealers are Kirk Equipment
  Paragould, Missco Monette, and Bazell Equipment
  Company Kennett, MO.

56

• Jeremys Question
• Question
• What are the primary keys in the OOD diagram for
  the John Deere Database?

57

• Evelyns Question
• Question
• What type of database does John Deere have?

58

• Jonathans Question
• Question
• What does John Deeres ERP consist of?

59
Chris Question

• Question
• How would you create this table from access into
  Oracle SQL.

60
Chris Answer
61

• Rachels Question
• Question
• One of the transitive dependencies in the John
  Deere DB that existed in 2nd NF was the
  Finance_amount. How did we remove this
  transitive dependency to prevent possible update
  anomalies that may arise?

62
Rachels Question
As shown in 3rd NF, we removed the
Finance_amount attribute from the Finance Method
Table and associated it with Customer Table.
Finance Method
Customer
63

• Matts Question
• Question
• How can graphing an ER diagram enhance the
  business reengineering process for John Deere?