hawkw/CMPSC380 Day 1.md

## CMPSC380 Day 1.md

      
    Raw
  

              CMPSC380 Day 1.md
            
          
    CMPSC380: DATABASES
August 26th, 2014

Data Management

How much data is there in the world today?

infinite amounts of data in an infinite universe
~1 yottabyte stored by people


How much data is produced every day?

~ 2.8 quintillion bytes/day


How much can we store every day?

Entropy is a thing
Computers r hard 'n' scary


About the Course

Course Objectives


Topics:

Algorithms for managing data

Relational databases & query ;anguages
Object-oriented data storage
XML


Issues

Concurrency control
Analysis of performance
high level & low-level


Performance Objectives:

Select right tools for data management problems
Familiar with data management concepts & skills
Comfortable with details of using different DBMS


Grading Policies


Always come to class (this is a "mandatory activity" ~ K)
Professor meeting record - meet with Professor Kapfhammer for >30 minutes/semester
Work:

First exam, interim exam, final exam
Laboratory assignments
Final project (group)


Assignment submission:

Turn assignment in on time (100% grade)
Turn assignment in one week late (85% of grade)
Not turn in assignment (0% grade)


Read assigned chapters (for your own good)

Notes

Common-Ground Definitions

What is a ...

database: A structured or organized collection of data.
database management system (DBMS): A software system that provides the infrastructure for interacting with a database.

Insert new data
Query database
Modify/Update existing records
Delete
Create new DBs


file processing system (FPS): a collection of files F_1 ... F_n where the user and OS are responsible for DMBS functions

Relational Databases

Most common type of database management system - most data is relational in nature
Common relational database management systems:

SQLite

Used in:

OSX
Android


MySQL
PostgreSQL
Oracle Database
Microsoft SQL
Microsoft Access
IBM DB/2
IBM Informix

Where are DMBS used?

Operating Systems

OS X
Android


Statistics programs

R
SPSS


Web apps
Mail clients

Why use relational DBs?

common/popular
data model fits data

Trade-Offs in Data Management

FPS vs DMBS
DMBS:
    + Easy to select many records efficiently
    + Capable of storing many types of data
    + Better at handling concurrency
    + Model real-world relationships between data
    - May be more expensive
    - May take more time to set up and configure
FPS:
    - Complexity
    - Security challenges
    - Duplication
    - Page faults (decreased performance)
    + Can be interacted with using shell/GUIs

Evaluation metrics:
+ Reliability
+ Complexity (time & space)
+ Performance
+ Expense (maintainace & purchasing)
+ Accessability
+ Adaptability
+ Security
Next class: FPS disadvantages

  
## CMPSC380 Day 10.md

      
    Raw
  

              CMPSC380 Day 10.md
            
          
    CMPSC380: DATABASES
September 23rd, 2014

SQL Data Types


Data types

Possible values
Possible operations


In SQL:

char(n) - n is length
varchar(n) - n is max length
int and smallint

Bit depth:

size or space overhead
length of number


numeric(p, d)

p: total number of digits
d: number of digits to right of decimal point


float(n):


CREATE TABLE Statement


Part of the DDL (Data definition language)
E.g.:

     CREATE TABLE   NAME 
        ( [attribute name], {type} #< Integrity constraints 1...n >
          [attribute name], {type})

ROWID: unique value for all rows in table
IF NOT EXISTS: doesn't create table if table by that name already exists, change error message displayed

Distinction between DML & DDL


DELETE FROM R - DML (delete state (data))
DROP TABLE R - DDL (delete structure (schema))

SELECTing Data

SELECT A1, A2, ..., Am  # Attributes/columns
FROM R1, R2, ..., Rm    # Relations
WHERE P                 # Predicate (boolean)

SELECT * FROM...: * = all attributes
How does the DBMS actually execute/run a SELECT?

FROM clause (get tables we are selecting from)
WHERE clause (filter out the rows we want)
SELECT the attributes from the rows


Complexity analysis of Cartesian product (page 68):

Worst case time complexity: O(n^m)

My professor just said "Wha-ha!"
m: number of relations
n: maximum number of tuples


JoSQL SELECT: instead of SELECT ... FROM <relation>, you say SELECT FROM <Java type>
Handling null values:

"Nulls are distinct in a UNIQUE column": this is like in Java how NaN != NaN. If you say that a column has to be unique, two null values are not considered the same.
1 < null = true, false, null?
true or null = null
true and null = false, null


## CMPSC380 Day 11.html
<!DOCTYPE html><html><head><meta charset="utf-8"><meta name="cmpsc380" content="DATABASES"><style>body {
  width: 45em;
  border: 1px solid #ddd;
  outline: 1300px solid #fff;
  margin: 16px auto;
}

body .markdown-body
{
  padding: 30px;
}

@font-face {
  font-family: fontawesome-mini;
  src: url(data:font/woff;charset=utf-8;base64,d09GRgABAAAAAAzUABAAAAAAFNgAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAABGRlRNAAABbAAAABwAAAAcZMzaOEdERUYAAAGIAAAAHQAAACAAOQAET1MvMgAAAagAAAA+AAAAYHqhde9jbWFwAAAB6AAAAFIAAAFa4azkLWN2dCAAAAI8AAAAKAAAACgFgwioZnBnbQAAAmQAAAGxAAACZVO0L6dnYXNwAAAEGAAAAAgAAAAIAAAAEGdseWYAAAQgAAAFDgAACMz7eroHaGVhZAAACTAAAAAwAAAANgWEOEloaGVhAAAJYAAAAB0AAAAkDGEGa2htdHgAAAmAAAAAEwAAADBEgAAQbG9jYQAACZQAAAAaAAAAGgsICJBtYXhwAAAJsAAAACAAAAAgASgBD25hbWUAAAnQAAACZwAABOD4no+3cG9zdAAADDgAAABsAAAAmF+yXM9wcmVwAAAMpAAAAC4AAAAusPIrFAAAAAEAAAAAyYlvMQAAAADLVHQgAAAAAM/u9uZ4nGNgZGBg4ANiCQYQYGJgBEJuIGYB8xgABMMAPgAAAHicY2Bm42OcwMDKwMLSw2LMwMDQBqGZihmiwHycoKCyqJjB4YPDh4NsDP+BfNb3DIuAFCOSEgUGRgAKDgt4AAB4nGNgYGBmgGAZBkYGEAgB8hjBfBYGCyDNxcDBwMTA9MHhQ9SHrA8H//9nYACyQyFs/sP86/kX8HtB9UIBIxsDXICRCUgwMaACRoZhDwA3fxKSAAAAAAHyAHABJQB/AIEAdAFGAOsBIwC/ALgAxACGAGYAugBNACcA/wCIeJxdUbtOW0EQ3Q0PA4HE2CA52hSzmZDGe6EFCcTVjWJkO4XlCGk3cpGLcQEfQIFEDdqvGaChpEibBiEXSHxCPiESM2uIojQ7O7NzzpkzS8qRqnfpa89T5ySQwt0GzTb9Tki1swD3pOvrjYy0gwdabGb0ynX7/gsGm9GUO2oA5T1vKQ8ZTTuBWrSn/tH8Cob7/B/zOxi0NNP01DoJ6SEE5ptxS4PvGc26yw/6gtXhYjAwpJim4i4/plL+tzTnasuwtZHRvIMzEfnJNEBTa20Emv7UIdXzcRRLkMumsTaYmLL+JBPBhcl0VVO1zPjawV2ys+hggyrNgQfYw1Z5DB4ODyYU0rckyiwNEfZiq8QIEZMcCjnl3Mn+pED5SBLGvElKO+OGtQbGkdfAoDZPs/88m01tbx3C+FkcwXe/GUs6+MiG2hgRYjtiKYAJREJGVfmGGs+9LAbkUvvPQJSA5fGPf50ItO7YRDyXtXUOMVYIen7b3PLLirtWuc6LQndvqmqo0inN+17OvscDnh4Lw0FjwZvP+/5Kgfo8LK40aA4EQ3o3ev+iteqIq7wXPrIn07+xWgAAAAABAAH//wAPeJyFlctvG1UUh+/12DPN1B7P3JnYjj2Ox4/MuDHxJH5N3UdaEUQLqBIkfQQioJWQ6AMEQkIqsPGCPwA1otuWSmTBhjtps2ADWbJg3EpIXbGouqSbCraJw7kzNo2dRN1cnXN1ZvT7zuuiMEI7ncizyA0URofRBJpCdbQuIFShYY+GZRrxMDVtih5TwQPHtXDFFSIKoWIbuREBjLH27Ny4MsbVx+uOJThavebgVrNRLAiYx06rXsvhxLgWx9xpfHdrs/ekc2Pl2cpPCVEITQpwbj8VQhfXSq2m+Wxqaq2D73Kne5e3NjHqQNj3CRYlJlgUl/jRNP+2Gs2pNYRQiOnmUaQDqm30KqKiTTWPWjboxnTWpvgxjXo0KrtZXAHt7hwIz0YVcj88JnKlJKi3NPAwLyDwZudSmJSMMJFDYaOkaol6XtESx3Gt1VTytdZJ3DCLeaVhVnCBH1fycHTxFXwPX+l2e3d6H/TufGGmMTLTnbSJUdo00zuBswMO/nl3YLeL/wnu9/limCuD3vC54h5NBVz6Li414AI8Vx3iiosKcQXUbrvhFFiYb++HN4DaF4XzFW0fIN4XDWJ3a3XQoq9V8WiyRmdsatV9xUcHims1JloH0YUa090G3Tro3mC6c01f+YwCPquINr1PTaCP6rVTOOmf0GE2dBc7zWIhji3/5MchSuBHgDbU99RMWt3YUNMZMJmx92YP6NsHx/5/M1yvInpnkIOM3Z8fA3JQ2lW1RFC1KaBPDFXNAHYYvGy73aYZZZ3HifbeuiVZCpwA3oQBs0wGPYJbJfg60xrKEbKiNtTe1adwrpBRwlAuQ3q3VRaX0QmQ9a49BTSCuF1MLfQ6+tinOubRBZuWPNoMevGMT+V41KitO1is3D/tpMcq1JHZqDHGs8DoYGDkxJgKjHROeTCmhZvzPm9pod+ltKm4PN7Dyvvldlpsg8D+4AUJZ3F/JBstZz7cbFRxsaAGV6yX/dkcycWf8eS3QlQea+YLjdm3yrOnrhFpUyKVvFE4lpv4bO3Svx/6F/4xmiDu/RT5iI++lko18mY1oX+5UGKR6kmVjM/Zb76yfHtxy+h/SyQ0lLdpdKy/lWB6szatetQJ8nZ80A2Qt6ift6gJeavU3BO4gtxs/KCtNPVibCtYCWY3SIlSBPKXZALXiIR9oZeJ1AuMyxLpHIy/yO7vSiSE+kZvk0ihJ30HgHfzZtEMmvV58x6dtqns0XTAW7Vdm4HJ04OCp/crOO7rd9SGxQAE/mVA9xRN+kVSMRFF6S9JFGUtthkjBA5tFCWc2l4V43Ex9GmUP3SI37Jjmir9KqlaDJ4S4JB3vuM/jzyH1+8MuoZ+QGzfnvPoJb96cZlWjMcKLfgDwB7E634JTY+asjsPzS5CiVnEWY+KsrsIN5rn3mAPjqmQBxGjcGKB9f9ZxY3mYC2L85CJ2FXIxKKyHk+dg0FHbuEc7D5NzWUX32WxFcWNGRAbvwSx0RmIXVDuYySafluQBmzA/ssqJAMLnli+WIC90Gw4lm85wcp0qjArEDPJJV/sSx4P9ungTpgMw5gVC1XO4uULq0s3v1rqLi0vX/z65vlH50f8T/RHmSPTk5xxWBWOluMT6WiOy+tdvWxlV/XQb3o3c6Ssr+r6I708GsX9/nzp1tKFh0s3v7m4vAy/Hnb/KMOvc1wump6Il48K6mGDy02X9Yd65pa+nQIjk76lWxCkG8NBCP0HQS9IpAAAeJxjYGRgYGBhcCrq214Qz2/zlUGenQEEzr/77oug/zewFbB+AHI5GJhAogBwKQ0qeJxjYGRgYH3/P46BgZ0BBNgKGBgZUAEPAE/7At0AAAB4nGNngAB2IGYjhBsYBAAIYADVAAAAAAAAAAAAAFwAyAEeAaACCgKmAx4DggRmAAAAAQAAAAwAagAEAAAAAAACAAEAAgAWAAABAAChAAAAAHiclZI7bxQxFIWPd/JkUYQChEhIyAVKgdBMskm1QkKrRETpQiLRUczueB/K7HhlOxttg8LvoKPgP9DxFxANDR0tHRWi4NjrPIBEgh1p/dm+vufcawNYFWsQmP6e4jSyQB2fI9cwj++RE9wTjyPP4LYoI89iWbyLPIe6+Bh5Hs9rryMv4GbtW+RF3EhuRa7jbrIbeQkPkjdUETOLnL0Kip4FVvAhco1RXyMnSPEz8gzWxE7kWTwUp5HnsCLeR57HW/El8gJWa58iL+JO7UfkOh4l9yMv4UnyEtvQGGECgwF66MNBooF1bGCL1ELB/TYU+ZBRlvsKQ44Se6jQ4a7hef+fh72Crv25kp+8lNWGmeKoOI5jJLb1aGIGvb6TjfWNLdkqdFvJw4l1amjlXtXRZqRN7lSRylZZyhBqpVFWmTEXgWfUrpi/hZOQXdOd4rKuXOtEWT3k5IArPRzTUU5tHKjecZkTpnVbNOnt6jzN8240GD4xtikvZW56043rPMg/dS+dlOceXoR+WPbJ55Dsekq1lJpnypsMUsYOdCW30o103Ytu/lvh+5RWFLfBjm9/N8hJntPhvx92rnoE/kyHdGasGy754kw36vsVf/lFeBi+0COu+cfgQr42G3CRpeLoZ53gmfe3X6rcKt5oVxnptHR9JS8ehVUd5wvvahN2uqxOOpMXapibI5k7Zwbt4xBSaTfoKBufhAnO/uqNcfK8OTs0OQ6l7JIqFjDhYj5WcjevCnI/1DDiI8j4ndWb/5YzDZWh79yomWXeXj7Nnw70/2TIeFPTrlSh89k1ObOSRVZWZfgF0r/zJQB4nG2JUQuCQBCEd07TTg36fb2IyBaLd3vWaUh/vmSJnvpgmG8YcmS8X3Shf3R7QA4OBUocUKHGER5NNbOOEvwc1txnuWkTRb/aPjimJ5vXabI+3VfOiyS15UWvyezM2xiGOPyuMohOH8O8JiO4Af+FsAGNAEuwCFBYsQEBjlmxRgYrWCGwEFlLsBRSWCGwgFkdsAYrXFhZsBQrAAA=) format('woff');
}

@font-face {
  font-family: octicons-anchor;
  src: url(data:font/woff;charset=utf-8;base64,d09GRgABAAAAAAYcAA0AAAAACjQAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAABGRlRNAAABMAAAABwAAAAca8vGTk9TLzIAAAFMAAAARAAAAFZG1VHVY21hcAAAAZAAAAA+AAABQgAP9AdjdnQgAAAB0AAAAAQAAAAEACICiGdhc3AAAAHUAAAACAAAAAj//wADZ2x5ZgAAAdwAAADRAAABEKyikaNoZWFkAAACsAAAAC0AAAA2AtXoA2hoZWEAAALgAAAAHAAAACQHngNFaG10eAAAAvwAAAAQAAAAEAwAACJsb2NhAAADDAAAAAoAAAAKALIAVG1heHAAAAMYAAAAHwAAACABEAB2bmFtZQAAAzgAAALBAAAFu3I9x/Nwb3N0AAAF/AAAAB0AAAAvaoFvbwAAAAEAAAAAzBdyYwAAAADP2IQvAAAAAM/bz7t4nGNgZGFgnMDAysDB1Ml0hoGBoR9CM75mMGLkYGBgYmBlZsAKAtJcUxgcPsR8iGF2+O/AEMPsznAYKMwIkgMA5REMOXicY2BgYGaAYBkGRgYQsAHyGMF8FgYFIM0ChED+h5j//yEk/3KoSgZGNgYYk4GRCUgwMaACRoZhDwCs7QgGAAAAIgKIAAAAAf//AAJ4nHWMMQrCQBBF/0zWrCCIKUQsTDCL2EXMohYGSSmorScInsRGL2DOYJe0Ntp7BK+gJ1BxF1stZvjz/v8DRghQzEc4kIgKwiAppcA9LtzKLSkdNhKFY3HF4lK69ExKslx7Xa+vPRVS43G98vG1DnkDMIBUgFN0MDXflU8tbaZOUkXUH0+U27RoRpOIyCKjbMCVejwypzJJG4jIwb43rfl6wbwanocrJm9XFYfskuVC5K/TPyczNU7b84CXcbxks1Un6H6tLH9vf2LRnn8Ax7A5WQAAAHicY2BkYGAA4teL1+yI57f5ysDNwgAC529f0kOmWRiYVgEpDgYmEA8AUzEKsQAAAHicY2BkYGB2+O/AEMPCAAJAkpEBFbAAADgKAe0EAAAiAAAAAAQAAAAEAAAAAAAAKgAqACoAiAAAeJxjYGRgYGBhsGFgYgABEMkFhAwM/xn0QAIAD6YBhwB4nI1Ty07cMBS9QwKlQapQW3VXySvEqDCZGbGaHULiIQ1FKgjWMxknMfLEke2A+IJu+wntrt/QbVf9gG75jK577Lg8K1qQPCfnnnt8fX1NRC/pmjrk/zprC+8D7tBy9DHgBXoWfQ44Av8t4Bj4Z8CLtBL9CniJluPXASf0Lm4CXqFX8Q84dOLnMB17N4c7tBo1AS/Qi+hTwBH4rwHHwN8DXqQ30XXAS7QaLwSc0Gn8NuAVWou/gFmnjLrEaEh9GmDdDGgL3B4JsrRPDU2hTOiMSuJUIdKQQayiAth69r6akSSFqIJuA19TrzCIaY8sIoxyrNIrL//pw7A2iMygkX5vDj+G+kuoLdX4GlGK/8Lnlz6/h9MpmoO9rafrz7ILXEHHaAx95s9lsI7AHNMBWEZHULnfAXwG9/ZqdzLI08iuwRloXE8kfhXYAvE23+23DU3t626rbs8/8adv+9DWknsHp3E17oCf+Z48rvEQNZ78paYM38qfk3v/u3l3u3GXN2Dmvmvpf1Srwk3pB/VSsp512bA/GG5i2WJ7wu430yQ5K3nFGiOqgtmSB5pJVSizwaacmUZzZhXLlZTq8qGGFY2YcSkqbth6aW1tRmlaCFs2016m5qn36SbJrqosG4uMV4aP2PHBmB3tjtmgN2izkGQyLWprekbIntJFing32a5rKWCN/SdSoga45EJykyQ7asZvHQ8PTm6cslIpwyeyjbVltNikc2HTR7YKh9LBl9DADC0U/jLcBZDKrMhUBfQBvXRzLtFtjU9eNHKin0x5InTqb8lNpfKv1s1xHzTXRqgKzek/mb7nB8RZTCDhGEX3kK/8Q75AmUM/eLkfA+0Hi908Kx4eNsMgudg5GLdRD7a84npi+YxNr5i5KIbW5izXas7cHXIMAau1OueZhfj+cOcP3P8MNIWLyYOBuxL6DRylJ4cAAAB4nGNgYoAALjDJyIAOWMCiTIxMLDmZedkABtIBygAAAA==) format('woff');
}

.markdown-body {
  font-family: sans-serif;
  -ms-text-size-adjust: 100%;
  -webkit-text-size-adjust: 100%;
  color: #333333;
  overflow: hidden;
  font-family: "Helvetica Neue", Helvetica, "Segoe UI", Arial, freesans, sans-serif;
  font-size: 16px;
  line-height: 1.6;
  word-wrap: break-word;
}

.markdown-body a {
  background: transparent;
}

.markdown-body a:active,
.markdown-body a:hover {
  outline: 0;
}

.markdown-body b,
.markdown-body strong {
  font-weight: bold;
}

.markdown-body mark {
  background: #ff0;
  color: #000;
  font-style: italic;
  font-weight: bold;
}

.markdown-body sub,
.markdown-body sup {
  font-size: 75%;
  line-height: 0;
  position: relative;
  vertical-align: baseline;
}
.markdown-body sup {
  top: -0.5em;
}
.markdown-body sub {
  bottom: -0.25em;
}

.markdown-body h1 {
  font-size: 2em;
  margin: 0.67em 0;
}

.markdown-body img {
  border: 0;
}

.markdown-body hr {
  -moz-box-sizing: content-box;
  box-sizing: content-box;
  height: 0;
}

.markdown-body pre {
  overflow: auto;
}

.markdown-body code,
.markdown-body kbd,
.markdown-body pre,
.markdown-body samp {
  font-family: monospace, monospace;
  font-size: 1em;
}

.markdown-body input {
  color: inherit;
  font: inherit;
  margin: 0;
}

.markdown-body html input[disabled] {
  cursor: default;
}

.markdown-body input {
  line-height: normal;
}

.markdown-body input[type="checkbox"] {
  box-sizing: border-box;
  padding: 0;
}

.markdown-body table {
  border-collapse: collapse;
  border-spacing: 0;
}

.markdown-body td,
.markdown-body th {
  padding: 0;
}

.markdown-body .codehilitetable {
  border: 0;
  border-spacing: 0;
}

.markdown-body .codehilitetable tr {
  border: 0;
}

.markdown-body .codehilitetable pre,
.markdown-body div.codehilite {
  margin: 0;
}

.markdown-body .linenos,
.markdown-body .code,
.markdown-body .codehilitetable td {
  border: 0;
  padding: 0;
}

.markdown-body td:not(.linenos) .linenodiv {
  padding: 0 !important;
}

.markdown-body .code {
  width: 100%;
}

.markdown-body .linenos div pre,
.markdown-body .linenodiv pre,
.markdown-body .linenodiv {
  border: 0;
  -webkit-border-radius: 0;
  -moz-border-radius: 0;
  border-radius: 0;
  -webkit-border-top-left-radius: 3px;
  -webkit-border-bottom-left-radius: 3px;
  -moz-border-radius-topleft: 3px;
  -moz-border-radius-bottomleft: 3px;
  border-top-left-radius: 3px;
  border-bottom-left-radius: 3px;
}

.markdown-body .code div pre,
.markdown-body .code div {
  border: 0;
  -webkit-border-radius: 0;
  -moz-border-radius: 0;
  border-radius: 0;
  -webkit-border-top-right-radius: 3px;
  -webkit-border-bottom-right-radius: 3px;
  -moz-border-radius-topright: 3px;
  -moz-border-radius-bottomright: 3px;
  border-top-right-radius: 3px;
  border-bottom-right-radius: 3px;
}

.markdown-body * {
  -moz-box-sizing: border-box;
  box-sizing: border-box;
}

.markdown-body input {
  font: 13px Helvetica, arial, freesans, clean, sans-serif, "Segoe UI Emoji", "Segoe UI Symbol";
  line-height: 1.4;
}

.markdown-body a {
  color: #4183c4;
  text-decoration: none;
}

.markdown-body a:hover,
.markdown-body a:focus,
.markdown-body a:active {
  text-decoration: underline;
}

.markdown-body hr {
  height: 0;
  margin: 15px 0;
  overflow: hidden;
  background: transparent;
  border: 0;
  border-bottom: 1px solid #ddd;
}

.markdown-body hr:before,
.markdown-body hr:after {
  display: table;
  content: " ";
}

.markdown-body hr:after {
  clear: both;
}

.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
  margin-top: 15px;
  margin-bottom: 15px;
  line-height: 1.1;
}

.markdown-body h1 {
  font-size: 30px;
}

.markdown-body h2 {
  font-size: 21px;
}

.markdown-body h3 {
  font-size: 16px;
}

.markdown-body h4 {
  font-size: 14px;
}

.markdown-body h5 {
  font-size: 12px;
}

.markdown-body h6 {
  font-size: 11px;
}

.markdown-body blockquote {
  margin: 0;
}

.markdown-body ul,
.markdown-body ol {
  padding: 0;
  margin-top: 0;
  margin-bottom: 0;
}

.markdown-body ol ol,
.markdown-body ul ol {
  list-style-type: lower-roman;
}

.markdown-body ul ul ol,
.markdown-body ul ol ol,
.markdown-body ol ul ol,
.markdown-body ol ol ol {
  list-style-type: lower-alpha;
}

.markdown-body dd {
  margin-left: 0;
}

.markdown-body code,
.markdown-body pre,
.markdown-body samp {
  font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace;
  font-size: 12px;
}

.markdown-body pre {
  margin-top: 0;
  margin-bottom: 0;
}

.markdown-body kbd {
  background-color: #e7e7e7;
  background-image: -moz-linear-gradient(#fefefe, #e7e7e7);
  background-image: -webkit-linear-gradient(#fefefe, #e7e7e7);
  background-image: linear-gradient(#fefefe, #e7e7e7);
  background-repeat: repeat-x;
  border-radius: 2px;
  border: 1px solid #cfcfcf;
  color: #000;
  padding: 3px 5px;
  line-height: 10px;
  font: 11px Consolas, "Liberation Mono", Menlo, Courier, monospace;
  display: inline-block;
}

.markdown-body>*:first-child {
  margin-top: 0 !important;
}

.markdown-body>*:last-child {
  margin-bottom: 0 !important;
}

.markdown-body .headeranchor-link {
  position: absolute;
  top: 0;
  bottom: 0;
  left: 0;
  display: block;
  padding-right: 6px;
  padding-left: 30px;
  margin-left: -30px;
}

.markdown-body .headeranchor-link:focus {
  outline: none;
}

.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
  position: relative;
  margin-top: 1em;
  margin-bottom: 16px;
  font-weight: bold;
  line-height: 1.4;
}

.markdown-body h1 .headeranchor,
.markdown-body h2 .headeranchor,
.markdown-body h3 .headeranchor,
.markdown-body h4 .headeranchor,
.markdown-body h5 .headeranchor,
.markdown-body h6 .headeranchor {
  display: none;
  color: #000;
  vertical-align: middle;
}

.markdown-body h1:hover .headeranchor-link,
.markdown-body h2:hover .headeranchor-link,
.markdown-body h3:hover .headeranchor-link,
.markdown-body h4:hover .headeranchor-link,
.markdown-body h5:hover .headeranchor-link,
.markdown-body h6:hover .headeranchor-link {
  height: 1em;
  padding-left: 8px;
  margin-left: -30px;
  line-height: 1;
  text-decoration: none;
}

.markdown-body h1:hover .headeranchor-link .headeranchor,
.markdown-body h2:hover .headeranchor-link .headeranchor,
.markdown-body h3:hover .headeranchor-link .headeranchor,
.markdown-body h4:hover .headeranchor-link .headeranchor,
.markdown-body h5:hover .headeranchor-link .headeranchor,
.markdown-body h6:hover .headeranchor-link .headeranchor {
  display: inline-block;
}

.markdown-body h1 {
  padding-bottom: 0.3em;
  font-size: 2.25em;
  line-height: 1.2;
  border-bottom: 1px solid #eee;
}

.markdown-body h2 {
  padding-bottom: 0.3em;
  font-size: 1.75em;
  line-height: 1.225;
  border-bottom: 1px solid #eee;
}

.markdown-body h3 {
  font-size: 1.5em;
  line-height: 1.43;
}

.markdown-body h4 {
  font-size: 1.25em;
}

.markdown-body h5 {
  font-size: 1em;
}

.markdown-body h6 {
  font-size: 1em;
  color: #777;
}

.markdown-body p,
.markdown-body blockquote,
.markdown-body ul,
.markdown-body ol,
.markdown-body dl,
.markdown-body table,
.markdown-body pre,
.markdown-body .admonition {
  margin-top: 0;
  margin-bottom: 16px;
}

.markdown-body hr {
  height: 4px;
  padding: 0;
  margin: 16px 0;
  background-color: #e7e7e7;
  border: 0 none;
}

.markdown-body ul,
.markdown-body ol {
  padding-left: 2em;
}

.markdown-body ul ul,
.markdown-body ul ol,
.markdown-body ol ol,
.markdown-body ol ul {
  margin-top: 0;
  margin-bottom: 0;
}

.markdown-body li>p {
  margin-top: 16px;
}

.markdown-body dl {
  padding: 0;
}

.markdown-body dl dt {
  padding: 0;
  margin-top: 16px;
  font-size: 1em;
  font-style: italic;
  font-weight: bold;
}

.markdown-body dl dd {
  padding: 0 16px;
  margin-bottom: 16px;
}

.markdown-body blockquote {
  padding: 0 15px;
  color: #777;
  border-left: 4px solid #ddd;
}

.markdown-body blockquote>:first-child {
  margin-top: 0;
}

.markdown-body blockquote>:last-child {
  margin-bottom: 0;
}

.markdown-body table {
  display: block;
  width: 100%;
  overflow: auto;
  word-break: normal;
  word-break: keep-all;
}

.markdown-body table th {
  font-weight: bold;
}

.markdown-body table th,
.markdown-body table td {
  padding: 6px 13px;
  border: 1px solid #ddd;
}

.markdown-body table tr {
  background-color: #fff;
  border-top: 1px solid #ccc;
}

.markdown-body table tr:nth-child(2n) {
  background-color: #f8f8f8;
}

.markdown-body img {
  max-width: 100%;
  -moz-box-sizing: border-box;
  box-sizing: border-box;
}

.markdown-body code,
.markdown-body samp {
  padding: 0;
  padding-top: 0.2em;
  padding-bottom: 0.2em;
  margin: 0;
  font-size: 85%;
  background-color: rgba(0,0,0,0.04);
  border-radius: 3px;
}

.markdown-body code:before,
.markdown-body code:after {
  letter-spacing: -0.2em;
  content: "\00a0";
}

.markdown-body pre>code {
  padding: 0;
  margin: 0;
  font-size: 100%;
  word-break: normal;
  white-space: pre;
  background: transparent;
  border: 0;
}

.markdown-body .codehilite {
  margin-bottom: 16px;
}

.markdown-body .codehilite pre,
.markdown-body pre {
  padding: 16px;
  overflow: auto;
  font-size: 85%;
  line-height: 1.45;
  background-color: #f7f7f7;
  border-radius: 3px;
}

.markdown-body .codehilite pre {
  margin-bottom: 0;
  word-break: normal;
}

.markdown-body pre {
  word-wrap: normal;
}

.markdown-body pre code {
  display: inline;
  max-width: initial;
  padding: 0;
  margin: 0;
  overflow: initial;
  line-height: inherit;
  word-wrap: normal;
  background-color: transparent;
  border: 0;
}

.markdown-body pre code:before,
.markdown-body pre code:after {
  content: normal;
}

/* Admonition */
.markdown-body .admonition {
  -webkit-border-radius: 3px;
  -moz-border-radius: 3px;
  border-radius: 3px;
  padding: 0.5em 1em 0.5em 1em;
  color: #888888;
  background-color: #F7F7F7;
  border: 1px solid #888888;
}

.markdown-body .admonition blockquote {
  color: rgba(136, 136, 136, 0.5);
  border-left: 4px solid rgba(136, 136, 136, 0.5);
}

.markdown-body .admonition table {
  color: #333;
}

.markdown-body .admonition p {
  padding: 0;
  margin: 0;
}

.markdown-body .admonition-title {
  font-weight: bold;
  margin: 0;
}

.markdown-body .admonition-icon {
  font: normal normal 16px fontawesome-mini;
  line-height: 1.5;
  -moz-osx-font-smoothing: grayscale;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  user-select: none;
  float: left;
}

.markdown-body .attention {
  color: #4F8A10;
  background-color: #DFF2BF;
  border: 1px solid #4F8A10;
}

.markdown-body .caution {
  color: #D63301;
  background-color: #FFCCBA;
  border: 1px solid #D63301;
}

.markdown-body .hint {
  color: #00529B;
  background-color: #BDE5F8;
  border: 1px solid #00529B;
}

.markdown-body .danger {
  color: #D8000C;
  background-color: #FFBABA;
  border: 1px solid #D8000C;
}

.markdown-body .question {
  color: #00049b;
  background-color: #BDC8F8;
  border: 1px solid #00049b;
}

.markdown-body .note {
  color: #9F6000;
  background-color: #FEEFB3;
  border: 1px solid #9F6000;
}

.markdown-body .attention {
  color: #4F8A10;
  background-color: #DFF2BF;
  border: 1px solid #4F8A10;
}

.markdown-body .caution blockquote {
  color: rgba(214, 51, 1, .5);
  border-left: 4px solid rgba(214, 51, 1, .5);
}

.markdown-body .hint blockquote {
  color: rgba(0, 82, 155, .5);
  border-left: 4px solid rgba(0, 82, 155, .5);
}

.markdown-body .danger blockquote {
  color: rgba(216, 0, 12, .5);
  border-left: 4px solid rgba(216, 0, 12, .5);
}

.markdown-body .question blockquote {
  color: rgba(0, 4, 155, .5);
  border-left: 4px solid rgba(0, 4, 155, .5);;
}

.markdown-body .note blockquote {
  color: rgba(159, 96, 0, .5);
  border-left: 4px solid rgba(159, 96, 0, .5);
}

.markdown-body .admonition-icon:before {
  content: "\f040\00a0";
}

.markdown-body .attention > .admonition-icon:before {
  content: "\f058\00a0";
}

.markdown-body .caution > .admonition-icon:before {
  content: "\f06a\00a0";
}

.markdown-body .hint > .admonition-icon:before {
  content: "\f05a\00a0";
}

.markdown-body .danger > .admonition-icon:before {
  content: "\f056\00a0";
}

.markdown-body .question > .admonition-icon:before {
  content: "\f059\00a0";
}

.markdown-body .note > .admonition-icon:before {
  content: "\f040\00a0";
}

/* progress bar*/
.markdown-body .progress {
  display: block;
  width: 300px;
  margin: 10px 0;
  height: 24px;
  border: 1px solid #ccc;
  -webkit-border-radius: 3px;
  -moz-border-radius: 3px;
  border-radius: 3px;
  background-color: #F8F8F8;
  position: relative;
  box-shadow: inset -1px 1px 3px rgba(0, 0, 0, .1);
}

.markdown-body .progress-label {
  position: absolute;
  text-align: center;
  font-weight: bold;
  width: 100%; margin: 0;
  line-height: 24px;
  color: #333;
  text-shadow: 1px 1px 0 #fefefe, -1px -1px 0 #fefefe, -1px 1px 0 #fefefe, 1px -1px 0 #fefefe, 0 1px 0 #fefefe, 0 -1px 0 #fefefe, 1px 0 0 #fefefe, -1px 0 0 #fefefe, 1px 1px 2px #000;
  -webkit-font-smoothing: antialiased !important;
  white-space: nowrap;
  overflow: hidden;
}

.markdown-body .progress-bar {
  height: 24px;
  float: left;
  border-right: 1px solid #ccc;
  -webkit-border-radius: 3px;
  -moz-border-radius: 3px;
  border-radius: 3px;
  background-color: #34c2e3;
  box-shadow: inset 0 1px 0 rgba(255, 255, 255, .5);
  background-size: 30px 30px;
  background-image: -webkit-linear-gradient(
    135deg, rgba(255, 255, 255, .4) 27%,
    transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%,
    transparent 77%, transparent
  );
  background-image: -moz-linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
  background-image: -ms-linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
  background-image: -o-linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
  background-image: linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
}

.markdown-body .progress-100plus .progress-bar {
  background-color: #1ee038;
}

.markdown-body .progress-80plus .progress-bar {
  background-color: #86e01e;
}

.markdown-body .progress-60plus .progress-bar {
  background-color: #f2d31b;
}

.markdown-body .progress-40plus .progress-bar {
  background-color: #f2b01e;
}

.markdown-body .progress-20plus .progress-bar {
  background-color: #f27011;
}

.markdown-body .progress-0plus .progress-bar {
  background-color: #f63a0f;
}

.markdown-body .gloss .progress-bar {
  box-shadow:
    inset -1px 1px 0 rgba(255, 255, 255, .5),
    inset 0 -4px 12px rgba(255, 255, 255, .7),
    inset 0 4px 12px rgba(255, 255, 255, .7),
    inset 0 -12px 0 rgba(0, 0, 0, .05),
    inset 1px -1px 0 rgba(255, 255, 255, .2);
}

.markdown-body .candystripe-animate .progress-bar{
  -webkit-animation: animate-stripes 3s linear infinite;
  -moz-animation: animate-stripes 3s linear infinite;
  animation: animate-stripes 3s linear infinite;
}

@-webkit-keyframes animate-stripes {
  0% {
    background-position: 0 0;
  }

  100% {
    background-position: 60px 0;
  }
}

@-moz-keyframes animate-stripes {
  0% {
    background-position: 0 0;
  }

  100% {
    background-position: 60px 0;
  }
}

@keyframes animate-stripes {
  0% {
    background-position: 0 0;
  }

  100% {
    background-position: 60px 0;
  }
}

/* Multimarkdown Critic Blocks */
.markdown-body .critic_mark {
  background: #ff0;
}

.markdown-body .critic_delete {
  color: #c82829;
  text-decoration: line-through;
}

.markdown-body .critic_insert {
  color: #718c00 ;
  text-decoration: underline;
}

.markdown-body .critic_comment {
  color: #8e908c;
  font-style: italic;
}

.markdown-body .headeranchor {
  font: normal normal 16px octicons-anchor;
  line-height: 1;
  display: inline-block;
  text-decoration: none;
  -webkit-font-smoothing: antialiased;
  -moz-osx-font-smoothing: grayscale;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  user-select: none;
}

.headeranchor:before {
  content: '\f05c';
}

.markdown-body .task-list-item {
  list-style-type: none;
}

.markdown-body .task-list-item+.task-list-item {
  margin-top: 3px;
}

.markdown-body .task-list-item input {
  margin: 0 4px 0.25em -20px;
  vertical-align: middle;
}

/* Media */
@media only screen and (min-width: 480px) {
  .markdown-body {
    font-size:14px;
  }
}

@media only screen and (min-width: 768px) {
  .markdown-body {
    font-size:16px;
  }
}

@media print {
  .markdown-body * {
    background: transparent !important;
    color: black !important;
    filter:none !important;
    -ms-filter: none !important;
  }

  .markdown-body {
    font-size:12pt;
    max-width:100%;
    outline:none;
    border: 0;
  }

  .markdown-body a,
  .markdown-body a:visited {
    text-decoration: underline;
  }

  .markdown-body .headeranchor-link {
    display: none;
  }

  .markdown-body a[href]:after {
    content: " (" attr(href) ")";
  }

  .markdown-body abbr[title]:after {
    content: " (" attr(title) ")";
  }

  .markdown-body .ir a:after,
  .markdown-body a[href^="javascript:"]:after,
  .markdown-body a[href^="#"]:after {
    content: "";
  }

  .markdown-body pre {
    white-space: pre;
    white-space: pre-wrap;
    word-wrap: break-word;
  }

  .markdown-body pre,
  .markdown-body blockquote {
    border: 1px solid #999;
    padding-right: 1em;
    page-break-inside: avoid;
  }

  .markdown-body .progress,
  .markdown-body .progress-bar {
    box-shadow: none;
  }

  .markdown-body tr,
  .markdown-body img {
    page-break-inside: avoid;
  }

  .markdown-body img {
    max-width: 100% !important;
  }

  .markdown-body p,
  .markdown-body h2,
  .markdown-body h3 {
    orphans: 3;
    widows: 3;
  }

  .markdown-body h2,
  .markdown-body h3 {
    page-break-after: avoid;
  }
}
</style><title>CMPSC380 Day 11</title></head><body><article class="markdown-body"><h1 id="september-25th-2014"><a name="user-content-september-25th-2014" href="#september-25th-2014" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>September 25th, 2014</h1>
<h4 id="josql-review"><a name="user-content-josql-review" href="#josql-review" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>JoSQL Review</h4>
<ul>
<li>Declarative is slower than imperative</li>
<li>Declarative is really slow for linked list</li>
<li>Why?<ul>
<li>AL &amp; LL reside in the heap</li>
<li>JoSQL has to access them through Reflection</li>
<li>Can interrogate AL through one reflect operation</li>
<li>But LL requires reflect operation for each node</li>
</ul>
</li>
<li>So why use declarative?<ul>
<li>This research doesn&rsquo;t show that <em>all declarative programming</em> is bad</li>
<li>It shows that <em>JoSQL</em> specifically is slow <em>in the java environment</em><ul>
<li>You&rsquo;re not doing reflection on a table</li>
</ul>
</li>
<li>Ease of use -&gt; programmer productivity</li>
</ul>
</li>
</ul>
<h4 id="null-value-handling"><a name="user-content-null-value-handling" href="#null-value-handling" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>Null Value Handling</h4>
<ul>
<li>Different DBMSs have different rules (SQL standard is ambiguous)</li>
</ul>
<h4 id="aggregation"><a name="user-content-aggregation" href="#aggregation" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>Aggregation</h4>
<ul>
<li>Aggregation: <ul>
<li>Use functions like <code>MIN</code>, <code>MAX</code>, <code>AVG</code>, <code>SUM</code>, and <code>COUNT</code></li>
<li>Used in conjunction with <code>GROUP BY</code> clause</li>
<li>Example:<ul>
<li>Sensor networks (Mica Mote)<ul>
<li>Wireless sensor network</li>
<li>Used in biosciences, esp. conservation biology</li>
</ul>
</li>
<li>Aggregation is necessary:<ul>
<li>Not enough storage on device to store much data</li>
<li>Power is scarce, wireless transmission takes lots of power</li>
<li>Can&rsquo;t phone home constantly - you&rsquo;re logging data very frequently but if you transmit every datum you log, it drains power very fast</li>
</ul>
</li>
<li>TinyDB: a database for sensor networks</li>
</ul>
</li>
<li>Kinds of aggregation:<ul>
<li><code>COUNT</code> returns number of matches instead of matches</li>
<li><code>GROUP BY</code> sorts the values</li>
<li><code>HAVING &lt;predicate&gt;</code> clause: like <code>WHERE</code> but for aggregate functions</li>
</ul>
</li>
</ul>
</li>
</ul>
<h4 id="transaction-handling"><a name="user-content-transaction-handling" href="#transaction-handling" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>Transaction Handling</h4>
<ul>
<li>Demarcated by a <code>BEGIN</code> and an <code>END</code></li>
<li>Result in either <code>COMMIT</code> or <code>ROLLBACK</code><ul>
<li><code>COMMIT</code> -&gt; you have successfully applied the transaction</li>
<li><code>ROLLBACK</code>-&gt; revert to previous state<ul>
<li>Why?<ul>
<li>Transactions violate some integrity constraint</li>
<li>Other failures cause abort:<ul>
<li>Hardware failure (disk crash)</li>
<li>Resources exhausted<ul>
<li>RAM</li>
<li>disk space</li>
</ul>
</li>
<li>Server offline</li>
</ul>
</li>
</ul>
</li>
<li>To where do we revert?<ul>
<li>Some point before transaction execution</li>
<li>Ideally, right before transaction</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>Integrity constraits during transaction processing?<ul>
<li>Option 1: Abort transaction immediately</li>
<li>Option 2: Ignore violation, relax constraints and continue</li>
</ul>
</li>
</ul>
<pre><code>BEGIN
T1
...
Tn
END
</code></pre></article></body></html>

## CMPSC380 Day 11.md

      
    Raw
  

              CMPSC380 Day 11.md
            
          
    CMPSC380: DATABASES
September 25th, 2014

JoSQL Review


Declarative is slower than imperative
Declarative is really slow for linked list
Why?

AL & LL reside in the heap
JoSQL has to access them through Reflection
Can interrogate AL through one reflect operation
But LL requires reflect operation for each node


So why use declarative?

This research doesn't show that all declarative programming is bad
It shows that JoSQL specifically is slow in the java environment

You're not doing reflection on a table


Ease of use -> programmer productivity


Null Value Handling


Different DBMSs have different rules (SQL standard is ambiguous)

Aggregation


Aggregation:

Use functions like MIN, MAX, AVG, SUM, and COUNT
Used in conjunction with GROUP BY clause
Example:

Sensor networks (Mica Mote)

Wireless sensor network
Used in biosciences, esp. conservation biology


Aggregation is necessary:

Not enough storage on device to store much data
Power is scarce, wireless transmission takes lots of power
Can't phone home constantly - you're logging data very frequently but if you transmit every datum you log, it drains power very fast


TinyDB: a database for sensor networks


Kinds of aggregation:

COUNT returns number of matches instead of matches
GROUP BY sorts the values
HAVING <predicate> clause: like WHERE but for aggregate functions


Transaction Handling


Demarcated by a BEGIN and an END
Result in either COMMIT or ROLLBACK

COMMIT -> you have successfully applied the transaction
ROLLBACK-> revert to previous state

Why?

Transactions violate some integrity constraint
Other failures cause abort:

Hardware failure (disk crash)
Resources exhausted

RAM
disk space


Server offline


To where do we revert?

Some point before transaction execution
Ideally, right before transaction


Integrity constraits during transaction processing?

Option 1: Abort transaction immediately
Option 2: Ignore violation, relax constraints and continue


BEGIN
T1
...    
Tn
END


## CMPSC380 Day 12.md

      
    Raw
  

              CMPSC380 Day 12.md
            
          
    CMPSC380: DATABASES
October 7th, 2014

Relational Join


Relational Join

Connecting two tables together
`Ti [ A(data type) ] <- Join -> Tj [ A(data type) ]``

entries
meaning


Why?

combine data collected at different places or times


You can have any number of inputs to a relational join

Input should be < output


Symbol: ⋈ (as Kapfhammer calls it, "the bowtie")
Example:

figs 4.1, 4.2 and 4.3 in textbook
What's happening?

The two tables are being joined along the "ID" column, associating each row in students with each corresponding row in takes with the corresponding ID.


View level


Levels

Physical

Low abstraction
Disk


Logical

Medium abstraction
Schema


View

High abstraction
portion of instance
User level


View level

Purpose: to manage:

Complexity
Security


Creation:

CREATE VIEW statement
Takes in a SELECT statement that's used to generate the view
S(SELECT statement ) -> [ CREATE VIEW ] -> V(view) <- u,i,d
Two forms of view:

Temporary: stored in memory only, discarded when DBMS is shut down
Materialized: saves state of view to disk


Handling updates, inserts, and deletes

View maintainance: you have to re-run the SELECT statement every time the database is updated
When you update a view, you need to go back to the source table and apply the same updates back to it.

You probably shouldn't support view modification because propagating changes backwards is Hard.
However, there is a way to do it, given the following:

FROM clause only contains one relation
No extra features such as aggregates
Nullables allowed


## CMPSC380 Day 13.md

      
    Raw
  

              CMPSC380 Day 13.md
            
          
    CMPSC380: DATABASES
October 9th, 2014


Materialized views:

Why materialize a view?

Performance - when it will be frequently used and fast response times are necessary
Frequent use is likely


Tradeoffs:

Lazy-load changed elements only when needed

Improves general performance
Decreases speed of view recquests


SQL statements and how they're handled

UPDATE, DELETE, and MODIFY
-DELETE
+ General example:
* DELETE FROM <table> WHERE <predicate>
+ Specific example:
* DELETE FROM Customers WHERE closed=true

UPDATE

General form

UPDATE <table name> SET <attribute>=<value>,<attribute>=<value>... WHERE <predicate>


Specific example:

UPDATE Instructors SET enthusiasm = 110 WHERE professor="Kapfhammer"


INSERT

General form:

INSERT INTO <table> VALUES(<values>)


Specific example:


Scope:

UPDATE, DELETE, and MODIFY act on WHOLE ROWS in a table


HSQLDB (HyperSQL DataBase)

100% Java DB
Why be 100% Java?

Performance: Everything is in memory
Performance: Runs anywhere there's a JVM
Compatibility:

Check constraints can call Java methods
Issues with this:

Method may not exist
Error handling

How does DBMS handle exceptions thrown by Java?


Higher potential for defects

Java is more expressive than SQL


Testing challenges

Now you're not just testing SQL, but you're testing SQL + ALL OF JAVA


You have to know Java to use it


Tradeoff: between expressiveness and reliability


Assertion checking:

Express a condition that we want the DB to satisfy

Create an assertion
Is it initially valid?
Check the assertion at every state manipulation


Trade-off: expressivity v checking cost


## CMPSC380 Day 14.md

      
    Raw
  

              CMPSC380 Day 14.md
            
          
    CMPSC380: DATABASES
October 16th, 2014


Referential Integrity

A FOREIGN KEY between Instructor and Department relations
When could a DELETE lead to a violation (of referential integrity)?

What if we DELETE an entry in Department?
Now all of the Instructors in that Department are invalid!
How do we handle this?

Cascade!

ON DELETE CASCADE
ON UPDATE CASCADE


Enforcement Options

Check constraints immediately
Constraints initially deferred
Constraints are deferrable

Hybrid of 1 & 2


Large objects:

CLOB: Character Large Object

Large text document


BLOB: Binary Large Object

e.g.:

image
executable binary
sound
etc


## CMPSC380 Day 15.md

      
    Raw
  

              CMPSC380 Day 15.md
            
          
    CMPSC380: DATABASES
October 21st, 2014

Advanced SQL Concepts

Not all queries can be expressed in SQL


SQL is not Turing complete
Two reasons:
Result set may contain more than needed

postprocess in another programming language


Non-Delcarative operations:

print a report
display results in GUI
user interactions
network transmitions


Embedded & Dynamic SQL


Dynamic

changing
built at runtime
Strings
driver (JDBC in Java)


Embedded

full SQL in the code
preprocessor (SQL4J)


Interpreting results

Challenge: Data type mismatch

java: int, double, float, char, Java ADTs
SQL: varchar, BLOB, CLOB, date, time, timestamp
Translating between these datatypes is sometimes ambiguous


Connecting to the DBMS


JDBC or ODBC
What is needed?

Connect to database

running DBMS
driver
address


Ship SQL to DBMS
Retrieve query results


Prepared statement

template
why?

reuse
robustness
reliability
security (avoid SQL injection)


Types of result:

ResultSet: iterate ONCE
RowSet:

iterate multiple times
transfer over the network


Metadata


Metadata is data about data
Different kinds

Schema
ResultSet metadata

Database columns

constraints
types
name


Database metadata

Tables

Names
Constraints


## CMPSC380 Day 2.md

      
    Raw
  

              CMPSC380 Day 2.md
            
          
    CMPSC380: DATABASES
August 28th, 2014

Definitions from last class:

Database: A structured or organized collection of data
DBMS (database management system): mechanism that allows you to access a database

Select
Update
Insert
Delete
Create
Destroy


FPS (file processing system):

R language for statistical computation

CSV files (comma-separated values)
CSVs are easy to set up
Scientists/statisticians are not necessarily computer scientists

Evaluation metrics:

   Performance
    /   |   \
time  space  throughput


Cost
|-> purchasing
|-> installing
|-> maintaining
\-> hardware required to run systems

Disadvantages of FPS:


Data redundancy & inconsistency
Access difficulty
Many locations & formats
Data integrity

Can't add constraints


Atomicity concerns
Concurrent access
Security

Focus & Choice

Both FPS & DBMS have a place.
+ Database takes a lot of work to set up, but should be used WHEN it's worth it.
+ This semester, we're gonna implement & evaluate both kinds of system
+ We'll learn how to pick the best choice for a specific purpose

Let's first investigate the relational database!
Relational database management systems

Levels of data management:


View level - describes a portion of the data
Logical level - stores data, relationships, constraints
Physical level - actual bits on disk

Logical level
Table T             Table T'
-------+      1...*     -------+ 
----+--| ----------->   ----+--|
     | |                     | |

View level is a restriction of the logical level.
For example...

Any query result is a view - it's a restricted subset of the data
Security & access roles might represent a view


Schema

Describes the structure of the database
This is like a class in OOP


Instance

Actual state/values
This is like an object (instance of a class) in OOP


Metadata (data about data) = schema
Database Models


Structured (if you want the data to go into the DB, it has to conform to the structure):

Relational model (tables with columns & rows)
Object model (this is OOP)


Semi-structured (there is a structure but it can change):

XML (extensible markup language)

HTML - fixed tags
XML - user-defined tags


Database Languages


SQL (_S_tructured _Q_uery _L_anguage)

SQL = DDL + DML
Used for instance AND schema


DDL (_D_ata _D_efinition _L_anguage)

used for defining a schema
E.g.: CREATE TABLE SQL statement


DML (_D_ata _M_anipulation _L_anguage)

used for modifying & interacting with an instance
E.g.: SELECT, UPDATE SQL statements


Database Applications

Program and a host language
Program P (Java)            
+==================+
|| Method M         ||
|| +------------+   ||
|| | statements |   ||
|| +------------+   ||
+==================+

Challenges:

null ResultSet
Unpacking ResultSet requires iteration
Bad query strings:

incorrect syntax
null string
malicious queries
Java compiler can't detect bad query strings


## CMPSC380 Day 3.md

      
    Raw
  

              CMPSC380 Day 3.md
            
          
    CMPSC380: DATABASES
September 2nd, 2014

Some review:


Levels of data management:

                |           ^ Increased:
    Decreased:  | View      |  + Level of Abstraction
     + Distance | Logical   |  + Security
    to hardware | Physical  |
     + Level of V           |
    granularity 


Schema vs Instance

schema: structure of database
instance: state of database


Database models:

relational: tables & connections between tables
object-oriented: just a bunch of serialized objects
semi-structured: flexible

XML

tags (describe structure)
data (between tags)


pro: increased flexibility
con: less guarantees for integrity


Database languages:

SQL = DDL + DML
DDL:

defines schema
e.g. CREATE TABLE


DML:

access & update data
e.g. UPDATE, SELECT, INSERT, DELETE


Imperative vs declarative:

Imperative (procedural): commands

"A list of instructions given to a computer to achieve some objective"
e.g. Java, C, Perl, Python...
"what" and "how"


Declarative:

"State the desired output or goal"
e.g. SQL, JoSQL, Prolog
"what" without the "how"
advantages:

ease of use
query processor can apply optimizations


SQL is not Turing complete (NOT a general-purpose language)


              +-----------+
Query Q ----> | SQL Query | ----> Result Set R
              | Processor |
              +-----------+

Database Applications


Entity-Relationship Models

a.k.a "ER models", "ER diagrams"
Entities = tables
Relationships = keys (lines connecting tables)


A tradeoff: Redundancy

"Three S's":

speed
safety
space


Redundancy increases space overhead, but makes the system faster
Normalization is the process of removing redundancy (decreases space, decreases speed, decreases safety)


null values:

in Java, null means that the pointer points to an empty memory location
in non-computer-science uses, null = no meaning/no value
in a database, null value = "not known"


Persistence:

when JVM is shut down, everything in the heap & the stack is removed from memory
how do you store that stuff between runs?

ORM

for example, Hibernate ("idiomatic persistence for Java and relational databases)
Java object --> [ ORM ] ---> Relational form of object --> [ RDBMS ]
When you put an object into the ORM, it is "flattened" and can be stored in the DB


alternative: serialization

XStream: turns Java objects to XML
JavaScript Object Notation (JSON)


JoSQL:

Iteration: imperative
JoSQL: declaratively find java objects


Database Architecture


Some DBMS components:

Query processor:

input: declarative query
output: answer
optimization


Storage manager:

store state of DB and schema on disk


Tiers:

Two-tiered architecture

Application talks directly to database


Three-tiered architecture

Client application communicates with server application which communicates with database


"Keep the computation close to the data"


## CMPSC380 Day 4.md

      
    Raw
  

              CMPSC380 Day 4.md
            
          
    CMPSC380: DATABASES
September 4th, 2014


Two-tier vs three-tier architecture:

Two-tier: [program] --network--> [database]
Three-tier: [client] --network--> [server] --local or network--> [database]
Tradeoffs?

Two-tier might be easier to set uo
Security: Three-tier is more secure/better access control
Performance:

Three-tier: Higher message overhead (more layers of indirection)
Two-tier: Client has to do more more work (three-tier supports 'thin client')


"Keep the computation close to the data"

validate input client-side


Chapter 2: Relational DBs


Relational DB = collection of tables T_1 ... T_n

Attributes

data types:

numerical
categorical
textual


Unique identifiers
Varying number of rows & columns


Why use a relational DB?

ease of growth
model real-world relationships
ease of use
marketplace dominance:

many options
lots of support


Terms:

relation or table
row, tuple, or record
attribute or column


Can the values of an attribute uniquely identify a tuple?

Cost of comparison

related to: data type
prefer smaller data types


Keys - defined by database admin for specific purpose

Superkey: a set of attributes in an individual tuple that allow you to identify the relation

e.g. "Name" attribute of instructor

generally speaking, this is a bad superkey (human naming is not collision-free)


e.g. "ID" attribute of instructor

yes, this is a good superkey (assuming IDs are assigned without collisions)


multiple attributes are sometimes necessary


## CMPSC380 Day 5.md

      
    Raw
  

              CMPSC380 Day 5.md
            
          
    CMPSC380: DATABASES
September 9th, 2014


Review:

How can we distinguish one tuple from another?

Key: if you know the value of the key, you can go directly to a row in the database
Superkey: those individual values that allow you to uniquely identify any rows in the database

Avoid extraneous attributes (performance)


Candidate Key: a key which might be a minimal superkey

minimal: number of attributes
data types:

types we want to compare:

int
boolean/logical
smallint
char


types we don't want to have to compare:

string/varchar
float
double
BLOB <- the worst


primary key: chosen candidate key that is the minimal superkey

stated/declared when schema is created
should be rarely (if ever) modified

chance of introducing collisions
performance implications (no free lunch)

costs:

define
maintain
update
check


Using sqlite3:

commands prefixed with .are unique to sqlite (not part of SQL standard)

e.g. .tables, .schema


Violation of constraints for primary key:

Different data type
NULL value
INSERT a redundant value


DML Statements:

SELECT
UPDATE
INSERT
DELETE


## CMPSC380 Day 6.md

      
    Raw
  

              CMPSC380 Day 6.md
            
          
    CMPSC380: DATABASES
September 11th, 2014

Bonchmorking

+----------------------------------------------------+
|   +-----------------------+     +------------+     |
|   | [] -> [] Linked List  |     |            |     |
|   | [      ] Array List   |     |            |     |
|   | {} {} {} Objects      |     |            |     |
|   +-----------------------+     +------------+     |
|     Heap                            Stack          |
+----------------------------------------------------+

How to find matching objects?

Iteration construct   [I]

for
while
do/while


Conditional logic     [I]
Sorting algerizms     [I]
JoSQL                 [D]

uses Reflection (SLOW)


Research questions:


RQ1: As the size of the arraylist increases, what happens to response time?


RQ2: Which storage type (ArrayList, LinkedList, Vector) gives the best performance?


Technique:

JoSQL
Control


Evaluation metrics:

Time overhead


Questions:

What have you learned about relational databases?


I learned the syntax for specifying a schema in SQL.
I learned that commands prefixed by the . character are internal to SQLite and are not part of the SQL standard.
I was already familiar with basic SQL queries from past Computer Science courses.
I learned that SQLite treats numbers ending with .0 as ints rather than floats

Did you have any questions about using sqlite3?

How do I enforce persistence in SQLite?
Foreign Keys

    foreign key        primary key
+------------+          +------------+
|        | A |--------->| A |        |
|        |---| refers   |---|        |
|        |   | to       |   |        |
+------------+          +------------+
 Table r_i                 Table r_j

"Key A in table r_i refers to table r_j".
What is the purpose of this db's foreign key?

SQL triggers fire when some event takes place

  
## CMPSC380 Day 7.md

      
    Raw
  

              CMPSC380 Day 7.md
            
          
    CMPSC380: DATABASES
September 16th, 2014

Review


Keys:

Types:

superkey
candidate key
primary key


Why?

access data quickly
uniquely identify rows


Commands:

SQLite specific:

.schema
.tables


SQL:

INSERT

Schema rejects; violate integrity constraint


Foreign Keys


"R_i references R_j"

Any entry in R_i must also appear in R_j


Two types:

Compound: many attributes
Simple: one attribute


Why use foreign keys?

Security
Capture & model relationships
Improve performance
Decrease use of storage
Improve data integrity

Avoids orphans

"If something is deleted here, it should also be deleted here"


## CMPSC380 Day 9.html
<!DOCTYPE html><html><head><meta charset="utf-8"><meta name="cmpsc380" content="DATABASES"><style>body {
  width: 45em;
  border: 1px solid #ddd;
  outline: 1300px solid #fff;
  margin: 16px auto;
}

body .markdown-body
{
  padding: 30px;
}

@font-face {
  font-family: fontawesome-mini;
  src: url(data:font/woff;charset=utf-8;base64,d09GRgABAAAAAAzUABAAAAAAFNgAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAABGRlRNAAABbAAAABwAAAAcZMzaOEdERUYAAAGIAAAAHQAAACAAOQAET1MvMgAAAagAAAA+AAAAYHqhde9jbWFwAAAB6AAAAFIAAAFa4azkLWN2dCAAAAI8AAAAKAAAACgFgwioZnBnbQAAAmQAAAGxAAACZVO0L6dnYXNwAAAEGAAAAAgAAAAIAAAAEGdseWYAAAQgAAAFDgAACMz7eroHaGVhZAAACTAAAAAwAAAANgWEOEloaGVhAAAJYAAAAB0AAAAkDGEGa2htdHgAAAmAAAAAEwAAADBEgAAQbG9jYQAACZQAAAAaAAAAGgsICJBtYXhwAAAJsAAAACAAAAAgASgBD25hbWUAAAnQAAACZwAABOD4no+3cG9zdAAADDgAAABsAAAAmF+yXM9wcmVwAAAMpAAAAC4AAAAusPIrFAAAAAEAAAAAyYlvMQAAAADLVHQgAAAAAM/u9uZ4nGNgZGBg4ANiCQYQYGJgBEJuIGYB8xgABMMAPgAAAHicY2Bm42OcwMDKwMLSw2LMwMDQBqGZihmiwHycoKCyqJjB4YPDh4NsDP+BfNb3DIuAFCOSEgUGRgAKDgt4AAB4nGNgYGBmgGAZBkYGEAgB8hjBfBYGCyDNxcDBwMTA9MHhQ9SHrA8H//9nYACyQyFs/sP86/kX8HtB9UIBIxsDXICRCUgwMaACRoZhDwA3fxKSAAAAAAHyAHABJQB/AIEAdAFGAOsBIwC/ALgAxACGAGYAugBNACcA/wCIeJxdUbtOW0EQ3Q0PA4HE2CA52hSzmZDGe6EFCcTVjWJkO4XlCGk3cpGLcQEfQIFEDdqvGaChpEibBiEXSHxCPiESM2uIojQ7O7NzzpkzS8qRqnfpa89T5ySQwt0GzTb9Tki1swD3pOvrjYy0gwdabGb0ynX7/gsGm9GUO2oA5T1vKQ8ZTTuBWrSn/tH8Cob7/B/zOxi0NNP01DoJ6SEE5ptxS4PvGc26yw/6gtXhYjAwpJim4i4/plL+tzTnasuwtZHRvIMzEfnJNEBTa20Emv7UIdXzcRRLkMumsTaYmLL+JBPBhcl0VVO1zPjawV2ys+hggyrNgQfYw1Z5DB4ODyYU0rckyiwNEfZiq8QIEZMcCjnl3Mn+pED5SBLGvElKO+OGtQbGkdfAoDZPs/88m01tbx3C+FkcwXe/GUs6+MiG2hgRYjtiKYAJREJGVfmGGs+9LAbkUvvPQJSA5fGPf50ItO7YRDyXtXUOMVYIen7b3PLLirtWuc6LQndvqmqo0inN+17OvscDnh4Lw0FjwZvP+/5Kgfo8LK40aA4EQ3o3ev+iteqIq7wXPrIn07+xWgAAAAABAAH//wAPeJyFlctvG1UUh+/12DPN1B7P3JnYjj2Ox4/MuDHxJH5N3UdaEUQLqBIkfQQioJWQ6AMEQkIqsPGCPwA1otuWSmTBhjtps2ADWbJg3EpIXbGouqSbCraJw7kzNo2dRN1cnXN1ZvT7zuuiMEI7ncizyA0URofRBJpCdbQuIFShYY+GZRrxMDVtih5TwQPHtXDFFSIKoWIbuREBjLH27Ny4MsbVx+uOJThavebgVrNRLAiYx06rXsvhxLgWx9xpfHdrs/ekc2Pl2cpPCVEITQpwbj8VQhfXSq2m+Wxqaq2D73Kne5e3NjHqQNj3CRYlJlgUl/jRNP+2Gs2pNYRQiOnmUaQDqm30KqKiTTWPWjboxnTWpvgxjXo0KrtZXAHt7hwIz0YVcj88JnKlJKi3NPAwLyDwZudSmJSMMJFDYaOkaol6XtESx3Gt1VTytdZJ3DCLeaVhVnCBH1fycHTxFXwPX+l2e3d6H/TufGGmMTLTnbSJUdo00zuBswMO/nl3YLeL/wnu9/limCuD3vC54h5NBVz6Li414AI8Vx3iiosKcQXUbrvhFFiYb++HN4DaF4XzFW0fIN4XDWJ3a3XQoq9V8WiyRmdsatV9xUcHims1JloH0YUa090G3Tro3mC6c01f+YwCPquINr1PTaCP6rVTOOmf0GE2dBc7zWIhji3/5MchSuBHgDbU99RMWt3YUNMZMJmx92YP6NsHx/5/M1yvInpnkIOM3Z8fA3JQ2lW1RFC1KaBPDFXNAHYYvGy73aYZZZ3HifbeuiVZCpwA3oQBs0wGPYJbJfg60xrKEbKiNtTe1adwrpBRwlAuQ3q3VRaX0QmQ9a49BTSCuF1MLfQ6+tinOubRBZuWPNoMevGMT+V41KitO1is3D/tpMcq1JHZqDHGs8DoYGDkxJgKjHROeTCmhZvzPm9pod+ltKm4PN7Dyvvldlpsg8D+4AUJZ3F/JBstZz7cbFRxsaAGV6yX/dkcycWf8eS3QlQea+YLjdm3yrOnrhFpUyKVvFE4lpv4bO3Svx/6F/4xmiDu/RT5iI++lko18mY1oX+5UGKR6kmVjM/Zb76yfHtxy+h/SyQ0lLdpdKy/lWB6szatetQJ8nZ80A2Qt6ift6gJeavU3BO4gtxs/KCtNPVibCtYCWY3SIlSBPKXZALXiIR9oZeJ1AuMyxLpHIy/yO7vSiSE+kZvk0ihJ30HgHfzZtEMmvV58x6dtqns0XTAW7Vdm4HJ04OCp/crOO7rd9SGxQAE/mVA9xRN+kVSMRFF6S9JFGUtthkjBA5tFCWc2l4V43Ex9GmUP3SI37Jjmir9KqlaDJ4S4JB3vuM/jzyH1+8MuoZ+QGzfnvPoJb96cZlWjMcKLfgDwB7E634JTY+asjsPzS5CiVnEWY+KsrsIN5rn3mAPjqmQBxGjcGKB9f9ZxY3mYC2L85CJ2FXIxKKyHk+dg0FHbuEc7D5NzWUX32WxFcWNGRAbvwSx0RmIXVDuYySafluQBmzA/ssqJAMLnli+WIC90Gw4lm85wcp0qjArEDPJJV/sSx4P9ungTpgMw5gVC1XO4uULq0s3v1rqLi0vX/z65vlH50f8T/RHmSPTk5xxWBWOluMT6WiOy+tdvWxlV/XQb3o3c6Ssr+r6I708GsX9/nzp1tKFh0s3v7m4vAy/Hnb/KMOvc1wump6Il48K6mGDy02X9Yd65pa+nQIjk76lWxCkG8NBCP0HQS9IpAAAeJxjYGRgYGBhcCrq214Qz2/zlUGenQEEzr/77oug/zewFbB+AHI5GJhAogBwKQ0qeJxjYGRgYH3/P46BgZ0BBNgKGBgZUAEPAE/7At0AAAB4nGNngAB2IGYjhBsYBAAIYADVAAAAAAAAAAAAAFwAyAEeAaACCgKmAx4DggRmAAAAAQAAAAwAagAEAAAAAAACAAEAAgAWAAABAAChAAAAAHiclZI7bxQxFIWPd/JkUYQChEhIyAVKgdBMskm1QkKrRETpQiLRUczueB/K7HhlOxttg8LvoKPgP9DxFxANDR0tHRWi4NjrPIBEgh1p/dm+vufcawNYFWsQmP6e4jSyQB2fI9cwj++RE9wTjyPP4LYoI89iWbyLPIe6+Bh5Hs9rryMv4GbtW+RF3EhuRa7jbrIbeQkPkjdUETOLnL0Kip4FVvAhco1RXyMnSPEz8gzWxE7kWTwUp5HnsCLeR57HW/El8gJWa58iL+JO7UfkOh4l9yMv4UnyEtvQGGECgwF66MNBooF1bGCL1ELB/TYU+ZBRlvsKQ44Se6jQ4a7hef+fh72Crv25kp+8lNWGmeKoOI5jJLb1aGIGvb6TjfWNLdkqdFvJw4l1amjlXtXRZqRN7lSRylZZyhBqpVFWmTEXgWfUrpi/hZOQXdOd4rKuXOtEWT3k5IArPRzTUU5tHKjecZkTpnVbNOnt6jzN8240GD4xtikvZW56043rPMg/dS+dlOceXoR+WPbJ55Dsekq1lJpnypsMUsYOdCW30o103Ytu/lvh+5RWFLfBjm9/N8hJntPhvx92rnoE/kyHdGasGy754kw36vsVf/lFeBi+0COu+cfgQr42G3CRpeLoZ53gmfe3X6rcKt5oVxnptHR9JS8ehVUd5wvvahN2uqxOOpMXapibI5k7Zwbt4xBSaTfoKBufhAnO/uqNcfK8OTs0OQ6l7JIqFjDhYj5WcjevCnI/1DDiI8j4ndWb/5YzDZWh79yomWXeXj7Nnw70/2TIeFPTrlSh89k1ObOSRVZWZfgF0r/zJQB4nG2JUQuCQBCEd07TTg36fb2IyBaLd3vWaUh/vmSJnvpgmG8YcmS8X3Shf3R7QA4OBUocUKHGER5NNbOOEvwc1txnuWkTRb/aPjimJ5vXabI+3VfOiyS15UWvyezM2xiGOPyuMohOH8O8JiO4Af+FsAGNAEuwCFBYsQEBjlmxRgYrWCGwEFlLsBRSWCGwgFkdsAYrXFhZsBQrAAA=) format('woff');
}

@font-face {
  font-family: octicons-anchor;
  src: url(data:font/woff;charset=utf-8;base64,d09GRgABAAAAAAYcAA0AAAAACjQAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAABGRlRNAAABMAAAABwAAAAca8vGTk9TLzIAAAFMAAAARAAAAFZG1VHVY21hcAAAAZAAAAA+AAABQgAP9AdjdnQgAAAB0AAAAAQAAAAEACICiGdhc3AAAAHUAAAACAAAAAj//wADZ2x5ZgAAAdwAAADRAAABEKyikaNoZWFkAAACsAAAAC0AAAA2AtXoA2hoZWEAAALgAAAAHAAAACQHngNFaG10eAAAAvwAAAAQAAAAEAwAACJsb2NhAAADDAAAAAoAAAAKALIAVG1heHAAAAMYAAAAHwAAACABEAB2bmFtZQAAAzgAAALBAAAFu3I9x/Nwb3N0AAAF/AAAAB0AAAAvaoFvbwAAAAEAAAAAzBdyYwAAAADP2IQvAAAAAM/bz7t4nGNgZGFgnMDAysDB1Ml0hoGBoR9CM75mMGLkYGBgYmBlZsAKAtJcUxgcPsR8iGF2+O/AEMPsznAYKMwIkgMA5REMOXicY2BgYGaAYBkGRgYQsAHyGMF8FgYFIM0ChED+h5j//yEk/3KoSgZGNgYYk4GRCUgwMaACRoZhDwCs7QgGAAAAIgKIAAAAAf//AAJ4nHWMMQrCQBBF/0zWrCCIKUQsTDCL2EXMohYGSSmorScInsRGL2DOYJe0Ntp7BK+gJ1BxF1stZvjz/v8DRghQzEc4kIgKwiAppcA9LtzKLSkdNhKFY3HF4lK69ExKslx7Xa+vPRVS43G98vG1DnkDMIBUgFN0MDXflU8tbaZOUkXUH0+U27RoRpOIyCKjbMCVejwypzJJG4jIwb43rfl6wbwanocrJm9XFYfskuVC5K/TPyczNU7b84CXcbxks1Un6H6tLH9vf2LRnn8Ax7A5WQAAAHicY2BkYGAA4teL1+yI57f5ysDNwgAC529f0kOmWRiYVgEpDgYmEA8AUzEKsQAAAHicY2BkYGB2+O/AEMPCAAJAkpEBFbAAADgKAe0EAAAiAAAAAAQAAAAEAAAAAAAAKgAqACoAiAAAeJxjYGRgYGBhsGFgYgABEMkFhAwM/xn0QAIAD6YBhwB4nI1Ty07cMBS9QwKlQapQW3VXySvEqDCZGbGaHULiIQ1FKgjWMxknMfLEke2A+IJu+wntrt/QbVf9gG75jK577Lg8K1qQPCfnnnt8fX1NRC/pmjrk/zprC+8D7tBy9DHgBXoWfQ44Av8t4Bj4Z8CLtBL9CniJluPXASf0Lm4CXqFX8Q84dOLnMB17N4c7tBo1AS/Qi+hTwBH4rwHHwN8DXqQ30XXAS7QaLwSc0Gn8NuAVWou/gFmnjLrEaEh9GmDdDGgL3B4JsrRPDU2hTOiMSuJUIdKQQayiAth69r6akSSFqIJuA19TrzCIaY8sIoxyrNIrL//pw7A2iMygkX5vDj+G+kuoLdX4GlGK/8Lnlz6/h9MpmoO9rafrz7ILXEHHaAx95s9lsI7AHNMBWEZHULnfAXwG9/ZqdzLI08iuwRloXE8kfhXYAvE23+23DU3t626rbs8/8adv+9DWknsHp3E17oCf+Z48rvEQNZ78paYM38qfk3v/u3l3u3GXN2Dmvmvpf1Srwk3pB/VSsp512bA/GG5i2WJ7wu430yQ5K3nFGiOqgtmSB5pJVSizwaacmUZzZhXLlZTq8qGGFY2YcSkqbth6aW1tRmlaCFs2016m5qn36SbJrqosG4uMV4aP2PHBmB3tjtmgN2izkGQyLWprekbIntJFing32a5rKWCN/SdSoga45EJykyQ7asZvHQ8PTm6cslIpwyeyjbVltNikc2HTR7YKh9LBl9DADC0U/jLcBZDKrMhUBfQBvXRzLtFtjU9eNHKin0x5InTqb8lNpfKv1s1xHzTXRqgKzek/mb7nB8RZTCDhGEX3kK/8Q75AmUM/eLkfA+0Hi908Kx4eNsMgudg5GLdRD7a84npi+YxNr5i5KIbW5izXas7cHXIMAau1OueZhfj+cOcP3P8MNIWLyYOBuxL6DRylJ4cAAAB4nGNgYoAALjDJyIAOWMCiTIxMLDmZedkABtIBygAAAA==) format('woff');
}

.markdown-body {
  font-family: sans-serif;
  -ms-text-size-adjust: 100%;
  -webkit-text-size-adjust: 100%;
  color: #333333;
  overflow: hidden;
  font-family: "Helvetica Neue", Helvetica, "Segoe UI", Arial, freesans, sans-serif;
  font-size: 16px;
  line-height: 1.6;
  word-wrap: break-word;
}

.markdown-body a {
  background: transparent;
}

.markdown-body a:active,
.markdown-body a:hover {
  outline: 0;
}

.markdown-body b,
.markdown-body strong {
  font-weight: bold;
}

.markdown-body mark {
  background: #ff0;
  color: #000;
  font-style: italic;
  font-weight: bold;
}

.markdown-body sub,
.markdown-body sup {
  font-size: 75%;
  line-height: 0;
  position: relative;
  vertical-align: baseline;
}
.markdown-body sup {
  top: -0.5em;
}
.markdown-body sub {
  bottom: -0.25em;
}

.markdown-body h1 {
  font-size: 2em;
  margin: 0.67em 0;
}

.markdown-body img {
  border: 0;
}

.markdown-body hr {
  -moz-box-sizing: content-box;
  box-sizing: content-box;
  height: 0;
}

.markdown-body pre {
  overflow: auto;
}

.markdown-body code,
.markdown-body kbd,
.markdown-body pre,
.markdown-body samp {
  font-family: monospace, monospace;
  font-size: 1em;
}

.markdown-body input {
  color: inherit;
  font: inherit;
  margin: 0;
}

.markdown-body html input[disabled] {
  cursor: default;
}

.markdown-body input {
  line-height: normal;
}

.markdown-body input[type="checkbox"] {
  box-sizing: border-box;
  padding: 0;
}

.markdown-body table {
  border-collapse: collapse;
  border-spacing: 0;
}

.markdown-body td,
.markdown-body th {
  padding: 0;
}

.markdown-body .codehilitetable {
  border: 0;
  border-spacing: 0;
}

.markdown-body .codehilitetable tr {
  border: 0;
}

.markdown-body .codehilitetable pre,
.markdown-body div.codehilite {
  margin: 0;
}

.markdown-body .linenos,
.markdown-body .code,
.markdown-body .codehilitetable td {
  border: 0;
  padding: 0;
}

.markdown-body td:not(.linenos) .linenodiv {
  padding: 0 !important;
}

.markdown-body .code {
  width: 100%;
}

.markdown-body .linenos div pre,
.markdown-body .linenodiv pre,
.markdown-body .linenodiv {
  border: 0;
  -webkit-border-radius: 0;
  -moz-border-radius: 0;
  border-radius: 0;
  -webkit-border-top-left-radius: 3px;
  -webkit-border-bottom-left-radius: 3px;
  -moz-border-radius-topleft: 3px;
  -moz-border-radius-bottomleft: 3px;
  border-top-left-radius: 3px;
  border-bottom-left-radius: 3px;
}

.markdown-body .code div pre,
.markdown-body .code div {
  border: 0;
  -webkit-border-radius: 0;
  -moz-border-radius: 0;
  border-radius: 0;
  -webkit-border-top-right-radius: 3px;
  -webkit-border-bottom-right-radius: 3px;
  -moz-border-radius-topright: 3px;
  -moz-border-radius-bottomright: 3px;
  border-top-right-radius: 3px;
  border-bottom-right-radius: 3px;
}

.markdown-body * {
  -moz-box-sizing: border-box;
  box-sizing: border-box;
}

.markdown-body input {
  font: 13px Helvetica, arial, freesans, clean, sans-serif, "Segoe UI Emoji", "Segoe UI Symbol";
  line-height: 1.4;
}

.markdown-body a {
  color: #4183c4;
  text-decoration: none;
}

.markdown-body a:hover,
.markdown-body a:focus,
.markdown-body a:active {
  text-decoration: underline;
}

.markdown-body hr {
  height: 0;
  margin: 15px 0;
  overflow: hidden;
  background: transparent;
  border: 0;
  border-bottom: 1px solid #ddd;
}

.markdown-body hr:before,
.markdown-body hr:after {
  display: table;
  content: " ";
}

.markdown-body hr:after {
  clear: both;
}

.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
  margin-top: 15px;
  margin-bottom: 15px;
  line-height: 1.1;
}

.markdown-body h1 {
  font-size: 30px;
}

.markdown-body h2 {
  font-size: 21px;
}

.markdown-body h3 {
  font-size: 16px;
}

.markdown-body h4 {
  font-size: 14px;
}

.markdown-body h5 {
  font-size: 12px;
}

.markdown-body h6 {
  font-size: 11px;
}

.markdown-body blockquote {
  margin: 0;
}

.markdown-body ul,
.markdown-body ol {
  padding: 0;
  margin-top: 0;
  margin-bottom: 0;
}

.markdown-body ol ol,
.markdown-body ul ol {
  list-style-type: lower-roman;
}

.markdown-body ul ul ol,
.markdown-body ul ol ol,
.markdown-body ol ul ol,
.markdown-body ol ol ol {
  list-style-type: lower-alpha;
}

.markdown-body dd {
  margin-left: 0;
}

.markdown-body code,
.markdown-body pre,
.markdown-body samp {
  font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace;
  font-size: 12px;
}

.markdown-body pre {
  margin-top: 0;
  margin-bottom: 0;
}

.markdown-body kbd {
  background-color: #e7e7e7;
  background-image: -moz-linear-gradient(#fefefe, #e7e7e7);
  background-image: -webkit-linear-gradient(#fefefe, #e7e7e7);
  background-image: linear-gradient(#fefefe, #e7e7e7);
  background-repeat: repeat-x;
  border-radius: 2px;
  border: 1px solid #cfcfcf;
  color: #000;
  padding: 3px 5px;
  line-height: 10px;
  font: 11px Consolas, "Liberation Mono", Menlo, Courier, monospace;
  display: inline-block;
}

.markdown-body>*:first-child {
  margin-top: 0 !important;
}

.markdown-body>*:last-child {
  margin-bottom: 0 !important;
}

.markdown-body .headeranchor-link {
  position: absolute;
  top: 0;
  bottom: 0;
  left: 0;
  display: block;
  padding-right: 6px;
  padding-left: 30px;
  margin-left: -30px;
}

.markdown-body .headeranchor-link:focus {
  outline: none;
}

.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
  position: relative;
  margin-top: 1em;
  margin-bottom: 16px;
  font-weight: bold;
  line-height: 1.4;
}

.markdown-body h1 .headeranchor,
.markdown-body h2 .headeranchor,
.markdown-body h3 .headeranchor,
.markdown-body h4 .headeranchor,
.markdown-body h5 .headeranchor,
.markdown-body h6 .headeranchor {
  display: none;
  color: #000;
  vertical-align: middle;
}

.markdown-body h1:hover .headeranchor-link,
.markdown-body h2:hover .headeranchor-link,
.markdown-body h3:hover .headeranchor-link,
.markdown-body h4:hover .headeranchor-link,
.markdown-body h5:hover .headeranchor-link,
.markdown-body h6:hover .headeranchor-link {
  height: 1em;
  padding-left: 8px;
  margin-left: -30px;
  line-height: 1;
  text-decoration: none;
}

.markdown-body h1:hover .headeranchor-link .headeranchor,
.markdown-body h2:hover .headeranchor-link .headeranchor,
.markdown-body h3:hover .headeranchor-link .headeranchor,
.markdown-body h4:hover .headeranchor-link .headeranchor,
.markdown-body h5:hover .headeranchor-link .headeranchor,
.markdown-body h6:hover .headeranchor-link .headeranchor {
  display: inline-block;
}

.markdown-body h1 {
  padding-bottom: 0.3em;
  font-size: 2.25em;
  line-height: 1.2;
  border-bottom: 1px solid #eee;
}

.markdown-body h2 {
  padding-bottom: 0.3em;
  font-size: 1.75em;
  line-height: 1.225;
  border-bottom: 1px solid #eee;
}

.markdown-body h3 {
  font-size: 1.5em;
  line-height: 1.43;
}

.markdown-body h4 {
  font-size: 1.25em;
}

.markdown-body h5 {
  font-size: 1em;
}

.markdown-body h6 {
  font-size: 1em;
  color: #777;
}

.markdown-body p,
.markdown-body blockquote,
.markdown-body ul,
.markdown-body ol,
.markdown-body dl,
.markdown-body table,
.markdown-body pre,
.markdown-body .admonition {
  margin-top: 0;
  margin-bottom: 16px;
}

.markdown-body hr {
  height: 4px;
  padding: 0;
  margin: 16px 0;
  background-color: #e7e7e7;
  border: 0 none;
}

.markdown-body ul,
.markdown-body ol {
  padding-left: 2em;
}

.markdown-body ul ul,
.markdown-body ul ol,
.markdown-body ol ol,
.markdown-body ol ul {
  margin-top: 0;
  margin-bottom: 0;
}

.markdown-body li>p {
  margin-top: 16px;
}

.markdown-body dl {
  padding: 0;
}

.markdown-body dl dt {
  padding: 0;
  margin-top: 16px;
  font-size: 1em;
  font-style: italic;
  font-weight: bold;
}

.markdown-body dl dd {
  padding: 0 16px;
  margin-bottom: 16px;
}

.markdown-body blockquote {
  padding: 0 15px;
  color: #777;
  border-left: 4px solid #ddd;
}

.markdown-body blockquote>:first-child {
  margin-top: 0;
}

.markdown-body blockquote>:last-child {
  margin-bottom: 0;
}

.markdown-body table {
  display: block;
  width: 100%;
  overflow: auto;
  word-break: normal;
  word-break: keep-all;
}

.markdown-body table th {
  font-weight: bold;
}

.markdown-body table th,
.markdown-body table td {
  padding: 6px 13px;
  border: 1px solid #ddd;
}

.markdown-body table tr {
  background-color: #fff;
  border-top: 1px solid #ccc;
}

.markdown-body table tr:nth-child(2n) {
  background-color: #f8f8f8;
}

.markdown-body img {
  max-width: 100%;
  -moz-box-sizing: border-box;
  box-sizing: border-box;
}

.markdown-body code,
.markdown-body samp {
  padding: 0;
  padding-top: 0.2em;
  padding-bottom: 0.2em;
  margin: 0;
  font-size: 85%;
  background-color: rgba(0,0,0,0.04);
  border-radius: 3px;
}

.markdown-body code:before,
.markdown-body code:after {
  letter-spacing: -0.2em;
  content: "\00a0";
}

.markdown-body pre>code {
  padding: 0;
  margin: 0;
  font-size: 100%;
  word-break: normal;
  white-space: pre;
  background: transparent;
  border: 0;
}

.markdown-body .codehilite {
  margin-bottom: 16px;
}

.markdown-body .codehilite pre,
.markdown-body pre {
  padding: 16px;
  overflow: auto;
  font-size: 85%;
  line-height: 1.45;
  background-color: #f7f7f7;
  border-radius: 3px;
}

.markdown-body .codehilite pre {
  margin-bottom: 0;
  word-break: normal;
}

.markdown-body pre {
  word-wrap: normal;
}

.markdown-body pre code {
  display: inline;
  max-width: initial;
  padding: 0;
  margin: 0;
  overflow: initial;
  line-height: inherit;
  word-wrap: normal;
  background-color: transparent;
  border: 0;
}

.markdown-body pre code:before,
.markdown-body pre code:after {
  content: normal;
}

/* Admonition */
.markdown-body .admonition {
  -webkit-border-radius: 3px;
  -moz-border-radius: 3px;
  border-radius: 3px;
  padding: 0.5em 1em 0.5em 1em;
  color: #888888;
  background-color: #F7F7F7;
  border: 1px solid #888888;
}

.markdown-body .admonition blockquote {
  color: rgba(136, 136, 136, 0.5);
  border-left: 4px solid rgba(136, 136, 136, 0.5);
}

.markdown-body .admonition table {
  color: #333;
}

.markdown-body .admonition p {
  padding: 0;
  margin: 0;
}

.markdown-body .admonition-title {
  font-weight: bold;
  margin: 0;
}

.markdown-body .admonition-icon {
  font: normal normal 16px fontawesome-mini;
  line-height: 1.5;
  -moz-osx-font-smoothing: grayscale;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  user-select: none;
  float: left;
}

.markdown-body .attention {
  color: #4F8A10;
  background-color: #DFF2BF;
  border: 1px solid #4F8A10;
}

.markdown-body .caution {
  color: #D63301;
  background-color: #FFCCBA;
  border: 1px solid #D63301;
}

.markdown-body .hint {
  color: #00529B;
  background-color: #BDE5F8;
  border: 1px solid #00529B;
}

.markdown-body .danger {
  color: #D8000C;
  background-color: #FFBABA;
  border: 1px solid #D8000C;
}

.markdown-body .question {
  color: #00049b;
  background-color: #BDC8F8;
  border: 1px solid #00049b;
}

.markdown-body .note {
  color: #9F6000;
  background-color: #FEEFB3;
  border: 1px solid #9F6000;
}

.markdown-body .attention {
  color: #4F8A10;
  background-color: #DFF2BF;
  border: 1px solid #4F8A10;
}

.markdown-body .caution blockquote {
  color: rgba(214, 51, 1, .5);
  border-left: 4px solid rgba(214, 51, 1, .5);
}

.markdown-body .hint blockquote {
  color: rgba(0, 82, 155, .5);
  border-left: 4px solid rgba(0, 82, 155, .5);
}

.markdown-body .danger blockquote {
  color: rgba(216, 0, 12, .5);
  border-left: 4px solid rgba(216, 0, 12, .5);
}

.markdown-body .question blockquote {
  color: rgba(0, 4, 155, .5);
  border-left: 4px solid rgba(0, 4, 155, .5);;
}

.markdown-body .note blockquote {
  color: rgba(159, 96, 0, .5);
  border-left: 4px solid rgba(159, 96, 0, .5);
}

.markdown-body .admonition-icon:before {
  content: "\f040\00a0";
}

.markdown-body .attention > .admonition-icon:before {
  content: "\f058\00a0";
}

.markdown-body .caution > .admonition-icon:before {
  content: "\f06a\00a0";
}

.markdown-body .hint > .admonition-icon:before {
  content: "\f05a\00a0";
}

.markdown-body .danger > .admonition-icon:before {
  content: "\f056\00a0";
}

.markdown-body .question > .admonition-icon:before {
  content: "\f059\00a0";
}

.markdown-body .note > .admonition-icon:before {
  content: "\f040\00a0";
}

/* progress bar*/
.markdown-body .progress {
  display: block;
  width: 300px;
  margin: 10px 0;
  height: 24px;
  border: 1px solid #ccc;
  -webkit-border-radius: 3px;
  -moz-border-radius: 3px;
  border-radius: 3px;
  background-color: #F8F8F8;
  position: relative;
  box-shadow: inset -1px 1px 3px rgba(0, 0, 0, .1);
}

.markdown-body .progress-label {
  position: absolute;
  text-align: center;
  font-weight: bold;
  width: 100%; margin: 0;
  line-height: 24px;
  color: #333;
  text-shadow: 1px 1px 0 #fefefe, -1px -1px 0 #fefefe, -1px 1px 0 #fefefe, 1px -1px 0 #fefefe, 0 1px 0 #fefefe, 0 -1px 0 #fefefe, 1px 0 0 #fefefe, -1px 0 0 #fefefe, 1px 1px 2px #000;
  -webkit-font-smoothing: antialiased !important;
  white-space: nowrap;
  overflow: hidden;
}

.markdown-body .progress-bar {
  height: 24px;
  float: left;
  border-right: 1px solid #ccc;
  -webkit-border-radius: 3px;
  -moz-border-radius: 3px;
  border-radius: 3px;
  background-color: #34c2e3;
  box-shadow: inset 0 1px 0 rgba(255, 255, 255, .5);
  background-size: 30px 30px;
  background-image: -webkit-linear-gradient(
    135deg, rgba(255, 255, 255, .4) 27%,
    transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%,
    transparent 77%, transparent
  );
  background-image: -moz-linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
  background-image: -ms-linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
  background-image: -o-linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
  background-image: linear-gradient(
    135deg,
    rgba(255, 255, 255, .4) 27%, transparent 27%,
    transparent 53%, rgba(255, 255, 255, .4) 53%,
    rgba(255, 255, 255, .4) 77%, transparent 77%,
    transparent
  );
}

.markdown-body .progress-100plus .progress-bar {
  background-color: #1ee038;
}

.markdown-body .progress-80plus .progress-bar {
  background-color: #86e01e;
}

.markdown-body .progress-60plus .progress-bar {
  background-color: #f2d31b;
}

.markdown-body .progress-40plus .progress-bar {
  background-color: #f2b01e;
}

.markdown-body .progress-20plus .progress-bar {
  background-color: #f27011;
}

.markdown-body .progress-0plus .progress-bar {
  background-color: #f63a0f;
}

.markdown-body .gloss .progress-bar {
  box-shadow:
    inset -1px 1px 0 rgba(255, 255, 255, .5),
    inset 0 -4px 12px rgba(255, 255, 255, .7),
    inset 0 4px 12px rgba(255, 255, 255, .7),
    inset 0 -12px 0 rgba(0, 0, 0, .05),
    inset 1px -1px 0 rgba(255, 255, 255, .2);
}

.markdown-body .candystripe-animate .progress-bar{
  -webkit-animation: animate-stripes 3s linear infinite;
  -moz-animation: animate-stripes 3s linear infinite;
  animation: animate-stripes 3s linear infinite;
}

@-webkit-keyframes animate-stripes {
  0% {
    background-position: 0 0;
  }

  100% {
    background-position: 60px 0;
  }
}

@-moz-keyframes animate-stripes {
  0% {
    background-position: 0 0;
  }

  100% {
    background-position: 60px 0;
  }
}

@keyframes animate-stripes {
  0% {
    background-position: 0 0;
  }

  100% {
    background-position: 60px 0;
  }
}

/* Multimarkdown Critic Blocks */
.markdown-body .critic_mark {
  background: #ff0;
}

.markdown-body .critic_delete {
  color: #c82829;
  text-decoration: line-through;
}

.markdown-body .critic_insert {
  color: #718c00 ;
  text-decoration: underline;
}

.markdown-body .critic_comment {
  color: #8e908c;
  font-style: italic;
}

.markdown-body .headeranchor {
  font: normal normal 16px octicons-anchor;
  line-height: 1;
  display: inline-block;
  text-decoration: none;
  -webkit-font-smoothing: antialiased;
  -moz-osx-font-smoothing: grayscale;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  user-select: none;
}

.headeranchor:before {
  content: '\f05c';
}

.markdown-body .task-list-item {
  list-style-type: none;
}

.markdown-body .task-list-item+.task-list-item {
  margin-top: 3px;
}

.markdown-body .task-list-item input {
  margin: 0 4px 0.25em -20px;
  vertical-align: middle;
}

/* Media */
@media only screen and (min-width: 480px) {
  .markdown-body {
    font-size:14px;
  }
}

@media only screen and (min-width: 768px) {
  .markdown-body {
    font-size:16px;
  }
}

@media print {
  .markdown-body * {
    background: transparent !important;
    color: black !important;
    filter:none !important;
    -ms-filter: none !important;
  }

  .markdown-body {
    font-size:12pt;
    max-width:100%;
    outline:none;
    border: 0;
  }

  .markdown-body a,
  .markdown-body a:visited {
    text-decoration: underline;
  }

  .markdown-body .headeranchor-link {
    display: none;
  }

  .markdown-body a[href]:after {
    content: " (" attr(href) ")";
  }

  .markdown-body abbr[title]:after {
    content: " (" attr(title) ")";
  }

  .markdown-body .ir a:after,
  .markdown-body a[href^="javascript:"]:after,
  .markdown-body a[href^="#"]:after {
    content: "";
  }

  .markdown-body pre {
    white-space: pre;
    white-space: pre-wrap;
    word-wrap: break-word;
  }

  .markdown-body pre,
  .markdown-body blockquote {
    border: 1px solid #999;
    padding-right: 1em;
    page-break-inside: avoid;
  }

  .markdown-body .progress,
  .markdown-body .progress-bar {
    box-shadow: none;
  }

  .markdown-body tr,
  .markdown-body img {
    page-break-inside: avoid;
  }

  .markdown-body img {
    max-width: 100% !important;
  }

  .markdown-body p,
  .markdown-body h2,
  .markdown-body h3 {
    orphans: 3;
    widows: 3;
  }

  .markdown-body h2,
  .markdown-body h3 {
    page-break-after: avoid;
  }
}
</style><title>CMPSC380 Day 9</title></head><body><article class="markdown-body"><h1 id="september-18th-2014"><a name="user-content-september-18th-2014" href="#september-18th-2014" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>September 18th, 2014</h1>
<h4 id="pragmas"><code>PRAGMA</code>s</h4>
<ul>
<li><code>PRAGMA</code> command: additional directives to SQLite indicating how DB is managed</li>
<li><code>PRAGMA foreign_key = ON</code><ul>
<li>Defaults to off</li>
<li>For backwards compatibility reasons</li>
</ul>
</li>
</ul>
<h4 id="schema-diagrams"><a name="user-content-schema-diagrams" href="#schema-diagrams" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>Schema Diagrams</h4>
<ul>
<li>Types<ul>
<li>Informal</li>
<li>Formal<ul>
<li>E-R (Entity-Relationship) Diagram</li>
</ul>
</li>
</ul>
</li>
<li>Benefits:<ul>
<li>Show primary &amp; foreign keys</li>
<li>high-level &lsquo;map&rsquo; of db</li>
</ul>
</li>
<li>Cool tools:<ul>
<li><code>SQLFairy</code><ul>
<li>Converts between dialects of <code>CREATE</code> syntax</li>
<li>Generates E-R-like visualizations (<code>GraphViz</code>, <code>GD</code>)</li>
<li>Written in Perl</li>
<li>Specifically, you&rsquo;d use the <code>sql-graph tool</code></li>
<li><code>.sql file -&gt; [sql-graph] -&gt; technical diagram</code></li>
</ul>
</li>
<li><code>SchemaSpy</code><ul>
<li>Produces broswer-displayable (e.g. HTML) representations of DBs</li>
<li>Also uses <code>GraphViz</code></li>
<li>Command-line but has optional GUI</li>
<li>Written in Java</li>
</ul>
</li>
</ul>
</li>
</ul>
<h4 id="joining-tables-together"><a name="user-content-joining-tables-together" href="#joining-tables-together" class="headeranchor-link"  aria-hidden="true"><span class="headeranchor"></span></a>Joining tables together</h4>
<ul>
<li>Natural join</li>
<li>Cartesian product<ul>
<li>Two tables</li>
<li>Join every tuple in t_1 with every tuple in t_2</li>
<li>Efficiency: O(<em>n</em>!)</li>
</ul>
</li>
</ul></article></body></html>

## CMPSC380 Day 9.md

      
    Raw
  

              CMPSC380 Day 9.md
            
          
    CMPSC380: DATABASES
September 18th, 2014

PRAGMAs


PRAGMA command: additional directives to SQLite indicating how DB is managed
PRAGMA foreign_key = ON

Defaults to off
For backwards compatibility reasons


Schema Diagrams


Types

Informal
Formal

E-R (Entity-Relationship) Diagram


Benefits:

Show primary & foreign keys
high-level 'map' of db


Cool tools:

SQLFairy

Converts between dialects of CREATE syntax
Generates E-R-like visualizations (GraphViz, GD)
Written in Perl
Specifically, you'd use the sql-graph tool
.sql file -> [sql-graph] -> technical diagram


SchemaSpy

Produces broswer-displayable (e.g. HTML) representations of DBs
Also uses GraphViz
Command-line but has optional GUI
Written in Java


Joining tables together


Natural join
Cartesian product

Two tables
Join every tuple in t_1 with every tuple in t_2
Efficiency: O(n^2)


Chapter 3

SQL Features


SQL:

Data definition language
Data manipulation language
Integrity constraint specification

Ensure that data doesn't get corrupted
e.g.:

NOT NULL
CHECK
PRIMARY KEY
FOREIGN KEY


View definition

A table derived from another table
The result set of a SELECT is a view


Transaction

ACID:

A: Atomicity - a transaction is indivisible
C: Consistency - when you run a transaction, the database must be in a consistent state (integrity constraints have not been violated)
I: Isolation - concurrent execution of transactions should result in same state as serial execution
D: Durability - stable storage

this is impossible (we live in a universe that is terrible & has entropy)
RAID (_R_edundant _A_rray of _I_nexpensive _D_isks)


Either it works, or it fails but we return to a safe state


Embedded and dynamic SQL

Embedded SQL code is inside some other program

Challenges:

Compiler does not verify correctness of strings
Security (SQL injection attack)


Dynamic SQL changes during execution


Security primitives:

Authentication: Who are you?
Authorization: Are you allowed to do this?


A transaction:
       T_1, T_2 ... T_n
Begin -------------------> End