Relational Database Design
Relational Database Design
This chapter includes the following sections:
• Overview of Database Design
• Stages of Design
Overview of Database Design
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.
Stages of Design
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:
1Conceptual Database Design
2Logical Database Design
3Physical Database Design
Conceptual Design
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:
•What types of information does my business currently use?
•What types of information does my business need?
•What kind of information do I want from this system?
•What are the assumptions on which my business runs?
•What are the restrictions of my business?
•What kind of reports do I need to generate?
•What will I do with this information?
•What kind of security does this system require?
•What kinds of information are likely to expand?
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 Design
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:
1Define the tables you need based on the information your business requires (as determined in the conceptual design).
2Determine the relationships between the tables. (See the section Table Relationships for more information.)
3Determine the contents (columns) of each table.
4Normalize the tables to at least the third normal form. (See the section Normalization for more information.)
5Determine the primary keys. (See the section Keys for more information.)
6Determine the values for each column.
Table Relationships
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
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 that contain more than one non-atomic value.
•Columns that duplicate or repeat.
•Columns that do not describe the table.
•Columns that contain redundant data.
•Columns that can be derived from other columns.
First Normal Form
Columns in the first normal form have the following characteristics:
•They contain only one atomic value.
•They do not repeat.
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:
•Allows you to create any number of transactions for each student without having to add new columns.
•Allows you to query and sort data for transactions quickly because you search only one column (transaction number).
•Uses disk space more efficiently because no empty columns are stored.
Second Normal Form
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:
•Update student information in just one row.
•Delete student transactions without eliminating necessary student information.
•Use disk space more efficiently since no repeating or redundant data is stored.
Third Normal Form
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.
Keys
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.
Physical Design
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:
•Data you will commonly use.
•Columns requiring indexes for data access.
•Areas needing flexibility or room for growth.
•Whether denormalizing the database will improve performance. (To denormalize your database, you reintroduce redundancy to meet performance.) For more information, see Normalization.
