Database Fundamentals : Page 1

Steps in Database Designing

1. DATABASE PURPOSE

Designing a database: Determine its purpose and use.

(a)    What information you want from the database

(b)    What subjects you need to store facts about

(c)    What facts you need to store about each subject

2. DETERMINE TABLES REQUIRED

Determining the tables can be the trickiest step in the database design process.

(a)    A table should not contain duplicate information, and information should not be duplicated between tables: When each piece of information is stored in only one table, you update it in one place.

(b)    Each table should contain information about one subject: Maintain information about each subject independently from other subjects.

3. DETERMINE FIELDS REQUIRED

Each table contains information about the same subject, and each field in a table contains individual facts about the table's subject

(i)      Relate each field directly to the subject of the table.

(ii)     Don't include derived or calculated data.

(iii)     Include all the information you need.

(iv)     Store information in its smallest logical parts.

4. IDENTIFY FIELD/FIELDS WITH UNIQUE VALUES IN EACH RECORD

Each table in the database must include a field or set of fields that uniquely identifies each individual record in the table (primary key).

5. DETERMINE THE RELATIONSHIPS

Define relationships between tables in a Microsoft Access database so that related information is brought back together again in meaningful ways. Enforce referential integrity.

6. REFINE THE DESIGN

Test and study the design (tables, fields, relationships) and detect any flaws that might remain. It is easier to change your database design now than it will be after you have filled the tables with data.

7. ENTER DATA, CREATE OTHER DB OBJECTS

When the table structures meet the design principles, then go ahead and add data to the tables. Then create queries, forms, reports, data access pages, macros, and modules.

DEFINING RELATIONSHIPS

The fields in the different tables must be related so that they show information about what is sought. A relationship works by matching data in key fields - usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table.

1. one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
2. A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
3. A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys from the two other tables.

Referential Integrity: A system of rules to ensure that relationships between records in related tables are valid, and that accidental deletion/change to related data. You can set referential integrity when all of the following conditions are met:

1. The matching field from the primary table is a primary key or has a unique index.
2. The related fields have the same data type.
3. Both tables belong to the same Microsoft Access database.

When referential integrity is enforced, you must observe the following rules:

1. You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated.
2. You can't delete a record from a primary table if matching records exist in a related table.
3. You can't change a primary key value in the primary table, if that record has related records.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. 

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table.

Back to PoSki CyberSMART Online Tutorials: Main Page