Creating a table « Credits »
Table 9 Structure of the table «Credits»
Field name |
Data Type |
ID student |
Number |
ID discipline |
Number |
Credit |
Number |
In this table two field will be to have the Primary key: ID student and ID discipline
ID student
This field will be the Primary key for this table.
Field Size: Integer or Autonumerical
Create a relation between this field and the table «Students» by a Lookup wizard (Last name)
ID discipline
This field will be the Primary key for this table.
Field Size: Integer or Autonumerical
Create a relation between this field and the table «Disciplines» by a Lookup wizard (Name of discipline)
Credit
Field Size: Byte
Complete four tables: every table in tens recording.
Creating relationships
To
create relationships in Access2003 firstly we need to click on the
button on the toolbar.
The Show table dialogue box will appear. Click on one of the tables required in the relationship and click on the Add button; or double-click the name of the table. Repeat the previous step to add the second table, and so on. Finally click on the Close button to finish adding tables.
Double click on relation line: the Edit relationships dialogue box appears next:
Picture 12 Edit relationships
Observe at the bottom the Relationship type assigned depends on the characteristics of the related fields (in our case One-to-Many)
Activate the Enforce Referential integrity by clicking on it. Also the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated. To terminate, click on the Create button.
Referential integrity is a system of rules that relational database systems use in order to ensure that data stored in related tables are valid. If Referential Integrity is checked, Access will not allow us to insert a record in the related table if there is no matching record in the primary table.
Referential integrity has two associated actions:
Cascade update the related fields: If checked, when a value is changed in the related field in the primary table, the values in its related records in the related table will be automatically changed.
Cascade delete related records: If checked, when a record is deleted from the primary table, all of the related records in the related table will also be deleted
Save relationships.
References:
http://www.teacherclick.com/access2003/index.htm
http://www.jegsworks.com/lessons/databases/intro/index.html
http://www.hostitwise.com/microsoft_access_2003.html