Relational Model Fundamentals

• The relational model, introduced in 1970 by E. F. Codd, is based on predicate logic and set theory.

Fundamentals

• Predicate logic provides a framework to verify assertions (statements of fact) as true or false; for example, a student with ID 324452 is Mark Reyes.
• Set theory deals with sets or groups and is the basis for data manipulation; for example, sets A (15, 23, 52) and B (41, 52, 70, 12) share the value 52.
• The relational model has three components: a logical data structure (relations), integrity rules, and a set of operations.
• A table (relation) is a two-dimensional structure of rows and columns.
• Each table row (tuple) represents data about an entity.
• Each table column represents an attribute with a distinct name.
• Each row and column intersection represents a single data value.
• Column values must conform to the same data format, with a specific range known as the attribute domain.
• The order of rows and columns is not important in a DBMS.

Student Table Conclusions

• The STUDENT table is composed of six rows/tuples and five columns/attributes.
• Each row describes a student; for example, row 5 describes Martin S. Cruz.
• STU_MI values are limited to characters A to Z, representing the domain [A, Z].
• STU_NUM is the primary key, guaranteed unique for each student.
• STU_LNAME is not a good primary key because students can share the same last name.

Keys

• A key is an attribute or group determining other attribute values; an invoice number identifies invoice attributes like date and customer name.
• Determination is when an attribute's value makes it possible to determine another's value, based on relationships among attributes.
• Functional dependence means one or more attributes' values determine others' values.
• The determinant or key is the attribute whose value determines another attribute, called the dependent.
• Standard notation for representing relationships between attributes is ATT_A → ATT_B; for example, STU_NUM → STU_LNAME.
• STU_NUM is the determinant, and STU_LNAME is the dependent; a STU_NUM value determines the STU_LNAME since only one value is associated with any given STU_NUM.
• Functional dependence can involve a determinant that comprises multiple attributes, example STU_NUM → (STU_LNAME, STU_FNAME, STU_MI, STU_SECT).
• A composite key is composed of more than one attribute.
• A key attribute is part of a key.

Key Types

• A superkey uniquely identifies rows; such as STU_NUM, or (STU_NUM, STU_LNAME).
• A candidate key is a superkey without unnecessary attributes; STU_NUM.
• A primary key is a candidate key to uniquely identify all attribute values in a row and cannot contain null entries; STU_NUM.
• A foreign key matches the primary key in another table or can be null; STU_SECT can be a foreign key if used as a primary key of another table.
• A secondary key is used strictly for data retrieval; such as (STU_LNAME, STU_FNAME, STU_MI).

Integrity Rules

• Entity integrity is when each table row has a unique identity.
• Referential integrity is when every reference to an entity instance by another entity instance is valid.
• Integrity rules are followed to maintain database design.

Entity Integrity

• Requirement: Primary key entries are unique and non-null.
• Purpose: Rows have unique identity, and foreign keys properly reference primary key values.
• Example: An invoice has a unique non-null number.

Referential Integrity

• Requirement: Foreign keys may be null as long as they are not part of the table's primary key, or an entry that matches the related table's primary key.
• Every non-null foreign key value must reference an existing primary key value.
• It is possible for an attribute to not have a value but impossible to have an invalid entry.
• The enforcement of referential integrity prevents deleting a row with mandatory matching foreign key values in another table.
• A customer might not yet have assigned sales representative number, but it will be impossible to have an invalid representative number.

Table Features

• Entity Integrity: The STUDENTS primary key (STU_NUM) column has no null entries, and all entries are unique.
• The SECTIONS table's primary key is STU_SECT, this column is also free of null entries.
• Referential Integrity: The STUDENTS table contains a foreign key, STU_SECT, that links entries in it to entries in the SECTIONS table.
• To avoid null values, special codes called flags are used, such as NS in the STU_SECT entry, to indicate that Marco Velasco's section does not yet have an assigned code.

Relational Algebra

• Data can be manipulated to generate useful information.
• Relational algebra manipulates relation contents, and when used on existing relations (tables) produce new relations called closure.
• The condition to be evaluated is also known as predicate.
• The eight fundamental relational operators are SELECT, PROJECT, UNION, INTERSECT, DIFFERENCE, PRODUCT, JOIN, DIVIDE.

Relational Algebra Operators

• SELECT retrieves a subset of rows: σ CONDITION (TABLE).
• PROJECT retrieves a subset of columns: π COLUMNS (TABLE).
• UNION merges union-compatible tables, dropping duplicate rows: TABLE1 ∪ TABLE2
• INTERSECT returns rows common to two union-compatible tables: TABLE1 ∩ TABLE2.
• DIFFERENCE retrieves rows not found in another union-compatible table: TABLE1 – TABLE2.
• PRODUCT retrieves possible row pairs; Cartesian Product: TABLE1 x TABLE2.
• JOIN retrieves rows based on criteria: TABLE1 ⨝ TABLE2.
• DIVIDE retrieves values: TABLE1 ÷ TABLE2.