Database Development Process

1
Database Development Process

• MIS 130
• Fall 2001

2
Enterprise Data Model

• Sets the range and general contents of
  organizational databases.
• Results in a total picture or explanation of
  organizational data, not in the design for a
  particular database.
• Entity-relationship diagram.
• Descriptions of entity types.
• Relationships between entities.
• Business rules.

3
Figure 2-1 Segment from enterprise data model
(Pine Valley Furniture Company)
4
Information Systems Architecture

• Data (Enterprise Data Model)
• Processes
• Data Network
• People
• Events and Points in Time (when processes are
  performed)
• Reasons (for events and rules, e.g. decision
  tables)

5
Information Engineering

• A data-oriented methodology to create and
  maintain information systems.
• Top-down planning approach.
• Four steps
• Planning
• (Results in an Information Systems Architecture)
• Analysis
• Design
• Implementation

6
Information Systems Planning

• Align IT with the business strategies.
• Three steps
• Identify strategic planning factors
• Organization goals - i.e., maintain 10 per year
  growth rate
• Critical success factors - i.e., high quality
  products, on-time delivery
• Problem areas - i.e., inaccurate sales forecasts,
  stock-outs, increased competition

7
Information Systems Planning

• Identify corporate planning objects
• Organizational units - various departments of
  organization
• Organizational locations - places where business
  occurs
• Business functions - related group of business
  processes that support some aspect of the mission
  of the enterprise
• Entity types - major categories of data about the
  people, places, things managed by the
  organization
• Information (application) systems - application
  software supporting procedures for handling
  sets of data
• Develop enterprise model
• Functional decomposition of business functions
• Enterprise data model (see below)
• Planning matrices (see below)

8
Example of process decomposition of an order
fulfillment function
9
Planning Matrices

• Function to data entity
• Location to function
• Unit to function
• IS application to data entity
• Supporting function to data entity
• which data are captured, used, updated, deleted
  within each function
• IS application to business objective

10
Example business function-to-data entity matrix
Customer Product Raw Material Order Work
Center Work Order Invoice Equipment Employee
Data Entity Types
Business Function
Business Planning X X X X Product
Development X X X
X Materials Management X X X X X
X Order Fulfillment X X X X X X X
X X Order Shipment X X X X
X X Sales Summarization X X
X X X Production Operations
X X X X X X X Finance and
Accounting X X X X X X X X
11
Systems Development Life Cycle (SDLC)
Project Identification and Selection
Project Initiation and Planning
Analysis
Logical Design
Physical Design
Implementation
Maintenance
12
Steps in the Database Development Process

• Enterprise Modeling
• Conceptual Data Modeling
• Cuts across Project Initiation and Planning
  Analysis phases of SDLC
• Logical Database Design
• Physical Database Design and Creation
• Database Implementation
• Database Maintenance

13
Database Development Process (1 0f 5)

• Enterprise modeling
• analyze current data processing
• analyze general business functions their
  database needs
• Justify need for new data databases in support
  of business

14
Database Development Process (2 0f 5)

• Conceptual data modeling
• identify scope of database requirements for
  proposed information system
• analyze overall data requirements for business
  functions supported by database
• develop preliminary conceptual data model
• compare preliminary conceptual data model with
  enterprise data model
• develop detailed conceptual data model
• populate repository with conceptual database
  specifications

15
Database Development Process (3 0f 5)

• Logical Database Design
• analyze in detail the transactions, form,
  displays, inquiries (database view) required by
  business functions
• integrate database views into conceptual data
  model
• identify data integrity and security
  requirements, populate repository

16
Database Development Process (4 0f 5)

• Physical database design
• define database to DBMS
• decide on physical organization of data
• design data processing programs
• Database implementation
• code test database processing programs
• complete database documentation training
  materials
• install database convert data from prior systems

17
Database Development Process (5 0f 5)

• Database maintenance
• analyze database database applications to
  ensure that evolving information requirements are
  met
• tune database for improved performance
• fix errors in database and database applications
  recover database when it is contaminated

18
Database Development Activities During the
Systems Development Life Cycle (SDLC)
Enterprise modeling
Project Identification and Selection
Conceptual data modeling
Project Initiation and Planning
Analysis
Logical database design
Logical Design
Physical Design
Physical database design creation
Implementation
Database implementation
Maintenance
Database maintenance
19
Database Development Process

• Sources
• Information systems planning
• User application requests - bottom-up
• IS Development Approaches
• Systems Development Life Cycle (SDLC)
• Rapid Application Development (RAD)
• Prototyping
• Others

20
Database and IS Development

• SDLC
• Generally clear match between phases in database
  development and SDLC.
• RAD
• Cursory attempt at conceptual data modeling.
• Define database during development of initial
  prototype.
• Repeat implementation and maintenance activities
  with new prototype versions.

21
Figure 2-6 The prototyping methodology and
database development process
22
Database and IS Development

• CASE - software tools that provide automated
  support for some portion of the systems
  development process (i.e., Oracle Designer)
• Database drawing tools
• Code generation (SQL)
• Repository - knowledge base of information about
  the facts that an enterprise must be able to
  access the processes it must perform to be
  successful.

23
People in Database Development

• Systems analysts
• Database analysts
• Users
• Programmers
• Database and data administrators
• Systems programmers, network administrators,
  testers, technical writers

24
Example Developing a database
applicationFigure 2-11 Preliminary data model
for product line marketing support system
25
Figure 2-12 Definition of PRODUCT LINE table
26
Figure 2-13 Definition of PRODUCT table
27
Figure 2-14 Definition of ORDER table
28
Figure 2-15 Definition of ORDERED PRODUCT table
29
Figure 2-16 Database definition for Home Office
product line marketing support system
30
Figure 2-17 Home Office sales-to-goal comparison
query
31
Figure 2-18 Home Office product line sales
comparison