Outline

• Going From Logical to Physical Constructs
• Types of Records
• Quiz
• Types of Files

Introduction

• We have now finished our first pass of NoSQL database.
• In traditional database development, we first find out our data requirements and sketch, using E/R diagrams or UML, a conceptual design.
• Next, we map this conceptual design into tables (traditional RDBMS or column store) or collections/predicates/common graph labels (Document Store, Triple store, Graph database). This gives a logical design.
• This logical design must then be physically arranged on our storage media for efficient access.
• This physical design relies on our knowledge of the internal structures our RDBMS uses to actually store things.
• This week we are going to quickly cover some common internal representations before switching to a discussion about physical database design.

Memory Hierarchy Review

• In our analysis of these constructs, we will assume people are familiar with the so-called storage hierachy: CPU registers are faster than CPU Cache which is faster than RAM which is faster than (persistent storage types) HDD or SSD which is faster than tape.
• Raw read speeds for RAM (high thousands of MB/s) are about 10x faster than SSD (about a thousand MB/s) which in turn 10x faster than HDDs (low hundred MB/s).
• In practice all the data we want is typically not adjacent to each other on an HDD, so HDD speeds are much slower.
• On the other hand, flash-based memory suffers memory wear and may only be overwritten (program-erase cycles) some fixed number of times (on the orders of a couple 100,000).
• Data on disks is organized in blocks.
• Disk reads and disk writes (Disk I/Os) operate on the block level (usually around 4kb).

Hard Drive Characteristics

• A given platter is broken into track which are further broken into arcs called sectors, several of wich would be grouped to make a disk block.
• The time to access a sector on the disk and read it into RAM from an HDD is split into the following steps:
  • Processor/controller time
  • Time to position head to correct cylinder (seek time). 5-10mS
  • Time to rotate head over a sector (rotational latency) - this depends only on rotational speed. For example, for a 7200 RPM drive, Time/round = 1/(7200 rounds/min) = 60/7200 S/round = 8.3mS. Average rotational latency is half this, so about 4.2 mS.
  • Transfer time - time for whole sector/gaps between adjacent sectors to rotate under head.

Going From Logical Design to Physical Constructs

• At the logical level, we have a generic concept of a record, which we might view as a tuple in one relation, a single key-value in a key-value (tuple,document) store, a single RDF triple, one edge in a graph, etc
• A record is described by values for its attributes. Records might further be organized into types, relations, tables, collections, etc. These in turn are grouped into sets of types, sets of relation, sets of collections, etc (aka databases).
• We might have logical relationships between our types, tables, etc such as foreign keys.
• To go to the physical level, we speak of mapping attribute values to data items that represent a specific value on a physical storage system.
• We map records to stored records and tables are mapped to physical files.
• Sometimes we might need to store records from two tables together in a mixed file.
• Physical files might contain additional structures such as indexes and pointers to speed up search and/or updates.
• A physical database is an integrated collection of physical files of stored records.

Example of Going From Conceptual Model to Physical Model

Types of Records

• A stored record is typically built out of bit-level representations of numerical types, fixed or variable length character types, date types, boolean types, and BLOB or CLOB types.
• Three common ways to organize these are:
  • relative location - store each attribute of the record entity in physically adjacent address according to the order of the attributes in its defining schema (i.e., we don't store the attribute type with the attribute value). For variable length items begin the item with its length or use a delimeter character sequence.
    • Very space efficient.
    • For highly irregular records (say document stores), this approach tends not to work well
    
  • embedded identification - store the attribute type and name before the attribute value. Only include non-empty attributes.
    • Very similar to JSON or XML storage.
    • Uses more space.
    • Works better for irregular records.
    
  • pointers and lists - there are different possibilities for this. We could store attributes using relative location. However, if an attribute is multivalued (for example, addresses), the first value is stored in a record followed by a pointer to additional values.
    

Variable Length Records

• The previous slide shows three ways of handling variable length records (length encoding, delimiters and chaining pointers). A fourth way is to have a record begin with a set of byte offset pointers in the order of the top level attributes. These pointers point to the starting byte of that attribute. If an attribute itself can have children we can begin the attribute value with a list of offsets for its children.
• With any luck our records fit within the same page/block. If not, offset pointers, or pointers from the previous slide need to be pairs (page no, byte offset).

Blocking Factor

• Eventually, records need to be physcially stored in memory pages (RAM) or blocks (Disks either SSD or HD).
• Such a page might begin with a header, followed by pointers to starts of records within a block (a slotted page).
• The expected number of records that can fit in a block for a given table or collection is called the blocking factor:
  `BF = E[\lfloor {BS}/{RS} \rfloor]` where BF := blocking factor, BS := Block Size, RS := record size.

Quiz

Which of the following is true?

1. To make a new collection in MongoDB we use the CREATE COLLECTION command.
2. Because document stores are essentially hash tables, a document store never supports indexes.
3. The syntax to make a new table in Cassandra, a column-oriented database, looks very similar to standard SQL.

Search Keys and Non-Key Attributes

• The records in a file are organized so that they can be retrieved by one or more search keys.
• Here a search key is a single attribute type whose values determine the criteria according to which records are retrieved.
• A search key might be a primary key such as employee id.
• A search key can also be a non key attribute. For example, date might be used to retrieve many flights on a particular day.
• Besides search keys there might be other record attributes which add information to the record but are non-frequently searched on, for example, customer street address.

Types of Files

• File Organization is typically split into two categories related to the kind of search key which will be used to access the data:
  1. Primary File Organization Methods - these methods are related to how records are physically positioned on the storage medium. Examples are heap file, sequential file, random file, and indexed sequential file organization.
     • More advanced methods relate a particular search key to where a record will be stored.
     • As such, one can only physically organize a file by one search key. For example, employee records might be stored according to increasing employee id.
  2. Secondary File Organization Methods - these methods relate to files used to quickly retrieve records for search keys which were not for the primary file organization. These might include secondary index structures.

Directories and Files

• To the operating system a disk looks like a sequence of blocks.
• Some of these blocks are reserved for a search tree (often a B-tree) structure called a directory. This directory associates file names with block pointers to files (and also maybe the last block in a file).
• The OS also typically maintains a unused block list.
• A file is basically a linked list of blocks. i.e., each block contains a header which includes a pointer to the next block or null if this is the last block in the file.
• Sometimes a file might have a more sophisticated structures (like B-trees in Sqlite) which allow us quick access to the `i`th block in the file, or more often, we assume that the file is organized as contiguous blocks on disk so the `i`th block of the file can be quickly computed.

Heap, Sequential, and Random File Organization

• For database systems, a heap file is an unordered collection of records stored in a file. If we want to add a new record to a heap file, we follow the last block pointer from the directory for the file and add the record there.
• We might need to allocate a new block for the record if there is not enough space in this block, but this process is still O(1) time.
• Lookup and update though will take O(# of blocks in file) time. (We are assuming time to read blocks is much longer than any RAM operations so will only count block reads in our times).
• If rather than use a heap, we instead store the records in increasing or decreasing order of a search key we get a sequential file.
• Lookup in this setting can be done via binary search so is O(log(# of blocks in file)) time.
• When creating such a file we make sure that we have a little overhead space in each block to hold new records and/or pointers to additional overflow blocks, so inserts/deletes/updates will also hopefully be O(log(# of blocks in file)) time.
• In random file organization a file consists of a sequence of buckets. Each bucket is a linked list of block.
• Such files are organization by a search key, and when a new record is added, a key-to-address transformation is done typically via a hash function to determine which bucket to store a record in.
• As long as the buckets don't need to take too many blocks lookup/inserts/updates take O(1) time.
• The Key parameter to determine this is the loading factor which is defined as:
  `LF := NR/(BS times NB)`
  Here NR is the number of records, BS is the bucket size, and NB is the number of buckets.
• Typically, we want LF to be around 0.7 or 0.9 to achieve could storage versus efficiency performance.

Avoiding Overflow Blocks, Preventing Row Migration

• The code to implement database relations is simpler/faster if we can avoid having to use overflow blocks and/or perform row migration.
• Oracle has two parameters which are useful in avoiding overflow blocks and preventing row migration: PCTUSED and PCTFREE:
  • PCTFREE - (defaults to 10) percentage of a block that is reserved for future updates to existing rows.
  • PCTUSED - (defaults to 40) percentage full a block removed from the free blocks list must fall below to be added back to the free blocks list.
• These values can be specified in a create table, create index, or alter table statement. For example,

  CREATE TABLE SOME_TABLE_NAME ( /*def of columns */ ) PCTFREE 15 PCTUSED 75;
  

• Microsoft SQL Server has a concept FILLFACTOR which corresponds to 100 - PCTFREE, but SQL Server doesn't have a notion corresponding to PCTUSED.
• DB2 allows one to specify the analogous values PCTFREE (for table and index) and MINPCTUSED (for an index only).
• Postgres, mysql, and sqlite don't have an analogous concept.