Overview

Database design still remains more of an art than exact science. A database does not exist in a vacuum; it serves some specific business purpose. General database types could be divided into two broad categories: operational databases and analytical databases. The operational database handles day-to-day operations: recording data, printing payroll checks, and so on. The data in such a database changes rather frequently. Examples of this kind of database include the ACME database supplied in this book.

Analytical databases are used to store historical data, which is analyzed for reporting purposes, used to generate statistics, and so on. The information in such a database is static; new data can be added, but the historic data cannot be modified.

We've listed several database types in Chapter 1, and the examples throughout the book use the ACME database, which is a fictitious database of a fictitious hardware company.

Database design is a two-phase process. In the first phase you need to determine and define tables and fields, establish relationships, and so on. You start with collecting user requirements and formalizing them, then you analyze these requirements and identify entities in the specific business model; each entity is a prime candidate for being a table in your database.

Once you've identified the tables, you must define their attributes. An attribute is like an adjective; it describes an entity, as "sweet" describes sugar. For the Customer entity you might think of something that identifies a customer, such as name, address, phone number, and so on. These attributes will become columns in your table. Logical relationships are signified via a primary-foreign key pair.

The second phase deals with the actual creation of the conceived database inside some RDBMS package. The process is by no means linear: you'll find yourself traveling back and forth between these two phases as you fine-tune your database requirements. SQL Bible teaches the SQL syntax for creating database objects (Chapter 4), which is virtually independent of vendor implementations.