Introduction
- A schema is the definition of a database. It defines the meaning of data.(schema就是数据库对象的集合)
- An instance of a database is the collection of data in the database at a particular point of time (snap-shot).
- DBMS – Database Management System.A DBMS is a collection of software programs to enable users to create, maintain and utilize a database.
The advantage of database isn not mentioned here.
ER Diagram
Entity-Relationship (ER) model describes data to be stored and the constraints over the data, views the real world as a collection of entities and relationships among entities.
Entity
An entity is an object in the real world that is distinguishable from other objects
An entity is described using a set of attributes whose values are used to distinguish one entity from another of same type
Different attribute types
- Simple attribute, which contains a single value
- Composite attribute， which contains several components. For example, address entity contains street,city,country
- Multi-valued attribute, which contains more than one value, representing as double ovals
- Derived attribute , computed from other attributes,representing one dashed oval. For example, age can be computed from data of birth and the current date.
Key attributes
Keys
A set of attributes that can uniquely identity an entity, representing underline.
Composite Key
Two or more attributes are used to serve as a key. For example, Name and Address together can uniquely identity an employee.
candidate key (候补键)
A minimal set of attributes that uniquely identifies an entity is called a candidate key.
If there are many candidatekeys, we should choose one candidate key asthe primary key.
Sometimes, artificial keys can be created. if there is no HKID stored in Employee, we can create a new attribute called “EmpID”
Relationship
- A relationship is an association among several entities.
- The degree refers to the number of entity sets that participate in a relationship set.
- Relationship sets that involve two entity sets are binary (or degree two).
Binary relation is not mentioned here.
Recursive Relation
- Entity sets of a relationship need not be distinct
- Sometimes, a relationship might involve two entities in the same entity set
有递归关系的模型，比如员工表，每个员工假设有个id栏位，都有一个直属上司，假设叫做manager_id，而这个上司其实也是员工表里的一员，所以manager_id会递归引用该员工表的id栏位。
Constrains
The model describes data to be stored and the constraints over the data
the mapping of a binary relationship can be classifiled into: 1-to-1, 1-to-many,Many-to-1,Many-to-Many
Participation Constraint （参与约束）
- Total
Each entity in the entity set must be associated in at least one relationship - Partial
Each entity in the entity set may (or may not) be associated in a relationship
Weak Entity / Strong Entity
Strong Entity
- An entity can be uniquely identified by some attributes related to this entity
- E.g., Employee has an attribute EmpID (which can be used to uniquely identify each employee)
Weak Entity
An entity cannot be uniquely identified by all attributes related to this entity
explain: 有些实体集的所有属性都不足以形成主码，这样的实体集称为弱实体集。与此相对，其属性可以形成主码的实体集称为强实体集（在现实世界中，有些实体对一另一些实体有很强的依赖关系，即一个实体的存在必须以另一实体的存在为前提。前者就称为"弱实体"，如在人事管理系统中，职工家属的信息就是以职工的存在为前提的，家属实体是弱实体，子女与职工的联系是一种依赖联系。在ER图中用双线框表示弱实体。又如，学生家长是一种弱实体，因为只有
学生实体存在，家长实体才会存在。用箭头表示依赖联系
A dependent can only be identified by considering some of its attributes in conjunction with the primary key of employee (identifying entity set)
The set of attributes that uniquely identify a weak entity for a given owner entity is called a discriminator or partial key （和Strong Entity 的主键一起形成的key的属性）
Definition: If a weak entity set W is dependent on a strong entity set E, we say that E owns W.
Class Hierarchy
Attributes are inherited by the entity set in the subclass.
two way:
- A class is specialized into subclasses.
- The subclasses are generalized by a superclass.
two kinds of constraints:
- Overlap constraints. (两个子类有父类重叠的属性)
- Covering constraints （子类包含父类所有的类型）
Non-Binary relationship
Ternary Relationship: a relationship involving 3 entities
In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set.
Relational Model
advantage: simplicity, elegance and mathematical foundations
schema表示表头
Foreign Key
A foreign key is a set of attributes in one relation r that is used to refer to a tuple in another relation s (it must correspond to the primary key of the second relation) 即其他类中的主键
ER-to-Relational Mapping
Step1（Strong Entity）
For each strong entity set E in the ER schema,
- create a relation schema R that includes all the attributes of E.
- choose one set of key attributes of E as a primary key for R.
最后得出schme，
Step 2 (Weak Entity)
For each weak entity set W in the ER model,
- create a relation schema R, and include all attributes.
- In addition, include the primary key(s) of the owner(s).
- The primary key of R is the combination of the primary key(s) of the owner(s) and the discriminator of the weak entity set W.
实际上就是加入外键
如果属性名和主类相同，需要修改名字
Step 3 (1-to-1 Relationship)
For each binary one-to-one (1:1) relationship set R，T–S
choice 1
include the relationship into one schema
- Choose one of the 2 relation schemas, say S,get primary key of T, and include it as foreign keys in S.
- Better if S has total participation in R
- Include the attributes of the relationship set R as attributes of S.
- Advantage:
The total number of relations remain unchanged - Disadvantage:
It may store NULL values if there is no total participation
choice 2
create a new relation
Step 4 (1-to-many or many-to-1 Relationship)
For each binary one-to-many relationship set
T ---- S
Choice 1:
include the relationship into one schema
- Include the primary key of entity set T as foreign key in relation schema of S.
- Include any attributes of the one-to-many relationship set as attributes of S.
Choice 2:
create a new relation
Step5 (Many-to-many Relationship)
Choice 1: include the relationship into one schema
Include the primary key of T into the attribute of relation schema S.
their combination will form the primary key of relation schema.
Also include attributes of the many-to-many relationship set as attributes of relation schema.
Choice 2:
create a new relation
Step 6 (Non-binary Relationship)
For each non-binary relationship set R
- create a new relation schema S to represent R.
- Include the primary keys of the participating entity sets as foreign key attributes in S.
- Also include any attributes of the non-binary relationship set as attributes of S.