PHYSICAL DATABASE DESIGN
Shared by: akm33296
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.