Database System Design Notes

Overview of Database System Design

  • System Architecture

    • Based on VMS (Virtual Memory System)

    • Independence between program, application, and system

    • Installation between business systems and physical components

  • Current Focus

    • No immediate need to develop a new DBMS (Database Management System)

    • Focus is on designing a database system using existing DBMS

  • Goals for the Course

    • Understand how to design a database system

    • Gain practical experience through a class project

Key Concepts in Database Design

Understanding Data Models

  • Definition of Data Model

    • A conceptual representation used to store, structure, and access data

    • Models represent abstracted information rather than real entities (e.g., students)

  • Purpose of Data Models

    • To abstract real-world information for storage and management in a system

Data Abstraction Process

  1. Real World Information

    • Information needs to be abstracted before storage

    • Conceptual model is used to create this abstraction

  2. Binary Storage

    • Computers store data in binary (0s and 1s)

    • The process involves multiple data models to transition from conceptual to physical storage

Levels of Data Models

  • High-Level Data Model

    • Focused on human understanding and management

    • Example: Conceptual Model

  • Low-Level Data Model

    • Focused on computer-readable formats

    • Responsible for how data is stored in binary

  • Logical Data Model

    • Bridges high-level and low-level models, translating the conceptual model into a practical format for implementation

Relational Data Model

  • Organizing Data

    • Utilizes tables to structure information

    • Each table has a defined structure (table names, columns, data types)

  • Constraints

    • Constraints enforce rules on the data (e.g., foreign key constraints)

    • Example: A grade column can only accept specific values (A, B, C, D, E, F)

Important Concepts in Relational Models

  • Database Schema

    • Structure of the database (table definitions, relationships)

    • Focus on how data is organized rather than specific data items

  • Database State

    • Represents the actual data stored at a specific point in time

    • Similar to instances in object-oriented programming (OOP)

SQL and Database Languages

  • DDL (Data Definition Language)

    • Used to define the schema and structure of the database

    • Commands include CREATE, ALTER, and DROP

  • DML (Data Manipulation Language)

    • Used to manipulate data within the database

    • Commands include INSERT, UPDATE, DELETE, and SELECT

Architecture for Database Deployment

  • Types of Architectures

    1. Centralized Architecture

    • Database is installed on a single computer with no network access

    • Limited to physical access for users

    1. Two-Tier Client-Server Architecture

    • Involves a server providing database services to multiple client machines over a network

    • Clients access the database using client applications (e.g., MySQL Workbench)

    1. Three-Tier Client-Server Architecture (Browser-Server Model)

    • Clients access the database through a web interface without needing to install client applications

    • Web servers manage database connections and user requests

Example Use Cases of Architectures

  • Travel Bookings

    • Three-tier architecture preferred for ease of access

    • Centralized would require customers to book flights on-site physically, which is impractical

    • Two-tier requires additional client installation, which may not always be feasible for all users

User Interaction with Database Systems

  • Types of Users

    • Actors on the Scene: Database administrators, designers, end-users

    • Workers Behind the Scenes: System maintainers, support staff, operators

Designing a Database System

  • Design Process Stages

    1. Requirement Gathering

      • Communicate with end-users to gather specifications and needs

    2. Design

      • Create conceptual models based on requirements

      • Transition from conceptual to relational models

    3. Implementation

      • Develop the database based on the design using SQL and the chosen DBMS

      • Testing and evaluation before deployment

    4. Deployment and Maintenance

      • Finalize the deployed system and provide support as needed

Summary and Next Steps

  • Course Focus

    • Concentration on designing and implementing database systems

    • Practical application through class projects

    • Upcoming sessions will elaborate on conceptual and relational models, culminating in a testing phase

  • Textbook References

    • Suggested readings from chapters 1 and 2 for additional context

  • Next Class Reminder

    • Focus will be on defining the conceptual model

    • Prepare for a break and subsequent classes with a focus on practical concepts learned

  • Important Note

    • All class-related materials and concepts will tie back into what is discussed in this session. Expect assessments which may include elements from today's material.