Access Methods Layer - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Access Methods Layer - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems Design

File and Access Methods Layer

• Buffer Manager provides a stream of pages
• But higher layers of DBMS need to see a stream of records
• A DBMS file provides this abstraction
  • File is a collection of records that belong to a relation R.
    • For example: Relation students might be stored in relation students.dat
  • File is made out of pages, and records are taken from pages
• File and Access Methods Layer implements various types of

files to access the records

• Access method – mechanism by which the records are extracted from the DBMS

Heap File

 - 123 Bob NY $ - 8387 Ned SJ $ - 121 Jil NY $ 

• 81982 Tim MIA $
  • 2381 Bill LA $
  • 4882 Al SF $
• 9403 Ned NY $
• 1237 Pat WI $ - Page - Page - Page - 121 Jil NY $ Sorted File - 123 Bob NY $ - 1237 Pat WI $ - 2381 Bill LA $ - 4882 Al SF $ - 8387 Ned SJ $
  • 9403 Ned NY $
• 81982 Tim MIA $ - Page - Page - Page

Index files structure

• Index entries
  • Store search keys
  • Search key – a set of attributes in a tuple can be used to guide a search - Ex. Student id
  • Search key do not necessarily have to be candidate keys
    • For example: gpa can be a search key on relation: Students(sid, name, login, age, gpa)
• Data entries
  • Store the data records in the index file
  • Data record can have
    • Actual tuples for the table on which index is defined
    • Record identifier for tuples that match a given search key

Issues with Index files

• Index files for a relation R can occur in three forms:
  • Data entries store the actual data for relation R.
    • Index file provides both indexing and storage.
  • Data entries store pairs <k, rid>:
    • k – value for a search key.
    • rid – rid of record having search key value k.
    • Actual data record is stored somewhere else, perhaps on a heap file or another index file.
  • Data entries store pairs <k, rid-list>
    • K – value for a search key
    • Rid-list – list of rid for all records having search key value k
    • Actual data record is stored somewhere else, perhaps on a heap file or another index file.

Implementing Heap Files

• Heap file links a collection of pages for a given

relation R.

• Heap files are built on top of Buffer Manager.

• Each page has a page id

• Often, we need to know the page size (e.g. 4KB)
  • All pages for a given file have the same size.
• Page id and page size can be used to compute an offset in a cooked file where the page is located.
• In raw disk partition, page id should enable DBMS to find block in disk where the page is located.

Linked Implementation of Heap

Files

Header Page

Data Page

Data Page

Data Page

Data Page

Data Page

Linked List of pages With free space

Linked List of full pages

Directory of pages

Date Page 1

Date Page 1

Date Page 3

Date Page N

.

..

header

Directory of pages

• Linked list of directory pages

• Directory page has

• Pointer to a given page
• Bit indicating if page is full or not
• Alternatively, have amount of space that is available

• More complex to implement

• Makes it easier to find page with enough room

to store a new record

Packed Fixed-Length Record

...

Slot 1 Slot 2 Slot 3

N

Slot N

Free Space

Page header

number of records

Unpacked Fixed-Length Record

...

Slot 1 Slot 2 Slot 3

N

Slot N

Free Space

Page header

number of slots

Slot bit vector

Fixed-Length records

• Size of each record is determined by

maximum size of the data type in each column

F1 F2 F3 F

Size in bytes^2 6 2

Offset of F1: 0 Offset of F2: 2 Offset of F3: 8 Offset of F4: 10

Need to understand the schema and sizes to find a given column

Variable-length records

• Either

1. use a special symbol to separate fields

Option 1^ 2.^ use a header to indicate offset of each fieldF1^ $^ F2^ $^ F3^ $^ F

Option 2^ F1^ F2^ F3^ F

Option 1 has the problem of determining a good $ Option 2 handles NULL easily