Untitled

The Relational Database Model Fundamentals

• Introduction

• Introduced by E. F. Codd in 1970.

• Based on predicate logic and set theory.

• Key Concepts

• Predicate Logic: Framework to verify assertions (true/false).

  • Example: A student with ID 324452 is Mark Reyes.

• Set Theory: Deals with groups of items (sets).

  • Example: Set A = {15, 23, 52}, Set B = {41, 52, 70, 12}, common value 52.

• Components of the Relational Model

1. Logical Data Structure: Represented by relations (tables).
2. Integrity Rules: Ensure data consistency over time.
3. Data Manipulation Operations: Defines operations for data handling.

• Tables (Relations)

• Two-dimensional structure of rows and columns.

  • Each row (tuple) represents an entity's data.
  • Each column represents an attribute with a distinct name.
  • Intersection of row and column = single data value.
  • Values in a column must have the same data format (attribute domain).

• Example Table: STUDENT
  | STUNUM | STULNAME | STUFNAME | STUMI | STU_SECT |
  |---------|-----------|-----------|--------|----------|
  | 324452 | Reyes | Mark | V | IT101 |
  | 324257 | Velasco | Marco | R | CS101 |
  | 324258 | Santos | Markus | D | IS101 |
  | 324273 | dela Cruz | Miguel | C | CS102 |
  | 324299 | Cruz | Martin | S | IT101 |
  | 324264 | Santiago | Matthew | A | IT102 |

• The table has 6 rows and 5 columns (attributes).

• Primary key: STU_NUM (unique for each student).

• Other attributes (e.g., STU_LNAME) not suitable as primary keys due to potential duplication.

• Keys

• Key: Attribute/group that determines other attributes.

  • Example: Invoice number identifies invoice attributes.

• Determination: Knowing one attribute value helps determine another.

• Functional Dependence: Value of attributes determining others.

• Notation: ATTA → ATTB (e.g., STUNUM → STULNAME).

• Composite Key: Key composed of multiple attributes.

• Key Attributes: Attributes part of a key.

• Types of Keys

  Key Type	Description	Example
  Superkey	Uniquely identifies any row	STUNUM, any combination with STUNUM
  Candidate Key	Superkey without unnecessary attributes	STU_NUM
  Primary Key	Candidate key to uniquely identify rows; cannot be null	STU_NUM
  Foreign Key	Combines with another table's primary key or is null	STU_SECT
  Secondary Key	Used strictly for retrieval	(STULNAME, STUFNAME, STU_MI)

• Integrity Rules

• Entity Integrity: Each row has a unique identity (no nulls in primary key).

• Referential Integrity: Valid references to entity instances.

• Examples of Integrity:

  • Entity Integrity: No duplicate invoices.
  • Referential Integrity: Foreign key must match a corresponding primary key or can be null.

• Example Tables

• STUDENTS: Primary Key - STUNUM; Foreign Key - STUSECT.

• SECTIONS: Primary Key - STU_SECT.

• Relational Algebra

• Framework for manipulating table contents.

• Closure: Use of algebra operators to produce new relations.

• Operators:

  Operator	Description	Symbol	Syntax	Example
  SELECT	Retrieves subset of rows	σ	σ CONDITION (TABLE)	σ STU_NUM = 324452 (STUDENTS)
  PROJECT	Retrieves subset of columns	π	π COLUMNS (TABLE)	π STUFNAME, STULNAME (STUDENTS)
  UNION	Merges two tables, drops duplicates	∪	TABLE1 ∪ TABLE2	STUDENTS ∪ SECTIONS
  INTERSECT	Common rows in tables	∩	TABLE1 ∩ TABLE2	STUDENTS ∩ SECTIONS
  DIFFERENCE	Rows in one not found in another	–	TABLE1 – TABLE2	STUDENTS – SECTIONS
  PRODUCT	Pairs of rows (Cartesian Product)	x	TABLE1 x TABLE2	STUDENTS x SECTIONS
  JOIN	Rows based on criteria	⨝	TABLE1 ⨝ TABLE2	STUDENTS ⨝ SECTIONS
  DIVIDE	Retrieves values	÷	TABLE1 ÷ TABLE2	STUDENTS ÷ SECTIONS

• References

• Coronel, C. & Morris, S. (2017) - Database systems.

• Elmasri, R. & Navathe, S. (2016) - Fundamentals of database systems.

• Kroenke, D. & Auer, D. (2016) - Database processing fundamentals.