Table Relationships in Database Management.pptx

Types of relationships created in access databases

1. Table Relationships

2. This is the joining or connection of two or more tables in a database in order to organize or matchup information in the connected fields of the table . Tables are joined using their Primary Key . When tables are joined – information is said to be related Joined tables save entering the same data into several table Joins allow forms, queries and reports to access the information from tables simultaneously What is a relationship?

3. There are TWO main types of relationships that we can create in a Database . One-to-One One-to-Many Types of relationships

4. This join usually occurs when the primary key of one table matches the primary key in the second table. This means that ONE record from Table 1 matches ONE record in Table 2 . One-to-One (1:1) relationship

5. This is where the Primary Key in Table 1 is joined to a Foreign Key or Composite Key in Table 2 . One record in Table 1 matches multiple records in Table 2. Example ONE department has MANY employees OR MANY employees are assigned to ONE department. One-to-Many ( 1: M ) relationship

6. The term data integrity refers to the accuracy and consistency of data . When creating databases, attention needs to be given to data integrity and how to maintain it. A good database will enforce data integrity whenever possible . For example, a user could accidentally try to enter a phone number into a date field. If the system enforces data integrity, it will prevent the user from making these mistakes. What is Data Integrity ?

7. While viewing a  table  in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon A Show Table dialog will appear with a list of tables and  queries . Select the tables (and/or queries) that you wish to create a relationship between, and close the dialog The Relationships tab will appear with the selected tables. Click and drag a  field  on top of a field in another table to initiate a relationship. The Edit Relationships dialog will pop up containing both fields. Make any adjustments and click  OK How to create relationships

8. The “Relationships” Button While viewing a table in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon . The “Show Table” Dialog A Show Table dialog will appear with a list of tables and queries. Select the tables (and/or queries) that you wish to create a relationship between, and close the dialog.

9. The “Relationships” Tab The Relationships tab will appear with the selected tables. Click and drag a field on top of a field in another table to initiate a relationship.

10. The “Edit Relationships” Dialog The “Edit Dialog” allows you to make adjustments to the relationship, such as specifying that it must enforce data integrity and more. The  Edit Relationships dialog will pop up containing both fields. Make any adjustments and click  OK

11. The “Relationships” tab showing a many-to-many relationship. In this case, the two CustomerId fields have been linked and the two ProductId fields have been linked. The Customers.CustomerId and Products.ProductId are primary keys, and Orders.CustomerId and Orders.ProductId are foreign keys. The Result