• select \(\sigma_{P}(r)\)
• project \(\Pi_{S}(r)\)
• rename \(\rho_{x(A_1,A_2,...,A_n)}(r)\)
• union \(r\cup s\)
• difference \(r-s\)
• cartesian-product \(r\times s\)

• intersection \(r\cap s = r-(r-s)\)
• natual join \(r\Join s = \Pi_{R\cup S}(\sigma_{r.A_1=s.A_1 \land ...}(r\times s))\)
  • theta join \(r\Join_{\theta}s = \sigma_{\theta}(r\times s)\)

• devision

  \[temp1 \leftarrow \Pi_{R-S}(r)\] \[temp2 \leftarrow \Pi_{R-S}((temp1\times s) - \Pi_{R-S,S}(r))\] \[result = temp1 - temp2\]

  Table 1: R

  Student	Task
  Peter	Database1
  Peter	Database2
  Peter	Compiler1
  Sara	Database1
  Sara	Database2
  Mary	Database1
  Mary	Comipler1

  Table 2: S

  Task
  Database1
  Database2

  Table 3: R÷ S

  Student
  Peter
  Sara

• aggregation \(group_column\zeta_{aggre\_func(column)}(r)\)

• delete \(r\leftarrow r - E\)
• insert \(r\leftarrow r\cup E\)
• update \(r\leftarrow \Pi_{F_1,F_2,...,F_n}(r)\)

CHAR, VARCHAR, BLOB, INT, DEC, DATE, DATETIME

1. Find the one thing need to be described.
2. List necessary information about this thing.(Depends on how to use this table)
3. Break down the information into pieces .

Data in your column is atomic if it's been broken
down into the smallest pieces that you need.

• Rule 1:

  A column with atomic data can't have several values of
  the same type of data in that column.
  One example obeys the rule 1:
  

  foodname	ingredients
  bread	flour, milk, egg, yeast, oil
  salad	lettuce, tomato, cucumber

• Rule 2:

  A table with atomic data can't have multiple columns
  with the same type of data.
  

  teacher	student1	student2
  Ms.Mary	Joe	Ron

• Rule 1: Be in 1NF
• Rule 2: Have no partial functional dependencies.

• Rule 1: Be in 2NF
• Rule 2: Have no transitive dependencies.

FD leads to the BCNF.

• Definition

  Relation R with FDs is in BCNF if:
  For each nontrivial \(\bar A\to B\), \(\bar A\) is a key.
  

• Definition

  Relation R with MVDs is in 4NF if:
  For each nontrivial \(\bar A\twoheadrightarrow \bar B\), A is a key.
  

4NF is in BCNF.

Complete: Every object is in at least one subclass.

Overlapping: One object is in two+ subclasses.

3 choices:

1. Subclass relations contain superclass key + specialized attrs
2. Subclass relations contain all attributes
3. One relation containing all superclass + subclass attrs

Best translation may depend on properties:
Heavily overlapping -> design 3
Disjoint, complete ->design 2

Examples:

1. S(_K_, A), S1(_K_, B), S2(_K_, C)
2. S(_K_, A), S1(_K_, A, B), S2(_K_, A, C)
3. S(_K_, A, B, C)

Constrain allowable database states.(static)

Major keywords: PK, FK, UNIQUE, CHECK

• Examples:

Create ...
{
    columnName type CHECK (columnName IN ('value1', 'value2'));
}

ADD CONSTRAINT CHECK columnName > 1;

CHECK 'A' = SUBSTRING(columnName, 1, 1);

• To enforce constraints(Dynamic)
• Move logic from apps into DBMS

• Event-Condition-Action Rules

  When event occurs, check condition; if true, do action.

• Syntax

  Create Trigger name
  Before|After|Instead of events
  [referencing-variables]
  [For Each Row]
  [when (condition)]
  action
  

  • events

    insert on T
    delete on T
    update [of C1,…,Cn] on T
    

  • [For Each Row]

    Determines whether the trigger is row-level or statement-level

  • referencing-variables

    Depends on [For Each Row]
    old row as var
    new row as var
    old table as var
    new table as var
    

  • condition

    In when or where clause depends on the SQL Implementation.

Different between full table scans and immediate location of tuples.

• Balanced trees (B tree, B+ tree)

  When uses ">, <, >=, <=" in query.

• Hashtable

  When uses "=" in query.

• Extra space
• Index creation

• Index maintenance(Important)

  When updates database, indexes will also be updated.

Benefits depends on:

• Data distributions
• Query vs. update load
• Size of table(and possibly layout)

• Input (database statistics and workload)
• Output (recommended indexes)

• Concurrent database access
• Resilience to system failures

• A(Atomicity)

  Each transaction is "all-or-nothing", never left half done.

• C(Consistency)

  Can assume all constrants hold when transaction begins.
  Must guarantee all constraints hold when transaction ends.
  Serializability -> constraints always hold
  

• I(Isolation)

  Serializability: Execution must be equivalent to some
  sequential(serial) order of all transactions.(e.g. T9, T1, T2, T3, …)
  

• D(Durability)

  If system crashes after transaction commits,
  all effects of transaction remain in database.
  

• dirty data: written by an uncommitted transaction

• nonrepeatable reads: an item read multiple times cannot change values

  T1:    Update Student Set GPA=(1.1)*GPA
  T2.S1: Select AVG(GPA) From Student
  T2.S2: Select MAX(GPA) From Student
  

  T2.S1 may excute before T1, T2.S2 may excute after T1.
  The GPAs in S1 and S2 are different, leads to a nonrepatable reads violation.
  

• phantoms

  T1:    Insert Into Student [100 new tuples]
  T2.S1: Select AVG(GPA) From Student
  T2.S2: Select MAX(GPA) From Student
  

  T2.S1 may excute before T1, T2.S2 may excute after T1.
  [100 new tuples] are known as the phantoms tuples.
  

• Standard default: Serializable
• Some systems have default Repeatable Read