Transcript 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:
Step5.2:
Step5.3:
Step5.4:
Step5.5:
Analyze transactions
Choose file organizations
Choose secondary indexes
Consider the introduction of controlled redundancy
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.