Ken Goldberg Database Lab Notes Lab 3 Relationships in ER Diagram and Relationships in MS Access MS Access Lab 3 Summary • Introduction to Relationships • Why Define Relationships? • Relationships in ER Diagram vs. Relationships in MS Access • Creating Relationships Between Tables o One-to-Many Relationship (1:N) o Many-to-Many Relationship (M:N) • Creating a Field/Column that looks up or lists values in tables o Creating a field that looks up data from another table o Creating a field that lists values from another table 1. Introduction: What is a Relationship? Definition in class: An association between 2 (or more) separate entities. Definition in MS Access: An association between 2 common fields (column) in two tables. There are three types of relationships: • One-to-One (1:1) • One-to-Many (1:N) • Many-to-Many (M:N). 2. Why Define Relationships? After you've set up different tables for each subject in your Microsoft Access Database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, the form in Fig. 1 includes information from five tables: Fig. 1: A Form Using Information from Five Tables 1Ken Goldberg Database Lab Notes How do relationships work? In the previous example in Fig. 1, the fields in five tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. 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. For example, employees can be associated with orders they're responsible for by creating a relationship between the “Employees” table and the “Orders” table using the EmployeeID fields (which we will show later). 3. Relationships in ER Diagram vs MS Access 3.1 Relationships in ER Diagram Let’s take a look at the relationship between the “EMPLOYEE” entity and the “ORDER” entity in Fig. 2. ‘Takes’ is a one-to-many relationship. The ‘Takes’ relationship can be converted into an MS Access relationship as shown in Fig 3. Fig. 2: Relationships in ER diagram 3.2 Relationships in MS Access Please refer to Fig. 3, which corresponds to the relationship in ER diagram shown in Fig. 2. 4. Creating Relationships Between Tables Again, we will be using Northwind Sample Database. Open it as before from Help menu. 4.1 One-to-One relationship 2Ken Goldberg Database Lab Notes Fig. 3: Relationships in MS Access Relationship View In a one-to-one relationship, each record in Table A can have only one matching record in Table B and each record in Table B can have only one matching record in Table A. This type of relationship is NOT common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with too many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. 4.2 One-to-many Relationship A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. Refer to Fig. 4 for Supplier table as A, and Products table as B. Defining a One-to-many Relationships between Tables 1. Close any tables you have open. You can't create or modify relationships between any open tables. 2. If you haven't already done so, switch to the Database Window. You can press F11 to switch to the Database window from any other window. 3. Click on menu Tools  Relationships (Note: when you do this, the toolbar in the window will look different, refer to Fig.3) If the relationships are already defined for the database, a relationship view of the current database will show up and look like Fig. 5. 4. If your database does not have any relationships defined, the Show Table dialog box will automatically be displayed (Fig. 6). Add the tables that you want to relate. When the Show Table dialog box isn't displayed, in the Relationships View (which you originally accessed via the menu Tool ÆRelationships), click on menu relationships ÆShow Table or right-click and select Show Table. 3Ken Goldberg Database Lab Notes Fig. 4 One-to-Many Relationship Fig. 5 Relationships View of a Northwind Database Fig. 6 Sample “Show Table” Dialog Box 4Ken Goldberg Database Lab Notes 5. Once in the relationship view you have all the tables you want to relate, then you need to define the relationship between any two tables by dragging the field that you want to relate from one table to the related field in the other table (refer to Fig. 7). To drag multiple fields, press the CTRL key and click each field before dragging them. In most cases, you drag the primary key1 field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key2 in the other table. The related fields are NOT required to have the same names (Note it is good practice to do so since it reminds you where the relationship comes from), but they MUST have the same domain (or data type3) and contain the same kind of information. In addition, when the matching fields are Number fields, they must have the same FieldSize property setting. The two exceptions to matching data types: • you can match an AutoNumber field with a Number field whose FieldSize property is set to Long Integer • you can also match an AutoNumber field with a Number field if both fields have their FieldSize property set to ReplicationID. 6. Once you have created the relationships, the Edit Relationships dialog box is displayed as shown in Fig. 8. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary. Set the relationship options if necessary. For information about a specific item in the Relationships dialog