db设计，模型

设计

        整个设计过程分为6个阶段。

        第一阶段，specifying，需求阶段，整理用户需求。

        第二阶段，designing，设计阶段，根据整理的需求出各种规范，制品，例如需求文档，页面原型，模型，设计UI图等。

        第三阶段，development，开发阶段。包含开发和测试。

        第四阶段，implementation，应该是指部署，上线。

        第五阶段，maintenance，维护，主要指产品迭代阶段，修复，完善，新功能等。

        第六阶段，retirement，废弃，开发新的替代产品。

数据库：

        Step 1. Selectone primary entity from the database requirements description and showattributes to be recorded for that entity

        

        Step2. Use structured English for entities, attributes, and keys to describe thedatabase that has been elicited

        

        Step 3. Examine attributes in the primary entity (possibly with user assistance) to find out ifinformation about one of the attributes is to be recorded

        Step 3a.If information about an attribute is needed, then make the attribute an entity,and then Define the relationship back to the original entity

        Step 3b.If the new entity depends entirely on another entity for its existence, thendraw the entity as weak (double boxed) and show the connection to theidentifying entity as a double diamond. The participation of the weak entity inthe relationship is full. Dash underline the partial key identifier(s) in theweak entity.

        

        Step 4. Ifanother entity is appropriate, draw the second entity with its attributes.Repeat step 2 to see if this entity should be further split into more entities

        Step 4a.If the additional entity or entities do not have candidate keys, then draw themas weak entities (as explained in step 3c) and show the connection to anidentifying entity. The participation of the weak entity in the relationship isfull or mandatory. Dash underline the partial key identifier(s) in the weak entity

        Step 5.Connect entities with relationships if relationships exist

        

        Step 6. State the exact nature of the relationships in structured English from all sides; forexample, if a relationship is A:B::1:M, then there is a relationship from A(1)to B(M) and from B(M) back to A(1)

        For ternary and higher-order (n-ary) relationships, state the relationship instructured English, being careful to mention all entities for the n-aryrelationship. State the structural constraints as they exist

        For specialization/generalization relationships, state the relationship instructured English, being careful to mention all entities (subclasses orspecializations). State the structural constraints as they exist

        

        Step 6a. Examinethe list of attributes and determine whether any of them need to be identifiedby two (or more) entities. If so, place the attribute on an appropriaterelationship that joins the two entities

        Step 6b.Examine the diagram for loops that might indicate redundant relationships. If arelationship is truly redundant, excise the redundant relationship

        

        Step 7.Present the “as designed”database to the user complete with the English for entities, attributes, keys,and relationships. Refine the diagram as necessary.

        

        Step 8.Show some sample data

        Step 9. Present the “as designed” database to the user complete with the English for entities,attributes, keys, and relationships. Refine the diagram as necessary.

三模型

        A data model—a collection of concepts that can be used to describe the structure of adatabase— By structure of a database we mean the data types, relationships, andconstraints that apply to the data. Most data models also include a set ofbasic operations for specifying retrievals and updates on the database.

        数据模型，数据结构，类型，关系约束，操作等与数据相关的集合

High-level or conceptual data models provide concepts that are close to the way many usersperceive data, whereas low-level or physical data models provide concepts thatdescribe the details of how data is stored on the computer storage media,typically magnetic disks. Concepts provided by physical data models aregenerally meant for computer specialists, not for end users 

        conceptual data models，概念模型，描述需求，ER图只是其中一种

        physical data models，物理模型，描述如何将数据存储在电脑的细节。只适用于电脑专家

Conceptual data models use concepts such as entities, attributes, and relationships

        概念模型，使用实体，属性，关系等概念

Most data models have certain conventions for displaying schemas as diagrams. A displayed schema is called a schema diagram A schema diagram displays only some aspects of a schema 

        逻辑模型，或称为schema图，描述schema中拥有哪些对象，例如表，视图等。

        概念模型与逻辑模型的区别：个人理解

        概念模型专注于描述用户的需求，逻辑模型专注于具体的实现

        概念模型和逻辑使用的概念不相同，在概念模型中有关系等概念，在逻辑模型中只有表的概念。需要进行映射

        概念模型与具体的数据库没有关系，只有数据库类型有关，例如一份ER图，可以在任意关系型数据库中实现。逻辑模型与数据库高度绑定，若通过Oracle实现的逻辑模型，转换为Mysql，或SqlServer，会有很多困难

The data in the database at a particular moment in time is called a database state or snapshot. Every time we insert or delete a record or change the value of a data item in a record, we change one state of the database into another state

When we define a new database, we specify its database schema only to the DBMS. At this point, the corresponding database state is the empty state with no data

        数据库的初始状态和任意的快照状态

Physical data models describe how data is stored as files in the computer by representing information such as record formats, record orderings, and access paths 

        物理模型，关注数据如何存储在电脑中。了解物理模型有助于理解索引，对SQL优化有很大的帮助

映射

3.1 entity

        For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E. Include only the simple component attributes of a composite attribute. Choose one of the key attributes of E as the primary key for R. If the chosen key of E is a composite, then the set of simple attributes that form it will together form the primary key of R.

        对于强实体，创建relation R，包含它的所有属性，并设置key

        For each weak entity type W in the ER schema with owner entity type E, create a relation R and include all simple attributes (or simple components of composite attributes) of W as attributes of R. In addition, include as foreign key attributes of R, the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s); this takes care of mapping the identifying relationship type of W. The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any. If there is a weak entity type E2 whose owner is also a weak entity type E1, then E1 should be mapped before E2 to determine its primary key first。 

        对于弱实体，首先建立强实体，然后将二者关联起来。具体取决于强实体于与弱实体的关系。1对1，1对M都使用主外键

        if a weak entity owns other weak entities, then the weak entity that is connected to the strong entity must be mapped first. The key of the weak owner entity has to be defined before the “weaker” entity (the one furthest from the strong entity) can be mapped

        若存在弱实体链，必须依照先后顺序进行映射，强实体是第一个映射的。

3.2 relationship

        choose one of the relations—S, say—and include as a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S

         主外键，适用于total participation，1对1或1对M类型。当是partial participation时，优先选择null值较少的一方

        Mapping binary 1:N relationships when the N side has partial participation. This situation would be handled just like a binary M:N relationship with a separate table for the relationship. The key of the new relation would consist of a concatenation of the keys of the related entities. Include any attributes that were on the relationship on this new table

        R，关系1：N，其中N为部分参与，创建单独的表，key为实体key的组合

        Mapping 1:N recursive relationships. Re-include the primary key of the table with the recursive relationship in the same table, giving it some other role name 

        R，关系1：N，自循环。在相同表中创建一列，列名添加角色名称，列值为主键值

        An alternative mapping of a 1:1 relationship type is to merge the two entity types and the relationship into a single relation. This is possible when both participations are total, as this would indicate that the two tables will have the exact same number of tuples at all times 

        关系表R，适用于1对1或1对M关系，关系本身存在属性。例如员工与项目的关系，工作时长，开始时间，结束时间等

        The third option is to set up a third relation R for the purpose of cross-referencing the primary keys of the two relations S and T representing the entity types. As we will see, this approach is required for binary M:N relationships. The relation R is called a relationship relation (or sometimes a lookup table), because each tuple in R represents a relationship instance that relates one tuple from S with one tuple from T. The relation R will include the primary key attributes of S and T as foreign keys to S and T. The primary key of R will be one of the two foreign keys, and the other foreign key will be a unique key of R 

        关系表R，适用于M对N关系，包含二者的主键。关系表R与两张主表建立主外键

        We use the relationship relation option. For each n-ary relationship type R, where n > 2, create a new relationship relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S. The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E′corresponding to E 

        当N个实体之间存在关系时，建立关系表R，包含N个实体，它的主键，以及R自身的属性。当N个实体存在1对1关系时，关系表的联合主键不应该包含该实体主键

        例如，学生，教师，课程。由于教师与课程之间是1对1关系，关系表R的联合主键为学生ID，课程ID

3.3 attribute

        Mapping atomic attributes. For entities with atomic attributes, map the entities to a table and form columns for each atomic attribute.

        对于原子型属性，创建表，每个原子属性对应其中的一列。

        Mapping composite attributes. For entities with composite attributes, map entities to a table and form columns of each elementary (atomic) part of the composite attributes. 

        对于复合属性，将其拆分为原子属性。

        For each multivalued attribute A, create a new relation R. This relation R will include an attribute corresponding to A, plus the primary key attribute K—as a foreign key in R—of the relation that represents the entity type or relationship type that has A as a multivalued attribute. The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components 

        为多值属性，单独建立表R，R中包含主表的key。表R中存在N条记录，每一条对应一种值

3.4 subclass & superclass

        四种方案：

        第一种,Multiple relations—superclass and subclasses

        为父类和子类单独建表，子类的表中包含父表的主键和独有属性

        

        第二种,Multiple relations—subclass relations only

        为每种子类单独建表，要求子类与父类关系为total和disjoint。

        total：表示所有子类总和与父类数量相同

        disjoint：不存在一条实例，属于多个子类。

   

        第三种,single relation with one type attribute

        创建单表R，属性用于区分子类的类型。

       

        第四种,Single relation with multiple type attributes.

        创建单表R，为每种类型单独创建布尔属性，标识该实例是否属于子类

        例如，用户isVip，isStudent，isTeacher，isSpecial。是否vip，是否学生，是否教师，是否特殊人士，残疾人士，孤独老人等

3.5 category

        For mapping a category whose defining super classes have different keys, it is customary to specify a new key attribute, called a surrogate key, when creating a relation to correspond to the union type. It is also recommended to add a type attribute to the OWNER relation to indicate the particular entity type to which each tuple belongs

        创建表R，表示category，其中主键来源于子类表中，添加额外标识列，标识子类的类型

Mapping categories or union types when super classes have the same primary keys. Create a new relation for the subclass (or union type) and include the primary key of the superclass (or super classes) in the subclass (or union type) as the primary key. Include the other attributes ofthe subclass in this relation. Create separate relations for each of the othersuper classes and map them as you would map regular entities. 

        为每个子类创建单独的表，但与父类拥有相同的主键值