Database design, database design tools

Database design, database design tools
Database Design ER Model

Entity-Relationship, Entity-link model. The graphical representation of this model is an erdiagram.

1. Use a rectangle to represent an object.

2. Use an ellipse to represent object attributes.

3. Use sporadic representation to indicate the Health Check relationship.

Example 1: A simple database system contains classes, students, and courses.

One class has several students, and one student can only belong to one class.

A student can take multiple courses.

Convert an erimage to a relational data model

1. entity Conversion
An object is converted into a relational model. The attributes of an object are the attributes of the link mode. The primary keys are similar.
2. Contact Conversion
(1) the relationship between two entities can be converted into an independent relationship mode, or merged with the Relationship Mode at any end.
(2) The 1: N relationship between two entities can be converted into an independent relational mode, or merged with the relational mode corresponding to N.
(3) The M: N relationship between two entities must generate a new link mode for the relationship. This mode contains at least two primary keys of the entity. If the contact has an attribute, you must add it to the new link mode.

Relational algebra

Traditional set operations: parallel, intersection, population, difference, Cartesian product.

Special relational operations: projection (vertical division), selection (horizontal division), join (link combination), Division (Inverse Calculation of Cartesian Product ).

Cartesian Product: set the number of attributes of elements R and S to r and s, respectively. R * S = {<A, B> | A belongs to R, B belongs to S }.

Left and right connections and internal connections

The left Outer Join is based on the table on the left. In general, first display all the tables on the left, and then splice the Qualified Data items in the right table.

The right outer join is based on the right table.

Only matching data items are displayed.

Example:
[TEST1 @ orcl #16-12-11] SQL> select * from t1;

ID NAME
------------------------------
1 aaa
2 bbb

[TEST1 @ orcl #16-12-11] SQL> select * from t2;

ID AGE
--------------------
1 20
3 30

Left Outer Join:
[TEST1 @ orcl #16-12-11] SQL> select * from t1 left join t2 on t1.id = t2.id;

ID NAME ID AGE
--------------------------------------------------

1 aaa 1 20

2 bbb

Outer right connection:
[TEST1 @ orcl #16-12-11] SQL> select * from t1 right join t2 on t1.id = t2.id;

ID NAME ID AGE
--------------------------------------------------
1 aaa 1 20
3 30


Internal Connection:
[TEST1 @ orcl #16-12-11] SQL> select * from t1 inner join t2 on t1.id = t2.id;

ID NAME ID AGE
--------------------------------------------------
1 aaa 1 20

The canonicalized function dependency of the link.

X and Y are two attributes in the relational R. If Y can be uniquely determined by X, Y depends on X. Record as X-> Y.

Unique index of the index type: there cannot be two rows with the same record.
Non-unique index: Compared with the above concepts.

Single-Column index: an index is created based on a single column.
Composite Index: The index is created based on multiple columns.

Clustered index: an index that performs physical sorting on a table. For example, the Chinese Dictionary is clustered and indexed in alphabetical order.
Non-clustered index: an index that does not sort tables physically. For example, the index of the radicals in the Chinese dictionary. The database can have one clustered index and multiple clustered indexes.

Database Design Steps? Solution

Database design steps: 1 Planning 2 requirement analysis 3 Concept Design 4 Logic Structure Design 5 database Physical Design 6 database implementation 7 Database Operation and Maintenance
Step 1: plan. The main task in the planning phase is to analyze the necessity and feasibility of establishing a database. For example, the system survey (that is, a comprehensive investigation of the enterprise, drawing an organizational hierarchy diagram, taking the organizational structure of the enterprise), feasibility analysis, determining the overall goal of DBS (Database System) and developing a project development plan.
Step 2: Demand Analysis. In the demand analysis phase, a comprehensive and detailed investigation should be conducted on the entire application of the system to determine the objectives of the enterprise organization, collect basic data that supports the overall design objectives of the system and requirements for such data, determine user requirements and write these requirements as requirement analysis reports acceptable to both the user and the Database Designer. In this phase, we only need to analyze user activities and generate business flowcharts; determine the scope of the system to generate a general picture; analyze the data involved in user activities, generate data flowcharts; and analyze system data, generate a data dictionary.
Step 3: conceptual design. The objective of conceptual design is to generate a database conceptual structure that reflects the information requirements of enterprise organizations, that is, to design a conceptual model independent of computer hardware and DBMS (Database Management System. E-R model is the main design tool.
Step 4: design the logical structure. The objective is to convert the global E-R pattern designed at the conceptual design stage into a logical structure (including the Database pattern and external pattern) that conforms to the data model supported by the DBMS on the selected machine ).
Step 5: physical design of the database. Select a physical structure that is most suitable for the application environment for the given data model. The physical structure of a database mainly refers to the storage record format, storage record arrangement, and access method of a database. It relies entirely on the given hardware environment hull database product.
Step 6: implement the database. There are three main tasks in this phase: 1. Establish the actual database structure 2. Load the test data to debug the application 3. Load the actual data and enter the trial run status.
Step 7: Run and maintain the database. The formal operation of the database system marks the end of the database design and application development and the start of the maintenance phase. This phase has four tasks: 1. Maintain database security and integrity 2. Monitor and improve database operation performance 3. Expand existing database functions as required 4. promptly correct system errors found during operation.
In fact, the bread contains other knowledge, I did not list, such as E-R graph rendering method, SQL, there will be involved in keywords, foreign keys, views, tables, modes, etc, if you want to learn this by yourself, I would like to recommend a book "database Principles and Applications". This is the teaching material I used to learn about databases. If you have any questions, please hi me. I will do my best to answer you.

Database Design Steps? Solution

Database design steps: 1 Planning 2 requirement analysis 3 Concept Design 4 Logic Structure Design 5 database Physical Design 6 database implementation 7 Database Operation and Maintenance
Step 1: plan. The main task in the planning phase is to analyze the necessity and feasibility of establishing a database. For example, the system survey (that is, a comprehensive investigation of the enterprise, drawing an organizational hierarchy diagram, taking the organizational structure of the enterprise), feasibility analysis, determining the overall goal of DBS (Database System) and developing a project development plan.
Step 2: Demand Analysis. In the demand analysis phase, a comprehensive and detailed investigation should be conducted on the entire application of the system to determine the objectives of the enterprise organization, collect basic data that supports the overall design objectives of the system and requirements for such data, determine user requirements and write these requirements as requirement analysis reports acceptable to both the user and the Database Designer. In this phase, we only need to analyze user activities and generate business flowcharts; determine the scope of the system to generate a general picture; analyze the data involved in user activities, generate data flowcharts; and analyze system data, generate a data dictionary.
Step 3: conceptual design. The objective of conceptual design is to generate a database conceptual structure that reflects the information requirements of enterprise organizations, that is, to design a conceptual model independent of computer hardware and DBMS (Database Management System. E-R model is the main design tool.
Step 4: design the logical structure. The objective is to convert the global E-R pattern designed at the conceptual design stage into a logical structure (including the Database pattern and external pattern) that conforms to the data model supported by the DBMS on the selected machine ).
Step 5: physical design of the database. Select a physical structure that is most suitable for the application environment for the given data model. The physical structure of a database mainly refers to the storage record format, storage record arrangement, and access method of a database. It relies entirely on the given hardware environment hull database product.
Step 6: implement the database. There are three main tasks in this phase: 1. Establish the actual database structure 2. Load the test data to debug the application 3. Load the actual data and enter the trial run status.
Step 7: Run and maintain the database. The formal operation of the database system marks the end of the database design and application development and the start of the maintenance phase. This phase has four tasks: 1. Maintain database security and integrity 2. Monitor and improve database operation performance 3. Expand existing database functions as required 4. promptly correct system errors found during operation.
In fact, the bread contains other knowledge, I did not list, such as E-R graph rendering method, SQL, there will be involved in keywords, foreign keys, views, tables, modes, etc, if you want to learn this by yourself, I would like to recommend a book "database Principles and Applications". This is the teaching material I used to learn about databases. If you have any questions, please hi me. I will do my best to answer you.