ch2

Chapter Overview

• Introduction to the concepts behind relational model database systems in the 6th Edition of Database System Concepts by Silberschatz, Korth, and Sudarshan.

Key Components of Database Systems

• Data Models: Relational databases; schema and instance.

• Database Design: Establishing a framework for databases.

• Storage Manager: Handles data storage and retrieval.

• Query Processing: How queries are interpreted and executed.

• Transaction Manager: Manages database operations that require multiple steps.

• Merits and Drawbacks of Database: Queries on this concept are introduced but not extensively discussed in Chapter 2.

Database Architecture

Review of Chapter 1

• Database System Architecture: Involves multiple layers:

  • View Level: Individual user views (view 1, view 2, view n).

  • Logical Level: Overall structure of the database.

  • Physical Level: How data is physically stored.

Structure of Relational Databases

• Database Schema: Outline of tables, attributes, and relationship.

• Keys: Identify records uniquely in a database.

  • Primary Keys: A field or combination of fields that uniquely identify a table record.

  • Foreign Keys: A field that links to the primary key of another table, indicating relationships.

• Schema Diagrams: Visual representations of database structure.

• Relational Query Languages: How queries are written to manipulate data.

• Relational Algebra: Mathematical structure for querying databases.

Data Models Overview

• Types of Data Models:

  • Relational Model: Focus of this chapter, represents data in tables.

  • Entity-Relationship Model: Used mainly for database design.

  • Object-Based Models: Include Object-oriented and Object-relational.

  • Semi-Structured Models: Includes XML.

  • Older Models: Network and hierarchical models.

Relational Data Model Details

• Attributes and Tuples:

  • Attributes (columns) and tuples (rows) are the primary structures in relational databases.

  • Each table is made up of tuples that express relationships between different pieces of data.

Domain of Attributes

• Domain: Set of allowable values for an attribute.

• Atomic Values: Attribute values should be indivisible.

• Null Values: Represents missing or unknown data, complicating operation definitions.

Schema and Instances

• Relation Schema: Structure of a relation as defined by its attributes.

• Current Values (Instance): A relation's present state represented in a table.

Distinguishing Tuples

• Superkey: A set of attributes that can uniquely identify tuples.

• Candidate Key: A minimal superkey, necessary for primary key selection.

• Foreign Key Constraints: Ensures referential integrity between tables.

Schema Diagrams

• Schema Diagram for University Database: Represents relationships and structures including student ID, departments, courses, etc.

Query Languages

Types of Query Languages

• Procedural vs Non-Procedural:

  • Procedural languages specify how to obtain a result; non-procedural focuses on what data to retrieve.

  • Pure Languages: Include relational algebra and relational calculus, all three are equivalent in computing power.

  • Focus of this chapter is on relational algebra with its six basic operations.

Basic Operations in Relational Algebra

• Operations yielding a single relation:

  • Select (σ): Picks tuples based on predicates.

  • Project (π): Retrieves selected attributes from tuples.

  • Union (∪): Combines tuples from similar structured tables without duplicates.

  • Set Difference (−): Finds tuples in one relation but not the other.

  • Intersection (∩): Captures common tuples across two relations.

• Join Operation: Merges tuples from two relations based on matching attribute values.

Queries and Aggregates

• Aggregate Functions: SUM, AVG, MAX, MIN can be performed, grouped by attributes (e.g. average salary by department).

Conclusion of Chapter 2

• This chapter focuses on the relational model's key concepts, structures, operations, and query capabilities within database systems, setting the groundwork for the advanced database concepts explored in subsequent chapters.