SQL Unit 21: Object-Oriented Modeling and Design with UML Michael Blaha and James Rumbaugh

1. SQL Unit 21: Object-Oriented Modeling and Design with UMLMichael Blaha and James Rumbaugh Summary of Selections from Chapter 19 prepared by Kirk Scott

2. Chapter 19 Databases

3. Chapter 19 in the book is specifically on the topic of developing a database to match an object-oriented design • Not surprisingly, the example pursued in chapter 19 is based on the example of chapter 12 • 19.1 Introduction • There is no need to go over this • It is a review of db concepts

4. 19.2 Abbreviated ATM Model • The book presents an abbreviated ATM model for chapter 19 • Some things are taken out so that the amount of stuff isn’t overwhelming • A couple of things are added so that specific db related things can be addressed which weren’t present in the original model • The abbreviated model is shown on the following overhead

6. 19.3 Implementing Structure--Basic • What happens next is a discussion of how elements of the OO model are converted into database constructs, like tables • The book notes that there are software tools out there that will do this for you automatically • It’s worthwhile knowing how to do it by hand in case you have to and so you know what it is the tools generate

7. 1. Implement classes = create tables • 2. Implement associations = create tables that are related by pk, fk pairs • 3. Implement generalizations, namely classes in superclass-subclass relationships = again, create tables that are related by pk, fk pairs • 4. Implement identity = make sure you have suitable pk identifiers in tables

8. 19.3.1 Classes • Each class in an O-O design will generally map to a table in a relational design • Each attribute in a class will map to a column in the table • Strictly speaking, classes typically don’t contain primary and foreign key attributes • The details of this will emerge gradually • Since you already know databases, how it’s done will be no surprise

9. Constructors and methods in a class have nothing to do with the relational table the class is mapped to • An example is shown on the following overhead • This pattern will be repeated for following examples: • A UML class diagram will be shown, followed by a table schema, followed by the SQL statement for creating the table

11. 19.3.2 Associations • Remember that the term associations in UML refers to relationships between classes • If classes map to tables, then associations will generally map to the inclusion of pk and fk fields • There is more to it than that • For example, when mapping a many-to-many relationship in an O-O system, it will be necessary to create the table in the middle

12. The book lists the different types of O-O associations, with differing multiplicity (cardinality) • It gives a verbal summary of how they are mapped. • For some it gives a complete example • For others, it’s limited to a verbal description

13. 1. Many-to-Many Associations • Make a table for each of the base classes • Remember to give those tables primary keys • Make a table in the middle • Embed the primary keys of the base tables in the table in the middle and make its primary key the concatenation of the embedded foreign keys • Remember to include any attributes of the association as fields in the table in the middle

15. 2. One-to-Many Associations • Make a table for each of the base classes • Remember to give those tables primary keys • Embed the primary key of the one table as a foreign key in the many table • Sometimes in the O-O design an association arc may have a name on it • If so, that would be a good choice for the name of the foreign key field

17. 3. One-to-One Associations • The book states that these rarely occur • Recall that in the database discussion, the question was whether this should be one table or two, and which way to embed • In this context, the assumption is that there are two classes in the O-O design and there will be two tables in the database design • The question still remains of which way to embed

19. 4. N-ary Associations • The book states that these also rarely occur • At an earlier point the term ternary association was used • N-ary and ternary refer to tables in the middle of star-like designs • In other words, tables in the middle where there are more than two base tables being connected • They are treated like tables in the middle, with primary keys consisting of the concatenation of embedded foreign keys • The book doesn’t provide a separate example of this

20. Association Classes • This boils down to the idea that in an O-O design, there may already be in essence a table in the middle • This book refers to association classes • Design pattern terminology might refer to this as some kind of mediator class • The bottom line is that if it exists in the O-O design, the easiest thing to do is turn it into a table in the relational design • Again, the book doesn’t provide a separate example of this

21. Qualified Associations • This topic will have an example • It is worth paying attention to because it should make clear what qualified associations really are • Remember that from the perspective of CS 202 and CS 204, the UML notation and the concept were things that hadn’t come up before

22. The point is that we need to be aware of this because a qualified association will translate into a relational database in a certain way • The qualified association under consideration is diagrammed on the following overhead

24. When you consider the diagram, you notice that a 1-1 relationship is shown • In fact, the relationship between banks and accounts is 1-m • What the diagram is telling you is that within the context of the Bank class, given an accountCode, you can identify exactly 0 or 1 accounts that match that combination

25. You could say that there is a 1-1 relationship between (bank + accountCode) and account • But when you translate into the relational model, you get the two base tables in a 1-m relationship • The primary key of the one table, bank, is embedded as a foreign key in the many table, account

26. The qualifier, accountCode, appears in the O-O model in the context of bank • However, the accountCode is a descriptor of an account • The accountCode becomes a field in the account table in the relational model

27. You may recall the term “candidate key” • This referred to a field or set of fields in a table that could have served as a primary key, but wasn’t chosen as the primary key • The concatenation of the bankID and the accountCode are a candidate key in the resulting Account table • The book indicates this with the abbreviation ck • The illustration follows

29. Aggregation, Composition • Aggregation and composition are just special forms of association • When turning them into relational models, the process is the same as for any other association

30. 19.3.3 Generalizations • The book points out that things work differently depending on whether you have single or multiple inheritance • Since we’re working with Java, we don’t have multiple inheritance • We have abstract classes and interfaces though

31. The point is that there is no such thing as an instance of an abstract class or an interface • If, in general, classes translate into tables, then instances translate into rows in tables • A table that cannot contain a row is meaningless • Therefore, trying to turn abstract classes or interfaces into tables is meaningless

32. What we are concerned with is concrete classes in an inheritance hierarchy • The basic rule of thumb still applies: • Turn each class into a table • What glues this all together is that a record in a subclass table will have the same primary key value as the corresponding record in the superclass table

33. In object-oriented terms, and object inherits certain instance variable from its superclass • In relational terms, there is a record in the superclass table and a matching record in the subclass table • The “inherited values” are the fields that are maintained in the superclass table with the matching primary key value

34. You may recall that in Watson’s presentation of this, animals and horses and sheep were used as illustrations. • There was a class for each, and the horse and sheep classes were referred to as subtypes. • You also saw something like this in the cardealership database

35. Car and Carsale had the same primary key • Car contained information common to all cars • Carsale was a subtype • It contained information about that category of cars that had sold • The book’s example is shown on the following overhead

37. There are a few things to observe about the example • Unlike the cardealership example, where both tables had a vin field, the different kinds of accounts have different primary key names • It’s not a bad idea to have differing, descriptive field names • The important point is that they are all on the same domain, and there is a pk-fk relationship from the superclass to the subclass tables

38. The book also points out that in this example there was a class, SavingsAccount, that had no instance variables • It translated into a table that only contained a primary key field • The book says that it’s still a good idea to keep this table • If it’s in one design it should be in the other • It’s possible that it will have fields added to it later

39. 19.3.4 Identity • In discussing this issue the book uses these two terms: • Object identity: This means making up an arbitrary (typically numeric) field as the pk for a table • Value based identity: This means using some combination (concatenation) of actual data fields as the pk for a table

40. This issue has come up before in the discussion of database design • When translating from O-O to relational, the design choice remains • The book prefers object identity—which is consistent with what we’ve talked about before

41. When you translate a base class into a base table, you give it an arbitrary pk field • The pk fields of tables in the middle are then concatenated • Recall that a table in the middle might have something like a date field that gets added to the pk • There is no way around that • In that case, a data value belongs in the key • The book illustrates its preference in the following diagram

43. 19.3.5 Summary of Basic Rules for RDBMS Implementation

44. 19.4 Implementing Structure--Advanced • This section will cover the following four topics: • 1. Implementing foreign keys • 2. Implementing check constraints • 3. Implementing indexes • 4. Considering views

45. 19.4.1 Foreign Keys • This section isn’t about creating the foreign keys • It’s about referential integrity • Depending on the translation from O-O to relational, there may be specific ways you want to handle ON DELETE, ON UPDATE, and so on

46. Up to this point, this was the standard default given for how to handle this: • ON DELETE RESTRICT • ON UPDATE CASCADE

47. Consider the case of generalizations • This was the translation of superclass and subclass into table and subtype table • The subtype table contains a fk that refers to the superclass table

48. If the parent record is deleted, you would like the child record to be deleted • This is accomplished by adding the following to the subtype table’s definition: • ON DELETE CASCADE • The book’s application of this rule to its example is shown on the following overhead by adding suitable constraints to some of the tables in the design

50. The book points out that in reality, in this situation, if the child is deleted, it would also be desirable to delete the parent record • Note that referential integrity does not support this • This would become something that you had to implement separately