MS Access: Creating Relational Databases

1. MS Access: Creating Relational Databases Instructor: Vicki Weidler Assistant: Joaquin Obieta

2. Overview • Normalize tables to reduce data redundancy • Understand the requirements for first, second, and third normal forms • Set one-to-one, one-to-many, and many-to-many relationships between tables • Implement referential integrity between related tables • Use cascading deletes and updates to prevent orphan records

3. Normalization The process of restructuring data files to: • reduce data to its simplest structure • minimize redundancy • achieve the most efficient and functional yet flexible way to store data

4. First Normal Form (1NF) Eliminates duplicate data… • a table should not contain similar information in several fields; eliminate duplicative columns from the same table or move repeating fields to a related table • a table should not contain fields that can be further broken down into smaller meaningful parts • create separate tables for each group or related data and identify each row with a unique column (the primary key); each table should describe a single entity (object) class Definition 1NF

5. Second Normal Form (2NF) Requires all data in the table apply directly to the subject of the table indicated by the primary key field… • if the table has a single-field primary key and meets the requirements for 1NF, then it also meets the requirements for 2NF • however, if the table has a composite (multiple-field) primary key, then all the fields in the table must be related to and/or dependent on ALL the fields of the composite primary key • to convert a table to 2NF, find all the fields that are related to only part of the composite (multiple-field) primary key, group these fields into another table, then assign a primary key to the new table • another way of looking at it is to remove ALL subsets of duplicate/redundant data that apply to multiple rows of a table and place them in new tables, then create relationships between these new tables and the original tables by using foreign keys Definition 2NF

6. Third Normal Form (3NF) Eliminates fields that can be derived from other fields… • if a table contains fields that do not relate to and are not fully dependent on the primary key and completely describe the object that table represents, then it is not in 3NF • to convert a table to 3NF, delete all the fields that do not relate to the primary key Definition3NF

7. Fourth Normal Form (4NF) Requires that tables not contain fields for two or more independent, multi-varied facts… • this rule requires splitting tables that consist of lists of independent attributes • i.e. in a relation table, job skills and language fluency are independent facts about an employee; therefore, the 3-field relation table must be split into 2 two-field relation tables

8. Fifth Normal Form (5NF) Requires the capability to reconstruct the source data from the tables that have met 1NF, 2NF, 3NF and 4NF… • you should be able to regenerate from the database a spreadsheet that contains all the data in the original version • this is an “acid test” for normalization and requires considerable skill in designing queries to join related tables to produce a readable output to compare wit the original data

9. Relating Tables Relationshipsused to extract data from several tables at the same time Primary keyunique identifier for each record in a table Foreign keythe primary key in the related table

10. Types of Relationships One-to-One Relationship When one complete record in the primary table is related to just one record in the related table and vice versa; both tables are equally dependent on each other One-to-Many Relationship When one record in the primary table is related to several records in the related table; however, a record in the related table has only one related record in the primary table Many-to-Many Relationship When several records in one table are related to several records in another table. A many-to-many relationship cannot be created directly in MS Access. To relate such tables, you must first create a junction table that contains the primary keys of both tables

11. Referential Integrity Referential Integrity Ensures that changes made to one table will be reflected in the related table Orphan Records When a record is deleted in the primary table without deleting the related records in the other table

12. Referential Integrity Cont. Cascading Deletes When you delete a record from the primary table, cascading deletes automatically deletes all related records from other related tables; this ensures that there are no orphan records Cascading Updates When you change a primary key in a table, the change is updated in all of the related tables

13. Summary • Normalize tables to reduce data redundancy • Understand the requirements for first, second, and third normal forms • Set one-to-one, one-to-many, and many-to-many relationships between tables • Implement referential integrity between related tables • Use cascading deletes and updates to prevent orphan records

14. Conclusion • Resources • Questions & Answers • Evaluations • Thank You!!!