Relational Model: Tables, Keys, Integrity & Normal Forms

TAKEAWAYS:

• The relational model represents data as tables (relations) with rows (tuples) and columns (attributes), governed by keys (primary, candidate, foreign) and integrity constraints.
• Relational algebra (select, project, join, etc.) provides a formal way to query and manipulate data without relying on procedural code.
• Normalization (1NF–BCNF) systematically eliminates redundancy by decomposing tables into well-structured forms, improving efficiency and consistency.
• Schema vs. instance: A schema defines the structure (tables, columns, constraints), while an instance is a snapshot of actual data at a given time.
• Real-world systems (e.g., eSewa, Khalti, NEPSE) use relational databases to manage transactions, user accounts, and financial records with ACID properties.
• Foreign keys enforce referential integrity, ensuring relationships between tables (e.g., linking a customer to their orders in Daraz).

Core Concepts of the Relational Model

erDiagram
    Student { string student_id PK "Unique ID" string name string email string dept_id FK }
    Department { string dept_id PK "Department ID" string dept_name string location }
    Enrollment { string student_id FK string course_id FK string grade }
    Course { string course_id PK "Course ID" string course_name string credits }
    Student ||--o{ Enrollment : enrolls_in
    Department ||--o{ Student : belongs_to
    Course ||--o{ Enrollment : offers
    Enrollment { string student_id, string course_id } ||--|{ Student : "Student ID"
    Enrollment { string student_id, string course_id } ||--|{ Course : "Course ID"

1. Tables as Relations

In the relational model, data is stored in tables (called relations), where:

• Each row (tuple) represents a single entity (e.g., a student, order, or account).
• Each column (attribute) holds a specific field (e.g., student_id, name, email).
• A relation has a name (e.g., Student, Course) and a degree (number of attributes) and cardinality (number of tuples).

Why tables? Tables are intuitive, easy to understand, and mathematically rigorous. They allow us to define relationships between entities clearly.

2. Keys: The Backbone of Relationships

Keys ensure uniqueness and enforce relationships between tables.

3. Integrity Constraints

These rules ensure data accuracy and consistency.

Relational Algebra: The Formal Query Language

Relational algebra is a procedural query language (unlike SQL, which is declarative). It uses operations to manipulate relations. Here are the key operations:

Basic Operations

Join Operations

Joins combine tuples from two relations based on a common attribute.

WORKED EXAMPLE: Joining Tables Suppose we have two tables:

• Employee(person_name, company_name, salary)
• Company(company_name, city)

Question: Find all employees and their company’s city. Relational Algebra Expression:

π_{person_name, city}(Employee ⋈ Company)

Explanation:

1. Join (⋈): Combine Employee and Company where company_name matches.
2. Project (π): Select only person_name and city from the result.

MERMAID DIAGRAM:

erDiagram
    Employee ||--o{ Company : "works_at"
    Employee {
        string person_name PK
        string company_name FK
        int salary
    }
    Company {
        string company_name PK
        string city
    }

This shows the relationship between Employee and Company via company_name (foreign key).

In the Real World

The relational model powers every major system students interact with daily. Here’s how:

1. eSewa (Nepal Government)

   • Tables Used: User, Transaction, Service, Payment.
   • Relational Idea: Foreign keys link a Transaction to a User (via user_id) and to a Service (via service_id). Normalization ensures no duplicate service records.
   • Example: When you pay a traffic fine, eSewa’s database:
     • Checks if your user_id exists (entity integrity).
     • Validates the service_id (referential integrity).
     • Updates the Transaction table with your payment.

2. Khalti (Digital Payments)

   • Tables Used: Customer, Account, Transaction, Merchant.
   • Relational Idea: Foreign keys enforce that every Transaction must reference a valid Customer and Merchant. Normalization (3NF) prevents redundant merchant details.
   • Example: When you transfer money to a friend:
     • Khalti’s system checks your account_id (PK) and your friend’s account_id (FK).
     • The Transaction table logs the transfer with timestamps and amounts.

3. NEPSE (Nepal Stock Exchange)

   • Tables Used: Company, Shareholder, Trade, Portfolio.
   • Relational Idea: The Trade table has foreign keys to Company (for stock symbol) and Shareholder (for buyer/seller). Normalization ensures no duplicate company profiles.
   • Example: When you buy shares of Ncell:
     • NEPSE’s database checks if Ncell exists in Company (PK).
     • Links your shareholder_id (FK) to the Trade record.

4. Daraz (E-Commerce)

   • Tables Used: Customer, Order, Product, OrderItem.
   • Relational Idea: An Order can have multiple OrderItem records (1:N relationship). Foreign keys ensure every OrderItem references a valid Order and Product.
   • Example: When you place an order:
     • Daraz’s system creates an Order record (PK: order_id).
     • Each product in your cart becomes an OrderItem (FK: order_id).
     • The Product table ensures stock levels are updated correctly.

Normalization: Eliminating Redundancy

Normalization is the process of decomposing tables to reduce redundancy and dependency issues. It follows a hierarchy of normal forms (NF).

Why Normalize?

• Redundancy: Duplicate data wastes storage and causes update anomalies.
• Anomalies:
  • Insertion Anomaly: Cannot insert data without violating constraints.
  • Update Anomaly: Updating one record requires multiple changes.
  • Deletion Anomaly: Deleting data unintentionally removes other data.

Normal Forms

WORKED EXAMPLE: Normalizing a Poorly Designed Table Unnormalized Table: StudentCourse

Problems:

1. Redundancy: instructor and dept_name repeat for the same course.
2. Update Anomaly: Changing Prof. X’s name requires updating multiple rows.
3. Insertion Anomaly: Cannot add a new course without a student enrolled.

Normalized Design (3NF):

1. Student (student_id PK, student_name)
2. Course (course_id PK, course_name, instructor, dept_name)
3. Enrollment (student_id FK, course_id FK, grade)

MERMAID DIAGRAM:

erDiagram
    Student ||--o{ Enrollment : "takes"
    Course ||--o{ Enrollment : "offers"
    Student {
        int student_id PK
        string student_name
    }
    Course {
        int course_id PK
        string course_name
        string instructor
        string dept_name
    }
    Enrollment {
        int student_id PK, FK
        int course_id PK, FK
        string grade
    }

Schema vs. Instance: The Big Picture

stateDiagram-v2
    [*] --> Schema
    Schema --> Instance: "Defines structure (tables, keys, constraints)"
    Instance --> [*]: "Snapshot of actual data"
    Schema --> Schema: "Can evolve (ALTER TABLE)"
    Instance --> Instance: "Changes with CRUD operations"

Advantages and Disadvantages of the Relational Model

Exam Tip: How to Score Full Marks

1. Define Clearly:

   • For relational model, start with: "The relational model represents data as a collection of tables (relations) where each table has rows (tuples) and columns (attributes), governed by keys and integrity constraints."
   • For normalization, explain the purpose (eliminate redundancy) and steps (1NF → 3NF → BCNF) with examples.

2. Draw ER Diagrams:

   • Always include an ER diagram for relationship questions. Label:
     • Entities (rectangles).
     • Attributes (ovals).
     • Primary keys (underlined).
     • Relationships (diamonds or lines with cardinality).

3. Relational Algebra:

   • Break down complex queries into steps (e.g., first join, then project).
   • Use symbols (σ, π, ⋈) to show formal expressions.
   • Example: For "Find all employees in Kathmandu", write:

     σ_{city = 'Kathmandu'}(Employee)
     

4. Normalization:

   • Identify anomalies in the given table before proposing fixes.
   • Show the decomposition step-by-step (e.g., 1NF → 2NF → 3NF).
   • Justify each step (e.g., "This eliminates partial dependency on student_id").

5. Real-World Links:

   • Connect concepts to eSewa, Khalti, or NEPSE in your answers. For example: "In Khalti’s database, the Transaction table has a foreign key to Customer to ensure referential integrity, similar to how we use foreign keys in relational algebra."

6. Common Pitfalls:

   • Don’t confuse schema and instance. Schema is the structure; instance is the data.
   • Don’t forget constraints. Always mention entity integrity (PK) and referential integrity (FK) where relevant.
   • Avoid vague answers. Instead of "it’s used for queries", say "relational algebra provides a formal way to express queries like selection (σ) and projection (π)."

Practice Questions (Based on Past Exams)

1. Differentiate between schema and instance.

   • Schema: The blueprint (e.g., Student(student_id, name)).
   • Instance: The actual data (e.g., (1, "Ram"), (2, "Sita")).

2. Write relational algebra for: "Find all employees earning more than 50,000 in Kathmandu."

   σ_{salary > 50000 ∧ city = 'Kathmandu'}(Employee)
   

3. Normalize the following table to 3NF: | order_id | customer_name | product_name | quantity | unit_price | customer_city |

   • Step 1 (1NF): Already atomic.
   • Step 2 (2NF): Remove partial dependency → Separate Customer and OrderItem.
   • Step 3 (3NF): Remove transitive dependency → Move customer_city to Customer.

4. Draw an ER diagram for a Library Management System.

   • Entities: Book, Member, Loan.
   • Relationships: Member borrows Book (via Loan).
   • Attributes: Book(isbn, title, author), Member(member_id, name), Loan(loan_id, date, due_date).

In the real world

• eSewa: Uses relational databases to store user accounts (PK: user_id), transactions (FK: user_id, FK: service_id), and service providers (PK: service_id). Foreign keys enforce referential integrity (e.g., a transaction must link to a valid user and service).
• NEPSE (Nepal Stock Exchange): Relational tables track traders (PK: trader_id), shares (PK: share_id), and transactions (FK: trader_id, FK: share_id). Normalization ensures no redundant share data across transactions.
• Daraz (Nepal): Customer orders (FK: customer_id) link to product tables (PK: product_id) via foreign keys, while inventory levels are managed in normalized tables to avoid redundancy.