Database

Description

A Database is an organized collection of data that is stored and accessed electronically. It allows efficient retrieval, insertion, deletion, and updating of data. Databases are foundational in nearly every software system—from small-scale apps to large enterprise systems and global internet services.

At its core, a database enables structured data management by organizing data in formats such as tables, documents, or graphs. It ensures consistency, integrity, and concurrency when multiple users or systems access or manipulate the data.

Key Concepts and Terminology

1. Tables (in Relational Databases)

A table is a collection of rows and columns. Each row represents a record, and each column represents a field.

Example:

2. Record (Row)

A single entry in a table.

3. Field (Column)

A specific piece of data within a record, such as Name or Age.

4. Primary Key

A unique identifier for each record in a table.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

5. Foreign Key

A field that creates a relationship between two tables.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

6. Index

Improves the speed of data retrieval.

CREATE INDEX idx_name ON users(name);

Types of Databases

1. Relational Database (RDBMS)

Stores data in structured tables with rows and columns.

• Examples: MySQL, PostgreSQL, SQLite, Oracle, SQL Server
• Uses Structured Query Language (SQL) for data manipulation

2. NoSQL Database

Designed for unstructured or semi-structured data.

• Document-oriented: MongoDB, CouchDB
• Key-Value stores: Redis, DynamoDB
• Column-family stores: Cassandra
• Graph databases: Neo4j, ArangoDB

3. In-Memory Databases

Store data in RAM for fast access (e.g., Redis, Memcached).

4. Time-Series Databases

Optimized for time-stamped data (e.g., InfluxDB, TimescaleDB).

5. NewSQL

Combines SQL interface with NoSQL scalability (e.g., CockroachDB, Google Spanner).

Database Management Systems (DBMS)

A DBMS is software that interacts with end-users, applications, and the database itself to manage the data.

Popular DBMS:

• Oracle DB
• Microsoft SQL Server
• MySQL
• PostgreSQL
• MongoDB
• SQLite

Common SQL Operations

1. Create Table

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary FLOAT
);

2. Insert Record

INSERT INTO employees (id, name, salary)
VALUES (1, 'Alice', 60000);

3. Select Records

SELECT * FROM employees;

4. Update Record

UPDATE employees SET salary = 65000 WHERE id = 1;

5. Delete Record

DELETE FROM employees WHERE id = 1;

Database Normalization

A process to reduce redundancy and improve data integrity by organizing fields and tables.

Normal Forms:

• 1NF: No repeating groups
• 2NF: Full functional dependency
• 3NF: No transitive dependency
• BCNF: Stronger version of 3NF

Transactions and ACID Properties

A transaction is a sequence of operations performed as a single unit.

ACID:

• Atomicity: All or nothing
• Consistency: Data remains valid
• Isolation: Transactions don’t interfere
• Durability: Once committed, remains committed

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Indexing and Performance

Indexes are data structures that improve the speed of queries.

Example:

CREATE INDEX idx_salary ON employees(salary);

But too many indexes can slow down write operations.

Joins in SQL

1. INNER JOIN

Returns records with matching values in both tables.

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id;

2. LEFT JOIN

Returns all records from the left table, matched ones from the right.

3. RIGHT JOIN

Vice versa of LEFT JOIN.

4. FULL OUTER JOIN

Returns all records when there is a match in either table.

Views

A view is a virtual table based on the result-set of an SQL query.

CREATE VIEW high_earners AS
SELECT name FROM employees WHERE salary > 100000;

Stored Procedures & Triggers

• Stored Procedure: Precompiled SQL block that performs operations.
• Trigger: Executes automatically in response to events like INSERT or UPDATE.

Backup and Recovery

Databases implement backup strategies (full, incremental, differential) to prevent data loss.

mysqldump -u root -p mydatabase > backup.sql

Security in Databases

• User Authentication
• Access Control
• Encryption (at rest and in transit)
• SQL Injection Prevention

Example of bad practice:

"SELECT * FROM users WHERE username = '" + input + "';"

Always use prepared statements:

cursor.execute("SELECT * FROM users WHERE username = ?", (input,))

Scaling Databases

1. Vertical Scaling

Add more resources (CPU, RAM) to one server.

2. Horizontal Scaling

Add more machines (sharding, replication).

Replication and Sharding

• Replication: Copying data from one DB to another for redundancy.
• Sharding: Splitting data across multiple databases for scalability.

Real-World Applications

NoSQL Document Example (MongoDB)

{
  "name": "Alice",
  "age": 30,
  "orders": [
    {"item": "Laptop", "price": 1200},
    {"item": "Mouse", "price": 20}
  ]
}

Related Terms

• SQL
• NoSQL
• ACID
• Schema
• Normalization
• Index
• Join
• View
• Stored Procedure
• Backup
• Sharding
• Query Optimization

Conclusion

Databases are a central component of the software ecosystem. Whether storing simple user profiles or managing complex financial transactions, databases enable reliable, scalable, and efficient data management. Understanding their structure, types, and operations is critical for developers, analysts, and engineers alike. As data continues to grow in volume and value, mastering database concepts remains a cornerstone of computer science.