Relational Model: Predicate Logic & Set Theory

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

Predicate Logic

• Predicate logic is used in mathematics
• It provides a framework to verify an assertion or statement of fact as true or false
• A student with an ID of 324452 named Mark Reyes exemplifies predicate logic

Set Theory

• Set theory deals with sets or groups
• It forms the basis for data manipulation in the relational model
• Example: Set A (15, 23, 52) and Set B (41, 52, 70, 12) share the common value 52

Relational Model Components

• The relational model consists of three components
• Logical data structure represented by relations
• A set of integrity rules ensuring data consistency over time
• Operations defining data manipulation

Tables

• A table (relation) is a two-dimensional structure composed of rows and columns
• Each row (tuple) represents data about an entity
• Each column represents an attribute with a distinct name
• The intersection of a row and column is one single data value
• All column values must conform to the same data format and the column contains a specific range of values (attribute domain)
• The order of rows and columns is not important in a DBMS

Table Conclusions

• The STUDENT table has six rows (tuples) and five columns (attributes)
• Each row describes a student, like row 5 describing Martin S. Cruz
• The STU_MI domain are characters A to Z
• STU_NUM is the primary key as it is unique for each student, unlike STU_LNAME

Keys

• A key is an attribute or group determining other attribute values
• For example, an invoice number identifies all invoice details, such as date and customer
• Determination is when knowing an attribute's value determines another's value, based on attribute relationships
• Functional dependence is when one or more attributes determine another's value
• The determinant is the attribute determining another, while the dependent is the attribute determined
• Standard notation: ATT_A → ATT_B
• STU_NUM → STU_LNAME exemplifies this
• STU_NUM is the determinant
• STU_LNAME is the dependent, as its value is determined by STU_NUM
• Functional dependence can involve multiple attributes like STU_NUM → (STU_LNAME, STU_FNAME, STU_MI, STU_SECT)
• A composite key combines more than one attribute
• An attribute part of a key is a key attribute

Types of Keys

• Superkey: Uniquely identifies rows
• Example: STU_NUM
• Example: any combination including STU_NUM, such as (STU_NUM, STU_LNAME) or (STU_NUM, STU_FNAME, STU_SECT)
• Candidate Key: A superkey without unnecessary attributes
• Example: STU_NUM
• Primary Key: A candidate key uniquely identifying all attribute values and cannot contain null entries
• Example: STU_NUM
• Foreign Key: Matches the primary key in another table or is null
• Example: STU_SECT if used as a primary key in another table
• Secondary Key: Used strictly 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 by another is valid
• Integrity rules are followed to design a good database

Entity Integrity

• Requirement: Primary key entries are unique and non-null
• Purpose: Each row has unique identity, foreign key values reference primary key values
• Example: Invoices are uniquely identified by number, which cannot be duplicated or null

Referential Integrity

• Requirement: Foreign key can be null if it is not part of its table's primary key, otherwise it matches a related table's primary key
• Purpose: Non-null foreign key must reference an existing primary key value
• It is possible for an attribute not to have a corresponding value, but it will be impossilbe to have an invalid entry
• Referential integrity prevents deleting a row with mandatory matching foreign key values in another table
• Example: A customer might not yet have an assigned sales representative, but it will be impossible to have an invalid sales representative

Tables

• STUDENTS Table:
• Primary key: STU_NUM
• Foreign key: STU_SECT
• SECTIONS Table:
• Primary key: STU_SECT
• Foreign key: None

Tables Features

• The STUDENTS table's STU_NUM has no null entries and all values are unique
• The SECTIONS table's STU_SECT also has no null entries and all entries are unique

Referential Integrity Details

• The STUDENTS table's STU_SECT links to the SECTIONS table
• The STU_CODE row identified by 324257 has a null STU_SECT foreign key because Marco Velasco isn't assigned a section
• The remaining STU_SECT entries in STUDENTS match STU_SECT entries in SECTIONS
• Dummy rows with special codes (flags) avoid nulls to indicate the absence of some value
• Example: The code NS for STU_SECT indicates no assigned section

Relational Algebra Overview

• Data manipulation generates useful information
• Relational algebra, mathematical principles for manipulating table contents
• Using relational algebra operators on existing relations (tables) creates new relations called closure
• The condition to be evaluated is known as predicate

Relational Operators

• SELECT: Retrieves rows subset
  • Syntax: σ CONDITION (TABLE) -STU_NUM = 324452 (STUDENTS)
• PROJECT: Retrieves columns subset
  • Syntax: π COLUMNS (TABLE) -π STU_FNAME, STU_LNAME (STUDENTS)
• UNION: Merges union-compatible tables, dropping duplicates
  • Syntax: TABLE1 ∪ TABLE2 -STUDENTS ∪ SECTIONS
• INTERSECT: Retrieves common rows in union-compatible tables
  • Syntax: TABLE1 ∩ TABLE2 -STUDENTS ∩ SECTIONS
• DIFFERENCE: Retrieves rows from one table absent in another union-compatible table
  • Syntax: TABLE1 – TABLE2 -STUDENTS – SECTIONS
• PRODUCT: Retrieves possible row pairs from two tables (Cartesian Product)
  • Syntax: TABLE1 x TABLE2 -STUDENTS x SECTIONS
• JOIN: Retrieves rows between from two tables based on criteria (common attribute values)
  • Syntax: TABLE1 ⨝ TABLE2 -STUDENTS ⨝ SECTIONS
• DIVIDE: Retrieves values
  • Syntax: TABLE1 ÷ TABLE2 -STUDENTS ÷ SECTIONS
• Two or more tables that have the same number of columns and the corresponding columns have compatible domains are union-compatible