Lesson 17

This module brought together the core activities of physical database design—the step in the database life cycle where a logical model is transformed into an implementation that performs well under real workloads.

Starting from the introduction to physical design, you examined how decisions about storage structures and access paths affect performance and maintainability:

• How to choose and create indexes with SQL, and when excessive indexing can become a liability.
• How clustering related rows or tables physically on disk can reduce I/O for common join patterns.
• How horizontal partitioning (splitting rows) and vertical partitioning (splitting columns) can improve query response time and manage very large tables.
• How SQL DDL statements are used to create databases, schemas, and tables that reflect the logical design.
• How an RDBMS captures all of this structure in its data dictionary and underlying catalog tables.

The key message of this module is that physical design is not an afterthought. It is an intentional process of aligning the logical model with indexing, clustering, and partitioning strategies so that the database can meet performance, scalability, and operational requirements.

Having completed this module, you should now be able to:

1. Relate physical design to performance Explain how physical design choices (storage layout, access paths, and partitioning) support query and transaction performance.
2. Evaluate indexing strategies Explain why indexes are created, how they accelerate lookups and joins, and what trade-offs exist in terms of storage, maintenance, and write overhead.
3. Explain clustering and its trade-offs Describe how clustering places related rows (or tables) together physically and how this can improve join performance while adding complexity to data maintenance.
4. Analyze horizontal and vertical partitioning Explain when to use horizontal partitioning (by rows) and vertical partitioning (by columns), and how each approach can improve performance or manageability for large tables.
5. Create a database with SQL Understand how SQL DDL statements are used to define databases, schemas, and storage structures in a modern RDBMS.
6. Create database tables using SQL Use CREATE TABLE to define base tables, including columns, data types, primary keys, foreign keys, and other constraints.
7. Use SQL to build a complete database Assemble schemas, tables, constraints, and views into a coherent database that reflects the logical data model.
8. Describe entries in the data dictionary Identify the metadata stored for tables, columns, keys, indexes, and other objects in a completed data dictionary.
9. Describe data dictionary tables Recognize the key system tables (or views) that make up the data dictionary and understand how they document the physical implementation.

The following glossary terms summarize important concepts introduced in this module.

1. block: The smallest addressable unit of storage on disk used by the RDBMS for reading and writing data pages.
2. catalog: Also called the data dictionary. The internal set of system tables and views that describes all database objects, their attributes, and their relationships.
3. cluster: A group of rows (or related tables) stored together on disk to reduce I/O for queries that access them together frequently.
4. clustering: The physical arrangement of data from one or more tables so that rows commonly joined or accessed together are stored close to each other to improve performance.
5. data dictionary: The authoritative metadata repository (catalog) where the RDBMS records tables, columns, indexes, constraints, permissions, and other structural information.
6. horizontal partitioning: Splitting a table’s rows into two or more tables or partitions, each holding a subset of rows (for example, by date range or region).
7. implementation: The life-cycle stage where the logical design is realized physically by using SQL DDL to create databases, schemas, tables, indexes, and other objects.
8. index: A separate structure that stores ordered key values and pointers to table rows, used to speed searches, joins, and filtering operations.
9. indexing: The process of defining and maintaining indexes on one or more columns to improve query performance, with trade-offs in storage and write cost.
10. partitioning: The general process of dividing a logical table into smaller, more manageable physical pieces to improve performance, maintenance, or availability.
11. physical design: The phase of database design that maps the logical model onto physical structures such as files, tablespaces, indexes, clusters, and partitions to meet performance and operational goals.
12. schema: The logical container (or namespace) that groups related database objects such as tables, views, indexes, and procedures.
13. SQL (Structured Query Language): The standard language for defining, querying, and manipulating relational data, including DDL, DML, and DCL statements.
14. vertical partitioning: Splitting a table into multiple tables by columns, placing different groups of columns in separate tables while preserving a shared key to join them.

In the next module, you will examine common design mistakes and practical strategies for avoiding them, building on the physical design concepts you have learned here.

Before moving to the next module, use the quiz below to reinforce your understanding of physical design concepts—indexing, clustering, partitioning, SQL implementation, and the data dictionary.
Physical Design – Quiz