Relational database basic concepts

DBMS

DBMS stands for Database Management System. It is the software that manages databases — handling storage, retrieval, and modification of data.

A RDBMS (Relational Database Management System) is a DBMS specifically designed for relational databases that use tables and SQL.

Common RDBMS examples:

• MySQL — open source, widely used in web applications
• MariaDB — open source fork of MySQL
• PostgreSQL — open source, known for advanced features and standards compliance
• SQLite — lightweight, file-based, no separate server needed
• SQL Server — Microsoft's commercial database
• Oracle — commercial, used in large enterprise systems

Database

In relational databases, data is stored in tables. A single database is a collection of tables. One database server can have multiple databases. Each database has its own data and name.

Table

A database contains tables. Tables have a defined structure of rows and columns. Each table has a name.

Column

Database table columns have:

• name (anything, except reserved words)
• type

Columns can also be defined as null or not null. Not null columns must have a value, while nullable columns can be left empty (null).

TIP

It is good idea to have consistent naming style for the columns. I usually use all lowercase and underscores (_) for the column names.

Row

A row is one set of data in a database table. It has the data divided into columns and each field on the row has to have the data in the format defined by the column.

For example: If a database table is about persons, their names and other information, each row has information about one specific person.

SQL

• Structured Query Language
• Developed by IBM
• Used with relational databases
• Used for data creation, retrieval, updating and deletion (CRUD)
• Basics are similar across all databases
• Some database-specific differences exist (function names, data types, etc.)

Relationships

Tables are connected to each other through keys. There are three types of relationships:

One-to-one

One row in table A is linked to one row in table B.

Example: Each student has exactly one student card.

One-to-many

One row in table A is linked to many rows in table B.

Example: One teacher can teach many courses, but each course has only one teacher.

This is the most common relationship type. It is implemented with a foreign key on the "many" side.

Many-to-many

Many rows in table A can be linked to many rows in table B.

Example: Students can enroll in many courses, and each course can have many students.

This requires a junction table (also called a join table) that holds foreign keys to both tables:

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

Primary Key

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. Every table should have a primary key.

Rules:

• Each value must be unique — no two rows can have the same primary key
• The value cannot be null
• A table can only have one primary key

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

A primary key is often an auto-incrementing integer (AUTO_INCREMENT in MySQL).

Foreign Key

A foreign key is a column in one table that references the primary key of another table. It creates a link between two tables.

CREATE TABLE grades (
  id INT PRIMARY KEY,
  student_id INT,
  grade INT,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

Here student_id in the grades table points to id in the students table. This ensures that every grade belongs to an existing student.

Foreign keys enforce referential integrity — you cannot insert a value that does not exist in the referenced table.

Index

An index speeds up data retrieval by allowing the database to find rows without scanning the entire table.

CREATE INDEX idx_name ON students(name);

• Primary keys are automatically indexed
• Add indexes on columns that are frequently used in WHERE, JOIN, or ORDER BY
• Indexes speed up reads but slow down writes (inserts, updates, deletes), because the index must also be updated

TIP

Apply indexes to those columns that are used for selecting the data most often