1 / 41

Chapter 16

Chapter 16. Methodology Physical Database Design Transparencies. Chapter 16 - Objectives. The purpose of physical database design. How to map the logical database design to a physical database design. How to design base relations for the target DBMS. 2. Chapter 16 - Objectives.

frederick-corbin
Download Presentation

Chapter 16

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. Chapter 16 Methodology Physical Database Design Transparencies

  2. Chapter 16 - Objectives • The purpose of physical database design. • How to map the logical database design to a physical database design. • How to design base relations for the target DBMS. 2

  3. Chapter 16 - Objectives • How to design enterprise constraints for the target DBMS. • How to select appropriate file organizations based on analysis of transactions. • When to use secondary indexes to improve performance. 3

  4. Chapter 16 - Objectives • When to denormalize to improve performance. • How to design security mechanisms to satisfy user requirements. 4

  5. Logical and Physical Database Design • Sources of information for the physical design process includes global logical data model and documentation that describes model. • Logical database design is concerned with the what, physical database design is concerned with the how. 5

  6. Physical Database Design • The process of producing a description of the implementation of the database on secondary storage. • Describes the storage structures and access methods used to achieve efficient access to the data. 6

  7. Overview of Physical Database Design Methodology • Step 1 Translate global logical data model for target DBMS • Step 2 Design physical representation • Step 3 Design security mechanisms

  8. Step 1: Translate global logical data model for target DBMS • To produce a basic working relational database schema from the global logical data model • Design base relations for target DBMS • Design enterprise constraints for target DBMS 7

  9. Step 1: Translate global logical data model for target DBMS (continued) • Design base relations for target DBMS • To decide how to represent the base relations we have identified in the global logical data model in the target DBMS. • Design enterprise constraints for target DBMS • To design the enterprise constraints for the target DBMS.

  10. SQL to create Property_for_Rent Relation

  11. Step 2 : Design physical representation To determine the file organizations and access methods that will be used to store the base relations; that is, the way in which relations and tuples will be held on secondary storage. • 2.1 Analyze transactions • 2.2 Choose file organizations • 2.3 Choose secondary indexes • 2.4 Consider the introduction of controlled redundancy • 2.4 Estimate disk space requirements

  12. Step 2 Design Physical Representation • Step 2.1 Analyze transactions • To understand the functionality of the transactions that will run on the database and to analyze the important transactions. • Step 2.2 Choose file organizations • To determine an efficient file organization for each base relation. 16

  13. Typical Disk Configuration

  14. Step 2.1 Analyze transactions • For each Transaction associated with the components of the data model (usually predefined queries including view, trigger, procedure, function and package), it needs to be broken down into further smaller units of work:

  15. Transactions Analysis (continued) • A.    Transformation Rules: Describe the rules (R,U,I, D) or algorithms used to transform data received into data generated. • B.    Edit and Error Rules: Define the rules validating data received and the method of processing erroneous data. • C.    Sequence Analysis: Describe under what conditions this transaction is performed and what rules determine which transaction will be performed next.

  16. Cross-referencing Transactions and Relations

  17. Transactions Analysis (continued) • D.    Audit Rules: Describe the rules required to audit the activity performed within this transaction. • E.    Security Rules: Define the security required to invoke the transaction or various facets of the transaction.

  18. Transactions Analysis (continued) • F.    Frequency of execution: Define the number of times this transaction is performed in a fixed period of time. • G.    Type of transaction mode: Describe whether the transaction is batch, on demand, or interactive.

  19. Example - Sample Transactions (A) Insert details for a new member of staff, given the branch address. (B) List rental properties handled by each staff member at a given branch address. (C) Assign a rental property to a member of staff, checking that a staff member does not manage more than 10 properties already. (D) List rental properties handled by each branch office. 17

  20. ER Model for Sample Transactions showing Expected Occurrences 18

  21. Analysis of Selected Transaction A 20

  22. Analysis of Selected Transaction B 21

  23. Analysis of Selected Transaction C 22

  24. Step 2 Design Physical Representation (continued) • Step 2.3 Choose secondary indexes • To determine whether adding secondary indexes will improve the performance of the system. • Step 2.4 Consider the introduction of controlled redundancy • To determine whether introducing redundancy in a controlled manner by relaxing the normalization rules will improve the performance of the system. 23

  25. Step 2.3 Choose secondary indexes Data File: The file contains the logical record. Index File: The file contains the index file. • The values in the index file are ordered per the indexing field which is usually based on a single attribute.

  26. Indexes • Primary index: The indexing field is guaranteed to have a unique value. • Secondary Index: An index that is defined on a non-ordering field of of the data. • Clustering index: If the index field is not a key field of the file, so that there can be more than one record corresponding to a value of the indexing field.

  27. Step 2.4 Consider the introduction of controlled redundancy • Simplified Relation with Derived Attribute • Duplicating Attribute • Setting up Lookup Table • Duplicating Foreign Key

  28. Simplified Staff Relation with Derived Attribute No_of_Properties 25

  29. Original Renter and Interview Relations 26

  30. Combined Renter and Interview Relations 27

  31. Original Property_for_Rent and Owner Relations 28

  32. Duplicating LName Attribute in the Property_for_Rent Relation 29

  33. Lookup Table for Property_Type Attribute 30

  34. Modified Property_for_Rent Relation with Duplicated Description Attribute 31

  35. Duplicating Foreign Key Bno in Owner Relation 32

  36. Original Property_for_Rent, Renter, and Viewing Relations 33

  37. Duplicating Street Attribute in Viewing Relation 34

  38. Step 2 Design Physical Representation (Continued) • Step 2.5 Estimate disk space requirements • To estimate the amount of disk space that will be required by the database. 37

  39. Step 3 Design Security Mechanisms • To design the security measures for the database as specified by the users. 39

  40. Step 3 Design Security Mechanisms • Step 3.1 Design user views • To design the user views that were identified in Step 1 of the conceptual database design methodology. • Step 3.2 Design access rules • To design the access rules to the base relations and user views. 40

  41. Staff3 View Listing 41

More Related