Module 9 Database Design

Successful database design must reflect the information system of which it is a part.
Information systems are developed within a framework known as the Systems Development Life Cycle (SDLC).
Successful databases within the information system undergo frequent evaluation and revision in the Database Life Cycle (DBLC).
Key learning points:
- Conduct evaluation and revision within SDLC and DBLC frameworks.
- Understand design strategies: top-down vs. bottom-up and centralized vs. decentralized design.

Elements:
- Data collection, storage, and retrieval.
- Composed of: People, hardware, software, databases, application programs, procedures.
Systems Analysis: Establishes need and extent of an information system.
Systems Development: The process of creating the information system.

Critical factors influencing an information system’s performance:
- Database design and implementation.
- Application design and implementation.
- Administrative procedures.
- Overall database development relies on the effective design and implementation process.

The SDLC traces the history of an information system, providing a framework within which database design and application development are evaluated.
It is an iterative rather than a strictly sequential process:
- Phases include:
  1. Initial Assessment
  2. Planning
  3. Feasibility study
  4. User requirements
  5. Analysis of existing systems
  6. Logical system design
  7. Detailed system specification
  8. Coding, testing, and debugging
  9. Implementation
  10. Evaluation
  11. Maintenance

CASE tools help to produce systems that are:
- Time and cost-effective.
- Structured, documented, and standardized applications.

Analyzes the company situation:
- Initial study to define problems, constraints, objectives, and boundaries of the database.
Steps include:
1. Create conceptual design.
2. DBMS software selection.
3. Logical, physical design.
4. Implementation and loading of the database.
5. Testing and evaluation,
6. Operation and maintenance.

Aims to analyze the company, define problems, constraints, scope, and objectives.
Ensures the system supports the company’s operations and objectives by examining completion procedures.

Business Manager’s View:
- Focused on identifying problems, solutions, and required information.
Database Designer’s View:
- Concentrates on data structure, access, and transformation of data into information.

Data Analysis and Requirements:
- Involves entity relationship modeling and normalization to ensure data integrity and efficiency.
- The process includes:
  - Verifying data model.
  - Designing data storage organization.
  - Ensuring integrity and security.
  - Establishing performance measures.

Testing Factors:
- Consider physical security, password security, access rights, audit trails, data encryption.
Levels of Backup:
- Full backup, differential backup, transaction log backup to secure data.
- All backups must have high security measures to prevent data loss.

The DBLC and SDLC run parallel activities with shared components:
- Initial analysis, conceptual design, logical design, and implementation across both cycles.

Conceptual design should be independent of software and physical details.
Ensures all necessary data items are present, adhering to the minimum data rule.

Involves detailed analysis and modeling of business rules, entities, and relationships to construct the initial Entity-Relationship (ER) diagram.
Normalization processes are employed to refine and validate the model against user requirements.

Involves iterations of identifying entities, modules, and validating against system requirements and making necessary adjustments as required.

Cohesivity: Measures the strength of relationships among module entities.
Coupling: Refers to how independent the modules are from each other; minimizing coupling reduces interdependencies.