Introduction to Relational Database Management Systems (RDBMS)

What is RDBMS?

A Relational Database Management System (RDBMS) is a database management system based on the relational model, introduced by Edgar F. Codd. This model organizes data into tables with rows and columns, creating a clear and manageable structure. RDBMS ensures data integrity, allows complex queries, and supports multiple users.

Main features of RDBMS

• Data is organized into tables: Data is stored in tables, each table representing an entity or relationship.
• Relational model: Tables can be linked together through keys, forming a network of relationships.
• Data integrity: RDBMS provides constraints to ensure that data is always accurate and consistent. Learn more about ACID in databases .
• SQL Query Language: SQL (Structured Query Language) is the standard language for querying and manipulating data in RDBMS.
• Multi-user support: RDBMS allows multiple users to access and manipulate data simultaneously, ensuring consistency and security.

Basic Concepts in RDBMS

Schema

A schema is the logical structure of a database, consisting of tables, columns, keys, and constraints. It defines how data is organized and related to each other. A schema can contain multiple tables, each with its own set of columns and constraints.

For example, a schema for a library management application might include tables such as Books , Authors , and Loans .

Tables

A table is the basic unit of data storage in an RDBMS. Each table consists of rows and columns. Each row represents a record, and each column represents an attribute of that record.

For example, the Customers table might have columns such as CustomerID , Name , Address , and PhoneNumber .

Rows

Rows, also known as records, contain data specific to each entity in the table. Each row must follow the structure defined in the table's schema.

For example, a row in the Customers table might contain information about a specific customer, such as CustomerID = 123 , Name = "John Doe" , Address = "123 Main St" , and PhoneNumber = "555-1234" .

Columns

Columns define the properties of the data in a table. Each column has a specific data type, such as integer, string, date, etc.

For example, in the Products table, the columns might be ProductID (integer), ProductName (string), Price (float), and Description (string).

Primary Key

A primary key is one or more columns in a table that uniquely identifies each row. Primary key values cannot be duplicated and cannot be empty (NULL). Each table can have only one primary key.

For example, in the Customers table, CustomerID is often used as the primary key.

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, Name VARCHAR(255), Address VARCHAR(255) );

Foreign Key

A foreign key is a column in one table that references the primary key of another table. It is used to establish a relationship between two tables.

For example, if there is an Orders table that contains information about orders, it might have a CustomerID foreign key that references CustomerID primary key in the Customers table.

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );

SQL (Structured Query Language)

SQL is the standard language for interacting with RDBMS. It allows you to create, query, update, and delete data in the database.

Basic SQL statements include:

• SELECT : Query data from one or more tables.
• INSERT : Add new data to the table.
• UPDATE : Update existing data in the table.
• DELETE : Delete data from the table.
• CREATE TABLE : Create a new table.
• ALTER TABLE : Change the structure of a table.
• DROP TABLE : Delete table.

For example:

-- Truy vấn tất cả khách hàng từ bảng Customers SELECT * FROM Customers; -- Thêm một khách hàng mới vào bảng Customers INSERT INTO Customers (CustomerID, Name, Address) VALUES (124, 'Jane Smith', '456 Oak St'); -- Cập nhật địa chỉ của khách hàng có CustomerID là 123 UPDATE Customers SET Address = '789 Pine St' WHERE CustomerID = 123; -- Xóa khách hàng có CustomerID là 124 DELETE FROM Customers WHERE CustomerID = 124;

RDBMS Example: PostgreSQL

PostgreSQL is a powerful and popular open source relational database management system. It supports many advanced features such as transactions, views, stored procedures, and triggers. PostgreSQL complies with the SQL standard and can run on many different operating systems.

Example using PostgreSQL:

-- Kết nối đến cơ sở dữ liệu PostgreSQL psql -U username -d database_name -h hostname -- Tạo một bảng mới trong PostgreSQL CREATE TABLE Employees ( EmployeeID SERIAL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50) ); -- Thêm dữ liệu vào bảng Employees INSERT INTO Employees (FirstName, LastName, Department) VALUES ('Alice', 'Johnson', 'Sales'); -- Truy vấn dữ liệu từ bảng Employees SELECT * FROM Employees WHERE Department = 'Sales';

Advantages of RDBMS

• Data integrity: RDBMS ensures data is always accurate and consistent through constraints and transactions.
• Flexibility: SQL allows for complex queries and flexible data manipulation.
• Scalability: RDBMS can scale to handle large amounts of data and large numbers of users. Learn more about Database Scaling .
• Security: RDBMS provides security mechanisms to protect data from unauthorized access.
• Stability: RDBMS is proven and widely used in many different applications.

Disadvantages of RDBMS

• Horizontal scaling can be complex: Splitting data across multiple servers (sharding) can require a lot of effort and expertise.
• Performance can be affected by complex queries: Complex queries can be time-consuming and resource-intensive to execute.
• Not suitable for unstructured data: RDBMS is designed to work with clearly structured data, not suitable for unstructured data like text, images, or videos.

Common RDBMS Interview Questions

• What is ACID? Explain the properties of ACID (Atomicity, Consistency, Isolation, Durability) in databases. Read more about ACID in databases .
• What is the difference between primary key and foreign key? Explain the role and use of primary key and foreign key in establishing relationships between tables.
• What are indexes and why are they important? Indexes help speed up data queries by creating a data structure that allows for quick searches of rows in a table.
• What is Sharding and When to Use It? Sharding is the process of dividing data across multiple servers to increase database scalability and performance. Ask: "How will you handle shard rebalancing?"

Hopefully this article has given you an overview of RDBMS. For more in-depth information, you can refer to the literature and online courses on relational databases.