PHYSICAL DATA ORGANIZATION FOR DATABASE SYSTEMS

1
Chapter 11

• PHYSICAL DATA ORGANIZATION FOR DATABASE SYSTEMS

2
Physical Data Storage Characteristics

• Physical Characteristics of Disk Drives
• Cylinders, Tracks, Sectors
• Read/Write Heads
• Accessing Data on Disk Drives
• Seek Time, Rotational Delay, Transfer Time
• The 3380 Disk Drive
• Blocks, Drives, Volumes

3
Storage Structure for a Multiple Platter Disk
4
Disk Side View and Cross Section
5
Access Time for Magnetic Disk Secondary Storage
6
IBM 3380 Disk Format
7
3380 Block per Track Capacity
8
3380 Capacity Per Unit Using 8K Byte Blocks

• 5 blocks per track
• 75 blocks per cylinder (5 15 Surfaces)
• 66,375 blocks per volume (75 885 cylinders)
• 531,000 K bytes per volume (8K 66,375 blocks)
• 4 volumes per unit 2.124 gigabytes

9
Western Digital 6 Gig Drive

• 13328 Cylinders
• 15 Surfaces
• 63 Sectors / Track
• 512 Bytes / Sector
• 5400 RPM
• Average Seek/Read 9.5 ms
• Transfer 16.6 MB/s

10
Western Digital 9.1 GB Drive SCSI

• 3956 Cylinders
• 12 Surfaces
• Variable Sectors / Track
• 512 Bytes / Sector
• 7200 RPM
• Average Seek/Read 9.5 ms
• Transfer 40 MB/s

11
File Organization for Random Access

• Index Structures
• Indexed-Sequential (ISAM) File Structure
• Virtual-Sequential (VSAM) Structures
• Clusters
• B-Tree Index Structures
• Non unique Indexes
• Index Structures in Relational DBMSs

12
File Organization for Random Access Continued...

• Hashing Structures
• Dividing by a prime number
• Using the remainder
• Pointer (Linked List) Structures
• Pointer
• Block Number/Record Number
• Page Number/Line Number
• Access Methods of the Network Model

13
Winchester Disk Track Format
14
Index Structures
15
A Multilevel Index Structure
16
Index Levels versus File Size
17
An Example of the Indexed Sequential File
Organization
18
Adding a Record to an Indexed-Sequential File
19
Overflow in Indexed Sequential File Structures
20
VSAM Using a Key-Sequence Data Set (KSDS)
Structure
21
VSAM File Example Using Customer Numbers
22
VSAM Split Internal Example
23
A B-Tree Index Structure
24
Adding a Record to a B-Tree Index Structure that
is Full
25
Splitting a Sequence Set in a B-Tree Index
Structure
26
A Hashed File Structure (11 Buckets, 3
Slots/Bucket)
27
Handling Overflows in a Hashed File
28
A Linked List Structure
29
A Linked List Structure Connecting Two Tables
30
A Linked List Structure with Prior and Owner
Pointers
31
Terms and Definitions

• Direct Access Storage Device (DASD) - A storage
  device which allows data to be accessed in
  approximately the same amount of time no matter
  where it is stored on the medium and regardless
  of its logical ordering in a data structure.
  Magnetic disk is the most common DASD.
• Read/Write Head - A read/write head is a device
  that can read or write magnetic spots on a disk
  surface. Multiple read/write heads are often
  attached to a common arm that can adjust their
  position over the disk surface.
• Track - A track is a concentric circle on the
  surface of a disk platter. A read-write head will
  pass over the set of data for a track if the
  read-write head remains stationary while a disk
  makes one complete revolution.

32
Terms Continued...

• Cylinder - A cylinder is a set of parallel tracks
  across all surfaces of a disk pack that can be
  assessed when the read/write arm is in a
  stationary position.
• Seek Time - the average time required for the
  read/write arm to be moved into position for the
  reading of a randomly selected record.
• Rotational Delay - the average wait time for the
  beginning of a set of data to be retrieved to
  rotate under the read/write head once it is in
  position.
• Data Transfer Time - The time required to
  physically transfer a set of data from secondary
  to primary storage once the read/write head has
  been positioned over the beginning point of the
  requested data.

33
Terms Continued...

• Sequential Access - When sequential access
  methods are used, data must be retrieved in
  sequential order based upon some characteristic
  of the data.
• Random Access - When random access methods are
  used, all data items can be retrieved in
  approximately the same amount of time regardless
  of their position on the storage medium and
  regardless of any logical sequencing of records
  which might exist.
• Index - An ordered list of items coupled with an
  indication of where those items can be found.
• ISAM File Structure - An ISAM (Indexed Sequential
  Access Method) file structure supports both
  random and sequential access by physically
  storing data in sequential order, but providing
  an index structure to allow random access. For
  ISAM files the index ranges are fixed.

34
Terms Continued...

• VSAM File Structure - A VSAM (Virtual Sequential
  Access Method) file structure supports both
  random and sequential access by storing data
  within sequence sets which can be processed
  sequentially, but providing an index structure to
  allow random access. For VSAM files the index
  ranges can be modified as data are added to or
  removed from the file.
• B-Tree Index Structure - A B-Tree is an index
  structure which has an algorithm that
  incrementally adjusts the index structure to
  maintain balanced depth of index ranges as data
  are added to or deleted from the database.
• B Tree Index Structure - A B Tree is an index
  structure which has all of the properties of the
  B-Tree structure and which additionally has
  pointers in its sequence set which support
  sequential processing without reference to higher
  levels of the index.

35
Terms Continued...

• Hashing - Hashing is an access method which
  determines the storage location of a record based
  upon a mathematical algorithm performed on some
  key attribute of that record. Hashing supports
  direct access, but not sequential access.
• Linked List - A data structure in which each
  record in the linked set contains a pointer to
  the next record in the set. Thus the set of
  related records can be retrieved using data
  stored with each record, without reference to a
  separate index.