Introduction to Physical Design of a Database
Up to now, this course has dealt with the logical design phase of the database life cycle (DBLC).
You laid out the database's tables, created links between the tables, normalized the tables so they will perform with maximum efficiency,
designed user views to create information from table data, and assigned permissions to users to ensure they made no unexpected changes to the database.
It is time now to investigate the third phase of the DBLC, physical design: how the computer stores the database on its hard drive.
You will learn how to change the physical design in ways that might improve database performance. Yes, "might." Unlike normalizing tables,
which verifiably improves database performance, the techniques you will learn in this module must be tried on a specific database before
their effects can be known. As you will see, changing the physical design of a database is just as likely to hurt performance as to help it.
After completing this module, you should be able to:
Describe physical design in terms of optimizing database 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:
- Horizontal partitioning, which splits a table's records into two or more tables
- Vertical partitioning, which splits a table's columns into two or more tables.
- Explain the purpose and trade-offs of creating indexes
index: An index is an ordered list of values in a field, exactly like the index in the back of a book.
- Explain the purpose and trade-off ofclustering:
clustering: Clustering brings records from two or more tables together on a hard disk to improve joins between the two tables.
- Explain the purpose and trade-offs of horizontal partitioning:
Horizontal partitioning divides a table's records into two or more tables
- Vertical partitioning: To divide a table into multiple tables, placing different columns in each new table.
- 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 thedata dictionary
data dictionary: Also called a catalog. The data dictionary is the foundation of the database and 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.
- Describe the tables that are part of the data dictionary
The next lesson introduces you to physical design.