ECE 569 Database System Engineering Spring 2004

1
ECE 569 Database System EngineeringSpring 2004

• Yanyong Zhang www.ece.rutgers.edu/yyzhang
• Course URL www.ece.rutgers.edu/yyzhang/spring04

2
File Organization

• File organization is a method of arranging the
  records in a file when the file is stored on
  disk.
• Each file organization makes certain operations
  efficient but other operations expensive
• eg. employee(name, age, salary)
• Retrieve employee records in order of increasing
  age
• Retrieve all the employees who make more than
  5000
• Sometimes, inserts are more important than
  retrieval
• heap files unordered file

3
Different File Organizations
File
heap
Unstructured (System Sequenced)
Structured
Non- Associative
Associative
Key-sequenced
Hash
Entry Sequenced
Relative
heap
4
Heap file implementation I linked list

• Maintain a heap file as a doubly linked list of
  pages
• The DBMS can remember where the first page is
  located by maintaining a table containing pairs
  of ltheap_file_name, page_1_addrgt in a known
  location on disk. The first page is called header
  page.

free page
free page
header page
data page
data page
5
Heap file implementation II page directory

• Maintain a directory of pages
• The DBMS can remember where the first directory
  page of each heap file is located. The directory
  is itself a collection of pages and is shown as a
  linked list below

data page
data page
6
File operations

• Create a file
• Destroy a file
• Insert a tuple
• Delete a tuple
• Search a tuple
• Different file organizations have different
  implementations

7
System-sequenced files

• The tuple-oriented file system has complete
  control over where the tuples are stored.
• Additional information such as insert order is
  not maintained.
• Tuples are inserted wherever the free space
  administration points.
• Space freed by deleted tuples can be reclaimed
  either directly or after reorganization.

8
System-sequenced files operations

• insert. The place for a new tuple is determined
  by the tuple-oriented space allocation.
• update. If the tuple length changes
• The tuple stays in its old page
• The tuple must be moved to another page, but it
  has to be completely removed from its current
  page.
• delete.

9
Entry-sequenced files

• Tuples are appended to the file.
• A sequential file that reflects the order in
  which the tuples arrived.
• e.g., log files

10
Entry-sequenced files operations

• insert
• Free space is handled by a free space cursor,
  which at any instant is the end-of-file pointer.
• If the last page cannot accommodate the tuple, a
  new empty page is claimed.
• update. An update cannot increase the tuple size.
• Delete. Free space cannot be reclaimed.

11
Relative Files

• Relative files are arrays of tuples.
• The file is pre-formatted
• Contiguous page allocation
• Fixed size tuples.

12
Relative files operations

• insert. insert at, insert near, insert at end,
  insert anywhere
• update. Tuples can be updated arbitrarily as long
  as the resulting length is smaller than the
  maximum size.
• delete. The directory entry pertaining to the
  slot to be set to 0.

13
Key-Sequenced and Hash Files

• Associative access is characterized by the
  mapping
• tuple.attribute value(s) -gt location in file.
• Implies a mapping from attribute value(s) to the
  TID.
• Fast access to each tuple.