Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

CS 411 Midterm 1 Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

SQL (Struc­tured Query Language)

Relational Database Model

- Data is structured as relations
- Defines a limited set of operations (query and modifi­cation) to interact with the data
- Allow for defining constr­aints on columns (attri­butes), a table (relat­ion), relati­onships among tables (foreign keys).

Syntax: Patterns and "­LIK­E"

- Used in a WHERE clause
- General form: 
<At­tri­but­e> LIKE <pa­tte­rn>
or 
<At­tri­but­e> NOT LIKE <pa­tte­rn>
- Pattern: quoted string with 
%
for any string, 
_
for any character

Closed

Query language is closed if we can use the answer from one query as input to another query

SQL Subquery Example

SELECT *
FROM (SELECT * FROM Customer WHERE
  name LIKE 'A%') as temp 
WHERE temp.phone LIKE '5%';

SQL Subqueries that Return Scalar

Subquery can be used as value if guaranteed to produce one tuple with one component
- “Single” tuple often guaranteed by key constraint
- A run-time error if not scalar

SQL: Boolean Operators

<tu­ple> IN <re­lat­ion> is true if and only if the tuple is a member of the relation.
EXISTS( <re­lat­ion> ) is true if and only if the <re­lat­ion> is not empty.
x = ANY( <re­lat­ion> ) is a boolean condition meaning that x equals at least one tuple in the relation.
Similarly, x <> ALL( <re­lat­ion> ) is true iff for every tuple t in the relation, x is not equal to t

Ex: Aggreg­ations

 SELECT AVG(price) FROM Sells
WHERE drink = ‘Mocha’;

Aggreg­ations

SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggreg­ation on the column.

Ex: Grouping

SELECT customer, AVG(price)
FROM Frequents, Sells 
WHERE drink = ‘Mocha’ AND Frequents.cafe = Sells.cafe
GROUP BY customer
   

Single User Assump­tions:

- Each operation (UPDATE ... SET ... WHERE) is executed one at a time
- ISOLATION - one op exec, maybe change DB, then next op exec
- ATOMIC - op exec entirely or not at all

Transa­ctions Defini­tions

- Group the SFW and USW into a transa­ction
- Transa­ction is a sequence of statements considered a "unit of operat­ion­" on DB
- Serial­iza­bility of transa­ctions - Either user1 transa­ction exec first or user2's, but not in parallel

Transa­ctions

Transa­ction: sequence of read/write ops on the DB w/ the property that either all or none of actions complete.
- May either succeed (COMMIT), or fail (ABORT or ROLLBACK)

ACID Properties

Atomicity
either all ops exec or none
Consis­tency
trans exec in isolation keeps DB in consistent state
Isolation
trans isolated from effects of other concur­rently exec trans
Durability
updates stay in DBMS

Transa­ction Manager

Ensure that transa­ctions that exec in parallel don’t interfere with each other.

Concurrent Execution Problems

Write-Read conflict
dirty/­inc­ons­istent read
Read-Write conflict
Unrepe­atable read
Write-­Write conflict
lost update