1 Use MySQL Workbench to create an EER model form the script

1. Use MySQL Workbench to create an EER model form the script file named create_my_guitar_shop.sql that\'s in the mgs_ex_starts folder.

From the model, create and EER diagram that shows the relationships between the seven tables in the database. (The administrators table is not related to the other six tables.)

Solution

Modeling simplifies database design and maintenance by enabling you, the data architect, to visualize requirements and resolve design issues. Model-driven database design is an efficient methodology for creating valid and well-performing databases, while providing the flexibility to respond to evolving data requirements. Models are used to build ER diagrams and physical MySQL databases.

MySQL Workbench provides extensive capabilities for creating and manipulating database models, including these:

Create and manipulate a model graphically

Reverse engineer a live database to a model

Forward engineer a model to a script or live database

Create and edit tables and insert data

This is not an exhaustive list. The following sections discuss these and additional data-modeling capabilities.

Because MySQL databases correspond to directories within the data directory, you must consider case sensitivity for database, table, and trigger names, which follow the case sensitivity rules of the underlying file system for your operating system. Synchronizing models with objects that differ in case may lead to MySQL Workbench producing a DROP statement for that object, before recreating it as lowercase. For more information, see Identifier Case Sensitivity

MySQL Workbench enables control over objects to synchronize, and the direction of synchronization for each object. Synchronization options include:

Specify all or specific tables and objects to synchronize

Synchronize both the model and live database, or only update one or the other (unidirectional or bidirectional)

Optionally update from or to an SQL script file

Instead of executing the synchronization, you may generate an ALTER Script File to later perform the appropriate updates

Fine-tune how the synchronization will be performed by choosing the direction of each individual object, or configuring particular objects to be ignored

In this case, the synchronization direction has been changed so that rather than the default action of friends being dropped from the live database, it will be incorporated into the MySQL Workbench model. As before, educational table will be added to the live (source) database.

The three actions available actions are:

Update Model: Causes the selected changes to be applied to the model, from the live database.

Ignore: Causes the changes to be ignored. No synchronization will take place for those changes. This is designated with a double arrow that is crossed out.

Update Source: Causes the changes to be applied only to the live database.

Clicking Table Mapping offers additional mapping

To start the wizard, open a model and select Database, Synchronize With Any Source from the main menu. The steps are similar to the Synchronize Model wizard, but with additional options to create and/or use SQL script files. See the Select Sources pages.