Database Design Some of these slides are derived

Database Design Some of these slides are derived from IBM/Rational slides from courses on UML and object-oriented design and analysis. Copyright to the original slides resides with IBM/Rational. They are used here, in this course, under password protection limited to students enrolled in the course, with permission of the owners, but are not to be published or further distributed. Other slides are derived from Dennis, Wixom & Tegarden Systems Analysis & Design with UML, 3 rd edition, copyright John Wiley & Sons, used under similar permissions.

Objectives: Database Design • Define the purpose of Database Design and where in the lifecycle it is performed • Explain how persistent classes map to the data model • Learn how to distribute class behavior to the database

Database Design in Context [Early Elaboration Iteration] [Inception Iteration (Optional)] Perform Architectural Synthesis Define a Candidate Architecture Analyze Behavior (Optional) Refine the Architecture Design Components Design the Database Designer Design

Database Design Overview Supplementary Specifications Use-Case Realization Analysis Classes Design Classes Database Design Data Model Project Specific Guidelines Design Model

Database Design Steps • Map persistent design classes to the data model • Distribute class behavior to the database

Database Design Steps • Map persistent design classes to the data model • Distribute class behavior to the database

Relational Databases & Object Orientation • Not entirely compatible – RDBMS • Focus is on data • Better suited for ad-hoc relationships and reporting application • Expose data (column values) – Object Oriented system • Focus is on behavior • Better suited to handle state-specific behavior where data is secondary • Hide data (encapsulation)

The Relational Data Model • Relational model is composed of – Entities – Attributes – Relations ORDER A n Relation LINE ITEM Order_Id line. Item Entity order Line. Item_Id Description Price Quantity Product_Id Order_Id products line. Items PRODUCT Product_Id Name Price Columns (aka Attributes)

The Object Model • The Object Model is composed of – Classes – Attributes – Associations Order - number : Integer Line. Item +line. Items - quantity : Integer - number : Integer +order 1. . * 1 Product - number : Integer - description : String - unit. Price : Double A class diagram Software Product - version : Double Hardware Product - assembly : String

Persistence Frameworks • Challenge: – Changes should not break the model • Solution: an object-relational framework that – Encapsulates the physical data store – Provides object translation services • Importance of the framework – 30% of development time is spent in accessing an RDBMS – Maintenance can be 60% of total cost Business Object Model • Compact interfaces • Object-relational translation • Encapsulates data store Relational Database

Object-Relational Framework: Characteristics • Performance – Decomposing objects to data – Composing objects from data • Minimize design compromises – Limit changes to object and relational models • Make it extensible – 15%-35% of the application will require extensions to the framework

Object-Relational Frameworks: Characteristics (continued) • Documentation of the API • Documentation of internals, for extension • Support for common object-relational mappings • Persistence interfaces – Examples are save, delete, and find

Common Object-Relational Services • Patterns are beginning to emerge for object-relational applications – CORBA Services specification • • • Persistence Query - e. g. , “find”, “find unique” Transactions Concurrency Relationships Refer to the appropriate CORBA specifications for further details.

Mapping Persistent Classes to Tables • In a relational database – Every row is regarded as an object – A column in a table is equivalent to a persistent attribute of a class Student - name : String - address : String - student. ID : Long Attributes from object type Name Address Student_ID Object Instance Thomas Stuart 987 High St. 123456

Mapping Associations Between Persistent Objects • Associations between two persistent objects are realized as foreign keys to the associated objects. – A foreign key is a column in one table that contains the primary key value of associated object Course Offering table Course. Offering - number : String Number Course_ID 678 456789 Foreign Key 0. . * Course 1 - name - description - number Primary Key Course table Name Description Number Math 101 Algebra 456789

Mapping Aggregation to the Data Model • Aggregation is also modeled using foreign key relationships – The use of composition implements a cascading delete constraint Student table Student. - student. ID : int Student_ID 123456 1 0. . * Schedule - semester : Semester Primary Key Schedule table Foreign Key Student_ID Semester 123456 Spring 2001

Modeling Inheritance in the Data Model • A Data Model does not support modeling inheritance in a direct way • Two options: – Use separate tables (normalized data) – Duplicate all inherited associations and attributes (de-normalized data)

Database Design Steps • Map persistent design classes to the data model • Distribute class behavior to the database

What Are Stored Procedures? • A stored procedure is executable code that runs under the RDBMS • Two types of stored procedures: – Procedures: Executed explicitly by an application – Triggers: Invoked implicitly when some database event occurs

Map Class Behavior to Stored Procedures • Determine if any operations can be implemented as a stored procedure • Candidates: – Operations that deal with persistent data – Operations in which a query is involved in a computation – Operations that need to access the database to validate data

Example: Map Class Behavior to Stored Procedures Class Student. + get. Tuition() + add. Schedule() + get. Schedule() + delete. Schedule() + has. Prerequisites() # passed() + get. Next. Avail. ID() + get. Student. ID() + get. Name() + get. Address() Candidate Operations • get. Tuition • add. Schedule • get. Schedule • delete. Schedule • get. Student. ID • get. Name • get. Address

Checkpoints: Database Design • Have all persistent classes been mapped to database structures? • Have stored procedures and triggers been defined? • Does the persistence mechanism use stored procedures and database triggers consistently?

Review: Database Design • What is the purpose of the Database Design? • What comprises a relational data model? • What are the components of an object model? • When mapping persistent classes to tables, what is every row in a table regarded as? What is every column equivalent to? • What are stored procedures?