Database Design Fundamentals

Database Design

• 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.

SQL Queries

• 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.

Transaction Management

• 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.

Data Modeling

• 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.

Indexing and Optimization

• 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

Database Design

• 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

• 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.

SQL Queries

• 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.

Transaction Management

• 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.

Data Modeling

• 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.

Indexing and Optimization

• 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.