Relational Model Fundamentals

Fundamentals of Relational Model

• The relational model is from 1970, by E. F. Codd.
• It is based on predicate logic and set theory.
• Predicate logic helps verify assertions in mathematics.
• Set theory deals with sets, and is the basis for data manipulation.
• Example: Sets A (15, 23, 52) and B (41, 52, 70, 12) share the value 52.
• The relational model includes:
  • A logical data structure represented by relations
  • A set of integrity rules for data consistency
  • A set of operations for data manipulation

Tables and Relations

• A table is a two-dimensional structure of rows and columns.
• A table row (tuple) represents data about an entity.
• A table column represents an attribute with a distinct name.
• Each row and column intersection represents a single data value.
• All column values must conform to the same data format.
• Each column has an attribute domain, which is a range of values.
• The order of rows and columns does not matter in a DBMS.
• In a STUDENT table:
  • There are six rows/tuples and five columns/attributes.
  • Each row describes a student (e.g., row 5 describes Martin S. Cruz).
  • The STU_MI domain is characters A to Z.
  • STU_NUM is the primary key; while STU_LNAME is not.

Keys

• A key is an attribute/group of attributes determining other attribute values.
• Invoice number identifies invoice attributes.
• Determination is knowing an attribute's value to find another's value.
• Functional dependence means one or more attributes determines the value of others.
• The determinant is the attribute whose value determines another.
• The dependent is the attribute whose value is determined by the other.
• Standard notation: ATT_A → ATT_B, e.g., STU_NUM → STU_LNAME.
• STU_NUM is determinant, STU_LNAME is dependent.
• Knowing STU_NUM determines STU_LNAME.
• Functional dependence can involve a determinant that comprises multiple attributes.
• Composite key: STU_NUM → (STU_LNAME, STU_FNAME, STU_MI, STU_SECT)
• A key attribute is a part of a key.

Key Types

• Superkey: An attribute/combination that uniquely identifies a table row.
  • Example: STU_NUM and any combination with STU_NUM
• Candidate Key: A superkey without unnecessary attributes.
  • Example: STU_NUM
• Primary Key: A candidate key to uniquely identify all attribute values, cannot contain null entries.
  • Example: STU_NUM
• Foreign Key: An attribute or combination in one table that matches the primary key in another table (or is null).
  • Example: STU_SECT.
• Secondary Key: An attribute or combination used for data retrieval.
  • Example: (STU_LNAME, STU_FNAME, STU_MI)

Integrity Rules

• Entity integrity: Each table row has a unique identity.
• Referential integrity: Every reference to an entity instance is valid.
• Integrity rules are followed to maintain a good database design.

Entity Integrity

• Requirement: Primary key entries are unique and not null.
• Purpose: Each row has a unique identity and foreign key values can properly reference primary key values.
  • Example: Invoices must be uniquely identified by their invoice number.

Referential Integrity

• Requirement: A foreign key can be null if it is not part of the table’s primary key, or matches the primary key value of a related table.
• Every non-null foreign key value must reference an existing primary key value.
• It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry
• Purpose: It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry.
• Enforcing referential integrity prevents deleting a row if its primary key corresponds to mandatory foreign key values in another table.
  • A customer might not have an assigned sales representative (number), but it will be impossible to have an invalid sales representative (number).

Real-World Referential Integrity

• STUDENTS table contains STU_SECT, a foreign key linking to SECTIONS table entries.
• STU_CODE row 324257 has a null STU_SECT (Marco Velasco unassigned).
• Other STU_SECT entries in STUDENTS match entries in SECTIONS.
• Flags (special codes) can indicate the absence of a value. Example: code NS. If used, the SECTIONS table must have a dummy row with STU_SECT value NS.

Relational Algebra

• Data can be manipulated for useful information.
• Relational algebra principles manipulating table contents.
• Relational algebra operators on tables yields new relations (closure).
• Condition evaluated is known as predicate.
• There are eight fundamental relational operators:

Relational Algebra Operators

• SELECT: Retrieves a subset of rows based on specified conditions.
  • Syntax: σ CONDITION (TABLE)
• PROJECT: Retrieves a subset of columns.
  • Syntax: π COLUMNS (TABLE)
• UNION: Merges union-compatible tables, removing duplicates.
• INTERSECT: Retrieves common rows from two union-compatible tables.
  • Syntax: TABLE1 ∩ TABLE2
• DIFFERENCE: Retrieves rows from one table not found in another union-compatible table.
  • Syntax: TABLE1 – TABLE2
• PRODUCT: Retrieves pairs of rows from two tables (Cartesian product).
  • Syntax: TABLE1 x TABLE2
• JOIN: Retrieves rows based on criteria (ex. common attribute values).
  • Syntax: TABLE1 ⨝ TABLE2
• DIVIDE: Retrieves values.
  • Syntax: TABLE1 ÷ TABLE2