Transcript Database Design Application

Database Design Application
For: 384K.11, Dr. Wyllys
2002 March 27
Don Drumtra
© 2001, Donald W. Drumtra, all rights reserved
Overview
 The reality being modeled
 History
 Failure
 Relational design
 Implications
The Reality Being Modeled
Circuits
CCSD-circuit number
APPV 3D01
Trunks
Trunk number
Circuits
Circuits
44JT01
The Reality Being Modeled
Link
Circuits
Circuits
Link Number
Trunks
Trunks
L2310
Circuits
Circuits
Trunk on Circuit
Trunk Number
CCSD-circuit number
Circuits
Circuits
ASNT 3NP4
44TX01
The Reality Being Modeled
Trunks
Circuits
Circuits
Trunks
Circuits
Trunks
Circuits
Trunks
Circuits
History
 Paper
 Colored punch cards
 Accounting machines
 Computer file card images
 Flat file database
 Networked database (CODASYL)
– Different views
 Relational databases
History
 One generation carries to the next
 Redesign is very hard to justify
 People are hard to change
 Two experts
– Bill Parker-R2D2
User, 30 years
– Dan Breedon-River rat
DBM, 20 years
Failure
 DCOSS Database
– Designed for operations
– Operations works with contractor
(Not R2D2 or River rat)
 Contractor effort $50 MIL
Database design $20 MIL
Equipment $30 MIL
Failure
 CCSD was used as key
– Circuit number
– Unique
– Basic
– 8 characters
Failure
 Hardware partially fielded
– Awesome
 Software had hooks to database
 Database design is in critical path
Failure
 Database load fails
– CCSD is not unique
– CCSD is not basic
– CCSD is not circuit number
– Circuit number is 10 characters
Failure
 Impact on program
– $15 MIL to redesign
– One year slip in program
 Program cancelled
– Existing money expires
– No additional money
– Program manager retires
Relational Design
 New contract
– Research and development
– Model system with relational tables
 Bad beginning
– “Transmission is basic – everything
depends on transmission”
– Design based on circuits
– Keys based on CCSD, Trunks, Links
Relational Design
 Reorientation
 New model
– Location is basic
– Location may be physical or virtual
– Path connects locations
– Circuits are on paths
– Keys are random numbers
Relational Design
 700 tables
 Most keys random
 Many concatenated keys
 1800 data elements
 Two volumes
 27,000 hours labor
 $1.5 MIL cost
Relational Design
 Management could not believe
complexity
 Very high cost to implement
 R2D2 retired after 40 years
 River rat retired after 30 years
 Design put on the shelf
Implications
 Include the right people
 Beware of history
 Beware of user views
 Think through physical reality
 Use random number keys
 Use concatenated keys