Skip to main content

Relational Database Models

Relational database models form the foundation of modern data management systems. This guide provides an in-depth exploration of relational databases, suitable for students studying computer science and pursuing a degree in database management systems.

What is a Relational Database?​

A relational database is a type of database that organizes data into tables with defined relationships between them. It uses a structured query language (SQL) to manage and manipulate data.

Key characteristics of relational databases include:

  • Data Independence: The ability to change the schema without affecting the data.
  • ACID Properties:
    • Atomicity: Ensures that transactions are all-or-nothing.
    • Consistency: Maintains database integrity before and after transactions.
    • Isolation: Ensures that transactions occur independently of one another.
    • Durability: Guarantees that once a transaction is committed, it remains so, even in the event of a system failure.
  • Scalability: Ability to handle increasing amounts of data efficiently.
  • Standardization through SQL: Use of Structured Query Language (SQL) for querying and managing data.

Basic Concepts​

To understand relational databases, it's essential to grasp these fundamental concepts:

  1. Tables: The core structure of a relational database, representing entities with rows and columns.
  2. Rows (Tuples): Individual records within a table.
  3. Columns (Attributes): Fields in a table that represent data attributes.
  4. Primary Keys: Unique identifiers for rows in a table to ensure data integrity.
  5. Foreign Keys: Columns that create a link between tables by referencing primary keys in another table.
  6. Relationships: The associations between tables, such as one-to-one, one-to-many, and many-to-many relationships.

Tables​

Tables are the fundamental components of a relational database. Each table represents an entity and consists of rows and columns.

Example:

| Student_ID | Name       | Age | Department |
|------------|------------|-----|------------|
| 101        | John Doe   | 20  | CS         |
| 102        | Jane Smith | 22  | Math       |

Primary Keys​

Primary keys are used to uniquely identify each row in a table. They ensure that each record can be uniquely identified and prevent duplicate entries.

Example:

| Order_ID | Customer_ID | Product_ID | Quantity | Price |
|----------|-------------|------------|----------|-------|
| 001      | 101         | 201        | 2        | 10.99 |
| 001      | 101         | 202        | 1        | 15.00 |

Normalized: Orders Table:

| Order_ID | Customer_ID | Total_Amount |
|----------|-------------|--------------|
| 001      | 101         | 27.98        |

Order_Items Table:

| Order_ID | Product_ID | Quantity | Unit_Price |
|----------|------------|----------|------------|
| 001      | 201        | 2        | 10.99      |
| 001      | 202        | 1        | 15.00      |

Querying Relational Databases​

SQL (Structured Query Language) is the standard language for interacting with relational databases. Here are some basic SQL queries:

  1. SELECT Statement: The SELECT statement is used to query data from a table.

    SELECT Name, Age FROM Students WHERE Department = 'CS';

    This query retrieves the names and ages of students in the Computer Science department.

  2. INSERT Statement: The INSERT statement is used to add new rows to a table.

    INSERT INTO Students (Student_ID, Name, Age, Department) 
    VALUES (103, 'Alice Johnson', 21, 'CS');

    This query inserts a new student record into the Students table.

  3. UPDATE Statement: The UPDATE statement is used to modify existing records in a table.

    UPDATE Students 
    SET Age = 22 
    WHERE Student_ID = 101;

    This query updates the age of the student with Student_ID 101.

  4. DELETE Statement: The DELETE statement is used to remove records from a table.

    DELETE FROM Students 
    WHERE Student_ID = 102;

    This query deletes the student record with Student_ID 102.

Conclusion​

Relational database models are fundamental to data management and organization. By understanding tables, primary keys, foreign keys, and relationships, students can effectively design and query relational databases. Mastery of SQL further enhances the ability to interact with and manage data efficiently. This foundational knowledge is crucial for pursuing more advanced topics in database management and computer science.