Relational Database Model

The Relational Database Model

• Introduced in 1970 by E.F. Codd, it relies on predicate logic and set theory.
• Predicate Logic: Used to verify assertions as true or false, such as "Student 324452 is named Mark Reyes."
• Set Theory: Deals with sets and is used for data manipulation. For example, Set A (15, 23, 52) and Set B (41, 52, 70, 12) share the common value 52.

Components of the Relational Model

• Logical data structure that is represented by relations.
• Set of integrity rules to maintain data consistency.
• Set of operations that define data manipulation.

Tables (Relations)

• Two-dimensional structure of rows and columns.
• Each row (tuple) represents data about an entity.
• Each column represents an attribute with a distinct name.
• Every row and column intersection contains one data value.
• All column values have the same data format and a set attribute domain.
• Row and column order isn't important.

Student Table Example

• STUDENT table has six rows/tuples and five columns/attributes.
• Each row describes a student, like row 5 which describes Martin S. Cruz.
• The domain of STU_MI values is limited to characters A to Z (the domain is [A, Z]).
• STU_NUM is a good primary key because it is guaranteed unique for each student.

Keys

• Key Attribute/Group of attributes determining other attribute values. An invoice number identifies all invoice attributes such as invoice date and customer name.
• Determination: Knowing an attribute's value makes it possible to determine another's value.
• Functional Dependence: When one or more attributes values determines the values of other attributes.
• Determinant/Key: The attribute determining another's value.
• Dependent: The attribute whose value is determined.
• Standard notation is ATT_A → ATT_B.
  • Example: STU_NUM → STU_LNAME, with STU_NUM as the determinant and STU_LNAME as the dependent.
  • Knowing STU_NUM, you can determine STU_LNAME because each STU_NUM is associated with only one STU_LNAME. Functional dependence can involve multiple attributes.
  • For example : STU_NUM → (STU_LNAME, STU_FNAME, STU_MI, STU_SECT).
• Composite key: A key composed of multiple attributes.
• A key attribute: is part of a key.

Types of Keys

• Superkey: An attribute/combination uniquely identifying a table row.
  • STU_NUM or any combination with STU_NUM is a superkey.
• Candidate Key: A superkey without unnecessary attributes. STU_NUM is an example.
• Primary Key: Uniquely identifies all attribute values in a row and cannot contain null entries. STU_NUM is an example.
• Foreign Key: Matches a primary key in another table or is null
• Secondary Key: Used strictly for data retrieval purposes.
  • Example: (STU_LNAME, STU_FNAME, STU_MI)

Integrity Rules

• Entity Integrity: Each table row has a unique identity.
• Referential Integrity: References to entity instances should be valid.
• Integrity rules are followed to maintain a good database design.

Entity Integrity

• Requirement: Primary key entries are unique and not null.
• Purpose: Guarantees each row has a unique identity, with foreign keys properly referencing primary key values.

Referential Integrity

• Requirement: A foreign key may be null or match a primary key.
• Purpose: Impossible for an attribute to have an invalid entry.

Tables Features

• STUDENTS table features include the STU_NUM primary key without null entries, and all entries are unique.
• SECTIONS table primary key is STU_SECT, and the values are free of null entries.

Referential Integrity Details

• The STUDENTS table holds a foreign key, STU_SECT, which links to entries in the SECTIONS table.
• Code "NS" indicates absence of value.
• if flags is used, the SECTIONS table has to hold a dummy row with an STU_SECT value of NS.

Relational Algebra Basics

• Relational algebra is a set of mathematical principles used to manipulate table content.
• New relations made by using relational algebra operators on tables are called closure.
• The condition that is evaluated is a predicate.
• Eight fundamental relational operators are: SELECT, PROJECT, UNION, INTERSECT, DIFFERENCE, PRODUCT, JOIN, DIVIDE.

Relational Algebra Operators

• Retrieves a subset of rows
  • Syntax: σ CONDITION (TABLE)
  • Example: σ STU_NUM = 324452 (STUDENTS)
• PROJECT: Retrieves a subset of columns.
  • Syntax: π COLUMNS (TABLE)
  • Example: π STU_FNAME, STU_LNAME (STUDENTS)
• UNION: Merges two union-compatible tables into a new table, dropping duplicates.
  • Syntax: TABLE1 U TABLE2
  • Example: STUDENTS U SECTIONS
• INTERSECT: Retrieves common rows from two union-compatible tables.
  • Syntax: TABLE1 ∩ TABLE2
  • Example: STUDENTS ∩ SECTIONS
• DIFFERENCE: Retrieves rows that are not in another union compatible table.
  • Syntax: TABLE1 - TABLE2
  • Example: STUDENTS – SECTIONS
• PRODUCT: Retrieves paired rows from two tables (Cartesian Product).
  • Syntax: TABLE1 x TABLE2
  • Example: STUDENTS x SECTIONS
• JOIN: Retrieves rows from two tables with common values in their attributes.
  • Syntax: TABLE1 TABLE2
  • Example: STUDENTS SECTIONS
• DIVIDE: Retrieves values.
  • Syntax: TABLE1 ÷ TABLE2
  • Example: STUDENTS ÷ SECTIONS