Encyclopedia of Database Systems

2018 Edition
| Editors: Ling Liu, M. Tamer Özsu

Physical Database Design for Relational Databases

  • Sam S. Lightstone
Reference work entry
DOI: https://doi.org/10.1007/978-1-4614-8265-9_644

Synonyms

Clustering; Database design; Database implementation; Database materialization; Indexing; Materialized query tables; Materialized views; Multidimensional clustering; Range partitioning; Table design; Table normalization

Definition

Physical database design represents the materialization of a database into an actual system. While logical design can be performed independently of the eventual database platform, many physical database attributes depend on the specifics and semantics of the target DBMS. Physical design is performed in two stages:
  1. 1.

    Conversion of the logical design into table definitions (often performed by an application developer): includes pre-deployment design, table definitions, normalization, primary and foreign key relationships, and basic indexing.

     
  2. 2.

    Post deployment physical database design (often performed by a database administrator): includes improving performance, reducing I/O, and streamlining administration tasks.

     

Generally speaking, physical...

This is a preview of subscription content, log in to check access.

Recommended Reading

  1. 1.
    Astrahan MM, Blasgen MW, Chamberlin DD, Jim Gray W, King FII, Lindsay BG, Lorie RA, Mehl JW, Price TG, Putzolu GR, Schkolnick M, Selinger PG, Slutz DR, Strong HR, Tiberio P, Traiger IL, Bradford W, Yost WRA. System R: a relational data base management system. IEEE Comput. 1979;12(5):42–8.CrossRefGoogle Scholar
  2. 2.
    Bayer R, McCreight EM. Organization and maintenance of large ordered indexes. SIGFIDET Workshop; 1970. p. 107–41.Google Scholar
  3. 3.
    Finkelstein S, Schikolnick M, Tiberio P. Physical database design for relational databases. ACM Trans Database Syst. 1988;13(1):91–128.CrossRefGoogle Scholar
  4. 4.
    Jun R, Chun Zhang, Megiddo N, Lohman GM. Automating physical database design in a parallel database. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 2002. p. 558–69.Google Scholar
  5. 5.
    Lightstone S, Bishwaranjan B. Automated design of multi-dimensional clustering tables for relational databases. In: Proceedings of the 30th International Conference on Very Large Data Bases; 2004. p. 1170–181.CrossRefGoogle Scholar
  6. 6.
    Lightstone S, Teory T, Nadeau T. Physical database design: the database professional’s guide to exploiting indexes, views, storage, and more. Morgan Kaufmann; 2007.Google Scholar
  7. 7.
    Markos Z, Cochrane R, Lapis G, Hamid P, Monica U. Answering complex SQL queries using automatic summary tables. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 2000. p. 105–16.Google Scholar
  8. 8.
    Sanjay A, Surajit C, Narasayya VR. Automated selection of materialized views and indexes in SQL databases. In: Proceedings of the 26th International Conference on Very Large Data Bases; 2000. p. 496–505.Google Scholar
  9. 9.
    Sriram P, Bishwaranjan B, Malkemus T, Cranston L, Huras M. Multi-dimensional clustering: a new data layout scheme in DB2. In: Proceedings of the ACM SIGMOD International Conference on Management of Data. p. 637–41.Google Scholar
  10. 10.
    Surajit C, Narasayya VR. AutoAdmin “What-if” index analysis utility. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 1998. p. 367–78.Google Scholar
  11. 11.
    Surajit C, Narasayya VR. Microsoft index tuning wizard for SQL Server 7.0. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 1998. p. 553–4.Google Scholar
  12. 12.
    Teorey T, Lightstone S, Nadeau T. Database modeling & design: logical design. 4th ed. Morgan Kaufmann; 2005.Google Scholar
  13. 13.
    Zilio DC, Jun R, Lightstone S, Lohman GM, Storm AJ, Garcia-Arellano Christian, Fadden Scott. DB2 design advisor: integrated automatic physical database design. In: Proceedings of the 30th International Conference on Very Large Data Bases; 2004. p. 1087–97.Google Scholar
  14. 14.
    Zilio DC, Zuzarte C, Lightstone S, Wenbin Ma, Lohman GM, Cochrane R, Hamid P, Latha SC, Gryz J, Alton E, Liang D, Valentin G. Recommending materialized views and indexes with IBM DB2 design advisor. In: Proceedings of the 1st International Conference on Autonomic Computing; 2004. p. 180–8.Google Scholar

Copyright information

© Springer Science+Business Media, LLC, part of Springer Nature 2018

Authors and Affiliations

  1. 1.IBM Canada LtdMarkhamCanada

Section editors and affiliations

  • Alexander Borgida
    • 1
  1. 1.Rutgers UniversityNew BrunswickUSA