Relationships in MS Access [ PGDCA / DCA]
Relationships in MS Access
Relationship in MS Access
In databases, relationships between tables define how the data in one table is related to the data in another table. Relationships establish connections between tables based on common fields, allowing for efficient data retrieval, manipulation, and maintenance. Any association between two entity(Tables, Databases) types is called a relationship. Table/Database/Entities take Part in relationship.
For example,
A teacher teaches students.
Here Teacher and student is a entity( Table or Database )
Here, "teaches" is a relationship
The connection between the Teacher entity and the Student entity can be described as a mutually dependent association. which is known as relationship
 |
| Relationships in MS Access |
Type of Relationship
In databases, relationships between tables can be categorized into different types based on how the data in one table is related to the data in another table. The main types of relationships are:
One-to-One (1:1):
In a one-to-one relationship, each individual entry in one table corresponds exclusively to a single entry in another table, and vice versa.
This type of relationship is relatively rare and is typically used when the data in the two tables is closely related and can be combined into a single table.
Example: If there are two entities
‘Person’ (Id, Name, Age, Address)
‘Adhar’(Adhar_no,Id).
So, each person can have only one Adhar and each Adhar belongs to only one person.
 |
| One-to-One Relationship |
 |
| example One-to-One Relationship |
One-to-Many (1:M):
- In a one-to-many relationship, each record in one table can relate to multiple records in another table, but each record in the second table can relate to only one record in the first table.
- This is the most common type of relationship in databases and is used to represent hierarchical or parent-child relationships.
For example
If there are two entity type ‘Customer’ and ‘Account’
then each ‘Customer’ can have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’. we can say that each Customer is associated with many Account So, it is a one-to-many relationship.
 |
| One-to-Many relationship |
Many-to-One (M:1):
- Many-to-one relationships are essentially the reverse of one-to-many relationships. They occur when multiple records in one table can relate to only one record in another table.
- This type of relationship is less common but can still be useful in certain scenarios.
Example: We Take the old Example of one-to-many where many Account is associated with one Customer then we can say that it is a many-to-one relationship.
Many-to-Many (M:M):
- In a many-to-many relationship, it is possible for multiple records in one table to be associated with multiple records in another table.
- This type of relationship typically requires the use of a junction table, also known as an associative table or link table, to link the two tables together.
Example:
If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy more than one product And many product can be bought by many different customers
 |
| Many-to-Many Relationship |
 |
| Many-to-Many Relationship |
Creating & Saving Relationships in MS Access
Creating relationships between tables in Microsoft Access involves several steps to ensure data integrity and efficient database management. Here's a step-by-step guide to creating relationships in MS Access:
1. Open the Database:
- Launch Microsoft Access and open the database that contains the tables you want to establish relationships between.
2. Navigate to Relationships View:
- Click on the "Database Tools" tab in the ribbon at the top of the Access window.
- In the "Show/Hide" group, click on the "Relationships" button. This will open the Relationships window.
 |
| Creating & Saving Relationships in MS Access |
3. Add Tables:
- In the Relationships window, click on the "Design" tab in the ribbon.
- To access the Show Table dialog box, simply click on the "Show Table" button located in the Relationships group. This action will prompt the dialog box to appear, allowing you to proceed with your desired actions.
 |
| Creating & Saving Relationships in MS Access |
- Select the tables that you want to establish a relationship between and click the "Add" button for each table. Then, click "Close" to close the dialog box.
 |
| Creating & Saving Relationships in MS Access |
4. Create Relationship:
- Arrange the tables in the Relationships window so that you can see the fields you want to relate.
- Click and drag the primary key field (usually denoted with a key symbol) from one table to the corresponding foreign key field in the other table. The primary key field should be dragged to the foreign key field it relates to.
- The Edit Relationships dialog box will be opened by executing this action. Ensure that the field names match, and then click "Create" to establish the relationship.
 |
| Creating & Saving Relationships in MS Access |
5. Set Relationship Options (Optional):
- In the Edit Relationships dialog box, you can specify relationship options such as enforcing referential integrity and cascade update/delete options. Enforcing referential integrity ensures that related records are consistent and prevents orphan records.
- Check the box for "Enforce Referential Integrity" to maintain data consistency. You can also choose cascade update and cascade delete options if necessary.
- Click "OK" to save the relationship and close the dialog box.
 |
| Creating & Saving Relationships in MS Access |
6. Review and Save:
- Review the relationships in the Relationships window to ensure they are set up correctly.
- Click the "Save" button in the Quick Access Toolbar or press Ctrl + S to save the relationships.
By following these steps, you can create relationships between tables in Microsoft Access, enabling efficient data management and ensuring data integrity within your database.
DELETE relationship in MS Access
Deleting a relationship in Microsoft Access is a straightforward process. Here are the steps to delete a relationship:
1. Open the Database:
- Launch Microsoft Access and open the database that contains the relationship you want to delete.
2. Navigate to Relationships View:
- Click on the "Database Tools" tab in the ribbon at the top of the Access window.
- In the "Show/Hide" group, click on the "Relationships" button. This will open the Relationships window.
3. Locate the Relationship:
- In the Relationships window, locate the relationship that you want to delete.
- Relationships are represented by lines connecting fields between tables. You can click on the line to select the relationship.
4. Delete the Relationship:
- Once the relationship is selected, press the "Delete" key on your keyboard, or right-click on the selected relationship and choose "Delete" from the context menu.
- Alternatively, you can click on the "Design" tab in the ribbon, then click on the "Delete" button in the Tools group.
 |
| DELETE relationship in MS Access |
5. Confirm Deletion:
- Access will prompt you to confirm the deletion of the relationship. Click "Yes" to confirm and delete the relationship.
6. Save Changes:
- After deleting the relationship, save the changes to the database.
- Click the "Save" button in the Quick Access Toolbar or press Ctrl + S to save the changes.
By following these steps, you can easily delete a relationship in Microsoft Access, allowing you to manage your database relationships efficiently.
Conclusion
In conclusion , this blog cover a brief description of following topics Like
Relationship between tables, Relationships between Databases like one to one, one to many, many to one, many to many relationship and their examples.
In summary, I can say that these topics are related to DBMS(MS Access) Subjects and very helpful for those who pursuing BCA,PGDCA, DCA ,'O' Level Courses from different universities
I hope this blog helps you a lot Happy learning....
Frequently Asked Question(FAQ)
What is relationship in MS Access ?
In databases, relationships between tables define how the data in one table is related to the data in another table.
What is one to one realationship ?
In a one-to-one relationship, each individual entry in one table corresponds exclusively to a single entry in another table, and vice versa.
What is one to Many relationship?
In a one-to-many relationship, each record in one table can relate to multiple records in another table, but each record in the second table can relate to only one record in the first table.
What is Many to Many relationship?
In a many-to-many relationship, it is possible for multiple records in one table to be associated with multiple records in another table.
Post a Comment