10. Relational Model in OOP (Part 2)

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.

Relational model - simple and complex attributes

As mentioned in the previous lesson, classes are mapped to tables, and attributes to columns. The latter rule applies, of course, to simple types only.

For complex attributes, the case is more complicated. We have several options, none of which is ideal. Consider the sample attribute address. We can store it as:

one "flattened attribute" of the text type with content, eg "Marszałkowska 10, 03-333 Warsaw, Poland. The basic problem with this approach is the difficult access to its individual elements. For example, searching for any business in a city can be, in extreme cases, impossible;
one text attribute but using special notation eg XML (below). Thanks to such structured construction it will be easier to process it. It seems that the problem is resolved, for example, from Java we can parse XML and get the particular entries. Remember, however, that we are referring to information stored in the database, and in that case we mostly use query languages, such as SQL. And parsing XML from SQL, may not be possible. As a result, you would have to retrieve all of the addresses using SQL (as a text field) and then parse and filter data on the programming language side (eg Java). This will obviously seriously affect performance.
XML
```
<Address>
	<Street>Marszałkowska</Street>
	<Number>12</Number>
	<ZipCode>03-333</ZipCode>
	<Country>Polska</Country>
</Address>
```
additional columns (formed from simple attributes in the complex attribute) in the main table. This is, of course, only possible where the cardinality of the complex attribute is not greater than1.
separate the Address table and connect it to the "main" entry with the key (figure below). It seems that this solution is the most feasible because it enables trouble-free processing of data using query language (eg SQL). You will sometimes have to use JOIN statement (when you want to get full company information, eg name, tax ID, address) and they have quite negative impact on efficiency. Unfortunately, also our data model is a bit complicated: for each complex attribute, we add an additional table.

Relational model - manadatory and optional attributes

For optional attributes, the column in the table must allow the null value to be stored. In this situation, let's remember two things. First of all, our data will take up a little bit more space (because we have to remember the data itself and information about lack of value). Secondly, the value null can be a bit of a hassle in SQL queries.

The manadatory attribute will be defined as a column that does not allow null, and as a result, the database management system will not allow you to create an entry without the value.

Relational model - single and multi-valued attributes

The single attribute does not cause any problems and is simply implemented as one column in the table.

Unfortunately, the multi-valued attribute gives us similar problems as complex one. Similarly, we have several options and we can implement it as:

one column of text type with special syntax. We separate the values by commas, for example, or use some form of structure, such as XML, just like with complex attributes. Similar problems are also associated with this approach; In short, they are reduced to difficult access to individual values.
separate table. Each of its row will be one business value with the foreign key referring to the "parent" table. This way you can store any data combinations, but at the cost of using SQL JOIN constructs, which can hinder access and/or reduce performance.

Relational model - object and class attributes

Unlike classes, the relational model does not provide a way for storing a single common value for all tuples of a given table. Therefore, we do not have anything close to semantics of the class attribute. For this reason, we need to apply some of our own solutions, none of which is ideal, e.g.:

Create a special table that contains class attribute values from all classes. As a result, we will have many columns (for each class attribute) and only one row (because the class attribute has only one value). Such a table will semantically "occupy space" on the schema, really storing very little information.
Declaring a special table with two columns: keys (class attribute names) and values. The disadvantage of this solution is also the type problem. Since we have only one column with a value, then all class attributes will have a common type, possibly a text. The alternative will be several columns with values, such as date, number, and text. Of course, in a given row only one of them will be used.
Save it outside the database, eg in a program code or configuration file.

We have mentioned that none of these methods is ideal, primarily because it means problems with the processing of a class attribute. Imagine any SQL query that includes such an attribute (eg maximum worker age) - in any case, it will be a problem.

Relational model - derived attributes

In programming languages, the derived attributes were supported by methods. For a relational model, and a suitable relational database, we can create a dedicated:

View - of course if your database management system supports it,
Method in database programming language, eg in SQL,
Method in programming language (outside of database).

Commentary

Database views are virtual tables, from the user's perspective, behaving as standard ones. They are usually defined using a query language (eg SQL). This allows us to put in practically any functionality, e.g. one that is bound to derivative attributes, e.g. age.

Similarly as in previous cases, none of the above methods are ideal and involve larger (programmable language method) or smaller (procedural or SQL views) constraints.

Relational model - object and class methods

Unfortunately, in the classical relational model, methods do not exist at all. Fortunately, modern database management systems provide SQL extensions (PL / SQL or T-SQL) that allow them to be created. This is not always compatible with object-oriented semantics, especially with regard to object methods (access to specific information-entities).

As an alternative, you can take the implementation of these methods on the "classical" programming language (Java or C#). This may involve performance issues due to the need to (manually) process information derived from DB.

It seems that you often encounter a hybrid approach where basic business logic is implemented in the database programming language (eg SQL), and it is called from eg Java. Thanks to this, critical operations are fully implemented in a fast and optimized database environment.