Relational Databases: An Overview

Tables and Relations

In the relational model, a table is formally called a relation. Each relation has a name and a set of attributes (columns). The data in a relation is a set of tuples (rows).

Example: A `Customers` table might have columns like `CustomerID`, `FirstName`, `LastName`, `Email`, and `PhoneNumber`.

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

Primary Keys

A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that each record can be uniquely addressed and prevents duplicate entries.

In the example above, `CustomerID` is the primary key for the `Customers` table.

Foreign Keys

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. This establishes a link between the two tables, enforcing referential integrity.

Example: An `Orders` table might have a `CustomerID` column that is a foreign key referencing the `Customers` table. This ensures that every order is associated with a valid customer.

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

SQL (Structured Query Language)

SQL is the standard language used to communicate with relational databases. It allows you to perform operations such as querying data, inserting new records, updating existing records, and deleting records.

Common SQL commands include:

• SELECT: Retrieves data from a database.
• INSERT: Adds new data to a database.
• UPDATE: Modifies existing data in a database.
• DELETE: Removes data from a database.
• CREATE TABLE: Creates a new table.
• ALTER TABLE: Modifies an existing table.
• DROP TABLE: Deletes a table.