What Is A Relational Database
At its core Relational Database, is just a database. It is used to organize data in different ways. It is based on the relational model of data. This means that data is split across multiple content holders called “tables” and this data is interlinked with each other in a meaningful way. We will see how this works very shortly.
Table Of Contents
- 1 Basics of Relational Database
- 2 Structure of Relational Database
- 3 Rules for design of Relational Databases
- 4 Example of Relational Database
- 5 Relationships in Relational Database
- 6 What is a Key?
- 7 What is a Primary Key?
- 8 What is a Composite Key?
- 9 What is a Foreign Key?
- 10 Different types of Relationships/Cardinality in a Relational Database
- 11 When to use Relational Database and their benefits
- 12 Conclusion
Basics of Relational Database
As we know by now, a Relational Database is just another place to store data. This data could be of different forms and could relate to different information. For example, in corporate sense, these this data could represent employee information, employee salary information and any other information that the company needs to see, review and take decisions upon. This database can also hold an Inventory of products for an online store and also the orders made for each of the products. This database can also contain customer information when someone tries to purchase the products.
Structure of Relational Database
Relational Database comprises of multiple content holders called “tables“. Each table contains a series of horizontal rows and vertical columns. The columns describe the data that needs to be stored in this table whereas, the rows denote the data that is being entered and stored dynamically. This entire row is called as a record. This row is supposed to contain unique data instance for a corresponding column. Each row in a table has a unique key that can be used to link to rows in other tables.
Rules for design of Relational Databases
- Each table must have a unique name.
- Each table consists of multiple rows.
- Each row in a table should be unique.
- Every table must have a key to uniquely identify the rows.
- Each column in a table must have a unique attribute name, also referred to as the “column name”.
Example of Relational Database
Let’s consider an online store. This store contains inventory. There could be multiple products in the inventory and these products could be of different shapes, sizes, colours, etc. It would be hard and cumbersome to keep a manual track of this data.
To ease up this process of tracking and dealing with all the data, a Relational Database could be used and a new table called “inventory” could be created and details such as the product sizes, colours, etc. could be stored in this. When a customer tries to purchase one (or more) of the products in the inventory, they could look through the specifications of the items provided by means of the User Interface and make a decision. And finally, when the customer purchases any of the items, this information could be stored in another table called “orders”. These orders are now connected with the inventory table. This connection is called as a relationship, as It helped to connect the products with the purchases and this helps in taking sensible business decisions.
Relationships in Relational Database
As we have seen in the example above, multiple tables can be linked to each other and these “relationships” are used to link tables together. These relationships can also be called as “table associations”. Relationships are made using “Keys”.
What is a Key?
In a Relational Database, a key is an identifier that is used to uniquely identify a record. A key can comprise of a single field (column) or multiple fields (columns) grouped together. The keys are essential part in a database as they help in creating associations between tables, building relationships and to maintain consistency in data.
There are multiple types of keys available. They are:
- Primary key
- Composite key
- Foreign key
What is a Primary Key?
A primary key is a field whose value is used to identify any record in a table in a unique way. Usually there is only one field that serves the purpose of the primary key. There are also cases when this key may contain multiple attributes or multiple fields. For example, in an “orders” table, “orderID” would be the primary key for that table. This means that only this field is used to identify any record in this table.
What is a Composite Key?
There are times when a primary key comprises of multiple fields grouped together. This means that only when these two fields or multiple fields are grouped together they can be used to uniquely identify a record.
What is a Foreign Key?
A foreign key is simply a field whose value is the same as the primary key of another table and this table will contain a different field as a primary key.
For example, let us consider that we have two tables namely “customers” table and “orders“ table. A customer’s table will contain a primary key called “customerID“. The orders table will contain a primary key called “ordersID“. At the same time the orders table will also contain another column or field that goes typically by the name “customerID“ and it will point to the “customerID“ (which is the primary key) in the customers table. So, in the “orders“ table, the “customerID“ column is called as the Foreign key.
So here are some points to remember:
- Primary keys are always unique.
- Foreign keys may contain duplicate values.
- Relationship between different tables is created using keys.
Different types of Relationships/Cardinality in a Relational Database
There are multiple relationships that a Relational Database can use to connect to the data that is present in other tables. These relationships, also referred to as “Cardinality”, are:
- One to One: In this relationship, one table record relates or points to another record in another table.
- One to Many: In this relationship, one table points multiple records in another table.
- Many to One relationship: In this relationship, more than one table record points to another table record.
- Many to Many: In this relationship, more than one table record points to one record in another table.
When to use Relational Database and their benefits
There are multiple scenarios when we can use Relational Databases. Here are few examples:
- When you want to make sure that your data is not compromised.
- When you want to make sure that the data is not repeated that is reduced data redundancy.
- When you are working with large amounts of data.
- To secure the data from others.
- To back up the information that you possess.
- To replicate a new instance of the backup that you already have.
- When you need scalability with data that is being able to add new data without having the need to modify the existing records.
In this article we have covered the basics of a Relational Database and we have seen how it could be useful for your business or any other area that deals with data. Here are some key takeaway points:
- The Relational Database is a very widely used type of database management system.
- The database consists of data holders usually referred to as tables.
- A database can contain a single table or multiple tables.
- These multiple tables could be linked together in some meaningful ways.
- Different types of keys exist to uniquely identify the rows in a table. These keys are called primary key, composite key and foreign keys.
- Primary keys and foreign keys are used to create relationships.
- There could be multiple relationships between different tables. These relationships are: One to One, One to Many, Many to One and One to One.
- All these keys and relationships help in understanding and connecting the data in a better fashion.