Database Design Fundamentals

Study Notes

Schema: A collection of database objects, including tables, views, and indexes.
Tables: Relations with rows and columns, similar to spreadsheets.
Views: Virtual tables based on the result of a SQL query.
Indexes: Data structures that improve query performance by providing quick access to specific data.
Normalization: Process of organizing data to minimize data redundancy and improve data integrity.
- First Normal Form (1NF): Each row/column intersection contains a single value.
- Second Normal Form (2NF): Each non-key attribute depends on the entire primary key.
- Third Normal Form (3NF): If a table is in 2NF, and a non-key attribute depends on another non-key attribute, then it should be moved to a separate table.

DDL (Data Definition Language): Statements that define database schema, such as CREATE, ALTER, and DROP.
DML (Data Manipulation Language): Statements that manipulate data, such as INSERT, UPDATE, and DELETE.
DQL (Data Query Language): Statements that retrieve data, such as SELECT.
Query Optimization: Process of improving query performance by optimizing the execution plan.
Subqueries: Queries nested inside another query.
Joins: Combining data from multiple tables based on a common column.

ACID (Atomicity, Consistency, Isolation, Durability): Properties that ensure database transactions are reliable and secure.
Transaction Isolation: Levels of isolation that define how transactions interact with each other.
- Read Uncommitted: Allows a transaction to read uncommitted changes.
- Read Committed: Ensures a transaction only reads committed changes.
- Repeatable Read: Ensures a transaction sees a consistent view of the data.
- Serializable: Ensures a transaction sees a consistent view of the data, as if it was the only transaction.
Locking: Mechanism that prevents concurrent transactions from accessing the same data.

Entity-Relationship Model (ERM): Conceptual representation of data as entities, attributes, and relationships.
Relational Model: Data organized into tables with well-defined schemas.
Object-Relational Mapping (ORM): Technique that maps objects to relational tables.

Index Types: Different types of indexes, including:
- B-Tree Index: Self-balancing index that keeps data sorted.
- Hash Index: Index that uses a hash function to map keys to locations.
- GiST Index: Index that supports range queries and nearest-neighbor searches.
Indexing Strategies: Techniques to improve query performance, including:
- Indexing columns used in WHERE clauses
- Indexing columns used in JOIN operations
- Indexing columns used in ORDER BY and GROUP BY clauses
Query Optimization Techniques: Methods to improve query performance, including:
- Reordering joins
- Rewriting subqueries
- Using efficient data types

A schema is a collection of database objects, including tables, views, and indexes.
Tables are relations with rows and columns, similar to spreadsheets.
Views are virtual tables based on the result of a SQL query.
Indexes are data structures that improve query performance by providing quick access to specific data.

Normalization is the process of organizing data to minimize data redundancy and improve data integrity.
First Normal Form (1NF) states that each row-column intersection contains a single value.
Second Normal Form (2NF) states that each non-key attribute depends on the entire primary key.
Third Normal Form (3NF) states that if a table is in 2NF, and a non-key attribute depends on another non-key attribute, then it should be moved to a separate table.

DDL (Data Definition Language) statements define database schema, such as CREATE, ALTER, and DROP.
DML (Data Manipulation Language) statements manipulate data, such as INSERT, UPDATE, and DELETE.
DQL (Data Query Language) statements retrieve data, such as SELECT.
Query optimization is the process of improving query performance by optimizing the execution plan.
Subqueries are queries nested inside another query.
Joins combine data from multiple tables based on a common column.

ACID (Atomicity, Consistency, Isolation, Durability) properties ensure database transactions are reliable and secure.
Transaction isolation levels define how transactions interact with each other.
Read Uncommitted allows a transaction to read uncommitted changes.
Read Committed ensures a transaction only reads committed changes.
Repeatable Read ensures a transaction sees a consistent view of the data.
Serializable ensures a transaction sees a consistent view of the data, as if it was the only transaction.
Locking prevents concurrent transactions from accessing the same data.

The Entity-Relationship Model (ERM) is a conceptual representation of data as entities, attributes, and relationships.
The Relational Model organizes data into tables with well-defined schemas.
Object-Relational Mapping (ORM) maps objects to relational tables.

There are different types of indexes, including B-Tree, Hash, and GiST indexes.
Indexing strategies improve query performance by indexing columns used in WHERE clauses, JOIN operations, and ORDER BY and GROUP BY clauses.
Query optimization techniques improve query performance by reordering joins, rewriting subqueries, and using efficient data types.