Storing Data - Disks and Files

Question 1

What does the physical scheme describe?

Answer 1

Files and Indexes used

Question 2

What is the basic abstraction of data in DBMS? How are they located?

Answer 2

A file of records; a record ID can locate a record in a file

Question 3

What is file organization and what are 3 types?

Answer 3

A method of arranging a file of records on external storage

Heap Files, Sorted Files, Indexes

Question 4

What are the two types of external storage and the differences between them?

Answer 4

Disks: Random access at fixed coast; cheaper for block I/O
Tapes: only read pages in sequence, cheaper

Question 5

What are the 3 components of the architecture?

Answer 5

1. File and index layers make calls to buffer manager
2. Buffer manager stages pages from external storage to main memory buffer pool
3. Disk Manager keeps track of pages used by files, and empty slots on pages

Question 6

What is a heap file and when is it used?

Answer 6

Random ordering file suitable when typical access is a file scan retrieving records

Question 7

What is a sorted file and when is it used?

Answer 7

Best if records must be retrieved in some order, or when a range of records is needed; file sorted on some value

Question 8

What is an index ?

Answer 8

A data structure used to organize records via trees or hashing
- speeds up searches based on key value; faster updates in index

Question 9

What is the main advantage of disk or tape?

Answer 9

Random access instead of sequential

Question 10

In what units are data stored and retrieved on disk?

Answer 10

Blocks Or Pages

Question 11

Why doesn’t DBMS use OS disk manager?

Answer 11

1. portability
2. too small files
3. OS files cannot span disk devices

Question 12

What is a buffer pool?

Answer 12

Collection of pages in main memory

Question 13

What is a frame?

Answer 13

Page in a buffer pool

Question 14

What is “pinning”

Answer 14

Locking/increment a counter for memory waits/accesses

Question 15

What is sequential flooding?

Answer 15

Using LRU + repeating sequential scans causing significant page faults; when buffer frames

Question 16

What are DBMS requirements for buffer management?

Answer 16

1- pin a page in buffer pool
2- force a page to disk
3- adjust replacement policy
4- pre-fetch pages 
3/4 based on access patterns

Question 17

When would a record be variable length?

Answer 17

When using varchar

Question 18

How are variable length records structured?

Answer 18

1. delimeters

2- off-set based using directory

Question 19

What complications of variable-length records?

Answer 19

modifying a field may cause it to grow; need to shift; may no longer fit on page; may not fit on one page

Question 20

What are 2 options for page formats of fixed length records

Answer 20

1- packed; all used packed at top; use number of records to find bottom; if delete, need to shfit
2- unpacked,bitmap; use bitmap to find out if record is empty or used

Question 21

What is the page format for variable length record?

Answer 21

directory of pointers for each slot

Question 22

How does a heap file work?

Answer 22

1. simple file structure that contain records in no order
2. as file grows/shrinks, disk parks are allocated/deallocated
3. keeps of pages in a file, free space on pages, and records on pages

Question 23

What are 2 implementations of heap file and what are the pros/cons

Answer 23

1. Double-linked list; header page hold pointers to data page doubly linked list and free pages DLL; expensive for insertion
2. page directory: holds pointers to all data pages; each directory entry identifies a page, and free space management

Question 24

What is the structure of an idnex fiel?

Answer 24

Leaf pages contain data entries and chained (prev/next)

non-leaf pages have index entries used to direct searches based on key

Question 25

What is a system catalog?

Answer 25

Relation itself that holds meta data including index structure, relation names, attributes, etc

Question 26

What are the two main techniques of RAID?

Answer 26

Data striping: partitioned for performance

Redundancy for backup

Question 27

What are 4 RAID levels and the reasons for them

Answer 27

Level 0: data loss not an issue

3: workloads with main large transfer request of several contigous block
5: good general purpose
6. high reliability