SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Physical Database Design Chapter 5

kacia avatar
kacia
26 diapositives · 1K Views · Jul 17, 2014

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:

Physical Database Design Chapter 5 - slide 1
01 / 26Physical Database Design Chapter 5
1 / 17

Télécharger la présentation

Physical Database Design Chapter 5
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

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