Database-Conceptual Structure Design

Database-Conceptual Structure Design
Conceptual Structure Design

What is conceptual structure design?
Abstract The user requirements obtained from the requirement analysis into an information structure, that is, the conceptual structure design process.
The conceptual structure is the common basis of various data models. It is more independent from machines and abstract than data models, and thus more stable.
Conceptual Structure Design is the key to the whole database design
Features of Conceptual Structure Design
(1) truly and fully reflect the real world
(2) easy to understand
(3) easy to change
(4) Easy conversion to various data models such as relational, mesh, and hierarchy
Tools used to describe the conceptual model
E-R Model
The conceptual model is independent of a specific DBMS.

Methods and steps for Conceptual Structure Design

Four Methods for Conceptual Structure Design
Top-down
First, define the framework of the global conceptual structure, and then gradually refine

Bottom-up
First, define the conceptual structures of each local application, and then integrate them to obtain the global conceptual structure.

Gradual expansion
First, define the most important core concept structure, then expand outward, and gradually generate other Conceptual Structures in snowball mode until the overall conceptual structure

Hybrid Policy
Combines top-down and bottom-up to design a global conceptual structure framework with a top-down policy, use it as the skeleton to integrate the local conceptual structures designed by the bottom-up policy.

Data abstraction

Abstract is to extract common characteristics of interest from actual people, things, things, and concepts, ignore non-essential details, and precisely describe these features with various concepts.
The conceptual structure is an abstraction of the real world.
Three Common abstractions
1. Classification)
Define a certain type of concept as a group of objects in the real world
Abstracts the semantics of "is member of" between object values and types.
2. Aggregation)
Define components of a certain type
Abstracts the semantics of "is part of" between the object internal type and component.
3. Generalization)
Define a seed set connection between types
Abstracts the semantics of "is subset of" between types
Inheritance

Local View Design

Steps for designing a E-R diagram:
Select a local application
Drawing one by one design by E-R Diagram

Select an appropriate layer of data flow diagram in the multi-layer data flow diagram, as the starting point of the design of the E-R Diagram
The middle-layer data flow diagram is usually used as the basis for designing the E-R diagram.
Task
Extract the data involved in each local application from the data dictionary.
The data flow diagram is used to calibrate the attributes and codes of objects and objects in each local application.
Determine the relationship between entities and their types (, 1: n, m: n)
Two principles:
(1) attributes cannot be described. That is, the attribute must be an inseparable data item and cannot be composed of other attributes.
(2) attributes cannot be associated with other entities. The link only occurs between entities.
[Example] Design of sales management subsystem E-R

Main functions of the sales management subsystem:
Process orders sent by customers and sales personnel
The factory is produced according to the order arrangement.
Deliver the goods and issue an invoice
After receiving the payment from the customer, the customer shall handle the receivables Based on the invoice stubs and credit conditions.

Types of conflict

Attribute conflict
Two types of attribute conflict

Attribute domain conflict
Attribute Value Type
Value Range
Different Value Sets

Attribute Value Unit conflict

Name Conflict
Two types of naming conflicts

Cognominal semantics: Objects of different meanings have the same name in different local applications.

Synonym: objects of the same meaning have different names in different local applications.

Structure conflict
Three types of structure conflicts
The same object has different abstractions in different applications.
The number of attributes and attribute order of the same entity in different E-R charts are not exactly the same
The relationships between objects present different types in different local views

Redundancy

Redundant data refers to the data that can be exported from basic data.
Redundant contact refers to the contact that can be exported by other contacts

Redundant data and redundant connections can easily damage the integrity of the database, making it difficult to maintain the database.
The preliminary E-R diagram after removing unnecessary redundancy is called the basic E-R Diagram

How to eliminate redundancy

Analysis Method
Based on data dictionary and data flow diagram
Based on the logical relationship between data items in the data dictionary
Efficiency VS redundant information
Determined Based on the overall needs of users
If some redundant data is artificially retained, the description of data association in the data dictionary should be used as the Integrity Constraint.
Q4 = Σ Q5
Once Q5 is modified, the integrity check should be triggered to modify Q4.
Standardization Theory
The concept of function dependency provides a formal tool for eliminating redundant connections.
The integration process solves the following problems:
Synonymous with different names. The project and product meanings are the same.
The relationship between employees and warehouses in inventory management is included in the contact between departments and employees in labor and personnel management, so it can be canceled.
The leadership relationship between employees and the LED relationship can be exported by the leadership relationship between departments and employees (managers), and the subordination between departments and employees. Therefore, it can be canceled.

Verify the overall conceptual structure

After the view is integrated, an overall database conceptual structure is formed. Further verification is required to ensure that the overall conceptual structure meets the following conditions:
The overall conceptual structure must be consistent and there is no conflicting expression.
The overall conceptual structure can accurately reflect each original view structure, including links between attributes, entities, and entities.
The overall conceptual structure can meet all the requirements defined in the needs analysis phase

The overall conceptual structure should be finally submitted to the user for comments from the user and relevant personnel for review, modification and optimization, and then determined as the conceptual structure of the database, as the basis for further database design.
Steps for Conceptual Structure Design
Abstract data and design local views
Integrate the local view to obtain the global conceptual structure
Verify the overall conceptual structure
Design local view
Select a local application
2. design the E-R diagram one by one
Calibration of objects, attributes, codes, and relationships between objects in a local application
Use a E-R diagram to describe it
Integrate local view
1. Merge the E-R diagram to generate the preliminary E-R Diagram
Eliminate conflicts
Attribute conflict
Name Conflict
Structure conflict
2. Modification and Reconstruction
Eliminate unnecessary redundancy and design to generate basic E-R Diagrams
Analysis Method
Standardization Theory