RDBMS Concepts

An RDBMS (Relational Database Management System) is a database system that stores data in related table structures. Understanding RDBMS concepts is essential before you start writing SQL queries.

What Is an RDBMS?

An RDBMS is software used to create, maintain, and manage relational databases. Data in an RDBMS is stored in tables consisting of rows and columns.

This relational concept was first proposed by Edgar F. Codd of IBM in 1970 in his paper titled "A Relational Model of Data for Large Shared Data Banks".

Tables, Rows, and Columns

The primary data structure in an RDBMS is the table. Here are the basic terms:

• Table — A collection of data organized in rows and columns
• Row/Record — A single entity or individual data item in a table
• Column/Field — An attribute or property that describes the data

Table: student
+----+---------+-----------+------+
| id | name    | major   | enrollment_year |
+----+---------+-----------+------+
| 1  | Alan    | Computer Science | 2023 |
| 2  | Bruce   | Information Systems | 2022 |
| 3  | Clara   | Computer Engineering | 2023 |
+----+---------+-----------+------+

In the student table above, there are 3 rows of data and 4 columns (id, name, major, enrollment_year).

Relations Between Tables

The main advantage of an RDBMS is its ability to link tables to one another through relationships. These relationships let us connect data without storing the same data repeatedly.

Here is an example of two related tables:

-- Students table
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    enrollment_year INT
);

-- Grades table
CREATE TABLE grades (
    id INT PRIMARY KEY,
    student_id INT,
    course VARCHAR(100),
    score INT
);

The grades table has a student_id column that references the student table. This way, we can look up each student's grades without storing the student's name again in the grades table.

Primary Key

A primary key is a column that uniquely identifies each row in a table. Primary key values cannot be NULL and must be unique.

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

In the table above, the id column acts as the primary key because every student has a unique identification number.

Foreign Key

A foreign key is a column in one table that references the primary key in another table. Foreign keys enforce referential integrity between tables.

CREATE TABLE grades (
    id INT PRIMARY KEY,
    student_id INT,
    course VARCHAR(100),
    score INT,
    FOREIGN KEY (student_id) REFERENCES student(id)
);

With a foreign key in place, every grade inserted into the grades table must have a valid student_id (one that exists in the student table).

A Simple Database Example

Here is an illustration of a simple database with two related tables:

student table              grades table
+----+------+-----------+    +----+---------------+----------+------+
| id | name | enrollment_year  |    | id | student_id  | course   | score |
+----+------+-----------+    +----+---------------+----------+------+
| 1  | Mike | 2023      |    | 1  | 1             | Database | 85 |
| 2  | John | 2022      |    | 2  | 1             | Programming | 90 |
| 3  | Anna| 2023      |    | 3  | 2             | Database | 78 |
+----+------+-----------+    +----+---------------+----------+------+

From the example above, we can see that Mike (id=1) scored 85 in the Database course and 90 in Programming.

Summary

An RDBMS organizes data into related tables. Primary keys guarantee the uniqueness of each row, while foreign keys preserve the integrity of the relationships between tables. With this understanding, we are ready to learn basic SQL syntax for interacting with a database.

In the next chapter, we will learn the writing rules and basic SQL syntax that you need to master before writing queries.