Creating a foreign key mysql workbench. Working with mysql database. using the mysql workbench tool. Adding and editing data
How to create a MySQL database structure? How to create MySQL tables? MySQL Workbench database creation program!
How to create a MySQL database structure using MySQL Workbench
Want to create your own database, but are you tired of creating tables and relationships between them using SQL? Use the free software MySQL Workbench, which was created to visually create databases.
MySQL Workbench allows you to model a MySQL database using a visual representation of tables. This eliminates the need to painstakingly describe the database structure in SQL; MySQL Workbench will generate the code for you! You can download the program for free on the website: http://www.mysql.com/downloads/workbench, you can download both the installation version and the one that only requires unpacking ( available systems include: Windows Ubuntu Linux, Fedora, Mac OS X).
How to use a program to create a MySQL database?
Open MySQL Workbench, select File -> New Model, or press CTRL + N. The database modeling area is shown in the image below:
The first thing you should do is create a table with attributes - so click on the "Add Table" button.
Fill in the appropriate fields: table name, attributes (remember that one of them must be the primary key - indicated by the checkbox, PK "primary key".).
When you create tables, you have to think about how they will relate to each other.
If you have completed all the tables, click on the "Add Diagram" button to define the relationships between subjects.
You will see a window similar to the one below, which shows the created table in the chart workspace.
My database structure will not be correct as here I am only showing how to model the database structure. Therefore, you can expand tables in the workspace.
Now join the table to form relationships.
Let's say they look like this:
Book may belong to one reader
The reader may occupy several books
Typically, there are three options that allow you to create a log (1:1, 1 to many, and many to many):
So we create connections as shown in the figure:
If you double click on the relationship, you will be able to set additional options.
When you are done creating the structure, you can create the base SQL data just by importing it. To do this, select the menu File -> Export -> and select the desired option, the data is mainly tables, and users (if any are created). The file I created is shown below.
Development of any radio-electronic device accompanied by physical or mathematical modeling. Physical modeling is associated with large material costs, since it requires the production of models and their labor-intensive research. Often physical modeling is simply not possible due to the extreme complexity of the device, for example, when developing large and ultra-large integrated circuits. In this case, they resort to mathematical modeling using computer tools and methods.
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 analysis of the state showed software circuit modeling, at the stage of initial development of computer-aided design methods and at the stages of carrying out search and research work, it is advisable to consider the possibility of using the following programs such as Electronics Workbench - EWB.
Electronics Workbench circuit simulation system is designed for simulation and analysis electrical diagrams Fig.1. It’s correct to say: Electronics Workbench system for modeling and analysis of electrical circuits, but for brevity, hereinafter we will call it a program.
Electronics Workbench allows you to simulate highly complex analog, digital, and digital-to-analog circuits. 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 over 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 a list of component libraries, and model parameters can also be changed by the user. A wide range of instruments allows you to measure various quantities, set input influences, and build graphs. All devices are depicted in a form as close as possible to the real one, so working with them is simple and convenient.
Simulation results can be printed or imported into a 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 into its various versions.
Main advantages of the program
Saving time Working in a real laboratory requires a lot of time spent preparing an experiment. Now, with the introduction of Electronics Workbench, an electronics lab is always at your fingertips, making learning about electrical circuits more accessible. Reliability of measurements
In nature there are no two completely identical elements, that is, all real elements have a large spread of values, which leads to errors during the experiment. In Electronics Workbench, all elements are described by strictly established parameters, so each time during the experiment the result will be repeated, determined only by the parameters of the elements and the calculation algorithm.
Convenience of measurements Studying is impossible without mistakes, and mistakes in a real laboratory are sometimes very costly for the experimenter. When working with Electronics Workbench, the experimenter is protected from accidental electric shock, and the devices will not fail due to an incorrectly assembled circuit. Thanks to this program, the user has at his disposal such a wide range of instruments that are 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 at minimum costs time. Graphics capabilities Complex circuits take up quite a lot of space, while they try to make the image denser, 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.
The intuitiveness and simplicity of the interface make the program accessible to anyone who is familiar with the basics using Windows. P-SPICE Compatible Electronics Workbench is based on standard SPICE software. This allows you to export various models elements and process the results using additional features various versions of the P-SPICE program.
Components and Experiments
The program component libraries include passive elements, transistors, controlled sources, controlled switches, hybrid elements, indicators, logic 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 element libraries. The program uses a large set of instruments for carrying out measurements: ammeter, voltmeter, oscilloscope, multimeter, Bode plotter (grapher of frequency characteristics of circuits), function generator, word generator, logic analyzer and logic converter.
Circuit Analysis Electronics Workbench can analyze DC and AC circuits. In DC analysis, the operating point of the circuit in steady state is determined. The results of this analysis are not reflected on the devices; they are used for further analysis of the circuit. AC analysis uses the results of DC analysis to obtain linearized models of nonlinear components.
Analysis of circuits in AC mode can be carried out in both the time and frequency domains. The program also allows you to analyze digital-analog and digital circuits. In Electronics Workbench, you can examine transients when circuits are exposed to various input waveforms.
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 diagrams to any place in the working field,
. rotation of elements and groups of elements at angles that are multiples of 90 degrees,
. copying, pasting or deleting elements, groups of elements, fragments of circuits and entire circuits,
. change in conductor color,
. highlighting the contours of diagrams with color for more convenient perception,
. simultaneous connection of several measuring instruments and observing their readings on monitor screen,
. assignment to element symbol,
. changing the parameters of elements in a wide range. All operations are performed using a mouse and keyboard. Control from the keyboard alone is not possible.
By setting up devices you can:
. change instrument scales depending on the measurement range,
. set the mode operation of the device,
. set the type of input influences on the circuit (constant and harmonic currents and voltages, triangular and rectangular pulses).
The graphical capabilities of the program allow you to:
. simultaneously observe several curves on the graph,
. display curves on graphs in different colors,
. measure the coordinates of points on a graph,
. import data into a graphic editor, which allows you to make the necessary transformations of the picture and output it to a printer.
Electronics Workbench allows you to use the results obtained in the P-SPICE, PSV programs, as well as transfer results from Electronics Workbench to these programs. You can insert a diagram or its fragment 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 analyzing electronic circuits. The capabilities of the Electronics Workbench v.5 program are approximately equivalent to those of the MicroCap program and allow you to perform work from simple experiments to statistical modeling experiments.
When creating a circuit, Electronics Workbench allows you to:
- select elements and devices from libraries,
Move elements and diagrams to any place in the workspace,
Rotate elements and their groups by angles that are multiples of 90 degrees,
Copy, paste or delete elements, diagram fragments,
Change the colors of conductors,
Highlight the outline color of the circuits,
Connect several measuring instruments at the same time and observe their readings on the monitor screen,
- assign symbols to elements,
Change element parameters.
By changing device settings you can:
-change instrument scales depending on the measurement range,
Set the operating mode of the device,
Set the type of input influences on the circuit (constant or harmonic currents or voltages, triangular or rectangular pulses).
Insert a diagram or a fragment of it into a text editor in which an explanation of the operation of the diagram 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 conventional images of components.
By clicking on the component icon, the field corresponding to this icon opens.
Below are some elements from the component fields:
Basic (basic components)
Connecting node
The node is used to connect conductors and create control points.
Resistor
The resistor resistance can be specified as a number in Ohm, kOhm, MOhm
Capacitor
The capacitance of the capacitor is specified by a number indicating the dimension (pF, nF, μF, mF, F).
Key
Key controlled by key. Such keys can be closed or unlocked using controlled keys on the keyboard. (The name of the 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.
12V DC voltage source
The EMF of a constant voltage source is indicated by a number indicating the dimension (from μV to kV)
Source direct current 1A
The current of the DC source is specified 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 specified by a number indicating the dimension (from µV to kV). It is possible to set the frequency and initial phase.
AC power supply 1 A/1 Hz
The effective value of the source current is specified by a number indicating the dimension (from μA to kA). It is possible to set the frequency and initial phase.
Clock generator 1000 Hz / 50%
The generator produces a periodic sequence of rectangular pulses. You can adjust the pulse amplitude, duty cycle and pulse repetition rate.
Indicators (Device from the indicator library)
The simplest instruments are a voltmeter and an ammeter. They automatically change the measuring range. Several such devices can be used simultaneously in one circuit.
Voltmeter
A voltmeter is used to measure AC or DC voltage. The side of the rectangle highlighted with a thick line corresponds to the negative terminal.
Double-clicking on the voltmeter image opens a dialog box for changing the voltmeter parameters:
- internal resistance values (default 1 MΩ),
-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 side of the rectangle highlighted with a thick line corresponds to the negative terminal.
Double-clicking on the ammeter image opens a dialog box for changing the ammeter parameters
Internal resistance values (default 1mOhm),
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 produces sinusoidal, triangular, or rectangular signals. The middle terminal of the generator, when connected to the circuit, provides a common point for measuring the amplitude of the alternating voltage. To measure 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 at the right pin changes in a positive direction relative to general conclusion, on the left terminal - in negative.
At double click Click on the generator image to open a larger image of the generator where you can set:
- the shape of the output signal,
- output voltage frequency (Frequency),
-duty cycle,
- output voltage amplitude (Amplitude),
- constant component of the output voltage (Offset).
2. Oscilloscope
There are four input terminals in the oscilloscope image
-top right clamp - general,
- lower right - synchronization input,
-The left and right bottom terminals represent the Channel A and Channel B inputs, respectively.
Double-clicking on the thumbnail image of the oscilloscope opens an image of a simple oscilloscope model on which you can install
- location of the axes along which the signal is plotted,
- the required scale of scanning along the axes,
-displacement of the origin of coordinates 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 moment the sweep begins on the oscilloscope screen. The buttons in the Edge line set the moment when the oscillogram is triggered by the positive or negative edge of the pulse at the synchronization input. The Level field allows you to set the level, when exceeded, the sweep is triggered.
Buttons Auto, A, B, Ext set synchronization modes
-Auto -automatic start sweep when turning on the circuit. When the beam reaches the end of the screen, the oscillogram is recorded from the beginning of the screen,
-A - the triggering signal is the signal arriving at input A,
-B - the triggering signal is the signal arriving at input B,
-Ext - External launch. In this case, the trigger signal is the signal applied to the clock input.
Pressing the EXPAND button on a simple oscilloscope model opens an advanced oscilloscope model. Unlike the simple model, there are three information panels on which measurement results are displayed. In addition, directly below the screen there is a scroll bar, which allows you to observe any time period from the moment the circuit is turned on to the moment it turns off.
On the oscilloscope screen there are two cursors (red and blue), designated 1 and 2, with which you can measure 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 at the top of the cursor are captured with the mouse).
The coordinates of the points of intersection of the first cursor with the oscillograms are displayed on the left panel, the coordinates of the second cursor on the middle panel. The right panel displays the difference values 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 construct 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 AC source.
The plotter has four terminals: two input (IN) and two output (OUT). The left terminals of the IN and OUT inputs are connected to the points under study, and the right terminals of the IN and OUT inputs are grounded.
Double-clicking on the plotter image opens a larger image of it.
The MAGNITUDE button is pressed to obtain the frequency response, the PHASE button is pressed to obtain the phase response.
The VERTICAL panel sets:
-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 configured in the same way.
When obtaining 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.
When obtaining a phase response, degrees from -720 degrees to +720 degrees are plotted along the vertical axis.
The horizontal axis always displays frequency in Hz or derived units.
The cursor is located at the beginning of the horizontal scale. The coordinates of the point where the cursor moves with the graph are displayed in the information fields at the bottom right.
Circuit Simulation
The circuit under study is assembled on the working field using a mouse and keyboard.
When building and editing diagrams, the following operations are performed:
-selecting a component from the component library;
-selecting an object;
-moving an object;
-copying objects;
-deleting objects;
- connection of circuit components with conductors;
-setting component values;
-connection of measuring instruments.
After constructing the circuit and connecting the devices, the analysis of the circuit operation begins after pressing the switch in the upper right corner of the program window (at the same time, circuit time instants 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 resumes the operation of the circuit (a similar result can be achieved by pressing the Pause button located under the switch.)
The selection of the component required to build the circuit is made after selecting the component field containing the required element. This element is grabbed by the mouse and moved to the workspace.
Selecting an object. When selecting a component, you must click on it with the left mouse button. The component turns red. (You can remove the selection by clicking anywhere on the work field.)
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 required operation
-Rotate (rotate 90 degrees),
-Flip vertical (flip vertically),
-Flip horizontal (flip horizontally)
Objects are copied using the Soru 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 buffer onto the work field, select the Paste command from the Edit menu
Deleting objects. Selected objects can be deleted using the Delete command.
Connecting circuit components with conductors. To connect components with conductors, you need to move the mouse pointer to the component pin (a black dot will appear on the pin). Press 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 are 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, you must move the mouse pointer to the output of the component (a black dot should appear). Press 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 properties dialog box, which is opened by double-clicking on the component image (Value tab).
Each component can be assigned a name (Label Tab)
Connecting devices. To connect the device to the circuit, you need to drag the device from the toolbar onto the work field with the mouse and connect the device leads to the points being studied. Some instruments must be grounded or their readings will be incorrect.
An expanded image of the device appears when you double-click on the reduced image.
Exercise: Build 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 input of the circuit from the function generator,
-Connect the same signal to channel A of the oscilloscope,
-Connect channel B of the oscilloscope to the divider output,
-highlight the conductors of channel A and channel B in different colors,
-Turn on the circuit, change the settings of the measuring instruments if necessary,
-Go to the advanced oscilloscope model. Using the cursor and the left information panel, measure the amplitude value of the output signal.
-Additionally connect voltmeters to the input and output and turn on the circuit again.
Obtain correct voltmeter readings.
Word generator
A small image of the word generator is displayed on the diagram
The 16 outputs at the bottom of the generator supply the generated word bits in parallel.
The clock signal output (lower right) is supplied with a sequence of clock pulses at a given frequency.
The clock input is used to supply a clock pulse from an external source.
Double-click to open an expanded 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 O to 03FF, i.e. from 0 to 2047) is displayed in the Edit window. While the generator is operating, the Address section displays the address of the current cell (Current), the initial cell (Initial) and the final cell (Final). Code combinations issued to 16 outputs (at the bottom of the generator) are displayed in ASCII code and binary code (Binary).
The generator can operate in step, cyclic and continuous modes.
-The Step button switches the generator to step-by-step mode;
-Burst button - to cyclic mode (all words are sent to the output of the generator one time in sequence;
-Cycle button - to continuous mode. To interrupt continuous operation, press the Cycle button again.
The Trigger panel determines the moment the generator starts (Internal - internal synchronization, External - external synchronization when data is ready.)
External synchronization mode is used when the device under test can acknowledge (confirm) the receipt of data. In this case, the device receives a signal from the Data ready terminal along with the code combination, and the device under test must produce a data receiving signal, which must be connected to the Trigger terminal of the word generator. This signal triggers the next start of the generator.
The Breakpoint button interrupts the operation of the generator in the specified cell. To do this, select the required cell with the cursor, and then press 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 the extension .dp.
Save - write all combinations typed on the screen to a file;
Up counter - fill the screen buffer with code combinations, starting with 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, shifting them to the right in the next four cells;
Shift left - the same thing, but shifted to the left.
Logic analyzer
A miniature image of the logic analyzer is displayed on the diagram
The logic analyzer is connected to the circuit using pins on its left side. Signals can be observed at 16 points of the circuit simultaneously. The analyzer is equipped with two sight lines, which allows you to obtain readings of time intervals T1, T2, T2-T1, as well as a horizontal scroll bar
The Clock block has terminals for connecting a regular External and selective Qualifier source of trigger signals, the parameters of which can be set using the menu called up by the Set button.
Triggering can be done on the rising (Positive) or falling edge (Negative) 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 starts.
External synchronization can be carried out by a combination of logic levels supplied to the inputs of the analyzer channels.
The purpose of this post is to help a novice developer quickly get used to and design a simple database using the visual database design tool MySQL Workbench from Oracle and obtain 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:
In order to open an existing model, click on the link: Open Existing EER Model, to create a new model – select the option: Create New EER Model To create an entity-relationship model from an existing database, click on the parameter: 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 Create New EER Model link; 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:
First let's create a table users, which will store data about users of the information system in the field table name enter the table name in the form section Columns Let's 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 an integer type integer.
- Second field fio, where it 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 login, it must be unique, like the field id, so let’s set it the property Unique and set the number of characters in 255
.
- The following fields: password containing the 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 the last field type who has enough 45
characters.
After the completed manipulations, a form with the table name users will look like this:
A table will appear on the diagram users with fields and indexes:
Let's create a table in a similar way settings with access settings to the IS database containing fields id, host to specify the host name (server address), db– database name, user And password with a username and password to install the IS on a remote server.
Next, using the already known method, we will create a table shops that will store data about shops in the fields: id type integer– key, non-zero, unique with auto-increment field name storing the store name, field address– his physical address, field tel- store phone number, site– online store website and field email with the store's email address.
Then let's create a table products storing data about store 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 storing the store number, field type_id with information about the product number from the table of product types. Brand field – manufacturer’s brand, 255 characters long, field model– with product model, field data– with 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 storing the type of products product_type, it consists of a unique, non-null key field id with auto-increment of an integer type, and a unique name field 255 characters long, which contains the name of the product type.
The table looks like this:
The last two tables are orders And deliveries, the first contains information about customer orders, and the last contains information about product delivery.
Table fields orders: id key, non-zero, unique field of integer type with auto-increment, field shop_id containing the store number - a key, non-zero integer field product_id storing the product number - a key, non-zero integer 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 255 characters long and a confirm field containing information about order confirmation – a logical type.
Table fields deliveries: order_id with order number - key, non-zero, unique field of integer type with auto-increment, field field fio with the number of the user who made the order - a key, non-zero integer field address storing the delivery address of the goods specified by the client - a string type 255 characters long, field time storing the desired delivery time of the goods - a string type 255 characters long, field date with the date the customer placed the order - type 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 the integer type, they will become the basis for linking.
For example, to link two tables 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), further into the field Foreign key name enter a unique name for the foreign key, double-click on the tab Referenced table and select the table product_type, then in the form located to the right select the referencing 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 a window by clicking on the relationship between the tables that appears, and select the tab Foreign Key and in the section Cardinality Select the one-to-many connection type and close the window. The diagram will display the relationship between the tables:
In a similar way, we link all the key fields in the tables so that they are logically interrelated, then we need to make sure that the designed database complies with third normal form.
Normal form- a property of a relationship in a relational data model, characterizing it from the point of view of redundancy, which can potentially lead to logically erroneous results of sampling or changing data. Normal form is defined as a set of requirements that a relation must satisfy.
In the relational model, a relation is always in first normal form by definition of the concept of a 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 reduced 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 visible in our database diagram:
Most tables are in a one-to-many relationship, with the exception of tables deliveries And orders in a one-to-one relationship, because delivered, there can only be one order, i.e. One order has only one delivery. The remaining 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 program start window:
Then fill in the fields in the window that opens:
Specify the connection name in the field Connection Name, select the connection method from the list Connection Method, set the host name and port in the tab Parameters, indicate the username and password if you have one and click on the OK button. Then open the tab EER Diagram, in the panel select the item Database and click on the parameter Forward Engineer:
After the window appears, click on the button "Next", select the parameter Export MySQL Table Objects and press the button "Next":
After clicking the button, a tab with SQL code will appear, you can save it by clicking the button “Save to file” if necessary and then press the button "Next". A window with connection parameters will appear:
We check if the connection parameters are correct and click on the button "Execute", if the SQL code does not contain errors, then after executing 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 Khabra residents were interested in the possibility of displaying table connection lines in field-to-field mode
on the advice of one of the users, I will give a short explanation on how to change the appearance of relationships and tables, to do this you need to select the following option in the menu section Relationship Notation:
After this, the table relationships will take the form:
It is also possible to change the type of tables; to do this, you need to check the box in the above section of the menu and in the following Object Notation:
This is what the table on the diagram looks like adjusted to the IDEF1X standard:
Thank you for your thoughtful comments!
A web developer grows with the projects he creates and develops. As projects grow, the complexity of the software increases, the amount of data it processes inevitably increases, as well as data schema complexity. Communication with other web developers shows that MySQL databases are very popular among us, and to manage them - the well-known 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 great free desktop program for working with MySQL.
In the first part of the review, I will talk about the very basics of working with the program, so you can use this article as beginner's 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 tool for visual database design that integrates 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 throw quickly and with pleasure project data schemas, design entities and connections between them, painlessly implement changes into the scheme and just as quickly and painlessly synchronize it with a remote server. A graphic editor EER diagrams, reminiscent of 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 combat arsenal of a web programmer.
Download MySQL Workbench
The MySQL Workbench distribution is available on this page. The latest version of the program at the time of writing is Version 6.1. Before downloading, you must select one of the following platforms:
- Microsoft Windows (MSI Installer and ZIP archive available)
- Ubuntu Linux
- Fedora
- Red Hat Enterprise Linux / Oracle Linux
- Mac OS X
After choosing a platform, you are prompted to register or log in to Oracle. If you don't want to, there's a link below. "No thanks, just start my download"- click on it ;)
Beginning of work
The program's start screen reflects the main areas of its functionality - designing database models and their administration:
At the top of the screen there is a list of connections to the MySQL servers of your projects, and a list of the latest open data models is at the bottom of the screen. Work usually starts with creating a data schema or loading an existing structure into MySQL Workbench. Let's get to work!
Creating and editing a data model
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 database name, select the default encoding and, if necessary, fill in the comment field. You can start creating tables.
Adding and editing a table
The list of project databases and the list of tables within the database will be located in the tab "Physical Schemes". To create a table, double click on "+Add Table":
A convenient interface will open for editing the list of fields and their properties. Here we can set the field name, data type, and also set various attributes for the fields: assign field primary key (PK), mark it Not Null (NN), binary (BIN), unique (UQ) and others, set for field auto-increment (AI) And default value.
Index management
You can add, delete and edit table indexes in the tab "Indexes" table management interface:
Enter the name of the index, select its type, then check 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 checked. In this example I added a unique index to the field username.
Relationships between tables
Setting foreign keys and linking tables is only possible for tables InnoDB(this storage system is selected by default). To manage relationships, each table has a tab "Foreign Keys":
To add a connection, 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 record:
- RESTRICT- throw an error when changing/deleting a parent record
- CASCADE- update foreign key when parent record changes, delete child record when parent is deleted
- SET NULL- set the foreign key value NULL when changing/deleting parent (not acceptable for fields that have the flag set NOT NULL!)
- NO ACTION- do nothing, but in fact the effect is similar to RESTRICT
In the example above, I added to the child table UserProfile foreign key to link to parent table User. When editing a field userId and deleting positions from the table User there will be similar changes automatically also occur with related records from the table UserProfile.
When creating a project, you often need to add startup data to the database. These could be root categories, administrative users, etc. There is a tab for this in MySQL Workbench table management "Inserts":
As can be seen from the example, if some MySQL function needs to be applied to the data before writing to the database, this is done using the syntax \func functionName("data"), For example, \func md5("password").
Creating an EER diagram (entity-relationship diagram)
To present the data schema, entities and their relationships in 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 "+Add Diagram":
In its interface you can create and edit tables, add relationships 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 a data schema to a graphic file, select "File → Export" and then one of the options (PNG, SVG, PDF, PostScript File).
Importing an existing data schema (from SQL dump)
If we already have a data schema, it can be easily imported into MySQL Workbench for further work. To import a model from an SQL file, select "File → Import → Reverse Engineer MySQL Create Script...", then select the required SQL file and click "Execute >"
MySQL Workbench also provides import and synchronization of the data model directly with a remote server. To do this you will need to create connecting remote access 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!