Relational Databases | Perl Database Programming

There are several types of databases. The most common nowadays is the relational database. The relational part comes from mathematics. Relations are what we call tables.

Tables in relational databases

In a relational database, a table is a collection of data, laid out like a spreadsheet. Each table has a name that must be unique in the database. Each column in a table holds the same sort of data: a name, a date of birth, an ISBN, and a phone number. Each column has a name that must be unique in the table. Each row in a table holds information about one thing (a person, a book, a CD-whatever the table has been created to store information about).

Figure 1-1 shows a simple address table with name, phone_type, phone_number, and address columns.

click to expand
Figure 1-1: A table of names, addresses, and phone numbers

Different DBMS have different rules for table and column names. Some are case- sensitive. Some allow spaces in names; some don't. A good policy is to assume the worst: use underscores to separate words. Use either uppercase or lowercase for all names. Make table names unique in a database, and make columns unique in a table.

Some people like to have column names unique in the database by prefixing an abbreviated table name: pers_name, pers_address and so on. I prefer not to use prefixes, but this means that queries using more than one table can have ambiguous column names.

Anywhere a column name can be used, you can prefix it with the full table name: person.name, person.address.

Values in tables

Each piece of information in the table (each cell in the spreadsheet) is atomic, or, in Perl-speak, a scalar value. The details of the types of data vary slightly among DBMS but include strings, numbers (separate types for integers, floating point, and currency), and dates. Common column types include:

INTEGER columns store whole numbers. They can be signed or unsigned. DBMS usually provide different sizes of integer-BIGINT, SMALLINT-but all DBMS support INTEGER.
FLOAT, DOUBLE. These are both floating-point numbers, but DOUBLE holds more significant digits (and takes up more space in memory and on disk).
DECIMAL columns store numbers in a way that doesn't have the rounding problems of floating-point numbers. This is the data type to use for storing currency. You must tell the DBMS the maximum size and number of decimal places to store.
CHAR and VARCHAR columns store fixed and variable-length strings. CHAR columns always take up the size you specify; VARCHAR columns take up only as much space as they need. Unlike Perl strings, these types have a limited size, often just 255 characters. Values longer than the column width are truncated.
BLOB-The Binary Large OBject type. This is what you use for your long documents, images, soundtracks, and videos!
DATE, TIME, DATETIME, TIMESTAMP columns provide different ways of storing the date and/or time. Most DBMS require the date/time values to be in a specific format.

The NULL value

Columns can hold one other value: NULL. NULL is different from all legal values. The NULL value enables you to distinguish a value that has never been set, from zero or an empty string.