Chapter 2 Database Development Process

1
Chapter 2Database Development Process
2
Chapter Goals

• Information Systems Architecture
• Life cycle of systems development project
• Alternative approaches to database development
• Roles of database personnel
• Differences between conceptual, external,
  physical schema and their interactions
• Rationale for three-schema architecture
• Scope of database design
• Key Terms

3
Information Systems Architecture

• A conceptual blueprint of the desired future
  information system (IS)
• Data
• Processes--that manipulate data
• Network--that transports data
• People-who perform processes and are source of
  data
• Events--when processes are performed
• Reasons--governing rules and their execution

4
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
  and Enterprise Data Model
• Analysis
• Design
• Implementation

5
Information Systems Planning

• Align IT with the business strategies.
• Three steps
• Identify strategic planning factors
• Organization goals
• Critical success factors
• Problem/opportunity areas
• Identify corporate planning objects
• Organizational units
• Organization locations
• Business functions
• Entity types
• Information (application) systems

6
Information Systems Planning

• Develop enterprise model
• Functional decomposition of business functions
• Enterprise data model (see below)
• Planning matrices (see below)

7
Enterprise Data Model

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

8
Figure 2-1 Segment from enterprise data model
(Pine Valley Furniture Company)
9
Example of process decomposition of an order
fulfillment function
10
Planning Matrices

• 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

11
Uses of Planning Matrices

• Identify orphans
• Spot missing entities
• Prioritize development

12
Example business function-to-data entry 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
13
Systems Development Life Cycle the PADIM3 Acronym

• Planning
• Project Identification/Selection
• Project Initiation/Planning
• Analysis
• Design
• Logical Design
• Physical Design
• Implementation
• Management, Maintenance, Modification

14
Planning

• Project Identification/Selection
• Purpose Preliminary understanding
• Deliverable Request to develop system
• Project Initiation/Planning
• Purpose Assess business situation and IS role
• Deliverable Request to study needed changes or
  rationale for new system

15
Analysis

• Purpose Thorough analysis of business situation
  to determine requirements
• Deliverable Functional specification of feasible
  system that meets requirements

16
Design

• Logical Design
• Purpose to elicit and structure all information
  requirements
• Deliverable Detailed functional specifications
  of all data, forms, reports, displays, and
  processing rules

17
Design

• Physical Design
• Purpose To develop all technology and
  organizational specifications
• Deliverable Program and database structures,
  technology purchases, physical site plans, and
  organizational redesigns

18
Implementation

• Purpose To write programs, build data files,
  test and install new system, train users, and
  finalize documentation
• Deliverable Programs that work accurately and to
  specifications, documentation, and training
  materials

19
Management, Maintenance, Modification

• Purpose To monitor the operation and usefulness
  of the system, repair, and enhance system
• Deliverable Periodic audits of the system

20
Database development activities during the
systems development life cycle (SDLC)
Project Identification and Selection
Project Initiation and Planning
Analysis
Logical Design
Physical Design
Implementation
Maintenance
21
Database Development Process

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

22
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.

23
Prototyping

• Alternative IS development approach
• Iterative/Interactive process
• Continually revised system
• Close cooperation between analysts and users

24
Figure 2-6 The prototyping methodology and
database development process
25
Database and IS Development

• CASE
• Database drawing tools
• Code generation (SQL)
• Repository
• Upper CASE Tools--Project Identification through
  Physical Design
• Lower CASE--Supports Implementation and
  Maintenance

26
People in Database Development

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

27
Figure 2-7a Gantt Chart
28
Figure 2-7b PERT chart
29
Successful Development--Review Points

• Validate project progress
• Take large picture view to see that parts are
  coming together
• Gain renewed commitment
• Incremental commitment
• Reviews after each phase
• Rejustification in each review

30
Three Schema Architecture for Database Development

• Conceptual Schema
• Analysis project phase
• External Schema--User views
• Analysis and Logical Design phases
• (subset of conceptual schema)
• Internal Schema
• Physical Design phase

31
Figure 2-8 Three-schema database architecture
32
Figure 2-9 Process of developing three-schema
architecture for a database project
33
Three-Tiered Database Location Architecture

• Client or presentation tier
• Department or workgroup
• Process services tier for data access and
  computations
• Minicomputer
• Enterprise
• Data services for complex computations diverse
  data access
• Mainframe

34
Figure 2-10 Three-tiered client/server database
architecture
35
Client/server Architectures

• Server houses DBMS engine
• Store Data
• Process commands
• Data integrity, security, backup
• Client workstations interact with server
• Application programs at client concentrate on
  user interface

36
Client/server Advantages

• Simultaneous processing on multiple servers at
  multiple locations
• Integration of micro, mini, and mainframe
  computing advantages
• Mix of client technologies, change in
  technologies possible
• Processing close to source for increased response
  time
• Encourages open system standards
• Natural environment for database development

37
Figure 2-11 Preliminary data model for product
line marketing support system
38
Figure 2-12 Definition of PRODUCT LINE table
39
Figure 2-13 Definition of PRODUCT table
40
Figure 2-14 Definition of ORDER table
41
Figure 2-15 Definition of ORDERED PRODUCT table
42
Figure 2-16 Database definition for Home Office
product line marketing support system
43
Figure 2-17 Home Office sales-to-goal comparison
query
44
Figure 2-18 Home Office product line sales
comparison