Skip to main content

Chapter 2: Relational Database Fundamentals

2.1 Understanding Tables, Rows, and Columns​

Relational databases organize data into tables, which are composed of rows and columns. This structure is fundamental to understanding how relational databases work.

2.1.1 Tables​

  • A table represents a single entity type (e.g., customers, orders, products).
  • Each table has a unique name within the database.

2.1.2 Rows​

  • Also known as records or tuples.
  • Each row represents a single instance of the entity (e.g., one specific customer).
  • All rows in a table have the same structure.

2.1.3 Columns​

  • Also known as fields or attributes.
  • Each column represents a specific characteristic of the entity (e.g., customer name, email).
  • Columns have a defined data type (e.g., integer, varchar, date).

2.2 Primary Keys, Foreign Keys, and Relationships​

These concepts are crucial for maintaining data integrity and establishing relationships between tables.

2.2.1 Primary Keys​

  • A primary key uniquely identifies each row in a table.
  • It can be a single column or a combination of columns.
  • Every table should have a primary key.

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

2.2.2 Foreign Keys​

  • A foreign key is a column (or combination of columns) that refers to the primary key of another table.
  • It establishes a link between two tables.

Example:

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

2.2.3 Relationships​

There are three types of relationships in relational databases:

  1. One-to-One (1:1)
  2. One-to-Many (1:N)
  3. Many-to-Many (M:N)

Example of a One-to-Many relationship:

  • One customer can have many orders, but each order belongs to only one customer.

2.3 SQL Basics (SELECT, INSERT, UPDATE, DELETE)​

SQL (Structured Query Language) is the standard language for interacting with relational databases. Here are the fundamental SQL operations:

2.3.1 SELECT​

Used to retrieve data from one or more tables.

SELECT column1, column2 FROM table_name WHERE condition;

-- Example
SELECT Name, Email FROM Customers WHERE City = 'New York';

2.3.2 INSERT​

Used to add new rows to a table.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- Example
INSERT INTO Customers (Name, Email) VALUES ('John Doe', 'john@example.com');

2.3.3 UPDATE​

Used to modify existing data in a table.

UPDATE table_name SET column1 = value1 WHERE condition;

-- Example
UPDATE Customers SET Email = 'newemail@example.com' WHERE CustomerID = 1;

2.3.4 DELETE​

Used to remove rows from a table.

DELETE FROM table_name WHERE condition;

-- Example
DELETE FROM Customers WHERE CustomerID = 1;

2.4 Real-life Example: Designing a Database for a Small E-commerce Website​

Let's design a simple database for a small e-commerce website selling books. This example will demonstrate how to apply the concepts we've learned.

Step 1: Identify Entities​

For our bookstore, we'll have these main entities:

  • Customers
  • Books
  • Orders
  • OrderItems

Step 2: Create Tables​

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    Address VARCHAR(200)
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(200),
    Author VARCHAR(100),
    Price DECIMAL(10, 2),
    StockQuantity INT
);

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

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    BookID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

Step 3: Insert Sample Data​

INSERT INTO Customers (CustomerID, Name, Email, Address) VALUES 
(1, 'Alice Johnson', 'alice@example.com', '123 Main St, Cityville'),
(2, 'Bob Smith', 'bob@example.com', '456 Elm St, Townsburg');

INSERT INTO Books (BookID, Title, Author, Price, StockQuantity) VALUES
(1, 'Database Fundamentals', 'Jane Doe', 39.99, 50),
(2, 'SQL for Beginners', 'John Smith', 29.99, 30);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount) VALUES
(1, 1, '2023-09-15', 39.99);

INSERT INTO OrderItems (OrderItemID, OrderID, BookID, Quantity) VALUES
(1, 1, 1, 1);

Step 4: Sample Queries​

  1. Retrieve all customers:
SELECT * FROM Customers;
  1. Find the total number of books in stock:
SELECT SUM(StockQuantity) AS TotalBooks FROM Books;
  1. Get order details including customer information:
SELECT O.OrderID, C.Name, O.OrderDate, O.TotalAmount
FROM Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID;
  1. List all books ordered by a specific customer:
SELECT B.Title, OI.Quantity
FROM OrderItems OI
JOIN Books B ON OI.BookID = B.BookID
JOIN Orders O ON OI.OrderID = O.OrderID
WHERE O.CustomerID = 1;

This example demonstrates how tables, primary keys, foreign keys, and relationships work together in a real-world scenario. It also shows how basic SQL operations can be used to interact with the database.