What is an RDBMS

A relational database management system (RDBMS) such as SQL Server stores data in tables of rows and columns, and links the tables with primary and foreign keys.

Novice · Database · T-SQL · Updated

After this lesson you can

  • Explain how a relational database stores data in tables, rows and columns, linked by primary and foreign keys

Key takeaways

  • A relational database keeps data in tables. Each row is one record, and each column is one field, such as CompanyName in Customers.
  • A primary key identifies each row uniquely, so no two customers share a CustomerId.
  • A foreign key, such as CustomerId in Orders, points to a primary key in another table, so you can't place an order for a customer that doesn't exist.
  • Normalization stores each fact once, such as a customer's address in Customers, and other tables refer to it by its key.
  • SQL is the language for reading and changing data in an RDBMS, and SQL Server's version of it is T-SQL.

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. SQL Server, the database you'll use in this course, is an RDBMS.

A diagram of five Northwind tables, Customers, Orders, OrderDetails, Products and Categories, with their columns, and lines linking each foreign key (FK) to the primary key (PK) it points to

Key Concepts of RDBMS

Tables

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

Example: The Customers table in the Northwind sample database has columns like CustomerId, CompanyName, 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, CustomerId is the primary key. No two customers share one.

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: The Orders table has a CustomerId foreign key that links each order to the CustomerId of a customer 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 a customer's address in every order, you store it once in the Customers table, and each order refers to it through its CustomerId.

SQL (Structured Query Language)

The standard language for interacting with an RDBMS. SQL is used for querying, updating, and managing relational databases. SQL Server's version of it is called T-SQL.

Example: This query reads one customer's company name and country:

SELECT CompanyName, Country
FROM Customers
WHERE CustomerId = '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 lets you combine data from several tables. This one lists each order with the name of the company that placed it:

SELECT Orders.OrderId, Customers.CompanyName
FROM Orders
JOIN Customers ON Orders.CustomerId = Customers.CustomerId;

You'll write joins yourself in the Joins in T-SQL chapter.

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

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

Check yourself

2 questions to try on the lesson page.

So what

Almost every app you'll build keeps its data in a relational database. Tables, keys and SQL are the ideas you'll use every time you read or save that data.

Key terms

Cheat sheets