PHYSICAL DATABASE DESIGN

							PHYSICAL DATABASE
DESIGN
Overview
 Step 4: Translate global logical data model for target
  database
   Step4.1: Design base relations for target DBMS
   Step4.2: Design enterprise constraints for target DBMS
 Step 5: Design Physical representation
   Step5.1:   Analyze transactions
   Step5.2:   Choose file organizations
   Step5.3:   Choose secondary indexes
   Step5.4:   Consider the introduction of controlled redundancy
   Step5.5:   Estimate disk space requirements
 Step6: Design security mechanisms
   Step6.1: Design user views
   Step6.2: Design Access rules
 Step7: Monitor and tune the operational system
  Step 4: Translate global logical
  data model for target database
Objective: To produce a basic working
 relational database schema from the global
 logical data model.
  Whether the system supports the definition of primary keys,
   foreign keys, and alternate keys
  Whether the system supports the definition of required data
   (does the system allow attributes to be defined as NOT
   NULL)
  Whether the system supports the definition of domains.
  Whether the system supports the definition of enterprise
   constraints
  How to create base relations
Step4.1: Design base relations
for target DBMS

   The name of the relation
   A list of simple attributes
   The primary key and, alternate keys and foreign keys
   Integrity constraints for identified foreign key

 Domains: data type, length and any constraints on the
  domain
 An optional default value of the attribute
 Whether the attribute is derived and, if so, how it should be
  computed
Step4.2: Design enterprise
constraints for target DBMS

Objective: To design the enterprise
 constraints for the target DBMS
  SQL CREATE TABLE statement for
   Property_for_Rent
     CONSTRAINT staff_not_handling_too_much
        CHECK (NOT EXISTS (SELECT sno
                          FROM property_for_rent
                          GROUP BY sno
                          HAVING COUNT (*)>10))


•Document Design of enterprise constraints
Step 5: Design Physical
representation

 Objective: To determine the file organizations and
  access methods
   Step 5.1: Analyze transactions
   Step 5.2: Choose file organizations
   Step 5.3: Choose secondary indexes
   Step 5.4: Consider the introduction of controlled
    redundancy
   Step 5.5: Estimate disk space requirements
Step5.1: Analyze transactions
Expected frequency at which the transaction
 will run
Relations and attributes accessed by the transaction
  Query, insert, update or delete
    Attributes that are updated
Conditions in WHERE clause
  pattern matching, range searches or exact match
   key retrieval
Time constraints imposed on the transaction
  e.g.: within 1 second.
Step5.2: Choose file organizations
Objective: Effect of adding secondary indexes
 on the performance

  CREATE INDEX property_for_rent ON property_for_rent(rent);
     Adding an index record to every secondary index
      whenever a record is inserted
     Updating a secondary index when the corresponding
      record in the relation is updated
     More disk space to store a secondary index.
     Possible performance degradation during query
      optimization

•Document choice of secondary indexes
Step5.4: Consider the introduction
of controlled redundancy

 Denormalization
    Makes implementation more complex
   Often sacrifices flexibility
   Speed up retrievals but it slows down
    updates
 Step5.4.: Consider derived attributes
 Step5.4.2: Consider duplicating attributes
  or joining relations together
  Denormalize in the
  following situations
Combine 1:1 relationships
Duplicating non key attributes in 1:M relationships to
 reduce joins
Reference tables
Duplicating tables
Duplicating foreign key attributes in 1:M relationships
 to reduce joins
Duplicating attributes in M:M relationships to reduce
 joins
Introducing repeating groups
Creating extract tables
  Step 6: Design Security
  Mechanisms
Step6.1: Design user Views
      Objective: To design the user views that were identified
       in Step1 of the conceptual database design methodology
CREATE VIEW staff3
AS SELECT sno,lname,fname,address,tel_no
  FROM staff
  WHERE bno=‘B3’;
Step6.2: Design access rules
      Objective: To design the access rules to the base
       relations and user views.
Document design of user views and security
 measures
Step7: Monitor and Tune
the Operational System

Objective:
  To monitor the operational system and
   improve the performance of the system to
   correct inappropriate design decisions or
   reflect changing requirements.