Database Design

Database Design¶

Objectives as Outlined by the Textbook¶

• Describe the role of database design as the foundation of a successful information system
• Describe the five phases in the Systems Development Life Cycle (SDLC)
• Design databases using the six phases in the Database Life Cycle (DBLC) framework
• Conduct evaluation and revision within the SDLC and DBLC frameworks
• Distinguish between top-down and bottom-up approaches in database design
• Distinguish between centralized and decentralized conceptual database design

The Information System (IS)¶

• An IS provides for data collection, storage, and retrieval
• Components of an IS are:
  • People
  • Procedures/Processes
  • Applications/Software
  • Databases/NoSQL Systems
  • Hardware
• Systems Analysis: Establishes IS need and to what extent.
• Systems Development: The process of creating and IS after Systems Analysis.
  • Performance Factors/Considerations during systems Development:
    • Database design and implementation
    • Application design and implementation
    • Administrative procedures and processes
• Database Development: The process of database design and its implementation (i.e. subset of Systems Development).

Systems Development Life Cycle (SDLC)¶

Description of Phases¶

• Planning: yields a general overview of the company and its objectives
• Analysis: problems defined during planning phase are examined in greater detail
• Detailed systems design: designer completes the design of the system’s processes
• Implementation: hardware, DBMS software, and application programs are installed, and the database design is implemented
• Maintenance: corrective, adaptive, and perfective

Database Development Life Cycle (DBLC)¶

Description of Phases¶

• Database initial study: define problems, constraints, objectives, scope, and boundaries
• Database design: making sure that the final product meets user and system requirements
• Implementation and loading: DBMS is installed, database is created, and data is loaded or converted
• Testing and evaluation: database is tested, fine-tuned, and evaluated
  • Full backup/dump: all database objects are backed up in their entirety
  • Differential backup: only modified/updated objects since last full backup are backed up
  • Transaction log backup: only the transaction log operations that are not reflected in a previous backup are backed up
• Operation: problems are identified and solutions implemented
• Maintenance and evolution: preventative, corrective, adaptive, etc.

Database Design Process¶

Database Failures to Plan for¶

• Software: Operating System, DBMS software, applications, viruses, or malware.
• Hardware: Memory errors, Disk failures, bad sectors, and disk space issues.
• Programming Exemptions: Application or User transactions rollbacks. Malicious or insecure code that’s been exploited.
• Transactions: Deadlocks and deadlock detection leading to failure.
• External Factors: Fire, earthquake, floods, or other natural disaster.

Conceptual Design¶

Steps in Conceptual Data Model¶

1. Data analysis and requirements engineering
   • Focused on: garnering information needs, end user data views, data sources and existing systems.
   • Directly catalogs current systems and assesses capabilities.
   • Collects desired outputs and needs.
   • Often interacts with systems group.
2. ER Modeling and normalization
   • Data dictionary is formed with (entities, attributes, relations, views, constraints, triggers, stored-procedures, etc.)
   • Normalization process takes place.
   • ER Modeling Conceptual Steps:
     flowchart TD A[Identify,\nanalyze,\nrefine\nthe business rules] --> B B[Identify entities,\nusing the results of Step 1] --> C C[Define the relationships among the entities,\nusing the results of\nSteps 1 and 2] --> D D[Define the attributes,\nprimary keys,\nand foreign keys\nfor each of the entities] --> E E[Normalize the entities] --> F F[Complete the initial ER diagram] --> G G[Validate the ER model against\nthe end users' information\nand processing requirements] --> H H[Modify the ER model,\nusing the results of Step 7]
   • Process in Reality:
3. Model validation and verification
   • Validate all reports and end user views are satisfied
   • Verify existing processes are still satisfied by model
   • Testing for things like…
     • Module: Component handling specific business function
     • Module Coupling: Low Coupling preferred because increases module independence.
     • Cohesivity: Strength of entity relationships in the model
4. Database design, distributed or otherwise
   • Considerations about location of DBMS
   • Database fragment: a sub-system of DB stored in different locations
   • Planning around integrity, security, and performance.

DBMS Software Selection¶

Purchasing Considerations¶

• Cost
• Tools and Features
• Support for Model and processes
• Portability and other system/software dependencies
• Hardware requirement

Logical Design¶

Design Steps¶

1. Map Conceptual Model to Logical Components
2. Validate Logical Model via Normalization
3. Validate Logical Model via Integrity Constraints
4. Validate Logical Model via User Requirements

Maping Conceptual Model to Logical Components (i.e. Rlational Model for us)¶

1. Map Strong Entities (Not dependent on any other entity)
2. Map Super/Sub-type relationships (Common attributes in supertype, unique attributes in subtypes).
3. Map Weak Entities (Not uniquely identified by attributes alone, FK is part of PK)
4. Map Binary Relationships 1:1, 1:M
5. Map higher-degree Relationships M:N

Physical Design¶

Database Design Strategies¶

Top Down¶

• Identify Data Sets (i.e. Entities and Entity Types)
• Define Data Elements per Set (i.e. Attributes for each Entity)

Bottom Up¶

• First identifies the element items (i.e. Attributes)
• Groups items into data-sets (i.e. Entities)