🗃️ Database Topics

A primary key is a field (or a set of fields) that uniquely identifies each record in a table.
It cannot contain NULL values, and each table can have only one primary key.

A unique key ensures that all values in a column are different from one another.
Unlike a primary key, it can allow NULL values (usually one or more, depending on the database).

A candidate key is any column (or combination of columns) that can uniquely identify a row.
A table can have multiple candidate keys, but only one is selected as the primary key.

A foreign key is a field in one table that points to the primary key in another table.
It is used to create relationships between tables and maintain referential integrity.

A composite key is a primary or candidate key made up of two or more columns.
The values in these columns together must be unique, even if the individual values are not.

An INNER JOIN returns only the rows that have matching values in both tables. If there is no match, the row is excluded from the result.

A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, NULLs are returned for columns from the right table.

A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. If there is no match, NULLs are returned for columns from the left table.

A FULL OUTER JOIN returns all rows when there is a match in either the left or right table. Rows with no match in one of the tables will have NULLs for the missing side.

- Use **INNER JOIN** when you only want records with matches in both tables.
- Use **LEFT JOIN** when you need all records from the left table, even if there's no match.
- Use **RIGHT JOIN** when you need all records from the right table, even if there's no match.
- Use **FULL OUTER JOIN** when you want all records from both tables, matched where possible.

SELECT Employees.name, Departments.dept_name
FROM Employees
INNER JOIN Departments ON Employees.dept_id = Departments.id;

SELECT Employees.name, Departments.dept_name
FROM Employees
LEFT JOIN Departments ON Employees.dept_id = Departments.id;

SELECT Employees.name, Departments.dept_name
FROM Employees
RIGHT JOIN Departments ON Employees.dept_id = Departments.id;

SELECT Employees.name, Departments.dept_name
FROM Employees
FULL OUTER JOIN Departments ON Employees.dept_id = Departments.id;

A one-to-one relationship means each row in Table A is linked to one and only one row in Table B, and vice versa. This type is used when two tables share the same primary key but store different types of data.

A one-to-many relationship means one row in Table A can be associated with many rows in Table B, but each row in Table B relates to only one row in Table A. This is the most common relationship in relational databases.

A many-to-many relationship occurs when multiple records in Table A relate to multiple records in Table B. To implement it, an intermediate (junction) table is used containing foreign keys referencing both tables.

CREATE TABLE Person (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
 
CREATE TABLE Passport (
  id INT PRIMARY KEY,
  person_id INT UNIQUE,
  passport_number VARCHAR(50),
  FOREIGN KEY (person_id) REFERENCES Person(id)
);

CREATE TABLE Customer (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
 
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customer(id)
);

CREATE TABLE Student (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);
 
CREATE TABLE Course (
  id INT PRIMARY KEY,
  course_name VARCHAR(100)
);
 
CREATE TABLE Enrollment (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES Student(id),
  FOREIGN KEY (course_id) REFERENCES Course(id)
);

CREATE TABLE Users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT * FROM Users;

SELECT name, email FROM Users WHERE id = 1;

UPDATE Users
SET name = 'John Doe'
WHERE id = 1;

DELETE FROM Users
WHERE id = 1;

A transaction is a sequence of one or more SQL operations executed as a single unit of work.
Either all the operations are committed (saved) together, or none are applied (rolled back).

Transactions ensure data integrity, especially in multi-user environments.
They allow multiple related operations to be treated as one atomic action, avoiding partial updates.

ACID Properties:
- Atomicity: All operations succeed or none are applied.
- Consistency: Database state remains valid after transaction.
- Isolation: Transactions are isolated from each other.
- Durability: Changes are permanently saved.

BEGIN;
 
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;
 
COMMIT;

Indexing is a data structure technique used to quickly locate and access data in a database table.
It works like an index in a book — speeding up lookups without scanning the entire table.

Indexes should be used when:
- Frequent lookups are required.
- Data is frequently sorted or filtered.
- Performance is critical for large datasets.

Indexes improve SELECT query performance but slow down INSERT, UPDATE, and DELETE operations due to maintenance overhead. They should be used on columns frequently queried in WHERE, JOIN, or ORDER BY clauses.

-- Single-column index
CREATE INDEX idx_username ON Users(username);
 
-- Composite index
CREATE INDEX idx_fullname ON Users(first_name, last_name);
 
-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON Users(email);

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability — the four key properties of a reliable transaction.

Atomicity ensures that a transaction is completed as a single unit of work. If any part fails, the entire transaction is rolled back.

Consistency ensures that a transaction leaves the database in a valid state, maintaining data integrity.

Isolation ensures that concurrent transactions do not interfere with each other, maintaining data consistency.

Durability ensures that once a transaction is committed, its changes are permanently saved, even in the event of a system failure.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

Normalization is important because it helps prevent data redundancy, reduces data duplication, and ensures data consistency. It also makes it easier to maintain and update the database.

-- Non-normalized table
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  product_name VARCHAR(100),
  quantity INT,
  price DECIMAL(10, 2)
);
 
-- Normalized tables
CREATE TABLE Customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);
 
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  product_name VARCHAR(100),
  quantity INT,
  price DECIMAL(10, 2)
);

Data modeling is the process of structuring and organizing data in a database to optimize performance, maintain data integrity, and support specific business requirements.

Data models define the structure and organization of data in a database.