Database and database Learning

Database and database Learning
Logical Structure Design

Task of Logical Structure Design
The basic E-R diagram designed at the conceptual structure design stage is converted into a logical structure that complies with the data model supported by the DBMS product.
Steps for Logical Structure Design
Transform the conceptual structure into general relationships, networks, and Hierarchical Models
Converts converted relationships, mesh, and hierarchical models to data models supported by a specific DBMS.
Optimize the data model

Conversion of E-R graph to Relational Model

Problems to be Solved in the transformation of E-R graph to Relational Model
How to convert the relationship between a real-world and an entity to a relational model
How to determine the attributes and codes of these link modes
Conversion content
Convert a E-R diagram to a relational model: converts the link between an object, an object's attributes, and an object to a relational model.

The relationships between physical entities are as follows:

(1) A link can be converted into an independent link mode, or merged with the link mode corresponding to any end. Convert to an independent relational mode and merge the relational modes corresponding to an object at one end

(2) A 1: n link can be converted into an independent link mode, or merged with the link mode corresponding to n. Convert to an independent relational mode and merge the relational modes corresponding to the n-end.

(3) A m: n link is converted into a link mode. For example, the "electives" contact is a m: n contact, which can be converted to the following link mode. The combination code of the student ID and course number is as follows: Optional (student ID, course number, score)

Instructor (Instructor ID, name, title)
Primary Key: Instructor ID
Course (course number, course name, instructor number, teaching material)
Primary Key: course No. Foreign key: Instructor No.
Student (student ID, name, gender, instructor ID)
Primary Key: Student ID foreign key: Instructor ID
Course Selection (student ID, course number, score)
Primary Key: (student ID, course number)
Foreign key 1: Student ID, foreign key 2: course number

(4) One multivariate link between three or more entities is converted into a relational model. For example, the "Lecture" contact is a three-element link, which can be converted to the following link mode. The course number, employee number, and book number are the link combination codes: Teach (course number, employee ID, Book Number) (5) the link mode with the same code can be merged. Objective: to reduce the number of links in the system. Merge method: add all attributes of one link mode to another link mode, remove the synonymous attributes (or different names may have the same name), and adjust the order of attributes as appropriate.

Note:
Theoretically, a link can be combined with the relational mode corresponding to any end.
However, in some cases, the efficiency of merging with different link modes is significantly different. Therefore, the combination of the link mode and the link mode depends on the specific situation of the application.
Connection operations are the most time-consuming operations, so we should generally aim to minimize connection operations.
For example, if you want to query the name of the class teacher of a class frequently, it is better to combine the management contact with the teacher relationship.

[Example] converting the E-R diagram above the dotted line in Figure 7.30 to the relational model department entity corresponding to the relational model department (Department number, Department name, Manager employee number ,...) This relationship model already contains the employee ID corresponding to the "Leader". The employee ID of the Relationship manager is the candidate code of the relationship. The employee ID corresponds to the employee entity, employee name, position ,...) The link mode already contains the link mode corresponding to the link "belongs ".

[Example] converting the E-R diagram above the dotted line in Figure 7.30 to the relational model (continued) product entity corresponding to the relational model product (product number, product name, product team lead employee number ,...) Supplier (supplier ID, name ,...) Relational Model Parts (part number, part name ,...)

[Example] the upper part of the dotted line in Figure 7.30 is converted to the Relationship Model (continued) contact "Participate" corresponding to the Relationship Model staff work (employee number, product number, working days, ...) Contact the relational model supply corresponding to "supply" (product number, supplier number, part number, supply)

Data Model Optimization

After obtaining the preliminary data model, you should modify and adjust the structure of the data model to further improve the performance of the database application system. This is the optimization of the data model.
The Optimization of the relational data model is usually guided by the Standardization Theory.

How to optimize the data model
Determine data dependency
Based on the semantics obtained in the demand analysis phase, the data dependencies between attributes in each link mode and between attributes in different link modes are respectively written.
Eliminate redundant connections
Data dependencies between various relational modes are minimized to eliminate redundant connections.
Determine the paradigm
Analyze the relationship modes one by one based on the data dependency theory
Check whether some function dependencies, transfer function dependencies, and multi-value dependencies exist.
Determine which relationship modes belong to the nth paradigm

According to the data processing requirements of various applications obtained in the requirement analysis stage, analyze whether these models are suitable for such application environments,
Determine whether to merge or break them down.
Note: the higher the degree of standardization, the better the relationship. Generally speaking, the third paradigm is enough.

For example, the following function dependency exists in the transcript (student ID, English, Mathematics, Chinese, and average score) of a student in relational mode: student ID → English student ID → mathematics student ID → Chinese student ID → average score (English, Mathematics, Chinese) → average score

Obviously there are: Student ID → (English, mathematics, and Chinese). Therefore, this relational model has the trust of the transfer function, which is a 2NF relationship. Although the average score can be calculated by other attributes, however, if the average score of students needs to be frequently queried in the application, the redundant data can still be retained to improve efficiency, and the link mode will not be further decomposed.

According to the data processing requirements of various applications obtained in the demand analysis stage, the relationship mode should be decomposed to improve data operation efficiency and storage space utilization.
Common decomposition methods
Horizontal Decomposition
Vertical Decomposition
Horizontal Decomposition
What is horizontal decomposition?
The (basic) link tuples are divided into several child sets and each child set is defined as a child relationship to improve system efficiency.
Applicability of horizontal Decomposition
Applications that meet the 80/20 Principle
Concurrent transactions often access inconsistent data
Vertical Decomposition
What is vertical decomposition?
Splits the attribute of relational mode R into several subsets to form several subrelational modes.
Applicability of vertical Decomposition
Depends on whether the total efficiency of all transactions on R is improved after decomposition.

Design User submodel

Issues that should be paid attention to when defining external user models
There are three aspects:
(1) use aliases that better suit user habits
(2) define different views for different levels of users to meet the security requirements of the system.
(3) Simplify users' use of the System

[Example] relational model products (product no., product name, specification, unit price, production workshop, production owner, product cost, product qualification rate, quality grade ), you can create two views on the product relationship: Create a view for a general Customer: Product 1 (product number, product name, specification, unit price) create a view for the product sales department: product 2 (product number, product name, specification, unit price, workshop, production director) the customer view only contains the property sales department view that allows the customer to query. Only the property production leading department that allows the sales department to query can be queried. Then, all product data can be queried to prevent unauthorized access by users and cannot be queried. data, ensure system security

Task
Convert a conceptual structure into a specific data model
Steps for Logical Structure Design
Transform the conceptual structure into general relationships, networks, and Hierarchical Models
Converts converted relationships, mesh, and hierarchical models to data models supported by a specific DBMS.
Optimize the data model
Design User submodel

Summary of Logical Structure Design

Contents of converting E-R graph to Relational Model
Principle of converting E-R graph to Relational Model

How to optimize the data model
1. Determine Data Dependencies
2. Data dependencies between various relational modes are minimized to eliminate redundant connections.
3. Determine which relationship modes belong to the nth paradigm respectively.
4. analyze whether these models are suitable for the application environment and determine whether to merge or decompose them.
5. Perform necessary decomposition or merging of the link mode
Design User submodel
1. Use aliases that better suit user habits
2. Define different external modes for different levels of users to meet the security requirements of the system.
3. Simplify your use of the System