ORN Additive: Shrinking the Gap between Database Modeling and Implementation

1
ORN Additive Shrinking the Gap between Database
Modelingand Implementation

• by
• Bryon K. Ehlmann
• Computer Science Department
• Southern Illinois University Edwardsville
• Edwardsville, IL 62026 USA
• behlman_at_siue.edu

2
Overview

• Problem of implementing database models in SQL
• Motivation for developing ORN Additive
• Overview of Object Relationship Notation (ORN)
• ORN Additive for SQL Server 2005
• Capabilities
• Operation and Architecture
• Conclusion
• Benefits and Drawbacks
• Status

3
Problem of Implementing Database Models in SQL
(1-to- Association)
SQL
?
4
Problem of Implementing Database Models in SQL
(1-to-1.. Association)
SQL
?
5
Problem of Implementing Database Models in SQL
(lt1-to-1..gt Association)
SQL
?

Continued on Next Page
6
Problem of Implementing Database Models in SQL
(lt1-to-1..gt Association)
Continued from Previous Page
7
Motivation for Developing ORN Additive

• To show that database models can be more easily
  implemented in a relational database
• i.e., the same paradigm used to model
  associations in UML class diagrams can be used to
  define them to a relational database.
• To show that more powerful association semantics
  can be easily modeled and implemented in a
  relational database
• Need an extended UML notation for describing
  association semantics

8
Object Relationship Notation (ORN)

• A declarative scheme for defining a variety of
  binary relationships, i.e. associations, between
  entities, i.e., classes
• Can be used during requirements analysis and
  database definition
• Graphical representation integrated into modeling
  diagrams
• Linear representation integrated into data
  definition languages like SQL
• Independent of database type, object or
  relational
• Extends UML multiplicities with bindings
• Indicate what action system should take when
  update operations cause multiplicities to be
  violated
• More powerful than the referential actions of SQL

9
ORN Syntax
10
ORN-Extended UML Class Diagram

• Semantics for binding for belongs to
  association
• On delete of an employee object, an
  employee?carpool link can be destroyed, but if
  this violates the multiplicity 2..6, the related
  carpool object must be implicitly deleted

11
ORN Semantics for Data Modeling
12
T-SQL w/ ORN Additive Statementsfor Defining
Associations
USE CompanyDB --ltgt USE CompanyDB CREATE TABLE
Organization ( name VARCHAR(15) PRIMARY
KEY, parent VARCHAR(15) CONSTRAINT
ChildParent REFERENCES Organization(name), --
actually requires an ALTER --ltgt ChildParent
lt-to-0..1gt' ON UPDATE CASCADE ) CREATE TABLE
Carpool ( id VARCHAR(8) PRIMARY
KEY, ) CREATE TABLE Job ( -- job description
code CHAR(3) PRIMARY KEY, ) CREATE TABLE
Employee ( ssn CHAR(11) PRIMARY KEY,
... carpoolid VARCHAR(8)
CONSTRAINT BelongsTo REFERENCES
Carpool(id), --ltgt CONSTRAINT BelongsTo
Xlt2..6-TO-0..1gt org VARCHAR(15)
CONSTRAINT WorksFor REFERENCES
Organization(name), --ltgt WorksFor lt-TO-1gt
jobcode CHAR(3) CONSTRAINT Assigned
REFERENCES Job(code), --ltgt Assigned
'lt-TO-0..1gt- ON UPDATE CASCADE )
13
ORN Semantics for Relational Databases

14
Additional Association Semantics inin Example
Model and Database

• If an employee Employee row is deleted, the
  link to the employees organization is implicitly
  destroyed the rows org reference is set to
  NULL (default binding and multiplicity).
• If an organization Organization row is deleted,
  all descendant organizations Organization rows
  recursively referencing it via parent are
  implicitly deleted (' binding) however, an
  organization is not deleted if it has any
  employees if it is referenced by an org in any
  Employee row (default binding and 1
  multiplicity). Similar semantics apply when an
  organization's link to its parent organization is
  destroyed its parent reference is set to NULL.
• If an employee Employee row is deleted, any
  related job description Job row is implicitly
  deleted (' binding) however, a job description
  is not deleted if it is linked to another
  employee if the Job row is referenced by the
  jobcode in another Employee row (- binding).
  Similar semantics apply when an employee's link
  to a job description is destroyed its jobcode
  reference is set to NULL.

15
T-SQL Query (Implicit Transactions)
-- Contents of the ORN header file must be
included here. USE CompanyDB DELETE Employee
WHERE ssn '555-55-5555' -- May result in the
deletion of a row in Carpool. DELETE Organization
WHERE name 'DP Services' -- May result in the
deletion of a hierarchy of organizations. GO
16
T-SQL Query (Explicit Transaction)
ltgtUSE CompanyDB ltgtBEGIN TRAN INSERT
INTO Carpool VALUES ('West End', ...)
UPDATE Employee SET carpoolid 'West End'
WHERE ssn'444-44-4444' OR
ssn'777-77-7777' OR
ssn'222-22-2222' OR ... -- Raises
exception if results in gt 6 employees in the
West End -- carpool and may result
in the implicit deletion of other --
carpools now having lt 2 employees. BEGIN
TRY ltgtCOMMIT TRAN -- Raises exception
if lt 2 employees in the 'West End carpool or if
-- any lowerbound is violated. PRINT
'Transaction committed.' END TRY BEGIN
CATCH ltgtROLLBACK TRAN PRINT 'Database
transaction rolled back' END CATCH GO
17
ORN Architecture andOperation
18
Conclusion Benefits and Drawbacks

• With ORN Additive
• Mapping between database models and SQL more
  direct
• Multiplicities and bindings for an association in
  a class diagram mapped directly into an
  ltassociationgt for the implementing foreign key.
• Association semantics more powerful than those of
  standard SQL
• Easily declared in SQL and enforced by DBMS
• Referential actions based on multiplicity
  violations
• Three flavors of ON DELETE CASCADE
• Database implementation made easier and less
  error-prone
• Less need for database developers to code, test,
  and maintain complex constraints and triggers
• However
• Enforcement of ORN semantics done via generated
  T-SQL code adds unnecessary overhead
• Ideally, no ORN Additive type-tool should be
  needed!!!
• Implementation of notation like ORN can be
  integrated within SQL.

19
Conclusion Status

• ORN Additive fully functional with no known
  errors
• Graduate students continue exhaustive testing
• Seeking real users

20
Questions?