Chapter 9 10 Database Planning Design and Administration

Chapter 9 & 10 Database Planning, Design and Administration

Agenda • Database Application Lifecycle • DBMS Selection • Database Administration

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

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

System Definition • Scope & boundaries • Applications

Requirement Collection • Fact-finding techniques – Interview – Questionnaires – Observation – Documentation – Experience

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

Database Design • Approach – Top-down – Bottom-up or inside-out – Mixed

Optimal Logical Data Model • • Structure validity Simplicity Expressability Nonredundancy Shareability Extensibility Integrity Diagrammatic representation

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

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

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

Prototyping • Type – Requirement prototyping – Evolutionary ptototyping • Pros – – Define user’s requirements Quick Feasibility test Low cost and risk, new technology • Cons – Costs

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

Data Conversion and Loading • Actual data conversion • Bridge

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

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

Data Administration Role • • Planning Developing and maintaining standard Developing policy & procedure Design conceptual and logical database

Database Administration Role • • Physical database design Security & integrity control Performance monitoring Tuning database

Points To Remember • Database Application Lifecycle • DBMS Selection • Database Administration

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