Module 3: The Relational Database Model Flashcards

Logical Data Representation and Relational Table Structure

• The relational data model allows database designers to focus on logical representations of data and relationships rather than physical storage details.

• A logical view provides a simplified, high-level abstraction of stored data, which facilitates simple and effective database design methodologies.

• A table, also referred to as a relation, is perceived as a two-dimensional structure composed of intersecting rows and columns.

• A table serves as a persistent representation of a logical relation, meaning its contents can be permanently saved and accessed for future processing.

Structural Characteristics of Relational Tables

• Two-Dimensional Structure: Tables are composed of rows (horizontal) and columns (vertical).

• Tuples: Each table row represents a single entity occurrence within an entity set. A row is formally termed a tuple.

• Attributes: Each table column represents an attribute of the entity, and every column possesses a distinct name.

• Single Data Values: Every intersection of a row and a column contains a single, atomic data value.

• Data Format Consistency: All entries in a given column must conform strictly to the same data format.

• Attribute Domain: Each column is restricted to a specific range of allowable values known as its attribute domain.

• Order Independence: The visual or physical ordering of rows and columns is completely immaterial to the Database Management System (DBMS).

• Unique Row Identification: Every table must contain an attribute or a combination of attributes that uniquely identifies each individual row.

Keys, Determination, and Functional Dependencies

• Keys are foundational elements in the relational model used to guarantee row uniqueness, establish logical table relationships, and enforce data integrity constraints.

Determination and Functional Dependency

• Determination: Determination is the functional state in which knowing the value of one or more attributes allows the exact value of another attribute to be determined.

  • A basic illustrative mathematical relationship is:     $\text{revenue} - \text{cost} = \text{profit}$

• Functional Dependence: The state where the value of one or more attributes determines the exact value of one or more other attributes.

• Determinant (Key): The attribute or attribute set whose value determines another attribute.

• Dependent: The attribute whose value is determined by the determinant.

• Full Functional Dependence: A condition where an attribute is functionally dependent on a composite key, but not on any subset of that composite key. The entire collection of attributes in the determinant is required to establish the relationship.

Key Classifications

• Superkey: Any attribute or set of attributes that uniquely identifies any given row in a table.

• Candidate Key: A minimal superkey; a superkey that contains no superfluous attributes or subsets that could independently serve as a superkey.

• Primary Key (PK): A candidate key selected to uniquely identify all other attribute values in any given row. A primary key cannot contain null values.

• Composite Key: A key composed of more than one attribute.

• Key Attribute: Any attribute that is a component of a key.

• Foreign Key (FK): An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null.

• Secondary Key: An attribute or combination of attributes used strictly for data retrieval purposes; it does not require functional dependency.

Definition and Nature of Nulls

• A null represents the complete absence of a data value.

• Nulls are never permitted in any component of a primary key.

• A null entry can signify:

  • An unknown attribute value.

  • A known, but currently missing, attribute value.

  • A "not applicable" condition for that specific record.

Database Integrity Rules

• Integrity rules maintain consistency, validity, and accuracy across relational tables.

Entity Integrity

• Requirement: All primary key entries must be entirely unique, and no portion of a primary key may contain a null value.

• Purpose: Ensures each row possesses a known, distinct identity, enabling foreign keys to accurately reference primary key values.

• Example: In an invoicing system, no invoice can feature a duplicate invoice number, nor can an invoice number be null; all invoices are strictly identified by their unique invoice number.

Referential Integrity

• Requirement: A foreign key entry must either be null (provided it is not part of its primary key) or contain a value that matches an existing primary key value in the related table.

• Purpose: Guarantees that every non-null foreign key reference accurately points to a valid primary key record. It prevents invalid links and prohibits deleting a record in a parent table if mandatory matching foreign key values exist in a dependent table.

• Example: A customer record may not yet have an assigned sales representative code (null), but it is impossible to enter a sales representative code that does not exist in the representative table.

Relational Algebra and Operators

• Relational Algebra: A theoretical set of mathematical principles used to manipulate the contents of relational tables through set operations.

Formal Definitions

• Relation: The set of data presented in table format.

• Relvar (Relation Variable): A named container variable that holds relation data, distinct from the actual relation data stored within it.

  • Heading: The structural component of a relvar containing attribute names.

  • Body: The structural component of a relvar containing the relation tuples.

The Eight Relational Operators

• SELECT (RESTRICT): Yields a subset of rows from a single table based on specified conditional criteria.

  

• PROJECT: Yields a subset of specified columns from a table, dropping unselected columns and eliminating duplicate rows.

  

• UNION: Combines all rows from two tables into a new table, automatically eliminating duplicate rows. The participating tables must be union-compatible (possessing identical column counts and compatible attribute domains).

• INTERSECT: Yields only the rows that are common to two union-compatible tables.

• DIFFERENCE: Yields all rows present in the first table that are not found in the second union-compatible table.

• PRODUCT (Cartesian Product): Produces all possible combinations of rows from two tables. If Table A has $m$ rows and Table B has $n$ rows, the product yields $m \times n$ rows.

  

• JOIN: Allows data to be combined intelligently from two or more tables.

  

  • Natural Join: Executes a Cartesian product, performs a SELECT to match common attribute values across tables, and executes a PROJECT to remove duplicate attribute columns.

  • Equijoin: Links tables based on an explicit equality condition comparing specified columns.

  • Theta Join: Links tables using an inequality condition (e.g., $<, >, \le, \ge$).

  • Inner Join: Returns only the matching records present in both joined tables.

  • Outer Join: Retains non-matching rows from one or both tables, filling missing fields with nulls:

  • Left Outer Join: Retains all rows from the first (left) table regardless of matching entries in the second.

  • Right Outer Join: Retains all rows from the second (right) table regardless of matching entries in the first.

• DIVIDE: Operates on two tables with a common attribute set to identify rows in one table that are associated with all values in the second table.

  

Data Dictionary and System Catalog

• Data Dictionary: A structural repository storing metadata (data about data) detailing all user-created tables, attribute definitions, domain restrictions, data types, and structural relationships.

  • Often referred to as "the database designer's database" because it captures all structural design decisions.

• System Catalog: A detailed, system-managed data dictionary that describes every object within the database environment, accessible via standard query tools.

Naming Conflict Prevention

• Homonym: Occurs when the exact same name is used to label different attributes across tables (must be avoided).

• Synonym: Occurs when different names are used to describe the exact same attribute across tables (must be avoided).

Relational Database Relationships

• Relational databases support structural relationships between entities, categorized by degree and cardinality.

One-to-Many ($1:M$) Relationships

• The standard default relationship type in relational database architecture.

• Created by taking the primary key of the "one" side table and placing it as a foreign key inside the "many" side table.

One-to-One ($1:1$) Relationships

• Indicates that one entity instance is associated with exactly one instance of another entity.

• Implemented by placing the primary key of one table into the other table as a foreign key. Used selectively to separate confidential data or optimize storage.

Many-to-Many ($M:N$) Relationships and Linking Tables

• Relational systems cannot directly implement $M:N$ relationships without introducing severe data redundancy and anomalies.

• Solution: Convert the $M:N$ relationship into two $1:M$ relationships by inserting an intermediate composite entity (also termed a linking table or associative entity).

• The linking table contains a composite primary key formed by combining the primary keys of the original tables.

Control of Data Redundancy and Invoicing Systems

• Controlled redundancy is maintained by sharing common attributes (foreign keys) across entities rather than replicating full record details.

• Valid exceptions where redundancy is intentionally introduced or retained include:

  • Improving execution speeds for high-priority transaction handling or reporting.

  • Preserving historical accuracy (e.g., recording product unit prices directly inside invoice line items to safeguard past transaction data against future price changes).

Relational Database Indexes

• Index: An ordered, secondary logical structure used to speed up data retrieval and optimize query access paths without scanning full tables sequentially.

• Index Key: The reference attribute pointing to specific physical data row locations.

• Unique Index: An index configuration where each key entry maps to exactly one physical pointer value.

• Indexes can consist of single attributes or composite multiple attributes.

Dr. Codd's Relational Database Rules

• Formulated by Dr. E. F. Codd to establish structural and functional standards defining a fully relational database system.

• Rule 0 (Rule Zero): To be considered relational, a system must manage database structures exclusively through its relational capabilities.

• Rule 1 (Information): All database information must be logically represented as explicit column values within table rows.

• Rule 2 (Guaranteed Access): Every single data value is guaranteed accessible through a combination of table name, primary key value, and column name.

• Rule 3 (Systematic Treatment of Nulls): Null values must be handled systematically, independent of data types, representing missing or inapplicable data.

• Rule 4 (Dynamic Online Catalog Based on the Relational Model): System metadata must be stored and queried in relational tables using standard database query tools.

• Rule 5 (Comprehensive Data Sublanguage): Systems must support at least one well-defined declarative sublanguage supporting DDL, DML, transaction management, authorization, and integrity rules.

• Rule 6 (View Updating): Any view that is theoretically updatable must be updatable by the system.

• Rule 7 (High-Level Insert, Update, and Delete): Database operations must support set-level manipulation for data modification.

• Rule 8 (Physical Data Independence): Application programs remain unaffected when physical storage structures or physical access methods change.

• Rule 9 (Logical Data Independence): Programs remain unaffected when table structures are modified in ways that preserve original data values.

• Rule 10 (Integrity Independence): Relational integrity constraints must be defined within the relational language and stored in the catalog, not hardcoded into applications.

• Rule 11 (Distribution Independence): Applications function identically regardless of whether data resides locally or across distributed networks.

• Rule 12 (Nonsubversion): Low-level access mechanisms must not bypass system integrity rules.

Questions & Discussion

• Question: What is the fundamental difference between a database and a table?

  • Answer: A table is a logical two-dimensional structure representing a single entity set that stores end-user data. A database is a broader container architecture holding one or more tables along with complete system metadata detailing attributes and entity relationships.

• Question: Which relational algebra operators can be applied to a pair of tables that are not union-compatible?

  • Answer: The PRODUCT, JOIN, and DIVIDE operators can be applied to non-union-compatible table pairs. DIVIDE imposes specific table structural rules, but union-compatibility is not one of them. SELECT (RESTRICT) and PROJECT operate on single tables (or the single resultant table produced by a JOIN).

• Question: Why is the data dictionary described as "the database designer's database"?

  • Answer: Just as a standard database holds operational data concerning real-world user objects, a data dictionary holds specialized data for database designers regarding technical structural decisions. It records the number of tables, table names, attributes, data types, domains, entity relationships, and constraints.