Intro to Relational DBMS
Tags: programming, database, backend, beginners
Published: 2023-10-08 18:43:16

A _Relational Database Management System_ (RDBMS) is a type of database management system that organizes data into structured tables with rows and columns and enforces the principles of the relational model. The relational model, developed by Edgar F. Codd in 1970, is a mathematical and conceptual framework for organizing and managing data. [Relational model Wikipedia](https://en.wikipedia.org/wiki/Relational_model)

Here are some key concepts associated with RDBMS:

- **Tables:** In an RDBMS, data is organized into tables. Each table consists of rows and columns. Rows represent individual records or data entries, while columns represent attributes or fields of those records. Each table has a unique name that identifies it within the database.

- **Rows and Records:** Rows in a table are also referred to as records or tuples. Each row represents a single data entity, such as a customer, product, or employee, and contains values for each of the table's columns that describe the characteristics of that entity.

- **Columns and Fields:** Columns in a table represent the attributes or characteristics of the data entities. Each column has a name and a data type that specifies the kind of data it can hold, such as text, numbers, dates, or binary data.

- **Keys:** Keys are used to uniquely identify rows within a table. The primary key is a column or set of columns that ensures each row has a unique identifier. Foreign keys are used to establish relationships between tables, allowing data to be linked and maintained across multiple tables.

    

- **Relationships:** RDBMS allows you to define relationships between tables. These relationships are typically based on keys, where one table's key is linked to another table's key. Common types of relationships include one-to-one, one-to-many, and many-to-many relationships.

  

- **Data Integrity:** RDBMS enforces data integrity rules to ensure the accuracy and consistency of data. Common integrity constraints include unique constraints, check constraints, and referential integrity constraints.

- **SQL (Structured Query Language):** SQL is the standard language used to interact with RDBMSs. It provides a set of commands for creating, querying, updating, and deleting data from the database. SQL allows users to perform operations like `SELECT` (to retrieve data), `INSERT` (to add data), `UPDATE` (to modify data), and `DELETE` (to remove data). 

- **Transactions:** RDBMSs supports transactions, which are sequences of database operations that are treated as a single unit of work. Transactions ensure that either all operations within the unit are completed successfully, or none are, maintaining the integrity of the data.

- **Normalization:** Normalization is a process used to design databases efficiently by minimizing data redundancy and ensuring data integrity. It involves decomposing tables into smaller, related tables to eliminate data anomalies.

- **Indexing:** Indexes are data structures that improve the performance of database queries by providing fast access to specific rows in a table. Indexes are created on one or more columns and allow the database system to quickly locate the data you are searching for.

RDBMSs are widely used in various applications, including business, finance, healthcare, e-commerce, and more, due to their ability to efficiently store, retrieve, and manage structured data while ensuring data consistency and integrity. Some well-known RDBMSs include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.

## Properties:
Relational Database Management Systems (RDBMSs) are known for their key properties and characteristics that make them suitable for managing structured data in various applications. Here are some of the core properties of RDBMSs

- **Data Structuring:** RDBMSs organize data into structured tables, where data is stored in rows and columns. This tabular format makes it easy to represent and manage structured data with clear relationships.

- **Data Integrity:** RDBMSs enforce data integrity through various constraints, such as primary keys, unique keys, foreign keys, and check constraints. These constraints ensure that data is accurate, consistent, and follows predefined rules.

- **Relationships:** RDBMSs support the establishment of relationships between tables using keys. This allows for the modeling of complex data structures, including one-to-one, one-to-many, and many-to-many relationships, which are crucial for data organization and retrieval.

- **ACID Properties:** RDBMSs provide support for transactions, which adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability). These properties ensure that database transactions are reliable, maintain data integrity, and can recover from failures.

1. **Atomicity:** Transactions are treated as a single unit of work, either fully completed or fully rolled back in case of failure.

2. **Consistency:** Transactions bring the database from one consistent state to another, preserving data integrity.

3. **Isolation:** Transactions are isolated from each other to prevent interference and maintain data consistency.

4. **Durability:** Once a transaction is committed, its changes are permanent and will survive system failures.

- **Query Language:** RDBMSs uses SQL (Structured Query Language) as a standard language for querying and manipulating data. SQL provides a powerful and standardized way to interact with the database, enabling users to perform operations like SELECT, INSERT, UPDATE, and DELETE.

- **Indexing:** RDBMSs support the creation of indexes on columns, which significantly improve query performance by allowing for faster data retrieval. Indexes enable the database engine to quickly locate specific rows based on the indexed column values.

- **Data Normalization:** RDBMSs encourage the practice of data normalization, which involves designing databases to minimize data redundancy and dependency. Normalization helps prevent data anomalies and improves data consistency.

- ** Multi-User Support:** RDBMSs are designed to support concurrent access by multiple users or applications. They incorporate mechanisms for managing locks and ensuring data consistency in a multi-user environment.

- **Security:** RDBMSs offers various security features, including user authentication, access control, and encryption. Database administrators can define user roles and permissions to restrict access to sensitive data.

- **Scalability:** RDBMSs can scale vertically by adding more resources to a single server or horizontally by distributing data across multiple servers in a cluster. This scalability helps handle growing data and user loads.

- **Backup and Recovery:** RDBMSs provide tools and mechanisms for creating backups of the database and recovering data in case of system failures, ensuring data durability and availability.

- **Data Independence:** RDBMSs provide a level of data independence, separating the logical schema (how data is viewed) from the physical schema (how data is stored). This allows for flexibility in adapting the database design to changing requirements without affecting the application code.