Database Relationships  «Prev  Next»

Lesson 1

Defining Database Relationships in Access

In a relational database such as Microsoft Access, you break various subjects, such as clients and projects into separate tables. Relationships specify how you want those tables to relate to each other. For example, each project belongs to a client, and each client can have many projects. Relationships in Access, like relationships in real life, can be somewhat complicated. In Access, as in life, you can have a
  1. one-to-one,
  2. one-to-many, or
  3. many-to-many relationship.

Up until now, you may have used relationships to enhance query performance or perhaps worked only with the Access default relationships. Relationships are maintained using the Relationships option from the Tools menu. You can see the base set of relationships for our course database here in this figure:

Microsoft Access 365
The base set of relationships for our course database
Here are the characteristics and details present in the image:
  1. Database Application: The screenshot is from Microsoft Access, a database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.
  2. Tables and Relationships: The image displays a diagram of database tables and the relationships between them. There are five tables shown:
    • `Clients`
    • `Projects`
    • `Hours`
    • `Consultants`
    • `ConsultantsBankInfo`
    • `ConsultingTypeTbl`
  3. Primary Keys: Each table has a primary key, which is indicated by a key symbol next to the field name. The primary keys for the tables are:
    • `Clients`: `ClientNo`
    • `Projects`: `ProjectID`
    • `Hours`: `HoursID`
    • `Consultants`: `ConsultantID`
    • `ConsultantsBankInfo`: `ConsultantID`
    • `ConsultingTypeTbl`: `ConsultingTypeID`
  4. Foreign Keys and Relationships:
    • The `Projects` table has a foreign key `ClientNo` that links to the `Clients` table, indicating a relationship where a client can have multiple projects.
    • The `Hours` table has a foreign key `ProjectID` that links to the `Projects` table, indicating a relationship where a project can have multiple entries of hours worked.
    • The `ConsultantsBankInfo` table has a foreign key `ConsultantID` that links to the `Consultants` table, indicating a relationship where a consultant has associated bank information.
    • The `Consultants` table has a foreign key `ConsultingTypeID` that links to the `ConsultingTypeTbl`, indicating a relationship where a consultant has a specific consulting type.
  5. Cardinality: The relationships have "1" and the infinity symbol (∞) showing the cardinality between tables. This symbolizes one-to-many relationships, such as one client having many projects, and one project having many hours recorded.
  6. User Interface Elements: The window has typical elements of a Windows application with menus at the top (File, Edit, View, Relationships, Tools, Window, Help), and toolbars providing shortcuts to various functions.
  7. Visual Indicators: The tables are visually represented with grid lines, and relationships are indicated by lines connecting primary keys to corresponding foreign keys.
  8. Table Fields: Some fields in the tables are visible, including `CompanyName`, `FirstName`, `LastName`, `Address` in the `Clients` table; `ProjectID`, `ClientNo`, `ConsultantID`, `ProjectDesc`, `StartDate` in the `Projects` table; and `HoursID`, `ProjectID`, `DateWorked`, `HoursWorked` in the `Hours` table.
  9. Overall Design: The database design suggests a consulting business model where clients commission projects, consultants work on these projects, and their work hours are tracked. Consultants have bank information stored for financial transactions, and there is a table for categorizing the types of consulting provided.

This detailed description covers the visual content and inferred functionality based on the relationships and table structures depicted in the image.

By the end of this module, you will know how to:
  1. Identify different types of relationships used in Access
  2. Create one-to-one and one-to-many relationships
  3. Create a many-to-many relationship
  4. Use relationships to manage referential integrity
  5. Set the Cascade Update and Cascade Delete options for relationships
Relationships can greatly affect the integrity of the information going into your database. Let us get started. In the next lesson, you will learn how to identify different types of relationships used in Access.

SEMrush Software TargetSEMrush Software Banner