This chapter includes the following sections:

This section introduces the fundamental principles of relational database design. A thorough database design supports an effective development process and is critical to successful database functionality and performance.

The Demodata sample database is provided with installation and is frequently used in the documentation to illustrate database concepts and techniques.

Once you understand the basic structure of a relational database, you can begin the database design process. Designing a database is a process that involves developing and refining a database structure based on the requirements of your business.

Database design includes the following three stages:

The first step in the database design cycle is to define the data requirements for your business. Answering these types of questions helps you define the conceptual design:

Identifying the goals of your business and gathering information from the different sources who will use the database is an essential process. With this information you can effectively define your tables and columns.

Logical database design helps you further define and assess your business’ information requirements. Logical database design involves describing the information you need to track and the relationships among those pieces of information.

Once you create a logical design, you can verify with the users of the database that the design is complete and accurate. They can determine if the design contains all of the information that must be tracked and that it reflects the relationships necessary to comply with the rules of your business.

Creating a logical database design includes the following steps:

In a relational database, tables relate to one another by sharing a common column. This column, existing in two or more tables, allows you to join the tables. There are three types of table relationships: one-to-one, one-to-many, and many-to-many.

A one-to-one relationship exists when each row in one table has only one related row in a second table. For example, a university may decide to assign one faculty member to one room. Thus, one room can only have one instructor assigned to it at a given time. The university may also decide that a department can only have one Dean. Thus, only one individual can be the head of a department.

A one-to-many relationship exists when each row in one table has many related rows in another table. For example, one instructor can teach many classes.

A many-to-many relationship exists when a row in one table has many related rows in a second table. Likewise, those related rows have many rows in the first table. A student can enroll in many courses, and courses can contain many students.

Normalization is a process that reduces redundancy and increases stability in your database. Normalization involves determining in which table a particular piece of data belongs and its relationship to other data. Your database design results in a data-driven, rather than process or application-driven, design which provides a more stable database implementation.

When you normalize your database, you eliminate the following columns:

Columns in the first normal form have the following characteristics:

The first rule of normalization is that you must remove duplicate columns or columns that contain more than one value to a new table.

Tables normalized to the first normal form have several advantages. For example, in the Billing table of the sample database, first normal form does the following:

A table is in the second normal form when it is in the first normal form and only contains columns that provide information about the key of the table.

In order to enforce the second rule of normalization, you must move those columns that do not depend on the primary key of the current table to a new table.

A table violates second normal form if it contains redundant data. This may result in inconsistent data which causes your database to lack integrity. For example, if a student changes her address, you must then update all existing rows to reflect the new address. Any rows with the old address result in inconsistent data.

To resolve these differences, identify data that remains the same when you add a transaction. Columns like Student Name or Street do not pertain to the transaction and do not depend on the primary key, Student ID. Therefore, store this information in the Student table, not in the transaction table.

Tables normalized to the second normal form also have several advantages. For example, in the Billing table of the sample database, second normal form allows you to do the following:

A table is in the third normal form when it contains only independent columns.

The third rule of normalization is that you must remove columns that can be derived from existing columns. For example, for a student, you do not have to include an Age column if you already have a Date of Birth column, because you can calculate age from a date of birth.

A table that is in third normal form contains only the necessary columns, so it uses disk space more efficiently since no unnecessary data is stored.

In summary, the rules for the first, second, and third normal forms state that each column value must be a fact about the primary key in its entirety, and nothing else.

An ODBC key is a column or group of columns on which a table’s referential integrity (RI) constraints are defined. In other words, a key or combination of keys acts as an identifier for the data in a row.

For more information about referential integrity and keys, refer to Advanced Operations Guide.

The physical database design is a refinement of the logical design; it maps the logical design to a relational database management system. In this phase, you examine how the user accesses the database. This step of the database design cycle involves determining the following types of information: