Types of Relationship in Database Table
A relational database collects different types of data sets that use tables, records, and columns. It is used to create a well-defined relationship between database tables so that relational databases can be easily stored. For example of relational databases such as Microsoft SQL Server, Oracle Database, MYSQL, etc.
There are some important parameters of the relational database:
- It is based on a relational model (Data in tables).
- Each row in the table with a unique id, key.
- Columns of the table hold attributes of data.
Employee table (Relation / Table Name)
| EmpID | EmpName | EmpAge | CountryName |
|---|---|---|---|
| Emp 101 | Andrew Mathew | 24 | USA |
| Emp 102 | Marcus dugles | 27 | England |
| Emp 103 | Engidi Nathem | 28 | France |
| Emp 104 | Jason Quilt | 21 | Japan |
| Emp 108 | Robert | 29 | Italy |
Following are the different types of relational database tables.
- One to One relationship
- One to many or many to one relationship
- Many to many relationships
One to One Relationship (1:1): It is used to create a relationship between two tables in which a single row of the first table can only be related to one and only one records of a second table. Similarly, the row of a second table can also be related to anyone row of the first table.
Following is the example to show a relational database, as shown below.
One to Many Relationship: It is used to create a relationship between two tables. Any single rows of the first table can be related to one or more rows of the second tables, but the rows of second tables can only relate to the only row in the first table. It is also known as a many to one relationship.
Representation of One to Many relational databases:
Representation of many to one relational database
Many to Many Relationship: It is many to many relationships that create a relationship between two tables. Each record of the first table can relate to any records (or no records) in the second table. Similarly, each record of the second table can also relate to more than one record of the first table. It is also represented an N:N relationship.
For example, there are many people involved in each project, and every person can involve more than one project.
Difference between a database and a relational database
| Relational Database | Database |
|---|---|
| A relational database can store and arrange the data in the tabular form like rows and columns. | It is used to store the data as files. |
| The data normalization feature is available in the relational database. | It does not have a normalization. |
| It supports a distributed database. | It does not support the distributed database. |
| In a relational database, the values are stored as tables that require a primary keys to possess the data in a database. | Generally, it stores the data in the hierarchical or navigational form. |
| It is designed to handle a huge collection of data and multiple users. | It is designed to handle the small collection of data files that requires a single user. |
| A relational database uses integrity constraints rules that are defined in ACID properties. | It does not follow any integrity constraints rule nor utilize any security to protect the data from manipulation. |
| Stored data can be accessed from the relational database because there is a relationship between the tables and their attributes. | There is no relationship between data value or tables stored in files. |
Advantages of relational databases
- Simple Model: The simplest model of the relational database does not require any complex structure or query to process the databases. It has a simple architectural process as compared to a hierarchical database structure. Its simple architecture can be handled with simple SQL queries to access and design the relational database.
- Data Accuracy: Relational databases can have multiples tables related to each other through primary and foreign keys. There are fewer chances for duplication of data fields. Therefore the accuracy of data in relational database tables is greater than in any other database system.
- Easy to access Data: The data can be easily accessed from the relational database, and it does not follow any pattern or way to access the data. One can access any data from a database table using SQL queries. Each table in the associated database is joined through any relational queries such as join and conditional descriptions to concatenate all tables to get the required data.
- Security: It sets a limit that allows specific users to use relational data in RDBMS.
- Collaborate: It allows multiple users to access the same database at a time.