Each solitaire game object also has some things it knows how to do: explain how to deal, explain how to play, explain how to identify a win, and so on. In object-oriented programming terminology, actions that objects know how to perform are called methods, services, functions, procedures, or operations.

An object is very security-minded. It typically keeps the things it knows about itself private and releases that information only through a method whose purpose is to share data values (an accessor method). For example, a user or program using one of the game objects cannot access the contents of the Dealing variable directly. Instead, the user or program must execute the How Do I Deal the Game? method to see that data.

Objects also keep private the details of the procedures for the things they know how to do, but they make it easy for someone to ask them to perform those actions. Users or programs cannot see what is inside the methods. They see only the result of the method. This characteristic of objects is known as information hiding or encapsulation.

An object presents a public interface to other objects that might use it. This provides other objects with a way to ask for data values or for actions to be performed. In the example of the solitaire games, the questions that Jane’s little brother can ask are a game’s public interface. The instructions below each question represent the procedure to be used to answer the question. A major benefit of data encapsulation is that as long as the object’s public interface remains the same, you can change the details of the object’s methods without needing to inform any other objects that might be using those methods. For example, the card game objects currently tell the user to “read” the contents of an attribute. However, there is no reason that the methods couldn’t be changed to tell the user to “print” the contents of an attribute. The user would still access the method in the same way, but the way in which the method operates would be slightly different.

An object requests data or an action by sending a message to another object. For example, if you were writing a computer program to manage the instructions for solitaire games, the program (an object in its own right) could send a message to the game object asking the game object to display the instructions for dealing the game. Because the actual procedures of the method are hidden, your program would ask for the instruction display and then you would see the instructions on the screen. However, you would not need to worry about the details of how the screen display was produced. That is the job of the game object rather than the object that is asking the game to do something.

An object-oriented program is made up a collection of objects, each of which has attributes and methods. The objects interact by sending messages to one another. The trick, of course, is figuring out which objects a program needs and the attributes and methods those objects should have.

In object-oriented terminology, the template on which similar objects like the solitaire game objects are based is known as a class. When a program creates an object from a class, it provides data for the object’s variables. The object can then use the methods that have been written for its class. All of the objects created from the same class share the same procedures for their methods. They also have the same types of data, but the values for the data may differ, for example, just as the names of the solitaire games are different.

A class is also a data type. In fact, a class is an implementation of what is known as an abstract data type, which is just another term for a user-defined data type. The implication of a class being a data type is that you can use a class as the data type of an attribute in a relation.

Suppose, for example, you were developing a class to handle data about the employees in your organization. The attributes of the class might include the employee ID, the first name, the last name, and the address. The address itself is made up of a street, city, state, and zip. Therefore, you would probably create an address class with those attributes and then, rather than duplicating those attributes in the employee class, simply indicate that an object of the employee class will include an object created from the address class to contain the employee’s address.

The database catalogs Yu-Gi-Oh cards (one of those animé related trading card games). The collector for whom this database and its application were developed has thousands of cards, some of which are duplicates. They are stored in three binders. Within each binder there may be several sections; the pages are numbered within each section.

There are three major types of cards: monsters, spell, and traps. The monster card in Figure 27.12 is fairly typical. Notice that it has a title, an “attribute” at the top right, a “level” (count the circles below the name), an “edition” (first, limited, or other), a set designation, a type (and optionally two subtypes), a description, attack points, and defense points. At the bottom left, there may be a code number, which is missing from some of the early cards.

A card with the same name may appear in many sets and the same set may have more than one card of the same name. What distinguishes them is their “rarity,” determined by how the title is printed (black or white for common cards and silver or gold for rare cards) and how the image is printed (standard color printing or holofoil printing). There are a variety of combinations of printing to generate common, rare, super rare, ultra rare, and ultimate rare cards.

Most cards can be used for game play, but some have been banned from specific types of games. Others have caveats (“rulings”) attached to them by the game’s governing board that affect how the card can be used in a game.

Spell cards that, as you might expect, can be used in the game to cast spells, share a number of attributes with the monster card, but don’t have things such as type and subtypes. The third type of card, a trap, also shares some attributes with monsters, but is missing others and has a property that is unique to this type of card. Spells also have properties, but the list of possible properties differs between spells and traps.

You can find an ER diagram for the card database in Figure 27.13. As you might have guessed, there is an entity for the card, which has three subclasses, one for each specific type of card. There are also many holdings for each card.

To design the relational database, we create one relation for each entity, including the superclass (in this example, Card) and its subclasses (Monster card, Trap card, and Spell card). With an object-oriented DMBS, we would create objects only from the subclasses; no object would ever be created from the superclass. The subclasses “inherit” the attributes of their parent. For the relational database, we have to do that manually, using some type of primary key–foreign key relationship to connect the subclass tables to the parent table. Differences in where cards of a given name appear and how they are printed are handled by Holdings. Therefore, the design of the card database looks like this:

Why have both the trap and spell card relations if they have exactly the same attributes? At the current time, they could certainly be maintained in one relation. However, there are several reasons to keep them separate. First, there is no way to guarantee that they will always have the same attributes. If they are separated from the start, it will be easier to add attributes to one or the other if needed at some later date.

Second, the major reason this type of design doesn’t perform as well as it might is because the details about a card always need to be looked up in another relation, joining on the internal card number. If we keep spell and trap data separate, the relations will remain smaller, and the joins will perform better.

Note: Here’s the answer to the primary key challenge: A Holding actually represents one or more physical cards in the inventory. It has a name (represented by the internal card number) and a set designation. When cards of the same name are printed in different ways in the same set, they have different set designations. Therefore, the concatenation of the internal card number and the set designation uniquely identifies a card (although not a physical card in the inventory, given that there may be duplicate cards in the inventory). The only other alternative is to assign unique inventory numbers to each physical card and to use them. For some collectors, this may make sense, given that they would want to track the condition of each and every card.

There is an alternative design for this type of database: Rather than use one relation for each entity, create only a single relation in which many attributes may be null, depending on the type of card. Such a relation might look like this:

The CardType attribute indicates which of the type-specific attributes should have data. For example, if CardType contained “M” for Monster, you would expect to find data in the ATK, DEF, and level attributes but not the spell type or trap type. The supposed benefit of this design is that you avoid the joins to combine the separate relations of the earlier design. However, when a DBMS retrieves a row from this relation, it pulls in the entire row, empty fields and all. Practically, in a performance sense, you haven’t gained much, and you’re stuck with a design that can waste disk space.

A pure-object oriented design for the same database would include the five entity classes, although the Card class would be an abstract class. It would also include a class to aggregate all objects created from subclasses of the Card class, letting users handle all cards, regardless of type, as a single type of object. The nature of these data—the major need for inheritance—suggests that an object-oriented database may well perform better than a relational database.

Although there are many techniques for object-oriented ERDs, one of the most commonly used is the Unified Modeling Language (UML). When used to depict a post-relational database design, UML looks a great deal like the IE style, but indicates relationships in a different way.

An example of an ER diagram using UML can be found in Figure 27.14. This design is of a purely object-oriented database and includes some elements that therefore won’t appear in a hybrid design. It has been included here to give you an overview of UML so that you can better understand the portions of the modeling tool that we will be using later in this chapter.

The basic features of UML include the following:

In addition to the basic features shown in Figure 27.14, UML diagrams can include any of the following:

As an example, we’ll create a table for customers of the rare book store that stores the customer’s address in a single column, using the ROW data type:

Notice that the ROW column is given a name, just as a single-valued column. The data type is followed by the contents of the row in parentheses. The row’s fields are declared in the same way as any other SQL column (name followed by data type).

We use “dot” notation to access the individual fields in a column of type ROW. For example, to reference the street field in the address column without qualifying the table name you would use

When the SQL statement requires a table name (for example, for use in a join or for display by a query in which the field appears in multiple tables), you precede the field reference with the table name, as in

Inserting values into a row column is only a bit different from the traditional INSERT statement. Consider the following example:

The data for the address column are preceded by the keyword ROW. The values follow in parentheses.

The array column is given a name and a data type, which are followed by the keyword ARRAY and the maximum number of elements the array should hold (the array’s cardinality) in brackets. The array’s data type can be one of SQL’s built in data types or a UDT.

Access to values in an array is by the array’s index (its position in the array order). Although you specify the maximum number of elements in an array counting from 1, array indexes begin at 0. An array of 100 elements therefore has indexes from 0 to 99. The sample customer table above includes a column (numb_orders) that stores the total number of elements in the array. The last used index will be numb_orders – 1.

You can input multiple values into an array at one time when you first insert a row:

The keyword ARRAY precedes the values in parentheses.

You can also insert or modify a specify array element directly:

When you query a table and ask for display of an array column by name, without an index, SQL displays the entire contents of the array, as in:

Use an array index when you want to retrieve a single array element. The query

displays the last order stored in the array.

Processing each element in an array requires programming (a trigger, stored procedure, or embedded SQL). Declare a variable to hold the array index, initialize it to 0, and increment it by 1 each time an appropriate loop iterates—the same way you would process all elements in an array using any high-level programming language.

You specify the contents of a multiset when you insert a row into a table, much like you do for an array. The only difference is the use of the keyword MULTISET to indicate that the values in parentheses are intended as a single group:

Because a multiset is unordered, you cannot access individual elements by position, as you do with array elements. You can, however, display the entire contents of the multiset by using its name in a query:

Updating a multiset is an all or nothing proposition. In other words, you can’t pull one value out, or put in a single value. An UPDATE statement such as

replaces the entire contents of the phones column.

We could create a very simple type to hold a date, for example:

We could then specify date_type as the data type for a column in a table:

You use dot notation to access the parts of the UDT. For example,

refers to the year potion of the date_type UDT.

Notice the AS OBJECT clause that has been inserted after the UDT’s name. This indicates that rather than being used as the domain for a value in a table, this class will be used as the structure of a typed table.

SQL creates a table with one column for each column in the UDT on which the table is based, along with a column for the object ID. There are three options for creating the object ID of a row:

You may want to use a primary key as a source for an object ID. Although this makes sense logically, it also provides the slowest retrieval performance.

By default, the object ID value is generated by the SQL command processor whenever a row is inserted into the typed table, using the method that was specified when the table was created. However, an insert operation can override the default object ID, placing a user-specified value into the ID column. Once created, the object ID cannot be modified.

To create the ingredient table, we could use

As a start, let’s create the recipe type that will be used as the superclass for types of recipes:

The two last lines in the preceding example convey important information about this class. Recip_type is an abstract class: Objects will never be created from it directly. We add NOT INSTANTIABLE to indicate this property.

By default, a UDT has a finality of FINAL. It cannot be used as the parent of a subtype. (In other words, nothing can inherit from it.) Because we want to use this class as a superclass, we must indicate that it is NOT FINAL.

To create the subtypes, we indicate the parent type, preceded by the keyword UNDER. The subtype declaration also includes any attributes (and methods) that are not in the parent type that need to be added to the subtype. For example, we could create the desert type with:

Because this type will be used to create objects, and because no other types will be derived from it, we can accept the defaults of INSTANTIABLE and FINAL.

To set up the table that will store that reference, use the data type REF for the appropriate column:

For example,

creates a table with a column that stores a reference to an ingredient. The SCOPE clause specifies the table or view that is the source of the reference.

To insert a row into a table with a REF column, you must include a SELECT in the INSERT statement that locates the row whose reference is to be stored. As you would expect, the object being referenced must exist in its own table before a reference to it can be generated. We must therefore first insert an ingredient into the ingredient table:

Then, we can insert a referencing row into ingredient_amount:

A SQL-only method is written using the language constructs discussed in Appendix B.

Such an expression can be, for example, included in an INSERT statement to insert the method’s return value into a column. It can also be included in another SQL method, trigger, or stored procedure. Its return value can then be captured across an assignment operator. Output parameters return their values to the calling routine, where they can be used as needed.