Specifying decimal places of the DB type

The data type specifies what type of data the column can hold. You also can specify a maximum for some columns. For example varchar(length) , it holds a fixed length string and the fixed size can specified in parenthesis. For decimal (precision, scale), it hold number with fractions. The maximum numbers of digits are specified in “precision”. The maximum number of digits to the right of the decimal is specified in “scale”. The DB Visual Architect (DB-VA) support configure the “length”, “precision”, “scale” for different kind of data type in Entity Relationship Diagram’s (ER Diagram) column.

Reverse Database to ERD

The following part, you will create the table call “accounts” in MySQL database. The accounts table contains different type of column with “length”, “precision” and “scale”. It will demonstrate how the DB-VA to reverse the column and specify precision to ER Diagram.

Create table “accounts” with the following column in MySQL Database.

Column name Data type Constraint

account_no int Primary Key

cust_no int

account_type varchar(3)

int_rate decimal(4,2)

free_trans int

ovd_limit decimal(9,2)

Type the following SQL statement to create accounts table in MySQL database.

CREATE DATABASE test;
USE test;
CREATE TABLE accounts (account_no int, cust_no int NOT NULL, account_type varchar(3) NOT NULL,
int_rate decimal(4, 2) NOT NULL, free_trans int(3) NOT NULL, ovd_limit decimal(9, 2) NOT NULL,
PRIMARY KEY(account_no));

Open new Project “Test Precision” in DB-VA.
Select Tools → Object-Relational Mapping (ORM) → Reverse Database … on menu bar.
Select Java Language and click Next.
Select MySQL (Connector/J Driver) driver and enter the information and then click Next.(must be set as default because the MySQL setting will use again for export update schema)
Select the accounts table to reverse and click Finish.
The ER Diagram is generated include the accounts table.

From accounts table, you can see that contain the entire column and include the length, precision and scale of different data type.

Specifying decimal places of the DB type

Now the accounts table is generated on ER Diagram, you can create the relative table call account_stmt. The account_stmt table contains column call acct_balance with data type decimal. You need specify the precision 9 and scale 2 in acct_balance column specification. decimal(9, 2) is a number that has 9 digits before the decimal and 2 digit after the decimal.

Create account_stmt table on ER Diagram.

Column name Data type Information

stmnt_no int Primary Key

stmnt_date_to date

stmnt_date_from date

acct_balance decimal(9,2)
Create the columns in account_stmt table.
Edit the acct_balance column decimal data type’s precision and scale.
- Enter precision and scale values on acct_balance column directly.
- Fill in the precision and scale values in Column Specification dialog.
Create the one to many relationsip from accounts to acct_smt table.
Rename the foreign key column from Accountsaccount_no to account_no.
Select Tools → Object-Relational Mapping (ORM) → Generate Database…on menu bar.
Select Update Database for Generate Database and Export to database and Click OK to export ER Diagram to database.
Use show create database statement to view the structure of the account_stmt table in MySQL database.
```
DESC account_stmt;
```
The acct_balance column’s precision and scale success export to the database.