Study Notes on the Relational Model

2.1 Structure of Relational Databases

  • A relational database consists of a collection of tables, each assigned a unique name.

  • Example of instructor Table:

    • Contains four column headers: ID, name, dept name, salary.

    • Each row records information about an instructor (ID, name, dept name, salary).

  • Example of course Table:

    • Stores information about courses with columns course id, title, dept name, credits.

    • Unique identification for each course is done through the course id column.

  • Prerequisite Table (Figure 2.3):

    • Columns: course id, prereq id.

    • Each row indicates prerequisite relationships between courses (e.g., one course is a prerequisite for another).

  • Each row in a table (such as instructor) represents a relationship between specified ID and corresponding values (name, dept name, salary).

  • Mathematical Representation:

    • A tuple is defined as a sequence (or list) of values.

    • A relationship for n values is represented as an n-tuple which corresponds to a row in the table.

    • Relation corresponds to a table, tuple corresponds to a row, and attribute refers to a column of that table.

  • Relation Instance:

    • A specific instance of a relation containing specific rows, as exemplified in Figure 2.1 with 12 instructors.

  • The order of tuples in a relation is irrelevant (rows can be sorted or unsorted).

    • Example: Two relations could be identical even if one is sorted and the other is not.

  • Domain of an Attribute:

    • Set of all possible values for a specific attribute.

    • Example: Salary attribute's domain includes all possible salary values.

  • Atomicity Requirement:

    • Domains of all attributes must be atomic, meaning indivisible.

    • Example: If phone numbers are stored as a set, the domain is non-atomic. Individual phone numbers must be treated as atomic units.

  • Null Values:

    • Represents an unknown or non-existent value, often used when a data point cannot be determined.

    • Can complicate database operations, therefore, best to avoid whenever possible.

  • The strict structure of relations has advantages for data storage and processing but may not adapt well to changing data types and structures over time.

2.2 Database Schema

  • Distinction between Database Schema and Instance:

    • Schema: Logical design of the database (types of data and organization).

    • Instance: Snapshot of the database contents at a specific time.

  • A relation schema consists of a list of attributes paired with their domains.

    • Implementation specifics will be discussed in Chapter 3 (especially concerning SQL).

  • Common attribute usage across different relations (e.g., dept name in both instructor and department schemas) allows for relational mapping.

  • Example of Department Relation Schema:

    • department(dept name, building, budget).

  • Common Relation Names:

    • Both instance and schema may use the same name (e.g., instructor) unless specification is required (e.g., "the instructor schema").

  • Defining sections and their relationship to other entities:

    • Each course may have multiple sections represented in the section relation: section(course id, sec id, semester, year, building, room number, time slot id).

  • Additional Relations:

    • Various relationships for a university setting, including student, advisor, takes, classroom, and time slot relations.

2.3 Keys

  • Defining Keys:

    • Determine how tuples are uniquely distinguished in a relation based on attribute values.

    • Superkey: A combination of attributes that can uniquely identify a tuple (e.g., ID in the instructor table).

    • Candidate Key: A minimal superkey, meaning it cannot have any superkey subset. A candidate key can be one or more attributes that can still uniquely identify a tuple.

    • Primary Key: A candidate key selected by the designer for identifying tuples, often noted as underlined in schemas.

  • Constraints are enforced via keys; no two tuples can have the same value for any key attributes.

  • Foreign Key Constraints: Enforce relationships by ensuring that foreign key attributes must match their corresponding primary key in another relation (example: dept name in instructor referencing department).

    • Illustrated by specific examples and schemas demonstrating the primary and foreign key relationships.

  • Referential Integrity Constraints: More generalized constraints that do not necessarily rely on primary keys for validation.

2.4 Schema Diagrams

  • Creating Schema Diagrams:

    • Visual representation of database schemas indicating relationships and constraints via figurative boxes for each relation with underlined primary keys and arrows to demonstrate foreign key constraints.

    • Understanding schema diagrams helps to visualize the structural aspects of the database schema as used in different database systems.

2.5 Relational Query Languages

  • A query language facilitates user requests for information from the database, elevated above programming languages. Query languages can be categorized:

    • Imperative: User specifies operations to achieve results.

    • Functional: Uses functions for computations on data.

    • Declarative: User describes desired information without detailing steps for obtaining it.

  • Practical implementation includes elements of all three types—SQL being a prime example.

2.6 The Relational Algebra

  • Overview of Relational Algebra:

    • Comprised of operations that yield new relations from existing ones.

    • Operations classified as unary (acting on a single relation) or binary (acting on two relations).

2.6.1 Select Operation

  • Select operation targets tuples meeting specific predicates. Notation used: σ (e.g., σdept_name=“Physics” (instructor)).

  • Allows for combining multiple predicates (e.g., σdept_name=“Physics” ∧ salary>90000 (instructor)).

  • Also allows for comparisons between attributes in selection predicates (as seen in examples).

2.6.2 Project Operation

  • The project operation excludes certain attributes from the result set, denoted by the uppercase Greek letter Π (e.g., ΠID, name, salary(instructor)).

  • The generalized version allows attributes to be manipulated within the list (e.g., for monthly salaries).

2.6.3 Composition of Relational Operations

  • Important concept where the output of one relational operation can serve as input for another (e.g., combining selections and projections).

2.6.4 Cartesian-Product Operation

  • Cartesian product combines every tuple from two relations, producing a larger set (notated as r1 × r2). Naming conventions mitigate attribute ambiguity.

2.6.5 Join Operation

  • The join operation unites both a selection and a Cartesian product to effectively filter based on matching keys (e.g., σinstructor.ID=teaches.ID (instructor × teaches)).

2.6.6 Set Operations

  • Union (∪): Combines unique tuples from both relations.

  • Intersection (∩): Identifies tuples in both sets.

  • The difference (−): Captures tuples in one relation but not the other.

2.6.7 The Assignment Operation

  • Assignment operator (←) allows for the conciseness of expressions through temporary relation variables for complex queries.

2.6.8 The Rename Operation

  • Rename operator (ρx (E)) provides new names for results of expressions, especially useful in cases where the same relation is referenced multiple times.

  • Alternative forms allow for renaming attributes directly in the result schema.

2.6.9 Equivalent Queries

  • Queries can often be rewritten in different forms that yield the same results, showing flexibility in relational algebra usage.

2.7 Summary

  • The relational data model revolves around tables, querying, updating tuples, and various expression languages.

  • Important concepts: relational schema vs instance, superkeys/candidate keys, primary/foreign keys, and referential integrity constraints are fundamental to database integrity and functionality.