SQL Database Engine: Database Concepts

Welcome to the core concepts of the SQL Database Engine. This section provides a foundational understanding of how data is structured, managed, and accessed within a relational database system.

1. Tables and Columns

A table is the fundamental structure for storing data in a relational database. It organizes data into rows and columns, similar to a spreadsheet. Each column represents a specific attribute or piece of information, while each row represents a single record or entity.

Primary Key: A column or set of columns that uniquely identifies each row in a table. It ensures data integrity and provides a way to reference specific records.
Foreign Key: A column or set of columns in one table that refers to the primary key in another table. This establishes relationships between tables, enabling data consistency and the ability to join related information.

Example: Customer Table

Consider a Customers table:


CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

Here, CustomerID is the primary key, ensuring each customer has a unique ID.

2. Data Types

Each column in a table is assigned a specific data type, which defines the kind of data it can store and the operations that can be performed on it. Common data types include:

Numeric: INT, DECIMAL, FLOAT
String: VARCHAR, CHAR, TEXT
Date and Time: DATE, DATETIME, TIME
Boolean: BIT (typically stores 0 or 1)
Binary: VARBINARY, BLOB

3. Relationships and Normalization

Relationships between tables are crucial for organizing data efficiently and reducing redundancy. Normalization is a process used to organize data in a database, typically to minimize data duplication and improve data integrity.

One-to-One: Each record in Table A corresponds to at most one record in Table B, and vice versa.
One-to-Many: Each record in Table A can correspond to many records in Table B, but each record in Table B corresponds to only one record in Table A.
Many-to-Many: Each record in Table A can correspond to many records in Table B, and vice versa. This usually requires an intermediate "junction" or "linking" table.

Example: Orders and Customers (One-to-Many)

An Orders table might have a CustomerID as a foreign key:


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

This links each order to a specific customer.

4. Views

A view is a virtual table based on the result-set of a SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Views can simplify complex queries.
They can be used to restrict access to certain rows or columns for security purposes.

Example: Customer Order Summary View

A view to see customer names and their order counts:


CREATE VIEW CustomerOrderSummary AS
SELECT
    C.FirstName,
    C.LastName,
    COUNT(O.OrderID) AS NumberOfOrders
FROM
    Customers AS C
LEFT JOIN
    Orders AS O ON C.CustomerID = O.CustomerID
GROUP BY
    C.CustomerID, C.FirstName, C.LastName;

You can then query this view like a regular table: SELECT * FROM CustomerOrderSummary;

5. Indexes

An index is a data structure that improves the speed of data retrieval operations on a database table. Indexes are created on one or more columns of a table. While they speed up reads, they can slow down writes (inserts, updates, deletes) as the index also needs to be updated.

Clustered Index: Determines the physical order of data in the table. A table can have only one clustered index.
Non-Clustered Index: Does not affect the physical order of the data but creates a separate structure pointing to the data rows. A table can have multiple non-clustered indexes.

6. Transactions

A transaction is a sequence of database operations performed as a single logical unit of work. Transactions adhere to the ACID properties:

Atomicity: Ensures that all operations within a transaction are completed successfully, or none of them are.
Consistency: Guarantees that a transaction brings the database from one valid state to another.
Isolation: Ensures that concurrent transactions do not interfere with each other.
Durability: Guarantees that once a transaction has been committed, it will remain committed even in the event of a system failure.

Example: Transferring Funds

A simple transaction to move money between accounts:


BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 101;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 202;
COMMIT TRANSACTION;