Design an ER diagram with Visio 2010 and translate it into the SQL language

We need to consider the relationship between the data when we set up the database, in order to clarify the relationship between the data we need to do a uniform collation of the data. For the more complex database, we need to establish a database model, which can be divided into two steps: first, the structure analysis of the database and the use of tree, table and other descriptive data, and then analyze the database operation methods, rules and additions and deletions of the database operation, these two procedures constitute what we usually call the database model.

The database model has multiple classifications, often referred to as the ER diagram is a graph model, in addition to the hierarchical model, network model, relational model and so on. This is simpler when the number ER diagram, also known as the entity-contact Graph (Entity Relationshipdiagram), provides methods for representing entity types, attributes, and relationships to describe the conceptual model of the real world. The General ER drawing method uses the circle, the square, the diamond to describe the entity and the relation between them, these three represent the attribute, the entity, the relation respectively in the diagram, as for the general ER diagram.

The use of PowerDesigner is advocated when modeling er diagrams (powerful, diverse), because PowerDesigner can directly export the ER diagram as T-SQL code . Can't powerdesigner be able to do Visio? In fact, Visio itself is not too much of a problem, and the operation is simple, but relatively few functions and other development program interaction is weak.

I. Visio drawing ER diagrams1. Visio draws a general ER diagram

         Visio does not provide a special template to draw a general ER diagram, there is a more eclectic approach: first in " more Shapes "-" Flowchart "-" Basic Flowchart " to find rectangles and diamonds, right-click " Add to My Shapes "--Add to New stencil" fill in the "ER diagram" in the popup dialog box, so we add the diamonds and squares to the new stencil "ER diagram". With the same idea, in "database The ORM diagram finds ellipses and lines that are added to the stencil ER diagram.

Once added, we can open the stencil when we draw the ER diagram (open the stencil with a shape, file , and so on), and all the elements in the ER diagram will be displayed in a stencil.

2. Using Visio's own database model model to draw ER diagram

         Visio provides two forms of drawing er diagrams, one through the reverse engineering , which interacts with Visio and other database drives, transforms an existing database schema into an ER diagram, and yourself. The following is an example of an ER diagram of a database for a toll system, which describes how Visio draws er diagrams.

        Select "" and "new"-"Software and Database" , and then double-click Create Database Model diagram. The left side of the control Panel is created with three dedicated tools for the database ER model, the most common being the top two representing the database table and the foreign key relationship respectively. Simple operation simply drag an entity onto the page, followed by the database properties below, where you can add or modify some of the properties of the data table.

You can add columns, set column data types, set primary keys, and more by changing the properties of an entity. An entity named T_admin was created and the primary key is chserial.

tip: If you want the entity graph to display the data type of the entity, you can use the database----Manage----display options----table----data type---display physical, set
。

Relationships between entities, there are two representations of representations in Visio, as shown in the following:

         Visio defaults to the first form, with arrows pointing to the parent table of the entity. Can be passed Database----Management----display Options----document----The relationship and crow's feet are selected at the same time to change the setting to the second display.

When you connect using a relationship connector, a foreign key is automatically generated. The generated foreign key automatically creates a primary key column in the parent table from the table, and you can break the column by using the database properties----definition----Check the columns associated with each other in the two tables----disconnect , or use this method to join the columns in both tables as foreign keys. As shown in the following:

Using Visio to draw the ER diagram basic steps have been introduced, in the use of Visio modeling must be careful, in the pre-drawing must be analyzed for each entity and entity attributes of the naming conventions.

The above method is the use of graphical tools to create their own ER diagram, the other way is through the "database"----"echo Engineering" to automatically generate a database of ER diagram, simple and convenient operation, it is recommended that you use and the ER diagram you created to compare to correct their use of misunderstanding.

ii. Visio Database Model diagram exported as SQL script

Visio does not provide a straightforward way to export, and it is not enough to export a well-drawn ER diagram to a SQL script that relies only on Visio, but with other tools. One approach is to use the Import/export feature of Visio to export the model diagram to a Erwin file and then open it using PowerDesigner or Erwin, and finally to SQL, but this method does not exist in Visio2010. VISIO2010 does not support exporting ER diagrams to Erwin format, only import is supported. This requires our second approach.

Another approach is to use the Orthogonaltoolbox gadget, which is a small tool that is specifically responsible for visioer, which generates the corresponding sqlscript through an XSLT template.

How to use:

1. Download and install the Orthogonaltoolbox (Note: the. Net Framework 1.1.4322,http://download.csdn.net/detail/zhang_xinxiu/5788193 is required first);

2. Copy the downloaded XSLT document to: C:\Program Files (X86) \orthogonalsoftware corporation\orthogonal toolbox\stylesheets;

3. If you do not see the Orthogonaltoolbox in the Visio Tools column, check the current tool list-tool list –orthogonaltoolbox tick, you can see;

4. Select "Add-ons"--"Orthogonaltoolbox"--Select the ER diagram you want to export--"export XML"--Check the set stylesheet--> and select the Otertosql.xslt template

5. Press Export to generate an XML file that opens the XML file using IE to see the SQL script that we created the database.

There is a solution to the problem, we need to be patient to find it, sometimes a few minutes, sometimes even hours or days, the key to solving the problem is whether there is patience, the knowledge learned in this process is more important than the result.

Design an ER diagram with Visio 2010 and translate it into the SQL language