Introduction to Relational Databases – Database Connectivity

24.1 Introduction to Relational Databases

Relational databases are based on the relational model that provides a standardized way to represent and query data. The data is stored in tables and queried using a query language.

Relational Tables

Relational databases store information in tables. Each table is designed to represent a business entity that applications need to store information about. Each table is composed of columns (a.k.a. attributes). Each column represents specific information that describes a specific characteristic of a business entity that the table represents.

Each column has a name and describes information of a specific type, such as fixed-or variable-length character data (type CHAR or VARCHAR), numeric values (type NUMBER or INTEGER), date and time values (type DATE or TIMESTAMP), and many other types. Different database providers may support different data types.

Each table may be defined with a number of constraints that enforce business rules associated with this entity. For example, constraints may be used to enforce uniqueness of column values (PRIMARY KEY), or that these values must always be present (NOT NULL), or that they are restricted to a specific list or range of values. Different database providers may support different constraint types.

Actual business data is stored in tables as rows (a.k.a. tuples). Each row is composed of values that follow the exact structure of columns defined by a given table.

As an example, a music catalogue application needs to store information about music compositions in a database. Let us assume that each composition has the following attributes:

• An International Standard Recording Code (ISRC), which uniquely identifies a composition
• A title
• A duration

Thus to store this information in a relational database would require a compositions table with isrc, title, and duration columns. Assume that all of these are mandatory pieces of information for every composition object, and that isrc and title are both of type VARCHAR, and duration is of type INT. The definition of the compositions table is shown in Table 24.1.

Table 24.1 Definition of the compositions Table

Note that column types often specify specific length or precision characteristics— for example, VARCHAR(12) in Table 24.1. Also, a PRIMARY KEY constraint is usually implicitly mandatory NOT NULL (does not allow null values). In Table 24.1, the isrc column name uniquely identifies a composition, hence its constraint is PRIMARY KEY and implicitly NOT NULL. The other two column names title and duration are mandatory for each composition, hence their constraint is also NOT NULL.

Table 24.2 shows example data (four rows) stored in the compositions table, which satisfies the table definition given in Table 24.1. Each row in Table 24.2 represents a unique music composition.

A table definition can be loosely compared to a class that describes a number of attributes, and a row can be compared to an object (instance) of that specific class. A spreadsheet is another example of organizing data in rows and columns.

Table 24.2 Data in the compositions Table