1 / 26

Physical Database Design Chapter 5

Physical Database Design Chapter 5. Agenda. Purpose Activities Fields Records Files. Purpose. Determine physical specifications for data Goal: Processing Efficiency Performance Integrity Security Recoverability. Physical Design Activities. Choose DBMS Detailed definitions for:

kacia
Télécharger la présentation

Physical Database Design Chapter 5

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 DesignChapter 5 G. Green

  2. Agenda • Purpose • Activities • Fields • Records • Files

  3. Purpose • Determine physical specifications for data • Goal: Processing Efficiency • Performance • Integrity • Security • Recoverability

  4. Physical Design Activities • Choose DBMS • Detailed definitions for: • fields (data dictionary) • records (physical record structure, quantity ) • files (access methods, space requirements) • Physical File Creation • Query Optimization

  5. Physical Design Activities • Choose DBMS • Detailed definitions for: • fields (data dictionary) • records (physical record structure) • files (access methods)

  6. Choosing a DBMS NOTE: This diagram is for effect ONLY—it is incomplete (e.g., no MDDB, no ODDB) AND contains some inaccuracies

  7. Choosing a DBMS, cont… • Compatibility with existing hardware, software, network, operating system • DBMS features meet requirements* • Needed functionality • Structure of data • Nature of Workload • Product reliability • Vendor support • IT personnel expertise • Pricing, licensing

  8. Physical Design Activities • Choose DBMS • Detailed definitions for: • fields (data dictionary) • records (physical record structure) • files (access methods)

  9. Designing Fields • Choose data types and lengths • Represent all possible values • Ensure data integrity • Support data manipulations • Minimize storage space • Additional data integrity controls: • defaults • ranges • nulls • referential integrity • Document above in data dictionary • see Table 1-1 • see Table 4-1

  10. Designing Fields, cont… • Text • Char vs. Varchar • Numbers • Int vs. Decimal (or Numeric) • Dates • Date vs. Time vs. Datetime • Others (will not use in class)

  11. Physical Design Activities • Choose DBMS • Detailed definitions for: • fields (data dictionary) • records (physical record structure) • files (access methods)

  12. Designing Records • Understand data usage • Re-design records for efficiency?

  13. Usage Analysis Example

  14. Designing Records, cont... • Re-design options include: • Denormalization • What? • When? • Why? • Why not? • Partitioning • Horizontal (row) • Vertical (column)

  15. A possible denormalization situation: a many-to-many relationship with nonkey attributes Original entities: Extra table access required Duplicate descriptions possible

  16. Physical Design Activities • Choose DBMS • Detailed definitions for: • fields (data dictionary) • records (physical record structure) • files (access methods)

  17. Designing Files • Efficient access to data • File Organizations • sequential • indexed • hashed • Efficient storage of data • How much storage?

  18. Sequential File Organization • Records physically ordered • often by PK • Examples • Advantages • Disadvantages

  19. Sequential File Organization, cont...

  20. Indexed File Organization • Data Records physically ordered • Index Records give physical location of each data record • indexes are separate files • Example • Why? • Why not?

  21. Indexed File Organization, cont... Without Index: PRODUCTTable • 10,000 records • Record size =.5Kb • Page size = 1Kb • 20% = type E • “Find all products • of type E” • How many I/Os?

  22. Indexed File Organization, cont... With Index: PROD_TYPE Index PRODUCTTable • 10,000 records • Record size =.5Kb • Page size = 1Kb • 20% = type E • 1 byte for TYPE • 4 bytes for ADDR • “Find all products • of type E” • How many I/Os?

  23. Indexed File Organization, cont... • Types of Indexes • Primary Key Index • Secondary (Key) Indexes • Clustered Index • How to structure indexes? • B-Tree • Bitmap • …

  24. B-Tree Index Organization Root Branch rowid rowid rowid rowid rowid rowid rowid rowid rowid Leaf See http://www.ovaistariq.net/733/understanding-btree-indexes-and-how-they-impact-performance/ for more information

  25. Summary • Purpose • Activities • Choose DBMS • Fields • Data dictionary • Data types • Records • Usage • Denormalization • Files • Organizations • Indexing

  26. Next Time… • 2/20 Assignment 3: Logical DB Des **DUE** • 2/25 Intro to SQL Server & SQL DDL, DML • Chapters 6 & 7 (see schedule for pages) • 2/27 ** EXAM 1 ** • 3/6 Team Project 1 **DUE **

More Related