meets Tuesday and Thursday from 0800-0915
office hours in Manning 112
Class Schedule
10 Jan | intro
15 Jan | clients
17 Jan | servers
22 Jan | networks
24 Jan | basics lab
29 Jan | structural layer
31 Jan | presentational layer
05 Feb | working with layers
07 Feb | behavior layer |
12 Feb | images & design
14 Feb | website lab
19 Feb | document markup
21 Feb | graphics
26 Feb | document markup lab
28 Feb | spreadsheets
05 Mar | formulas & functions
07 Mar | data display
19 Mar | database tools
21 Mar | spreadsheets lab
26 Mar | relational databases
28 Mar | tables
02 Apr |
relationships |
05.02 |
next session
04 Apr | input & output
09 Apr | SQL
11 Apr | complex queries
16 Apr | databases lab
18 Apr | presentation design
23 Apr | presentation delivery
25 Apr | presentation lab
30 Apr | 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.