database
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
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 |
-
JOIN / INNER JOIN
-
通过多个表之间的外键关联来进行拼接,注意用于拼接的列需要加上索引
(MySQL会默认添加)
-
SELECT x1.xxx1, |