CSE 340: Web Backend Development

Relational Databases

Relational databases organize data into tables with rows and columns, similar to spreadsheets. Each table represents a specific type of entity (like users, products, or orders), and the rows represent instances of that entity. The columns define the attributes or properties that each instance has.

What makes relational databases powerful is their ability to establish relationships between different tables. This allows you to model complex real-world scenarios where different entities interact with each other.

Key Concepts in Relational Databases

The following are some fundamental concepts that form the foundation of relational databases.

Tables, Rows, and Columns

A relational database stores data in tables (also called relations). Each table has a name and consists of rows and columns:

• Tables: Represent entities or concepts (for example, users, products, orders)
• Rows: Represent individual records or instances (for example, a specific user)
• Columns: Represent attributes or properties (for example, user_id, username, email)

Here is a simple example of what a "users" table might look like:

| user_id | username  | email               | join_date  |
|---------|-----------|---------------------|------------|
| 1       | johndoe   | john@example.com    | 2023-01-15 |
| 2       | janedoe   | jane@example.com    | 2023-02-20 |
| 3       | bobsmith  | bob@example.com     | 2023-03-05 |

Primary Keys

A primary key is a column (or a combination of columns) that uniquely identifies each row in a table. In the example above, the "user_id" column serves as the primary key, ensuring that each user record can be uniquely identified.

Primary keys are essential because they:

• Ensure each record is unique within a table.
• Provide a way to reference specific records.
• Enable relationships between different tables.

Foreign Keys and Relationships

A foreign key is a column in one table that refers to the primary key of another table. This creates a relationship between the two tables, allowing you to model connections between different types of data. For example, if we have a "posts" table in a blog application, we might include a "user_id" column that references the "user_id" in the "users" table:

| post_id | title           | content                   | user_id |
|---------|-----------------|---------------------------|---------|
| 1       | First Post      | This is my first post!    | 1       |
| 2       | Hello World     | Hello to everyone!        | 2       |
| 3       | Database Intro  | Databases are important...| 1       |

In this example, the "user_id" column in the "posts" table is a foreign key that references the "user_id" primary key in the "users" table. This relationship links each post to the user who created it, allowing you to easily retrieve all posts by a specific user or identify the author of any post. When designing tables, it is common to define the foreign key either immediately after the primary key in the table definition or at the end of the column list. This helps clarify the structure and relationships within your database schema.

Introduction to SQL

SQL (Structured Query Language) is the standard language for interacting with relational databases. It provides a set of commands for creating, modifying, querying, and managing data in a relational database system.

SQL was developed in the 1970s by IBM researchers and has since become the standard language for relational database management. It is designed to be both human-readable and powerful enough to handle complex data operations.

Basic SQL Operations

Here are some examples of common SQL commands that demonstrate these types of operations:

Creating a Table

The following SQL command creates a simple "users" table:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE
);

This command defines a table with four columns, specifying the data type for each column and adding constraints like PRIMARY KEY, NOT NULL, and UNIQUE to ensure data integrity. In this case, the "user_id" column is the primary key, which uniquely identifies each user, so there's no need to add a separate UNIQUE constraint. The "username" and "email" columns are marked as NOT NULL, meaning they must have a value when inserting a new record. The "email" column is also marked as UNIQUE, ensuring that no two users can have the same email address.

Inserting Data

To add data to a table, you use the INSERT command:

INSERT INTO users (user_id, username, email, join_date)
VALUES (1, 'johndoe', 'john@example.com', '2023-01-15');

Querying Data

The SELECT command retrieves data from one or more tables:

SELECT username, email
FROM users
WHERE join_date > '2023-01-01'
ORDER BY username;

This query selects the username and email of all users who joined after January 1, 2023, and sorts the results alphabetically by username.

Relationships Between Tables

Relational databases derive their power from the ability to establish relationships between tables. These relationships are implemented using foreign keys and help model real-world connections between different types of data.

One-to-One (1:1)

In a one-to-one relationship, one record in the first table is related to exactly one record in the second table.

        CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL
        );

        CREATE TABLE student_details (
            detail_id SERIAL PRIMARY KEY,
            student_id INTEGER UNIQUE,  -- Ensures one-to-one relationship
            address TEXT,
            phone VARCHAR(20),
            medical_notes TEXT,
            FOREIGN KEY (student_id) REFERENCES students (student_id)
        );
    

The UNIQUE constraint on student_id in the student_details table ensures that each student can have only one detail record.

One-to-Many (1:N)

In a one-to-many relationship, one record in the first table can be related to multiple records in the second table, but each record in the second table is related to only one record in the first table.

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

In this example, one department can have many employees, but each employee belongs to only one department.

Many-to-Many (N:M)

In a many-to-many relationship, one record in the first table can be related to multiple records in the second table, and vice versa. This requires a junction table (also called a bridge or linking table).

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INTEGER
);

-- Junction table for the many-to-many relationship
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id),  -- Composite primary key
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

In this example, one student can enroll in many courses, one course can have many students, and the enrollments junction table resolves this many-to-many relationship by creating two one-to-many relationships.