Chapter 9: ClientServer Database Systems

1
(No Transcript)
2
Chapter 6 Physical Database Design and
Performance
3
Physical Database Design Process

• Many physical database design decisions are
  implicit or eliminated when you choose the
  database management technology.
• Purpose - translate the logical description of
  data into the technical specifications for
  storing and retrieving data
• Primary goal create a design for storing data
  that will provide adequate performance and insure
  database integrity, security and recoverability

4
Physical Database Design Process
5
Designing Physical Fields

• Data types
• minimize storage space
• represent all possible values
• improve data integrity
• support all data manipulations
• Coding and compression
• Data integrity
• default value
• range control
• null value control
• referential integrity
• Missing Data
• estimate resolve sensitivity test

6
Designing Physical Records

• Page the unit of storage on disks
• some power of 2, e.g., 2112048
• 21665,536 2301,073,741,824
• 1,073,741,824 65,536 16,384
• FAT16 vs. FAT32
• Blocking factor number of records per page
• Fixed-length field exact location can be
  determined
• Variable-length field in separate physical file
  BLOB

7
Denormalization (part of physical record design)

• Denormalization
• denormalize 11 when optional side usually
  present
• denormalize MN if frequent joins needed
• denormalize 1M if 1 side is reference data
• horizontal partitioning if processed by group
• vertical partitioning if certain fields processed
  together
• replicate to various locations

8
Indexes

• Primary key index (unique identifier)
• Secondary key index (clustering)
• When to index
• larger tables
• primary key (mandatory)
• fields used for selecting, grouping, or sorting
  data
• variety in attribute values
• dont exceed limit
• fields with null values may not work

9
RAID

• Redundant Array of Inexpensive Disks
• Stripe concurrently read data
• RAID-0 fast access, no backup
• RAID-1 disk mirror
• RAID-2 error-correction or parity codes
• RAID-3 one disk for ECC with single reads
• RAID-4 one disk for ECC but parallel reads
• RAID-5 no dedicated parity drive

10
General/Specific
11
Ledgers/Transactions
12
(No Transcript)