Relational Database Model

Overview

The Relational Database Model, introduced by Dr. E.F. Codd in 1970, is the foundation of modern database systems. It organizes data into tables (relations) consisting of rows (tuples) and columns (attributes), establishing relationships between tables through keys.

The relational model provides a mathematical foundation for database management and ensures data integrity through constraints and relationships.

Core Concepts

1. Relation (Table)

A relation is a two-dimensional table that contains data. It's the fundamental structure in the relational model.

┌─────────────┬──────────────┬──────────────┐
│ StudentID   │ StudentName  │ Department   │
├─────────────┼──────────────┼──────────────┤
│ 101         │ John Smith   │ Computer Sci │
│ 102         │ Jane Doe     │ Mathematics  │
│ 103         │ Bob Johnson  │ Computer Sci │
└─────────────┴──────────────┴──────────────┘

2. Tuple (Row)

A tuple is a single row in a relation, representing one instance of an entity. Each tuple contains values for all attributes.

3. Attribute (Column)

An attribute is a column in a relation, representing a property of the entity. Each attribute has a domain (set of valid values).

4. Domain

A domain is the set of all possible values that an attribute can take. For example, the domain of "Age" might be all positive integers.

5. Degree and Cardinality

• Degree: The number of attributes (columns) in a relation
• Cardinality: The number of tuples (rows) in a relation

Properties of Relations

Relations in the relational model have specific properties:

• No duplicate rows: Each tuple is unique
• Order doesn't matter: Rows can be in any order
• Atomic values: Each cell contains a single, indivisible value
• All values are from the same domain: Column values must be of the same type
• No multivalued attributes: Each attribute has only one value per tuple

Keys

Keys are crucial for establishing relationships and ensuring data integrity:

Primary Key

A primary key uniquely identifies each tuple in a relation. It must be unique and cannot be NULL.

Students Table:
┌─────────────┬──────────────┬──────────────┐
│ StudentID   │ StudentName  │ Department   │
│ (Primary    │              │              │
│  Key)       │              │              │
├─────────────┼──────────────┼──────────────┤
│ 101         │ John Smith   │ Computer Sci │
│ 102         │ Jane Doe     │ Mathematics  │
└─────────────┴──────────────┴──────────────┘

Foreign Key

A foreign key is an attribute (or set of attributes) in one relation that references the primary key of another relation, establishing relationships between tables.

Students Table (Primary Key: StudentID)
┌─────────────┬──────────────┐
│ StudentID   │ StudentName  │
├─────────────┼──────────────┤
│ 101         │ John Smith   │
│ 102         │ Jane Doe     │
└─────────────┴──────────────┘

Enrollments Table (Foreign Key: StudentID)
┌─────────────┬──────────────┬──────────────┐
│ EnrollmentID│ StudentID    │ CourseID     │
│             │ (Foreign Key)│ (Foreign Key)│
├─────────────┼──────────────┼──────────────┤
│ 1           │ 101          │ CS101        │
│ 2           │ 102          │ MATH201      │
└─────────────┴──────────────┴──────────────┘

Candidate Key

A candidate key is any attribute or combination of attributes that can uniquely identify a tuple. One candidate key is chosen as the primary key.

Superkey

A superkey is any set of attributes that uniquely identifies tuples. It may contain more attributes than necessary.

Integrity Rules

1. Entity Integrity

No attribute that is part of the primary key can be NULL. This ensures every tuple can be uniquely identified.

2. Referential Integrity

A foreign key value must either match a primary key value in the referenced relation or be NULL. This ensures relationships between tables are valid.

Valid:
Enrollments.StudentID = 101  → Must exist in Students.StudentID
Enrollments.StudentID = NULL → Also allowed

Invalid:
Enrollments.StudentID = 999  → Does NOT exist in Students.StudentID
                              → Violates referential integrity!

Advantages of Relational Model

• Simple structure: Easy to understand and use
• Data independence: Logical structure independent of physical storage
• Powerful querying: SQL provides flexible data manipulation
• Data integrity: Built-in constraints ensure data quality
• Normalization: Reduces data redundancy
• Standardization: Widely accepted and standardized

Relational Algebra

Relational algebra is the theoretical foundation of SQL operations. Core operations include:

• SELECT (σ): Selects tuples that satisfy a condition
• PROJECT (π): Selects specific attributes
• JOIN (⨝): Combines related tuples from two relations
• UNION (∪): Combines tuples from two relations
• DIFFERENCE (-): Returns tuples in first relation but not in second
• PRODUCT (×): Cartesian product of two relations

Mapping ER Model to Relational Model

When converting an ER diagram to relational tables:

1. Entity → Table
   - Entity name becomes table name
   - Attributes become columns
   - Primary key identified

2. Relationship → Foreign Key
   - 1:1 relationship: Foreign key in either table
   - 1:M relationship: Foreign key in "many" table
   - M:M relationship: Create junction table

3. Attributes → Columns
   - Simple attributes → Single column
   - Composite attributes → Multiple columns
   - Multivalued attributes → Separate table

Relational Database Management System (RDBMS)

An RDBMS is software that manages relational databases. Popular RDBMS include:

• MySQL: Open-source, widely used
• PostgreSQL: Advanced open-source RDBMS
• Oracle Database: Enterprise-level database
• Microsoft SQL Server: Windows-based RDBMS
• SQLite: Lightweight, embedded database

Next Steps

Learn about representing relationships visually with ERD & Crow's Foot Notation, or explore database design principles in Normalization.