PHYSICAL%20DATABASE%20DESIGN

1
PHYSICAL DATABASE DESIGN
2
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

3
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

4
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

5
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 ()gt10))

• Document Design of enterprise constraints

6
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

7
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.

8
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

9
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

10
Denormalize in the following situations

• Combine 11 relationships
• Duplicating non key attributes in 1M
  relationships to reduce joins
• Reference tables
• Duplicating tables
• Duplicating foreign key attributes in 1M
  relationships to reduce joins
• Duplicating attributes in MM relationships to
  reduce joins
• Introducing repeating groups
• Creating extract tables

11
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 bnoB3
• 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

12
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.