You can follow pretty much what I would have been saying to you in class by working your way through our class notes for Thursday. If you already have your five tables ready (books, authors, publishers, book-author, and orders), you are ready to relate them together. You may do it manually in the relationship window by grabbing the Primary Key field from the table that is the one side of a one-to-many relationship with the left key on your mouse and, keeping the left key pressed, drag the PK over the Foreign Key field in the table that is the many side of the one-to-many relationship. This will effect a relationship between the two fields in the two tables.
But you need to enforce referential integrity to make the relationship strong and healthy. You do this in the relationship window by right-clicking on the line that connects two tables. This should reveal the dialog box that offers you the opportunity to select "enforce referential integrity." (You can see this dialog box by looking at the slides linked to the class notes.) Enforcing referential integrity activates a system of rules that ensure relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You may run into some issues with referential integrity and the dialog box may tell you that you cannot do it. The usual suspects are that you are
- attempting to enforce referential integrity on two fields with different data types (text related to number, for example)
- attempting to enforce referential integrity on two fields with similar data types, but different data formats or field sizes (a number with a field size/format of double attempting to be related to a number with a field size/format of long integer, for example)
But that was all prologue to this: it is easiest to create relationships by
- going to the table design view for a table that is the many side of a one to many relationship
- finding the foreign key in the table
- selecting the data type field in design view
- noticing the drop down lookup box when you select the data type
- selecting the Lookup Wizard from the drop down lookup box choices
- working through the Wizard to create a new drop down lookup box that will appear in this field in the datasheet (not the design) view that will allow you to see the names of the related fields in the table that is the one side of the one to many relationship
If you use the Wizard, it will create the relationships for you, but you will still have to open your relationship window to enforce referential integrity on each relationship.
It's a lot easier to show and do than it is to explain, but Microsoft has some good explanations linked from our class pages. They go into detail we won't need for this class and steps we won't follow, but they do a good job.
I may well be available during class time via Google Chat. If you wish to chat, add my gmail address to your chat list and, if I'm online, I should be visible.
See you all next week.