Databases and SQL

1. What Is a Database?

A database is an organized collection of data that can be stored, accessed, managed, and updated.

Schools can use databases to manage student records, libraries can track books, and online stores can maintain product and order details.

What is a DBMS?

A Database Management System (DBMS) is software used to create and manage databases. It helps users store, retrieve, modify, and protect data.

Examples of database systems include:

• MySQL
• PostgreSQL
• SQLite
• Microsoft SQL Server
• Oracle Database

2. Relational Databases

A relational database organizes data into tables made up of rows and columns.

• Table: A collection of related records.
• Row: One record in a table.
• Column: A field or attribute shared by records.
• Schema: The defined structure of the database.

Example: Students table

Each row represents one student, while each column describes a property of the students.

3. Primary Keys and Foreign Keys

Primary key

A primary key uniquely identifies each row in a table. Its values must be unique and cannot be NULL.

In the Students table, student_id can serve as the primary key because each student has a different ID.

Foreign key

A foreign key is a column or group of columns that refers to a primary key or another eligible unique key in a table. It helps establish relationships between tables.

Example: Courses and enrolments

Courses

Enrolments

Here, course_id in Enrolments refers to course_id in Courses. The combination of student_id and course_id could be used as a composite primary key to prevent duplicate enrolments.

4. What Is SQL?

SQL (Structured Query Language) is a language used with relational database systems to define structures and query or modify data.

Common SQL command categories

• DDL: Data Definition Language — defines database structures, such as CREATE and ALTER.
• DML: Data Manipulation Language — modifies data, such as INSERT, UPDATE, and DELETE.
• DQL: Data Query Language — retrieves data, commonly using SELECT.
• DCL: Data Control Language — manages permissions, such as GRANT and REVOKE.
• TCL: Transaction Control Language — manages transactions, such as COMMIT and ROLLBACK.

5. Creating a Table

The CREATE TABLE command defines a new table.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade INT
);

In this example:

• INT stores integer values.
• VARCHAR(100) stores text up to a specified length in systems that support this type.
• PRIMARY KEY identifies each record uniquely.
• NOT NULL prevents the name field from being left NULL.

6. Inserting Data

Use INSERT INTO to add records.

INSERT INTO Students (student_id, name, age, grade)
VALUES (101, 'Asha', 16, 11);

INSERT INTO Students (student_id, name, age, grade)
VALUES (102, 'Ravi', 17, 12);

Text values are usually enclosed in single quotation marks. The exact syntax and available data types can vary by database system.

7. Retrieving Data with SELECT

The SELECT command retrieves data from one or more tables.

Retrieve every column

SELECT * FROM Students;

Retrieve selected columns

SELECT name, grade
FROM Students;

Filter results with WHERE

SELECT *
FROM Students
WHERE grade = 11;

Sort results with ORDER BY

SELECT name, age
FROM Students
ORDER BY age DESC;

ASC sorts in ascending order, while DESC sorts in descending order. Many systems use ascending order by default.

8. Updating and Deleting Data

UPDATE

Use UPDATE to change existing records.

UPDATE Students
SET grade = 12
WHERE student_id = 101;

DELETE

Use DELETE to remove records.

DELETE FROM Students
WHERE student_id = 102;

9. Aggregate Functions and GROUP BY

Aggregate functions calculate a result from multiple rows.

Example: Count students in each grade

SELECT grade, COUNT(*) AS student_count
FROM Students
GROUP BY grade;

GROUP BY combines rows with the same grade into groups, allowing an aggregate calculation for each group.

10. Relationships and JOIN

A JOIN combines related rows from two or more tables using a condition.

INNER JOIN

Returns rows that match the join condition in both tables.

SELECT Students.name, Courses.course_name
FROM Enrolments
INNER JOIN Students
    ON Enrolments.student_id = Students.student_id
INNER JOIN Courses
    ON Enrolments.course_id = Courses.course_id;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table. When there is no match, columns from the right table are typically NULL.

SELECT Students.name, Enrolments.course_id
FROM Students
LEFT JOIN Enrolments
    ON Students.student_id = Enrolments.student_id;

JOIN operations help avoid storing every piece of information in one enormous table.

11. Database Design and Normalization

Database normalization is a way of organizing relational tables to reduce unnecessary duplication and prevent certain update, insertion, and deletion problems.

• First Normal Form (1NF): Each field contains a single value appropriate to the chosen data model, rather than a list of repeating values in one cell.
• Second Normal Form (2NF): The table is in 1NF and non-key attributes depend on the whole candidate key, not just part of a composite key.
• Third Normal Form (3NF): The table is in 2NF and avoids certain unwanted dependencies of non-key attributes on other non-key attributes.

12. Transactions and Data Integrity

A transaction is a group of database operations treated as a unit of work.

Transactions are commonly described by the ACID properties:

• Atomicity: The transaction's operations succeed as a unit or are rolled back as appropriate.
• Consistency: A successful transaction preserves defined database rules and constraints.
• Isolation: Concurrent transactions are controlled to prevent unintended interference, according to the isolation level.
• Durability: Committed changes are designed to persist, including through specified failures.

BEGIN TRANSACTION;

UPDATE Accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE Accounts
SET balance = balance + 500
WHERE account_id = 2;

COMMIT;

This simplified example transfers an amount between two accounts. A real system must also validate accounts and balances, handle errors, and use the transaction features supported by its database.

13. Knowledge Check

Try answering each question before revealing the answer.

Key Takeaways

• A database stores organized information, and a DBMS manages it.
• Relational databases organize records into tables.
• Primary and foreign keys help identify records and connect tables.
• SQL supports table creation, data insertion, queries, updates, and deletion.
• JOIN combines related data, while normalization improves database organization.
• Transactions help maintain reliable, consistent data.