my research: adding more support for relationships to dbmss

My Research: Adding More Support for Relationships to DBMSs

by

Dr. Bryon K. Ehlmann

Computer Science Department

Southern Illinois University Edwardsville

Edwardsville, IL 62026 USA

behlman@siue.edu

Overview

• The problem of mapping relationships in database models into database definition languages (DDLs)– i.e., the problem in implementing associations (the majority of

relationships) in DBMSs

• The Object Relationship Notation (ORN)• Extending Data Models with ORN

– ORN-extended UML Class Diagrams

• Extending Object Databases with ORN– Object Relater Plus and the ORN Simulator

• Extending Relational Databases with ORN– ORN Additive

• Conclusion– Questions– A two minute quiz

Problem of Mapping Database Modelsinto DDLs (1-to-* Association)

→

SQL:

Where is the “1” ? Where is the many ( “*”)?

Problem of Mapping Database Modelsinto DDLs (1-to-1..* Association)

→

SQL:

Problem of Mapping Database Modelsinto DDLs (<1-to-1..*>|~ Association)

→

Continued on Next Page…

| ~

SQL:

Problem of Mapping Database Modelsinto DDLs (<1-to-1..*>|~ Association)

… Continued from Previous Page

Problem of Mapping Database Modelsinto DDLs (<1-to-1..*>|~ Association)

→

| ~

ODMG ODL: class Department { d_String deptNum; … // other attributes relationship set<Employee> employees

inverse Employee::department; … // methods (must enforce association semantics) };

class Employee { d_String SSN; … // other attributes relationship Department department inverse Department::employees; … // methods (must enforce association semantics) };

Overview of theObject 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

Syntax of ORN

<association>: <binding> < <multiplicities > > <binding>

<multiplicities>: <multiplicity> -to- <multiplicity>

<multiplicity>: <minimum>.. <maximum> * <number> *

<binding>: <d-s>: – <d-s> ~ ' 1 | <d-s> ! 1 X 1 – path can be taken only once

Semantics of ORN

…

Extending Data Models with ORN

Example of an ORN-Extended UML Class Diagram

• Semantics for |~ binding for “belongs to” association:On delete of an employee object, an employeecarpool link can be destroyed, but if this violates the multiplicity 2..*, the related carpool object must be implicitly deleted

• If an employeecarpool link is destroyed, the related carpool object is implicitly deleted if there are just two employees in a carpool (X~ binding).

• If an employee is deleted, the link to the employee’s organization is implicitly destroyed (default binding and * multiplicity) .

• If an organization is deleted, all descendant organizations are implicitly deleted. Any descendant organization, however, will not be deleted in this complex operation if it has any employees (' binding and default binding with 1 multiplicity for Organization in “works for” association) .

• If a link between organizations is destroyed, the child organization and all its descendant organizations are implicitly deleted; however, again, an organization is not deleted if it has any employees (' binding and default binding with 1 multiplicity for Organization in “works for” association) .

Additional Association SemanticsCaptured in Previous Model

Extending Object Databases with ORN

Example of an ORN-extended ODDL (ODMG 3.0 ODL)

Object Relater Plus (OR+) and the ORN Simulator

• OR+ is a prototype, object DBMS that implements ORN– Built on top of Object Store, a commercial ODBMS– Implemented on Unix using C++– Provides an ORN-extended, ODMG 3.0 standard façade to Object

Store

• ORN Simulator is a prototype, Web-accessible database modeling tool built using OR+– an OR+ application– Server implemented on Unix using C++– Client implemented as an applet using Java– Accessible at www.siue.edu/~behlman – “Simulates” the modeled database by allowing the user to create and

update a prototype database in the context of an ER or UML class diagram

– Provides a instructional tool for learning database modeling, ORN, and transaction processing

Extending Relational Databases with ORN

Example of an ORN-Extended UML Class Diagram

Extending Relational Databases with ORN

Example of an ORN-extended, standard SQL

CREATE TYPE ORG_TYPE AS (ID CHAR(5),PARENT REF(ORG_TYPE),);

CREATE TABLE ORGANIZATION OF ORG_TYPE (REF IS ORG_REF SYSTEM GENERATED,PARENT WITH OPTIONS SCOPE ORGANIZATION <*-TO-0..1>’...

);CREATE TABLE EMPLOYEE (

SSN CHAR(11) PRIMARY KEY,...CARPOOL_ID CHAR(8) REFERENCES CARPOOL |~X~<2..*-TO-0..1>

ON UPDATE CASCADE,ORG REF(ORG_TYPE) SCOPE(ORGANIZATION) <*-TO-1>

);CREATE TABLE CARPOOL (

ID CHAR(8) PRIMARY KEY,...

);

ORN Additive

• A (prototype?) tool that essentially adds ORN to Microsoft’s SQL Server– Currently being developed on Windows platform using

Visual C++ and Transact SQL

– Consists of a DDL Utility postprocessor to allow <association>s to be given for foreign key constraints

• Generates triggers and stored procedures to enforce ORN

– Consists of a DML Utility preprocessor to allow for ORN-required transaction processing (e.g., checks on lowerbound multiplicities at transaction commit)

T-SQL w/ ORN Additive (Database Definition)

CREATE TABLE Employee ( ssn CHAR(11) PRIMARY KEY, ... carpoolId VARCHAR(8)

CONSTRAINT BelongsTo REFERENCES Carpool(id),--+<> BelongsTo |~X~<2..6-TO-0..1>; -- Upperbound added org VARCHAR(15)

CONSTRAINT WorksFor REFERENCES Organization(name),--+<> WorksFor <*-TO-1>;);CREATE TABLE Carpool ( id VARCHAR(8) PRIMARY KEY, ...);CREATE TABLE Organization ( name VARCHAR(15) PRIMARY KEY, parent VARCHAR(15) CONSTRAINT ChildParent REFERENCES Organization(name),--+<> ChildParent <*-to-0..1>' ON UPDATE CASCADE; ...);

T-SQL w/ ORN Additive (Database Manipulation)

-- Contents of the ORN header file for the CompanyDB must be included-- here.

USE CompanyDB;

DELETE Employee WHERE ssn = '555-55-5555'; -- May result in the deletion of a row in Carpool.

UPDATE Employee SET carpoolId = ′West End′ WHERE ssn = ′123-45-6789′; -- Will result in an exception if six employees already belong to -- the West End carpool.

DELETE Organization WHERE name = ′DP Services′; -- May result in the deletion of a hierarchy of employeeless -- organizations.

GO

Questions?

Conclusion: Two Minute Quiz

1. Dr. Ehlmann’s research deals with enhancing DBMSs to better supporta) security b) objects c) relationships d) queries e) video and sound

2. The following notation includes multiplicities and bindings that define the semantics of associations.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

3. The following prototype implements an extended object DBMS on Unix and is built on top of Object Store.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

4. The following prototype provides enhanced database modeling and allows a prototype database to be created and updated in the context of the model.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

5. The following tool will enhance Microsoft’s SQL Server and is being developed using Visual C++.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

Conclusion: Answers to Two Minute Quiz

1. Dr. Ehlmann’s research deals with enhancing DBMSs to better supporta) security b) objects c) relationships d) queries e) video and sound

2. The following notation includes multiplicities and bindings that define the semantics of associations.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

3. The following prototype implements an extended object DBMS on Unix and is built on top of Object Store.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

4. The following prototype provides enhanced database modeling and allows a prototype database to be created and updated in the context of the model.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL

5. The following tool will enhance Microsoft’s SQL Server and is being developed using Visual C++.a) OR+ b) ORN c) ORN Additive d) ORN Simulator e) ODDL