Database Systems: Relational Database Model Study Guide

Key Terminology in Database Systems

  • 3 Vs: A term used to describe the characteristics of Big Data: Volume, Velocity, and Variety.
  • American National Standards Institute (ANSI): The organization responsible for defining standardized protocols and data modeling standards.
  • Attribute: A characteristic or quality of an entity.
  • Big Data: Large and complex datasets that require non-traditional data processing applications.
  • Business Rule: A brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization.
  • Chen Notation: A specific style of entity relationship modeling notation used in ERDs.
  • Class: A collection of similar objects with shared structure and behavior in object-oriented modeling.
  • Class Diagram Notation: A graphical representation in the Unified Modeling Language (UML) used to describe classes and their relationships.
  • Class Hierarchy: The organization of classes in a tree-like structure where child classes inherit from parent classes.
  • Client Node: A node in a distributed system that requests data or services.
  • Conceptual Model: A high-level description of the data requirements of an organization, independent of any DBMS.
  • Conceptual Schema: The representation of the conceptual model, usually in the form of an ERD.
  • Connectivity: The type of relationship between entities (e.g., 1:1, 1:M, M:N).
  • Constraint: A restriction placed on data to ensure integrity.
  • Crow’s Foot Notation: A popular notation style for entity relationship diagrams.
  • Data Definition Language (DDL): A language used by a DBMS to define the database schema and structure (e.g., CREATE, ALTER, DROP).
  • Data Manipulation Language (DML): A language used to manage and manipulate data within the database (e.g., SELECT, INSERT, UPDATE, DELETE).
  • Data Model: A relatively simple representation of complex real-world data structures.
  • Data Modeling: The process of creating a specific data model for a determined problem domain.
  • Data Node: In a Hadoop environment, a node that stores actual data blocks.
  • Entity: A person, place, thing, or event about which data is collected and stored.
  • Entity Instance/Occurrence: A specific member of an entity set (a single row in a table).
  • Entity Relationship Diagram (ERD): A graphical tool used to represent the database's main components: entities, attributes, and relationships.
  • Entity Relationship (ER) Model (ERM): A process of database design that uses ERDs to map the database structure.
  • Entity Set: A collection of like entities.
  • Extended Relational Data Model (ERDM): A model that includes the features of the relational model plus support for complex data types and object-oriented features.
  • Extensible Markup Language (XML): A metalanguage used to represent and carry data in a structured way.
  • External Model/Schema: The specific user's view of the database environment.
  • Hadoop: An open-source framework for distributed storage and processing of very large datasets.
  • Hadoop Distributed File System (HDFS): The primary storage system used by Hadoop.
  • Hardware Independence: The ability of a database to function regardless of the physical hardware used.
  • Hierarchical Model: An early database model where data is organized in a top-down, tree-like structure.
  • Inheritance: The ability of an object to inherit data structure and methods from a parent class.
  • Internal Model/Schema: The representation of the database as seen by the DBMS.
  • Internet of Things (IoT): A network of physical objects embedded with sensors and software.
  • Logical Design: The process of mapping the conceptual model into a specific DBMS structure.
  • Logical Independence: The ability to change the internal model without affecting the external model.
  • Many-to-Many (M:N or ..) Relationship: A relationship where many instances of one entity can relate to many instances of another.
  • MapReduce: A programming model for processing large datasets in parallel across a Hadoop cluster.
  • Method: A program code within an object-oriented environment that defines actions for an object.
  • Name Node: The master node in HDFS that manages metadata.
  • Network Model: An early database model that allows records to have multiple parents.
  • NoSQL: A category of non-relational database management systems designed for Big Data.
  • Object: An abstract entity that includes data (attributes) and behavior (methods).
  • Object-Oriented Data Model (OODM): A model where both data and their relationships are contained in a single structure known as an object.
  • Object-Oriented Database Management System (OODBMS): A DBMS that supports the OODM.
  • Object/Relational Database Management System (O/R DBMS): A relational DBMS with object-oriented capabilities.
  • One-to-Many (1:M or 1..*) Relationship: A relationship where one instance of an entity relates to many instances of another.
  • One-to-One (1:1 or 1..1) Relationship: A relationship where one instance of an entity relates to exactly one instance of another.
  • Physical Independence: The ability to change physical storage devices without affecting the logical design.
  • Physical Model: The lowest level of data abstraction, describing how data is saved on storage media.
  • Relation: A mathematical construct represented as a table in the relational model.
  • Relational Database Management System (RDBMS): Software that manages a relational database.
  • Relational Diagram: A graphical representation of the relational database's entities and their relationships.
  • Relational Model: A data model based on the concept of relations, developed by E. F. Codd in 1970.
  • Relationship: An association between entities.
  • Schema: A logical group of database objects (tables, indexes, views).
  • Segment: A term used in hierarchical models to refer to a record type.
  • Semantic Data Model: A model that attempts to incorporate more meaning into the data representation.
  • Software Independence: The ability of a database to function regardless of the specific software or operating system.

Chapter 3 Learning Objectives: The Relational Database Model

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

A Logical View of Data

  • Overcoming Constraints: In contrast to hierarchical and network models, which involve complex physical structures, the relational model allows designers to focus on logical representations and relationships.
  • Automotive Analogy: The relational database is compared to an automatic transmission: it relieves the designer of manipulating "clutch pedals and gearshifts" (physical storage details) by enabling a purely logical view of data.
  • Conceptual Similarity to Files: While fundamentally different due to structural and data independence, a table resembles a simple file from a conceptual standpoint. This makes the model easier to understand than its predecessors.
  • Logical Simplicity: Simple methodologies lead to effective database design.

Tables and Their Characteristics

  • Formal Translation: A relation is a mathematical construct. End users generally refer to this construct as a table.
  • Table Definition: A two-dimensional structure composed of rows and columns.
  • Persistent Representation: Tables serve as a persistent representation of a logical relation, meaning contents are permanently saved.
  • Terminology Synonyms: The terms entity set and table are often used interchangeably.
Characteristics of a Relational Table (Table 3.1 Summarized)
  1. Dimension: Perceived as a two-dimensional structure of rows/columns.
  2. Tuple: Each row represents a single entity occurrence within the entity set.
  3. Attribute: Each column represents an attribute with a distinct name.
  4. Value: Each intersection of a row and column contains a single data value.
  5. Data Format: All values in a specific column must conform to the same data format.
  6. Attribute Domain: Each column has a specific range of permissible values.
  7. Order: The order of rows and columns is immaterial to the DBMS.
  8. Unique Identifier: Each table must have an attribute (or combination) that uniquely identifies each row.
Logical vs. Physical Terminology
  • Database professionals often use file system terms interchangeably with database terms, though they represent different concepts:
    • Row (Logical) ≈\approx Record (Physical).
    • Column (Logical) ≈\approx Field (Physical).
    • Table (Logical) ≈\approx File (Physical).
Data Types
  • Numeric: Used for mathematical manipulation (e.g., STU_HRS, STU_GPA).
  • Character (Text/String): Contains any character/symbol not intended for math (e.g., STU_CLASS, STU_PHONE).
  • Date: Stored in a special format known as the Julian date format (e.g., STU_DOB).
  • Logical: Limited to True/False or Yes/No values (e.g., STU_TRANSFER).

Keys and Dependencies

  • Purpose of Keys: Keys ensure each row is uniquely identifiable and establish relationships between tables while maintaining data integrity.
  • Definition of a Key: One or more attributes that determine other attributes.
Determination
  • Definition: The state in which knowing the value of one attribute makes it possible to determine the value of another.
  • Formulaic Example: revenue−cost=profit\text{revenue} - \text{cost} = \text{profit}. If you know any two values, you can determine the third.
  • Database Determination: Usually based on attribute relationships rather than math. If STU_NUM is known, STU_LNAME can be determined.
Functional Dependence
  • Notation: A→BA \rightarrow B (A functionally determines B).
  • Determinant: The attribute whose value determines another (the key).
  • Dependent: The attribute whose value is determined by the determinant.
  • Full Functional Dependence: A condition where the entire collection of attributes in a determinant is necessary for the relationship. If a subset of the determinant can determine the dependent, it is not a full functional dependency.
Types of Keys (Table 3.3 Summarized)
  • Composite Key: A key composed of more than one attribute.
  • Key Attribute: An attribute that is part of a key.
  • Superkey: Any attribute or combination of attributes that uniquely identifies each row (functionally determines every attribute in the row).
  • Candidate Key: A minimal superkey (a superkey without any unnecessary attributes). These are the "candidates" for the primary key.
  • Primary Key (PK): The candidate key chosen to uniquely identify all other attribute values in a given row. Requirements:
    1. All values must be unique.
    2. No part of the PK can be null.
  • Secondary Key: Used strictly for data retrieval purposes. It does not require functional dependency or uniqueness (e.g., searching by Last Name and Phone Number).
  • Foreign Key (FK): An attribute in one table whose values must match the primary key in another table (or be null).

Integrity Rules

Entity Integrity
  • Requirement: All primary key entries must be unique; no part of a primary key may be null.
  • Purpose: Ensures each row has a known, unique identity and can be referenced by foreign keys.
Referential Integrity
  • Requirement: A foreign key must either contain a null entry (if not part of its own table's PK) or match a primary key value in the related table.
  • Purpose: Ensures all references to other tables are valid; prevents "orphaned" records.
  • Delete Constraint: Referential integrity makes it impossible to delete a row in one table whose primary key is being used as a foreign key by another table.
Definitions Regarding Nulls
  • Null: The absence of any data value (not zero or a blank space).
  • Problems with Nulls: Can create logical problems during table links and issues with aggregate functions (COUNT, SUM, AVERAGE).
  • Meanings of Null: Can represent an unknown value, a known but missing value, or a "not applicable" condition.
  • Flags/Dummy Variables: Used to avoid nulls. Example: Using a code of −99-99 to indicate "None" and creating a corresponding row in the related table with that value.

Relational Algebra and Operators

  • Relational Algebra: A theoretical way of manipulating table contents using relational operators.
  • Relational Completeness: A DBMS is considered minimally relational if it supports SELECT, PROJECT, and JOIN.
  • Relvar (Relation Variable): The container/structure that holds table data. It has two parts:
    1. Heading: Contains attribute names.
    2. Body: Contains the relation (the data).
  • Notation: An unspecified relation is lowercase ("r") while the relvar is uppercase ("R"), denoted as r(R)r(R).
Core Relational Operators
  • SELECT (RESTRICT):
    • Type: Unary operator.
    • Function: Yields a horizontal subset of a table (rows) that satisfy a condition.
    • Notation: Denoted by the Greek letter sigma (σ\sigma).
  • PROJECT:
    • Type: Unary operator.
    • Function: Yields a vertical subset of a table (selected columns) in the requested order.
    • Notation: Denoted by the Greek letter pi (π\pi).
  • UNION:
    • Function: Combines all rows from two tables, excluding duplicates.
    • Requirement: Tables must be union-compatible (same number of columns and compatible domains).
    • Notation: Denoted by ∪\cup.
  • INTERSECT:
    • Function: Yields only the rows that appear in both tables.
    • Requirement: Tables must be union-compatible.
    • Notation: Denoted by ∩\cap.