1 / 12

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

ora-hudson
Download Presentation

PHYSICAL DATABASE DESIGN

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 (*)>10)) • 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 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

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

  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.

More Related