Databases DBMS Review

This is not copied from Laurence S., and I give no credit to him. This is all my work.

Database

A collection of correlated data, managed by a DBMS; data we want to persist (after runtime ends)

DBMS

Software used to manage a databases - permits interaction of users and other programs with database content

Aspects of DBMS

Efficiency / Persistent / Reliability / Convenience / Multi-User / Safe / Massive

Query Parser

Builds the tree structure from textual form (syntax) of the query

Query Preprocessor

Checks semantics (calling correct tables, pages, tuples, etc.), checks data types, adds algebraic operators to tree

Query Optimizer

Finds the best sequence of actions (Query Plan) using the stats from the File Manager and metadata, especially indexes.

Buffer Allocation Process

1) Buffer Manager identifies that page containing Sales Dpt is not available
2) Storage manager reads Sales Dpt info from disk into memory
3) Log buffer is given before / after data to allow rollback in case of failure
4) User is able to commit, if successful logs are flushed to disk, else rollback

Buffer allocation process

1) Buffer Manager identifies that page containing Sales Dpt is not available
2) Storage manager reads Sales Dpt info from disk into memory
3) Log buffer is given before / after data to allow rollback in case of failure
4) User is able to commit, if successful logs are flushed to disk, else rollback

Execution Engine

Issues / performs sequence of requests based on query plan built in Query Compiler

Index/File/Record Manager (IFR Manager)

Checks to see if pages (segments of table, e.g., rows 1-1000, 1001-2000, etc.) are available to access in main memory / loaded. Knows the metadata necessary to find tables in pages.

Buffer Manager

Communicates with storage manager to obtain data on disk and place in memory, selects protocols to drop / add pages in DBMS (e.g., LiFo, FiFo, LRU, MRU), and Manages Buffers

Atomicity

A part of ACID denotating that there can be no partial actions - "all or nothing" queries.

Consistency

A part of ACID denotating that the database must be in a good state. If it is ever otherwise, it must fail.

Isolation

A part of ACID denotating that multi-user platforms with concurrent execution must only be transparent (usable) by one user at a time.

Durability

A part of ACID denotating that the database can recover from failures, errors, or misuse.

View Layer (View Level)

Shows different parts of a DB to users based on their inherent permissions and needs

Conceptual View Layer

A view layer of the DBMS that lets users see schema definitions

Physical View Layer

A view layer in the DBMS that lets users see the literal composition of data in DB (indexing, compression, etc.)

Schema

The types and data as variables in programming language; the structure of the DB - typically static. Includes the structure of each relation, too.

Key

A unique identifying attribute for each entity set, with primary keys acting as the primary identifier - tuples can not share keys

"is-a" relationships inherit keys from parent to child entity set, and weak entity sets require keys to hold a tangible definition

Weak Entity Sets

Entity sets which depend on a relation / strong entity set to have a tangible definition - e.g., set "Rooms" to set "Buildings". DB design calls for as few as possible

They are ALWAYS many to EXACTLY one.

Name the 3 strategies used to convert ER models to relations

Entity Relational, Object Oriented, Null

Entity Relational Subclass Relationship

Use of ONE relation for EACH subclass containing key attributes and subclass specific attributes, e.g. Movie -> Action, Romance, Comedy subsets. Objects that fall under multiple subclasses have representatives in them (some redundancy).

Object Oriented Subclass Relationship

Create 2^n relations for n subclasses - essentially all permutations of entity sets together (e.g., Movies(A), Movies_Anime(A, B), Movies_Action(A, C), Movies_Anime_Action(A, B, C))

Pro: Highly discrete, entities never appear in multiple relations Con: Rapidly expanding number of relations for more subclasses

Null Subclass Relationship

Just use one mega relation with NULL for non-corresponding attributes, no hierarchy

Pro: Efficient for few attributes Con: Less "organized", can fill many entries with null data

R1 := σC (R2)

Selection - R2 is being queried, C is a given condition, R1 is all tuples that meet that C found in R2

R1 := π_L (R2)

Projection - R2 is being queried, L is a list of attributes in R2's schema, R1 is constructed exclusively of the columns within R2's attributes L

R3 := R1 X R2

Cartesian Product - Get a subtable composed of ALL arrangements of R1's tuples paired with R2's tuples

R3 := R1 ⋈ R2

Natural Join - Join columns that have the same name; if vals per columns are equivalent, join the tuples which hold that corresponding data - if that is the case, place that column first in R3

R3 := R1 ⋈_C R2

Theta Join - Take the cartesian product of R1 and R2, then join all tuples that satisfy condition C - do not include tuples which do not satisfy C in R3