Chapter 2
Database Fundamentals

In This Chapter

This chapter discusses fundamental concepts such as relational databases, SQL, and database access programming interfaces. This chapter includes:

•  Relational databases

•  Software architectures

•  Standard database APIs such as ODBC

Almost 100 percent of today’s enterprise applications use a database. These databases are often managed by a Relational Database Management System (less often by an Object Database Management System). Whichever database management system (DBMS) is used, its role in the corporate information system is predominant. The DBMS offers a lot of features other than a centralized view of what may be a distributed database architecture. DBMSs ensure availability, integrity, consistency, concurrency, security of the corporate data through access control, and a lot more. Such DBMS facilities lighten all client programs since they are not involved in these issues. Furthermore, a lot of the query processing is done within the database management system itself, which makes optimized access plans to data when parsing client queries. Client programs are not able to do that.

The Java Database Connectivity (JDBC) interface allows Java applets, servlets, and applications to access data in popular database management systems. The standard for accessing data is SQL, which permits maximum interoperability. Of course, SQL is the language used with JDBC. JDBC is a software layer that allows developers to write real client-server projects in Java. JDBC does not concern itself with specific DBMS functions.

Relational Databases

Relational databases are the most common DBMS. A main characteristic of a relational database is the absolute separation between physical and logical data. Data is accessed through the associated logical model to avoid supplying physical storage locations and to reduce the limitations imposed by using physical information. Relational databases allow the definition of relations and integrity rules between data sets. E.F. Codd developed this model at the IBM San Jose Research Lab in the 1970s. A language to handle, define, and control data was also developed at the IBM lab: SQL. SQL stands for Structured Query Language. SQL is a query language that interacts with a DBMS. It allows data access without supplying physical access plans, data retrieval as sets of records, and the performing of complex computations on the data.

Software Architectures

The first generation of client-server architectures is called two-tiered. It contains two active components: the client, which requests data, and the server, which delivers data. Basically, the application’s processing is done separately for database queries and updates, and for user interface presentations. Usually the network binds the back end to the front end, although both tiers could be present on the same hardware. For example, hundreds or thousands of airline seat reservation applications can connect to a central DBMS to request, insert, or modify data. While the clients process the graphics and data entry validation, the DBMS does all the data processing. Actually, it is inadvisable to overload the database engine with data processing that is irrelevant to the server, thus some processing usually also happens on the clients. The typical client-server architecture is shown in Figure 2-1.

Figure 2-1:  Typical client-server architecture with a DBMS.

Load balancing is sometimes necessary. The network becomes a bottleneck when too much data transits from the server to the clients. When this happens, it is necessary to limit the amount of data that comes back from the server. It is often unnecessary to display millions of data records on a client’s screen (dynamic queries may return a lot of rows). If the database engine is overloaded, DBMS replication may be a good solution.

What is more important is that the real business logic is often located in the client’s GUI logic and in the database at the same time. This occurs in many current applications and is a problem for code maintenance and code reusability. Indeed, applications evolve with time, but the GUI part, the data part, and the business logic part may not evolve concurrently. Figure 2-2 illustrates the two-tier architecture.

Figure 2-2:  Two-tier architecture.

Although the two-tiered architecture is common, another design is starting to appear more frequently. To avoid embedding the application’s logic at both the database side and the client side, a third software tier may be inserted. In three-tiered architectures, most of the business logic is frozen in the middle tier. In this architecture, when the business activity or business rules change, only the middleware must be modified. Figure 2-3 illustrates the three-tier architecture.

Figure 2-3:  Three-tier architecture.

Database Standards

Database vendors are numerous, and, fortunately, industry standards exist. A group of companies or organizations often define these standards by consensus. It takes a long time before those creating the definitions agree on a common specification of functions. Standards bodies make sure these specifications match industry requirements. In some cases, though, developers do not wait for a standard to emerge. If they can invest in in-house–designed technology that will boost their productivity, they will develop it and use it. However, issues such as standard database connectivity and interoperability bring such benefits that the return on investment is worth waiting for.

SQL

SQL is not a complete programming language usable to build complex applications. It is commonly used within a host language that offers specific features for building complete applications. However, SQL is an industry standard to access databases. It enables data definition, manipulation and management, access protection, and transaction control. Its roots are in relational databases, and SQL handles many relational database objects, including tables, indexes, keys, rows, and columns. The American National Standards Institute (ANSI) standardized SQL in 1986 and defined it to be independent of any programming language and database management system.

The ANSI 1989 standard defines three programmatic interfaces to SQL:

•  Modules: Separate compiled modules may define procedures and then call them from a traditional programming language.

•  Embedded SQL: The specification defines embedded statements for a few traditional programming languages. It allows embedding static SQL statements within complete programs.

•  Direct invocation: Access is implementation-defined.