Relational Database Tutorial

A database is a collection of data that includes fields, records and tables. A combination of fields make up a record; a combination of records make up a table; and a combination of tables make up the database. A relational database allows you to share information between one table and another and minimize redundancy.

Preparing the Relational Database

To use a relational database effectively, you should break down your tables to their simplest form through normalization. Before building your relational database, you'll need to identify what your database will be used for, what fields you'll use, where your fields will be placed, and the unique values (primary keys) in each table.

Your table should only include data that are specific to that table. For example, a table called "Author" would include field names: "Author_ID," "Author_FName," "Author_LName," and "Birthdate," while a table called "Publisher" would include field names: "Publisher_ID," "Publisher_Name," and "Publisher_Location."

Next, you'll need to determine how each table relates to another. Relational databases have three relationships; one-to-one, one-to-many and many-to-many. A one-to-one relationship indicates that a record only has one matching record and vice-versa (example: one person has one social security number). A one-to-many relationship indicates that a record has many matches (example: one student has many classes.) A many-to-many relationship indicates that several records have several matches in another table (example: many students with many instructors).

A one-to-many relationship in a relational database needs to have a primary key. A one-to-one relationship needs a primary key for each table. A many-to-many relationship would include two foreign keys (primary keys from other tables) that link them together.

Creating the Relational Database

Using Microsoft Access, create the tables in "Design View." Include the field names and data types using your normalization outline. Add a primary key to any unique fields, example: Publisher_ID. To create a primary key, right-click on the field name and select "Primary Key." Save your table and repeat the steps to create additional tables until you are done.

Next, click on the "Relationships" button to create relationships. Choose "Relationships, Show Table" from the toolbar. Click on the tables you want to create relationships for and click on "Add." Drag the related fields from one table to the next. Click on "Create" in the "Edit Relationships" dialog box. Then, double-click on the relationship line. This will open the "Edit Relationships" dialog.

Place a check-mark by "Enforce Referential Integrity," to keep you from entering data in the foreign key field if it hasn't been entered in the primary key field yet. If you have a many relationship table, place a check-mark by "Cascade Update Related Fields" and "Cascade Delete Related Fields" as well. This will update your data automatically as it's entered. Click on "OK." Your relational database is now complete.

References

• Microsoft Access 2000 Complete, Lisa Friedrichsen, 2000