• Intention of a relation: relation definition and constrains
• Extention of a relation: the actual data, tuples

Relation properties

• Set Theory
  
  
  • Attributes has order (not necessary)
  • Value are used to identify tuples
  • Tuples dont have order
  • Tuples cant have duplication
• Attribute value are atomic
• Degree: How many attributes in the schema
• Cardinality: How many tuples in an instance

Constraints

• Let the DBMS to ensure the entry or modification on data are legal
• Give applications' bug no chance to ruin the data

Three Constraint Level

• Tuple-level
  
  
  • Domain restrictions (datatype)
  • Attribute comparisons (好像是指check)
• Relation-level
  
  
  • Special type of keys
    
    
    • Superkey: 能uniquely identifies a tuple的一个or一组attribute
    • Candidate key: Superkey的最小集, (最简单的一个or一组attribute)
    • Primary key: 被选中, 用来identity tuples的Candidate key
• Database-level
  
  
  • Referential integrity
    
    
    • Foreign key: 其他relation中的primary key
    • Referential integrity: 不允许出现和对应relation中的记录不符的情况
  • Inclusion dependencies: = =`不懂

• Database: is a large and persistent collection of pieces of information organized in a way that facilitate efficient retrieval and modification.
• DBMS: is a program that manages details related to storage and access for a database.
• Schema: is a description of the data interface to the database.
• Instance: is a database that conforms to a given schema.
• Transaction: is an application-specified atomic and durable unit of work.
• View: is a relation in the external schema whose instance is determined by the instances of the relations in the conceptual schema.
• Trigger: is a procedure executed by the database in response to a change to the database instance

Three Level Schema Architecture

• External schema (view): visualized data
• Conceptual schema: logical structure
• Physical schema: file/storage devices

Data Independence

• Application access the data through an abstract data model provided by the DBMS, rather than direct access
• Physical: applications cant touch the storage stucture
• Logical: applications cant change the data organization

Four Transaction Properties

NoSQL据说就是要break these properties= =`IDK why...

• Atomic: once the transaction happens, it happened. No intermediate stage!
• Consistency: transaction follows the database consitences
• Isolated: concurrent transactions wont effect each other
• Durable: transaction results permanently

What DBMS do

• Remoce common code from applications
• Provide uniform access to data
• Guarantee data integrity
• manage concurrent access
• Protect against system failure
• Set access policies for data

SELECT attribute-expression-list FROM relation-list [ WHERE condition ];

attribute-expression-list:

• [relation-name.]attribute
• [relation-name.]attribute [arithmatic computation] AS another name
  
  
  • i.e., E.Salary - 40000 AS SalaryDiff
• CASE WHEN ... THEN ...
  ELSE ... END
  
  
  • i.e., CASE WHEN E.Salary < 40000 THEN 0
    ELSE E.Salary - 40000 END

relation-list:

• list of table names
• seperate by comma

condition:

• arithmetic operation +, -, *, /
• comparisions =, <>, <, <=, >. >=
• logical connectives AND, OR, NOT
• attribute IN (Q)
• attribute NOT IN (Q)
• attribute op SOME (Q)
• attribute op ALL (Q)
• EXISTS (Q)
• NOT EXISTS (Q)
• IS [NOT] NULL

NULL

AND Table

OR Table

NOT Table

UNION INTERSECT EXCEPT

(Q1 and Q2 must have same attribute-list)

Q1 UNION Q2 => Together all the tuples in Q1 and Q2
Q1 INTERSECT Q2 => Only tuples in both Q1 and Q2
Q1 EXCEPT Q2 => Tuples only in Q1 but not in Q2

ALL关键字: 允许重复
UNION ALL will include twice of duplicate tuples
INTERSECT ALL will include all possible pairs of match tuples, duplication possible
EXCEPT ALL will include all "not in Q2" tuples, duplication possible

[INNER]/OUTER JOIN

1. Inner join把两个表连接在一起, 返回两个表中相匹配的记录, 是2和3的交集
2. Left outer join, 左侧表所有的记录都返回, 右侧匹配的记录返回, 没有匹配的返回Null
3. Right outer join, 与Left outer join相反, 右侧的记录返回, 左侧返回匹配的记录, 没有匹配返回Null
4. Full outer join, 2和3的并集
5. Cross join, 两个表的笛卡儿积, 返回所有可能的值, 不允许有连接条件

ORDER BY

SELECT ... ... ORDER BY attribute [DESC/ASC], attribute [DESC/ASC], ......

Note: 如果没有指定Order, return的数据可能是任意顺序

GROUP BY, HAVING, Aggregate expressions

{count, sum, avg, min, max} => Aggregate expressions

Order: Group => Having => Aggregate

• count(*): number of tuples
• count(E): number of tupple for which E is non-NULL
• count(distinct E): number of distinct non-NULL E values
• sum(E)
• sum(distinct E)
• avg(E)
• avg(distinct E)
• min(E)
• max(E)

Note: 没有被group by指定的attribute不能出现在SELECT的attr-list中, 除非是aggregate

INSERT INTO

INSERT INTO relation-name [( attribute-list )] VALUE ( value-list );

DELETE

DELETE FROM relation-name [ WHERE condition ];

UPDATE

UPDATE relation-name SET attribute-assignment-list [ WHERE condition ]; 

attribute-assignment-list:

• pairs of assignment
• seperate by comma
• i.e., WorkDept = 'E01', Address = 'Waterloo'

CREATE TABLE

CREATE TABLE relation-name ( attribute-name attribute-type [constraints-list], ... )

attribute-type: http://www.w3school.com.cn/sql/sql_datatypes.asp

constraints-list: (Constraints的格式在各种数据库中都不太一样, 就不列举了)

• NOT NULL
• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• Column or Tuple CHECK

CREATE VIEW

CREATE VIEW view-name AS ( SELECT ... ) 

从View SELECT的方法和table一样

CREATE TRIGGER

CREATE TRIGGER trigger-name 
AFTER UPDATE OF attribute-list ON relation-name 
REFERENCING OLD as instance-name(o) NEW as instance-name(n) 
FOR EACH ROW ...  

不同database语法不一