Database design with a relational model

1
Database design with a relational model

• Internal Level Physical Storage and Access

2
DBMS Storage Hierarchy example
Database - a group of tablespaces
Partition - also a group of contiguous extents,
but may be part of a file or multiple files -
Useful when there are a large number of users and
extents to allow parallelism
Tablespace - a file or group of files containing
data, so can have or share multiple extents -
Allows 1) Different users in different physical
areas, 2) Separation of user data from system
data, 3) Backups on only part of a database
File - a group of contiguous extents
Extent - a group of contiguous, stored pages (not
for I/O read groups are for that) - Provides
for allocation performance improvement
Page - aka, block, data block, blocking unit,
control interval, row group - A storage location
for rows of data, typically the same size across
the storage medium multiples of 1024 bytes are
common - Contains an integral number of rows -
Rows in a page preferably come from the same
table - Used for 1) a minimal unit for disk i/o
(but can read multiple pages), 2) locking, 3)
caching to a buffer pool, 4) housekeeping
(includes header information)
3
File operations

• Retrieval or update
• DBMS/OS decomposes selects to basic operation,
  then
• Open allocates buffers, retrieves file header,
  sets pointer
• Find Searches for first true, transfers block
  to buffer, sets file pointer
• Read Copies record from buffer to program
  variable
• FindNext Searches for next true, transfers to
  buffer
• Close releases buffers, closes file

DBMS
File Manager
Disk Manager
Data File
Record
Block
Disk I/O
4
File operations

• Disk Manager
• Retrieve page from block
• Replace page within page set
• Add a new page to page set
• Remove page from page set
• File Manager
• Retrieve stored record from file
• Replace stored record within file
• Add a store record to file and return RRN
• Remove stored record from file
• Create a new stored file
• Destroy a stored file

5
Physical Storage Basic Concepts

• Managed by DBMS, OS or DBMS/OS
• Includes
• Primary Storage fast and expensive
• Secondary Storage cheap and slow
• The combination so must be optimized
• DBMS makes request to buffer manager when a
  block is needed from disk. If block is already
  in buffer, buffer manager passes address to
  requestor. If the block isnt in buffer, space
  is allocated (possibly removing some other
  block).

6
Physical Storage Basic Concepts

• Secondary storage must consider
• Seek time
• Rotational delay
• Number of disk accesses
• Arrangement of data on disk

7
Physical Storage Basic Concepts

• Goal minimize block transfers, seek time, spin
  latency
• Heap file (Unordered)
• Ordered file
• Hashed file
• Indexes
• Hashed
• Trees
• Bitmaps

8
File storage
Page ID
Page No Offset from foot of page
9
Record blocking

• Fixed length
• x Records/Block (per floor function)
• Unused space b (bfr R) bytes
• Variable length
• Spanning provides pointer at end of first block
  to surface, track and block location of remainder
  of record
• b r/bfr blocks (per ceiling function)
• Slotted-page structure
• Block Header Records

10
File header

• Contains description used for access of records
  in the file
• Disk addresses of blocks
• Record formats
• Field lengths
• Field order (fixed length)
• Field type
• Separators
• Codes (variable length)
• Located by Relative Record Number (RRN)

11
Heap Files

• Unordered
• Easy writes
• New records inserted at end of file
• Once a block is full, pointer set to new block
  and rest of record is written
• Requires a linear search for anything
• Large number/size of records decrease speed
• Deletes require write to buffer, mark record as
  deleted, write back to disk with deleted space
  left in place.
• Modifications force periodic file reorganization
  to recover disk space
• Fixed-length fields contiguously allocated in
  unspanned blocks improves search latency (i.e.,
  i/bfr denotes block, i mod bfr denotes location
  in block)

12

• Physical Record Storage (Heap File)

13
Ordered Files

• One field (attribute) selected for ordering
• If a key field, data is key-sequenced
• Allows binary searches for faster retrieval
  (always retrieves mid-page between upper and
  lower limits until correct page is found), since
  log2(B) blocks accessed
• Inserts and deletes require ordering to be
  maintained (may require writing all pages above
  affected record)
• Overflow (transaction) file will help to reduce
  this problem
• Typically only used if a primary index is applied
• No gain for non-ordered fields
• Typically requires indexed file access path

14
File storage Ordered with linked lists

• Assume one record/page (i.e., 5B/Fixed Block)
• Then
• Insert Dad
• Delete Geode
• Insert Pod

15
File storage Ordered with linked lists

• Assume one record/page (i.e., 5B/Fixed Block)
• Then
• Insert Dad
• Delete Geode
• Insert Pod

16
Hash Files

• Records written in non-sequential order
• Hash function calculates address of the page
  where record is stored
• based on a one or more base fields (hash field)
• If a key field, called hash key
• Hash function creates even spread of records
  across file
• Folding applies math to different parts of the
  has field (empID 0110 could become (01)10. 11
  is address of disk page
• Division-remainder uses mod 0110 mod 100. 10 is
  address of the disk page
• Can use
• Open addressing, Unchained overflow, Chained
  overflow, Multiple hashing, Dynamic hashing
• Limitations
• Useful for exact match
• Poor for ranges, patterns, additional fields

17
Indexes

• Ordered indices values in sorted fashion
• Hash indices values distributed across
  buckets by using a function
• An index record consists of a value and pointers
  to one or more records with that value. Can be
• Dense every value group indexed
• Sparse only some values are indexed
• Include
• Compound indexes (values from more than one data
  column)
• Covering index (uses values in the index for the
  SELECT clause)
• Unique index
• Clustering indexes (stores similar data rows near
  each other)
• Bitmap indexes (assigns 1 if a value is true, 0
  if false)

18
B-Trees

• Well established as the most common structures
  for indexes
• Multi-level
• d is the order of the tree it is a measure of
  the tree node capacity
• Every node except the root contains m entries,
  where d/2 lt m lt d
• The root node contains 1 lt m lt d entries
• Non-leaf nodes with m index entries contain m1
  pointers to children
• Pointer Pi points to a subtree with K values such
  that Ki-1 lt K lt Ki

19
B-Trees (order 2) one algorithm

• Query find all values with a pointer value of P
• If search value is lt SearchKey value, go left
  otherwise, go right

20
B Tree index/sequence sets
21
B Trees datapage leaf
Sort order Anizy,Apach,Apensen,Ardwick,Arnham,Ath
ens
22
B Tree Example

• Rules for this example
• d is the order of the tree it is a measure of
  the capacity of child nodes
• Every node except the root contains m entries,
  where d/2 lt m lt d
• The root node contains 1 lt m lt d entries
• Non-leaf nodes with m index entries contain
  between (m1)/2 and m1 pointers to children
• Pointer Pi points to a subtree with K values such
  that Ki-1 lt K lt Ki
• Search uses pointer to the right for greater than
  or equal to in non-leaf nodes, greater than in
  leaf nodes until equal to is found or not found

23
Records in a block

• How to store records in blocks?
• number of records r
• block size B
• record size R
• blocking factor bf number of records in a
  block
• Bf ?B/R? (spanned, unspanned)
• number of blocks needed b
• b ?r/bf?

24
B-trees performance impact

• A 4k page can many records per page
• ((4 b/pointer 4b/field)n, 4b/pointer) order
  of 512
• Root 511 records
• Level 1 261,632 records
• Level 2 133,955,584 records
• Total 134,217,727 records
• Shallow is better

25
B-trees performance impact

• 1,000,000 records of 300B (including header)
• Search key is a 4 byte int a pointer requires 4
  bytes
• 4KB blocks, no block header, random placement,
  avg. retrieval time 5.6 ms
• No time for memory reads
• 13.6 records/block 76924 blocks to store data
• 512 indexes/block 1954 blocks to store index
• No index
• (76924/2) 38462 block accesses (avg.)
• Time to find a record 38462 5.6 ms 215.4 s
• Indexed, binary search
• log(1954) 1 11 1 12 block accesses
  (maximum)time to find a record 12 5.6 ms
  67.2 ms
• Indexing increased speed by 3205 times.