📘 Core Concepts

Welcome to Core Concepts of Relational Databases! In this chapter, we'll explore the fundamental building blocks that power modern databases. We'll dive into tables, rows, columns, and how they form the foundation of data storage. You'll learn about essential concepts like primary keys, foreign keys, and constraints that enforce data integrity. Additionally, we'll cover normalization and denormalization techniques to optimize your database design.

💡 Overview of Relational Databases

A **relational database** organizes data into tables with rows and columns. Each table represents a specific entity (e.g., `users`, `orders`), while rows represent individual records and columns define the attributes of those records.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

💡 Tables, Rows, and Columns Explained

• `Table`: A collection of related data organized in rows and columns.
• `Row`: Represents a single record or entity within the table.
• `Column`: Defines an attribute or property of the entities stored in the table.

✅ Primary Keys: The Backbone of Data Integrity

A **primary key** uniquely identifies each row in a table. It can be a single column or a combination of multiple columns.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  product VARCHAR(100) NOT NULL
);

✅ Foreign Keys: Establishing Relationships Between Tables

A **foreign key** is a column or set of columns in one table that references the primary key of another table, establishing a relationship between them.

CREATE TABLE order_details (
  detail_id INT PRIMARY KEY,
  order_id INT NOT NULL,
  product VARCHAR(100) NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

💡 Constraints: Enforcing Business Rules

• `NOT NULL`: Ensures a column cannot contain null values.
• `UNIQUE`: Guarantees all values in a column are unique.
• `PRIMARY KEY`: Combines NOT NULL and UNIQUE constraints for identifying rows.
• `FOREIGN KEY`: Enforces referential integrity between tables.

✅ Normalization: Organizing Data Efficiently

Normalization is the process of organizing data to reduce redundancy and improve integrity. It follows a series of normal forms (1NF to 5NF), each addressing specific issues like repeating groups or functional dependencies.

✅ Denormalization: Balancing Performance Trade-offs

**Denormalization** involves intentionally adding redundant data to improve query performance. While it can simplify reporting and speed up queries, it must be done carefully to avoid inconsistencies.

📘 Database Design

💡 Entity-Relationship (ER) Modeling

Entity-Relationship (ER) modeling is a fundamental technique for designing relational databases. It helps visualize the structure of your database, including entities and their relationships.

• `Entities`: Objects or concepts about which data must be stored (e.g., `Customer`, `Product`).
• `Attributes`: Properties of entities (e.g., `CustomerName`, `ProductId`).
• `Relationships`: Associations between entities (e.g., a `Customer` may `Order` multiple `Products`).
• `Keys`: Constraints that uniquely identify entities (Primary Key, Foreign Key).

// Example ER Diagram in SQL
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100)
);

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID),
    ProductID INT FOREIGN KEY REFERENCES Product(ProductID)
);

💡 Schema vs Instance

Understanding the difference between schema and instance is crucial for database design.

• `Schema`: The logical structure of a database, including tables, columns, constraints.
• `Instance`: The actual data stored in the database at a particular time.

// Example Schema
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

// Example Instance Data
INSERT INTO Employee VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith');

💡 Indexes and Performance Considerations

Indexes are critical for optimizing database performance. They allow the database engine to quickly locate data without scanning the entire table.

• `Clustered Index`: Organizes the physical order of records in a table.
• `Non-Clustered Index`: Creates a separate structure that points to the actual data rows.

// Creating an Index
CREATE INDEX idx_CustomerName ON Customer (CustomerName);

// Clustered vs Non-Clustered
-- Clustered
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY CLUSTERED,
    CustomerName VARCHAR(100)
);

-- Non-Clustered
CREATE INDEX idx_ProductCategory ON Product (ProductCategory);

💡 Best Practices for Database Design

• Normalize your database to reduce redundancy and improve integrity.
• Use meaningful names for tables, columns, and indexes.
• Avoid over-indexing as it can negatively impact write operations.