Database Partitioning: Principles, Methods, and Features
Database Partitioning Principles
Completeness: Every piece of data in the original table must appear in at least one partition.
Why? This ensures no data is lost during the partitioning process.
Example: If a Customers table is partitioned into regions, all customers must belong to one of the regional partitions.
Reconstruction: It must be possible to reconstruct the original table by combining the partitions.
Why? This ensures that partitioning doesn’t make it impossible to retrieve the full dataset when needed.
Example: Rejoining horizontally partitioned tables (e.g., “North Customers” and “South Customers”) should recreate the complete Customers table.
Disjointness: Data must belong to only one partition unless explicitly allowed (e.g., in derived partitioning).
Why? Ensures no duplicate data across partitions, preventing redundancy.
Example: If customers are divided into partitions based on Region, no customer record should appear in two partitions.
Partitioning Methods
| Method | Best For | Advantages | Disadvantages |
|---|---|---|---|
| Heap | Fast insertion/appending. | Simple, quick insertions. | Slow lookups, full table scans needed. |
| Sequential | Sorted or range-based queries. | Efficient for ordered data. | Expensive insertions/deletions. |
| Hash | Exact-match queries. | Fast lookups, simple hashing. | Inefficient for range queries. |
| B-tree | Mixed queries (range/equality). | Balanced, efficient for indexing. | More complex than hash/heap. |
| Cluster | Range queries, reducing I/O. | Physically groups related data. | Expensive maintenance for updates. |
Database System Components
Database: A structured collection of data that is stored electronically. It organizes data in such a way that it can be easily accessed, managed, and updated.
DBMS: A software tool that facilitates the creation, management, and interaction with databases. It allows users and applications to query, insert, update, and delete data while ensuring integrity and security.
DbAplktn: A software program or tool that interacts with the database and DBMS to perform specific tasks, such as data entry, reporting, or analysis.
DbSys: A complete system that encompasses the database, the DBMS, the database applications, and the underlying hardware and software infrastructure.
Database Views
External: How individual users view and interact with the database. It provides a customized view of the database tailored to specific user needs.
Conceptual: This level represents the logical structure of the entire database, independent of how the data is stored physically.
Internal: This level defines the physical storage of the database, including how data is organized, partitioned, and accessed on disk.
Data Independence
Logical data independence: Ability to change the logical schema of a database without affecting the external schema or application programs that interact with the database. e.g., adding a column.
Physical data independence: Ability to change the physical schema of a database without affecting the logical schema or the external schema. e.g., change to heap.
Codd’s 8 Functions for DBMS
D.Definition, Retrieval, Manipulation, Integrity, Security, Transaction Management, Concurrency Control, Data Recovery.
Relational Model Features
Data Structure: The relational model uses relations (tables), which consist of tuples (rows) and attributes (columns). Each attribute has a specific domain.
Operators: Operations in the relational model include those defined by relational algebra (e.g., selection, projection, union) and relational calculus (e.g., tuple relational calculus, domain relational calculus) for manipulating and retrieving data.
Constraints: Keys (primary and foreign) and integrity constraints (entity, referential, domain) ensure data consistency and integrity.
These three features collectively define the structure, manipulation, and integrity of data in a relational database system, ensuring that data is organized, accessible, and consistent.
Database Constraints
Constraints: Domain, Entity Integrity, Referential Integrity, Enterprise