Databases are an important part of most web applications. Relational databases are the most common type of database.
The basic unit of a relational database is a table: a two-dimensional structure whose rows represent individual "things" of some type and whose columns represent attributes of those things. Like object members, attributes can be references to other "things".
You can also think of a database table as being a list of structs (as in C and C++). This gives you the capability of doing everything that you can do with an object oriented language, but with added search capabilities. The first three sections of this presentation describe some ways that tables can be used.
Most relational databases accept Structured Query Language (SQL) commands for database construction, modification, and data retrieval. The rest of this presentation describes the table structure for a simplified Cars database and illustrates the power of SQL for accessing its data.
The simplest use of a table is an entity table. It just captures attributes of a simple "thing". The attributes can be various types of data such as strings, integers, floating-point numbers, dates, and times.
A table can also represent relations between entities. Doing this requires that the entities have an id table column that uniquely identifies each row. Other tables can have entries called foreign references that refer to an appropriate row.
People are not easily classified in an object-oriented programming language. If you attempt to define classes for different types of people (e.g. teachers, students, customers, employees, etc.) you quickly find problems:
These problems are best handled by identifying roles for people rather than types. In the table structure below a customer is a person; an employee is also a person. The structure is similar to the implementation of inheritance in object-oriented languages. However, you can have the same person in both the customer and employee tables. You can also dynamically insert a person into or delete a person from either table in response to changing roles.
There are five tables in the Cars database:
They are diagrammed below.
Data is retrieved from a relational database with a SELECT query. A SELECT query returns a table constructed from the database tables, using a WHERE clause to impose conditions on the data. The ability to search multiple tables for data satisfying multiple conditions with a single query is a very powerful tool. Optimization techniques have been developed over SQL's 40 year history so that queries can be executed efficiently.
A WHERE clause typically begins with foreign reference conditions which ensure consistency of column data from multiple tables. This is illustrated in the following query. Each of the conditions in the WHERE clause just equates the two ends of a foreign reference (red arrows in the above diagram).
The table resulting from the above query has a row for each owner/car pair. Additional conditions in the WHERE clause can select a smaller number of rows. If the tables were enhanced with more columns such as "color" and "year" columns for the "car" table, additional WHERE clauses could limit the rows to information about cars with a specific description.