ch2

Chapter 2: Introduction to Relational Model Database System Concepts

Overview of Database Systems

  • Contents Summary:

    • Data Models

    • Relational Databases: Schema & Instance

    • Database Design

    • Storage Manager

    • Query Processing

    • Transaction Manager

  • Questions to Consider:

    • What are the merits and drawbacks of databases?

Architecture of a Database System

  • Three Levels of Database Architecture:

    View Level

    • Presents different views of the data to users.

    • Multiple views can exist for the same data.

    Logical Level

    • Conceptualizes the structure of the entire database.

    • Describes entities, attributes, and relationships.

    Physical Level

    • Defines how data is physically stored in the database.

Structure of Relational Databases

  • Key Components:

    Database Schema

    • Represents the design or structure of the database.

    Keys

    • Primary Keys: Uniquely identify a record in a table.

    • Foreign Keys: Establish connections between tables.

    Schema Diagrams

    • Visual representations of the database structure.

    Relational Query Languages

    • Used to interact with relational databases.

    • Examples include SQL and Relational Algebra.

Data Models

  • Definition:

    • A data model is a conceptual framework that describes data structures, relationships, and constraints.

  • Types of Data Models:

    Relational Model (Focus of this Chapter)

    Entity-Relationship Model (Used primarily for database design)

    Object-based Data Models (Object-oriented and Object-relational)

    Semi-structured Data Model (e.g., XML)

    Older Models:

    • Network Model

    • Hierarchical Model

The Relational Data Model

  • Features:

    • Uses tables (relations) to represent data and relationships.

    • Most modern Database Management Systems (DBMS) follow the relational model.

  • Key Terminology:

    • Attributes (Columns)

    • Tuples (Rows)

    • Relation: Collection of tuples (table).

    • Domain: Set of allowed values for an attribute.

    • Atomic Values: Attribute values must be indivisible.

    • Null Values: Represent unknown or non-existent values, complicating operations.

Relation Schema and Instances

  • Relation Schema Example:

    • Instructor = (ID, Name, Dept_Name, Salary)

  • Relation Instance:

    • Current values of the schema represented in a table format.

  • For every schema defined, the instances will maintain adherence to its attributes and domain values.

Primary and Foreign Keys

  • Importance of Key Selection:

    Superkey

    • A set of attributes that can uniquely identify tuples.

    Candidate Keys

    • Minimal Superkeys.

    • Examples of candidate keys include single attributes that uniquely identify records.

    Primary Key

    • Chosen from candidate keys, ideally immutable.

    Foreign Key Constraints

    • Enforces a link between data in two tables, maintaining referential integrity.

Schema Diagrams

  • Visual representation of the database schema showing entities, attributes, primary keys, and foreign keys.

Query Languages

  • Query languages allow users to request information from databases; they include:

    Procedural Languages

    • Specify the procedure to obtain data.

    Declarative Languages

    • Specify what data to retrieve without detailing how to obtain it.

    Key Operations in Relational Algebra:

    • Selection (σ): Filters tuples based on a predicate.

    • Projection (π): Selects specific attributes.

    • Set Operations: Union (∪), Intersection (∩), etc.

Relational Algebra Operations

  • Selection Example: σ dept_name= “Physics” (instructor)

  • Projection Example: π ID, Salary (instructor)

  • Union Operation: Combines two relations without duplicates.

  • Natural Join: Combines tuples based on matching attribute values.

Aggregate Functions

  • Common aggregate functions include:

    • SUM, AVG, MAX, MIN

  • Group By Clause: dep_name G average(salary)(instructor)

Summary of Symbols for Query Operations

  • Selection: σ

  • Projection: Π

  • Cartesian Product: x

  • Union: ∪

  • Natural Join: ⋈

  • Set Difference: -

End of Chapter 2

  • Conclusion of Database System Concepts as detailed by Silberschatz, Korth, and Sudarshan.