How to Use Entity-Relationship Diagrams in SQL for HTML5and CSS3 Programming

As an HTML5 and CSS3 programmer, you can solve all the problems with the SQL database by breaking the single table into a series of smaller, more specialized tables. The typical way of working with data design is to use a concept called an Entity-Relationship (ER) diagram. This form of diagram usually includes the following:

• Entities: Typically, a table is an entity, but you see other kinds of entities, too. An entity is usually drawn as a box with each field listed inside.

• Relationships: Relationships are drawn as lines between the boxes. As you find out about various forms of relationships, there are particular symbols used to describe these relationship types.

How to use MySQL Workbench to draw ER diagrams

You can create ER diagrams with anything, but some very nice free software can help. One particularly nice program is called MySQL Workbench. This software has a number of really handy features:

• Visual representation of database design: MySQL Workbench allows you to define a table easily and then see how it looks in ER form. You can create several tables and manipulate them visually to see how they relate.

• An understanding of ER rules: MySQL Workbench is not simply a drawing program. It's specialized for drawing ER diagrams, so it creates a standard design for each table and relationship. Other data administrators can understand the ER diagrams you create with this tool.

• Integration with MySQL: After you've created a data design you like, you can have MySQL Workbench create a MySQL script to create the databases you've defined. In fact, you can even have Workbench look at an existing MySQL database and create an ER diagram from it.

How to create a table definition in Workbench

Creating your tables in MySQL Workbench is a fairly easy task:

1. Create a new model.

   Choose File→New to create a new model.

   

2. Create a new table.

   Use the Add Table icon (near the top of the screen) to create a new table. A new dialog box opens at the bottom of the screen, allowing you to change the table name. Change the table name to hero but leave the other values blank for now.

   

3. Edit the columns.

   Select the Columns tab at the bottom of the screen to edit the table's fields. You can add field names and types here. Create a table that looks like the hero table. You can use the tab key to add a new field.

   

4. Make a diagram of the table.

   So far, MySQL Workbench seems a lot like phpMyAdmin. The most useful feature of Workbench is the way it lets you view your tables in diagram form. You can view tables in a couple of ways, but the easiest way is to select Create Diagram from Catalog Objects from the Model menu. When you do so, you'll see a screen.

   

   The diagram doesn't show the contents of the table, just the design. In fact, MySQL Workbench doesn't really care that much about what is in the database. The key idea here is how the data is organized. This matters because you will be creating several tables to manage your superheroes.

5. Extract the code.

   If you want, you can see the SQL code used to create the table you just designed. Simply right-click the table and choose Copy SQL to Clipboard. The CREATE statement for this table is copied to the Clipboard, and you can paste it to your script. Here's the code created by Workbench:

   CREATE TABLE IF NOT EXISTS 'mydb'.'hero' (
    'heroID' INT NOT NULL ,
    'name' VARCHAR(50) NULL ,
    'birthDate' DATE NULL ,
    'missionID' INT NULL ,
    PRIMARY KEY (heroID) )
   ENGINE = InnoDB

• Default NULL values are indicated: Most fields are defined with a default value of NULL. (Of course, the primary key can't be NULL, and it's defined that way.)

• Field and table names are quoted: The auto-generated code uses single quotes around all field and table names. Single quotes are needed when identifiers have spaces in them.

• The primary key notation is different: Rather than defining the primary key in the field definition, the primary key is set up as a separate entry in the table definition. This is simply a matter of style.