小留学生辅导老师的SQL总结（渣渣的第一篇文章

小留学生辅导老师的SQL总结（渣渣的第一篇文章

基础知识总结篇

SQL复习文件

 数据库构建过程
a. Describe two stages of database design process and the steps within
Database design & development process:
Stage 1: Information-level design
Output: ERD
Independent on your DBMS choice
(other name: conceptual design)
Stage 2: Physical-level design
Output: physical database
Dependent on your DBMS choice
 ERD
Chen ERD
还有其他形式
 关系数据库Relational databases最重要的三个要素：

1. Entity = object, person, place, activity
   

2. Attribute = characteristics of entity
   ( Candidate Key（唯一识别），主键（唯一识别+被选为主键），外键)—但是一般为了满足范式直接另外设立一个ID

3. Relationship:（一对一 11，一对多1N，多对多MN（转化为两个一对多，bridge (bridge entity)））多对多在实际设计中不存在
   可以限定是否必须为1，或者也可以为0（Cardinality）
   

ERD 小技巧 （分模块开发（可以在ERD上分为不同颜色），思考如何融合各个模块）

 recursive FK

Person中有些人是经理，有些人不是，所以可以管理其他人
MANAGERID和PERSONID是一样的
MANAGERID在PERSONID后面表示这个员工的管理者（MANAGERID是这个人的管理着的PERSONID）是谁，如果一个人没有MANAGERID那么他本身就是一个上层管理者，所以没人管他

分层级

 范式normal form以及各种依赖
1NF

2NF

3NF

4NF

 转化关系

1. One-to-many relationship: you have to create a foreign key in the “many” side that contains the primary key for the “one” side

1.1 A foreign key is a value that can reference to another value in another table
2. Many-to-many relationship: M-M is NOT allowed in a database. To normalize a M-M relationship, you have to create a new table called “bridge table”

2.1. The M-M relationship  2 1-M relationships
2.2. The primary keys for the two “many” tables become the primary key and foreign key in the bridge table
3. One-to-one relationship: is turned into one-to-many relationship

4. Attributes will many nulls become a new table

5. Multivalued attributes will become new tables

6. Unary relationship needs a new attribute in the same table

When normalize ERD:
Composite attributes must become single attribute
Multivalued attributes must become its own entity:

REFERENCE:
图片来自当年我的老师的PPT和网图