16

							Chapter 16

               Methodology
         Physical Database Design
             Transparencies
Chapter 16 - Objectives
    The   purpose of physical database design.

    How to map the logical database design to a
    physical database design.

       to design base relations for the target
    How
    DBMS.



                                                   2
Chapter 16 - Objectives
    How to design enterprise constraints for the
    target DBMS.

    How to select appropriate file organizations
    based on analysis of transactions.

    When to use secondary indexes to improve
    performance.

                                                    3
Chapter 16 - Objectives
    When   to denormalize to improve performance.

    How to design security mechanisms to satisfy
    user requirements.




                                                     4
Logical and Physical Database Design

    Sources  of information for the physical design
     process includes global logical data model and
     documentation that describes model.

    Logical database design is concerned with the
     what, physical database design is concerned
     with the how.



                                                       5
Physical Database Design

    The process of producing a description of the
     implementation of the database on secondary
     storage.

    Describesthe storage structures and access
     methods used to achieve efficient access to the
     data.



                                                       6
Overview of Physical Database Design
Methodology

    Step 1
     Translate global logical data model for target
     DBMS
    Step 2
     Design physical representation
    Step 3
     Design security mechanisms
Step 1: Translate global logical data
model for target DBMS

 Toproduce a basic working relational database
 schema from the global logical data model

  –   Design base relations for target DBMS
  –   Design enterprise constraints for target DBMS




                                                      7
Step 1: Translate global logical data
model for target DBMS (continued)

    Design    base relations for target DBMS
      –   To decide how to represent the base
          relations we have identified in the global
          logical data model in the target DBMS.

    Design    enterprise constraints for target DBMS
      –   To design the enterprise constraints for the
          target DBMS.
SQL to create Property_for_Rent Relation
Step 2 : Design physical representation
     To determine the file organizations and access methods
     that will be used to store the base relations; that is, the
     way in which relations and tuples will be held on
     secondary storage.

      –   2.1 Analyze transactions
      –   2.2 Choose file organizations
      –   2.3 Choose secondary indexes
      –   2.4 Consider the introduction of controlled
          redundancy
      –   2.4 Estimate disk space requirements
Step 2 Design Physical Representation

    Step   2.1 Analyze transactions
      –   To understand the functionality of the
          transactions that will run on the database
          and to analyze the important transactions.

    Step   2.2 Choose file organizations
      –   To determine an efficient file organization
          for each base relation.

                                                        16
Typical Disk Configuration
Step 2.1 Analyze transactions

    For each Transaction associated with the
     components of the data model (usually
     predefined queries including view, trigger,
     procedure, function and package), it needs to
     be broken down into further smaller units of
     work:
Transactions Analysis (continued)
      A. Transformation Rules: Describe the rules (R,U,I,
       D) or algorithms used to transform data received into
       data generated.

      B. Edit and Error Rules: Define the rules validating
       data received and the method of processing erroneous
       data.

      C. Sequence Analysis: Describe under what
       conditions this transaction is performed and what rules
       determine which transaction will be performed next.
Cross-referencing Transactions and
Relations
Transactions Analysis (continued)

    D.   Audit Rules: Describe the rules required
     to audit the activity performed within this
     transaction.

    E.  Security Rules: Define the security
     required to invoke the transaction or various
     facets of the transaction.
Transactions Analysis (continued)

    F.   Frequency of execution: Define the
     number of times this transaction is performed
     in a fixed period of time.

    G.   Type of transaction mode: Describe
     whether the transaction is batch, on demand,
     or interactive.
Example - Sample Transactions

   (A)   Insert details for a new member of staff, given
         the branch address.
   (B)   List rental properties handled by each staff
         member at a given branch address.
   (C)   Assign a rental property to a member of staff,
         checking that a staff member does not manage
         more than 10 properties already.
   (D)   List rental properties handled by each branch
         office.
                                                       17
ER Model for Sample Transactions showing
Expected Occurrences




                                           18
Analysis of Selected Transaction A




                                     20
Analysis of Selected Transaction B




                                     21
Analysis of Selected Transaction C




                                     22
Step 2 Design Physical Representation
(continued)
   Step 2.3 Choose secondary indexes
     – To determine whether adding secondary indexes will
       improve the performance of the system.

   Step 2.4 Consider the introduction of controlled redundancy
     – To determine whether introducing redundancy in a
       controlled manner by relaxing the normalization rules will
       improve the performance of the system.




                                                               23
Step 2.3 Choose secondary indexes

   Data File: The file contains the logical record.
   Index File: The file contains the index file.

    The  values in the index file are ordered per the
     indexing field which is usually based on a
     single attribute.
Indexes


    Primary     index: The indexing field is
     guaranteed to have a unique value.
    Secondary Index: An index that is defined on a
     non-ordering field of of the data.
    Clustering index: If the index field is not a key
     field of the file, so that there can be more than
     one record corresponding to a value of the
     indexing field.
Step 2.4 Consider the introduction of
controlled redundancy

    Simplified Relation with Derived Attribute
    Duplicating Attribute
    Setting up Lookup Table
    Duplicating Foreign Key
Simplified Staff Relation with Derived
Attribute No_of_Properties




                                         25
Original Renter and Interview Relations




                                          26
Combined Renter and Interview Relations




                                          27
Original Property_for_Rent and Owner
Relations




                                       28
Duplicating LName Attribute in the
Property_for_Rent Relation




                                     29
Lookup Table for Property_Type Attribute




                                           30
Modified Property_for_Rent Relation with
Duplicated Description Attribute




                                           31
Duplicating Foreign Key Bno in Owner
Relation




                                       32
Original Property_for_Rent, Renter, and
Viewing Relations




                                          33
Duplicating Street Attribute in Viewing
Relation




                                          34
Step 2 Design Physical Representation
(Continued)

    Step   2.5 Estimate disk space requirements
      –   To estimate the amount of disk space that
          will be required by the database.




                                                      37
Step 3 Design Security Mechanisms

    To design the security measures for the
     database as specified by the users.




                                               39
Step 3 Design Security Mechanisms

    Step   3.1 Design user views
     –   To design the user views that were identified
         in Step 1 of the conceptual database design
         methodology.

    Step  3.2 Design access rules
     –   To design the access rules to the base
         relations and user views.

                                                     40
Staff3 View Listing




                      41