Overview
This lecture covers setting up primary and foreign key relationships in Microsoft Access, enforcing referential integrity, and explains the correct order for data entry.
Setting Up Relationships in Access
- Use Design View to check for primary keys in tables.
- Foreign keys are set up via Database Tools > Relationships in Access.
- Add all relevant tables to the Relationships window using the Add button.
Creating Relationships Between Tables
- Match related fields (e.g., class number to class number, student number to student number) by dragging and dropping in the Relationships window.
- Confirm relationship details when prompted.
Enforcing Referential Integrity
- Enable "Enforce Referential Integrity" to ensure related records exist in referenced tables.
- Referential integrity checks if a referenced record (e.g., a student) exists before allowing related data (e.g., a grade) to be entered.
- Prevents entering invalid or "garbage" data into the database.
Data Entry Order and Error Handling
- Enter data in the correct order: class table, then student table, then grade table.
- If data is entered in the wrong order, Access will prevent the entry and display an error.
- Use the escape key to cancel an unwanted or invalid entry.
Timing of Relationship Creation
- Creating relationships before data entry gives immediate, specific error feedback for invalid data.
- Creating relationships after data entry may yield vague errors and make finding issues harder.
Queries and Further Reading
- Creating and running queries is covered in the textbook; students should follow along independently.
Key Terms & Definitions
- Primary Key — a unique identifier for each record in a table.
- Foreign Key — a field linking one table to the primary key of another table.
- Referential Integrity — a rule that enforces valid links between related tables, preventing orphaned records.
Action Items / Next Steps
- Practice entering table data in Access as described in the textbook.
- Follow the textbook instructions for creating and running queries (see page 33).
- Read the next chapter as assigned.