As the name suggests, a database management system – or DBMS – is a sophisticated system of management protocols that govern a database and all the information it stores.
Starting up data scientists, digital product managers, and project managers require some basic understanding of how a database works to perform well in their roles. If you are a professional in any of these fields – or you're interested in understanding how a relationship in DBMS is established and works come from another source – this article is for you.
In this article, we will discuss how relationships within databases are built and run. We will also focus on understanding how these bonds among the elements within a database can be of various types. In addition, we will learn key differences between regular database management systems and relational DBMS. Eventually, we will also talk about how you can create impressive DBMS blueprints using a modern online whiteboarding tool.
Part 1. Introduction to the Notion of a Relationship in DBMS
You might remember that when you were a child, you learned how the people around you were related to each other. You had your parents, uncles, aunts, siblings, and cousins, among other relations. You also learned each of these relationships was different from the others. You had more freedom in some while others were more demanding. This is exactly how relationships in a DBMS work too!
Now, a relationship in a DBMS works as the central idea around which that particular database is built and managed. The nature of these internal relationships determines the way a database behaves and the information within it is used. To state it clearly, a relationship in DBMS tells us how any two sets of data are related and linked to each other.
Understanding this bond among the data sets also helps us learn how the information within the database will behave when queries for information are sent.
This knowledge is crucial, as it allows us to access information within the database easily, send appropriate queries for data access, and build effective DBMS protocols.
Before we talk about the 3 key types of DBMS relationships, we need to understand a few principles and concepts that make everything in database management work.
- Primary Key: This is the primary data set in the relationship in terms of how it relates to the requested information.
- Foreign Key: This is the other data set within the relationship, which is also helpful in accessing the related information.
Part 2. What are the Various Types of Relationships in DBMS?
Now that we understand what relationships in DBMS are like, let us learn what types of relationships are possible between any two data sets within a DBMS.
2.1 One-to-One DBMS Relationship
A one-to-one bond between two data sets means that a particular record in one of these two tables is linked to a corresponding entry in the other.
So, for instance, in the tables captioned “Citizen Name” and “Social Security Number” one entry in the names table is related to only one entry in the social security numbers table.
In other words, only one particular citizen is awarded that corresponding unique social security number from the other table. No other citizen will ever be assigned that number.
As the example suggests, this type of relationship in DBMS is established for information security. To elaborate, because the social security number gives its linked citizen certain benefits across the country, this information is kept secure. While we make a table with the names of all the citizens, a separate table is made with all the social security numbers so that these records are kept safe from prying eyes. Here is another example that should make that point with greater clarity.
In other words, there may be a separate table each for Employee Home Addresses, Employee Emails, Employee Phone Numbers, etc. You get the picture.
2.2 One-to-Many DBMS Relationship
In a one-to-many relationship, a unique record in one table may be related to multiple records or entries in another table or data set.
For instance, if John Doe is a unique entry in the table named “Employee Names,” this entry is linked to all the records and entries in a separate table captioned “John Doe – Employee Record”.
In this example, if the table with all the employees’ names is Table A, the entry with a particular employee’s name will be linked to all the records in a separate table (Table B) that contains only this employee’s information. While one entry in Table A is linked to many entries in Table B, each of these entries in Table B is only linked to one record in Table A, i.e. John Doe.
This relationship is used in databases where a data set contains unique entries while another has multiple entries linked to that one record. For instance, in bank account databases where a customer (an account holder with a unique name and identity in Table A) may hold multiple accounts in the bank (listed in Table B which contains all bank account numbers).
Similarly, a retail store may assign a unique customer number to each of its unique customers (in a table titled “Customers”, but a customer may place multiple orders (as recorded in the table captioned “Orders”).
2.3 Many-to-Many DBMS Relationship
Now that we know the first two types of relationships in DBMS, unlocking this final type is much easier. As the name suggests, multiple entries in one table may correspond with multiple records within another table.
A simple example of this relationship would be a “Students” table in a university database along with another table titled “Courses.” Any one student from the first table may be linked to multiple courses in the other table.
Similarly, any course in the second table may have multiple students from the first table enrolled in any given term.
Part 3. A Couple of Bonus Tips for You
If you have read this article so far, we are pleased to say you have already covered the basics of understanding relationships in DBMS.
Now, we have a couple of extra bits of useful knowledge for you.
1. Differences between DBMS & Relational DBMS
As you may have guessed by now, a DBMS can also be understood as a software program that organizes information within a database.
With that in mind, it would be useful to learn that there is an advanced version of these software programs – the highly specialized Relational Database Management Systems (RDBMS).
Since an RDBMS is a more efficient tool for database management, it offers a more robust set of information management features that you may not find in a regular database.
Here are 5 top differences between the two:
| DBMS | RDBMS |
|---|---|
| Data is stored in files. | Data is stored in tables. |
| Only individual records are accessible. | Multiple records are accessible. |
| It supports one user at a time. | It allows multi-user support. |
| It allows multi-user support. | Data redundancy is reduced significantly. |
| It does not support distributed databases. | It does not support distributed databases. |
2. Drawing DBMS Related Diagrams Online for Free
Now, if you are starting as a DBMS user in an authoritative role, you might frequently need to design databases with a lot of brainstorming. You might then also need to share your database concepts with your team.
To do that, you can use Boardmix, which is a highly capable online whiteboarding tool that allows users like you to make complex mind maps, brainstorming diagrams, and other more sophisticated visuals for free.
Conclusion
To sum up, a relationship in DBMS is the cornerstone of all information management done by the DBMS. It provides the basis for data categorization.
Without building databases with proper DBMS relationships, managing records is nearly impossible where a large number of records is involved. Even with smaller data sets, picking out the right information can be quite time-consuming without properly-constructed DBMS relationships among records.
