Database Concepts L3
Learning Objectives of the Relational Data Model
The relational database model provides a logical view of data, abstracting it from physical storage complexities.
The model's basic component is the relation.
Relations are logical constructs comprised of rows, known as tuples, and columns, known as attributes.
Relations are implemented physically as tables within a Relational Database Management System (RDBMS).
The study includes relational database operators, the function of the data dictionary, and the system catalog.
It addresses how data redundancy is managed and the importance of indexing for data retrieval efficiency.
Foundations of the Relational Model
The foundation of the relational model is a mathematical concept known as a relation.
To manage the complexity of abstract mathematical theory, a relation is viewed as a two-dimensional structure composed of intersecting rows and columns.
Each row in a relation is formally called a tuple.
Each column in a relation represents an attribute.
Database Example: Ch02 InsureCo.
Table name: AGENT (contains attributes like , , , , , and ).
Table name: CUSTOMER (contains attributes like , , , , , , , , , and ).
These tables are linked through the common attribute .
Relational Database Management Systems (RDBMS)
The RDBMS performs basic functions similar to hierarchical and network DBMS systems.
It makes the relational data model much easier to understand and implemented logically.
One of its primary roles is to hide the inherent complexities of the relational model (and physical storage) from the end-user.
The Logical View of Data
The relational model enables programmers and users to view data logically rather than physically.
A Table (Relation) has advantages of structural and data independence.
From a conceptual point of view, a table resembles a file but is part of a more sophisticated structure.
Logical simplicity yields simple and effective database design methodologies.
Relationships are facilitated through logical constructs based on these relations.
Mathematical Definition of a Relation
The concept is modeled on a rigorous mathematical construct.
Formal Mathematical Definition: Given a number of sets (which are not necessarily distinct), is a relation on these sets if it is a set of tuples, each of which has its first element from , its second element from , and so on until .
Concrete Example:
Assume .
Set 1: student's last names .
Set 2: department codes .
The relation defined over these sets could be: .
Fundamental Properties of a Relation
A table is perceived as a two-dimensional structure composed of rows and columns.
Each table row (tuple) represents a single entity occurrence within the entity set and must be distinct; duplicate rows are strictly prohibited.
Each table column represents an attribute and must possess a distinct name.
Each cell (the intersection of a row and column) must contain only an atomic value, meaning a single data value. Multiple values (multivalued attributes) are not allowed within a cell.
All values in a column must conform to the same data format (e.g., if an attribute is designated as an integer, every value in that column must be an integer).
Each column has a specific range of values known as the attribute domain.
The order of rows and columns is irrelevant to the DBMS.
Each table must have an attribute (or a combination of attributes) that uniquely identifies each row.
Attributes, Domains, and Schema
Attributes: Named columns within a relational table drawing values from a domain.
Domain: The range of permitted values for an attribute. These must be atomic and non-divisible into further components.
Relational Schema: A textual representation of database tables. It is written as the table name followed by the list of attributes in parentheses.
Formal definition: , where are the set of attributes belonging to relation .
Example: .
Degree and Cardinality
Degree: The number of attributes (columns) in a relation.
Cardinality: The number of tuples (rows) in a relation.
The total number of attribute values in a relation is the mathematical product of its degree and its cardinality: .
A relation with columns and rows is said to be of degree and cardinality .
Relational Database Keys
Key Concept: Consists of one or more attributes that determine other attributes based on the principle of determination. If the value of attribute is known, the value of attribute can be determined.
Primary Key (PK): An attribute (or combination) that uniquely identifies any given row/entity.
Composite Key: A key composed of more than one attribute.
Key Attribute: Any attribute that forms part of a key.
Superkey: Any key (attribute or combination) that uniquely identifies each row.
Candidate Key: A superkey that contains no redundancies (a minimal superkey).
Secondary Key: A key used strictly for data retrieval purposes (e.g., searching by phone number instead of a primary ID).
Foreign Key (FK): An attribute whose values match the Primary Key values in a related table, allowing for the linking of tables.
Determination Example: Student Classification
Hours Completed < : Classification =
Hours Completed to : Classification =
Hours Completed to : Classification =
Hours Completed or more: Classification =
Handling Null Values
A Null represents the absence of a data entry.
Nulls are NOT permitted in a primary key (Entity Integrity).
Nulls should generally be avoided in other attributes.
A Null can represent:
An unknown attribute value.
A known but missing attribute value.
A "not applicable" condition.
Problems with Nulls: They can cause errors in aggregate functions like , , and , and create logical issues when linking relational tables.
Redundancy Management
Controlled Redundancy: This makes the relational database functional. Tables share common attributes (Foreign Keys) to enable linking.
Necessary Duplication: Multiple occurrences of values are not considered redundant if they are required to establish relationships Between tables.
Unnecessary Redundancy: Exists only when there is duplicate attribute information that does not serve to link tables, leading to data anomalies (insertion, deletion, and update anomalies).
Integrity Rules
Entity Integrity:
Requirement: All primary key entries must be unique, and no part of a primary key may be null.
Purpose: Ensures every row has a unique identity and allows foreign keys to reference them accurately.
Example: No invoice can have a duplicate number or a null number.
Referential Integrity:
Requirement: A foreign key may have a null entry (if not part of its own table's PK); if not null, the FK value must match an existing primary key value in the related table.
Purpose: Prevents invalid entries and ensures that relationships between tables remain consistent. It allows for the controlled deletion of records.
Example: A customer might not have an assigned sales rep (null allowed), but it is impossible to assign a sales rep number that does not exist.
Domain Integrity:
Requirement: Column values must fall within the acceptable range defined by the domain.
Purpose: Includes data type, length, null acceptance, range constraints, and default values.
Example: It is impossible to have negative prices (Price ).
The Data Dictionary and System Catalog
Data Dictionary:
Provides a detailed accounting of all tables in the user/designer-created database.
Contains metadata (data about data), including attribute names, types (CHAR, VARCHAR, etc.), formats, domains, and whether the attribute is required or acts as a PK/FK.
Recorded as the "database designer's database" reflecting design decisions.
System Catalog:
A detailed system-level data dictionary describing all objects within the database.
Can be queried like any other table.
Relationships within the Relational Database
1:* (One-to-Many): The ideal relational modeling norm. Examples: One Painter paints many Paintings; one Agent serves many Customers.
1:1 (One-to-One): Rare and should be used sparingly. Often suggests that two entities should be in the same table. Example: A Department is chaired by one Lecturer.
: (Many-to-Many): Cannot be directly implemented in a relational model. It must be converted into two relationships.
Conversion involves a bridge (composite) entity/table.
Example: Many Students take many Classes. This is resolved by an ENROLL table linking and .
Indexing
An arrangement used to logically access rows in a table without scanning the entire structure.
Index Key: The pointer or reference point that identifies the data location.
Unique Index: An index where the index key can have only one pointer value (row) associated with it.
Indexes are associated with a single table to speed up data retrieval.
Relational Algebra and Operators
Relational algebra is a set of mathematical principles for manipulating table contents. It features the property of "closure," where operators applied to relations produce new relations.
Unary Operators:
Select (Restrict): Yields a horizontal subset of a table (specific rows based on criteria). Example: Select price < $2.00.
Project: Yields a vertical subset of a table (specific columns). Example: Project and .
Binary/Set Operators:
Union: Combines all rows from two union-compatible tables, excluding duplicates.
Union-compatible: Tables must have the same number of columns and compatible domains.
Intersect: Yields only rows appearing in both union-compatible tables.
Difference: Yields rows in the first table that are not found in the second (tables must be union-compatible).
Product: Yields all possible pairs of rows from two tables ().
Join: Combines information from two or more tables intelligently.
Divide: Uses a 2-column table as a dividend and a 1-column table as a divisor to find associated values.
Types of Joins
Natural Join: Links tables by selecting rows with common values in joint columns.
Equijoin: Links tables based on an equality condition comparing specified columns.
Theta Join: An extension of the natural join using a theta subscript.
Inner Join: Returns only matched records from the joined tables.
Outer Join: Retains matched pairs and keeps unmatched values from the other table as null.
Left Outer Join: Yields all rows from the first (left) table and matching rows from the second.
Right Outer Join: Yields all rows from the second (right) table and matching rows from the first.
Questions & Discussion
Quick Concept Check Questions:
How do you identify primary, foreign, composite, and secondary keys in a given dataset?
How would you explain the three integrity rules (Entity, Referential, Domain) using specific tables?
What is the specific effect of referential integrity violations?
Identify which relational operator is represented by: "Display all products with price below R100" (Select), "Display name and surname" (Project), "Combine lists in Table1 and Table2" (Union), "Customers in both" (Intersect), "In Table1 but not Table2" (Difference).
Revision Questions:
Describe relational table characteristics.
Define secondary keys.
Why are entity and referential integrity important?
What are the requirements for union-compatibility?
What is the purpose of the system catalog?