SQL - RDBMS Concepts

This chapter introduces the foundational concepts of Relational Database Management Systems (RDBMS), which are the backbone of SQL. Understanding these core ideas is essential before you start writing queries.

What is RDBMS?

RDBMS stands for Relational Database Management System. It’s a type of database that stores and provides access to data points that are related to one another. The relational model, conceived by E. F. Codd in 1970, organizes data into one or more tables of columns and rows, with a unique key identifying each row.

RDBMS is the basis for SQL and all modern database systems. Popular examples include:

Open Source: PostgreSQL, MySQL, MariaDB, SQLite
Commercial: Oracle Database, Microsoft SQL Server, IBM Db2
Cloud-based: Amazon RDS, Google Cloud SQL, Azure SQL Database, Snowflake, Amazon Redshift

Tables, Rows, and Columns

In an RDBMS, data is stored in database objects called tables. A table is a collection of related data organized in a grid-like format, consisting of columns and rows.

A Column (also called a Field or Attribute) is a vertical entity that defines a specific type of data for every record (e.g., last_name, hire_date).
A Row (also called a Record or Tuple) is a horizontal entity representing a single item in the table (e.g., all information about one specific customer).

Here is an example of a CUSTOMERS table:

ID	Name	Age	Salary	City	Country
1	Ramesh	32	2000.00	Hyderabad	India
2	Mukesh	40	5000.00	New York	USA
3	Sumit	45	4500.00	Muscat	Oman

In this table, ID, Name, Age are columns. The entry for ‘Ramesh’ is a row.

Understanding NULL Values

A NULL value in a table represents missing or unknown data. It is a placeholder in a field where a value does not exist.

It’s crucial to understand that NULL is different from a zero value (0) or a string of spaces (' '). A field with a NULL value is one that has been left blank during record creation. Special care must be taken when handling NULLs in comparisons, as NULL is not equal to anything, not even another NULL.

SQL Constraints: Enforcing Data Rules

Constraints are rules applied to data columns in a table to ensure data accuracy and reliability. They limit the type of data that can go into a table.

NOT NULL: Ensures a column cannot have a NULL value.
UNIQUE: Ensures all values in a column (or a set of columns) are different.
PRIMARY KEY: A combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table.
FOREIGN KEY: Uniquely identifies a row in another table, creating a link between the two tables.
CHECK: Ensures that all values in a column satisfy a specific condition (e.g., age > 18).
DEFAULT: Provides a default value for a column when no value is specified.

While INDEX is sometimes listed with constraints, its primary purpose is performance. It allows the database to find and retrieve data very quickly, rather than enforcing a data integrity rule.

Data Integrity

Data integrity refers to the overall accuracy, completeness, and consistency of data. RDBMSs maintain data integrity through the use of constraints:

Entity Integrity: Ensures there are no duplicate rows in a table. Enforced by the PRIMARY KEY.
Domain Integrity: Enforces valid entries for a column by restricting the data type, format, or range of values. Enforced by data types, CHECK, and NOT NULL constraints.
Referential Integrity: Ensures that relationships between tables remain consistent. A row cannot be deleted if it is used by other records in another table. Enforced by the FOREIGN KEY.
User-Defined Integrity: Enforces specific business rules not covered by the other types. Often implemented via triggers or complex CHECK constraints.

Database Normalization: A Brief Introduction

Database normalization is the process of structuring a relational database to reduce data redundancy and improve data integrity. It involves dividing larger tables into smaller, well-structured tables and defining relationships between them.

The main goals are:

Eliminating redundant data: Storing the same piece of data in only one place.
Ensuring logical data dependencies: Making sure that related data is stored together.

Normalization is organized into a series of ‘Normal Forms’. For most applications, achieving the Third Normal Form (3NF) is sufficient.

First Normal Form (1NF): Ensures that the table is atomic. Each cell should contain a single value, and each record should be unique.
Second Normal Form (2NF): The table must be in 1NF, and all non-key attributes must be fully functional on the primary key.
Third Normal Form (3NF): The table must be in 2NF, and all attributes must be dependent only on the primary key, not on other non-key attributes.

Modern Database Practices

In a modern development environment, managing a database involves more than just writing SQL queries. Key practices include:

Schema Version Control: Database schemas (the CREATE TABLE statements, constraints, etc.) should be stored in a version control system like Git.
Migration Tools: Tools like Flyway or Liquibase are used to apply and track schema changes in an automated and repeatable way across different environments (development, testing, production).