Understanding Database Systems: Three-Schema Architecture, Data Independence, and Data Models

Posted on Jun 10, 2024 in Computers

Understanding Database Systems

Three-Schema Architecture

The Three-Schema Architecture is a framework for database systems that separates the database into three levels of abstraction to achieve data independence. This architecture helps in isolating the user’s application from the physical database. The three levels are:

External Level

This level is closest to the end users. It contains the data that is of interest to the user, and the user interacts with this level to access the database. There can be multiple external schemas for different users.

Conceptual Level

This level provides a community user view of the database. It describes what data is stored in the database and the relationships among those data. It hides the details of the physical storage structures and focuses on the logical structure of the data.

Internal Level

This is the lowest level of abstraction, closest to physical storage. It describes how the data is physically stored and accessed. This level includes data structures, file organizations, indexing, and other performance-related details.

Data Independence

Data independence refers to the capacity to change the schema at one level of a database system without having to alter the schema at the next higher level. This is achieved through the separation provided by the Three-Schema Architecture. There are two types of data independence:

  • Logical Data Independence: The ability to change the conceptual schema without having to change external schemas or application programs.
  • Physical Data Independence: The ability to change the internal schema without having to change the conceptual schema. This means changes to how data is stored and accessed (e.g., changing indexes, storage devices) can be made without affecting the logical structure of the database.

Advantages of DBMS over FPS

  • Data Redundancy and Consistency:
    • DBMS: Centralized storage reduces redundancy and ensures consistency.
    • FPS: Duplication leads to inconsistencies.
  • Data Integrity:
    • DBMS: Enforces constraints for accuracy.
    • FPS: Risks inaccuracies due to lack of integrity mechanisms.
  • Data Security:
    • DBMS: Robust security features like authentication and access controls.
    • FPS: Generally lacks advanced security.
  • Data Independence:
    • DBMS: Schema changes don’t affect applications.
    • FPS: Requires application changes with data structure changes.
  • Efficient Data Access:
    • DBMS: Efficient queries with languages like SQL.
    • FPS: Complex coding needed for data access.
  • Backup and Recovery:
    • DBMS: Automated features ensure data protection and recovery.
    • FPS: Lacks comprehensive backup mechanisms, risking data loss.

Types of Attributes

  • Simple (Atomic) Attribute
  • Composite Attribute
  • Single-Valued Attribute
  • Multi-Valued Attribute
  • Derived Attribute
  • Stored Attribute

Characteristics of DBMS

  • Self-describing nature
  • Insulation between program and data abstraction
  • Support of multiple views of data
  • Sharing of data and multi-user transaction processing

Database Concepts

  • Weak Entity: Entity reliant on another for identification, lacking a unique identifier.
  • Snapshot: Static data representation at a specific time, capturing database state.
  • Cardinality Ratio: Describes the number of associations between entities.
  • Degree of Relationship: Quantity of entities involved in a relationship.
  • Data Model: Conceptual structure depicting data organization and manipulation.
  • Schema: Formal blueprint detailing database organization and structure.
  • Instance: Singular occurrence or realization of an entity in a database.
  • Canned Transaction: Predefined and standardized database operation or query.
  • Program Data Independence: Applications accessing and modifying data without schema impact.
  • Total Participation: Constraint ensuring every entity participates in a relationship.

Different Categories of Data Models

  • Conceptual Data Models: High-level representation focusing on business concepts and relationships.
  • Logical Data Models: Detailed description of data elements, relationships, and constraints.
  • Physical Data Models: Defines how data is stored, organized, and accessed within a specific DBMS.
  • Hierarchical Data Models: Organizes data in a tree-like structure with parent-child relationships.
  • Network Data Models: Allows each child to have multiple parent records, creating a network structure.
  • Object-Oriented Data Models: Represents data as objects with attributes and methods, supporting OOP principles.
  • Object-Relational Data Models: Integrates relational and object-oriented features for flexible data management.

Database Constraints

  • Domain Constraints:
    • Definition: Specify allowable values for attributes.
    • Example: Age must be between 18 and 100.
  • Key Constraints:
    • Definition: Ensure each row is uniquely identifiable.
    • Example: StudentID is the primary key in a table of students.
  • Constraints on NULLs:
    • Definition: Rules regarding NULL values.
    • Example: City cannot be NULL in an employee address table.
  • Entity Integrity Constraints:
    • Definition: Ensure each tuple has a unique primary key and primary key attributes are not NULL.
    • Example: OrderID is the primary key in an orders table.
  • Referential Integrity Constraints:
    • Definition: Maintain consistency of relationships between tables.
    • Example: StudentID in a courses table references StudentID in a students table.

These constraints enforce data integrity and ensure the reliability of the database schema.