As previously noted, Microsoft Access is a relational database. The relationships between the data exist because the subjects/entities are related.
Within Access those relationships are established through relationships between the tables.
There are three different types of relationships within Access.
Are the base object of the database. They contain all of the data about the subjects in the database. Tables resemble spreadsheets.
The column headers are attributes which describe the subject. So, for example, column headers in the Student table might be
First Name, Last Name, Address, City, State, Zip Code, Home Phone, Cell Phone, Data of Birth, Etc. Also, tables are the objects on which the queries,
forms and reports are based.
One-to-one: one instance of an entity is related to one instance of another entity
One employee works in one department
One car has one VIN number
One-to-many: one instance of an entity is related to many instances of another entity
One student registers for many courses
One customer places many orders
Many-to-many: many instances of an entity are related to many instances of another entity
Many products can be ordered many times
Many students visit many student union restaurants
Referential integrity is a method of improving data quality through the established relationships. Basically, this ensures that all records in related tables have
matches…leaving no orphan data. In Figure 5.3 shown below, a relationship has been established between the Vet Offices table and the Vets table. Each box represents a table and the list of attributes in the table.
The line between the tables represents the relationship. Referential integrity is evident because of the 1 and infinity symbol (∞) on opposite ends of the relationship line.
All relationships can be read in two directions. Doing so will help ensure that the relationship is set up correctly. From the figure above the relationship would be read:
One vet office employs many veterinarians
Many veterinarians work at one vet’s office
Click here to see the layout of all relationships within
the Menagerie Kennel database. You will set up these relationships in a future tutorial.
