Attention, since WorkBench has been updated, I wrote this article, which consists of the theory and practice of building a database from WorkBench.
In chapter “foreign key Options” configure the behavior of the foreign key when the corresponding field changes (ON UPDATE) and removal (ON DELETE) parent entry:
Saving from model to real/physical database
“File → Export → Forward Engineer MySQL Create Script…”
We mark the necessary checkboxes, I needed only one Generate INSERT Statements for Tables. If you need to save the script to a file, enter the directory in the field above.
In the next window, you can configure which objects we will export. If you look closely, we have only 2 tables created.
Click on the "house" in the upper left corner of the program ...
Then double click on MyConnection….
We have a tab like this...
This is our connection to the server, this is where we will execute our script. Please note, on the left, the databases that were created in the WorkBench program ....
Now, you need to give the command to execute this script, for this, click in the top menu, Query Execute (All or Selection)
So, if everything is fine, then in the lower output window, you will see all the “green checkmarks”. And when you press Refresh in context menu in the list of databases, you will see the newly created database mydatabase1.
Finally, let's build an ER diagram. ER stands for Entity Relation - a successful Entity-Relationship model, which, in particular, was developed by Peter Chen. So, back to the model tab and click on Add Diagramm…
We have created a one-to-many relationship. Several students can study at the same faculty. Note that the link next to the Students table is split - this means "to many".
So, we have created a model, from it, through the execution of a script, a real database with tables. And also created an ER diagram.
The development of any electronic device is accompanied by physical or mathematical modeling. Physical modeling is associated with high material costs, since it requires the manufacture of mock-ups and their labor-intensive research. Often, physical modeling is simply not possible due to the extreme complexity of the device, for example, in the development of large and extra-large integrated circuits. In this case, resort to mathematical modeling using the means and methods of computer technology.
For example, the well-known P-CAD package contains a block of logical modeling of digital devices, but for beginners, including students, it presents significant difficulties in mastering. No less difficulties are encountered when using the DesignLab system. As the condition analysis showed software circuit modeling, at the stage of initial development of computer-aided design methods and at the stages of research and development, it is advisable to consider the possibility of using the following programs such as Electronics Workbench - EWB.
The Electronics Workbench circuit simulation system is designed for simulation and analysis electrical circuits fig.1. It is correct to say: a system for modeling and analyzing electrical circuits Electronics Workbench, but for brevity, hereinafter we will call it a program.
The Electronics Workbench program allows you to simulate analog, digital and digital-analog circuits of a high degree of complexity. The libraries available in the program include a large set of widely used electronic components. It is possible to connect and create new component libraries.
Component parameters can be changed in a wide range of values. Simple components are described by a set of parameters, the values of which can be changed directly from the keyboard, active elements - by a model, which is a set of parameters and describes a specific element or its ideal representation.
The model is selected from the list of component libraries, the model parameters can also be changed by the user. A wide range of instruments allows you to measure various quantities, set input effects, build graphs. All devices are displayed in a form as close as possible to the real one, so working with them is simple and convenient.
Simulation results can be printed out or imported into text or graphics editor for their further processing. The Electronics Workbench program is compatible with the P-SPICE program, that is, it provides the ability to export and import circuits and measurement results in various versions of it.
The main advantages of the program
Saving time Working in a real laboratory requires a lot of time to prepare an experiment. Now, with the advent of Electronics Workbench, the electronic laboratory is always at hand, making the study of electrical circuits more accessible. Measurement reliability
In nature, there are no two completely identical elements, that is, all real elements have a wide range of values, which leads to errors during the experiment. In Electronics Workbench, all elements are described by strictly established parameters, therefore, each time during the experiment, the result will be repeated, determined only by the parameters of the elements and the calculation algorithm.
Ease of measurements Learning is impossible without mistakes, and mistakes in a real laboratory are sometimes very expensive for the experimenter. Working with the Electronics Workbench, the experimenter is insured against accidental electric shock, and the devices will not fail due to an incorrectly assembled circuit. Thanks to this program, the user has such a wide range of devices at his disposal, which is unlikely to be available in real life.
Thus, you always have a unique opportunity to plan and conduct a wide range of studies electronic circuits with a minimum investment of time. Graphic features Complex circuits take up a lot of space, while trying to make the image more dense, which often leads to errors in connecting conductors to circuit elements. Electronics Workbench allows you to place the circuit in such a way that all the connections of the elements and at the same time the entire circuit are clearly visible.
Intuitiveness and simplicity of the interface make the program accessible to anyone who is familiar with the basics Windows usage. Compatibility with P-SPICE The Electronics Workbench program is based on standard elements of the SPICE program. This allows you to export various element models and process the results using additional features various versions P-SPICE program.
Components and experimentation
The program component libraries include passive elements, transistors, controlled sources, controlled switches, hybrid elements, indicators, logical elements, trigger devices, digital and analog elements, special combinational and sequential circuits.
Active elements can be represented by models of both ideal and real elements. It is also possible to create your own element models and add them to the element libraries. The program uses a large set of instruments for measurements: ammeter, voltmeter, oscilloscope, multimeter, Bode plotter (plotter frequency characteristics circuits), function generator, word generator, logic analyzer and logic converter.
Circuit Analysis Electronics Workbench can analyze DC and AC circuits. When analyzing at direct current, the operating point of the circuit in the steady state of operation is determined. The results of this analysis are not reflected on the instruments, they are used for further analysis of the circuit. AC analysis uses the results of DC analysis to obtain linearized models of non-linear components.
Analysis of circuits in AC mode can be carried out in both time and frequency domains. The program also allows you to analyze digital-analog and digital circuits. In Electronics Workbench, you can explore transients when exposed to circuits input signals various shapes.
Operations performed during analysis:
Electronics Workbench allows you to build circuits of varying degrees of complexity using the following operations:
. selection of elements and devices from libraries,
. moving elements and schemes to any place of the working field,
. rotation of elements and groups of elements by angles that are multiples of 90 degrees,
. copying, pasting or deleting elements, groups of elements, circuit fragments and entire circuits,
. changing the color of conductors,
. color highlighting of circuit outlines for easier perception,
. simultaneous connection of several measuring instruments and observation of their readings on monitor screen,
. element assignment symbol,
. changing the parameters of elements in a wide range. All operations are performed using the mouse and keyboard. Control only from the keyboard is not possible.
By configuring devices, you can:
. change instrument scales depending on the measurement range,
. set the mode instrument operation,
. set the type of input actions on the circuit (constant and harmonic currents and voltages, triangular and rectangular pulses).
The graphical capabilities of the program allow:
. simultaneously observe several curves on the chart,
. display curves on graphs in different colors,
. measure the coordinates of points on the graph,
. import data into a graphics editor, which allows you to make the necessary transformations of the picture and output it to the printer.
Electronics Workbench allows you to use the results obtained in the P-SPICE, PCB programs, as well as transfer the results from Electronics Workbench to these programs. You can insert a diagram or a fragment of it into text editor and print in it explanations or comments on the operation of the circuit.
Working with Electronics Workbench
The Electronics Workbench program is designed for modeling and analysis of electronic circuits. The capabilities of the Electronics Workbench v.5 program are roughly equivalent to those of the MicroCap program and allow you to perform work from simple experiments to statistical modeling experiments.
When creating a schematic, Electronics Workbench allows you to:
- select elements and devices from libraries,
Move elements and schemes to any place of the working field,
Rotate elements and their groups by angles that are multiples of 90 degrees,
Copy, paste or delete elements, fragments of diagrams,
Change conductor colors
Highlight circuit outlines with color,
Simultaneously connect several measuring devices and observe their readings on the monitor screen,
- assign symbols to elements,
Change element settings.
By changing the device settings, you can:
- change instrument scales depending on the measurement range,
Set the operating mode of the device
Set the type of input actions on the circuit (constant or harmonic currents or voltages, triangular or rectangular pulses).
Insert a scheme or its fragment into a text editor, in which an explanation of the operation of the scheme is printed.
Electronics Workbench Components
After launching WEWB32, the menu bar and component bar appear on the screen.
The component panel consists of icons of component fields, and the component field consists of conditional images of components.
A mouse click on a component icon opens the field corresponding to that icon.
Below are some of the elements from the component fields:
Basic (basic components)
Connecting node
The node is used to connect conductors and create control points.
Resistor
Resistor resistance can be set as a number in Ohm, kOhm, MOhm
Capacitor
the capacitance of the capacitor is given by a number indicating the dimension (pF, nF, μF, mF, F).
Key
A key controlled by a key. Such keys can be closed or opened using controlled keys on the keyboard. (Name control key can be entered from the keyboard in the dialog box that appears after double-clicking on the key image.)
Sources
Earth
The "Ground" component has zero voltage and serves as a reference point for potentials.
DC voltage source 12V
EMF of a constant voltage source is indicated by a number with an indication of the dimension (from μV to kV)
DC power supply 1A
The current of the DC source is set by a number indicating the dimension (from μA to kA)
AC voltage source 220 V / 50 Hz
The effective value (root-mean-sguare-RMS) of the source voltage is given by a number indicating the unit (from μV to kV). It is possible to set the frequency and the initial phase.
Source alternating current 1 A/1 Hz
The effective value of the source current is given by a number indicating the dimension (from μA to kA). It is possible to set the frequency and the initial phase.
Clock generator 1000 Hz / 50%
The generator generates a periodic sequence of rectangular pulses. You can adjust the amplitude of the pulses, the duty cycle and the frequency of the pulses.
Indicators (Instruments from the library of indicators)
The simplest instruments are the voltmeter and ammeter. They automatically change the measuring range. In one scheme, you can use several of these devices at the same time.
Voltmeter
A voltmeter is used to measure AC or DC voltage. The thick lined side of the rectangle corresponds to the negative terminal.
Double-clicking on the voltmeter image opens a dialog box for changing the voltmeter parameters:
-values of internal resistance (default 1MΩ),
- type of measured voltage (DC-constant, AC-variable).
When measuring alternating sinusoidal voltage (AC), the voltmeter shows the effective value
Ammeter
An ammeter is used to measure AC or DC current. The thick lined side of the rectangle corresponds to the negative terminal.
Double-clicking on the image of the ammeter opens a dialog box for changing the parameters of the ammeter
Internal resistance values (default 1mΩ),
Type of measured voltage (DC-constant, AC-variable).
When measuring alternating sinusoidal voltage (AC), the ammeter shows the effective value
instruments
1.Function generator
The generator is an ideal voltage source that generates sinusoidal, or triangular, or rectangular waveforms. The middle terminal of the generator, when connected to the circuit, provides a common point for reading the amplitude of the alternating voltage. To read the voltage relative to zero, this pin is grounded. The leftmost and rightmost pins are used to supply a signal to the circuit. The voltage on the right terminal changes in a positive direction relative to the common terminal, on the left terminal - in a negative direction.
Double-clicking on the generator image opens an enlarged generator image where you can set:
- the shape of the output signal,
- output voltage frequency (Frequency),
- duty cycle (Duty cycle),
- output voltage amplitude (Amplitude),
- the constant component of the output voltage (Offset).
2. Oscilloscope
There are four input terminals on the oscilloscope image
- top right clip - common,
- lower right - synchronization input,
- The left and right bottom terminals represent the Channel A and Channel B inputs, respectively.
Double-clicking on the thumbnail of an oscilloscope opens an image of a simple oscilloscope model on which you can set
- the location of the axes along which the signal is delayed,
- the desired scale of the sweep along the axes,
- offset of the origin along the axes,
- capacitive input (AC button) or potential input (DC button) of the channel,
- synchronization mode (internal or external).
The Trigger field is used to determine the start of the sweep on the oscilloscope screen. The buttons in the Edge line set the moment of triggering the oscillogram on the positive or negative edge of the pulse at the synchronization input. The Level field allows you to set the level above which the sweep is triggered.
Buttons Auto, A, B, Ext set synchronization modes
-Auto - automatic launch of the sweep when the circuit is turned on. When the beam reaches the end of the screen, the waveform is recorded from the beginning of the screen,
-A - the trigger is the signal at input A,
-B - triggering is the signal at input B,
-Ext - External start. In this case, the trigger signal is the signal applied to the clock input.
Pressing the EXPAND button on a simple oscilloscope model opens the expanded oscilloscope model. Unlike a simple model, there are three information boards here, which display the measurement results. In addition, directly below the screen there is a scroll bar that allows you to observe any time interval from the moment the circuit is turned on to the moment the circuit is turned off.
On the oscilloscope screen there are two cursors (red and blue), marked 1 and 2, with which you can measure the instantaneous voltage values at any point on the oscillogram. To do this, the cursors are dragged with the mouse to the required position (the triangles in the upper part of the cursor are captured by the mouse).
The coordinates of the intersection points of the first cursor with oscillograms are displayed on the left board, the coordinates of the second cursor on the middle board. The right panel displays the values of the differences between the corresponding coordinates of the first and second cursors.
The Reduce button provides a transition to a simple oscilloscope model.
3. Plotter (Bode plotter)
Used to build amplitude-frequency (AFC) and phase-frequency<ФЧХ) характеристик схемы.
The plotter measures the ratio of signal amplitudes at two points in the circuit and the phase shift between them. For measurements, the plotter generates its own frequency spectrum, the range of which can be set when setting up the device. The frequency of any AC source in the circuit under study is ignored, but the circuit must include some kind of AC source.
The plotter has four clamps: two input (IN) and two output (OUT). The left pins of the IN and OUT inputs are connected to the points under test, and the right pins of the IN and OUT inputs are grounded.
Double-clicking on the plotter image opens its enlarged image.
The MAGNITUDE button is pressed to get the frequency response, the PHASE button - to get the phase response.
The VERTICAL panel defines:
-initial (I) value of the vertical axis parameter,
-final (F) value of the vertical axis parameter
- type of vertical axis scale - logarithmic (LOG) or linear (LIN).
The HORIZONTAL panel is set up in the same way.
Upon receipt of the frequency response, the voltage ratio is plotted along the vertical axis:
- on a linear scale from 0 to 10E9;
- on a logarithmic scale from -200 dB to 200 dB.
Upon receipt of the PFC, degrees from -720 degrees to +720 degrees are plotted along the vertical axis.
The horizontal axis always represents the frequency in Hz or derived units.
The cursor is located at the beginning of the horizontal scale. The coordinates of the cursor moving point with the graph are displayed in the information fields at the bottom right.
circuit modeling
The circuit under study is assembled on the working field using the mouse and keyboard.
When building and editing schemes, the following operations are performed:
-selection of a component from the library of components;
- selection of an object;
- movement of the object;
-copying objects;
-removal of objects;
- connection of circuit components with conductors;
-setting the values of the components;
- connection of measuring devices.
After building the circuit and connecting devices, the analysis of the operation of the circuit begins after pressing the switch in the upper right corner of the program window (in this case, the moments of the circuit time are shown in the lower left corner of the screen).
Pressing the switch again stops the circuit.
You can pause while the circuit is running by pressing the F9 key on the keyboard; pressing F9 again restarts the circuit (a similar result can be achieved by pressing the Pause button located under the switch.)
The choice of the component necessary for constructing the circuit is made after selecting the field of components containing the necessary element. This element is captured by the mouse and moved to the working area.
Selecting an object. When selecting a component, left-click on it. In this case, the component turns red. (You can remove the selection by clicking anywhere in the workspace.)
Moving an object. To move an object, select it, place the mouse pointer on the object and, holding down the left mouse button, drag the object.
The object can be rotated. To do this, you must first select the object, then right-click and select the desired operation.
-Rotate (rotate 90 degrees),
-Flip vertical (flip vertically),
-Flip horizontal (flip horizontally)
Copying of objects is carried out by the Copy command from the Edit menu. Before copying the object must be selected. When the command is executed, the selected object is copied to the buffer. To paste the contents of the clipboard into the workspace, select the Paste command from the Edit menu
Removing objects. Selected objects can be deleted with the Delete command.
Connection of circuit components with conductors. To connect components with conductors, you need to move the mouse pointer to the pin of the component (in this case, a black dot will appear on the pin). Pressing the left mouse button, move the mouse pointer to the pin of the component you want to connect to, and release the mouse button. The terminals of the components will be connected by a conductor.
The color of the conductor can be changed by double-clicking on the conductor with the mouse and selecting the desired color from the window that appears.
Removing a conductor. If, for any reason, the conductor needs to be removed, it is necessary to move the mouse pointer to the output of the component (a black dot should appear). By pressing the left mouse button, move it to an empty area of the working field and release the mouse button. The conductor will disappear.
Parameter values are set in the component's properties dialog box, which is opened by double-clicking on the component image (Value tab).
Each component can be given a name (Label tab)
Connecting devices. To connect the device to the circuit, you need to drag the device from the toolbar to the working field with the mouse and connect the device leads to the points under study. Some devices must be grounded, otherwise their readings will be incorrect.
An enlarged instrument image appears when you double-click on the thumbnail image.
Exercise: Assemble the voltage divider circuit shown in the figure.
- Apply a sinusoidal voltage with a frequency of 3 kHz and an amplitude of 5 V to the circuit input from the function generator,
- Connect the same signal to channel A of the oscilloscope,
- Connect to the output of the divider channel B of the oscilloscope,
- highlight the conductors of channel A and channel B with different colors,
- Turn on the circuit, if necessary, change the settings of the measuring instruments,
-Go to advanced oscilloscope model. Using the cursor and the left information board, measure the amplitude value of the output signal.
-In addition, connect voltmeters to the input and output and turn on the circuit again.
Get the correct voltmeter readings.
Word generator
The diagram displays a reduced image of the word generator
The 16 outputs at the bottom of the generator feed the bits of the generated word in parallel.
The clock signal output (bottom right) is fed with a sequence of clock pulses at a given frequency.
The sync input is used to supply a clock pulse from an external source.
Double-click to open an enlarged image of the generator
The left side of the generator contains 16 bit words specified in hexadecimal code. Each code combination is entered using the keyboard. The number of the cell being edited (from 0 to 03FF, i.e. from 0 to 2047) is highlighted in the Edit box. During the operation of the generator, the address of the current cell (Current), the initial cell (Initial) and the final cell (Final) are displayed in the Address section. Issued to 16 outputs (at the bottom of the generator) code combinations are displayed in ASCII code and binary code (Binary).
The generator can operate in step, cyclic and continuous modes.
-Step button puts the generator in step mode;
- Burst button - in cyclic mode (all words are sent to the generator output once in sequence;
-Cycle button - in continuous mode. To stop continuous operation, press the Cycle button again.
The Trigger panel determines the moment the generator starts (Internal - internal synchronization, External - external synchronization when the data is ready.)
The external synchronization mode is used when the device under test can acknowledge (acknowledge) the receipt of data. In this case, the device, along with the code combination, receives a signal from the Data ready terminal, and the device under test must issue a data receive signal, which must be connected to the Trigger terminal of the word generator. This signal produces the next start of the generator.
The Breakpoint button breaks the generator in the specified cell. To do this, select the desired cell with the cursor, and then click the Breakpoint button
The Pattern button opens a menu with which you can
Clear buffer - erase the contents of all cells,
Open - load code combinations from a file with .dp extension.
Save - write all the combinations typed on the screen to a file;
Up counter - fill the screen buffer with code combinations, starting from 0 in the zero cell and then adding one in each subsequent cell;
Down counter - fill the screen buffer with code combinations, starting with FFFF in the zero cell and then decreasing by 1 in each subsequent cell;
Shift right - fill every four cells with combinations 8000-4000-2000-1000 and shift them to the right in the next four cells;
Shift left - the same, but shifted to the left.
Logic Analyzer
A reduced image of the logic analyzer is displayed on the diagram
The logic analyzer is connected to the circuit using the pins on its left side. Simultaneously, signals can be observed at 16 points of the circuit. The analyzer is equipped with two sight lines, which allows you to get readings of time intervals T1, T2, T2-T1, as well as a horizontal scroll bar
The Clock block contains terminals for connecting a conventional External and selective Qualifier source of trigger signals, the parameters of which can be set using the menu called by the Set button.
You can trigger on the rising (Positive) or falling (Negative) edge of the trigger signal using an external (External) or internal (Internal) source. In the Clock qualifier window, you can set the value of the logical signal (0.1 or x) at which the analyzer is launched.
External synchronization can be carried out by a combination of logic levels applied to the inputs of the analyzer channels.
A web developer grows with the projects he creates and develops. With the growth of projects, the complexity of the software part increases, the amount of data processed by it inevitably increases, as well as data schema complexity. Communication with other web developers shows that bases are very popular among us. MySQL data, and to manage them - the notorious PHPMyAdmin. Moving from small projects to large ones, from cms to frameworks, many, like me, remain faithful to MySQL. However, for designing a complex database with a large number of tables and relationships, the capabilities of PHPMyAdmin are sorely lacking. So I decided to write a review MySQL Workbench is a wonderful free desktop program for working with MySQL.
In the first part of the review, I will cover the very basics of working with the program, so you can use this article as novice user guide. The second part will be devoted to using Workbench in combat when working with a remote server. In it I will give the basic instructions and recommendations for setting up a server connection and synchronization with it.
MySQL Workbench- a visual database design tool that integrates the design, modeling, creation and operation of a database into a single seamless environment for the MySQL database system.
I must say that the program is really great. It allows you to quickly and with pleasure throw project data schemas, design entities and relationships between them, painlessly implement changes into the scheme and just as quickly and painlessly synchronize it with a remote server. A graphics editor EER-diagrams, resembling funny cockroaches, allows you to see the overall picture of the data model and enjoy its lightness and elegance :) After the first try, this tool becomes an indispensable assistant in the web programmer's combat arsenal.
The MySQL Workbench distribution is available on this page. The latest version of the program at the time of this writing is Version 6.1. Before downloading, you must select one of the following platforms:
After choosing a platform, you are prompted to register or log in to Oracle. If you don't want, there is a link below. "No thanks, just start my download"- click on it ;)
The start screen of the program reflects the main areas of its functionality - the design of database models and their administration:
At the top of the screen is a list of connections to your projects' MySQL servers, and a list of recently opened data models is at the bottom of the screen. Work usually begins with creating a data schema or loading an existing structure in MySQL Workbench. Let's get to work!
To add a model, click the plus sign next to the "Models" heading or select "File → New Model" (Ctrl + N):
On this screen, enter the name of the database, select the default encoding and, if necessary, fill in the comment field. You can start creating tables.
The list of project databases and the list of tables within the database will be located in the tab "Physical Schemas". To create a table, double click on "+Add Table":
A convenient interface for editing the list of fields and their properties will open. Here we can set the field name, data type, as well as set various attributes for the fields: assign a field primary key (PK), mark it Not Null (NN), binary (BIN), unique (UQ) and others, set for the field auto-increment (AI) And default value.
You can add, delete and edit table indexes in the tab "Indexes" table management interface:
We enter the name of the index, select its type, then tick the list of fields participating in this index in the required order. The order of the fields will correspond to the order in which the checkboxes were ticked. In this example, I added a unique index to the field username.
Setting foreign keys and linking tables is only possible for tables InnoDB(this storage system is selected by default). To manage relationships in each table there is a tab "Foreign Keys":
To add a link, open the tab "Foreign Keys" child table, enter the name of the foreign key and select parent table. Further in the middle part of the tab in the column Column select the key field from the child table, and in the column Referenced Column- the corresponding field from the parent table (field types must match). When creating foreign keys corresponding indexes are automatically created in the child table.
In chapter Foreign Key Options configure the behavior of the foreign key when the corresponding field changes (ON UPDATE) and removal (ON DELETE) parent entry:
In the above example, I added to the child table UserProfile foreign key to link to parent table user. When editing a field userId and removing positions from the table user similar changes will automatically happen to related records from the table UserProfile.
When creating a project, it is often necessary to add start data to the database. These can be root categories, administrator users, and so on. There is a tab for this in MySQL Workbench table management "Inserts":
As you can see from the example, if you need to apply some MySQL function to the data before writing to the database, this is done using the syntax \func functionName("data"), For example, \func md5("password").
To represent the data schema, entities and their relationships in a graphical form, MySQL Workbench has an EER diagram editor. To create a diagram at the top of the database management screen, double-click on the icon "+AddDiagram":
In its interface, you can create and edit tables, add links of various types between them. To add a table that already exists in the diagram to the diagram, simply drag it from the panel catalog tree.
To export the data schema to a graphic file, select "File → Export" and then one of the options (PNG, SVG, PDF, PostScript File).
If we already have a data schema, it can be easily imported into MySQL Workbench for further work. To import the model from the SQL file, select "File → Import → Reverse Engineer MySQL Create Script...", then select the desired SQL file and click "Execute >"
MySQL Workbench also provides for importing and synchronizing the data model directly with a remote server. For this you need to create remote access connection to MySQL, which I will talk about in the continuation of this review.
The demo project from the article is available for download at this link. I wish you success and beautiful cockroach schemes!
The purpose of this post is to help a novice developer quickly get used to and design a simple database using the MySQL Workbench visual database design tool from Oracle and get its ER model and SQL dump.
Well, less words and more meaning! The appearance of the program window, the "Data Modeling" section looks like this:
To open an existing model, click on the link: Open Existing EER Model, to create a new model - select a parameter: Create New EER Model to create an entity-relationship model from an existing database, click on the option: Create EER Model From Existing Database, and to create an EER model from a SQL script, you need to select: Create EER Model From SQL Script.
To create a new model, use the link Create New EER Model, after clicking on it, a window with parameters will be displayed:
First you need to create tables, to do this, click on the button Add Table, the following form will appear:
Let's create a table first users, which will store data about users of the information system, in the field table Name enter the name of the table in the form section columns create table fields:
- First field id will contain a unique user number, set its properties: Auto Increment, Not Null, Primary key And Unique, In chapter data type choose integer type integer.
- The second field fio, where will be stored FULL NAME. user, set the property field: Not Null, Primary key, In chapter data type choose string type VARCHAR 255
.
- Third field login, will contain the user's login, it must be unique, like the field id, so let's set its property Unique and set the number of characters in 255
.
- The following fields: password containing a password e_mail containing an email address and a field type containing the user type will be without special properties, with a string type VARCHAR long in 255
characters except for the last field type who has had enough 45
characters.
After the done manipulations, the form with the table name users will look like this:
A table will appear on the chart. users with fields and indexes:
Let's create a table in the same way. settings with settings for access to the IS database containing fields id, host to specify the hostname (server address), db- the name of the database, user And password with a username and password to install the IP on a remote server.
Next, using the already known method, we will create a shops table that will store data about stores in the fields: id type integer– key, non-zero, unique with auto-increment, field name store name field address– its physical address, field tel- store phone number site– website of the store and the field email with the store's email address.
Then we create a table products storing data about the store's products in the fields: id type integer– key, non-zero, unique with auto-increment, name field storing the name of the store, key, non-zero field of integer type shop_id store number, field type_id with information about the item number from the table of product types. The brand field is the manufacturer's brand, 255 characters long, the field model– with product model, field data– with the data and characteristics of the product type Tinytext, field img with the full address to the product image 255 characters long, and the price field with the price of the product and warranty with information about the warranty period for the product, 45 characters long.
Tables we created settings, shops And products look like this:
Next, we need a table that stores the type of products product_type, it consists of a unique, non-null, key field id with an auto-increment of an integer type, and a unique name field 255 characters long, which contains the name of the product type.
The table view is as follows:
The last two tables are orders And deliveries, the first contains information about customer orders, and the last data about the delivery of products.
Table fields orders: id key, non-null, unique field of integer type with autoincrement, field shop_id containing the store number - a key, non-zero integer type field product_id storing the product number - a key, non-zero integer type field fio date with order date - type DATE, field quantity with the number of ordered goods – integer type, field tel with the customer's phone number - a string type with a length of 255 characters and a confirm field containing information about order confirmation - a boolean type.
Table fields deliveries: order_id with order number - key, non-zero, unique integer type field with auto-increment, field field fio with the number of the user who made the order - a key, non-zero integer type field address storing the delivery address of the goods specified by the client - a string type with a length of 255 characters, the field time storing the desired delivery time of the goods - a string type with a length of 255 characters, the field date with the date the order was placed by the customer - such as DATE and a boolean field confirm storing information about the delivery of goods.
tables orders And deliveries look like this:
Table relationships
We have created a database consisting of seven tables, now we need to link the tables, we have already created key fields of an integer type, they will become the basis for linking.
To link two tables, for example products And product_type, you need to double-click the left mouse button on the diagram with the products table and select the tab foreign keys(foreign keys), then in the field foreign key name enter a unique foreign key name, double click on the tab Reference table and select table product_type, then in the form located to the right, select the referring field type_id and select the field from the pop-up list id.
Thus, both fields of the table are connected, then you need to set the type of relationship between the tables, open the window by clicking on the relationship between the tables that appears, and select the tab foreign key and in the section cardinality select the type of relationship one to many, and close the window. The diagram will display the relationship of the tables:
Similarly, we link all the key fields in the tables so that they are logically interconnected, then we need to make sure that the designed database corresponds to the third normal form.
normal form- a property of a relationship in a relational data model that characterizes it in terms of redundancy, which can potentially lead to logically erroneous results of sampling or changing data. Normal form is defined as the set of requirements that a relation must satisfy.
In a relational model, a relation is always in first normal form, by definition of the concept of relation. As for the various tables, they may not be correct representations of relationships and, accordingly, may not be in first normal form. A relation variable is in second normal form if and only if it is in first normal form and every non-key attribute is irreducibly (functionally complete) dependent on its candidate key. A database will be in third normal form if it is cast to second normal form and each non-key column is independent of each other.
Thus, our base is in third normal form, because each non-key column is independent of each other. This is clearly seen in the diagram of our database:
Most tables are in a one-to-many relationship, with the exception of tables deliveries And orders in a one-to-one relationship, since delivered, there can be only one order, i.e. One order has only one delivery. The rest of the connections are clearly indicated above.
Now let's upload our database to the server. To do this, create a new connection to the database by clicking on the link New connection in the start window of the program:
Then fill in the fields in the window that opens:
Specify the connection name in the field connection name, select the connection method in the list Connection Method, set the hostname and port in the tab Parameters, specify the username and password if it exists and click on the OK button. Then open the tab EER Diagram, select the item in the panel database and click on the option Forward Engineer:
After the window appears, click on the button "next", select the parameter Export MySQL Table Objects and press the button "next":
After pressing the button, a tab with SQL code will appear, you can save it by pressing the button Save to file if necessary, and then click on the button "next". A window with connection parameters will appear:
Check if the connection parameters are correct and click on the button "Execute", if the SQL code does not contain errors, then after the execution of the code we will see a window with a list of tables, otherwise an error message will be displayed. Now our database is uploaded to the server.
Thank you for your attention, download the program itself.
UPD:
Some habravchans were interested in the ability to display table link lines in field-to-field mode
on the advice of one of the users, I will give a small explanation on how to change the appearance of relationships and tables, for this you need to select the following option in the menu section Relationship Notation:
After that, the table relationships will take the form:
It is also possible to change the appearance of tables, for this you need to check the box in the above menu section and in the following Object Notation:
This is how the table on the diagram looks like reduced to the IDEF1X standard:
Thanks for the helpful comments!
Whatever the database developer is: a beginner (in particular) or a bearded professional, it is always easier and more visual for him to imagine what he is working on, what he is developing. Personally, I put myself in the first category and in order to understand the material, I would like to see visually what I design / develop.
To date, there are various programs and tools that cope with a similar task: some are better, some are worse. But today I would like to talk a little about MySQL WorkBench - a visual database design tool that integrates database design, modeling, creation and operation into a single seamless environment for the MySQL database system, which is the successor to DBDesigner 4 from FabForce.(c) Wikipedia. MySQL WorkBench is distributed in two flavors: OSS-Community Edition(distributed under the LGPL) and SE - Standard Edition- version for which developers ask for money. But I think that for many it will be enough and OSS versions (especially for beginners and those who do not want or consider it inappropriate to pay for software, as well as adherents of open source programs), Especially since the OSS version has rich functionality.
So, as the name suggests, this tool is designed to work with MySQL databases, and supports a large number of different types of MySQL models (see screenshot below) and will become an indispensable tool for better understanding and learning relational databases (in particular MySQL) for beginners:
Thus, any MySQL developer will find what he needs. Besides MySQL Workbench allows you to connect an existing database, execute SQL queries and SQL scripts, edit and manage database objects. But for those who are just starting to master relational databases, the most interesting, in my opinion, is the ability to create EER Models Database. In other words, this is a visual representation of all relationships between tables in your database, which, if necessary, can be easily presented as an SQL script, edited or created a new view. But more on that later. First, let's see what the main eye looks like. MySQL Workbench(5.2.33 rev 7508):
In order to create an EER-model of your database, select " Create New EER Model". As a result, we will have a tab in which we can add / create diagrams, tables, views, procedures; set various access rights for users; create a model using SQL scripts. This tab looks like this:
We will not consider the process of creating tables and databases, because everything is simple here. I will give only the final version of the finished model (see the screenshots below). Moreover, if you hover over the link line (dashed line) of the tables, then the "link", the primary key, and the foreign key will be highlighted in a different color. If you hover over a table, the table itself will be highlighted, as well as all links belonging to the selected table.
In order to edit the table, just right-click on the table we need and select " Edit Table...". As a result, an additional table editing area will appear at the bottom of the window, in which you can change the table name, columns, foreign keys, and much more. In order to export a table to an SQL script, just right-click on the table we need and choose " Copy SQL to Clipboard", and then paste from the clipboard to the desired location / program / file.
And now directly about installation MySQL WorkBench. Naturally, first you need to download MySQL WorkBench. To do this, go to the MySQL WorkBench download page, at the bottom of the page in the drop-down list, select the operating system we need. As a result, we will be offered several download options:
So, select the desired download option and click DownLoad. Then we will be kindly asked to introduce ourselves: for registered users - to enter a login and password, for beginners - to register. If you do not want to introduce yourself, then select the option below " "No thanks, just take me to the downloads!" and choose the nearest mirror for downloading. In addition, before installing, make sure that you have installed mysqlclient,.otherwise MySQL WorkBench will refuse to install.
Things to remember and know for Linux users:
Naturally, as in the case with Windows, do not forget about the MySQL Client. For Ubuntu users - you need to download the version of the program, in accordance with the version of your Ubuntu. During installation, carefully look at the error messages, if any, which will most likely tell you which packages are missing in your OS. Read about it below.
How things are with rmp-base distributions, I unfortunately do not know, because. I have never used such distributions, but I think that it is about the same as with debian-based.
You may have noticed that there is no assembly MySQL Workbench for OS Debian GNU/Linux. But, as practice has shown, it's okay. For installation MySQL Workbench in Debian 6.0 (Squeeze) we will use deb- package for Ubuntu 10.04(do not forget about the bitness of your OS: x86 or x64). Let me remind you that in order to install the downloaded deb package, you can use the utility gdebi or enter the following command in the console as root:
# dpkg -i mysql-workbench-gpl-5.2.33b-1ubu1004-amd64.deb For example, I got the following error while installing MySQL WorkBench:
dpkg: package dependencies prevent customization of mysql-workbench-gpl package:
mysql-workbench-gpl depends on libcairomm-1.0-1 (>= 1.6.4), however:
Package libcairomm-1.0-1 is not installed.
mysql-workbench-gpl depends on libctemplate0, however:
The libctemplate0 package is not installed.
mysql-workbench-gpl depends on libgtkmm-2.4-1c2a (>= 1:2.20.0), however:
Package libgtkmm-2.4-1c2a is not installed.
mysql-workbench-gpl depends on libpangomm-1.4-1 (>= 2.26.0), however:
Package libpangomm-1.4-1 is not installed.
mysql-workbench-gpl depends on libzip1 (>= 0.9), however:
The libzip1 package is not installed.
mysql-workbench-gpl depends on python-paramiko, however:
The python-paramiko package is not installed.
mysql-workbench-gpl depends on python-pysqlite2, however:
The python-pysqlite2 package is not installed.
dpkg: failed to process mysql-workbench-gpl option (--install):
dependency issues -- leave unconfigured
Errors occurred while processing the following packages:
mysql-workbench-gpl
To fix this error, I just had to type a command in the console to install some packages:
# aptitude install libzip1 libcairomm-1.0-dev libctemplate0 libgtkmm-2.4-1c2a
To install the above packages, you will also need additional packages who manager apt kindly offer to download. After installing all the necessary packages, MySQL WorkBench installs without problems.
Everything: MySQL WorkBench is successfully installed and ready to learn how to work.
upd:
If I'm not mistaken, since Ubuntu 12.04 MySQL WorkBench can be found in the distribution's repositories. Therefore, the installation process is much easier and without any crutches.
To install MySQL WorkBench, just enter the following command in the terminal:
sudo aptitude install mysql-workbench
