Physical Database Design Conclusion
This module examined the steps involved in the physical design and implementation stages of the database life cycle.
In particular, it described and weighed the trade-offs inherent to indexing, clustering, and horizontal and vertical partitioning.
It also looked at using SQL to create a database and how an RDBMS might represent that database internally.
Having completed this module, you should be able to:
- Describe physical design in terms of optimizing database performance
- Explain the purpose and trade-offs involved in creating indexes
- Explain the purpose and trade-off involved in clustering
- Explain the purpose and trade-offs involved in horizontal and vertical partitioning
- Explain how to create a database with SQL
- Explain how to use SQL to create database tables
- Explain how to use SQL to build a database
- Describe the entries that appear in the data dictionary
- Describe the tables that are part of the data dictionary
This module introduced you to the following terms:
- block: The smallest area that can be addressed on a magnetic disk.
- catalog: Also called the data dictionary. The catalog is the foundation of the database, it's where the RDBMS finds which tables are in a database, which columns are in the tables, which columns are primary or foreign keys, and what type of data to expect in those columns.
- cluster: A group of table columns or rows often accessed together.
- clustering: Clustering brings records from two or more tables together on a hard disk to improve joins between the two tables.
- data dictionary: Also called a catalog. The data dictionary is the foundation of the database, it is where the RDBMS finds which tables are in a database, which columns are in the tables, which columns are primary or foreign keys, and what type of data to expect in those columns.
- horizontal partitioning: Horizontal partitioning divides a table\'s records into two or more tables
- implementation: Using SQL to create a database; the fourth step in the database life cycle.
- index: An index is an ordered list of values in a field, exactly like the index in the back of a book.
- indexing: Creating a list of values in a column to speed searches, joins, and queries.
- partitioning: The process of breaking tables into parts to improve retrieval performance.
- physical design: Partitioning a table, in database lexicon, divides a single table into two or more tables to limit the amount of data the RDBMS has to retrieve at once. There are two types of partitioning operations: 1) Horizontal partitioning, which splits a table's records into two or more tables . 2) Vertical partitioning, which splits a table's columns into two or more tables.
- schema: The overall logical plan of a database.
- SQL: SQL is an acronym for Structured Query Language. It provides a set of commands that can be used to add data to a database, retrieve that data, and update it. SQL, often pronounced “sequel”, is universally supported by relational database vendors.
- Vertical partitioning: To divide a table into multiple tables, placing different columns in each new table.
The next module describes common design mistakes and strategies for avoiding them.
Physical Design - Quiz
Before you move to the next module, click the Quiz link below to reinforce your understanding of physical design.
Physical Design - Quiz