This section outlines a general strategy for designing and implementing databases.
Define Entities in a Database
Determine the entities that make up the database. This means you must decide what the database will contain. For example, a student tracking database would probably include the following entities:
 |
Students – who is the database keeping track of |
|
Courses – which courses are available |
|
Classes – which classes are available |
|
Instructors – who is teaching the courses |
|
Schedules – putting students into classes |
Define Attributes for the Entities
Once you know what entities you are dealing with, start defining the attributes that make up the entities. For example, the students would include:
|
Student ID |
|
Last name |
|
First name |
|
Address |
|
City |
|
State |
|
Zip |
Remember that you need to define what information is relevant to the student entity. This process is repeated for all entities.
Determine Relationships Between Entities
At this point, the relationships can be developed between entities by looking at common data. Relationships fall into three basic categories:
-
One to one
-
One to many
-
Many to many
After you've set up different entities for each subject in your database, you need a way of telling the database how to bring that information back together again. The first step in this process is to define relationships between your entities.
How do relationships work?
A relationship works by matching data in key attributes. In most cases, matching attributes are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, key attributes such as the student ID, course ID, and class ID can relate student, class, and course entities.
A one-to-many relationship
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, an entity in Table A can have many matching entities in Table B, but a entity in Table B has only one matching entity in Table A.
A many-to-many relationship
In a many-to-many relationship, an entity in Table A can have many matching entities in Table B, and a record in Table B can have many matching entities in Table A. This type of relationship is only possible by defining a third entity (called a junction) whose primary key consists of two attributes including the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third entity.
A one-to-one relationship
In a one-to-one relationship, each record in Table A can have only one matching entity in Table B, and each record in Table B can have only one matching entity in Table A. This type of relationship is not common, because most information related in this way would be in one entity. You might use a one-to-one relationship to divide a table with many attributes, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main entity.
Establishing and Maintaining Referential Integrity
When using foreign keys to establish relationships, you must be careful to make sure that keys are maintained between entities. For example, only a student in the student table can be in a class. The need to ensure that each value in the foreign key exists in the appropriate entity is called referential constraint. Furthermore, ensuring that there are no dangling references is called maintaining referential integrity.
Enabling Cascaded Updates and Deletions
Many database tools allow you to automatically update and or delete entities as necessary to maintain referential integrity.
Building Tables
Building tables is simple:
-
Establish a table for each entity.
-
Assemble the fields for each attribute. Assign the appropriate data types as necessary for each field.
-
Establish the relationship mechanisms (one to many, etc.) for all tables.
-
Add data.
Using Indexes
Indexes are used to provide random access to data in a database. Indexes may be used to speed up accesses to a single table based on a single or multiple fields. Indexes consume additional space in the database but offer significant increases in speed and performance.
Using Null Values
Null values are empty data that are used for two reasons:
-
To indicate that data is missing or unknown.
-
To indicate that a value is not applicable in the current context.
Generally speaking, null values are never used in key fields for obvious reasons (remember, key fields are used to determine uniqueness for the record/entity).
Managing Redundancy
Managing redundancy is the process of ensuring that no data is repeated in a database. It is based on the fact that attributes are used for:
|
Information |
|
Identification |
|
Both identification and information. |
Generally speaking, most attributes provide information, e.g., names, addresses, and phone numbers are all useful information that you find in a database.
An example of an attribute for identification would be the following table scheme:
(PubID, PubName, PubAddress, YearFounded)
In this example, PubID is used to provide a unique “identifier” or key for each record. The actual value of PUBID has little informational value except to ensure that each record is unique (i.e., it is also the key to the table).
An example of identification and informational attributes would be the following table scheme:
(ISBN, Title, PubID)
Normalization
| |
| |
| |
|