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
- Explain the purpose and trade-offs of creating indexes
- Explain the purpose and trade-off ofclustering
- Explain the purpose and trade-offs of horizontal partitioning:
- Explain the purpose and trade-offs of 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 thedata dictionary
- Describe the tables that are part of the data dictionary
The next lesson introduces you to 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.
An index is an ordered list of values in a field, exactly like the index in the back of a book.
Clustering brings records from two or more tables together on a hard disk to improve joins between the two tables.
Horizontal partitioning divides a table's records into two or more tables
To divide a table into multiple tables, placing different columns in each new table. This type of partition divides the table vertically, which means that the structure of the main table changes in the new tables which are generated.
: 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.