1 / 22

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.

bern
Télécharger la présentation

My Research: Adding More Support for Relationships to DBMSs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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

  3. Problem of Mapping Database Modelsinto DDLs (1-to-* Association) SQL: → Where is the “1” ? Where is the many ( “*”)?

  4. Problem of Mapping Database Modelsinto DDLs (1-to-1..* Association) SQL: →

  5. Problem of Mapping Database Modelsinto DDLs (<1-to-1..*>|~ Association) SQL: → | ~ Continued on Next Page…

  6. Problem of Mapping Database Modelsinto DDLs (<1-to-1..*>|~ Association) … Continued from Previous Page

  7. 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) }; → | ~

  8. 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

  9. Syntax of ORN

  10. Semantics of ORN …

  11. Extending Data Models with ORNExample of an 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..*, the related carpool object must be implicitly deleted

  12. Additional Association SemanticsCaptured in Previous Model • If an employeecarpool 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) .

  13. Extending Object Databases with ORNExample of an ORN-extended ODDL (ODMG 3.0 ODL)

  14. 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

  15. Extending Relational Databases with ORNExample of an ORN-Extended UML Class Diagram

  16. Extending Relational Databases with ORNExample 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, ... );

  17. 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)

  18. 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; ... );

  19. 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

  20. Questions?

  21. Conclusion: Two Minute Quiz • 1. Dr. Ehlmann’s research deals with enhancing DBMSs to better support • a) 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 • 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

  22. Conclusion: Answers to Two Minute Quiz • 1. Dr. Ehlmann’s research deals with enhancing DBMSs to better support • a) 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 • 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

More Related