Database Notes

Relational Database Model

• Introduced by E. F. Codd in 1970, based on predicate logic and set theory.
  • Predicate logic: Framework to verify assertions as true or false.
  • Set theory: Deals with sets and is the basis for data manipulation.
• The relational model has three components:
  1. Logical data structure (relations)
  2. Integrity rules for data consistency
  3. Operations for data manipulation

Tables (Relations)

• Two-dimensional structure of rows and columns.
• Row (tuple): Data about an entity.
• Column: Attribute with a distinct name.
• Intersection: Single data value.
• Column values: Conform to the same data format.
• Attribute domain: Specific range of values for a column.
• Order of rows/columns: Not important in a DBMS.

Keys

• Key: Attribute(s) that determines other attribute values.
• Determination: Knowing an attribute's value determines another's.
• Functional dependence: Value of one or more attributes determines the value of one or more other attributes.
  • Determinant (key): Attribute that determines another.
  • Dependent: Attribute determined by the other.
  • Notation: $ATT<em>A \rightarrow ATT</em>B$ (e.g., $STU<em>NUM \rightarrow STU</em>LNAME$).
• Composite key: Key composed of multiple attributes.
• Key attribute: Attribute that is part of a key.

Key Types

• Superkey: Attribute(s) that uniquely identifies a row.
  • Example: STUNUM or (STUNUM, STU_LNAME)
• Candidate Key: Superkey without unnecessary attributes.
  • Example: STU_NUM
• Primary Key: Candidate key to uniquely identify all other attribute values; cannot contain null entries.
  • Example: STU_NUM
• Foreign Key: Attribute(s) in one table that must match the primary key in another table or be null.
  • Example: STU_SECT
• Secondary Key: Attribute(s) used for data retrieval.
  • Example: (STULNAME, STUFNAME, STU_MI)

Integrity Rules

• Entity integrity: Each row has a unique identity.
• Referential integrity: Every reference to an entity instance is valid.

Entity Integrity

• Requirement: Primary key entries are unique and not null.
• Purpose: Unique row identity; foreign keys can reference primary keys.
• Example: Invoice numbers must be unique and not null.

Referential Integrity

• Requirement: Foreign key may be null (if not part of primary key) or match a primary key value in another table; every non-null foreign key must reference an existing primary key.
• Purpose: Prevents invalid entries; ensures valid relationships between tables.
• Example: A customer might not have an assigned sales representative, but it will be impossible to have an invalid sales representative.

Flags

• Special codes used to avoid nulls by indicating the absence of some value.
• Example: NS for an unassigned section; requires a dummy row in the SECTIONS table.

Relational Algebra

• Mathematical principles for manipulating relational table contents.
• Closure: Using relational algebra operators on tables produces new tables.
• Predicate: Condition to be evaluated.

Relational Operators

• SELECT ($\sigma$): Retrieves a subset of rows.
  • Syntax: $σ CONDITION (TABLE)$
  • Example: $σ STU_NUM = 324452 (STUDENTS)$
• PROJECT ($\pi$): Retrieves a subset of columns.
  • Syntax: $π COLUMNS (TABLE)$
  • Example: $π STU<em>FNAME, STU</em>LNAME (STUDENTS)$
• UNION ($\bigcup$): Merges two union-compatible tables, dropping duplicates.
  • Union-compatible: Tables with the same number of columns and compatible domains.
  • Syntax: $TABLE1 \bigcup TABLE2$
  • Example: $STUDENTS \bigcup SECTIONS$
• INTERSECT ($\bigcap$): Retrieves rows common to two union-compatible tables.
  • Syntax: $TABLE1 \bigcap TABLE2$
  • Example: $STUDENTS \bigcap SECTIONS$
• DIFFERENCE (–): Retrieves rows from one table not found in another union-compatible table.
  • Syntax: $TABLE1 – TABLE2$
  • Example: $STUDENTS – SECTIONS$
• PRODUCT (x): Retrieves all possible pairs of rows from two tables (Cartesian Product).
  • Syntax: $TABLE1 x TABLE2$
  • Example: $STUDENTS x SECTIONS$
• JOIN ($\Join$): Retrieves rows from two tables based on criteria (e.g., common values).
  • Syntax: $TABLE1 \Join TABLE2$
  • Example: $STUDENTS \Join SECTIONS$
• DIVIDE ($\div$): Retrieves values.
  • Syntax: $TABLE1 \div TABLE2$
  • Example: $STUDENTS \div SECTIONS$