Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted May 9, 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2

By Rob Gravelle

Converting a Model into another Type

All the work that you put into the Conceptual model need not go to waste.  We can utilize it as the basis for the Logical model. 

  • Select File > Model Conversion... from the main menu to open the Convert to New Model dialog.
  • Choose Logical as the Model Type and click OK:

Convert to New Model
Convert to New Model

Note that the Target Database dropdowns are disabled because a Logical model does not contain database-specific information.

Navicat Modeler will create a new Logical Model of the Sales Star Schema.  Entities and their relationships will be carried over, so that we can dive right into attribute (a.k.a. field) creation.

Here are the attributes for each of the four entities:

t_sales:

Name

Type

(Char) Length

Decimals*

Not Null

Default Value**

id (PK)

INTEGER

0

0

Yes

N/A

date_id

INTEGER

0

0

Yes

N/A

store_id

INTEGER

0

0

Yes

N/A

product_id

INTEGER

0

0

Yes

N/A

units_sold

INTEGER

0

0

No

NULL

*Decimals only apply to numeric fields.
** Can be either NULL or an EMPTY STRING.

t_dates:

Name

Type

Length

Decimals

Not Null

Default Value

id (PK)

INTEGER

0

0

Yes

N/A

day

CHAR

2

0

No

NULL

month

CHAR

2

0

No

NULL

year

CHAR

4

0

No

NULL

quarter

NUMERIC

1

0

No

NULL

t_stores:

Name

Type

Length

Decimals

Not Null

Default Value

id (PK)

INTEGER

0

0

Yes

N/A

store_num

INTEGER

0

0

Yes

N/A

name

CHAR VARYING

255

0

Yes

N/A

t_products:

Name

Type

Length

Decimals

Not Null

Default Value

id (PK)

INTEGER

0

0

Yes

N/A

prod_code

CHAR VARYING

25

0

Yes

N/A

name

CHAR VARYING

255

0

Yes

N/A

prod_category

CHAR VARYING

50

0

Yes

N/A

The Converted Sales Star Schema Model
The Converted Sales Star Schema Model

  • The easiest way to define an entity’s attributes is to right-click it and choose Design Entity from the popup menu.
    That brings up the Entity Design dialog.  It provides tabs for managing attributes (fields), indexes, relations, uniques, and comments:

t_dates - Entity
t_dates - Entity

 

The Completed Sales Star Schema Logical Model
The Completed Sales Star Schema Logical Model



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM