What is an RDBMS

A relational database (RDBMS) stores data in tables of rows and columns, links tables with primary and foreign keys, and is queried with SQL.

Novice · Database · SQL · Updated

By

A Relational Database Management System (RDBMS) is a type of database management system. In an RDBMS, all data is organized into tables and stored in rows and columns. This structure makes locating and accessing specific values within the database easy. RDBMSs use Structured Query Language (SQL) for database access and management.

Diagram of five northwind tables (customers, orders, order_details, products and categories), with each primary key (PK) and the foreign keys (FK) that link the tables

Key Concepts of RDBMS

Tables

Data in an RDBMS is organized into tables. Each table consists of rows (records) and columns (fields).

Example: A table named customers might have columns like customer_id, company_name, address, and country.

Primary Key

Each table has a primary key: a unique identifier for each record in the table.

Example: In the customers table, customer_id could be the primary key.

Foreign Key

Foreign keys are used to establish relationships between tables. A foreign key in one table points to a primary key in another table.

Example: An orders table might have a customer_id foreign key that links to the customer_id in the customers table.

Normalization

The process of organizing data to reduce redundancy and improve data integrity. This often involves dividing large tables into smaller, related tables.

Example: Instead of storing customer addresses in every order record, you store them in the customers table and reference them via the customer_id.

SQL (Structured Query Language)

The standard language for interacting with an RDBMS. SQL is used for querying, updating, and managing relational databases.

Example:

SELECT company_name
     , country
FROM customers
WHERE customer_id = 'ALFKI';

Advantages of RDBMS

Data Integrity and Accuracy

Enforcing data integrity rules ensures accurate and consistent data within the database. A foreign key helps ensure data integrity. For example, you cannot place an order for a customer that does not exist.

Flexibility in Queries

SQL allows complex queries to retrieve and manipulate data efficiently. A JOIN statement lets you combine data from multiple tables.

SELECT orders.order_id
     , customers.company_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Data Security

RDBMSs offer robust security features to control access to data. Administrators can grant specific permissions to users or roles to restrict who can view or modify data.

Examples of RDBMSs

  • MySQL: Open-source RDBMS widely used in web applications.
  • PostgreSQL: Advanced open-source RDBMS, known for its robustness and compliance with standards.
  • Microsoft SQL Server: RDBMS developed by Microsoft, widely used in enterprise environments.
  • Oracle Database: High-performance RDBMS, known for its scalability and reliability in large-scale applications.

Do it: find one customer's orders by its key

The orders table doesn't repeat a customer's name or country. Each order holds only the customer's key, customer_id: a foreign key that points to that customer's row in customers. So to find one customer's orders, you look for its key in orders.

Ana Trujillo's shop, whose customer_id is 'ANATR', has asked for a list of its orders.

The two tables (the Tables tab shows the SQL that makes them) hold these rows:

customer_id company_name country
ALFKI Alfreds Futterkiste Germany
ANATR Ana Trujillo Emparedados y helados Mexico
AROUT Around the Horn UK
order_id customer_id order_date
10308 ANATR 2016-09-18
10355 AROUT 2016-11-15
10383 AROUT 2016-12-16
10625 ANATR 2017-08-08
10643 ALFKI 2017-08-25
10692 ALFKI 2017-10-03
10759 ANATR 2017-11-28
10926 ANATR 2018-03-04

Your query should give exactly these rows, in any order:

order_id order_date
10308 2016-09-18
10625 2017-08-08
10759 2017-11-28
10926 2018-03-04

Your job: change the query in the box so it lists Ana Trujillo's orders. Right now it reads one customer from customers, the lesson's example.

  • Show two columns, order_id and order_date, in that order.
  • Read them from the orders table.
  • Keep only the rows whose customer_id is 'ANATR'.

Practiced live on the lesson page: write the SQL right there and check it.

Key terms

Cheat sheets