Database Planning, Design and Administration

1
Chapter 9 10

• Database Planning, Design and Administration

2
Agenda

• Database Application Lifecycle
• DBMS Selection
• Database Administration

3
Database Application Lifecycles

• Database planning
• Systems definition
• Requirements collection and analysis
• Database design
• DBMS selection
• Application design
• Prototyping
• Implementation
• Data conversion and loading
• Testing
• Operational maintenance

4
Database Planning

• Business goals, plans, and needs
• Mission statements
• Mission objectives
• Current information systems evaluation
• Strengths
• Weaknesses
• IT opportunities for competitive advantages
• Corporate data model
• User needs
• Standards
• Legal requirement

5
System Definition

• Scope boundaries
• Applications

6
Requirement Collection

• Fact-finding techniques
• Interview
• Questionnaires
• Observation
• Documentation
• Experience

7
Requirement Analysis

• Requirements specification technique
• Structured Analysis and Design (SAD)
• Data Flow Diagram (DFD)
• Hierarchical Input Process Output (HIPO)
• Computer-Aided Software Engineering (CASE)
• Unified Model Language (UML)
• Approach
• Centralized
• View integration (local data model and global
  data model)
• Combination

8
Database Design

• Approach
• Top-down
• Bottom-up or inside-out
• Mixed

9
Optimal Logical Data Model

• Structure validity
• Simplicity
• Expressability
• Nonredundancy
• Shareability
• Extensibility
• Integrity
• Diagrammatic representation

10
Phase of Database Design

• Conceptual database design
• Independent of physical consideration
• Logical database design
• Normalization
• Specific data model
• Physical database design
• Relational tables and constraints
• Storage structures and access methods
• Security protection

11
DBMS Selection

• Steps
• Define terms of reference of study
• Shortlist two or three products
• Evaluate products (feature, comment, rating
  weight, and score)
• Selection Criteria
• Data definition
• Physical definition
• Accessibility
• Transaction handing
• Utilities
• Development
• Other features

12
Application Design

• Considerations
• Data
• Transaction type (retrieval, update, or mixture)
• User interface (forms reports)
• Logical
• Simple
• Error handling
• Help
• Meaningful
• Consistency
• Status

13
Prototyping

• Type
• Requirement prototyping
• Evolutionary ptototyping
• Pros
• Define users requirements
• Quick
• Feasibility test
• Low cost and risk, new technology
• Cons
• Costs

14
Implementation

• Data definition language (DDL)
• Data manipulation language (DML) or embedded DML
• Security control

15
Data Conversion and Loading

• Actual data conversion
• Bridge

16
Testing

• Learnability
• Performance
• Robustness (tolerant of user error)
• Recoverability
• Adapatability

17
CASE Tools

• Computer-Aided Software Engineering (CASE)
• Types
• Upper-Case planning to design
• Lower-Case implementing, test, performance
• Integrated-Case
• Benefits
• Productivity (effectiveness and efficiency)
• Standard
• Integration (repository)
• Support structured methods
• Consistency
• Automation

18
Data Administration Role

• Planning
• Developing and maintaining standard
• Developing policy procedure
• Design conceptual and logical database

19
Database Administration Role

• Physical database design
• Security integrity control
• Performance monitoring
• Tuning database

20
Points To Remember

• Database Application Lifecycle
• DBMS Selection
• Database Administration

21
Assignment

• Review chapters 1-10, appendix c
• Read chapters 11-12
• Oracle assignment due date
• 2nd exam chapters 5, 6, 9,10, 11, and 12