Last demos here an overview of how to create an EER Diagram but today we will detail the part of creating the databases and tables.
Creating a database and tables is not something difficult to define in MySQL Workbench. The harder it is even "draw" the structure of the database.
Creating a Database?
A data base can be created in several ways. However, for this tutorial, we'll create it through the EER model.For this we go to models and carry the "+"
Then we double click in the area indicated by the first arrow and change the name in the text box below. And it is created (in the EER model) to db_Pplware database.
Creating tables?
After creating the database we proceed to the creation of tables. For this tutorial we will consider a very simple example with only two tables. An author and another table named publishers. For this carry on Add Table
Authors table
To this table we will consider the following fields:
- id_autores, which will be the table key (PK), the field can not be null (NN) and each record the key value is incremented +1 (AI - AutoIncrement)
- name - the author's name to be of type VARCHAR, with 45 characters
- DateOfBirth, field of type date
- Address to be of type varchar, also with 45 characters
Table editor
To this table we will consider the following fields:
- id_editora, which will be the table key (PK), the field can not be null (NN) and each record the key value is incremented +1 (AI - AutoIncrement)
- Publisher name - Publisher's name to be of type VARCHAR, with 45 characters
Relationships between tables
In a relational database, relationships enable you to prevent the existence of redundant data. There are three types of relationships:
1) 1: 1
As the name indicates a type 1 1 ratio, is a relationship in which an instance of the Table corresponds one and only one instance of table B and vice versa.
As the name indicates a type 1 1 ratio, is a relationship in which an instance of the Table corresponds one and only one instance of table B and vice versa.
Example: A person can only have a number of BI, and BI can only belong to one person.
A relationship type one to one, it is the "creator" of the entity-relationship model of the choice which the table that will receive the foreign key.
2) 1 to N (where N stands for number)
A ratio of type 1 to n, is a link to several, i.e. between two tables A and B, an occurrence of the table may match multiple table occurrences B, whilst an instance of table B corresponds to only one table A.
A ratio of type 1 to n, is a link to several, i.e. between two tables A and B, an occurrence of the table may match multiple table occurrences B, whilst an instance of table B corresponds to only one table A.
Example: A player can make multiple requests, but a request can only be made by a reader, this means that between the table and the player requests table is a type 1 compared to n.
The main key is added to the side having n, thus becoming a foreign key.
3. N to M (where N and M number of mean)
a relation of n to m is a ratio of several to several, i.e. between two tables A and B, several instances of the table may be many instances Table B, and vice versa.
a relation of n to m is a ratio of several to several, i.e. between two tables A and B, several instances of the table may be many instances Table B, and vice versa.
Example: A coin may be issued for several years, but a year can issue multiple currencies, this means that between the currency table and the table year there is a relation of n to m.
For any relationship type n to m, it is necessary to decompose the relation into two type 1 to n, that is, will have to create a new table with the name the "creator" of the entity-relationship it wants Model , where it will contain the main keys of the tables involved, these keys which will become a new table consisting of the key.
Final decomposition:
Create relations between tables in Workbench
To create relationships between tables in the Workbench should press Add Diagram
Then should drag the two tables to the diagram.
The next step is to define the relationship between tables. In this case we are dealing with a 1: N ie a lineeditor table can have multiple matching rows in the authors table, but a row in the authors table can have only one matching row in table editor.
To define the relationship simply click on the sidebar button, load on the Authors table and drag to the publisher table.
Note: As you can see, this sidebar there are two types of buttons to set a ratio of 1: N. The - means that the primary key of the table that we make the relationship becomes secondary in the other table. In case of choosing the button with ___ this key also stands as the primary table where we make the relationship.
Post a Comment