%toc

• Chapter5, 44, EDB and IDB
• Chapter6-BasicQuery, 29~32, Operational Semantics

1. Introduction
2. Relational Model
   1. Terms
   2. Algebra (or called Operations)
3. Design Theory of Relational Model
   1. FD, MVD and Normal Forms
4. High-Level Database Models
   1. E/R Model
   2. UML
   3. ODL
5. Logic Query Languages
   1. Datalog
   2. Comparation of Relational Algebra and Datalog Algebra
6. Database Langrage -- SQL
   1. Sentences
      1. Basic Query
      2. Subquery, Aggregation
      3. Join, Modification
   2. Transaction
7. Constraints and Triggers
   1. Keys and Foreign Keys
   2. Constraints on attributes and tuples
   3. Modification of constraints
   4. Assertions
   5. Triggers
8. Views and Indexes
   1. Views (virtual and materialized)
   2. Updatable views
   3. Indexes (creation, use) (B-tree, no standard)

• UNF: 全放一张表里，选个key
• 1NF \(\leftarrow\) Repeating Attributes Removed: 让key成为真的key，能够唯一确定一个tuple
• 2NF \(\leftarrow\) Partial Key Dependencies Removed: 消除key-nonkey's FD
• 3NF \(\leftarrow\) Non-Key Dependencies Removed: 消除nonkey-nonkey's FD
• BCNF \(\leftarrow\) Dependencies between Keys Removed: 消除key-key's FD
• 4NF \(\leftarrow\) Dependencies between Key Sets Removed: 消除MVD
• 5NF, PJNF 暂未理解，参考：wikipedia
• RNFN; SKNF; 6NF 还未看，参考：《数据库范式 1NF，2NF，3NF，BCNF，4NF，RFNF，SKNF，5NF，6NF》

• Operands: relations
• Base Operators:
  • Union: \(U\)
  • Selection: \(\sigma_C\)
  • Projection: \(\pi_L\)
  • Products and Joins: \(\times, \Join, \Join_C\)
  • Renaming: \(\rho_{<table-name, all-need-attributes>}\)
• Type:
  • Set
  • Bag
• Extended (Nonclassical) Operators:
  • Duplicate-elimination(去重): \(\delta\)
  • Extended projection(可加由函数生成的新列), \(\pi_L\)
  • Sorting(以某些列为关键字排序): \(\tau_L\)
  • Grouping-and-Aggregation: \(\gamma\)
    • Aggregation: Apply a function to all tuples in one column, such as: SUM, AVG, COUNT, MIN, MAX
    • Grouping-and-Aggregation: Group with some columns and aggregation some other columns.
  • Outerjoin: \(\Join^o\)
    • Join with NULL in empty cells, so that won't lose any information.

• This form of logic allows us to write queries in the relational model.
• Rule: head \(\leftarrow\) subgoals, they are atoms, and an atom consists of an predicate applied to some number of arguments.
• IDB and EDB
• Intensional Database and Extensional Database
• Relational algebra and datalog

• 
• SQL is case insensitive
  • Only inside quoted strings does case matter
• 
• 
• 
• 
• 

• Enviroment: One DBMS, Many Users
• Transaction: A transaction is a sequence of one or more SQL operations treated as a unit.
  • Result: Commit or Rollback
  • ACID Transaction:
    • Atomic : Whole transaction or none is done.
    • Consistent : Database constraints preserved.
    • Isolated : It appears to the user as if only one process executes at a time.
    • Durable : Effects of a process survive a crash.
  • Isolation Level:
    • SET TRANSACTION ISOLATION LEVEL X = <following>
      1. SERIALIZABLE
      2. REPEATABLE READ: with phantoms
      3. READ COMMITTED: with nonrepeatable reads, phantoms
      4. READ UNCOMMITTED: with dirty reads, nonrepeatable reads, phantoms
    • This is personal choice.
    • Read only transactions
      • Set transaction read only; (before set level)
      • Help system optimize performance
      • Independent of isolation level

• 
• 
• 

• SQA（苏格兰学历管理委员会）的教程 挺棒的
• Enterprise Information Integration
• w3school SQL 教程