【数据库】Database Modeling and Implementation

主要写给自己看的笔记，可能有点草率，继续更新。有时间会补充详细一点，欢迎友好的讨论指正！ （大概没人看hh）

Ideas/requirements -> E/R design -> Relational schema -> Relational database

E/R design:

1. Entity    Entity Set
   weak entity types, 依赖一个strong ( normal entity ), 有strong才有他 eg. 有我才有我要写的作业 
     - partial key 我给作业编个号

2. Attribute

• 分类

  • structure - Simple attribute - Composite attribute

  • value - Single-valued - Multi-valued

  • derived attribute 计算后得到的

• form the keys all can identify uniquely an entity in an entity set(candidate) key  
  3. Relationship Relationship set

• Cardinality ratio 适合binary, recursive,不适合 n-ary
  横线上写1或N 或 箭头表1, 横线表N

• Participation constraint Total=, Partial-

• (min,max)
  (Participation constraint, Cardinality ratio)

• 

Relational database schema

Informal Terms	Formal Terms
Table	Relation
Column Header	Attribute
All possible Column Values	Domain
Row	Tuple
Table Definition	Schema of a Relation
Populated Table 填充表	State of the Relation

ER-to-Relational Mapping Algorithm

几个大概，细节请动脑子记 😊
（一下几条除注释都来自课件）
(a) Map strong entity type into relation
(b) Map weak entity + identifying relationship type into relation
primary key of the relation:
primary key of the owner entity(now foreign key of the relation) + partial key of the weak entity
(c) Map binary 1:1 relationship types into attributes
foreign key
(d) Map binary 1:N Relationship types into attributes
1 老师教 n 学生: tid是学生表的attribute, 且是foreign key
sid是primary key
(e) Map binary M:N relationship type into relation
每个tuple是特殊可认的-->
foreign keys: primary keys of the participating entity as foreign keys
primary key: the combination of fks
(f) Map N-ary relationship type into relation
同上
(g) Map multi-valued attribute into relation
1个公司有n个地址(i.e. multi-valued attribute)
fk: 公司的pk
pk: the combinations of fk 和 自己的

Relational Database Schema

由两部分组成

• a set of relation schema S = {R1, R2, ..., Rn}

• a set of integrity constraints IC (完整性约束)(必须满足)

Integrity constraints

• Inherent or Implicit Constraints: These are based on the data model itself. E.g., the relational model does not allow multiple values for any attribute

• Schema-based or Explicit Constraints: They are expressed in the schema by using the facilities provided by the model. E.g., max. cardinality ratio constraint in the ER model

  INSERT, UPDATE 四种都可能触犯 DELETE may violate only referential integrity

  • Domain constraints: C-Name: string of char(30)

  • Key 主键 can uniquely identify

  • Entity integrity 主键不为null

  • Referential integrity 如果关系R1的Fk与关系R2的Pk相对应 则R1中的每一个元组的 Fk 或者等于R2 中某个元组的Pk 或者为空值 因此改变对应主键也会对这边的fk造成影响，可能引起对Referential integrity constraints的冲突 violation on Referential integrity constraints

    • 图像显示 箭头从fk指向对应pk

• Application-based or Semantic constraints: These are beyond the expressive power of the model and must be specified and enforced by the application programs. E.g., 工资不能比老板高

• Integrity violation解决方法

  • cancel the operation that causes the violation

  • perform the operation but inform the user of the violation (e.g. ask the user to provide a valid value )

  • trigger additional updates so the violation is corrected (e.g. cascade(传递) the deletion by deleting tuples that reference the tuple being deleted)

  • execute a user-specified error-correction routine

Functional Dependency

作用： find the super key--> candidate key develop the concept about normalization 仔细理解概念，简单来说，描述attributes 间的关系, X决定Y

Inference Rules for FDs

Armstrong's inference rules:(最基础 sound and complete)
IR1. (Reflexive) If Y is a subset of X, then X→Y
IR2. (Augmentation) If X→Y, then XZ →YZ (Notation: XZ stands for X U Z)
IR3. (Transitive) If X→Y and Y→Z, then X→Z

有用推论
Decomposition: If X →YZ, then X →Y and X →Z
Union: If X →Y and X →Z, then X →YZ
Pseudotransitivity: If X →Y and WY →Z, then WX →Z

Closure of F(F+)

Closure of a set F (denoted by F+ ): the set of all FDs that include F as well as all dependencies that can be inferred from F.

Cover

简记: F covers G: G 是 F+ 子集 [Definition] A set of functional dependencies F is said to cover another set of functional dependency G if every FD in G is also in F+

Equivalent

[Definition] Two sets of FDs F and G are equivalent if F+ =G+

Closure of a set of attributes X under F ( X+ )

Closure of a set of attributes X under F (denoted by X+ ):
Set of all attributes that are functionally determined by X based on F
Note both X and X+ are a set of attributes
If X+ consists of all attributes of R, X is a superkey for R