Class Schedule
21 Aug | intro
23 Aug | clients
28 Aug | servers
30 Aug | networks
04 Sep | basics lab
06 Sep | structural layer
11 Sep | presentational layer
18 Sep | working with layers
20 Sep | behavior layer
25 Sep | images & design
27 Sep | website lab
02 Oct | object layers
02 Oct | graphics
09 Oct | document markup lab
11 Oct | spreadsheets, formulas & functions
16 Oct | thoughts about data display
18 Oct | Fall Break
23 Oct | database tools
25 Oct | spreadsheets lab
30 Oct | relational databases
01 Nov | tables
06 Nov | relationships |
05.02 |
next session
08 Nov | input & output
13 Nov | SQL
15 Nov | complex queries
20 Nov | databases lab
22 Nov | Thanksgiving
27 Nov | presentation design
29 Nov | presentation delivery
04 Dec | presentation lab
13 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.