In this chapter, we revisit RDBMSs, which are very popular in industry. We start by refreshing the key building blocks of the relational model and discuss its limitations. We then look at the ways of extending relational databases, starting with reviewing triggers and stored procedures as two key mechanisms to make RDBMSs more active. Next, we introduce object-relational DBMSs (ORDBMSs). In contrast to the OODBMSs in Chapter 8, ORDBMSs build on a relational engine, but they extend RDBMSs with object-oriented (OO) characteristics. We conclude with recursive queries, which are a powerful extension to the SQL language.

Limitations of the Relational Model

The relational data model, and the RDBMS implementations thereof, have been very successful for managing well-structured numerical and alphanumerical data. One of the major reasons for this is the mathematical simplicity and soundness of the relational model. As discussed in Chapter 6, its two key building blocks are tuples and relations. A tuple is a composition of values of attribute types that describe an entity. It can be created by using a tuple constructor. A relation is a mathematical set of tuples describing similar entities. It can be created by using a set constructor. As also mentioned in Chapter 6, the relational model requires all relations to be normalized. Data about entities can be fragmented across multiple relations, which are connected using primary–foreign key relationships.

In this chapter, we take a closer look at the forms of accessing database systems. Naturally, the manner of how a DBMS is interfaced with heavily depends on the system architecture it applies. Accessing a DBMS in a legacy mainframe set-up is different than interfacing with a DBMS in a client–server-based architecture, so we open this chapter with an overview of different database system architectures. Next, we turn our attention toward the different database application programming interfaces, or APIs for short. As the name suggests, a database API exposes an interface through which clients, third-party, and end-user applications can access, query, and manage the DBMS. Finally, we look at DBMSs’ role and place within the World Wide Web, and how recent trends in this landscape are influencing and shaping the requirements imposed on DBMSs.

Data are everywhere. To give some staggering examples, IBM projects that every day we generate 2.5 quintillion bytes of data. Every minute, more than 300,000 tweets are created, Netflix subscribers are streaming more than 70,000 hours of video, Apple users download 30,000 apps, and Instagram users like almost two million photos. In relative terms, 90% of the data in the world have been created in the last two years. These massive amounts of data yield an unprecedented treasure of internal customer knowledge, ready to be analyzed using state-of-the-art analytical techniques to better understand and exploit customer behavior by identifying new business opportunities together with new strategies. In this chapter, we zoom into this concept of “Big Data” and explain how these huge data troves are changing the world of DBMSs. We kick-off by reviewing the 5 Vs of Big Data. Next, we zoom in on the common Big Data technologies in use today. We discuss Hadoop, SQL on Hadoop, and Apache Spark.

In this chapter we start by zooming out and reviewing the database design process. We elaborate on conceptual, logical, and physical database design. We continue the chapter with conceptual design, which aims at elucidating the data requirements of a business process in a formal way. We discuss three types of conceptual data models: the ER model; the EER model; and the UML class diagram. Each model is first defined in terms of its fundamental building blocks. Various examples are included for clarification. We also discuss the limitations of the three conceptual data models and contrast them in terms of their expressive power and modeling semantics. Subsequent chapters continue from the conceptual data models of this chapter and map them to logical and internal data models.

Phases of Database Design

Designing a database is a multi-step process, as illustrated in Figure 3.1. It starts from a business process. As an example, think about a B2B procurement application, invoice handling process, logistics process, or salary administration. A first step is requirement collection and analysis, where the aim is to carefully understand the different steps and data needs of the process. The information architect (see Chapter 4) will collaborate with the business user to elucidate the database requirements. Various techniques can be used, such as interviews or surveys with end-users, inspections of the documents used in the current process, etc. During the conceptual design, both parties try to formalize the data requirements in a conceptual data model.

This chapter focuses on the most important principles pertaining to the physical organization of records and files. As such, it can be considered as the prerequisite to Chapter 13, which applies these principles in the context of physical database organization. In this way Chapters 12 and 13 are complementary in covering all facets of physical database design – the translation of a logical data model into an internal data model, including the design of indexes to speed up data access.

First, we present some overall properties of storage devices and the impact of the mechanicals of hard disk drives on the performance aspects of data retrieval. After that, we overview the mapping of logical modeling constructs onto physical concepts, and thus of the logical data model onto the internal model. Then, we briefly discuss record organization, covering the different alternatives to organize physical data records, consisting of individual data fields. After that comes the main body of this chapter, focusing on file organization and covering methods to organize records into physical files, as well as techniques to efficiently search for records with certain characteristics. We discuss several approaches, such as sequential files, hashing and the use of different index types, including B+-trees. Chapter 13 follows on from this chapter, applying the insights gained from the file organization section to the particular context of physical database organization.

Storage Hardware and Physical Database Design

Physical database design pertains to the design of an internal data model and to the way in which the logical database concepts discussed in the previous chapters are realized physically as stored records, physical files, and, ultimately, a physical database.