Database Design Methods, specifications and skills (1) I. Database Design Process Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements. Requirement analysis stage in Database Design
Database Design Methods, specifications and skills (1) I. Database Design Process Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements. Requirement analysis stage in Database Design
Database Design Methods, specifications and skills (1)
I. Database Design Process
Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements.
In the demand analysis phase of database design, the application requirements of various users are integrated (real-world requirements ), in the conceptual design stage, a conceptual model (information world model) independent of the machine characteristics and various DBMS products is formed, which is described by E-R diagram. In the logic design stage, the E-R diagram is converted into the data model supported by the specific database product, such as the relational model, to form the database logic model. Then, based on the user's processing requirements and security considerations, the necessary VIEW is created on the basis of the basic table to form an external data mode. In the physical design phase, you can arrange physical storage based on the characteristics and processing needs of the DBMS, design indexes, and form the internal database mode.
1. Demand analysis stage
Requirement collection and analysis: the data requirement described by the data dictionary is obtained (and the processing requirement described by the data flow diagram ).
Requirement Analysis focuses on investigating, collecting, and analyzing users' information requirements, processing requirements, and security and integrity requirements in data management.
Requirement Analysis Methods: Investigate the organization and organization, investigate the business activities of each department, assist users in clarifying various requirements for the new system, and determine the boundaries of the new system.
Common survey methods include: follow-up operations, opening survey meetings, inviting special personnel to introduce, ask, and designing surveys. Users are requested to fill in and check records.
The methods for analyzing and expressing user requirements mainly include top-down and bottom-up methods. The top-down Structured Analysis method (SA) starts with the upper-level system organization and analyzes the system layer by layer, the data flow diagram and data dictionary are used to describe each layer.
The data flow chart shows the relationship between the data and the processing process. The Data in the system is described using the Data Dictionary (DD.
A data dictionary is a collection of various data descriptions. It describes the data in a database, that is, metadata, rather than the data itself. A data dictionary consists of data items, data structures, data streams, data storage, and processing processes (at least the Data Type of each field and the Primary and Foreign keys in each table should be included ).
Data item description = {data item name, description, alias, data type, length,
Value range, value meaning, and logical relationship with other data items}
Data Structure Description = {Data Structure name, description, composition: {data item or data structure }}
Data Stream description = {data stream name, description, data stream source, data stream destination,
Composition: {data structure}, average traffic, peak traffic}
Data storage description = {data storage name, description, number, inbound data stream, outbound data stream,
Composition: {data structure}, data volume, access method}
Process description = {process name, description, input: {data stream}, output: {data stream },
Handling: {brief description }}
2. Conceptual Structure Design Stage
A conceptual model independent of a specific DBMS is formed through the synthesis, induction and abstraction of user requirements, which can be expressed by a E-R diagram.
The conceptual model is used for modeling the information world. The conceptual model does not depend on the data model supported by a DBMS. A conceptual model can be converted to a specific data model supported by a DBMS on a computer.
Concept model features:
(1) Strong semantic expression ability, which can easily and directly express various semantic knowledge in applications.
(2) It should be simple, clear, and easy to understand. It is a language for communication between users and database designers.
A common method for conceptual model design is the IDEF1X method, which applies the entity-contact method to a Semantic Modeling Technology in the semantic data model and is used to establish a system information model.
The steps to create a E-R model using the IDEF1X method are as follows:
2.1 Step 1 -- initialize the project
In this phase, the task starts from the description of the purpose and scope, determines the modeling target, develops the modeling plan, organizes the modeling team, collects the source material, and develops constraints and specifications. Collecting source materials is the focus of this phase. Through investigation and observation results, business processes, input and output of the original system, various reports, and collection of raw data, a basic data table is formed.
2.2 Step 1 -- Define an object
Entity integrators have a common set of features and attributes that can identify most entities directly or indirectly from the collected source material-basic data tables. Mark the entities represented by the terms in the source material name table that indicate things and terms that end with "code", such as customer code, agent code, and product code, in this way, potential entities are preliminarily identified to form a preliminary entity table.
2.3 Step 2 -- Define contact
Only binary connections are allowed in the IDEF1X model. n-element connections must be defined as n binary connections. Based on actual business requirements and rules, the entity contact matrix is used to identify the binary relationship between entities. Then, the potential, Link name, and description of the connection relationship are determined based on the actual situation, and the link type is determined, whether it is an identity link, a non-identity Link (mandatory or optional), a non-deterministic link, or a classification link. If each instance of the Child body needs to be identified by the relationship with the parent object, the relationship is identified, otherwise it is not identified. In a non-identity relationship, if the instance of each child entity is associated with only one parent entity, it is mandatory; otherwise, it is not mandatory. If the parent entity and the Child entity represent the same real object, they are classification relationships.
Step 3-definition code
Remove the non-deterministic relationship generated in the previous stage by introducing the cross entity, and then identify the Hou code attribute from the non-cross entity and the independent entity to uniquely identify the instance of each entity, then confirm the master code from the candidate code. To determine the validity of the master code and link, the non-empty rules and non-multi-value rules are used to ensure that an attribute of an object instance cannot be null, you cannot have more than one value at the same time. Identify the identified relationship by mistake, break down the entity, and finally construct the key base view of the IDEF1X model (KB chart ).
2.5 Step 4 -- Define attributes
Extract descriptive terms from the source data table to develop an Attribute Table and determine the attribute owner. Defines non-primary Code attributes and checks non-null and non-multi-value rules of attributes. In addition, check fully dependent function rules and non-pass dependency rules to ensure that a non-primary code attribute must depend on the primary code, the entire primary code, and only the primary code. In this way, the full attribute view of the improved IDEF1X model, which is at least in line with the third paradigm of the relational theory, is obtained.
Step 5: Define other objects and rules
Defines the data type, length, precision, non-null, default value, and constraint rules of an attribute. Defines trigger, stored procedure, view, role, synonym, sequence, and other object information.
3. Logical Structure Design Stage
Converts a conceptual structure to a data model (such as a relational model) supported by a DBMS and optimizes it. To design the logical structure, select the data model that best suits to describe and express the corresponding conceptual structure, and then select the most appropriate DBMS.
To convert a E-R diagram to a relational model is to convert the relationship between the object, the object attributes, and the object into a relational model. This conversion generally follows the following principles:
1) A real shape is converted into a relational model. An object attribute is a link attribute. The entity code is the link code.
2) One m: n contact is converted into a relational mode. The codes of the entities connected to the contact and the attributes of the contact are converted to the Link Attributes. The link code is a combination of Entity Codes.
3) A 1: n link can be converted into an independent link mode, or merged with the link mode corresponding to n. If it is converted to an independent link mode, the codes of the entities connected to the contact and the attributes of the contact are converted to the link attributes, the link code is the entity code of n.
4) A link can be converted into an independent link mode, or merged with the link mode corresponding to any end.
5) One multivariate link between three or more entities is converted into a relational model. The codes and attributes of each object connected to the multivariate link are converted to the link attributes. The link code is a combination of Entity Codes.
6) links between entities in the same entity set, that is, self-links, can also be handled in three situations:, 1: n, and m: n.
7) The link mode with the same code can be merged.
In order to further improve the performance of the database application system, the data model structure should be modified and adjusted appropriately under the guidance of standardization theory. This is the optimization of the data model. Determine the data dependency. Eliminate redundant connections. Determine which relationship modes belong to the nth paradigm. Determine whether to merge or break them down. In general, the relationship is divided into 3NF criteria, namely:
Each value in the table can be expressed only once.
?? Each row in the table should be uniquely identified (with a unique key ).
The table should not store non-key information that depends on other keys.
4. Physical database design phase
Select the physical structure (including the storage structure and access method) that best suits the application environment for the Logical Data Model ). Arranges physical storage and designs indexes based on DBMS features and processing needs to form the internal database mode.
5. database implementation stage
Use the Data Language (such as SQL) provided by DBMS and its host language (such as C) to establish a database based on the logic design and physical design results, compile and debug applications, and organize the data warehouse receiving, and run the test. Database implementation mainly includes the following tasks: defining the database structure with DDL, organizing data warehouse receiving, compiling and debugging applications, and database trial run
6. Database Operation and Maintenance
The database application system can be put into operation after trial run. During the operation of the database system, it must be constantly evaluated, adjusted, and modified. Including: Database dumping and recovery, database security, integrity control, database performance monitoring, analysis and improvement, database re-organization and re-construction.
Use of modeling tools
To speed up database design, there are currently many database auxiliary tools (CASE tools), such as Rational Rose, Erwin and Bpwin of CA, sybase PowerDesigner and Oracle Designer.
ERwin is mainly used to establish conceptual and physical models of databases. It can describe the attributes of objects, links, and objects in a graphical manner. ERwin supports the IDEF1X method. By using the ERwin modeling tool to automatically generate, change, and analyze the IDEF1X model, we can not only obtain excellent business functions and data requirement models, but also transform the IDEF1X model to the physical design of the database. The model drawn by ERwin tool corresponds to two types: Logical Model and physical model. In the logical model, the IDEF1X toolbox can be used to easily create and draw object links and attributes in a graphical manner. In the physical model, ERwin can define the corresponding tables and columns and automatically convert them to appropriate types for various database management systems.
Designers can select appropriate database design modeling tools as needed. For example, after the requirement analysis is complete, the designer can use Erwin to draw an erdiagram, convert the erdiagram into a relational data model, generate a database structure, draw a data flow diagram, and generate an application.
2. Database Design Skills
1. Before designing the database (demand analysis stage)
1) understand customer requirements and ask users how to view future demand changes. Ask the customer to explain their needs, and as the development continues, ask the customer frequently to ensure that their needs are still under development.
2) understanding enterprise business can save a lot of time in the future development phase.
3) Pay attention to input and output.
When defining database tables and field requirements (input), Check Existing or designed reports, queries, and views (output) to determine which tables and fields are necessary to support these outputs.
For example, if you want a report to be sorted, segmented, and summed by zip code, make sure that it contains a separate zip code field instead of mixing the zip code into the address field.
4) create a data dictionary and ER chart
ER charts and data dictionaries allow anyone who knows the database to clearly learn how to obtain data from the database. The erdiagram is useful for displaying the relationship between tables, while the data dictionary describes the purpose of each field and any possible aliases. It is absolutely necessary to document SQL expressions.
5) define standard object naming rules
The names of various objects in the database must be standardized.
2. Table and field design (Database Logic Design)
Table Design Principles
1) standardization and standardization
Data Standardization helps eliminate data redundancy in databases. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to have achieved the best balance in terms of performance, scalability and data integrity. To put it simply, the table design principle for databases that comply with the 3NF standard is: "One Fact in One Place", that is, a table only includes its basic attributes, when they are not their own properties, they need to be decomposed. The relationships between tables are connected by foreign keys. It has the following features: a group of tables specifically store the associated data connected by keys.
For example, a 3NF database storing customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the Customer associated with the Order, but stores a key value. The key points to the row in the Customer table that contains the Customer information.
In fact, for the sake of efficiency, it is sometimes necessary not to standardize the table.
2) data-driven
Using data-driven, not hard-coded methods, many policy changes and maintenance are much easier, greatly enhancing system flexibility and scalability.
For example, if you want to access external data sources (files, XML documents, and other databases) on the user interface, you may wish to store the connection and path information in the user interface support table. In addition, if you execute tasks such as workflows on the user interface (such as sending emails, printing letterhead, and modifying record status), the workflow data can also be stored in the database. Role permission management can also be completed through data-driven. In fact, if the process is data-driven, you can push a considerable amount of responsibility to users to maintain their own workflow processes.
3) consider various changes
When designing a database, consider which data fields may change in the future.
For example, the last name is the same as the last name of a Western person, for example, the last name of a female after marriage ). Therefore, when a system is created to store customer information, the last name field is stored in a separate data table, and fields such as the start date and end date are appended, in this way, we can track the changes of this data entry.