Database Modeling Step by Step

Gavin Powell

• Publisher: Auerbach Publications
• Year: 2020
• ISBN:
  9781000055603
• Edition: 1
• Topic: Informatique
• Subtopic: Modélisation et conception de données

With the aim of simplifying relational database modeling, Database Modeling Step-by-Step presents the standard approach to database normalization and then adds its own approach, which is a more simplistic, intuitive way to building relational database models. Going from basics to contemporary topics, the book opens with relational data modeling and ends with BigData database modeling following a road map of the evolution in relational modeling and including brief introductions to data warehousing and BigData modeling. A break-down of the elements of a model explains what makes up a relational data model. This is followed by a comparison between standard normalization and a more simplistic intuitive approach to data modeling that a beginner can follow and understand.

A brief chapter explains how to use the database programming language SQL (Structured Query Language), which reads from and writes to a relational database. SQL is fundamental to data modeling because it helps in understanding how the model is used. In addition to the relational model, the last three chapters cover important modern world topics including denormalization that leads into data warehouses and BigData database modeling. The book explains how there is not much to logical data modeling in BigData databases because as they are often schema-less, which means that BigData databases do not have schemas embedded into the database itself, they have no metadata and thus not much of a logical data model.

Online bonus chapters include a case study that covers relational data modeling and are available at the author's web site: www.oracletroubleshooter.com/datamodeling.html

Chapter 1

The Evolution of Relational Database Modeling

A page of history is worth a volume of logic.^*

—Oliver Wendell Holmes

Why a theory was devised and how it is now applied can be more significant than the theory itself.

The relational database model evolved into what it is today to keep pace with commercial demands over the past 50 years. The various data models that came before the relational database model, such as the hierarchical and network database models, were only partial solutions to a never-ending problem—how to store and retrieve data and how to do it accurately and efficiently. The relational database model does have strengths and weaknesses but is currently the most flexible solution for both storage and retrieval of large quantities of transactional information.

In the world of data modeling, it is essential to understand how the different data models evolved into the relational database model we have today, because understanding the relational database model from its roots helps in understanding the critical problems that the relational database model helps to solve.

This chapter covers:

• The evolution of database modeling

• Hierarchical and network database modeling

• From relational to object and object-relational database modeling

• Some general approaches to database modeling

• Brief introductory notes on data warehouses and BigData

1.1 From File Systems to Object-Relational Databases

The evolution of database modeling occurred as a result of a need to solve various problems, whose most obvious first solution was to use file systems to store files as flat files on disk, which were simple text or binary files. The first model structure to evolve was called the hierarchical database model, which was used to enforce structure onto different subsets of data, in which each subset group was contained within a parent. The next thing that appeared on the database scene was the network database model, improving on the hierarchical database model by providing solutions to some of the issues that the hierarchical model could not address. The ultimate in relational database modeling evolution was the object-relational database model as an extension of the object database model.

Figure 1.1 shows a picture of the evolutionary process of relational data modeling over time, from around the late 1940s through to and beyond the turn of the millennium, more than 50 years later. Some software has longevity, in that there might even be some network and hierarchical databases that are still in use, and perhaps even the odd file system structured database implementation.

Figure 1.1 The evolution of relational database modeling techniques.

1.1.1 File Systems

Using a file system database model implies very few modeling techniques other some data typing analysis, and the database is stored in flat files in a file system utilizing the structure of the operating system alone. Data can be stored in individual files or multiple files. Any relationships and validation between different flat files would have to be programmed in manually and limited only by the power and sophistication of that manual programming.

1.1.2 The Hierarchical Database Model

The hierarchical database model is an inverted, tree-like structure, in which each child table has a single parent table and each parent table can have multiple child tables.

A table is the most basic container in a relational database, containing both the definition of data and the data itself.

A child table can only exist if its parent table exists, and thus child tables are completely dependent on their respective parent tables—any entries in child tables can only exist where corresponding parent entries exist in parent tables. The result of this structure is that the hierarchical database model can support one-to-many relationships, such as a project having many employees working on a single project at once; but the hierarchical model cannot support many-to-many relationships, such as many employees working on multiple projects at the same time, where the unique employee project combinations cannot be found.

Figure 1.2 shows an example hierarchical database model in which every task is part of a project, which belongs to a manager, which is part of a division, which is part of a company. The disadvantages of the hierarchical database model are that any access must originate at the root node. In the hierarchical database in Figure 1.2, you always have to begin a search with a company and eventually have to search the entire database in order to locate and retrieve a single task—that is a lot of searching. As can be seen, direct many-to-many relationships are not supported, so a task cannot be assigned to an employee directly, but only through the parent objects of project and manager—the back and forth processing can be complicated and time consuming, and as a result, much of this processing must be hard coded within applications, obscuring data relationships and making maintenance more difficult.

Figure 1.2 The hierarchical database model.

1.1.3 The Network Database Model

The network database model is essentially a refinement of the hierarchical database model, allowing child tables to have more than one parent, creating a networked-like table structure. The effect of multiple parent tables for each child allows for many-to-many relationships, but those relationships are hard coded and they limit flexibility.

Figure 1.3 shows managers having multiple parents, and in this case those parents are company and department. In other words, the network model in Figure 1.3 is taking into account the fact that not only does each department within a company have a manager, but also that each company has an overall manager or CEO. Figure 1.3 also shows the addition of type or static data tables, in which employees can be defined as being of different types, such as full-time, part-time, or even contract employees. Most importantly to note from Figure 1.3 is the new Assignment table allowing for the assignment of tasks to employees. The creation of the assignment table is a direct result of the addition of multiple parent capability in the evolution from the hierarchical to the network model. The relationship between the employee and task tables is a many-to-many relationship, because each employee can be assigned multiple tasks, and each task can be assigned to multiple employees. The assignment table resolves a problem by allowing a unique definition for the combination of employee and task as the assignment of a task to a particular employee.

Figure 1.3 The network database model.

1.1.4 The Relational Database Model

The relational database model improves on the hierarchical and network database models by lending a two-dimensional structure to data. The relational database model does not completely abandon the hierarchy of data, as can be seen in Figure 1.4, because in the relational model any table can be accessed directly without having to access a parent object, as would have to be done with the hierarchical model. The trick is to know what to look for, such as employees or managers; if you want to find the address of a specific employee, you have to know which employee to look for, such as a person’s name, but you can also examine all employees first in order to find a specific person.

Figure 1.4 The relational database model.

Another benefit of the relational database model is that any tables can be linked together, regardless of their position in the hierarchy. There should be a sensible link between two tables, but one is not restricted by a strict hierarchical structure, and thus a table can be linked to both any number of parent tables and any number of child tables.

Figure 1.5 shows a small example section of the relational database model shown in Figure 1.4, describing some data that could be contained within the project and the task tables. The PROJECT_ID column in the project table uniquely identifies each project in the project table. The relationship between the project and task tables is a one-to-many relationship linking with the PROJECT_ID column (the PROJECT_ID column is present in both the project and task tables, as shown in Figure 1.5), duplicated from the project table to the task table. As can be seen in Figure 1.5, the first three entries in the Task table are all part of the Software sales data mart project, showing three tasks contained within a single project, which is a one-to-many relationship between project and tasks (one project has many possible tasks).

Figure 1.5 The relational database model—a picture of the data from Figure 1.4.

1.1.5 The Object Database Model

An object database model gives a three-dimensional structure to data, wherein any item in a database can be retrieved from any point, and very rapidly—as long as it is small in size. In other words, the relational database model lends itself to retrieval of groups of rows in sets, and in two dimensions. In contrast, the object database model is very efficient for finding small but unique items; consequently, the object database model performs very poorly when retrieving more than a single item, such as when doing reporting, because it has to search for many individual items repeatedly; but it is extremely fast when updating a single object, such as an employee’s name.

In the competition between the relational and object models, the relational database model is reasonably good at working with small chunks of data as well as larger chunks in something like a report, and so the relational model tends to win out over the object model in the end for most commercial applications.

The object database model does resolve some of the more obscure complexities of the relational database model, such as the removal of the need for types (static data) as well as many-to-many relationship resolution tables; types and many-to-many relationships are built in to an object structure. Figure 1.6 shows an example object database model structure equivalent of the relational database model structure shown in Figure 1.4. The assignment of tasks to employees is catered to by using a collection inclusion in the manager, employee, and c...