Download - CAT Rose Data Modeler MANUAL
-
8/3/2019 .CAT Rose Data Modeler MANUAL
1/100
sup po [email protected]
http:/ / www.rational.com
Rational the e-development company
Using Rose Data Modeler
Rational Rose
VERSION: 2001A.04.00
PART NUMBER: 800-024463-000
-
8/3/2019 .CAT Rose Data Modeler MANUAL
2/100
U.S. GOVERNMENT RIGHTS NOTICE
U.S. GOVERNMENT RIGHTS. Use, d up lication, or disclosure by th e U.S. Government is subject to
restrictions set forth in the applicable Rational License Agreement and in DFARS 227.7202-1(a) and
227.7202-3(a) (1995), DFARS 252.227-7013(c)(1)(ii) (Oct 1988), FAR 12.212(a) 1995, FAR 52.227-19, or FAR52.227-14, as applicable.
TRADEMARK NOTICE
Rational, the Rational logo, and Rational Rose are trad emark s or registered tr adem arks of Rational Software
Corporation in the United States and in other countries.
Visual C++, Visual Basic, and SQL Server are tra dem arks or registered tradem arks of th e Microsoft
Corporation. Java is a tra dem ark of Sun Microsystems Inc. DB2 is a tr adem ark of the IBM Corporation. All
other names are used for identification purposes only and are trademarks or registered trademarks of their
respective compan ies. Portions of Rational Rose includ e source code from Compaq Compu ter Corpora tion;
Copyright 2000 Compaq Computer Corporation.
U.S. Registered Patent Nos. 5,193,180 and 5,335,344 and 5,535,329. Licensed under Sun Microsystems Inc.'s
U.S. Pat. No. 5,404,499. Other U.S. and foreign patents pending. Printed in the U.S.A.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
3/100
iii
Contents
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Other Resources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Contacting Rational Technical Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xii
Contacting Rational Technical Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xii
1 Introduction: Unifying the Team . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
Team Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Business Analyst Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Application Designer Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Database Designer Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Role Dependencies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
The Data Modeler Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 UML and Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
UML Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Why UML for Data Modeling? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Data Modeling Profile Added to UML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Advantages of the UML Data Modeling Profile. . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Advantages of Rose UML and Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
The Data Model Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Reusing Data Modeling Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Data Modelers Transformation and Engineering Features. . . . . . . . . . . . . . . . . . 9
3 Logical Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Using Rose for Logical Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Class Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Standardized Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Mapping Capabilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Mapping an Object Model to a Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Mapping Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Mapping Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Mapping Packages to Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
-
8/3/2019 .CAT Rose Data Modeler MANUAL
4/100
iv Contents
Mapping Classes to Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Mapping Attributes to Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Mapping Composite Aggregations to Identifying Relationships . . . . . . . . . . . . . 17
Mapping Aggregations and Associations to Non-Identifying Relationships . . . . 18
Mapping Association Classes to Intersection Tables . . . . . . . . . . . . . . . . . . . . . 20
Mapping Qualified Associations to Intersection Tables. . . . . . . . . . . . . . . . . . . . 21
Mapping Inheritance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Transforming the Object Model to the Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Why Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
The Transformation Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
4 Physical Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Data Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Building a New Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Create a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Create a Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30Create a Data Model Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Create Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Create Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Create Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Create Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Create Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Define Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Create Custom Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Create Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Reverse Engineering to Create a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Reverse Engineering Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Reverse Engineering DB2 Databases or DDL . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Reverse Engineering Oracle Databases or DDL . . . . . . . . . . . . . . . . . . . . . . . . 44
Reverse Engineering SQL Server Databases or DDL . . . . . . . . . . . . . . . . . . . . 45
Reverse Engineering Sybase Databases or DDL. . . . . . . . . . . . . . . . . . . . . . . . 45
After Reverse Engineering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
After Building the Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
5 Mapping the Physical Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . 47
Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Mapping the Data Model to an Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
-
8/3/2019 .CAT Rose Data Modeler MANUAL
5/100
Contents v
Mapping Schemas to Packages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Mapping Domains to Attribute Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Mapping Tables to Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Mapping Columns to Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Mapping Enumerated Check Constraints to Classes. . . . . . . . . . . . . . . . . . . . . 49
Mapping Identifying Relationships to Composite Aggregations . . . . . . . . . . . . . 50
Mapping Non-Identifying Relationships to Associations . . . . . . . . . . . . . . . . . . 51
Mapping Intersection Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Mapping Supertype/Subtype Structures to Inheritance Structures . . . . . . . . . . 55
Transforming a Data Model to an Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Why Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
The Transformation Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
6 Implementing the Physical Data Model. . . . . . . . . . . . . . . . . . . . . . 59
Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Forward Engineering a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Forward Engineering Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Forward Engineering to the ANSI SQL 92 DDL . . . . . . . . . . . . . . . . . . . . . . . . . 60
Forward Engineering to a DB2 Database or DDL . . . . . . . . . . . . . . . . . . . . . . . 61
Forward Engineering to an Oracle Database or DDL. . . . . . . . . . . . . . . . . . . . . 61
Forward Engineering to a SQL Server Database or DDL . . . . . . . . . . . . . . . . . 61
Forward Engineering to a Sybase Database or DDL . . . . . . . . . . . . . . . . . . . . . 62
Comparing and Synchronizing a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Synchronization Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
A UML Data Modeling Profile. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
B Object to Data Model Data Type Mapping. . . . . . . . . . . . . . . . . . . . 67
C Data to Object Model Data Type Mapping. . . . . . . . . . . . . . . . . . . . 71
D Database Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
DB2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Connecting to DB2 Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Connecting to Oracle Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Connecting to SQL Server Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80Sybase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
-
8/3/2019 .CAT Rose Data Modeler MANUAL
6/100
vi Contents
Connecting to Sybase Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
-
8/3/2019 .CAT Rose Data Modeler MANUAL
7/100
Figures vii
Figure 1 A Data Model Diagram in Rose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Figure 2 A Class Diagram in Rose. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Figure 3 Classes Map to Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Figure 4 Attributes Map to Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Figure 5 ID-based Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Figure 6 Domain Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Figure 7 Composite Aggregations Map to Identifying Relationships . . . . . . . . . . 18
Figure 8 Associations Map to Non-Identifying Relationships. . . . . . . . . . . . . . . . 19Figure 9 Many-to-Many Associations Map to Intersection Tables . . . . . . . . . . . . 20
Figure 10 Association Classes Map to Intersection Tables . . . . . . . . . . . . . . . . . . 21
Figure 11 Qualified Associations Map to Intersection Tables . . . . . . . . . . . . . . . . 22
Figure 12 Inheritance Maps to Separate Tables . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Figure 13 Transform Object Model to Data Model Dialog Box . . . . . . . . . . . . . . . . 25
Figure 14 A Domain. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Figure 15 A Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32Figure 16 An Identifying Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Figure 17 A Non-Identifying Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Figure 18 A Role . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Figure 19 An Intersection Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Figure 20 A Self-Referencing Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Figure 21 Domain Columns Map to Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Figure 22 Tables Map to Classes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Figure 23 Columns Map to Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Figure 24 Enumerated Check Constraints Map to Classes with . . . . 50
Figure 25 Identifying Relationships Map to Composite Aggregations . . . . . . . . . . 51
Figure 26 Non-Identifying Relationships Map to Associations. . . . . . . . . . . . . . . . 52
Figure 27 Intersection Tables Map to Many-to-Many Associations . . . . . . . . . . . . 53
Figure 28 Intersection Tables Map to Qualified Associations . . . . . . . . . . . . . . . . 54
Figure 29 Intersection Tables Map to Association Classes . . . . . . . . . . . . . . . . . . 55
Figure 30 Transform Data Model to Object Model Dialog Box . . . . . . . . . . . . . . . . 57
Figures
-
8/3/2019 .CAT Rose Data Modeler MANUAL
8/100
-
8/3/2019 .CAT Rose Data Modeler MANUAL
9/100
Tables ix
Table 1 Cardinalities for Foreign Key Constraints. . . . . . . . . . . . . . . . . . . . . . 37
Table 2 UML Data Modeling Profile Stereotypes . . . . . . . . . . . . . . . . . . . . . . 65
Table 3 Analysis Object to Data Model Data Type Mapping . . . . . . . . . . . . . 68
Table 4 Java Object to Data Model Data Type Mapping. . . . . . . . . . . . . . . . . 69
Table 5 Visual Basic Object to Data Model Data Type Mapping . . . . . . . . . . 70
Table 6 SQL 92 Data Model to Object Model Data Type Mapping. . . . . . . . . 72
Table 7 DB2 Data to Object Model Data Type Mapping. . . . . . . . . . . . . . . . . 73
Table 8 Oracle Data to Object Model Data Type Mapping . . . . . . . . . . . . . . . 74
Table 9 SQL Server Data to Object Model Data Type Mapping . . . . . . . . . . . 75
Table 10 SQL Server 7.0 Data to Object Model Data Type Mapping . . . . . . . . 76
Table 11 Sybase Data to Object Data Type Mapping. . . . . . . . . . . . . . . . . . . . 77
Tables
-
8/3/2019 .CAT Rose Data Modeler MANUAL
10/100
-
8/3/2019 .CAT Rose Data Modeler MANUAL
11/100
xi
Preface
This man ual p rovides an introdu ction to Rational Suite. Rational Suite d elivers a
comprehensive set of integrated tools that embod y softw are engineering best
practices and span the entire software development life cycle. Rational Suite's
un paralleled level of integration im proves comm un ication both w ithin teams andacross team boun daries, redu cing d evelopment time and imp roving softw are quality.
Audience
This man ual is intend ed for:
I Database developers and administrators
I Software system architects
I Softw are engineers and program mers
I Anyon e wh o m akes design, architecture, configur ation man agement, and testing
decisions
This manual assumes you are familiar with database modeling concepts and the
life-cycle of a software dev elopm ent project.
Other Resources
I Online H elp is ava ilable for Rational Suite.
From a Suite tool, select an option from the Help menu .
I All manuals are available online, either in HTML or PDF format. The online
manu als are on th e Rational Solutions for Windows Online Documentation CD .
I For more information on training opp ortun ities, see the Rational University Web
site: http://www.rational.com/university.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
12/100
xii Preface
Contacting Rational Technical Publications
To send feedback abou t d ocumentation for Rational produ cts, please send e-mail to
our technical pu blications dep artment at [email protected].
Contacting Rational Technical Support
If you have q uestions about installing, using, or m aintaining this prod uct, contact
Rational Technical Su pp ort as follow s:
Note: When you contact Rational Techn ical Su pp ort, please be p repared to sup ply the
following information:
I Your n ame, telephone nu mber, and comp any nam e
I Your comp u ters make and m odel
I Your op erating system and version n um ber
I Product release number and serial number
I Your case ID nu mber (if you are following u p on a previously-reported problem)
Your Location Telephone Fax E-mail
North America (800) 433-5444
(toll free)
(408) 863-4000Cup ertino, CA
(781) 676-2460
Lexington, MA
Europe, Middle
East, Africa
+31 (0) 20-4546-200
Netherlands
+31 (0) 20-4545-201
Netherlands
Asia Pacific +61-2-9419-0111Australia
+61-2-9419-0123Australia
-
8/3/2019 .CAT Rose Data Modeler MANUAL
13/100
1
1Introduction: Unifying theTeam
What tru ly makes a team is a group of people working together to accomp lish a
common overall goal. Sometimes the team consists of only two peop le, other times it
consists of hu nd reds, but regardless of the nu m ber, the team m ust be u nified in itsefforts. A development team is a specialized team in which the various members hold
different responsibilities in the development life cycle. In solving business problems,
there are three distinct roles in a developm ent teamthe bu siness analyst, the
app lication d esigner, and the d atabase designer.
Team Roles
For the scope of this book, the role of business analyst is performed by
business-process analysts, systems analysts, and those wh o capture an d review
requirements for a bu siness process. The role of ap plication d esigner is p erformed by
app lication d evelopers, software engineers, and th ose who design an d review
app lication d esigns. The role of database d esigner is p erformed by d ata d evelopers,
database ad ministrators (DBAs), data an alysts, and th ose wh o design an d review
relational databases.
Business Analyst Role
In the development process, the business analysts responsibility is to interview the
end-user or client to u nd erstand t he overall business problem, analyze the bu siness as
it currently is, identify the defects in the current processes that are causing the overall
business problem, and mod el the business as it could be. The business analyst
captures the end-users requirements and the process improvements in businessm odels. Business mod els divide the p rocess into comp onents of events, people or
things, and order the p rocess using use-case, sequence, and activity m odels.
Application Designer Role
The respon sibility of the ap plication d esigner is to gen erate cod e and build an
application based on the business analysts business models. The application designer
uses object-oriented conceptual models and creates classes from the use cases,organizing the classes into an object mod el structure u sing class mod els. Then the
-
8/3/2019 .CAT Rose Data Modeler MANUAL
14/100
2 Chapter 1 - Introduction: Unifying the Team
app lication d esigner assigns the class mod el structure to a comp onent an d generates
code, building a n ap plication. The app lication d esigner is also respon sible for creating
classes that can access the data in the database.
Database Designer Role
The respon sibility of the d atabase designer is to p rovide a storage container for all
data applying to the built application, and provide a method to maintain the data
structures integrity, based on the business analysts business models. The database
designer u ses logical and ph ysical data mod els; creates schemas, tables, and other
database elements from the use cases or classes; and organizes the schemas, tables,
and database elements into a data model structure. Then, using the data modelstructure, the d atabase designer selects a d atabase man agement system (DBMS) and
generates a d ata-defined language (DDL) script, building a d atabase. The database
designer m ust also ensure the ap plication designer can m ap the ap plication classes to
the correct tables to access the data for the application.
Role Dependencies
Due to their differences in responsibilities and knowledge, each team member solves
a bu siness p roblem using a d ifferent m ethod. The bu siness an alyst solves the problem
by revising the p rocesses; the app lication designer solves the p roblem by generating
code; and the d atabase designer solves the problem b y controlling the d atabase and
data accepted into it. For any one of these meth ods to solve the o verall business
problem, the other two m ethods mu st be included in the p lanning.
A dependency exists between each of the different roles to solve the overall business
problem. Mod eling the business p rocesses alone, does n ot p hysically solve the
business problem. Generating code is useless withou t the d atabase to host the d ata.
Data is meaningless without an ap plication to access it. Each role is dep end ent on the
other; no one role can completely solve the business problem. The application
designer and database designer need the business models with the modeled
requirements. The bu siness analyst needs to confirm that the database d esign meets
all the required business rules. If the ap plication designer need s an add itional field ,
the database designer needs to illustrate what impact such a change would have on
the d atabase structure.
These d epend encies are especially evident in iterative developmen t, wh ere chang e is
constant an d comm un ication b etween the team s is essential. H owever, wh en each of
these roles is using a different notation, communication is difficult. A unified
language between the teams can reduce miscommunication and development time
wh ile imp roving qu ality.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
15/100
The Data Modeler Solution 3
The Data Modeler Solution
Rose un ified the roles of bu siness analyst and app lication d esigner in the p revious
releases of the Rose software. With th e ad d ition of Data Mod eler, Rose u nifies all threeroles of the development team, allowing them to commu nicate freely through their
individual models using the common language of UML.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
16/100
-
8/3/2019 .CAT Rose Data Modeler MANUAL
17/100
5
2UML and Data Modeling
Contents
This chapter is organized as follows:
I UML Introduction on page 5I Why UML for Data Modeling? on page 5I Advant ages of Rose UM L and Data M odeling on page 7
UML Introduction
Teams need one tool, one methodology, and one notation. Since its inception, the
Unified Modeling Language (UML) has been un ifying m embers of a developm ent
team for faster and higher qu ality ap plications. But UML allowed only bu siness
analysts and app lication d esigners to comm un icate with each other, database
designers were exclud ed because they u sed a different kind of notation. Rose ad ds a
UML profile to accomm odate entity/ relationship (E/ R) notation w ith the a dd ition of
Data Modeler, allowing the d atabase designer to commu nicate with th e bu siness
analyst and app lication designer, making the UML a truly un ifying langu age.
Why UML for Data Modeling?
The UML offers a standard notation very similar to Peter Chens E/ R notation. Like
Chens E/ R the UML is based on building stru ctures u sing entities that relate to one
ano ther. By ad din g the d ata m od eling p rofile, UMLs ability to mod el an entire system
is increased, allowing you to mod el not only logical mod els, but p hysical data m od elstoo, mapping your applications and your databases.
Data Modeling Profile Added to UML
A UML profile is an Object Managem ent Group (OMG) approved method of adding
to UML for a specific subject, without altering the UML metamodel. UML profiles
add ed to UML use customized stereotypes an d tagged valu es based on th eir subjects
concepts and terminology.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
18/100
6 Chapter 2 - UML and Data Modeling
The UML data m od eling profile allows you to mod el databases based on data
mod eling stereotypes add ed to existing UML structures. Stereotypes ad d ed to U ML
structures such as comp onents, packages, and classes allow you to m odel d atabases,
schem as, and tables. Stereotypes ad ded to UML associations allow you to mo d elrelationships. Strong relationship s are m odeled with the ad dition of stereotypes to
composite aggregations. Finally, stereotypes added to UML operations allow you to
mod el primary key constraints, foreign key constraints, uniqu e constraints, and
add itional d atabase concepts such as check constraints, triggers, and stored
proced ures. Refer to Appendix A for a listing of database concepts an d the UML d ata
modeling profile stereotypes.
Advantages of the UML Data Modeling Profile
The UML d ata m od eling profile has four d istinct ad vantages consisting p rimarily of
its compatibility with business modeling and applications. First, the UML focuses on
the overall architecture of the system allowing you to m odel h igh-level business
processes, applications, and their imp lemen tation.
Second , the UML separates logical and ph ysical design, making m app ing you r
database to you r app lication, and chan ge man agement easier. When you sep arate theph ysical design from th e logical d esign, you can customize the p hysical design to
accomm odate you r specific database managem ent system (DBMS) and app ropriate
levels of normalization, while the logical design remains a high-level design
app ropriate for an overall view of you r d atabase or ap plication. The separ ate designs
also allow you to see the effect changes have on the design before the changes are
comm itted. The chan ge ma y be ap plied easily to the logical design , bu t sp ecific DBMS
structures may restrict the sam e chan ge wh en it is app lied in the p hysical mod el;therefore, the change wou ld n ot be acceptable for the p hysical design.
Third, the UML add resses behavior mod eling, allowing you to m odel operations and
constraints, including business rules.
Finally, the UML is com p atible with object-oriented notat ion. It is the d ifference in th e
object-oriented notation for some applications and E/ R notation for databases that
hinders communication between the database designer and the other team members.
Database designers are isolated, often excluded from the communication of crucial
design d ecisions, and not able to comm un icate to other team m embers design
decisions that they themselves make. The UML eliminates this communication failure
by allowing all the team m embers to commu nicate their design decisions in the same
notation. This UML data modeling profile enables Rose to create a UML-based data
model.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
19/100
Advantages of Rose UML and Data Modeling 7
Advantages of Rose UML and Data Modeling
Rose UML offers d istinct advan tages wh en d ata m odeling. These adv antages are the
introdu ction of th e da ta m odel d iagram to Roses set of UML diagrams, reusability ofdata m odeling elem ents, and Data Mod eler s engineering capabilities.
The Data Model Diagram
For each type of d evelopm ent m odelthat is business mod els, application mod els,
and data m odelsRose u ses a specific type of diagram .
I Business m odelsUse case d iagram , activity d iagram, sequence d iagram
I App lication m odels or logical d ata m od els Class d iagram
I Physical data m odelsData m odel d iagram
An ad vantage the d ata m odel diagram offers database designers is the ability to
m odel using term s and structures already familiar to them , such as colum ns, tables,
and relationships. Also, the data m odel d iagram comp letes Roses set of diagram s to
m odel the wh ole system, closing the chasm in d evelopm ent between d atabase
designers and app lication d evelopers.
The data m odel diagram visually represents the phy sical data m od el, so to work in
your p hysical data m odel you m ust create a new or activate an existing d ata mod el
diagram . Rose provides this ad ditional diagram to redu ce the confusion between
object model and data model items, and to support features unique only to Data
Modeler such as su pp orted DBMSs, key migration, and schema migration.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
20/100
8 Chapter 2 - UML and Data Modeling
Figure 1 A Data Model Diagram in Rose
Reusing Data Modeling Elements
Another ad vantage of using Rose is it enables you to reuse your m odeling elements to
app ly to other mod eling needs. You can d o this by using frameworks. Framew orks are
files that act as templates for other models. You can create a framework based on your
entire .md l file using th e Frameworks Wizard. Because framew orks w ork as a
temp late, you can create standard fram eworks u sed for your bu siness. Framew orks
are especially useful wh en th e mod el contains d omains, because d om ains can enforce
your business standard s at the column level. It is the combination of framew orks
containing stand ard entities and dom ains that act as a fou nd ation to enforce specific
stand ards of your bu siness. For example w hen mod eling th e business process of a
clinic you need certain entities like patient, physician and clinic, but you will also
need to specify that each patients first name cannot exceed 20 characters and each
ph ysician m ust h ave a social security nu mber of num eric value and exactly 9 d igits in
length. Creating a framew ork that contains these entities and d omains that su pp ort
these standard s will allow you to reuse these standard s repeatedly.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
21/100
Advantages of Rose UML and Data Modeling 9
Data Modelers Transformation and Engineering Features
With the UML d ata mod eling p rofile, Data Modeler can m ap from the d ata m odel
diagram known as the data model to the class diagram known as the object model an d
vice versa. This map ping enables Data Modelers transformation and engineering
features to create a round-trip engineering effect.
Transformation Between the Object Model and Data Model
The relationship between logical classes and physical tables provides the basis for the
mapping between a logical data model and a physical data model. This mapping
autom atically occurs wh en you use th e Transform Object Mod el to Data Model orTransform Data Model to Object Model features. These features map the classes and
tables in a one-to-one map ping, w ith the exception of d enormalization issues and
DBMS restrictions.
Forward and Reverse Engineering the Data Model
The relationship between logical classes and physical tables can also act as a mapping
between a DBMS and an object-oriented language like Java or C++, using forwardengineering or reverse engineering features. When you reverse engineer a DBMS
schema to a data model and then transform that data model to an object model, the
object m od el transforms to the Analysis langu age. By reassigning the langu age of
your object model from Analysis to Java or C++, Rose maps your DBMS database to
an ap plication mod el that can be u sed to bu ild an ap plication. The same p rocess
app lies to map ping the ap plication to the d atabase the object m odel that bu ilt the
app lication using a C++ langu age, can be reassigned to the An alysis language. Thenthat object model can be transformed to a data model, and that data model can be
forward engineered to create a schema in a DBMS.
Comparing and Synchronizing the Data Model
If you are working with a legacy DBMS database and app lication, you can still use
these round -trip eng ineering featu res. How ever, instead of forward engineering to a
DBMS, you can use Data Mod elers Comp are and Synchronization feature to u pd ateyour existing D BMS database, synchronizing your DBMS database w ith th e
transformed object m odel that bu ilt the ap plication.
All of Data Mod elers featu res workin g together create a roun d -trip engineering effect
w ith th e map ping of logical classes to ph ysical tables being th e key to m app ing the
database to th e app lication.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
22/100
3L i l D M d li
-
8/3/2019 .CAT Rose Data Modeler MANUAL
23/100
11
3Logical Data Modeling
Contents
This chapter is organized as follows:
I Introduction on page 11I Usin g Rose for Logical Data M odeling on page 11I Mapping an O bject M odel t o a Data M odel on p age 13I Transforming the O bject M odel t o the Data M odel on p age 24
Introduction
Logical data m odeling is an essential step in m od eling a database. The logical data
m odel gives an overall view of the captu red bu siness requirements as they p ertain to
d ata entities. You can u se Rose for logical data m od elin g and custom ize you r logical
data model to transform to a physical data model.
Using Rose for Logical Data Modeling
The previous chapter discussed t he ad vantages of using Rose and the UML in general
for d ata mod eling. This chapter discusses the advan tages of using Rose and the UML
for logical data modeling. These advantages are Roses ability to graphically depict a
logical data m odel using th e class diagram, and Roses stand ardized notation and
m app ing capabilities.
Class Diagram
The class diagram can graph ically dep ict a logical data m odel because it uses a
structure similar to a logical data m odel. When you create a logical data m odel, you
start by identifying high-level entities. The class diagram also identifies high-level
entities. In U ML term inology th ese entities are called classes. Rose allows you to
assign the stereotype to these classes for further distinction.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
24/100
12 Chapter 3 - Logical Data Modeling
The n ext step is to assign attributes to these entities to id entify them to the system.
This is the same step you take wh en m odeling in the class diagram; you assign
attributes to the classes.
The final step that both the logical data m odel and the class diagram share is to relatethese entities or classes to other entities or classes using associations.
Figure 2 A Class Diagram in Rose
Standardized Notation
Another ad vantage is Rose uses the comm on n otation of the UML. As was d iscussed
in Chapter 2, UM L and Data M odeling, the UML data m odeling profile add ed
stereotypes to UML elements relating to d ata m odeling terminology. These same
UML elemen ts are used in the class diagram and with these stereotypes you can
understand the mapping from the logical data model to the physical data model.
Mapping Capabilities
In Rose data modeling terminology, a class diagram is also known as an object model.
Object mod els serve two pu rp oses. A class diagram or object m odel can act as a
logical data model, but an object model can also act as a model to capture a conceptual
-
8/3/2019 .CAT Rose Data Modeler MANUAL
25/100
Mapping an Object Model to a Data Model 13
view of an app lication. An object m odel is necessary if you wan t to m ap an
app lication to a d atabase. It is the object model m app ing to the p hysical data m od el
that is the basis for m app ing the ap plication to the d atabase.
Mapping an Object Model to a Data Model
Rose allows you to take a step beyond identifying high-level entities, attributes, and
associations. It allow s you to create a robu st logical data m odel that can ma p more
precisely to a p hysical da tabase. Custom izing the object m od el for you r d atabase
helps to m anage change, thereby decreasing the im pact a change of requirements can
have on the existing m odel. If the object m odel map s to the d ata mod el, and changesor enhan cements are m ade to the object m odel, the same chang es or enhancements
can be app lied t o the d ata m od el. You can m od el you r object mod el specifically to
m ap to a d atabase by mod eling you r object model elementswith the exception of
components and operationsto map to data model elements.
Mapping Components
Components represent the actual application language. In Rose terminology, acompon ent rep resents a softw are mod u le (source cod e, binary code, executable, or
DDL) with a well-defined interface. According to the UML Data Modeling profile, a
compon ent map s to a database, but this map ping is only for reference pu rposes; in
actual object m odel to d ata mod el transformation, comp onents are ignored.
Although Rose gives you th e option of several comp onent langu ages to assign to you r
logical package, the Data Mod eler ad d-in is compatible with only th ree of those
comp onen t langu agesJava, Visual Basic, and Analysis. The classes that you w ant to
map to tables in the data model must use one of these three component languages to
be transformed to a data model. If you want to use a component language not
com patible with Data Modeler, it is recomm end ed you create a separate object m odel
using your desired component language, and map that model to an Analysis object
model that is used for transformation purposes.
Mapping OperationsAccording to the UML Data Mod eling profile, operations m ap to various constraints;
how ever, just like compon ents, operations are ignored in the transformation p rocess.
Op erations are th e behavior of a class, and can be useful to d atabase designers
because they can be used as a basis for identifying index items, possible triggers, and
other constraints.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
26/100
14 Chapter 3 - Logical Data Modeling
Mapping Packages to Schemas
In a class diagram, a logical package is an optional element, but when using Data
Modeler, a logical package is required. A logical package is considered to be the
primary container of you r object mod el, so it is the level at w hich Data Mod eler
initiates the transformation process. You must group your classes in a logical package
to transform them to a d ata mod el. Data Modeler allows y ou to transform one logical
package at a time and generates one schema for each logical package transformed.
Mapping Classes to Tables
Classes are high-level entities that can have two states of existencetransient and
persistent. It is the persistent classes that map to physical tables, because persistent
classes can work as persistent data storage, existing even after the application has
completed its process. All persistent classes can map to tables using a one-to-one
mapping, unless you are using an inheritance structure in your model, then the
mapp ing could be a on e-to-many mapp ing.
Figure 3 Classes Map to Tables
Mapping Attributes to Columns
Persistent attributes m ap to column s in a one-to-one mapp ing. In your mod el, youmay h ave mu ltiple attribu tes that map to one column , but in th e transformation, Data
Modeler transforms one-to-one for every attribute. Data Modeler ignores
non-persistent attributes like d erived values.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
27/100
Mapping an Object Model to a Data Model 15
Figure 4 Attributes Map to Columns
Rose allows yo u to customize your attribute m app ing by m app ing specific attributes
to speciality column s like primary k ey colu mn s, ID-based colum ns, or d omain
colum ns, and to m ap attribute types to specific DBMS data typ es.
Primary Keys
You can m ap an attribute to be a primary key colum n by assigning the attribute to be
a candidate key. A candidate key is an attribute tagged as part of object identity, whichData Modeler transforms to a p rimary key in a table du ring the object to d ata mod el
tran sform ation p rocess. You can a ssign one or m ore attributes to be cand idate keys. If
you assign m ore than on e cand idate key to a class, those attributes will transform to a
composite primary key in the data model.
ID-based Columns
If you d o not d esignate a cand idate key in you r p arent classes, Data Modeler will
autom atically generate a primary k ey for each p arent class when you tran sform yo ur
object mod el to a data m odel. Data Modeler generates this key by ad ding an
add itional colum n to the table (called an ID-based column) and assigns it as a primary
key. An ID-based column uses u nique system-generated values.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
28/100
16 Chapter 3 - Logical Data Modeling
Figure 5 ID-based Columns
ID-based Key vs. Candidate Key
An ID-based key is considered to have distinct advantages over a candidate key, when
choosing a unique identifier for a table. One of these advantages is an ID-based keys
ability to maintain a constant size, because it is a system-generated value.
Another ad vantage is that an ID-based key u ses one colum n, whereas a cand idate key
may need to use m ultiple colum ns to u niquely identify the table. Using a single
colum n results in a simp ler, cleaner d atabase d esign, becau se in relationsh ips only one
colum n not m ultiple colum ns m igrates as a foreign key.
Using mu ltiple colum ns also m akes it more d ifficult to ensure u nique en tries in th e
table. For examp le, in the T_Physician table you m ay u se nam e and add ress as
cand idate keys, but you may encounter du plications if one physician has the same
nam e as another ph ysician w ho lives at the same add ress, such as w ould occur in a
mother-dau ghter situation. To avoid th is you m ay u se ssn as the candid ate key, but
then there is a greater likelihood of this information being entered incorrectly. A
system-generated ID-based key redu ces these problems.
Domain Columns
You can m ap a ttribu tes to domains if you already have a domain defined in your data
m od el. Refer to Chapter 4, Physical Data Modeling for more information on creatingdomains in the data model. Domains can act as a user-defined data type
correspond ing to a sp ecific DBMS langu age. It is imp ortant th at you assign your
dom ain to the same DBMS language th at you w ill use for you r d ata mo del, so your
domain will be compatible with your database.
You m ap attributes to dom ains by setting the attribute typ e to the nam e of you r
dom ain. In the transformation process, the attribute transform s to a colum n u sing the
domain name as the data type, thereby using the domains defined data type andsettings.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
29/100
Mapping an Object Model to a Data Model 17
Figure 6 Domain Columns
Data Type
Data Modeler automatically maps your attribute type to an appropriate column data
type of you r DBMS or the AN SI SQL 92 standard . If you wan t you r attribute to
transform to a particular data type in the data model, you need to designate the
correct attribute type that ma ps to y our d esired d ata typ e. Refer to Appendix B for a
listing o f the data typ e map ping. Furtherm ore, if you w ant to sp ecify a d efault value
for you r colu mn , you can sp ecify it in th e attributes initial value wh ich map s to a
colum ns d efault v alue.
Mapping Composite Aggregations to Identifying Relationships
Aggregations by value, know n as composite aggregations, map to identifying
relationships in the d ata mod el. Comp osite aggregations consist of a wh ole and a part,
indicating a strong relationship, w herein the p art cann ot exist withou t the w hole.
You u se a composite aggregation wh en you have on e instance of a parent class that
owns a dependent class. The dependent class is defined as the part and must be
accomp anied by a w hole or p arent class. If the p arent class is deleted its composite
parts m u st be d eleted also, therefore the p arent class m ust u se the m ultiplicity of 1.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
30/100
18 Chapter 3 - Logical Data Modeling
Figure 7 Composite Aggregations Map to Identifying Relationships
Mapping Aggregations and Associations to Non-Identifying
RelationshipsAggregations by reference (known as aggregations) and associations map to
non-identifying relationships, with the exception of many-to-many associations. Both
of th ese join tw o classes withou t using a st rong relationsh ip. You u se an
aggregation when you have multiple instances of a parent class owning a dependent
class. You use an a ssociation w hen you hav e classes tha t are ind epen den t of each
other. An aggregate or association can be mandatory, where a parent class is required,
by u sing a m ultiplicity of 1 or 1..n. An association can also be optiona l, w here a p arentclass is not required, by using a multiplicity of 0..n.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
31/100
Mapping an Object Model to a Data Model 19
Figure 8 Associations Map to Non-Identifying Relationships
Many-to-Many Associations
Many-to-man y associations m ap to intersection table stru ctures wh ere all the colum ns
of the intersection table are primary/ foreign keys. In the transformation process, Data
Modeler reads the tw o classes in the many -to-many relationship and creates two
separate tables, then it joins these two tables with tw o id entifying relationships to a
system-generated table called an intersection table. As part of generating the
identifying relationships, the two tables primary keys migrate to the intersection
table as primary/ foreign keys.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
32/100
20 Chapter 3 - Logical Data Modeling
Figure 9 Many-to-Many Associations Map to Intersection Tables
Mapping Association Classes to Intersection Tables
An association class that links to a m any-to-m any association map s to an intersection
table structure w here the intersection table contains one or m ore add itional colum ns
that are not primary/ foreign keys. An association class is an additional class attached
to an association that can store properties and op erations shared by the tw o
individual classes of the association. The reason the classes share these properties and
operations is because they cannot be stored in either of the classes. Data Modeler
transforms association classes and their properties, but association class operations
are ignored by Data Mod eler. In the object to d ata m odel transformation, Data
Modeler transforms the two classes to tables and joins them to an intersection table
with tw o identifying relationships, migrating the pr imary keys of each ind ividu al
table to the intersection table as primary/ foreign keys. Then Data Mod eler ad ds th e
attributes of the association class as columns in the intersection table.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
33/100
Mapping an Object Model to a Data Model 21
Figure 10 Association Classes Map to Intersection Tables
Mapping Qualified Associations to Intersection Tables
A qualified association map s to an intersection table that contains an ad ditional primarykey. Qualified associations are many-to-many associations that use qualifiers. A
qu alifier is an at tribute ap plied to on e sid e of an association that w orks as an id entifier
to return a specific set of objects at the opposite end of the association. Similar to the
tran sformation process of association classes, the tw o classes in a qu alified a ssociation
transform to individu al classes and are joined w ith identifying relationships to th e
intersection table. The qualifier of the qualified association is transformed to a
-
8/3/2019 .CAT Rose Data Modeler MANUAL
34/100
22 Chapter 3 - Logical Data Modeling
primary key in the intersection table. The intersection table then contains the
primary / foreign keys of the tw o tables, and th e add itional primary key colum n
generated by the transformation of the qualifier.
Figure 11 Qualified Associations Map to Intersection Tables
Mapping Inheritance
Inheritance structures or, in UML terminology, generalization structures can map to
separate tables or on e table; how ever, Data Mod eler on ly transforms inh eritance
structures to separate tables. Inheritance structures associate a general parent class
with more specific child classes. The child classes share the same attributes of the
parent class and add additional attributes that affect only their specific class.
Wh D M d l f i h i bl bl
-
8/3/2019 .CAT Rose Data Modeler MANUAL
35/100
Mapping an Object Model to a Data Model 23
When Data Mod eler transforms an inheritance structure to separate tables, one table
is created for each participating class. The parent table is joined to each of the child
tables with a zero-to-one or one identifying relationship. Therefore, each child table
contains a prim ary/ foreign key related to th e parent tables p rimary key.
Figure 12 Inheritance Maps to Separate Tables
You can m anu ally m ap an inh eritance structure to one class after you transform your
object m od el to a d ata m odel. You d o this by d eleting th e tw o identifying
relationships between th e tables; this deletes the primar y/ foreign keys in your tables.
Then, you m ove the individ ual colum ns from th e two child tables to the intersection
table. Finally, you set the child classes in your object model to transient.
Important: If you manu ally map your inheritance structure to one table, you will have
to repeat this process each tim e you tran sform your o bject m odel to the d ata m odel or
vice versa. If you d o not m anu ally remap your m odels each time you transform, you
m ay encounter conflicts with you r ap plication and DBMS mapp ing.
Transforming the Object Model to the Data Model
-
8/3/2019 .CAT Rose Data Modeler MANUAL
36/100
24 Chapter 3 - Logical Data Modeling
Transforming the Object Model to the Data Model
After customizing you r object mo del to map to a da ta mod el, you can transform you r
object mod el to generate a data m od el that reflects the ma pp ing you specified. Youtransform the object m odel to a d ata mod el for various reasons, but regard less of the
reason the p rocess is the same.
Why Transform
You can transform an ob ject m od el to generate a n ew DBMS datab ase, or to
synchronize the object mod el and the d ata mod el, sharing information w ith data
designers.Transform ation is a necessary step in generating a new database that sup ports the
app lication. When you transform the object m od el, a data mod el is generated. This
data m odel can then be forward engineered to create a DBMS database.
Also, when you transform the object m odel to a data m odel you are synchronizing th e
mod els, because each of the generated elements in the data m odel map to one or m ore
elements in the object model. This synchronization creates consistency in the system
and allows for the sharing of information between th e app lication designers and the
database d esigners throu gh the object mod el.
The d atabase designer is depend ent on the object m odel of the app lication, because it
is through this structure that the data is accessed. Therefore the tran sformation of the
object model to the d ata m odel can be the m eans of comm un icating app lication
changes that may affect the DBMS database. The application designer can share
information, especially changes, by tran sforming the changed object m odel to a d ata
mod el. The d atabase designer can review the changes an d see the impact such
chan ges would have on the DBMS database without u pd ating the d atabase. You can
share the object m odel changes with th e d atabase designer by u sing the Data Mod eler
Transform Object Model to Data Model feature.
The Transformation Process
All the object mod el elements m entioned in the m app ing section of this chap ter
transform to the data model items they are mapped to, except components and
operations.
Even though components map to a database, components themselves do not
transform to the d ata m odel. In fact, in p lace of a comp onent, you m u st create a new
database before transform ing your object m odel to a d ata mod el. When you create a
new d atabase, you can specify a database name an d assign th e database target to a
supported DBMS or the ANSI SQL 92 standard. Refer to Chapter 4 Physical DataModeling for information on creating a new d atabase.
Transform Object Model to Data Model Dialog Box
-
8/3/2019 .CAT Rose Data Modeler MANUAL
37/100
Transforming the Object Model to the Data Model 25
Transform Object Model to Data Model Dialog Box
The process of tran sform ing an ob ject m od el begins from the Transform Object Model to Data
Model dialog box. From this dialog box, you can select or specify a schema name for
your data mod el, the database nam e you will be using, prefixes for your tables, andw hether or not you wan t to create indexes for your foreign k eys.
Figure 13 Transform Object Model to Data Model Dialog Box
Destination Schema
If you select an existing schema name, that existing schema will be overwritten by the
new schem a generated from y our object mod el. If you specify a n ew schema nam e,
Data Modeler will generate a new schema using that name without overwriting any
existing schema you may have assigned to your database.
Target Database
-
8/3/2019 .CAT Rose Data Modeler MANUAL
38/100
26 Chapter 3 - Logical Data Modeling
Target Database
When you specify a Target, which is the database nam e, you m ust select a nam e from
the list. If you leave the Target blank , your specification d ialog boxes w ill be
un available to you after the transformation is com plete.
Prefixes
You can d istinguish y ou r new tables from t he classes in you r object mod el by
assigning a prefix to them. It is recommended you use a prefix. The prefix will appear
before each of your tables nam es and w ill help you avoid d rawing relationship s
between a table and a class or vice versa, mak ing you r object mod el or data m odel
invalid.
Indexes for Foreign Keys
As an additional convenience, you can specify to have an index built for every foreign
key in your new data model. When you click OK on this dialog box, the actual
transformation process begins.
Transforming Packages, Classes, and Associations
In the actual transformation process, Data Modeler generates a schema using th e
schema name you designated in the Transform Object Model to Data Model dialog box. If you
designated an existing schema nam e, Data Mod eler up d ates that existing schem a.
Then Data Modeler reads the object model package looking for persistent classes and
relationsh ips betw een p ersistent classes. These classes are transform ed to t ables in the
data m odel schema.
At this point, Data Mod eler looks for an y attributes assigned as candid ate keys in the
persistent classes. If there are attribu tes assigned as cand idate keys, each of these keys
are transformed to prim ary keys and are add ed to the tables primary key constraint
with un ique ind exes being created for each constraint. If none of the attributes are
assigned as a candid ate key, Data Modeler add s an ID-based column to the p arent
tables and assigns th at colum n as the p rimary key for the t able. This process ensures
that all the p arent tables have a primary key, so all relationsh ips w ill be valid.Thereafter, all other attributes are added to the table as columns.
Once the tables are established, Data Modeler transforms the object model
associations to data model relationships. Each of the relationships receives the
app ropriate cardinality as d esignated in th e object m odel. Also, each of the
relationships migrates a foreign key to the child table based on th e primar y key in the
pa rent table. If you specified to have a n ind ex built for each foreign key, these ind exes
are generated after the foreign key migrates from the parent to the child table. Thisalso includes any special structures like those that transform to intersection tables.
The entire p rocessing time of the tran sformation d epend s on the n um ber of classes to
-
8/3/2019 .CAT Rose Data Modeler MANUAL
39/100
Transforming the Object Model to the Data Model 27
p g p
be transformed in your object m odel.
After the Transformation ProcessWhen the transformation process is com plete you can m ake any chan ges necessary in
the d ata m odel to m ap the object and d ata m odel m ore precisely. You can also see the
object mod el to data mod el mapping using the M apped From property in the data
m od els Table or Colum n Specifications.
Mapped From
When you transform an object mod el or a d ata mod el the Mapped From text box isautom atically p opu lated w ith the nam e of the referencing object m odel element. This
is a protected field so you cannot control it m anu ally. When you are working with a
data m odel you can kn ow wh ich class or attribu te a table or colum n m aps to, even if
the class has been renamed , by reviewing th e Mapped From text box for you r p articular
table or colum n. This property is also helpful becau se map ping object mod el elements
to d ata m od el elements is not always a one-to-one m app ing. You can have instances
w here your d ata mod el is den ormalized and a class and a table loses their one-to-onem app ing, because of the m ovement of colum ns between tables. In an instance like
this, the Mapped From text box can h elp you track the source of an entity regard less of
the changes made to it.
-
8/3/2019 .CAT Rose Data Modeler MANUAL
40/100
4Physical Data Modeling
-
8/3/2019 .CAT Rose Data Modeler MANUAL
41/100
29
4
Contents
This chapter is organized as follows:
I
Introduction on page 29I Data M odels on page 29I Building a N ew Data M odel on p age 30I Reverse Engin eering to Create a Data M odel on page 44I After Bu ilding the Data M odel on page 45
Introduction
Physical data m odeling is the next step in mod eling a database. The p hysical data
m odel uses the logical d ata mod els captu red requirements, and ap plies them to
specific database management system (DBMS) languages. Physical data models also
capture the lower-level detail of a DBMS database. Rose Data Modeler calls this
ph ysical data m odel, the d ata mod el.
Data Models
When w orking with Data Mod eler, the ph ysical d ata mod el is known as a data m odel
and is graphically represented in the data m odel diagram. The data mod el diagram is
customized from other UML diagrams allowing the d atabase designer to w ork w ith
already familiar concepts and terminology.
Using Rose you can create a data m odel in a variety of ways. The p revious chap terexplained how to create a data m odel by transforming an object mod el, but you can
also create a data m odel by bu ild ing a new mod el, or by reverse engineering a
database or DDL file.
Building a New Data Model
-
8/3/2019 .CAT Rose Data Modeler MANUAL
42/100
30 Chapter 4 - Physical Data Modeling
You can bu ild a new data m odel by m anu ally creating the elements of a data m odel.
This section d escribes the process of build ing a data m odel u sing Data Mod eler.
Create a Database
A database is the im plementation comp onent for a d ata mod el. Each database is
assigned to a target. The target refers to the actual AN SI SQL 92 stand ard or sup por ted
DBMS and DBMS version y ou w ant to u se. You can s pecify you r target u sing the
Database Specification. The d efault target is AN SI SQL 92. Wh en y ou d esignate a
target only the elements su pp orted by your d esignated DBMS version or ANSI SQL
92 standard are supp orted in your d ata mod el.
Also when you create a database, a Schemas folder is automatically created for you in
your Rose browser, so you can store your schemas. Each d atabase can contain on e or
more schemas.
Create a Schema
A schema is the primary container for a d ata mod el and contains all the tables of thedata model. Data Modeler requires a schema for the data model to exist. Therefore, all
elements in a d ata mod el, with the exception of dom ains are requ ired to be assigned
to a schema .
Before you create tables for you r schema, you shou ld assign you r schema to a
database. When you assign your schema to a database, only the elements supported
by your databases designated DBMS version or ANSI SQL 92 standard are
supported. If you do not assign your schema to a database, your schema will use the
ANSI SQL 92 standard as a default.
Create a Data Model Diagram
It is necessary for you to create a data m od el diagram if you w ant to create
relationships, because you must draw your relationships on the data model diagram.
When you create a data mod el diagram an d enable it, Data Modelers customized tool
set and th e correspon d ing m enu comm and s are mad e available to you. Refer to
Chapter 2, Data M odeling and UM L for more information on data model diagrams.
Create Domains
Domains act as a template for colum ns you use frequ ently and can be ap plied to
colum ns and attributes alike as a custom ized d ata type. For examp le, when m odeling
tables for emp loyees, the social security nu m ber will always be n eeded. Instead of
recreating the ssn column settings repeatedly, you can create a domain that contains
th i l it b tti d i th t d i th d t t f th
-
8/3/2019 .CAT Rose Data Modeler MANUAL
43/100
Building a New Data Model 31
the social secu rity num ber settings an d assign that d omain as the d ata type for the
colum n. Refer to Figure 14 on page 31.
Creating a d omain is optional and is depend ent upon the domain sup port for yourDBMS. When you create a do main, you mu st first create a dom ain package as a
container for your d oma in. You can assign y ou r d om ain p ackage to a specific DBMS.
Then you can create your domain and assign your domain to the domain package.
Assigning you r d omain to a d omain package means your d omain will only supp ort
the d ata typ es sup ported by th e dom ain packages DBMS. You can also tag you r
dom ain as server-generated for forward engineering p urp oses.
Figure 14 A Domain
Create Tables
A table identifies an entity. Each table can contain columns, constraints, triggers, and
indexes. Tables are joined together through relationships. A table can belong to only
one schem a, how ever a schema m ay contain z ero or more tables. You can also ind icatethe tablespace nam e for your table.
When y ou create a table, your table nam e mu st be uniqu e within the schem a to wh ich
it belongs, and must meet your specified DBMSs naming requirements. It is
recommended you use a prefix to distinguish the tables in your data model from the
classes in your object model.
Figure 15 A Table
-
8/3/2019 .CAT Rose Data Modeler MANUAL
44/100
32 Chapter 4 - Physical Data Modeling
Create Columns
A colum n d efines th e characteristics of a table. The tables are linked by th eir colum ns.
Column names must be unique within the table and column values are controlled by
constraints. You can sp ecify a column typ e, data type, p recision, leng th, scale (if
required), and wh ether the colum n is part of a key constraint and is nullable.
Column Types
Each colum n is assigned a colum n typ e. Data Modeler supp orts two column
typesdata colum ns and comp uted columns.
Data Column
A d ata colum n stores any data information except derived values. You can assign
your data column to a data typ e sup ported by you r target DBMS, or to an existing
dom ain. If you sp ecify a data type, your d ata type m ay require you to enter a length
or precision and an accom pan ying scale. You can assign a d efault value to your data
colum n. You can also assign constraints to y ou r d ata colum n, specifying if it is a
primary key, unique key, and w hether N ULL values are accepted. Data column s also
support SQL Servers Identity property and DB2s ForBitData.
Computed Column
A comp uted colu mn uses a SQL expression to d erive and store its values. Because
they are derived, and do n ot use u nique values, comp uted column s cannot be
assigned as a p rimary key or u nique key. As part of optimization efforts to increase
query speed, you can create an index on a computed column.
Create Constraints
-
8/3/2019 .CAT Rose Data Modeler MANUAL
45/100
Building a New Data Model 33
There are tw o typ es of constraintskey constraints an d check constraints. Key
constraints restrict da ta in a colum n or grou p of colum ns to u niquely identify a row
and enforce referential integrity within a relationship. Check constraints restrict theadd ing to o r m odifying of a tables d ata to enforce business rules.
Key Constraints
There are three types of key constraintsprimary key, unique, and foreign key.
How ever, also inclu ded as a type of key constraint is ind exes. Indexes are includ ed as
a typ e of key constraint because they relate d irectly to the oth er key constraints, and
use a key colum n list. Each table can hav e only one p rimary key constraint and can
contain zero or more unique and foreign key constraints. To enforce the key
constraints, the d atabase server creates a u nique index.
Primary Key Constraint
If you a ssign a primary key for one of your colum ns, a primary key constraint is
autom atically created for you consisting o f that colum n an d any oth er colum ns you
assign as a p rimary key. Prim ary key constraints do n ot allow any tw o rows of a table
to have the same non-NULL values in any p rimary key colum n, and d o not allow an y
primary keys to have NULL values. Primary keys control the characteristics of all
correspond ing foreign keys an d can identify the parent table in a relationship .
Primary key constraints can consist of one p rimary key or a comp osite prim ary key.
The com posite primary key can consist of pr imary keys and / or primar y/ foreign
keys. Primary/ foreign keys are embedded keys that enforce the referential integrity of
a relationship, and therefore cannot h ave N ULL values. Primary/ foreign keys are
created throug h id entifying relationships, so you cannot ma nu ally control a
p rimary / foreign key. Refer to Embedded Primary/Foreign Key on page 36 for more
information on prim ary/ foreign k eys.
Unique Constraint
Unique constraints also know n as alternate constraints consist of one or more u nique
colum ns. Unique constraints do n ot allow any tw o rows of a table to have the same
non-NULL values in any un ique constraint colum ns. NULL valu es are not allow ed
for this constrain t, with the exception of the SQL Server D BMS. SQL Server allows
NULL values for uniqu e constraints.
Foreign Key Constraint
i k i i f f i k i k
-
8/3/2019 .CAT Rose Data Modeler MANUAL
46/100
34 Chapter 4 - Physical Data Modeling
Foreign key constraints consist of one or more foreign keys. Foreign keys are
read-only with the exception of the foreign key name and default value. Each foreign
key is generated by creating a relationship between tw o tables, thereby m igrating theprimary or u nique key from the p arent table. Any changes m ade to the p arent tables
primary or u nique keys cascade to the foreign keys in th e child table. NULL and
unique constraints on foreign keys are controlled by the relationships cardinality.
Refer to Cardinality on page 37for more information.
Index
Indexes consist of a key list of columns that provide quick access to any given value bysearching on ly that key list of colum ns. Each index m ust ha ve a un ique n am e. You can
cluster you r index, howev er Data Mod eler only allows one clustered index per table.
Clustering an ind ex increases the efficiency of an in dex by p hy sically storing t he ind ex
with th e data. You should always use a clustered ind ex when you are creating an
index for a child table that participates in an identifying relationship.
You can also sp ecify a fill factor/ p ercent free for yo u r ind ex. The fill factor/ p ercent
free specifies the percentag e of rows each ind ex pag e can contain. Sp ecifying a low fillfactor allows for flexibility in you r in d ex. Sp ecifying a high fill factor allows for little
chan ge to the records in th e index.
Creating Key Constraints
You can create key constraints by sp ecifying th e nam e of the key constrain t, and wh at
type of constraint it iseither primary key, unique, or an index. You can also select
columns to include in your key constraint. If you are creating an index you can specifyif you want the ind ex to be un ique; the key constraints that autom atically generate
their indexes are set as u nique in dexes.
Check Constraints
A check constraint restricts actions to a tables data, by using SQL predicate
expressions. If the SQL expression retu rns false w hen it is execut ed, the t ables d ata is
not a ltered. You can create a check constra int for tables or d oma ins usin g th e Check
Constraints tab o n the Table or Dom ain Specification. You can sp ecify a check constrain t
name and a SQL expression, and if you are using Oracle as your target DBMS you can
also specify deferrable or non-deferrable.
Deferrable vs. Non-deferrable (Oracle only)
Ch k t i t f th O l DBMS b id tifi d d f bl
-
8/3/2019 .CAT Rose Data Modeler MANUAL
47/100
Building a New Data Model 35
Check constraints for the Oracle DBMS can be identified as non-deferrable or
d eferrable. Non -deferrable check constraints v erify the v alidity of an action at the en d
of the SQL statement . Deferrable check constraints v erify th e validity o f an a ctioneither at th e end of the statement using Initially Immed iate, or at the end of the
transaction before it is committed using Initially Deferred.
Create Relationships
Relationships relate tables to one anoth er in a d ata m odel using tw o typ es of
relationshipsidentifying an d non-identifying. You can also d efine th e cardinality an d
roles for a relationship, and you can use them in different relationship structures.
Identifying Relationships
An identifying relationship specifies that a child instance cannot exist without the
parent instance. It is a relationship of part-to-whole, wh ere the part wou ld h ave no
m eaning w ithout the w hole. For exam ple in the Clinic mod el, the T_Add ress table has
add resses, but no nam es of patients wh o live at those ad dresses; therefore the
add resses themselves have no mean ing, they m ust be related to a p atients nam e.
Figure 16 An Identifying Relationship
When you use an id entifying relationship, the p rimary key of the parent tablemigrates to the child table as a foreign key. The foreign key is embedded in the child
tables existing primary key constraint as a primary/ foreign key. If the child table
does n ot hav e an existing p rimary key constraint, the m igrating foreign key is
assigned as a prim ary/ foreign key creating both a foreign key an d a primar y key
constraint.
Embedded Primary/Foreign Key
When th e foreign key is embed ded it app ears as a prim ary/ foreign key in the table
-
8/3/2019 .CAT Rose Data Modeler MANUAL
48/100
36 Chapter 4 - Physical Data Modeling
When th e foreign key is embed ded , it app ears as a prim ary/ foreign key in the table.
Embedding the foreign key in the primary key enforces the referential integrity of the
relationship. This embedd ing p revents orphan records in th e child table by requiringthe deletion of the instance in the child table first, before deleting the instance in the
parent table. It also prevents you from reassigning the p rimary key to another colum n
in the p arent table, because su ch a reassignm ent w ould create orphan records in th e
child table. It is the embed d ed primary/ foreign key that d istinguishes the
relationship as an identifying relationship as op posed to it being a n on-iden tifying
relationship.
Non-Identifying Relationships
Non -id entifying relationships are relationships in w hich t here is no strong
interd epend ency between the child and parent instances , hence the foreign k ey is not
embed ded in the child tables pr imary key constraint. There are two kind s of
non-identifying relationshipsoptional an d mandatory. In an optional no n-identifying
relationship a parent instance is not required, therefore the p arent table of the
relationsh ip u ses the card inality of 0..1. In a man d atory no n-iden tifying relationship , aparent instance is required and uses the cardinality of 1.
Figure 17 A Non-Identifying Relationship
Mandatory Non-Identifying Relationships vs. Identifying Relationships
Becau se both man d atory non -id entifying relationships and identifying relationships
require a parent instance, it may be difficult to know wh en to u se them. An im portan t
factor to consider is your business use case. If you need to relate the child to different
instances of the parent d uring the life cycle of the child , then u se a m and atory
non-identifying relationship. If the child is always required to relate to the same
instance of parent during its life cycle, then use an identifying relationship.
Cardinality
Cardinality is the minimum and maximum number of possible instantiations of a
-
8/3/2019 .CAT Rose Data Modeler MANUAL
49/100
Building a New Data Model 37
Cardinality is the minimum and maximum number of possible instantiations of a
relationship between two tables. Cardinality is used to enforce referential integrity. If
a table has a cardin ality of 1, then t hat sign ifies th e table mu st exist in the relationship .This cardinality is especially imp ortant for p arent tables to prevent orp han records in
the child tables.
Cardinality can also determine if a foreign key is u nique and can be n ullable. If the
p arent table has a card inality of one or m ore the foreign k ey cannot be N ULL. Below is
a table specifying the necessary cardinalities to make a foreign key nullable and/ or
unique.
Table 1 Cardinalities for Foreign Key Constraints
Roles
Roles in a relationship explain how the table acts in the relationship, giving meaning to
cardinality. You can ap p ly roles to either o ne table or b oth tables in a relationship .Roles combined with cardinality create a grammatical statement of what is occurring
in the relationship . So as each paren t table in a relationship acts as a n oun , the role acts
as a verb and the child table acts as a direct object, and vice versa. For example in
Figure 18 on page 38 the role of the relationship between a clinic and the services it
provides is stated: One clinic provides one or m ore services.
Required ConstraintsParent Table
Cardinality
Child Table
Cardinality
Foreign key is nullable and u nique 0..1 0..1 or 1
Foreign key is nullable and not u nique 0..1 0..* or 1..*
Foreign key is not nu llable an d not u niqu e 1 1..* or 0..*
Foreign key is not nu llable, but is u niqu e 1 0..1 or 1
Figure 18 A Role
-
8/3/2019 .CAT Rose Data Modeler MANUAL
50/100
38 Chapter 4 - Physical Data Modeling
This helps wh en trying to transform bu siness requirements to m odeled elements in
the data model, and communicates to the business analyst that the business
requirements have been met.
Relationship Structures
Using identifying and non-identifying relationships you can create different
relationship structures. One of these structures is an intersection table. The other
structu re is a self-referencing stru cture.
Intersection Tables
Although, an intersection table is by d efinition a table, the sp ecific relationsh ip
structure it participates in is what really defines it. An intersection table is a
relationship structure in which one child table is related to two parent tables with two
1:n identifying relationships. When such a structure is created the child table contains
primary / foreign keys correspon d ing to the p arent tables primar y keys, therefore anyup d ates ma de to th e child tab le will affect both p arent tab les. This kind o f relationship
structure can be used for supertype/ subtype structures.
Figure 19 An Intersection Table
-
8/3/2019 .CAT Rose Data Modeler MANUAL
51/100
Building a New Data Model 39
Self-referencing
Another relationship structure is a self-referencing structure. A self-referencing
structure uses only one table, relating the table to itself with a non-identifying
relationsh ip. You u se a self-referencing str u cture w hen you hav e an instan ce of a table
that m ust be related with an other instance of the sam e table. This kind of relationship
structure can be used for recursive relationships.
Figure 20 A Self-Referencing Relationship
-
8/3/2019 .CAT Rose Data Modeler MANUAL
52/100
40 Chapter 4 - Physical Data Modeling
Define Referential Integrity
Referent ial int egrity ensures the integrity of your data when you update or delete the
parent table of a relationship. It does this by applying specific actions to its
correspond ing child tables or by preventing the p arent table itself from being u p dated
or deleted .
Data Mod eler offers two method s to su pp ort referential integrity actions: declarative
referential integrity (DRI) and system-generated referential integrity (RI) triggers. With
these two m ethods you can perform the following actions:
I Cascade deletes/ updates all associated childrenI Restrict prevents d eletion/ u pd ate of the parentI Set N ULL sets all ch ild foreign keys to NU LLI No Action no action takenI Set Default sets all child foreign keys to a default value
Declarative Referential Integrity
DRI specifies the referential integrity action as p art of the foreign key clause wh en th e
data mod el is forward engineered. Although it is considered the most efficient
method and easiest method , DRI is DBMS depend ent and not all DRI actions are
supported for each DBMS.
System-generated Referential Integrity Triggers
System-generated RI triggers specify the referential integrity actions by generating
system triggers. System-generated RI triggers are better supported by each DBMS. As
an additional integrity measure you can specify Child Restrict to prevent the insertion
of orphan records.
Create Custom Triggers
Custom triggers execute a set of SQL statements wh en you up date, insert, or delete
-
8/3/2019 .CAT Rose Data Modeler MANUAL
53/100
Building a New Data Model 41
d ata in yo ur t able. You can use a trigg er to enforce business ru les for m ultip le tables,
because triggers can prev ent sp ecific d ata m od ifications. It is imp ortan t to use triggersas a method of enforcing bu siness rules, because it ensu res that th e app lication
designer and the d atabase designer comp lete the same bu siness processes using the
same logic.
Trigger Events
When creating a trigger you mu st decide on an event or combination of events w hich
will fire the trigger. A trigger event is a specific action such as u pd ate, insert, and deletethat m odifies the data.
Additional Trigger Settings for DB2 and Oracle
Along w ith trigger events Data Modeler sup ports ad ditional trigger settings available
only to DB2 and Oracle DBMSs. These settings are trig ger typ e, granu larity,
referencing, and using the Wh enClause.
Trigger Type
Data Modeler defines a trigger t ype as the determination of when th e trigger statement
is verified before the trigger event or after the trigger event.
If the trigger statement is verified before the trigger event, the trigger can verify if the
condition of the modification is appropriate for the database, before the modification
occu rs. For example, if you are using a before trigger and attempt to insert a newpatient record that does not meet the specified requirements in the SQL statement, the
insert action is rejected, and the d atabase is not m odified.
If the trigger statement is verified after the trigger event, the trigger can verify if the
condition of the m odification is ap propr iate for the database after the mod ification
occu rs. Using the after trigger type yo u can determine a level of granularity and
reference alias names, so you can see the results the modification would make to your
data before comm itting it. This is imp ortant for the app lication d esigner, wh o cancreate a call that points to the old database if an error occurs, or