11. Relational Model in OOP (Part 3)

Educational outcomes

Student:

• explains how to implement different concepts of objectivity in relational model using natural language (understanding);
• identifies and corrects errors in the source code, which is an implementation of the above structure;
• lists constructs implemented in the source code (understanding);
• implements the above structures using the selected programming language (application);
• indicates functional differences between two selected programs based on code snippets source (analysis);
• creates a program that implements specific business requirements using the constructs learned from the lectures (synthesis);

Justification

Modern programming languages ​​are mostly based on the object paradigm. Therefore, its practical understanding is a necessary basis for working in this profession. Moreover, programmers often make systems based on analytical materials. And these largely use object-oriented graphical notations (such as UML). During this and the next lessons, you will deepen your theoretical bond, but first of all you will learn how to implement different concepts from object-orientness. This will allow you to design and implement modern business applications.

Association mapping 1:1 and 1:*

As we mentioned, connections between tables are described in the relational model using the primary key and the foreign key. Compared to the object model, this is a big inconvenience because it forces us to introduce purely technical elements to the business model. As if that was not enough, we do not have more advanced constructs, such as association with attribute, etc.

The basic type of association is replaced by a single relationship. This is for 1 - 1 and 1 - *. Let's look at the figure below showing the relationship between a n employee and a group.

The above business situation can be presented in the relational model as shown below:

As you can see, we created two tables. The employee table contains:

• PK, primary key,
• Column storing name
• FK, foreign key storing the groupidentifier of the employee.

There are only two columns in the Grouptable: primary keyand name.

Let's consider for a moment how will we get a list of all employees belonging to a particular group? In the object model, having an Groupclass instance (i.e. a reference or pointer to a Groupclass object), we immediately have access to all objects associated with it. Unfortunately, in the relational model, in order to get the same information, you need to look at all the employees and filter those who have the proper group ID. As you can see, this is a very serious fault. Fortunately, thanks to indexing, such searches are done fairly quickly.

Association mapping *:*

The above solution works for the "1 - *" range. And what about the "* - *" case? Let's look at the modified class diagram:

In the situation described here, the group may have many employees (as before) but the employee may belong to several groups. In order to present this situation in a relational model, we must first introduce an intermediate class. Thanks to this, one association "* - *" will be replaced by two associations with the numbers "1 - *". The effect of this transformation is shown below:

Since we now have two associations "1 - *", we can easily present them in the relational model:

• Three classes are mapped to three tables.
• In the "middle" table we put the foreign keys.

Example relational diagram for the above procedure:

In the intermediate table, there will be only foreign keys connecting employeeswith groups. In some cases we can add extra primary key for such pair. As it happens in the relational model, to find:

• employeesbelonging to a group
• groupsto which a specific employeebelongs

we need to look through all the tuples in the EmployeeGrouptable. Of course, the performance of this solution can be significantly improved by using proper indexing (on the database side).

Mapping association with attribute

Similarly the binary association of "* - *", we are dealing with an association with an attribute. Let's look at the class diagram:

It expands the previous example to include time membership information. What do we have to do to be able to store such relationships in a relational model? Well, we do not have much choice:

• We introduce an intermediate class with attributes from the association class.
• Three classes are mapped to three tables.
• In the "middle" table we place foreign keys and attributes derived from the intermediate class (association class).

Relational version is in the drawing below. We see that apart from the foreign keys (as in the previous example), we also added columns describing the start and end dates.

Mapping qualified associations

Qualified associations do not have a direct counterpart in the relational model. We can partially "simulate" it using a foreign key qualifier. Let's look at an example class diagram containing a qualified association:

An analyst has created such a design to quickly reach a group of specific employees based on its name. We can represent this in a relational model by using the group nameas the primary key (in the Group) table and as the foreign key in the Employeetable. Sample diagram containing two tables and one relation:

We see that the Employeetable, in addition to primary IDEmployeehas a Namecolumn and a foreign key (GroupName) being the group name. Thanks to this we are able to navigate from employee table to group table. One can argue whether a textual primary key is the right thing to do. It is certainly acceptable (by database management system).

Mapping of aggregation and composition

Similarly to the transition from the analytical to the implementation model, the implementation of aggregation is exactly the same as the association. So, depending on the specific situation, we use relationships, possibly with intermediary tables (see previous blocks). For compositions, we can try to have a more sophisticated approach. Depending on the capabilities of the database management system we use:

• An appropriate view that more accurately describes our design needs.
• Specific integrity constraints and dedicated triggers. This allows us to carefully follow the principles governing the composition. By creating appropriate SQL expressions (and sometimes even PL-SQL), we are able to enforce, for example, cascading delete or block sharing of parts.

Another solution, but definitely worse, is the implementation of the features of the composition on the classical programming language side. In other words: the database treats the connection as a normal relationship, and the special behaviour is implemented in eg. Java or C++.

Inheritance mapping

In traditional relational databases, inheritance does not exist as such. In some newer systems inheritance occurs, but mostly in the simplest form: disjoint and single. In the absence of it, you can try different workarounds. Let us emphasize once again that wherever a given construction takes place in its native form, it should be used. As the name implies, circumvention is not the solution. Therefore, if your database management system allows you to inherit, usually this should be used. In the remainder of this chapter we will deal with the situation when we do not have such an opportunity, and we must replace the inheritance somehow.

It can be said that the ways of circumventing inheritance in relational databases are similar to those used in transition from conceptual to implementation. In general, we include two solutions:

• Use relationships (associations) to describe relationships between tables (classes),
• Flattening hierarchy.

The above figure shows three approaches to implementing inheritance in a relational model. We can summarize them as:

1. One Table per Hierarchy (TPH). Complete flattening of the entire hierarchy and placement of all classes in one table. It will contain all the attributes from all classes in the inheritance tree. In addition, we add a column that specifies the type of the tuple. In other words, this discriminator informs us "to what class belongs this object". The undoubted advantage of this approach is the great simplicity of implementation. Unfortunately, some of the attributes will be not used. These features are very similar to those occurring when solving the overlapping inheritance problem.
2. Table Per Concrete Class (TPC). Partial flattening of the hierarchy and creation of two tables. The idea is to create one table for each bottom subclass. We include elements from the replaced subclass and all its superclasses. The advantage is the relative simplicity of the implementation. The drawback is that for each subclass we repeat invariants from the common superclass. In this particular case, the Namescolumn will appear in each table. In addition, we have difficulty processing the personsubclass as just people. A special SQL operator may be helpful: UNION.
3. Table Per Type (TPT). Create three tables and combine their relationships. Each class has its own table. Inheritance is replaced by aggregations, that is, in the relational model, relations between tables. We add the corresponding primary and foreign keys. The advantage of this solution is the full use of all the columns. Nothing is wasted, because in the table there are only elements belonging to a particular class. Usually there are no flawless solutions, so here we will find some. Particularly one: quite elaborate implementation and complicated management of such a structure, eg to find out the student's name, they need to read from the connected table Person. Of course, in the case of a more advanced hierarchy, going through many tables/relationships makes life harder and decreases the performance (SQL JOIN).

It is clear from the above that there is no one ideal solution. Each of them has its advantages, such as ease of implementation at the expense of underutilized columns (approach #1), and defects, such as fairly difficult to use, at the cost of optimal resource management (solution #3). The concrete approach needs to be adapted to the individual situation.

Working with databases in Java

Each of the popular programming languages ​​provides libraries for relational databases. For Java there are two general categories:

• JDBC (Java Database Connectivity). This is a standard interface supported by most databases available on the market. It enables all kinds of operations on a database system, including data retrieval and metadata, data updates, tables, relationships, etc. It was intended to isolate the functionality of a particular database from Java code. This way, our program should work in the same way, regardless of the database attached. Unfortunately, as in real life, the reality is not so perfect and sometimes after changing the DB engine you need to make some changes to the code of the program. Due to their versatility they are not always the best choice in terms of performance or ease of use.
• Native solutions for specific databases provided by their manufacturers. Because specific APIs are for a particular database management system, they are usually better optimized than JDBC. Therefore, if we do not care about cross-platform (DB), then it is worth checking to see if there are adequate programming libraries for our database.

As we mentioned, the most common way to connect to a Java database is to use the functionality provided by JDBC. Let's have a look at the code and comments below:

// Attempting to load the driver
try
{
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}
catch (ClassNotFoundException ex)
{
	// Error
	System.err.println ("Exception: " + ex);
	System.exit(0);
}

Connection db_conn = DriverManager.getConnection ("jdbc:odbc:dbname" "user" "pass");

Statement db_statement = db_conn.createStatement();

db_statement.executeUpdate("create table employee { int id, char(50) name};");
db_statement.executeUpdate("insert into employee values (1, 'Jan Kowlski');");
db_connection.commit();

// [...]

// Execute query
ResultSet result = db_statement.executeQuery("select * from employee");

// Process the results
while (result.next() )
{
	System.out.println ("ID : " + result.getInt("id"));
	System.out.println ("Name : " + result.getString("name"));
}

• (4). It may seem that this code does nothing (because we do not use the created object). In fact it checks if a suitable JDBC driver is in your system. If not, then we will get an exception.
• (13). We open the connection to the database.
• (15). We are preparing an instance of an object for creating commands for BD.
• (17). We are sending a SQL command that creates a new table for the employees.
• (18). We send another command to the database. This time we put records into the table - we create employee information.
• (19). Calling this method will execute previously sent commands.
• (24). We send a query to the database, returning all our employees.
• (27). As a result, we got a ResultSet object, which is sort of a collection. It provides methods for navigating through individual records.
• (9). And here we come to the main part of working with data. It is also a specific example illustrating the impedance mismatch. Notice how we work with the records we receive. We retrieve the values ​​of individual columns using the appropriate methods: different for text, numbers, and other types of data. As a result, we have different values ​​of type Stringintetc.. It would by much better to have real objects, e.g. Person class.

As you can see, the above method of working with data is not the most convenient, especially when compared with an object model. We will return to this problem in the next lesson.