Database System learning note

Week 1

1.1

key terms

Data
- Facts and statistics collected together for reference or analysis
  
  收集起来供参考或分析的事实和统计数据
- Data is the actual information stored in the database
  
  数据是存储在数据库中的实际信息
Database
- A shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization.
  
  逻辑相关数据的共享集合（以及对这些数据的描述），旨在满足组织的信息需求。
DBMS
- A software system
  
  一个软件系统
- Enables users to define, create, maintain, and control access to the database.
  
  使用户能够定义、创建、维护和控制对数据库的访问。
Database application programme
- A computer program that interacts with database
  
  与数据库交互的计算机程序
- Issues appropriate requests (eg SQL statements) to the DBMS
  
  向数据库管理系统发出适当请求（如 SQL 语句）
Data model
- A model is a representation of ‘real world’ objects and events, and their associations.
  
  模型是对 "现实世界 "中的物体和事件及其关联的表示。
Schema
- The description of the database is the database schema.
  
  对数据库的描述就是数据库模式。
- Data is the actual information stored in the database
  
  数据是存储在数据库中的实际信息
- A relation schema can be thought of as the basic information describing a table or relation.
  
  关系模式可视为描述表或关系的基本信息。
Relation
- A relation is a table with rows and columns.
  
  关系是一个有行和列的表格。
Relational algebra
- Relational algebra is formal language associated with the relational Model.
  
  关系代数是与关系模型相关的形式语言。

Components in the Database Environment

componets

Roles in the Database Environment

Data Administrator (DA)
Database Administrator (DBA)
Database Designers (Logical and Physical)
**Application Developers **
End Users (naive and sophisticated)

Three level ANSI-SPARC architecture

![Three level ANSI-SPARC architecture](database/Three level ANSI-SPARC architecture.png)

Objective

Separate each user’s view of the database from the way the database is physically represented.
- Users’ views should be unaffected by changes to the physical aspects of storage.
  
  用户视图不应受存储物理方面变化的影响。
- Internal structure of the database should be unaffected by changes to the physical aspects of storage.
  
  数据库的内部结构应不受存储物理方面变化的影响。
- The DBA should be able to change the conceptual structure of the database with minimum affect to users’ views.
  
  DBA 应能在对用户视图影响最小的情况下更改数据库的概念结构。

1.2

key terns

Relational model
- In the relational model, all data is logically structured within relations (tables).
  
  在关系模型中，所有数据的逻辑结构都是由关系（表）构成的。
- Each relation is made up of attributes (columns) of data.
  
  每个关系由数据的属性（列）组成。
- Each tuple (row) contains one value per attribute.
  
  每个元组（行）包含一个属性值。
- Terminology
  - Relation: A relation is a table with columns and rows.
    
    关系：关系是一个包含列和行的表。
  - Attribute: An attribute is a named column of a relation.
    
    属性：属性是关系中命名的列。
  - Domain: the set of allowable values for one or more attributes.
    
    域：一个或多个属性的允许值集合。
  - Tuple: A tuple is row of a relation.
    
    元组：元组是关系中的行。
  - Degree: the number of attributes in a relation.
    
    度：关系中属性的数量。
  - Cardinality: the number of tuples in a relation.
  卡性：关系中元组的数量。
  - Relational database: A collection of normalized relations with distinct relation names.
    
    关系数据库：具有不同关系名称的规范化关系集合。
Relational Algebra

Please watch the pdf 1.2

1.3

ER modeling

Entity

Basic building block of a data model.

数据模型的基本构件。
An entity has characteristics or attributes.

实体具有特征或属性。
An entity is a “thing” about which data should be stored.

实体是需要存储数据的 “事物”。
Group of objects with same properties, identified by enterprise as having an independent existence.

具有相同属性的一组对象，被企业认定为独立存在。
Can be objects with a physical or conceptual existence

可以是物理存在或概念存在的对象
- Physical existence: (e.g. student or a textbook)
  
  物理存在：（如学生或教科书）
- Conceptual existence: (e.g. a module or an exam)
  
  概念存在：（如模块或考试）
Intergrity
- Entity intergrity
  - In a base relation, no attribute of a primary key can be null.
- Domain intergrity
- Referential integrity
  - If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be null.
  如果关系中存在外键值，要么外键值必须与原关系中某个元组的候选键值相匹配，要么外键值必须为空。

Attributes

An entity has characteristics or attributes.

实体具有特征或属性
An attribute is a discrete element of data; it describes an entity.

属性是一个离散的数据元素；它描述一个实体。
Attributes shown below the entity’s name.

属性显示在实体名称下方。
Attribute names must be carefully selected so that they are self-explanatory and unique.

*必须仔细选择属性名称，使其不言自明且独一无二。 *
A identifier (primary key) uniquely identifies an instance of an entity. Attribute(s) that are identifiers are labeled in the entity (here with a star).

标识符（主键）是实体实例的唯一标识。作为标识符的属性会在实体中标注（此处用星号标注）。

Relationship

Entities are related to other entities.

实体与其他实体相关。
Relationship describes a linkage between two entities and is represented by an arc between them.

关系描述了两个实体之间的联系，用它们之间的弧线表示。

Relational keys

Candidate Key
- A set of attributes that uniquely identifies a tuple within a relation.
  
  关系中唯一标识元组的一组属性。
- Uniqueness : In each tuple, candidate key uniquely identify that tuple.
  
  唯一性：在每个元组中，候选键唯一标识该元组。
- Irreducibility: No proper subset of the candidate key has the uniqueness property.
  
  不可还原性：候选密钥的适当子集都不具有唯一性。
Primary Key
- Candidate key selected to identify tuples uniquely within relation.
  
  选定的候选键，用于在关系中唯一标识元组。
Foreign Key
- Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation.
  
  一个关系中与另一个（可能相同）关系的候选键相匹配的属性或属性集。
Composite Key
- A candidate key that consists of two or more attributes.
  
  由两个或多个属性组成的候选密钥。

Mapping 1:m relationship into relational model

Post a copy of the primary key attribute(s) of one-side entity into the relation representing the many-side, to act as a foreign key

Week 2

2.1

EER

Semantic concepts are incorporated into the original ER model and called the Enhanced Entity-Relationship (EER) model.

语义概念被纳入原始 ER 模型，称为增强实体-关系（EER）模型。

specialization/generalization

Superclass
- An entity type that includes one or more distinct subgroups of its occurrences.
  
  实体类型，包括其出现的一个或多个不同子组。
Subclass
- A distinct subgrouping of occurrences of an entity type.
  
  实体类型出现次数的独特分组。
Superclass & Subclass
- Superclass/subclass relationship is one-to-one (1: 1).
  
  超类/子类关系是一对一的（1: 1）。
- Superclass may contain overlapping or distinct subclasses.
  
  超类可能包含重叠或不同的子类。
- Not all members of a superclass need be a member of a subclass.
  
  并非所有超类成员都必须是子类成员。
Attribute Inheritance
- An entity in a subclass represents same ‘real world’ object as in superclass
  
  子类中的实体表示与超类中相同的“真实世界”对象
- May possess subclass-specific attributes, as well as those associated with the superclass.
  
  可拥有子类的特定属性以及与超类相关的属性。
Specialization
- Process of maximizing differences between members of an entity by identifying their distinguishing characteristics.
  
  通过识别实体成员的显著特征，最大限度地缩小成员之间差异的过程。
Generalization
- Process of minimizing differences between entities by identifying their common characteristics.
  
  通过确定实体的共同特征，最大限度地减少实体间差异的过程。

2.2

We will learn

How to turn a conceptual ER model into a set of relations (relational model).

How to place foreign keys (and new relations) to represent different multiplicities.

Different rules for mapping 1: 1, 1: *, *: * relationships

How to represent subclass/superclass as a set of relations.

Different rules for optional/mandatory disjoint/nondisjoint

2.3

SQL

本处只做补充，详情可见

多表查询

FROM
- 可以查询一个或多个表格
- 如果查询多表会将引用表格合成一个笛卡尔集
- 数据量大，查询慢

SELECT x1.y1, x2.y2, ..., xn.yn
FROM xx, xxx, xxxx, ..., xn

JOIN / INNER JOIN
- 通过多个表之间的外键关联来进行拼接，注意用于拼接的列需要加上索引
  
  （MySQL会默认添加）

SELECT x1.xxx1,
	   x2.xxx2,
	   x3.xxx3
FROM x1
JOIN x2
    ON x1.pk = x2.pk
JOIN x3
	ON x1.pk = x3.pk

database

Week 1

1.1

key terms

Data

Database

DBMS

Database application programme

Data model

Schema

Relation

Relational algebra

Components in the Database Environment

Roles in the Database Environment

Data Administrator (DA)

Database Administrator (DBA)

Database Designers (Logical and Physical)

**Application Developers **

End Users (naive and sophisticated)

Three level ANSI-SPARC architecture

Objective

1.2