Specifying default data in database design

---

Setting up your database

MySQL will be used as database software throughout this tutorial. You can, however, use any other types of database products that we support. When you are familiar with the database software you are using, it won't be hard for you to complete this tutorial.

Create a database and name it as myshop in advance.

Creating new project

Create a new project with starting DB-VA. Select File > New Project from the main menu. In the New Project dialog box, name the project as My Shop Model and click Create Blank Project button.

Configuring your database

1. Select Tools > Object-Relational Mapping (ORM) > Database Configuration... from the main menu.
2. In the Database Configuration dialog box, check MySQL to select it as the target database.
   
3. In the Database Setting panel, press the green down arrow button next to Driver file field to let DB-VA download the driver for you.
   
   If your database provider does not support downloading driver file, the pop-up Download dialog box will notify you that the database software is not available and provide a link for downloading the driver file.
   
   Alternatively, you can refer to Database Driver Description section for information on downloading the driver file.
   
4. Fill in the connection information for connect to database. Enter myshop as database name. Select InnoDB as engine to keep the relationships among entities when generating database. Click on Test Connection to verify the connection. Click OK to close the configuration dialog box.
   

Drawing an ERD

1. Create an ERD. Right click on Entity Relationship Diagram in Diagram Navigator and select New Entity Relationship Diagram from the pop-up menu.
2. Name the diagram as MyShop ERD using the rename box at the top left corner. Keep Physical Model selected at the top right corner.
   
3. Next, create an entity. Press on Entity on diagram toolbar and drag to the diagram. Name it as PurchaseOrder and press Enter to confirm.
   
4. A purchase order consists of many products, while a product can appear in many purchase order. This is a typical example of many-to-many relationship. Now, create the product entity. Move the mouse pointer to the entity PurchaseOrder, press on the resource icon Many-to-Many Relationship -> Entity, drag it out and then release the mouse button to confirm. Name the entity as Product.
   
5. Add columns to the entities. Right click on PurchaseOrder and select New Column from the pop-up menu. Enter +id:int as name (note: The plus sign indicates that this is a Primary Key column). Press Enter.
   
6. Follow the table below to add columns in all entities.

   Entity	Column
   PurchaseOrder	+id : int (10), staff : varchar(255)
   Product	qty : int(11)
   PurchaseOrder_Product	+id : int(10), name : varchar(255)

   
   The result of diagram is shown as below:
   

Entering default data

1. Select PurchaseOrder. You can see the table record editor appears at the bottom of diagram lists the columns for you to add default data. If you do not see the editor, right click on the ERD's background and select Show Table Record Editor from the pop-up menu.
   
2. Double click on the id cell and enter 1. Then, double click on the staff cell and enter Mary. Press Enter.
   
3. Follow the table below to add records in PurchaseOrder entity.

   Id (PK)	staff
   1	Mary
   2	David
   3	Paul

4. Repeat the previous steps to add records in Product entity. Here are the records to add:

   Id (PK)	name
   1	Shapmoo (500 ml)
   2	Battery (AAA)

5. Select PurchaseOrder_Product entity.
6. In the editor, select 1 in PurchaseOrderid and 2 in Productid, and then enter 5 in qty.
   
7. Add records to the PurchaseOrder_Product entity. Here are the records to add:

   PurchaseOrderid (PK+FK)	Productid (PK+FK)	qty
   1	2	5
   2	1	2
   3	1	1
   3	2	1

Generating database

1. Select Tools > Object-Relational Mapping (ORM) > Generate Database... from the main menu.
2. In the Database Code Generation dialog box, check Export to database and Generate sample data. If you do not select them, both database and sample data will not be created in database.
   
3. Click OK button to start generation.

Checking your database

You can now check your database to see if the schema and default data are both generated. Here is a screenshot captured under MySQL, for checking the generated schema.

This is another screen that shows the default records added to database.