📄️ Introduction
Welcome to the Database Design handbook.
📄️ Fundamentals
Database vs DBMS
📄️ Data Model
A data model is a blueprint for how data will be structured and managed. Database design usually progresses through three levels:
📄️ ER Diagram
An Entity–Relationship (ER) Diagram is a type of diagram used in database design to visually represent the structure of a database. It shows how entities (tables) in a system are related to each other, along with their attributes and the type of relationships they share.
📄️ Normalization
Functional Dependency
📄️ Denormalization
- Denormalization is the process of intentionally introducing redundancy into a database by combining tables or adding derived data.
📄️ Relational Model
Domains in the Relational Model
📄️ Relationship Attributes
In an ER model, sometimes the relationship itself (not just the entities) needs to store additional information.
📄️ Recursive Relationships
A recursive relationship (also called self-referencing relationship) occurs when an entity is related to itself.
📄️ Identifying vs Non-Identifying
In an ER Diagram, a relationship between entities can either be:
📄️ Specialization
Specialization is the process of dividing a higher-level entity (superclass) into two or more lower-level entities (subclasses) based on some distinguishing characteristic.
📄️ Generalization
Generalization is the opposite of specialization. It is the process of combining two or more lower-level entities into a higher-level entity (superclass) based on their common attributes.
📄️ Aggregation
Aggregation is an abstract relationship used when we need to represent a relationship between a relationship and an entity.
📄️ Mapping Business Rules
To create a database from this, we need to translate requirements into ERD components:
📄️ Partitioning
Partitioning = dividing a large table (or index) into smaller, manageable pieces (partitions), while still treating it as one logical table in SQL.
📄️ Sharding
Sharding = a form of horizontal partitioning across multiple servers (databases).
📄️ Storage Consideration
When we talk about storage, we mean how data is physically stored on disk (or SSD, memory). This includes:
📄️ ACID Properties
A transaction in a database is a single logical unit of work that can consist of one or multiple SQL operations (like INSERT, UPDATE, DELETE, SELECT). Transactions are crucial because they ensure data consistency and reliability, even in cases of system failures or concurrent access.
📄️ Integrity
Integrity ensures that the data stored in a database is accurate, consistent, and reliable.
📄️ Referential Integrity
Referential Integrity ensures that relationships between tables remain consistent and valid.
📄️ Cascading
When we design relational databases with foreign keys, we can define what happens to child records when the parent record is updated or deleted.
📄️ Transaction Management
A transaction is a sequence of one or more SQL operations (INSERT, UPDATE, DELETE, SELECT) that form a single logical unit of work.
📄️ Database Security
User Roles and Privileges