DEV Community: Pranjali Nandan

What is Foreign Key in DBMS?

Pranjali Nandan — Fri, 04 Mar 2022 10:15:22 +0000

Before understanding the concept of foreign keys, let’s understand what a key is. So, as we know, in a database, we store our data in the form of tables, in the form of rows and columns in a table. What if we want to connect two or more tables with each other? For that, we need to understand the concept of a key. So, using the concept of primary key and foreign key Let's understand what the foreign key is in this article.

A foreign key is a column or a group of columns in a database table which must match with some other column in another table. In simple words, we can say a column which is common in two tables can be considered a foreign key. Let’s understand more clearly what foreign keys are using an example given below.

This is a student table, in which we have roll no, name, class, and age as attributes. This type of storage of data is seen in our databases (in the form of tables). Let's explore another table named "Student Info Table."

As we can see, it is a table containing information about a student, such as fees, status, contact information, and address. If you see, both tables (student and student info) have a common column, which is Roll No. So rolling no column in the student info table will work as a foreign key and rolling no column in the student table will work as a primary key. Using the concept of a foreign key and a primary key, we can connect both the tables.

Foreign Key Query in MySQL

Let’s see how we can write code of foreign key in MySQL.
CREATE TABLE Student info ( Roll no int NOT NULL, Fees status varchar (30), Contact no int NOT NULL, Address varchar (50) NOT NULL, PRIMARY KEY (Roll no), FOREIGN KEY (Roll no) REFERENCES Student (Roll no) );

Let’s explore how we can add content to these tables.

INSERT INTO Student VALUE (21,’Ramesh’, ‘10th’,15); INSERT INTO Student VALUE (22,’Atul’, ‘3rd’,8); INSERT INTO Student VALUE (23,’Rahul’, ‘8th’,13); INSERT INTO Student VALUE (24,’Piyush’, ‘12th’,17); INSERT INTO Student VALUE (25,’Arnav’, ‘7th’,13);

Remove the Foreign Key Constraint

If we want to write a SQL command to drop a foreign key constraint, then we will write the following command.

ALTER TABLE Student DROP FOREIGN KEY

Why do we need Foreign Key?

Using the concept of a foreign key, we can connect two or more tables as we can create a relationship between those tables with a common attribute.
Foreign keys also help us to maintain the referential integrity of the database.

How does Foreign Key maintain Referential Integrity?

Let's understand this concept with the help of two tables? One is the student table, which contains the roll number, name, and address of a student, and the other table is the course table, which contains information about a student's course.

So, roll no in the course table (referencing table) will work as a foreign key and roll no in the student table will work as a primary key. Here, the student table is the reference table and the course table is the referencing table. So, the main thing we'll learn in this topic is what happens if we insert, update, or delete something in this table. So, let's explore that part also.

Insert – If we insert any entry into our base table, that is, the student table, then it will not create any issue. We can insert as many entries as we want, and it won’t create any issue for insertion. As a result, there is no violation in insertion.
Delete – If we delete any entry from our base table, like if any student leaves an organization, then we have to delete that data from our table. Let’s consider that roll no. 1 leaves the organization, and we delete that entry from the student table. But in the course table, we see that roll no. 1 is currently studying network subjects. So directly deleting will create an issue. Integrity will be lost in this case. But if we consider a case where we insert a new entry, let’s say roll no 4, and then we delete that roll no, then it will not create any issue. So, deletion may create a violation.

So, what do you think will be the solution to this issue? So, we have a concept of a delete cascade where the row that we are deleting will be automatically deleted from other tables also. Another method is to delete set null where the row we are deleting is found, and if the same row is found in another table, then we set the foreign key value null on that set. Another method is to delete data with no action. As the name suggests, it will not do any action, so data will be deleted in this case.

Update – If we want to update any row, then it will create an issue, like if we have to update that data in other related tables, as we saw in the deletion. We can use the same method that we perform on deletion, that is, on update cascade, on update set null, on update no action. Updating may result in a violation.

To learn more about the concepts of primary key and the distinction between primary key and foreign key on Scaler Topics

Author: Arnav Bhardwaj

Difference Between
and
Tag in HTML

Pranjali Nandan — Thu, 24 Feb 2022 11:09:14 +0000

Introduction

Suppose you want your webpage to show the below message with the same format, How will you write it in HTML?

If you are planning to use carriage return (ENTER key) to produce line breaks, then let me tell you that it won’t work because HTML will ignore any carriage return and extra spaces.

You can use the br tag which is used to produce line breaks in HTML and here are the two possible ways of using br tag.

Snippet 1:

<div> Hello <br> Welcome to <br> the blog<br> </div>

Snippet 2:

<div> Hello <br/> Welcome to <br/> the blog <br/> </div>

Both will give the same output, but what is the difference between br and br/?

But before that let’s look at some pointers.

br tag is used to produce line breaks in an HTML document and it is a self-closing tag.
When a tag is used with nothing between it, then a self-closing tag (or empty tag) can be used. br is one such self closing tag, which means there is no need of closing tag ( /br) while using br.
Generally, TAG/ (not to be confused with /TAG) is used as a condensed way of writing TAG.../TAG.

So from the above pointers, it is evident that br and br/ will provide the same results when used in HTML.

But, whenever we are using XHTML (which is more strict than HTML), it doesn’t allow leaving tags open (like you cannot write just br, you have to close it also but /br doesn't make sense since it is a self-closing tag ), hence br/ will be used in that case.

Also using br is less effective when it comes to code neatness and readability than br/. Hence br/ is generally preferred over br

To sum up, the key differences between br and br/ in HTML can be understood with the help of the following table:

Difference between br and br/ Tag

Browser Compatibility

Let’s look at its browser compatibility now

Desktop Browsers

Conclusion

Both br and br/ produces the same result i.e line break. But br/ is preferred because it can be used with strict rules as well(like while writing XHTML document) and the latter looks cleaner and readable.

Read more about other self-closing tags on Scaler topics.

Author: Rdiddhi Suteri

What is RDBMS ?

Pranjali Nandan — Thu, 17 Feb 2022 10:42:13 +0000

RDBMS stands for Relational Database Management System. Also called RDB (Relational Database), it is a database that stores data in tables (rows and columns) so that it can be used to form relations with other tables. The only difference between relational databases and simple databases is that we can easily create relationships with other tables using RDB. Most databases used these days are relational databases.
We can perform any type of operation on RDB, such as updating, deleting, viewing, and so on. Most relational databases use SQL as a language to access the database. SQL (Structured Query Language) is a programming language that is used to communicate with databases. It helps us create, update, or delete data in relational databases. The syntax of SQL is also very simple, making it easy to learn.

Let’s make RDB clear with the help of a simple example. Let's assume we have two tables; one is a student table in which we have student name and student roll number, and the other table is an info table, which has roll number and total marks. So, we can make a relationship between these two tables and find out the total marks of individual students. Note that we have a common column (student roll number) in these two tables. Some of the examples of relational database management systems are mentioned below:-

MySQL
Oracle DB
PostgreSQL
SQLite
SQL Server

RDBMS Operation

A relational database management system works in tables; it stores data in the form of tables. There can be any number of tables that contain rows and tables. Every table in database has unique primary key. As we know, tables have rows and columns, so let’s understand the role of these rows and columns. So, a row records the data of an individual entity, and a column holds the data for a specific field. Using SQL, we can run a query to show some specific results. SQL has a term called SQL Constraints. They are used for some rules for data before it enters the table. Let's explore some constraints given below:-

NOT NULL - It ensures that a column doesn’t have any empty values or NULL values.
UNIQUE - It ensures that no two values in a given column are the same.
PRIMARY KEY - A primary key is a group of columns in a table that uniquely identify the row.
CHECK – This function ensures that columns adhere to any given condition.

Why RDBMS?

The relational database management system is very safe. If for any reason, our programme crashes, our data will remain safe. There are many security layers, so the data will remain safe.
In relational databases, accessing the data is easy and if we want to perform any action like deleting, updating, etc., such a type of action is easy to implement.
A relational database can manage large amounts of data and this data is stored in the form of rows and columns in a table.
Using a foreign key, we can link two or more tables and then work on them according to our requirements.
We can also give permission to multiple users so that they can work individually according to their needs.
Management of relational databases is also very clean and effective as data is stored in the form of rows and columns.

RDBMS Advantages

Below are some of the advantages of relational databases:

Relational databases are very flexible in nature. Let's understand this by taking an example. Assume that we have an employee table, and if we need to update any information about an employee, rather than updating it in each file, we simply update it in the main file, and RDBMS automatically updates this information in every file of the database. By doing this, we can save a lot of time.
Relational databases can be accessed at any time, and they are also very easy to use. Relational databases allow database admins to control their events, like maintaining data, updating data, etc. Backing up data is also simple with the help of RDBMS and some automation tools.
The database admin of a relational database has control over the database and can give specific access to the required user. It cannot give all access to all users. Access is given according to the user’s need.
As we know, relational databases use rows and columns to store their data, so tables are very comfortable for users to understand. Also, writing queries for RDBMS is easy.

RDBMS Disadvantages

A relational database has many advantages, but it also comes with some disadvantages. For example, to implement a relational database, we need some special software, so some special software needs to be purchased. This results in an extra cost. Also, to setup a relational database, we have to write millions of lines of code into RDBMS tables. For that, we need some extra programmers. Also, while doing this, we must take care that our data doesn’t fall into the wrong hands. Also, sometimes combining multiple tables can become more complicated in some cases.

Indexing in RDBMS

To get faster access to data, indexes are used. Suppose you have an employee table with 10 employees. If we sort the data in increasing order, we can easily find out the nth salary using indexes.

DBMS vs. RDBMS

Now let's explore the key difference between DBMS and Relational DBMS as given below:

In DBMS, only one user is accepted at a time, but in the case of relational databases, multiple users are allowed at a time.
Relational databases necessitate greater investment in software and hardware than DBMS.
As we know, relational databases make relational between multiple tables, so they can manage large amounts of data easily, but DBMS only manage a small amount of data as compared to RDBMS.
A relational database stores its data in the form of tables (using rows and columns), while a DBMS stores its data in hierarchical form.
Relational databases support distributed databases (it is a type of database that is present on multiple sites, i.e., on multiple computers or over a network of computers; it is not limited to only one user), while DBMS doesn’t support distributed databases.
Relational databases can be normalized, but in the case of DBMS, it is difficult to normalize them.
Relational databases follow ACID properties (Atomicity, Consistency, Isolation, Durability) while storing the data, while the DBMS doesn’t follow ACID properties.

For a better understanding of difference between DBMS and RDBMS, refer to Scaler Topics

Author - Arnav Bhardwaj

How to Prevent Deadlock in DBMS?

Pranjali Nandan — Thu, 10 Feb 2022 11:39:48 +0000

Before proceeding directly to the prevention of deadlock in DBMS, let's understand what deadlock is. Deadlock is a condition where one or more processes are waiting for a resource indefinitely, where this resource is acquired by another process.
For greater clarity, consider the following scenario: we have two resources, say R1 and R2, and we can have two processes, say P1 and P2. P1 has R2 resource, and P2 has R1 resource; therefore, P1 requires both R1 and R2 resources to complete its process, and P2 requires both R1 and R2 resources to complete its process. P1 process now holds R2 resource and waits indefinitely for R1 resource, similar to P2 process. So, these conditions are deadlock conditions where some processes are blocked because they hold some resources and are waiting for other processes that were held by other processes, so this waiting will never end until resolved.

So, we need to understand the conditions that can lead to a deadlock. So, according to Coffman, there are four conditions where deadlock can occur, which are mentioned below:

Mutual exclusion
Circular waiting situation
Condition of hold and wait
There is no preemption condition.

Prevention of Deadlock

When any transaction starts to execute, the database management system will inspect all its operations so that a deadlock condition will not occur. If it finds in any condition that a deadlock condition can occur, then that transaction is not allowed to be executed.
We can avoid deadlock by avoiding one or more of the four Coffman's conditions.

Removing mutual exclusion, i.e., all the resources must be shareable.
Removing the hold and wait condition
Preemption of resources
Avoid the condition of circular waiting.

Methods for Preventing Deadlocks

Deadlock prevention is very suitable for large databases. If all the resources were arranged in such a manner that the process did not need to wait for resources, then the deadlock could be prevented. The database management system analyses the situation of operations of a transaction and if there is any chance of deadlock occurring, then that transaction will never be executed. Let’s look at some methods of prevention:

Wait-Die
Wound-Wait

Wait-Die

The Wait-Die scheme is a non-preemptive technique to use for preventing deadlock. Suppose we have two transactions, T1 and T2.
When T1 requests data currently held by transaction T2, in this case, T1 is allowed to wait if and only if its timestamp is smaller than the timestamp of T2.
(In simple words, it means T1 is older than T2). If not, then T1 is going to die or be killed.
That is why this scheme is known as the "WAIT-DIE SCHEME."

In this technique, two possibilities may occur:

a) T1 timestamp < T2 timestamp = It means if T1 is older than T2, then T1 is allowed to wait until the resource is not provided to T1.
b) T1 timestamp > T2 timestamp = It means if T1 is younger than T2, then T1 is going to die. It is not allowed to wait.

So more precisely, we can say that if the transaction is older than the other transaction, then it has the right to wait, otherwise it is going to die.

Example
Let's consider this example. It will clear up all your doubts and the above theory in a simple manner.

Suppose we have a transaction T1, T2, T3 having a timestamp of 2, 4, 6, respectively.

If T1 requests a resource that is held by T2, then T1 has the right to wait. What's the deal? because the T1 timestamp is earlier than the T2 timestamp
If T3 requests a resource that is held by T2, then T3 is going to die. What's the deal? because the T3 timestamp is more recent than the T2 timestamp

I hope it is clear to all of you now.

Wound-Wait

The Wound-Wait scheme is a preemtive technique used in the prevention of deadlock in DBMS. It is the total opposite of the wait-die scheme.
Suppose we have two transactions, T1 and T2. If T1 requests data or a resource which is held by T2, then T1 is allowed to wait if and only if T1's timestamp is greater than T2. Otherwise, in this case, T2 is going to die instead of T1.
That means T2 is wounded by T1. That is why this scheme is known as the "Wound-wait" scheme in DBMS.

In this technique, 2 possibilities may occur, like in the wait-die scheme:
a) T1 timestamp < T2 timestamp = In this case, T2 is going to be killed by T1 and it will restart at a random delay but with the same timestamp.
b) T1 timestamp > T2 timestamp =It means if T1 is older than T2, then T1 is allowed to wait until the resource is not provided to T1.

Example
Let's consider this example. It will clear up all your doubts and the above theory.
Suppose we have a transaction T1, T2, T3 having a timestamp of 2, 4, 6, respectively.

If T1 requests a resource that is held by T2, then T1 is going to force T2 and it will wound T2.
If T3 requests a resource that is held by T2, then T3 has the right to wait.

For a better understanding of deadlocks, Read more about it on Scaler Topics