Core Database Concepts in SQL

To effectively write and execute SQL queries, it’s crucial to understand the underlying architecture and principles of relational databases. SQL is not just a language — it’s a tool designed to interact with a well-defined data model. In this chapter, we will dive deep into the fundamental concepts of databases and how SQL interfaces with them.

What is a Database?

At its core, a database is an organized collection of data, typically stored and accessed electronically from a computer system. In traditional relational databases, data is structured in a tabular format, which makes it easy to manage, retrieve, and manipulate.

A relational database is based on the relational model, a theory introduced by E.F. Codd in 1970. It uses tables (also known as relations) to represent data and relationships among data.

Types of Databases

Relational Databases (RDBMS) – Use tables, columns, and rows. Examples: MySQL, PostgreSQL, Oracle.
NoSQL Databases – Schema-less, flexible storage. Examples: MongoDB, Cassandra.
In-Memory Databases – Fast storage using memory. Examples: Redis, Memcached.
Distributed Databases – Spread across multiple locations. Examples: Google Spanner, Amazon Aurora.

Relational databases continue to dominate where data consistency, integrity, and complex querying are important — all areas where SQL shines.

Tables, Rows, and Columns

SQL operates through three key components in a relational database:

1. Tables (Relations)

A table is the fundamental unit of storage in a relational database. It represents an entity, such as Customers, Orders, or Products. Tables consist of:

Columns (Attributes): Define the data type and constraints
Rows (Records): Represent actual data entries

Example table: Students

StudentID	Name	Age	Major
1	Alice Smith	21	Computer Science
2	Bob Johnson	22	Mathematics

Each row represents one entity (a student), and each column is a specific attribute of that entity.

2. Columns and Data Types

Each column in a table has a data type, which dictates what kind of data it can hold:

INT, BIGINT – for integers
VARCHAR(n), TEXT – for text
DATE, DATETIME, TIME – for temporal data
BOOLEAN, DECIMAL, FLOAT – for booleans and floating numbers

Properly defining data types is essential for performance, indexing, and validation.

3. Rows (Tuples)

A row contains the actual data entry. Each row in a table is unique if it’s identified using a primary key, which we’ll cover next.

Keys in SQL

Keys are critical in maintaining relationships and ensuring uniqueness in data entries.

1. Primary Key

Uniquely identifies each row in a table
Cannot be NULL
Only one primary key per table

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT
);

2. Foreign Key

Refers to the primary key in another table
Establishes a relationship between two tables

CREATE TABLE Enrollments (
  EnrollmentID INT PRIMARY KEY,
  StudentID INT,
  CourseID INT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

This creates a link between Enrollments and Students, enabling JOIN operations.

3. Composite Key

Uses more than one column to define a primary key

CREATE TABLE CourseAssignments (
  CourseID INT,
  InstructorID INT,
  PRIMARY KEY (CourseID, InstructorID)
);

Database Schemas

A schema is a logical container or blueprint that holds database objects like tables, views, indexes, stored procedures, and more.

In SQL systems:

A user can own one or multiple schemas.
Each schema can contain multiple objects.

This abstraction is useful in large databases where different modules or applications interact with different parts of the data.

Entity-Relationship Model (ER Model)

Before implementing a database using SQL, data is often modeled using ER diagrams. These represent:

Entities: Real-world objects (e.g., Student, Course)
Attributes: Properties of those objects
Relationships: Associations between entities

Example relationships:

One-to-One (1:1)
One-to-Many (1:N)
Many-to-Many (M:N)

SQL enforces these relationships using foreign keys and constraints.

Relational Database Management System (RDBMS)

An RDBMS is software used to create, manage, and query relational databases. Popular RDBMSs include:

MySQL – Open-source, widely used
PostgreSQL – Open-source, feature-rich
Oracle – Enterprise-grade, powerful features
Microsoft SQL Server – Commercial, strong integration with Windows tools

RDBMS provides functionality like:

Multi-user access
Security and permissions
Backup and recovery
Transaction management
Concurrency control

Integrity Constraints

Constraints ensure the accuracy and reliability of data within a database.

Constraint	Description
`PRIMARY KEY`	Ensures each record is unique and not null
`FOREIGN KEY`	Enforces referential integrity
`NOT NULL`	Ensures a column cannot have NULL value
`UNIQUE`	Ensures all values in a column are unique
`CHECK`	Validates values based on a condition
`DEFAULT`	Provides a default value when none is specified

Examples:

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Age INT CHECK (Age >= 18),
  Department VARCHAR(50) DEFAULT 'General'
);

ACID Properties

A cornerstone of relational databases is ACID compliance, which guarantees reliable transaction processing.

Property	Meaning
Atomicity	Transactions are all-or-nothing
Consistency	Transactions bring the database from one valid state to another
Isolation	Transactions are isolated from each other (no intermediate visibility)
Durability	Once a transaction commits, the changes are permanent

For example, in a banking system, transferring money from Account A to B should not succeed partially — it must either complete fully or not at all.

Indexing in Databases

An index is a performance optimization feature that allows faster querying. Like an index in a book, it enables the database to find data without scanning the entire table.

Types of indexes:

Single-column Index
Composite Index
Unique Index
Full-text Index (for searching text)

Example:

CREATE INDEX idx_lastname ON Employees(LastName);

Proper indexing improves query performance, but over-indexing can slow down data modifications like INSERT or UPDATE.

Relationships Between Tables

Relational databases are named so because they relate tables via keys. For example:

-- Courses Table
CREATE TABLE Courses (
  CourseID INT PRIMARY KEY,
  CourseName VARCHAR(100)
);

-- Enrollments Table
CREATE TABLE Enrollments (
  EnrollmentID INT PRIMARY KEY,
  StudentID INT,
  CourseID INT,
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

This creates a one-to-many relationship: one course can have many enrollments.

Database Normalization

Normalization is the process of organizing tables to reduce data redundancy and improve integrity.

Forms of normalization:

1NF (First Normal Form) – Eliminate repeating groups, make atomic values
2NF – Remove partial dependencies
3NF – Remove transitive dependencies
BCNF and beyond – Advanced refinements

Example:
Instead of storing student and course details in one table, normalization splits them:

Students table for student data
Courses table for course data
Enrollments table to link them

This allows scalable, clean, and efficient data structures.

Real-World Use Case

Suppose you are building a Student Management System. You might define tables as:

Students (StudentID, Name, DOB)
Courses (CourseID, Title)
Enrollments (EnrollmentID, StudentID, CourseID, Grade)

Using SQL, you can:

Add a new student: INSERT INTO Students ...
Retrieve all students enrolled in ‘Computer Science’: SELECT ... JOIN ...
Calculate the average grade in a course: SELECT AVG(Grade) ...

These operations rely heavily on your understanding of relational database design.

Summary

Understanding core database concepts is essential to mastering SQL. Tables, rows, keys, constraints, and relationships form the building blocks of SQL-based systems. Without this foundation, writing optimal and meaningful SQL queries becomes difficult.

In the upcoming chapters, we will dive into SQL syntax and commands, starting with data types, followed by SQL command categories like DDL, DML, and more.