Please enable JavaScript.

Coggle requires JavaScript to display documents.

Physical Databases (DBMS and File System (File System oversees data…

- - - - Functioning
        
        Decide how data is stored on disk blocks - efficient storage and manipulation of data
        
        Each PAS implements its own block organization
        
        Sequential and hash-based PAS:
        
        block header (trailer)
        
        page header (trailer)
        
        page dictionary with pointers
        
        useful part
        
        checksum
        
        Block factor: how many whole tuples it can contain
        
        Unspanned records
        
        Spanned records
        
        Each table into a primary PAS, may have secondary ones
        
        Primary: how to organize and store data
        
        Secondary: pointers to blocks, some cached values, indexes... to speed up queries
        
        Indexes are like books' indexes: an auxiliary structure for the efficient access to the tuples of a primary structure, based on the values of a given attribute or set of attributes –called the index key
        
        Every table should have primary (key sequenced, normally on unique keys) and secondary (keys attributes most used for selections and joins)
        
        Software modules similar to objects, storing a single data structure and offering primitives to manipulate it
        
        Primitives:
        
        insertion and update of a tuple
        
        tuple deletion (typically marked invalid)
        
        access to a field of a tuple (by offset and length)
      - Types
        
        Sequential
        
        Tuples in blocks are stored one after another
        
        Entry-sequenced
        
        Array, with an index
        
        Sequentially-ordered, with a key field
        
        Hash-based
        
        File managed as a sequence of buckets (typically one block large)
        
        Indexes are tables that match the bucket number to the address of the block in secondary memory storing it
        
        The placement and search of objects in the buckets is determined by a hash function (tuples have a key field)
        
        Evaluation: folding of the key field + hashing
        
        Optimal performance if using only 80% of the available space
        
        Possibility of collisions, solved with overflow chains
        
        Secondary hash-based PAS same organization - only contain keys and pointers to the primary
        
        Tree-based
        
        Most frequently used in relational DBMSs for secondary PAS, to make indexes primarily (but can also work as primary) - SQL indexes for tables are trees!
        
        Functioning
        
        Blocks in files are organized as nodes of a tree
        
        Balanced trees give optimal performance.
        
        Each node carries a series of pointers to reach various subtrees
        
        Possible organizations for tuples
        
        B+ trees
        
        1 more item...
        
        B trees
        
        1 more item...
        
        Tuples have a key field, that determines its node of belonging
- - - - Basic functioning
        
        The only available i/o operations (typically): moving a block from secondary to main memory and vice-versa
        
        Capacity organized into equal-sized blocks (few KB)
      - Units
        
        For decades
        
        Mechanical disks (Hard Disk Drives, HDD), "Winchester"s
        
        Time to read a block to main memory
        
        For random access (~ 10 ms):
        
        Seek time - heads to the right track
        
        Rotational Latency - waif for the right sector
        
        Transfer time
        
        Overhead
        
        10^4 times more time-costly to access data than main memory!
        
        For sequential access much quicker! (~ 10 ms) No 1. and 2.
        
        Platters, stack of heads, tracks, sectors and blocks (contiguous sequence of sectors on a single track)
        
        Now
        
        Solid DIsk Drives, SDD
        
        Similar performance actually (because erasing degrades performance)