Lesson 32: Designing a Relational Database

1. Lesson 32: Designing a Relational Database

2. Lesson Objectives • After studying this lesson, you will be able to: • Identify and apply principles for good relational database design • Modify field settings and create Lookup fields that return multiple values • Create a split form • Identify database relationship types and create and print a relationships report • Identify object dependencies

3. Principles of Good Database Design • Reduce redundancy • Divide data into subject-based tables • Show Access how data in all tables related to scope (purpose) of the database • Ensure that data is complete and accurate • Include report formats, forms, and queries to process data and report information that meets the intended needs of the database Tip! Incomplete/inaccurate data results in inaccurate processing and reporting, and invalidates the integrity of the database.

4. Applying Design Steps

5. Normalizing Data • Process of refining tables, keys, fields, and relationships to create an efficient database • Includes: • Discarding repeating groups • Minimizing redundancy • Eliminating composite keys for partial dependency • Separating non-key attributes • Follows steps and guidelines for designing a database

6. Common Field Properties

7. Number Field Size Property Details

8. Multi-Value Lookups • Enables you to select multiple values for a field • For example, you might want to identify multiple vendors that supply products

9. Split Forms • Displays form view at the top of the window and datasheet view at the bottom of the window.

10. Working with Relationships • Three types of relationships: • One-to-One • One-to-Many • Many-to-Many

11. One-to-One Relationship • Each record in Table A can have only one matching record in Table B • Example: Each employee’s address is stored in one table and has a matching record in the payroll table

12. One-to-Many Relationships • Each record in Table A can have many matching records in Table B • Example: Each supplier in the Suppliers table could have multiple items in the Inventory table

13. Many-to-Many Relationships • Records in Table A can have many matching records in Table B, and vice-versa • Normally requires a Junction Table • Example: Items could be ordered by many customers and each customer might order many items

14. Relationship Join Line • Connects fields common to both tables

15. Referential Integrity • Sorting query results • A system of rules used to ensure that relationships are valid • Must be set in Edit Relationships dialog box • Access identifies the many and one side of the relationship

16. Cascade Options • Set in the Edit Relationships dialog box • Cascade Update updates key field of a related table when edits are made to field in primary table • Cascade Delete removes records from a related table when record is deleted from primary table

17. Relationship Join Properties • Join Properties: Control circumstances under which a record from a related table appears when you run a query • Inner Joins: Check for records with matching values in the join field and displays only those that match • Left Outer Joins: Display all records from the one side of a one-to-many relationship • Right Outer Joins: Display all records from the many side of a one-to-many relationship

18. Join Properties Dialog Box Right table name: Many side of one-to-many relationship Left table name: One side of one-to-many relationship Join types and descriptions

19. One vs. Many Sides of Relationships Many (right) side of relationship One (left) side of relationship Join Type button

20. Object Dependencies • Shows the object for which dependencies are shown at the top • You can choose to identify object dependent on the active object or those on which the object depends • Objects are listed by type

21. Lesson 32: Designing a Relational Database