Lesson 17

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:

1. Describe physical design in terms of optimizing database performance
2. Explain the purpose and trade-offs involved in creating indexes
3. Explain the purpose and trade-off involved in clustering
4. Explain the purpose and trade-offs involved in horizontal and vertical partitioning
5. Explain how to create a database with SQL
6. Explain how to use SQL to create database tables
7. Explain how to use SQL to build a database
8. Describe the entries that appear in the data dictionary
9. Describe the tables that are part of the data dictionary

This module introduced you to the following terms:

1. block: The smallest area that can be addressed on a magnetic disk.
2. 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.
3. cluster: A group of table columns or rows often accessed together.
4. clustering: Clustering brings records from two or more tables together on a hard disk to improve joins between the two tables.
5. 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.
6. horizontal partitioning: Horizontal partitioning divides a table\'s records into two or more tables
7. implementation: Using SQL to create a database; the fourth step in the database life cycle.
8. index: An index is an ordered list of values in a field, exactly like the index in the back of a book.
9. indexing: Creating a list of values in a column to speed searches, joins, and queries.
10. partitioning: The process of breaking tables into parts to improve retrieval performance.
11. 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.
12. schema: The overall logical plan of a database.
13. 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.
14. 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.

Before you move to the next module, click the Quiz link below to reinforce your understanding of physical design.
Physical Design - Quiz