This tutorial unit focuses on data modeling, or the process of creating a conceptual model of your storage system by identifying and defining the entities that your system requires, and their relationships to one another. The data model should contain all the logical and physical design parameters required to generate a script using the Data Definition Language (DDL), which can then be used to create a database.
In this unit, you work primarily with MySQL Workbench, a graphical tool that enables you to create data models, reverse-engineer SQL scripts into visual representations, forward-engineer data models into database schemata, and synchronize models with a running MySQL database server.
You begin by creating an entity-relationship diagram to represent the data model for
AffableBean application. When you have completed identifying and
defining all entities and the relationships that bind them, you use Workbench to
forward-engineer and run a DDL script that converts the data model into a database
schema. Finally, you connect to the new schema from the NetBeans IDE.
You can view a live demo of the application that you build in this tutorial: NetBeans E-commerce Tutorial Demo Application.
|Software or Resource||Version Required|
|NetBeans IDE||Java bundle, 6.8 or 6.9|
|Java Development Kit (JDK)||version 6|
|MySQL database server||version 5.1|
|MySQL Workbench||version 5.1 or 5.2|
In the real world, you may not have the luxury of designing the data model for your application. For example, your task may be to develop an application on top of an existing database system. Provided you do not have a data model to base your application on, creating one should be one of the first design steps you take before embarking on development. Creating a data model involves identifying the objects, or entities, required by your system and defining the relationships between them.
To begin identifying the entities we need for the data model, re-examine the use-case presented in Designing the Application. Search for commonly-occurring nouns. For example:
Customer visits the welcome page and selects a product category. Customer browses products within the selected category page, then adds a product to his or her shopping cart. Customer continues shopping and selects a different category. Customer adds several products from this category to shopping cart. Customer selects 'view cart' option and updates quantities for cart products in the cart page. Customer verifies shopping cart contents and proceeds to checkout. In the checkout page, customer views the cost of the order and other information, fills in personal data, then submits his or her details. The order is processed and customer is taken to a confirmation page. The confirmation page provides a unique reference number for tracking the customer order, as well as a summary of the order.
The text highlighted above in bold indicates the candidates that we can consider for the data model. Upon closer inspection, you may deduce that the shopping cart does not need to be included, since the data it provides (i.e., products and their quantities) is equally offered by a customer order once it is processed. In fact, as will be demonstrated in Unit 8, Managing Sessions, the shopping cart merely serves as a mechanism that retains a user session temporarily while the customer shops online. We can therefore settle on the following list:
With these four entities, we can begin constructing an entity-relationship diagram (ERD).
Note: In this tutorial, we create a database schema from the ERD, then use the IDE's EclipseLink support to generate JPA entity classes from the existing database. (EclipseLink and the Java Persistence API (JPA) are covered in Unit 7, Adding Entity Classes and Session Beans.) This approach is described as bottom up development. An equally viable alternative is the top down approach.
For more information on top down and bottom up design strategies, see Data modeling: Modeling methodologies [Wikipedia].
Start by running MySQL Workbench. In this exercise, you use Workbench to design an
entity-relationship diagram for the
Note: The following instructions work for MySQL Workbench versions 5.1 and 5.2. The images used in this tutorial are taken from version 5.2. There are slight differences in the graphical interface between versions, however the functionality remains consistent. Because version 5.2 incorporates a query editor (previously MySQL Query Browser), as well as a server administration interface (previously MySQL Administrator), you are presented with the Home screen when opening the application (shown below).
If you are working in Workbench 5.2, click Create New EER Model beneath the Data Modeling heading in the Home screen.
AffableBeanapplication. Click the plus ( ) icon located to the right of the Physical Schemata heading.
utf8 - utf8_unicode_ci
Schema used with the AffableBean application
For an explanation of character sets and collations, see the MySQL Server Manual: 9.1.1. Character Sets and Collations in General.
Start by creating a new entity-relationship diagram in MySQL Workbench. You can drag-and-drop entity tables onto the canvas.
Note: The terms 'table' and 'entity' are nearly synonymous in this tutorial unit. From the point of view of a database schema, you are creating tables. From a data modeling perspective, you are creating entities. Likewise, the columns that you later create for each table correspond to entity properties.
maintains customer details
Tablesnode. The customer table now displays.
More importantly, note that the new
customer table is now included in the
affablebean schema. Because the
affablebean schema was selected
when you created the new EER diagram, any changes you make to the diagram are automatically
bound to the schema.
order' is one of them. (For example, '
order' can be used in an
ORDER BYstatement.) Therefore, instead of naming your table '
order', name it '
customer_order' instead. At this stage, don't worry about arranging the tables on the canvas in any special order.
For a list of reserved words used by the MySQL server, refer to the official manual: 2.2. Reserved Words in MySQL 5.1.
Now that you've added entities to the canvas, you need to specify their properties.
Entity properties correspond to the columns defined in a database table. For example,
customer entity. In regard to the
application, what aspects of a customer would need to be persisted to the database?
These would likely be all of the information gathered in the checkout
page's customer details form, as well as some association to the processed order.
When adding properties, you need to determine the most appropriate data type for each property. MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types. Refer to the official manual for a summary of data types within each category: 10.1. Data Type Overview. In this tutorial, the data types have been chosen for you. Choosing the appropriate data type plays a significant role in optimizing storage on your database server. For more information see:
The following steps describe how you can use MySQL Workbench to add properties to an existing entity in your ERD. As with most of the initial design steps, determining the entity properties would call for careful consideration of the business problem that needs to be solved, and could require hours of analysis as well as numerous consultations with the client.
customertable heading to bring up the Table editor in WorkBench.
|Column||Datatype||PK (Primary Key)||NN (Not Null)||UN (Unsigned)||AI (Autoincrement)|
customertable by adding the following
VARCHARcolumns. These columns should be self-explanatory, and represent data that would need to be captured for the Affable Bean business to process a customer order and send a shipment of groceries to the customer address.
|Column||Datatype||NN (Not Null)|
VARCHARdata type, see the MySQL Reference Manual: 10.4.1. The CHAR and VARCHAR Types.
customertable selected on the canvas, choose Arrange > Reset Object Size to resize the table so that all columns are visible on the canvas. Also click the Indexes row so that any table indexes are also visible. (This includes primary and foreign keys, which becomes useful when you begin creating relationships between tables later in the exercise.)
customerentity looks as follows.
For details on the
TIMESTAMP data type, see the MySQL Reference Manual:
10.3.1.1. TIMESTAMP Properties.
So far, the entity-relationship diagram contains several entities, but lacks any relationships between them. The data model that we are creating must also indicate whether objects are aware of (i.e., contain references to) one another. If one object contains a reference to another object, this is known as a unidirectional relationship. Likewise, if both objects refer to each other, this is called a bidirectional relationship.
References correlate to foreign keys in the database schema. You will note that, as you begin linking tables together, foreign keys are added as new columns in the tables being linked.
Two other pieces of information are also commonly relayed in an ERD: cardinality (i.e., multiplicity) and ordinality (i.e., optionality). These are discussed below, as you begin adding relationships to entities on the canvas. In order to complete the ERD, you essentially need to create two one-to-many relationships, and one many-to-many relationship. Details follow.
Examine the four objects currently on the canvas while considering the business problem. You can deduce the following two one-to-many relationships:
Incorporate these two relationships into the ERD. You can download a copy of the MySQL Workbench project that contains the four entities required for the following steps: affablebean.mwb.
producttable, then click the
categorytable. The first table you click will contain the foreign key reference to the second table. Here, we want the
producttable to contain a reference to
category. In the image below, you see that a new column,
category_id, has been added to the
producttable, and that a foreign key index,
fk_product_categoryhas been added to the table's indexes.
category_idis of type
TINYINT, similar to the
categorytable's primary key.
belongs to'. In other words, "product x belongs to category y." Note that this is a unidirectional relationship: A
productobject contains a reference to the category it belongs to, but the related
categoryobject does not contain any references to the products it contains.
product) can be identified independently of the parent (
category). An identifying relationship means that the child cannot be uniquely identified without the parent. An example of this is demonstrated later, when you create a many-to-many relationship between the
ordertable first (this table will contain the foreign key), then click the
customertable. A relationship is formed between the two tables.
is placed by'. The relationship now reads, "customer order x is placed by customer y."
You can click and drag tables on the canvas into whatever position
makes the most sense for your model. In the image above, the
table has been moved to the left of
Many-to-many relationships occur when both sides of a relationship can have
numerous references to related objects. For example, imagine the Affable Bean business
offered products that could be listed under multiple categories, such as cherry ice
cream, sausage rolls, or avocado soufflé. The data model would have to account
for this by including a many-to-many relationship between
category, since a category contains multiple products, and a product
can belong to multiple categories.
In order to implement a many-to-many relationship in a database, it is necessary
to break the relationship down into two one-to-many relationships. In doing so,
a third table is created containing the primary keys of the two original tables. The
category relationship described above might look as
follows in the data model.
Now, consider how the application will persist customer orders. The
entity already contains necessary properties, such as the date it is created, its confirmation
number, amount, and a reference to the customer who placed it. However, there currently is no
indication of the products contained in the order, nor their quantities. You can resolve this
by creating a many-to-many relationship between
product. This way, to determine which products are contained in a given order,
the application's business logic can query the new table that arises from the many-to-many
relationship, and search for all records that match an
order_id. Because customers
can specify quantities for products in their shopping carts, we can also add a
column to the table.
customer_ordertable, then click the
producttable. A new table appears, named
Recall that an identifying relationship means that
the child cannot be uniquely identified without the parent. Identifying
relationships are indicated on the Workbench canvas by a solid line linking
two tables. Here, the
customer_order_has_product table forms
an identifying relationship with its two parent tables,
product. A record contained in the
table requires references from both tables in order to exist.
customer_order_has_producttable contains two foreign keys,
fk_customer_order_has_product_product, which reference the primary keys of the
producttables, respectively. These two foreign keys form a composite primary key for the
customer_order_has_producttable to '
ordered_product'. Double-click the
customer_order_has_producttable to open the Table editor. Enter
ordered_productinto the Name field.
ordered_product's Table editor, click the Foreign Keys tab. Then, click into both foreign key entries and replace '
customer_order_has_product' with '
ordered_product'. When you finish, the two entries should read:
quantitycolumn in the
ordered_producttable. To do so, click the Columns tab in the
ordered_product's Table editor. Enter the following details.
|Column||Datatype||NN (Not Null)||UN (Unsigned)||Default|
You have now completed the ERD (entity-relationship diagram). This diagram
represents the data model for the
As will later be demonstrated, the JPA entity classes that you create will
be derived from the entities existing in the data model.
Choose View > Toggle Grid to disable the canvas grid. You can also create notes for your diagram using the New Text Object ( ) button in the left margin.
To incorporate the data model you created into the MySQL database, you can employ WorkBench to forward-engineer the diagram into an SQL script (more precisely, a DDL script) to generate the schema. The wizard that you use also enables you to immediately run the script on your database server.
Important: Make sure your MySQL database server is running. Steps describing how to setup and run the database are provided in Setting up the Development Environment: Communicating with the Database Server.
DROP Objects Before Each CREATE Object', and '
Generate DROP SCHEMA'.
DROPoptions are convenient for prototyping - if you need to make changes to the schema or schema tables, the script will first delete (i.e., drop) these items before recreating them. (If you attempt to create items on the MySQL server that already exist, the server will flag an error.)
affablebeanschema are included.
Note: In examining the script, you may notice that the following variables are set at the top of the file:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
For an explanation of what these variables are, and their purpose in the script, see the official Workbench manual: Chapter 11. MySQL Workbench FAQ.
affablebean schema is now created and exists on your MySQL server.
In the next step, you connect to the schema, or database, from the IDE. At
this stage you may ask, "What's the difference between a schema and a database?"
In fact, the MySQL command
CREATE SCHEMA is a synonym for
DATABASE. (See 12.1.10.
CREATE DATABASE Syntax.) Think of a schema as a blueprint that defines the contents
of the database, including tables, relationships, views, etc. A database implements the
schema by containing data in a way that adheres to the structure of the schema. This is
similar to the object-oriented world of Java classes and objects. A class defines an object.
When a program runs however, objects (i.e., class instances) are created, managed, and
eventually destroyed as the program runs its course.
Now that the
affablebean schema exists on your MySQL server, ensure
that you can view the tables you created in the ERD from the IDE's Services window.
Important: Make sure that you have followed the steps outlined in Setting up the Development Environment: Communicating with the Database Server. This heading describes how to run the MySQL database server, register it with the IDE, create a database instance, and form a connection to the instance from the IDE.
affablebeandatabase instance you created in the previous tutorial unit.
affablebeandatabase. To do so, right-click the connection node and choose Refresh.
The IDE is now connected to a database that uses the schema you created for the
AffableBean application. From the IDE, you can now view any table
data you create in the database, as well as directly modify, add and delete data.
You will explore some of these options later, in Connecting
the Application to the Database, after you've added sample data to the database.
ALTER. Other subsets include Data Manipulation Language (DML), and Data Control Language (DCL). For more information, see Data Definition Language [Wikipedia].