1 / 17

Chapter 16-17 Physical Database Design Methodology

Chapter 16-17 Physical Database Design Methodology. Software & Hardware Mapping Logical Design to DBMS Physical Implementation Security Implementation Monitoring and Tuning DBMS. Software & Hardware. Define user’s requirement Select software based on user’s requirement

kamin
Download Presentation

Chapter 16-17 Physical Database Design Methodology

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-17Physical Database Design Methodology • Software & Hardware • Mapping Logical Design to DBMS • Physical Implementation • Security Implementation • Monitoring and Tuning DBMS

  2. Software & Hardware • Define user’s requirement • Select software based on user’s requirement • Select hardware based on software requirement

  3. Mapping Logical Design to DBMS • Create base relations • Name of relation • Attributes (domain & defaults) • Primary key (not null) • Foreign key (referential integrity) • Alternative keys • Indexes • Integrity rules • Enterprise • Referential • Entity

  4. Physical Implementation • Efficiency measurement • Throughput • Response time • Factors for efficiency measurement • Main memory • CPU • Disk I/O (os, database, index, recovery log) • Network (traffic, collisions) • File organization & access method

  5. Physical Implementation • Analysis transactions • Choose file organizations • Add secondary indexes • Balance flexibility and performance • Estimate disk space

  6. Analyze Transactions • Frequency of the transaction - logical access map • max • avg • min • Access structure - attributes used in “where” conditions • Relational operations (join, select, project) • Data operations (I, D, U, R) • User response time requirement • Day • Time

  7. File Organization Selection Criteria • Heap • Bulk-loading • A few pages • Retrieve most record • Primary & secondary indexes • Indexed sequential • Exact key match • Direct & sequential accesses • Update deterioration

  8. File Organization Selection Criteria • Direct (hash or random) • Direct access • Primary and secondary indexes • Update deterioration • Not for pattern match or range of values • B+ tree • Similar to indexed sequential • No overflow problem (deterioration) • Pattern match or range of values • Dynamic growth

  9. Secondary Indexes • Overhead consideration • Spaces • Update • Query performance • Selection criteria • Do • Primary key • Heavily used attributes or foreign keys • Don’t • Small relations • Frequently updated relations • Long character strings • Retrieve most records

  10. Balance Flexibility and Performance • Denormalization • Low update rate • High query rate • Cross-referencing transactions and relations table • Steps • Derived data • Redundancy

  11. Derived Data • Storage costs • Calculation costs • Response time

  12. Redundancy - Reduce Join • Combining one-to-one relationship (company, store) • Duplicating nonkey attributes or foreign keys in one-to-many relationship (po, supplier) • Duplicating attributes in man-to-many relationship (student, class) • Introducing repeating group • Static • Maximum no. • less than 12

  13. Redundancy - Reduce Join • Reference table (type, description) • Creating extract tables or relations • Multi-relations (static data and not current or accurate) • Large amount of derived data

  14. Estimate Disk Space • Software (DBMS) • Hardware (disk spaces) • Record size • Relation size • Storage area for indexes • System overhead • File organization

  15. Security Implementation • User views • Access rules • Authorization • Privileges

  16. Monitoring and Tuning DBMS • Correct inappropriate design • Implement new minor requirement • Produce better Response time and throughput • Increase user’s satisfaction

  17. Assignment • Review chapter 5-6, 11-17, 24-26 • Read chapter 18 • Project (enhanced e-r & normalization) • Due date: • Project (SQL part) • Due date:

More Related