Database Systems: Lecture 11 - Practical Database Design and Tuning presents about Practical Database Design and Tuning (Physical Database Design in Relational Databases, An Overview of Database Tuning in Relational Systems). | Practical Database Design and Tuning Outline Practical Database Design and Tuning Physical Database Design in Relational Databases An Overview of Database Tuning in Relational Systems Reading: Physical Design: Chapter 16 [1] Physical Database Design in Relational Databases Factors that Influence Physical Database Design: A. Analyzing the database queries and transactions For each query, the following information is needed. The files that will be accessed by the query; The attributes on which any selection conditions for the query are specified; The attributes on which any join conditions or conditions to link multiple tables or objects for the query are specified; The attributes whose values will be retrieved by the query. Note: the attributes listed in items 2 and 3 above are candidates for definition of access structures. Physical Database Design in Relational Databases Factors that Influence Physical Database Design (contd.): A. Analyzing the database queries and . | Practical Database Design and Tuning Outline Practical Database Design and Tuning Physical Database Design in Relational Databases An Overview of Database Tuning in Relational Systems Reading: Physical Design: Chapter 16 [1] Physical Database Design in Relational Databases Factors that Influence Physical Database Design: A. Analyzing the database queries and transactions For each query, the following information is needed. The files that will be accessed by the query; The attributes on which any selection conditions for the query are specified; The attributes on which any join conditions or conditions to link multiple tables or objects for the query are specified; The attributes whose values will be retrieved by the query. Note: the attributes listed in items 2 and 3 above are candidates for definition of access structures. Physical Database Design in Relational Databases Factors that Influence Physical Database Design (contd.): A. Analyzing the database queries and transactions (contd.) For each update transaction or operation, the following information is needed. The files that will be updated; The type of operation on each file (insert, update or delete); The attributes on which selection conditions for a delete or update operation are specified; The attributes whose values will be changed by an update operation. Note: the attributes listed in items 3 above are candidates for definition of access structures. However, the attributes listed in item 4 are candidates for avoiding an access structure. Physical Database Design in Relational Databases Factors that Influence Physical Database Design (contd.): B. Analyzing the expected frequency of invocation of queries and transactions The expected frequency information, along with the attribute information collected on each query and transaction, is used to compile a cumulative list of expected frequency of use for all the queries and transactions. It is expressed as the expected frequency of using
