Relational Database Model Fundamentals

The Relational Database Model Fundamentals

Introduction to the Relational Model

The relational database model was introduced by E. F. Codd in 1970. It is structured around predicate logic and set theory. Predicate logic allows assertions to be verified as true or false, providing clarity in representing data. For instance, asserting "a student with an ID of 324452 is named Mark Reyes" can be validated. Set theory focuses on groups of objects (sets), which is crucial for data organization and manipulation. An example demonstrates this: Set A = {15, 23, 52} and Set B = {41, 52, 70, 12} indicates that both share the value 52.

Components of the Relational Model

The relational model consists of three main components:

  1. Logical Data Structure:

    • Represented by relations, which can be visualized as tables.

  2. Integrity Rules:

    • Ensure that data remains consistent over time.

  3. Operations for Data Manipulation:

    • Defines the methods through which data can be altered.

Structure of Tables in Databases

A table (or relation) is a two-dimensional structure made up of rows and columns:

  • Each row (or tuple) represents a data record (an entity).

  • Each column represents an attribute, each with distinct naming.

  • Each intersection of a row and column holds a single data value.

  • All values in a column must follow the same data format, termed as the attribute domain.

  • Important to note, the order of rows and columns in a DBMS is not significant.

Example Table: STUDENTS

  • Rows (tuples): 6

  • Columns (attributes): 5

  • Example row:

    • STU_NUM: 324452

    • STU_LNAME: Reyes

    • STU_FNAME: Mark

    • STU_MI: V

    • STU_SECT: IT101

  • Conclusion from the STUDENT table:

    • STUNUM serves as the primary key ensuring each student's uniqueness while STULNAME alone is not sufficient as multiple students can share the same last name.

Keys in Relational Databases

Definition of Key Terms

  • A key is an attribute or set of attributes that uniquely identifies a row in a table.

  • Determination occurs when the value of one attribute can determine another value.

  • Functional dependence indicates that one attribute's value can uniquely dictate another's value.

  • Determinants determine the dependent attributes.

Notation of Functional Dependence

The notation for this relationship is:

  • ATTA → ATTB
    Example: STUNUM → STULNAME (where STUNUM is the determinant, and STULNAME is the dependent).
    A composite key consists of multiple attributes that together act as a unique identifier in a table.

Types of Keys

  1. Superkey: Uniquely identifies any row (e.g., STU_NUM and any combination including it).

  2. Candidate Key: A superkey stripped of unnecessary attributes (e.g., STU_NUM).

  3. Primary Key: A selected candidate key that uniquely identifies row values and cannot contain nulls.

  4. Foreign Key: Attribute(s) in one table that correspond to a primary key in another table.

  5. Secondary Key: Used for data retrieval (e.g., STULNAME, STUFNAME).

Integrity Rules Explained

Integrity rules help maintain a sound database structure:

  • Entity Integrity: Each row must have unique identity; primary key entries should not be null.

    • Example: No two invoices share a number, and all must be present.

  • Referential Integrity: Ensures valid references between entities; foreign keys must match existing primary keys or be null.

    • Example: A sales representative’s ID must correspond to an existing ID in another table.

Sample Tables and Keys Analysis

  • STUDENTS Table: Primary key = STUNUM; Foreign key = STUSECT.

  • SECTIONS Table: Primary key = STU_SECT; no foreign keys.

Relational Algebra and Its Operators

Relational Algebra is foundational for manipulating and generating information from relational tables; it uses mathematical principles:

  • Operations yield new relations from existing ones, a concept known as closure.

  • Predicate defines conditions for operations. Here are eight fundamental operators:

  1. SELECT (σ): Retrieves rows based on a condition.

    • Example: σ STU_NUM = 324452 (STUDENTS).

  2. PROJECT (π): Retrieves specific columns.

    • Example: π STUFNAME, STULNAME (STUDENTS).

  3. UNION (∪): Merges two tables, removing duplicates.

    • Example: STUDENTS ∪ SECTIONS.

  4. INTERSECT (∩): Retrieves common rows from two tables.

  5. DIFFERENCE (–): Rows from one table not found in another.

  6. PRODUCT (×): Pairs rows from two tables (Cartesian Product).

  7. JOIN (⨝): Combines rows based on matching criteria.

  8. DIVIDE (÷): Retrieves values reflecting division between tables.

Conclusion

Understanding the relational model is crucial for effective database management and implementation. Comprehending the roles of keys, integrity constraints, and operations like those in relational algebra serves as a foundation for manipulating and leveraging data effectively in real-world applications.