Database-Logical Structure design

Logical Structure Design

The task of logical structure design
The basic E-R diagram designed in conceptual structure design phase is converted into a logical structure conforming to the data model supported by the DBMS product.
The steps of logical structure design
Transform the conceptual structure into a general relational, mesh, and hierarchical model
Transform a transformed relationship, mesh, and hierarchy model into a data model supported by a specific DBMS
Optimize your data model

Transformation of E-r graph to relational model

The problems to be solved in the transformation of E-r graph to relational model
How to convert the relationship between entity and entity to relational mode
How to determine the properties and codes of these relational patterns
Convert content
Convert a e-r diagram to a relational model: Converts the relationships between entities, attributes of entities, and entities into relational mode.

The relationship between the entity type has the following different situations:

(1)一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。转换为一个独立的关系模式 与某一端实体对应的关系模式合并

(2)一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。 转换为一个独立的关系模式与n端对应的关系模式合并

(3) 一个m:n联系转换为一个关系模式。    例，“选修”联系是一个m:n联系，可以将它转换为如下关系模式，其中学号与课程号为关系的组合码：　　选修（学号，课程号，成绩）

Teacher (teacher number, name, title)
Primary key: Teacher number
Course (Course number, course name, teacher number, textbook)
Primary key: Course extra key: Teacher number
Student (school number, name, gender, teacher number)
Primary key: Learn extra key: Teacher number
Elective Course (School number, course number, score)
Primary key: (school number, course number)
FOREIGN Key 1: School number, foreign key 2: Course number

(4)三个或三个以上实体间的一个多元联系转换为一个关系模式。    例，“讲授”联系是一个三元联系，可以将它转换为如下关系模式，其中课程号、职工号和书号为关系的组合码：　　讲授（课程号，职工号，书号）(5)具有相同码的关系模式可合并目的：减少系统中的关系个数合并方法：将其中一个关系模式的全部属性加入到另一个关系模式中，然后去掉其中的同义属性（可能同名也可能不同名），并适当调整属性的次序

Attention:
Theoretically, 1:1 linkage can be combined with a relational pattern that corresponds to either end
In some cases, however, the efficiency of merging with different relational models can be significant. Therefore, it depends on the specific circumstances of the application that the relationship pattern should be merged with the end.
Because the connection operation is the most time-consuming operation, you should generally aim to minimize the connection operation.
For example, if you frequently want to query the name of a class's head teacher, it is better to merge the management links with the teacher relationship.

[例] 把图7.30中虚线上部的E-R图转换为关系模型 部门实体对应的关系模式     部门（部门号，部门名，经理的职工号，…） 此关系模式已包含了联系“领导”所对应的关系模式 经理的职工号是关系的候选码 职工实体对应的关系模式     

[例] 把图7.30中虚线上部的E-R图转换为关系模型（续） 产品实体对应的关系模式     产品（产品号，产品名，产品组长的职工号，…）供应商实体对应的关系模式     供应商（供应商号，姓名，…） 零件实体对应的关系模式     

[例] 把图7.30中虚线上部的E-R图转换为关系模型（续） 联系“参加”所对应的关系模式     职工工作（职工号，产品号，工作天数，…） 联系“供应”所对应的关系模式    

Optimization of the data model

After obtaining the preliminary data model, the structure of the data model should be modified and adjusted to further improve the performance of the database application system, which is the optimization of the data model.
Optimization of relational data model is usually guided by the theory of normalization

Ways to optimize your data model
Determine data dependencies
According to the semantics of the requirement analysis stage, the data dependencies between each attribute in each relational pattern and the data dependence between different relational pattern attributes are written separately.
Eliminate redundant connections
Eliminate redundant connections by minimizing data dependencies between relational schemas.
Identify the paradigm of ownership
Analyze the relationship pattern by the theory of data dependence
Examine whether there are partial function dependencies, transitive function dependencies, multivalued dependencies, etc.
Determine the model of each relationship in the first paradigm

According to the requirements of various applications in the requirements analysis phase, the paper analyzes the suitability of these models for such application environment,
Determine whether you want to merge or explode them.
Note: The higher the degree of normalization is not the better, generally speaking, the third paradigm is sufficient

例：在关系模式           学生成绩单(学号,英语,数学,语文,平均成绩)            中存在下列函数依赖：　           学号→英语　           学号→数学　           学号→语文　           学号→平均成绩            (英语, 数学, 语文)→平均成绩

  显然有：          学号→(英语,数学,语文)因此该关系模式中存在传递函数信赖，是2NF关系   虽然平均成绩可以由其他属性推算出来，但如果应用中需要经常查询学生的平均成绩，为提高效率，仍然可保留该冗余数据，对关系模式不再做进一步分解

According to the requirements of various applications in the requirement analysis stage, the relational pattern is decomposed to improve the efficiency of data operation and the utilization of storage space.
Common decomposition methods
Horizontal decomposition
Vertical decomposition
Horizontal decomposition
What is horizontal decomposition
The tuple of the (basic) relationship is divided into sub-sets, and each subset is defined as a sub-relationship to improve the efficiency of the system.
Scope of application of horizontal decomposition
Meet the "80/20 principle" application
Concurrent transactions frequently access disjoint data
Vertical decomposition
What is vertical decomposition
Decomposing the properties of the relationship mode R into several sub-sets to form several sub-relational patterns
The applicable range of vertical decomposition
Depends on whether the total efficiency of all the transactions on R has been improved after decomposition

Design User sub-mode

What you should focus on when defining an out-of-user model
Includes three areas:
(1) Use aliases that are more user-compliant
(2) Define different view for different levels of users to meet the security requirements of the system.
(3) Simplify the user's use of the system

[例] 关系模式产品（产品号，产品名，规格，单价，生产车间，生产负责人，产品成本，产品合格率，质量等级），可以在产品关系上建立两个视图：        为一般顾客建立视图：            产品1（产品号，产品名，规格，单价）        为产品销售部门建立视图：            产品2（产品号，产品名，规格，单价，车间，生产负责人）顾客视图中只包含允许顾客查询的属性销售部门视图中只包含允许销售部门查询的属性生产领导部门则可以查询全部产品数据可以防止用户非法访问不允许他们查询的数据，保证系统的安全性

Task
Translating a conceptual structure into a specific data model
The steps of logical structure design
Transform the conceptual structure into a general relational, mesh, and hierarchical model
Transform a transformed relationship, mesh, and hierarchy model into a data model supported by a specific DBMS
Optimize your data model
Design User sub-mode

Summary of Logical Structure design

The transformation content of E-r graph to relational model
The transformation principle of e-r graph to relational model

Ways to optimize your data model
1. Determine Data dependencies
2. Eliminate redundant connections by minimizing the data dependencies between the various relational schemas.
3. The modalities for determining the relationship are in the first paradigm.
4. Analyze whether these patterns are appropriate for the application environment and determine whether they are to be merged or decomposed.
5. The necessary decomposition or merging of the relational model
Design User sub-mode
1. Use aliases that are more user-compliant
2. Define different external modes for different levels of users to meet the security requirements of the system.
3. Simplify the user's use of the system

Database-Logical Structure design