Database Cheat Sheet

Database Cheat Sheet: Types, SQL, Keys, Normalization & Examples

A database is an organized collection of data that can be stored, retrieved, updated, and managed efficiently. Databases power websites, mobile applications, banking systems, e-commerce platforms, business reporting, and many other digital services.

Understanding databases is useful for software developers, data analysts, database administrators, business intelligence professionals, and anyone working with data-driven applications.

This cheat sheet explains essential database concepts, common database types, table structures, relationships, normalization, indexing, transactions, and security.

1. What Is a Database?

A database stores information in a structured or otherwise organized form so applications and users can access and manage it.

Examples include:

• Customer information.

• Employee records.

• Product catalogs.

• Online orders.

• Financial transactions.

• Website user accounts.

• Marketing campaign performance data.

• Inventory and delivery information.

A database management system (DBMS) is software that helps create, query, update, secure, and maintain databases.

Examples of database management systems include MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, SQLite, and MongoDB.

Database vs. DBMS

2. Types of Databases

Databases can be classified according to their data model and intended use.

Relational Databases

Relational databases organize data into tables containing rows and columns. Relationships between tables are commonly established through keys.

Examples:

• MySQL.

• PostgreSQL.

• Microsoft SQL Server.

• Oracle Database.

• SQLite.

Common uses include financial systems, business applications, inventory management, and transactional systems.

NoSQL Databases

NoSQL is a broad category of databases that use data models other than the traditional relational model, or that provide non-relational data access approaches.

NoSQL databases are useful for various workloads, including flexible document storage, distributed applications, caching, and relationship analysis.

Other Database Categories

• Time-series databases: Designed for timestamped measurements and events.

• Data warehouses: Designed primarily for analytical queries and reporting.

• In-memory databases: Keep significant working data in memory for fast access.

• Embedded databases: Operate within or alongside an application, such as SQLite in many use cases.

Different categories can overlap. Choose a database based on data structure, query patterns, consistency needs, scale, cost, and operational requirements.

3. Tables, Rows, and Columns

In a relational database, a table organizes records into rows and attributes into columns.

Consider an Employees table:

• Table: Employees

• Row: One employee's record.

• Column: An attribute such as name or salary.

• Schema: The defined structure of database objects, including tables and their columns.

• Data type: Specifies the kind of values a column can hold.

A table's design determines which data types, constraints, and relationships apply to its records.

4. Database Keys

Keys help identify records and establish relationships between tables.

Primary Key

A primary key uniquely identifies each row in a table.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100)
);

A primary key must be unique and cannot contain NULL values.

Foreign Key

A foreign key references a key in another table, helping enforce referential integrity.

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES Departments(department_id)
);

This design connects employees with departments.

Other Key Types

A database may support different rules for nullable unique columns. Consult the relevant DBMS documentation when designing constraints.

5. Database Relationships

Relational databases use relationships to connect related records.

One-to-One

One record is associated with at most one record in another table, and vice versa.

Example: An employee and a dedicated employee profile.

One-to-Many

One record can be associated with multiple records in another table.

Example: One department has many employees.

Many-to-Many

Multiple records in one table can relate to multiple records in another.

Example: Students can enroll in many courses, and each course can have many students.

A junction table is commonly used:

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

The referenced student and course keys would normally be defined as foreign keys as well.

Understanding relationships helps avoid duplicated information and incorrect joins.

6. SQL and Database Operations

SQL stands for Structured Query Language. It is commonly used to work with relational databases.

Retrieve Data

SELECT name, salary
FROM Employees;

Filter Data

SELECT *
FROM Employees
WHERE salary > 50000;

Insert Data

INSERT INTO Employees (employee_id, name)
VALUES (104, 'Sara');

This assumes that the remaining columns allow omitted values or have suitable defaults.

Update Data

UPDATE Employees
SET salary = 52000
WHERE employee_id = 101;

Delete Data

DELETE FROM Employees
WHERE employee_id = 104;

Always check the intended rows before running an update or delete operation. Omitting a WHERE clause can affect every row.

7. Database Normalization

Normalization is the process of organizing relational tables to reduce unnecessary duplication and prevent certain data anomalies.

Why Normalize a Database?

Without a suitable design, duplicated data can lead to:

• Update anomalies.

• Insertion anomalies.

• Deletion anomalies.

• Inconsistent information.

First Normal Form (1NF)

A table is in first normal form when each field contains a single value from its intended domain and repeating groups are avoided.

Instead of storing several phone numbers in a single field separated by commas, use a suitable structure that represents phone numbers as individual values.

Second Normal Form (2NF)

A table is in second normal form when it satisfies 1NF and every non-prime attribute depends on the whole of each candidate key, not just part of a composite candidate key.

This is especially relevant when a table has composite keys.

Third Normal Form (3NF)

A table is in third normal form when it satisfies 2NF and has no prohibited transitive dependencies of non-prime attributes on candidate keys.

In practical terms, attributes should depend appropriately on the key rather than on unrelated non-key attributes.

Normalization Example

Suppose an order table stores:

The customer's name and city are repeated.

A possible redesign separates customer information:

Customers

Orders

This reduces repeated customer details. A real e-commerce database would typically use additional tables for order items, products, addresses, and other entities.

Normalization is valuable, but some analytical or performance-oriented systems deliberately use denormalized structures.

8. Database Indexes

An index is a data structure that helps a database find records more efficiently for suitable queries.

Example:

CREATE INDEX idx_employee_name
ON Employees(name);

A query filtering by employee name may benefit from the index:

SELECT *
FROM Employees
WHERE name = 'Alex';

Common Index Concepts

• Single-column index: Uses one column.

• Composite index: Uses multiple columns.

• Unique index: Enforces uniqueness in supported configurations.

• Clustered index: Determines or strongly influences how table data is organized in certain database systems.

• Nonclustered index: Maintains a separate index structure referencing table records.

The exact meanings and availability of clustered and nonclustered indexes vary by database system.

Benefits of Indexing

• Faster lookups for suitable queries.

• Potentially faster joins.

• Improved sorting or filtering in some cases.

• Support for certain uniqueness constraints.

Indexing Trade-Offs

Indexes consume storage and can increase the cost of inserts, updates, and deletes.

Do not create indexes on every column automatically. Review query patterns, execution plans, table sizes, and write workloads.

9. Transactions and ACID Properties

A transaction groups database operations into a logical unit of work.

The ACID properties describe important transaction guarantees in transactional database systems.

Example: Bank Transfer

A transfer may involve two updates:

1. Deduct money from one account.

2. Add money to another account.

If only one update succeeds, the balances could become inconsistent. A transaction helps keep the related changes together.

Illustrative SQL:

BEGIN;

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

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

COMMIT;

A real implementation must validate the accounts, prevent inappropriate negative balances where required, handle failures, and use appropriate concurrency controls.

Transaction syntax and behavior vary by database.

10. Database Backup and Recovery

Backups help recover data after accidental deletion, corruption, hardware failure, or other incidents.

Common Backup Types

Full backup: Copies the entire selected database or backup scope.

Incremental backup: Captures changes since a relevant earlier backup, according to the backup system's design.

Differential backup: Captures changes since a defined full backup.

Logical backup: Exports database objects and data into a logical representation.

Physical backup: Copies database files or physical storage structures using supported procedures.

Not every DBMS supports all these methods in the same way.

Recovery Best Practices

• Define recovery time and recovery point objectives.

• Schedule backups according to business needs.

• Encrypt sensitive backup data.

• Store copies separately from production systems.

• Restrict access to backup files.

• Test restoration regularly.

• Document recovery procedures.

• Monitor backup failures.

A backup is not fully reliable until the recovery process has been tested.

11. Database Security

Database security protects information from unauthorized access, modification, disclosure, and destruction.

Essential Security Practices

• Apply least-privilege permissions.

• Use strong authentication.

• Protect credentials and connection strings.

• Encrypt data in transit using appropriate secure connections.

• Encrypt sensitive stored data where required.

• Patch and update database software.

• Audit important access and changes.

• Back up data securely.

• Validate application input.

• Use parameterized queries to help prevent SQL injection.

• Follow applicable privacy and retention requirements.

Example: Parameterized Query

Unsafe query construction can allow malicious input to change the meaning of a SQL statement.

In Python, a database library may support a parameterized query such as:

cursor.execute(
    "SELECT * FROM Employees WHERE employee_id = ?",
    (employee_id,)
)

The placeholder syntax varies by database driver. Use the parameter format documented by your specific library rather than assuming every driver accepts ?.

12. Database Performance Optimization

Performance optimization aims to improve query speed, resource usage, concurrency, and system responsiveness.

Practical Optimization Checklist

1. Identify slow queries.

2. Review the execution plan.

3. Retrieve only required columns.

4. Filter data efficiently.

5. Check join conditions.

6. Evaluate suitable indexes.

7. Avoid unnecessary repeated queries.

8. Monitor CPU, memory, storage, and connection usage.

9. Consider data volume and concurrent workloads.

10. Measure performance before and after changes.

Common Performance Problems

An optimization should be measured rather than assumed to work.

13. Relational vs. NoSQL Databases

These are general patterns, not universal limitations. Modern relational and NoSQL systems offer a wider range of capabilities than a simple comparison can capture.

How to Choose

Consider:

• How structured is the data?

• Which queries must run efficiently?

• Are relationships and joins central to the workload?

• What consistency guarantees are required?

• What is the expected data volume?

• What are the availability and scaling requirements?

• Which operational skills and infrastructure are available?

14. Data Warehouse vs. Database

A transactional database and a data warehouse serve different primary purposes, although their capabilities can overlap.

A typical architecture may use an operational database to capture transactions and a separate warehouse to analyze historical information.

Data may move through extraction, transformation, and loading or related ingestion processes before becoming available for analysis.

15. Database Design Best Practices

Good database design improves data integrity, maintainability, and application performance.

• Identify entities and their attributes before creating tables.

• Choose appropriate data types.

• Define primary keys.

• Establish valid foreign-key relationships.

• Apply necessary constraints.

• Normalize transactional data where appropriate.

• Avoid storing multiple unrelated values in a single field.

• Document important tables and columns.

• Consider indexing based on real query patterns.

• Plan for backups and recovery.

• Protect sensitive information.

• Test schema changes before production deployment.

Database design should reflect the application's real requirements rather than following rules mechanically.

16. Essential Database Terms

17. Database Interview Questions

What is a database?

An organized collection of data that can be stored, retrieved, updated, and managed.

What is a DBMS?

Software that provides facilities for managing databases.

What is the difference between SQL and NoSQL?

SQL is a language commonly used with relational databases. NoSQL refers to a broad category of non-relational database approaches.

What is a primary key?

A key that uniquely identifies a row in a table.

What is a foreign key?

A key that references a key in another table and can enforce referential integrity.

What is normalization?

The process of organizing relational data to reduce unnecessary duplication and certain data anomalies.

What is an index?

A data structure that helps the database locate records efficiently for suitable queries.

What does ACID stand for?

Atomicity, Consistency, Isolation, and Durability.

What is a database transaction?

A logical unit of database operations managed under transaction rules.

What is the difference between a database and a data warehouse?

Operational databases commonly support application transactions, while data warehouses are designed primarily for analytical workloads.

What is a database backup?

A recoverable copy of database data or structures.

What is a deadlock?

A situation in which transactions wait on one another in a way that prevents progress until the database resolves it.

18. Database Revision Checklist

Fundamentals

• Explain the difference between a database and a DBMS.

• Understand tables, rows, columns, and schemas.

• Compare relational and NoSQL databases.

• Identify primary and foreign keys.

• Understand one-to-one, one-to-many, and many-to-many relationships.

SQL and Design

• Write basic SELECT, INSERT, UPDATE, and DELETE statements.

• Explain normalization and common normal forms.

• Understand constraints and data types.

• Explain indexes and their trade-offs.

• Understand views, stored procedures, and triggers at a basic level.

Operations and Security

• Explain ACID properties.

• Understand backups and recovery.

• Recognize common database performance problems.

• Apply least-privilege access principles.

• Understand parameterized queries and SQL injection prevention.

• Explain the difference between operational databases and data warehouses.

Conclusion

Databases are fundamental to modern applications, analytics platforms, and business systems. Understanding database types, table structures, keys, relationships, normalization, indexing, transactions, and security provides a strong foundation for working with data.

Start with a relational database and practice designing tables, defining keys, writing SQL queries, and retrieving related records. Then explore performance optimization, backup strategies, and NoSQL models to understand how different database systems solve different problems.

Use this cheat sheet as a quick reference while learning database concepts, building applications, or preparing for technology interviews.