2.0 DATABASE DESIGN

2.0 DATABASE DESIGN

1. 2.1 & 2.2 ANSI-SPARC Three Level Architecture (Three Levels Data Abstraction)

   • Key Objectives
     • Data Abstraction
       • Separates user applications from physical database details
     • Data Independence
       • Allows changes at one level without affecting others
   • Keyword characteristics degree of abstraction
   • A framework for data modeling based on degrees of data abstraction
   • Benefits
     • Data Independence
     • Improved Security
     • Flexibility
     • Simplified Maintenance
   • What are Those Three Levels?
     • External Model (USER’S VIEW)
       • shows the end users' view of the data
         • End users → use apps (Sales, Finance, HR) to get info
           • External Schema → user’s way of seeing & accessing relevant data
       • Key Objectives
         • external model
           • application programmer’s view of data environment
         • external schema
           • representation of external view (end user’s perspective)
       • Independent of
         • Hardware
         • Software
       • Advantages
         • User Needs
           • Clarifies what data each user needs
         • Validation
           • Helps designers validate model & processes
         • Security
           • Improves security → users see only relevant data
         • App
           • Easier app development tailored to team needs
     • Conceptual Model (DESIGNER’S VIEW)
       • Shows complete view of entire database (organization-wide
         • Combines all external views → entities, relationships, rules, processes
           • Common model → Entity-Relationship (ER) Model
             • Visual tool → ERD (Entity-Relationship Diagram)
       • Key Objectives
         • Conceptual Model
           • result of conceptual design process
         • Conceptual Schema
           • graphical representation of conceptual model
       • Independent of
         • Software
         • Hardware
       • Advantages
         • Clear Overview
         • Independent from Software and Hardware
         • Flexible Design (Logical Design)
     • Internal Model (DATABASE ADMINISTRATOR)
       • Connects Conceptual Model → DBMS (MySQL, Oracle, etc.)
         • Must follow rules of DBMS + Conceptual Model
           • Logical Independence → change DBMS/storage without altering conceptual model
             • Internal Schema → shows how data is stored (using DBMS tools/features)
       • Independent of
         • Hardware
       • Key Objectives
         • Internal Model
           • adapts conceptual model to specific DBMS
         • Internal Schema
           • representation using DBMS constructs
         • Logical Independence
           • hardware-independent (not affected by storage/OS changes)
       • Advantages
         • Data Integrity & Consistency
         • Security & Access Control
         • Performance Optimization
         • Physical Data Independence
         • Efficient Storage Management

2. 2.3 & 2.4 Database Life Cycle (DBLC)

   • Phase
     • Phase 1 : Database initial study
       • Analyze the company situation
       • Define problems and constraints
       • Define objectives
       • Define scope and boundaries
     • Phase 2 : Database design
       • Create conceptual design
       • DBMS Software selection
       • Create logical design
       • Create Physical design
     • Phase 3 : Implementation and Loading
       • Install the DBMS
       • Create the Database(s)
       • Load or Convert the Data
     • Phase 5 : Operation
       • Produce the required information flow
     • Phase 4 : Testing and evaluation
       • Test the database
       • Fine-tuning the database
       • Evaluate the Database and Its Application Programs
     • Phase 6 : Maintenance and Evolution
       • Introduce Changes
       • Make enhancements

3. 2.5 Database Language and Data Model

   • Database Language
     • What is database language?
       • Enables users to:
         • Create & manage database structures
         • Manipulate data → add, delete, modify
         • Execute queries → transform raw data → useful information
   • Data Modeling and Data Model
     • Data Model
       • defines how data is structured, stored, and manipulated within a database
       • Importances
         • Enhances Organizational Understanding
         • Provides a Common Vision
         • Clarifies Data Usage Across Roles
         • Guides Application Development
         • Facilitates Communication
         • Ensures a Holistic View of Data
         • Supports Better Decision-Making
       • Types
       • Differentiate Data Model
       • Basic Building Blocks
         • entities
           • a person, place, thing, concept, or event about which data will be collected and stored
         • attributes
           • An attribute is a characteristic of an entity or object
         • relationships
           • is an association between entities
             • One-to-many (1 : M)
             • Many-to-many (M:N)
             • One-to-one (1 : 1)
         • constraints
           • is a restriction placed on the data
           • to ensure data integrity
           • help to enforce business rules in the database design
           • normally expressed in the form of rules
     • Data Modelling
       • Process of creating a specific data model
         • Based on a problem domain → defined real-world area with clear scope & boundaries

Images and files in the map