Notes on the Relational Model

Introduction to the Relational Model

  • The relational model is the primary data model for commercial data-processing applications.
  • Achieved prominent status due to its simplicity, making programming easier compared to earlier models such as the network model or hierarchical model.
  • Retained its position by integrating new features and capabilities over its 50 years of existence:
    • Object-relational features such as complex data types.
    • Stored procedures support.
    • Support for XML data.
    • Tools for handling semi-structured data.
  • The relational model's independence from specific low-level data structures has enabled its durability, even with modern data storage approaches like column-stores designed for large-scale data mining.

Fundamentals of the Relational Model

  • This chapter covers the foundational aspects of the relational model:
    • Extensive theory exists for relational databases.
    • Chapters 6 and 7 will explore database theory relevant to the design of relational database schemas.
    • Chapters 15 and 16 will focus on theories concerning the efficient processing of queries.
    • Chapter 27 will examine formal relational languages beyond the introductory discussion in this chapter.

Structure of Relational Databases

  • A relational database is comprised of a collection of tables, each uniquely named.

Example Tables

Instructor Table (Figure 2.1)
  • Purpose: Stores information about instructors.
  • Structure:
    • Columns:
    • ID: Unique identifier for instructors.
    • name: Instructor's name.
    • dept_name: Department of the instructor.
    • salary: Instructor's salary.
  • Data (Example Rows):
    • | ID | name | dept_name | salary |
      |------|------------|------------|--------|
      | 10101| Srinivasan | Comp. Sci. | 65000 |
      | 12121| Wu | Finance | 90000 |
      | 15151| Mozart | Music | 40000 |
      | 22222| Einstein | Physics | 95000 |
      | 32343| El Said | History | 60000 |
      | 33456| Gold | Physics | 87000 |
      | 45565| Katz | Comp. Sci. | 75000 |
      | 58583| Califieri | History | 62000 |
      | 76543| Singh | Finance | 80000 |
      | 76766| Crick | Biology | 72000 |
      | 98345| Brandt | Comp. Sci. | 92000 |
Course Table (Figure 2.2)
  • Purpose: Stores information about courses.
  • Structure:
    • Columns:
    • course_id: Unique identifier for each course.
    • title: Title of the course.
    • dept_name: Department the course belongs to.
    • credits: Number of credits for the course.
  • Data (Example Rows):
    • | courseid | title | deptname | credits |
      |-----------|-----------------------------|-----------|--------|
      | BIO-101 | Intro. to Biology | Biology | 4 |
      | BIO-301 | Genetics | Biology | 4 |
      | BIO-399 | Computational Biology | Biology | 3 |
      | CS-101 | Intro. to Computer Science | Comp. Sci.| 4 |
      | CS-190 | Game Design | Comp. Sci.| 4 |
      | CS-315 | Robotics | Comp. Sci.| 3 |
      | CS-319 | Image Processing | Comp. Sci.| 3 |
      | CS-347 | Database System Concepts | Comp. Sci.| 3 |
      | EE-181 | Intro. to Digital Systems | Elec. Eng.| 3 |
      | FIN-201 | Investment Banking | Finance | 3 |
      | HIS-351 | World History | History | 3 |
      | MU-199 | Music Video Production | Music | 3 |
      | PHY-101 | Physical Principles | Physics | 4 |
Prereq Table (Figure 2.3)
  • Purpose: Stores prerequisites for each course.
  • Structure:
    • Columns:
    • course_id: Identifier for the course.
    • prereq_id: Identifier for the prerequisite course.
  • Data Representation:
    • Each row denotes a pair of course identifiers where the second course is a prerequisite for the first.

Relationships Between Tables

  • For example, a row in the instructor table may represent relationships via the course_id, highlighting interconnections among the instructors and the courses they are associated with.
  • Understanding these relationships is crucial for the design and querying of relational databases, enhancing the database's ability to enforce constraints and maintain data integrity.