Class Schedule
22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab
12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab
03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab
15 Oct | spreadsheets
17 Oct | formulas & functions
22 Oct | thoughts about data display
18 Oct | Fall Break
24 Oct | database tools
29 Oct | spreadsheets lab
31 Oct | relational databases
05 Nov | tables
07 Nov | relationships |
05.02 |
next session
12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries
26 Nov | databases lab
21 Nov | Thanksgiving
28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
home & schedule | class blog | syllabus | contact | grades
We will come to class today with five tables which we built according to the specs in Task 05.01.
We'll talk about the concepts of relating tables through the connection between a primary key and a foreign key.
We will look at creating a relationship, first manually, then using the lookup wizards to do the task.
Use a copy of the PK from the one side of a relationship as a FK in the many side of a relationship. You can do this either manually, or by using the lookup wizard to modify the properties of the FK in the table itself.
A relationship requires the primary key of one table be inserted as a foreign key in a second table.
To view/create relationships in Access, select Database Tools > Relationships (or select the Relationships icon on the toolbar).
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.
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
note that your table properties provide you advice about how to ensure the relationships are based on the proper field properties.
in a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A
in Access, this type of relationship is only possible by defining a third table (called a junction table)
You have tables and you have the potential for a relationship, but until you actually link the tables together, the relationship is not yet established
Establish the relationship in the relationship window.
Once you have decided which way the relationship goes, create a copy of the Primary Key from the one side of the relationship and place it as Foreign Key in the many side of the relationship
referential integrity is 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
for two tables to retain their referential integrity (that is, so that the relationship between them remains healthy and correct), you should always enforce it.
you can override the restrictions against deleting or changing related records and still preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes
When you complete the editing of the relationship, your Relationship view will show you the proper relationship between the two tables.
Display values looked up from a related table
Same as a lookup list but consists of a fixed set of values you type in when the lookup is created. A value list should only be used for values that will not change very often and don't need to be stored in a table
In Design View.... (note how this example includes descriptions of each field to help the user understand the intent of the field).
If you have the field already related as a Foreign Key, you may have to temporarily delete the relationship in the relationship window in order to run the Lookup Wizard. To check if things worked, change views to Datasheet view.