As with many aspects of software development, an ability to develop useful software depends on the developer requiring a knowledge of the problem domain, and in database development this means developing a view of the data, based on what the database will be used for and the best way in which data can be accessed and stored efficiently for these purposes.

The domain, i.e., the target for the data modelling exercise, of course, contains the target data. As we have discussed previously, this data might be stored as a file-based system. It is important, for the developer, to understand how the data is understood from the point of view of the current user of that data, in the context of the client business, for example. The data will be used within this context in a certain way and, consequently, the user will have developed a view of the data – this will include how the data is related, stored, used, updated etc. In order that a useful database representation of the data can be developed, it is important during the initial stages, as with software development more generally, for the developer (in this case the database developer, or database development team) to get a good understanding of how the client understands and uses the data, as well as developing a knowledge of the current storage and representation of the data itself. In order to develop this understanding, questions should be asked to facilitate this and, again as with software development generally, client interactions will be quite frequent during the initial stages.

When the database team begin to implement the database it is a good idea that the data modelling is complete. That is, for relational database design development is done in a top-down fashion. The phrase top-down, here, refers to the idea that thorough comes first, before the database is implemented. In recent times, the idea of agile software application development has taken hold, which emphasizes a modern approach to development, in contrast to the more traditional, top-down approaches to software engineering. However, an agile mind-set is not best suited to relational database development. This is a view well expressed by Allardice (2015):

“…planning is vital [for relational database development]. Whereas in other areas of development such as agile incremental approaches (build something quickly, get it out there, revise it, add new features [quickly] over weeks or even days), relational databases reward up-front planning because the entire point is to impose rules, constraints and structure on the data and we don’t want to have one set of rules for one week and another set of rules for another week.”

Allardice (2015)

Figure 1: Database modelling

Therefore, the database modelling process itself is about how we get from the user-defined view of the data, how the data gets processed and updated etc., to the associated, database view of the data

As in many areas of software engineering, the output of the design process – the design itself – can be communicated to others in natural language (i.e., text), but a diagram can sometimes be worth a thousand words. Therefore, just like we might use UML to communicate the design of an application program, database design comes with its own diagrammatic tools. We briefly look at commonly used diagrams here and a simple example.

In Figure 2, we present some common graphical components typically used to construct entity-relationship diagrams. Entities are square-boxed, Attributes are represented by ovals and actions, which relate entities, are represented by diamonds. Figure 2 also contains typical line type used to denote the mapping between entities – mappings can be of various kinds (one-to-one, one-to-many, one-to-zero-or-many, one-to-one-or-many, many-to-many).

In the example, we have three entities (Customer, Product and Order). A singular, concrete situation can be imagined as follows: a customer is browsing an on-line shopping web-site they have already created an account with. He/she clicks on a product, likes the look of it and consequently orders the product. In a similar vein, but out of sight of the customer, the customer consequently generates an order – i.e., a stored set of information, that contains the product, which is used in the end by the company to make sure the product arrives to the customer. Looking at the example we can see that these various actions relate the given entities.

However, the purpose of the diagram in Figure 2 is not to merely represent any single situation, specific to a given customer ordering a product. The purpose is to represent all such situations. For example, take three separate situations (Situation A, Situation B and Situation C), which are all different. Situation A is the one we just described: a single customer ordering a single item, resulting in the generation of a single order that contains the product. Alternatively, in situation B, we can have a customer who has registered an account but who has placed no orders at all. Situation C is different again – customer C has visited the site many times, on occasions ordering a varying number of products at any one time, after accumulating them in their basket, then triggering one order on completion for numerous products. We therefore require the relationship between Customers, Products and Order to be well defined, meaning that the definitions of the relationships need to represent realworld use cases. The example triadic relationship between Customer, Product and Order is defined quite specifically in the diagram. In addition to the actions that connect the entities, the connections specify well-defined constrains, as follows:

A Customer can order one or many products at one time.

A Customer can generate one Order when purchasing their products, but over time they might accumulate many of them.

A Product type can be ordered never, once, or many times from any given Customer

An Order can contain one or many Products.

An Order might never be generated by a customer (someone who only registered the account and did not buy anything), might be generated once (or many times) by other customers.

Technical diagrams are useful to people who learn them. Although it takes time to learn their meaning, in the end this hard work will pay off. This is because diagrams then speak to the developer about the design, and in quite an efficient way (compare the amount of text written about the diagram, above, with the simplicity of the diagram itself). Over time you will learn to read diagrams quickly, and create them such they you can communicate your own creations to others as efficiently.

Figure 2: Relating entities graphically

Let us consider a set of objects to begin with: a bicycle, a motorbike, a car, a bus, a truck, a boat, a sailing boat, an airplane. We follow the idea that a set of objects can be thought of as being contained together inside the set, which is visually rendered as a see-through container (Stewart, 1995), such that we know which objects are there. Additionally, we denote the act of placing the objects in the container with the mapping symbol (↦). In this way, Figure 3 defines the act of creating a set of vehicles.

Figure 3: Creating a set

Although we have decided to place all of the objects into a single set, we are at the same time able to recognize that each individual member has attributes. For example, we might notice that the bicycle is the only vehicle whose engine is also the driver, which makes the bicycle quite unique, although it shares the attribute of having wheels with the motorbike, motorcar, bus and truck.

We can render these observations visually, as in Figure 4. We simply place the bicycle inside a ‘bag’ (left-hand picture), inside the see-through container, and we do likewise (middle picture) with the vehicles not propelled by a human ‘engine’, but which do have wheels, which of course can be used to roll along roads. So far, then, we have identified these two sets, and we can see that they are subsets of the set of vehicles. The larger subset of the set of vehicles, i.e., the vehicles with wheels (right-hand picture) is a type of unification of these two sets, known as the union.

Figure 4: Subset and union

Figure 5: Subset and difference.

In a different way, rather than noticing a unification of two existing sets, we might start with a subset, say all of the vehicles that use energy derived from fossil fuels (i.e., airplane, motorbike, motorcar, truck, bus, boat) and want to ‘get at’ members of this set, but only those that are designed to be propelled on a road surface. This situation is presented in Figure 5. We place the vehicles that run on fossil fuels inside a bag (left-hand picture ‘A’) and we do likewise for all of the vehicles with wheels (left-hand picture ‘B’). Then we take the set of vehicles with wheels that do not use fossil fuels, the bicycle, identified previously (left-hand picture ‘C’). From these sets we can derive set ‘D’ (right-hand picture).

With this definition, we start with B (set of wheeled vehicles) and we ‘minus’ set A to get rid of all the vehicles that are not suitable for the road. When we have completed this operation, we minus set C from the result, the bicycle in this case, which is propelled by a human, not the burning of fossil fuel, leaving set ‘road vehicles that run on fossil fuel’, i.e., set D:

(B - A) - C = D

Another important aspect of set operation is where we have two separate sets, and we want to know if the two sets have any common members. The intersection provides the answer to this.

An example intersection is presented in Figure 6. We have two separate sets, this time drawn inside two squares. There is the set of vehicles that pollute the atmosphere, because they burn fossil fuels in the process of acceleration, and the set of 2-wheeled vehicles. The intersection is a single member set, the motorbike, which has two wheels and pollutes. If we denote vehicles that pollute with a letter P, the set 2-wheeled vehicles with letter Q, and the single motorbike as R, then the following symbolic representation describes the same picture:

Figure 6: Intersection of two sets.

The union, difference and intersection of sets form three important operators in terms of relational databases. Up until now we have been visualising the set, and operations on sets, as two-dimensionally. Another way to do this is by using Venn diagrams.

Venn diagrams – illustrated in Figure 7 – can be used to generally represent what is inside a given set, irrespective of what this might be. A set is represented by a circle and where there are members of sets that are the same, this is represented as the overlapping areas of the circles. We thus have three sets (A, B and C). The six example operations show different ways of identifying areas of the sets, individually, or as a combination. The shaded areas correspond to the result of the operations defined underneath the pictures. Here, we only have three sets, but the power of the set theory is that operations can be applied to any number of sets, even though this is difficult to then visualize ‘on paper’, so to speak. The different operators shown here correspond to the operation described above – i.e. ∪, ∩ ,− are the union, intersection and difference operators, respectively.

Figure 7: Sets as Venn diagrams.