class: bottom, left
background-image: url(images/g.png)

<h2 class="title_headings_sml">COSC210 Database Management Systems</h2>

<h1 class="title_headings_sml"> Lecture 10 - Disk Storage, File Structures, Hashing and Indexing</h1>

<h3 class="title_headings_sml"> Dr. Edmund Sadgrove </h3>


---
background-image: url(images/reg_background.png)
# Reading

* Chapter 16, 17 from ***Fundamentals of Database Systems*** by Elmazri and Navathe

---
background-image: url(images/reg_background.png)
## Summary

* Hardware Overview
* File Records and Operations
* Files of Ordered/Unordered Records
* Hashing Techniques
* Single Level Indexes
* Multilevel Indexes

---
background-image: url(images/reg_background.png)
## Hardware Overview
* Up until now we have covered:
	* The **Database Schema**.
	* The Three-Schema Architecture:
		* The **External Schema**. &#10004;
		* The **Conceptual Schema**. &#10004;
		* The **Internal Schema**.

* In this lecture we will look at the **Internal Schema**.
* This topic is fairly high-level as much of the content will be covered in detail in other units.

---
background-image: url(images/reg_background.png)
## Hardware Overview

* Data is stored in bytes with 8 (binary) bits.
<img style="float: right;" width="40%" src="images/lecture_13/hdd.png" />
* Memory has three main types:
	* **Cache Memory**: typically attached to the CPU.
	* **Primary storage**: fast, but volatile (RAM).
	* **Secondary storage**: slower, but non-volatile.
		* SSD, HDD or sequential (tape recorders). 
* This forms a hierarchy: 
	* Secondary transferred to primary and cache and to the CPU at execution time.
* **Databases** are typically stored on secondary storage, but some are stored in main memory.
---
background-image: url(images/reg_background.png)
## Hardware Overview

* **Buffers** are maintained in primary storage.
	* Temporary stored data from secondary storage.
* **Double buffers** allow:
	* One buffer to written/read to/from the secondoary storage (I/O processor). 
	* Second buffer to process data at the same time (CPU).
<img style="float: left;" width="65%" src="images/lecture_13/double_buffer.png" />
---
background-image: url(images/reg_background.png)
## Placing File Records on Disk

* Stored **records** (tuples) will either have a fixed or variable length.
<img style="float: right;" width="45%" src="images/lecture_13/struct.png" />
	* **Fixed-length**:
		* Records occupy a fixed set of bytes.
	* **Variable-Length**:
		* Records contain fields that vary in size.
* Data is processed in blocked sized units with `R` bytes and `B` block size:
	* Fixed length blocking factor: 
		* `\(bfr = \lfloor B/R \rfloor \)`
	* Unused space given by: 
		* `\( B - (bfr \times R) \)` 
---
background-image: url(images/reg_background.png)
## Typical File Operations

* File **I/O functions** are encapsulated within SQL commands with some lower level access.
	* The DBMS will parse the SQL statements.
	* Convert them to an internal representation.
	* Construct an execution plan using the lower-level file operations. 

* **Basic operations**: Open, Close, Reset, Read, Find, FindNext.
* **High level**: Insert, Modify, Delete.

---
background-image: url(images/reg_background.png)
## Files of Ordered / Unordered Records

* **Records** can be stored:
	* **Unordered**: order of insertion.
		* Insertion is efficient (block data retrieved and re-written)
		* Delete leaves blank space or utilises a *delete-bit*.
		* Linear Search function (fixed length records are useful).
<img style="float: right;" width="55%" src="images/lecture_13/bs.png" />
			* Complexity: ` O(n) `
	* **Ordered**: based on a key attribute.
		* Insertion is expansive (resorting required).
		* Delete and update are also expansive.
		* Binary search function.
			* Complexity: ` O(log n) `
---
background-image: url(images/reg_background.png)
## Hashing Techniques
* A **Hashing** function provides implicit ordering:
	* Maps a **key** attribute to a disk location.
<img style="float: right;" width="55%" src="images/lecture_13/hash.png" />
	* This attribute is called the **hash key**
	* Search algoritm:
		* Complexity: `O(1)`
* Example:
	* Memory locations *0* to *M-1*.
	* Hash key *K*.
	* A hashing funtion could be:
		* `\(h(K) = K\ mod\ M \)`
* **Non-numeric** keys need to be converted to a number first.

---
background-image: url(images/reg_background.png)
## Hashing Techniques

* **Collisions**: when different hash keys map to the same location.
* Three solutions for resolving collisions:
	* ***Open Addressing***:
		* Subsequent positon checks until an unallocated disk location is found.
	* ***Chaining***:
		* Overflow locations are used to resolve collisions. 
		* Typically utilises a *linked-list* at the hash address.
	* ***Multiple hashing***:
		* Program runs a second hash function to find another location. 
		* Subsequent collisions are resolved with open addressing.
---
background-image: url(images/reg_background.png)
## Hashing Techniques

* Hashing can be:
	* **External hashing**:
		* Disk file level.
		* Address space is made up of **buckets**.
<img style="float: right;" width="55%" src="images/lecture_13/block_mapping.png" />
		* Each bucket points to multiple records.
		* Disk blocks can be:
			* Contiguous or single block.
		* File header table maintains order.
	* **Internal hashing**:
		* In program - e.g. arrays.
		* Order preserving.
<div style="float: right;">  This is an example of static hashing.</div>
---
background-image: url(images/reg_background.png)
## Hashing Techniques
<img style="float: right;" width="50%" src="images/lecture_13/buckets.png" />
* **Static hashing**:
	* Utilises a fixed number of storage slots (e.g. buckets).
	* Results in wasted storage locations. 
	* **Overflow** locations are created if fixed number is exceded.
		* Requires pointers to additional storage locations. 
---
background-image: url(images/reg_background.png)
## Hashing Techniques

* Dyanamic file expansion allows variable address space.
<img style="float: right;" width="43%" src="images/lecture_13/extendible.png" />
	* **Extendible** Hashing.
	* **Dynamic** Hashing.
* ***Extendible Hashing***: 
	* Utilises a 2nd  array of bucket addresses.
	* The value ***d*** indicates global depth.
	* The value ***d'*** indicates local depth.
	* The value *d* can be increased or decreased.
		* E.g. 00,01 can be become 010, 011.
---
background-image: url(images/reg_background.png)
## Hashing Techniques

* ***Dynamic Hashing***: 
<img style="float: right;" width="43%" src="images/lecture_13/dynamic.png" />
	* A **tree structure** is used in place of the *flat* directory table.
    	* Internal nodes have two pointers corresponding to either 0 or 1.
    	* **Leaf nodes** have one pointer that links to the destination bucket.

* The algorithms for insertion and deletion involves simply splitting and combining nodes. 

---
background-image: url(images/reg_background.png)
## Single Level Indexes

* ***Auxiliary access structures*** called indexes can be used as an alternative or to assist hashing.
* Indexes are designed to speed up the retrieval of individual records.
	* Indexes are a ***Secondary Access Path*** based upon an indexing field(s).
	* Indexes can be **ordered** or **unordered**.
		* E.g. creating an index on employee last name.
* Types of ordered indexes include:
	* **Primary**.
	* **Clustering**.
	* **Secondary**.

---
background-image: url(images/reg_background.png)
## Single Level Indexes
<img style="float: right;" width="40%" src="images/lecture_14/prim.png" />
* A **primary index**:
	* Ordered file whose records are of fixed length. 
	* Consists of two fields:
		* A **key** value - the primary key.
		* A **pointer** to a disk block.
* There is one entry in the index file for each block of data in the data file.
	* Has less entries than the number of records.
	* The first record in each data block is called the *anchor* record.
* Insertion and deletion operations are expansive.

---
background-image: url(images/reg_background.png)
## Single Level Indexes

* **Indexes** can be characterised as either:
	* **Dense**:
		* A file entry for each of the search **key** values (every record).
	* **Sparse**:
		* A file entry for only a subset of the search values.
		* Refers to multiple records per block.

* The **primary index** is an example of a **sparse** index.
	* It contains an entry for the record at the start of each block. 
		* I.e. the **Anchor record**.

---
background-image: url(images/reg_background.png)
## Single Level Indexes
<img style="float: right;" width="40%" src="images/lecture_14/cluster.png" />
* **Clustering indexes**:
	* Another example of a **sparse** index.
	* Ordering is based on a **non-key** field.
	* One entry in the index file for each distinct value.
* The index file's pointer points to the *first* block that contains the corresponding index value.
* Retrieving returns all records that correspond to a distinct key.

---
background-image: url(images/reg_background.png)
## Single Level Indexes
<img style="float: right;" width="40%" src="images/lecture_14/secondary.png" />
* **Secondary indexes**.
	* Primary access already exists:
	* Created on a candidate key or non-unique attribute.
	* The index file will be ordered by the index field.
* Does not use *Anchors* (non-key).
	* Therefore requires an entry for each record.
		* Implies **dense** indexing.
	* Entry pair will point to the *block* location not the actual record.

---
background-image: url(images/reg_background.png)
## Multilevel Indexes
<img style="float: right;" width="35%" src="images/lecture_14/multi.png" />
* In single-level indexing there was one entry for each index.
	* Ordered indexing allowed `\(O(log_{2}b_{i})\)` block access.
		* Where *b<sub>i</sub>* is the index for each block.
* **Multilevel indexing** files contain a hierarchy of pointers.
	* This reduces the search space by a facor of `\(bfr_{i}\)`.
	* This process is called ***fanning* out**.

---
background-image: url(images/reg_background.png)
## The Tree Structure

* A ***Tree*** is a data structure that consists of ***nodes***:
<img style="float: right;" width="50%" src="images/lecture_14/tree.png" />
	* Root node: *A*.
	* Branches: links (pointers) between nodes.
	* Leaf nodes: points to a memory location.
		* *E, J, C, G, H, K*.

* Multilevel indexing uses a form of **Search Tree**.

---
background-image: url(images/reg_background.png)
## Search Trees and B-Trees

* A ***Search Tree*** provides a guided path to a record.
<img style="float: right;" width="60%" src="images/lecture_14/stree.png" />
	* **Root node**:
		* Points to a subset of records.
	* **Leaf node**:
		* Points directly to a record.
* Formally: A search tree of ***order p***:
	*  Has *p-1* search values and *p* pointers:
		*  *< P <sub>1</sub>, K<sub>1</sub>,P<sub>2</sub>, K<sub>2</sub>,P<sub>q-1</sub>, K<sub>q-1</sub>P<sub>q</sub>\>* 
		* where *P<sub>i</sub>* is a pointer to a child node.
---
background-image: url(images/reg_background.png)
## Search Trees and B-Trees

* To search for a record we traverse the tree.
	* This involves checking index values at each **child node**.
* Trees can be:
	* **Balanced**:
<img style="float: right;" width="50%" src="images/lecture_9/btree.gif" />
		* Leaf nodes have the same number of parent connections.
	* **Unbalanced**:
		* Connections to leafe nodes are inconsistent.
* **B-Tree (balanced trees):**
	* Ensures that search speeds are consistent.
 	* This requires re-balancing in the case of deletion and insertion.

---
background-image: url(images/reg_background.png)
## Creating Indexes on PostgreSQL Tables

* Indexes can be created on fields within PostgreSQL using the `CREATE INDEX` clause. With the syntax:

```sql

CREATE INDEX ON table (index_field) USING method;

```
* `CREATE INDEX` constructs an index on the specified column(s) of the specified table. 
* Indexes are primarily used to enhance database performance. 
	* Inappropriate use can result in slower performance.

---
background-image: url(images/reg_background.png)
## Creating Indexes on PostgreSQL Tables


* The *index_field* can be specified as a column name. 
	* A **Primary key** can be used.
	* **Composite keys** require multicolumn indexing support.

* PostgreSQL uses the **B-tree** structure by default to implement its indexes.
* The *method* option allows different choices:
	* **B-tree**
	* **Hash**
	* **GiST**
	* **GIN**

---
background-image: url(images/reg_background.png)
## Summary

* Hardware Overview
* File Records and Operations
* Files of Ordered/Unordered Records
* Hashing Techniques
* Single Level Indexes
* Multilevel Indexes

---
background-image: url(images/reg_background.png)
# Reading

* Chapter 16 and 17 from ***Fundamentals of Database Systems*** by Elmazri and Navathe

---