TOPIC 3: The Relational Database Model

Database Systems: Design, Implementation, and Management

Chapter Objectives

By the end of this chapter, you should be able to:

  1. Describe the relational database model’s logical structure
  2. Identify the relational model’s basic components and explain the structure, contents, and characteristics of a relational table
  3. Use relational database operators to manipulate relational table contents
  4. Explain the purpose and components of the data dictionary and system catalog
  5. Identify appropriate entities and then the relationships among the entities in the relational database model
  6. Describe how data redundancy is handled in the relational database model
  7. Explain the purpose of indexing in a relational database

A Logical View of Data

  • The relational data model allows the designer to focus on the logical representation of the data and its relationships, rather than on the physical storage details.
  • Benefits of this approach:
    • Simpler design
    • Easier understanding
    • Better data management

Characteristics of a Relational Table

  • Definition: A relational table is the basis of the relational model, storing data in a structured format of rows and columns.

Keys in Relational Databases

  • A key consists of one or more attributes that determine other attributes.
  • Importance of keys:
    • Ensure that each row in a table is uniquely identifiable
    • Establish relationships among tables
    • Ensure the integrity of the data
Dependencies
  • The role of a key is based on the concept of determination, which is defined as
    • Knowing the value of one attribute helps to determine the value of another.
    • Example: revenue – cost = profit
  • Functional Dependence: Means that the value of one or more attributes determines the value of one or more other attributes.
    • The attribute whose value determines another is called the determinant or the key.
    • The attribute whose value is determined by the other is called the dependent.
    • Full Functional Dependence: Refers to functional dependencies where the entire collection of attributes in the determinant is necessary for the relationship between those attributes.

Types of Keys

  1. Composite Key: A key that is composed of more than one attribute.
  2. Key Attribute: An attribute that is part of a key.
  3. Superkey: A key that can uniquely identify any row in the table.
  4. Candidate Key: A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey.
  5. Entity Integrity: The condition in which each row in the table has its own known, unique identity.
  6. Null: The absence of any data value, not allowed in any part of a primary key.
Null Values Interpretation
  • A null can represent multiple scenarios:
    • An unknown attribute value
    • A known, but missing, attribute value
    • A “not applicable” condition
  • Foreign Key (FK): A primary key of one table that has been placed in another table.
  • Referential Integrity: A condition ensuring that a dependent table’s foreign key entry must have either a null entry or a matching entry in the primary key of the related table.
  • Secondary Key: A key used strictly for data retrieval purposes without requiring a functional dependency.

Integrity Rules

1. Entity Integrity
  • Description: All primary key entries are unique, and no part of a primary key may be null.
  • Purpose: Each row will have a known, unique identity, allowing foreign key values to reference primary key values correctly.
  • Example: All invoices must be uniquely identified by their invoice number.
2. Referential Integrity
  • Description: A foreign key may have either a null entry or an entry that matches a primary key value in a related table.
  • Purpose: To ensure every foreign key reference is valid.
  • Example: A customer may not have an assigned sales representative, but invalid sales representative numbers cannot exist.

Formal Definitions and Terminology

  • Relation: The data that you see in your tables.
  • Relation Variable (relvar): A variable that holds a relation; it contains two parts:
    1. Heading: Contains the names of the attributes.
    2. Body: Contains the relation data.

Relational Algebra

  • Definition: The theoretical framework for manipulating table contents using relational operators.
  • Primary relational set operators:
    1. SELECT (or RESTRICT): Selects a subset of rows.
    2. PROJECT: Selects a subset of columns.
    3. JOIN: Combines rows from two or more tables.
    4. INTERSECT: Yields only the rows common to two union-compatible tables.
    5. UNION: Merges two tables into a new table while dropping duplicate rows.
    6. DIFFERENCE: Yields rows from one table not found in another.
    7. PRODUCT: Yields all possible pairs of rows from two tables (Cartesian product).
    8. DIVIDE: Answers questions about one set of data being associated with all values of data in another set.

Knowledge Check Activity 3-1

  • Question: What is the difference between a database and a table?
  • Answer:
    • A table is a logical structure that represents an entity set and is only one of the components in a database — storing end-user data.
    • A database is a structure that houses one or more tables and metadata, which includes attribute characteristics and the relationships between entity sets.

The Data Dictionary and the System Catalog

  • Data Dictionary: Provides detailed descriptions of all tables in the database, recording design decisions about tables and their structures — sometimes referred to as “the database designer’s database.”
  • System Catalog: A detailed system data dictionary describing all objects within the database.
  • Need to avoid homonyms and synonyms:
    • Homonym: Same name used for different attributes.
    • Synonym: Different names describing the same attribute.

Relationships within the Relational Database

  • One-to-Many (1:M): Norm for relational databases.
  • One-to-One (1:1): One entity can be related to only one other entity and vice versa.
  • Many-to-Many (M:N):
    • Implemented by creating a new entity in 1:M relationships.
    • Not supported directly in relational environments, managed through composite entities (bridge or associative entities).

Converting Many-to-Many Relationships

  • Linking Table: Involves creating a separate entity (like ENROLL) that links two original tables to convert an M:N relationship into two 1:M relationships.

Data Redundancy Revisited

  • The chapter discusses how data redundancy is handled effectively within the confines of the relational database model, minimizing the potential for duplicate data entry and ensuring relationships between data points remain consistent and adherent to integrity rules.

Indexes

  • Purpose of Indexing: Designed to improve the speed of data retrieval operations on a database table. Indexes allow the database management system to find data without scanning the entire table.

Knowledge Check Activity 3-3

  • Question: Explain why the data dictionary is sometimes called "the database designer's database."
  • Answer: Just as databases store data relevant to users, the data dictionary holds information pertinent to the database designer, documenting crucial decisions regarding the database structure, including the number of created tables, their attributes, relationships, data types, and enforced domains of attributes.