Relational Databases: Tables

In a relational database, a table is the fundamental structure for storing data. It organizes data into rows and columns, similar to a spreadsheet.

• Rows (Records or Tuples): Each row represents a single entry or record in the table.
• Columns (Fields or Attributes): Each column represents a specific type of data attribute for the entries in the table. All entries in a single column share the same data type.

The structure of a table is defined by its schema, which specifies the names and data types of its columns.

Tables are typically created using SQL (Structured Query Language). The CREATE TABLE statement is used to define the table's structure.

Example: Creating a 'Customers' Table

Let's consider creating a table to store customer information:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE
);

Explanation of Components:

• CustomerID INT PRIMARY KEY AUTO_INCREMENT: Defines an integer column named CustomerID. It's the primary key, ensuring each customer has a unique identifier, and it automatically increments for new entries.
• FirstName VARCHAR(50) NOT NULL: A string column for the first name, allowing up to 50 characters, and it cannot be left empty.
• LastName VARCHAR(50) NOT NULL: Similar to FirstName, for the last name.
• Email VARCHAR(100) UNIQUE: A string column for email addresses, allowing up to 100 characters. The UNIQUE constraint ensures no two customers share the same email.
• RegistrationDate DATE: Stores the date when the customer registered.

In relational databases, tables are often linked together to represent complex relationships between different types of data. This is achieved through foreign keys.

Example: Linking 'Orders' to 'Customers'

Suppose we have an Orders table and we want to link each order to the customer who placed it. We can add a CustomerID column to the Orders table that references the CustomerID in the Customers table.

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

The FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) clause establishes a relationship, ensuring that a CustomerID in the Orders table must exist in the Customers table.

Here are some essential operations you'll perform with tables:

• Inserting Data: Add new rows.

  INSERT INTO Customers (FirstName, LastName, Email, RegistrationDate)
  VALUES ('Jane', 'Doe', 'jane.doe@example.com', '2023-11-15');

• Querying Data: Retrieve data from tables.

  SELECT * FROM Customers WHERE LastName = 'Doe';

• Updating Data: Modify existing rows.

  UPDATE Customers
  SET Email = 'jane.doe.updated@example.com'
  WHERE CustomerID = 1;

• Deleting Data: Remove rows.

  DELETE FROM Customers WHERE CustomerID = 1;

• Altering Table Structure: Modify an existing table (add, delete, or modify columns).

  ALTER TABLE Customers
  ADD COLUMN PhoneNumber VARCHAR(20);

• Dropping a Table: Permanently delete a table.

  DROP TABLE Customers;

• Use meaningful names: Choose descriptive names for tables and columns.
• Normalize your data: Design tables to reduce redundancy and improve data integrity.
• Define primary keys: Ensure every table has a unique identifier.
• Use appropriate data types: Select the most suitable data type for each column.
• Document your schema: Keep a record of your table structures and relationships.