Trending March 2024 # Understanding Transaction Management In Sql # Suggested April 2024 # Top 7 Popular

You are reading the article Understanding Transaction Management In Sql updated in March 2024 on the website Moimoishop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 Understanding Transaction Management In Sql

This article was published as a part of the Data Science Blogathon.

A transaction is a set of operations carried out as a logical unit of work. It is a logical work unit that includes one or more SQL statements. A database system must ensure that transactions are properly executed even if they fail completely or partially. A transaction is most commonly used in banking or the transaction industry.

The below pictorial representation explains the transaction process:

3. Debiting from the Cust A account is successful, but crediting from the Cust B account is not.

The first condition indicates a successful transaction, while the second is less important. We are not required to retransmit, but the third condition will cause a problem if the first operation is successful but the second one fails due to a technical issue. As a result, the Cust A account will be debited but the Cust B account will not be credited. This implies that we will lose the data. We can use transaction management to solve all of these issues. A transaction guarantees that either a debit or a credit will be performed, or that nothing will be performed.

Using Transaction in SQL

The transaction control mechanism uses the following commands:

BEGIN: To start a transaction.

COMMIT: To save changes. After the commit command, the transaction can’t rollback.

SAVEPOINT: Provides points where the transaction can rollback to.

ROLLBACK: To rollback to a previously saved state.

1. Begin transaction

It denotes the beginning of a local or explicit transaction.

Begin transaction T1

2. Commit Transaction

The database modifications are permanently reflected through commits. “COMMIT” is the statement that initiates the transaction.

Commit;

3. Rollback

Rollback is used to undo modifications, meaning that the record will remain in its original state and not be modified. “ROLLBACK” is the statement that initiates the transaction.

rollback;

4. savepoint

savepoint s1;

Let’s see one example of a transaction in SQLPlus :

First, create a table:

           id int,            name varchar(15),            last_name varchar(15),            address varchar(20));

Table created.

Insert Records in table student:

insert into student values(2,’uday’,’patel’,’valsad’) insert into student values(3,’om’,’parekh’,’bardoli’) insert into student values(4,’anjali’,’soni’,’ahmedabad’) insert into student values(5,’aarav’,’soni’,’baroda’);

Now we are going to check whether records are inserted or not by using following command

Now let’s try to understand the concept of commit, rollback and savepoint on the above table student.

Now, I am going to update the address of the student whose id is 5.

1 row is updated.

Commit Complete.

So here we have changed the data of students and saved changes successfully by using the commit command.

Now, we are going to create savepoint by following :

 Savepoint created.

To understand this concept better,  we are going to delete the record which I have updated in the last statement.

1 row was deleted.

Let’s check the updated table after the deletion of one record.

So I have deleted the last row which I have updated but now I want to undo the deletion operation. So we have already created savepoint before the delete operation. So we will roll back to that savepoint and will get records back. So let’s see.

Rollback complete.

Above code undo changes upto savepoint s1 and we will get the original table before the creation of savepoint.

So this is how the transaction works in SQL.

Now we’ll see the basics of  “Transaction Management”

A transaction is made up of four properties, which are also known as ACID rules.

ACID Properties

4. Durability

1. Atomicity

In this transaction, if Rs. 100 is deducted from account A then it must be added to account B.

Let’s assume the following transaction T which has two transactions T1 and T2:

If the transaction fails after completing T1 but before completing T2, the amount is deducted from P but not added to Q. consequently, the database is in an inconsistent condition. As a result, the transaction must be completed in its entirety to ensure that the database state is correct.

2. Consistency

After any transaction, the database must maintain consistency. If the database was consistent before a transaction, it must be consistent once the transaction is finished. In our example, the total of P and Q must remain the same before and after the execution of the transaction.

3. Isolation

Changes made in one transaction are not visible in other transactions until that transaction is committed. The results of intermediate transactions must be hidden from continuously running transactions. For any pair of transactions, one should begin execution only after the other has been completed.

• So transaction results should not be accessible to another transaction until the first transaction is completed.

4. Durability

• Our transaction must be saved permanently once we’ve completed the last step (step 6). If the system fails, it should not be eliminated.

If the database is lost, however, the recovery manager is in charge of ensuring the database’s long-term viability. We must use the COMMIT command to commit the values every time we make a change.

Schedule

A schedule is a method of combining transactions into a single batch and executing them in a specific order. It is the order in which instructions in a system are performed in a chronological (sequential) order. In a database, a schedule is essential because when multiple transactions run in parallel, the outcome of the transaction may be influenced. This means that if one transaction updates the values that the other transaction accesses, the sequence in which these two transactions execute will affect the outcome of another transaction. As a result, a schedule is made to carry out the transactions.

Kindly see the below example of the schedule:

Serial Schedule

A serial schedule is one in which no transaction begins until the previous one has been completed, and transactions are carried out one after the other. Because transactions are processed in sequential order, this type of schedule is known as a serial schedule. The above schedule is an example of a serial schedule.

To tackle this difficulty, we allow a transaction scheduled to be executed in parallel if its transactions are serializable or have some equivalence connection.

Equivalent Schedule

As a result, this equivalence isn’t widely thought to be noteworthy. Following is the example of an Equivalent schedule where both the schedules are producing the same result after Execution.

Using transactions enhances performance; for example, inserting 100 entries using transactions takes less time than standard insertion. In a typical transaction, COMMIT would be executed after each query execution, increasing the execution time each time; however, in a transaction, there is no need to run the COMMIT statement after each SQL query. In the end, COMMIT would permanently reflect all of the modifications to the database. Also, if you use a transaction, reversing the modifications is a lot easier than it is with a regular transaction. ROLLBACK will undo all of the modifications at the same time, restoring the system to its former state.

Conclusion

This article will provide a fundamental understanding of SQL transactions along with the ACID Properties. In relational database systems, transactions using SQL  are beneficial because they ensure database integrity. We have also seen the basics of scheduling which can able to manage multiple transactions which are running concurrently. so we can see that by using scheduling,  operations are performed based on the sequence and only one operation can be performed at a time.

Are you preparing for a job interview? Read this article on 20 SQL Coding Interview Questions and strengthen your concepts more than ever!

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Related

You're reading Understanding Transaction Management In Sql

Understanding Strategic Project Management (Spm)

The losses incurred from project failure in the past are huge. You might have heard of the financial disaster Coca-Cola experienced when it launched New Coke back in 1985. They planned to launch a new coke, which failed miserably. The product didn’t receive the attention the company had planned, resulting in a loss of $4 million in development and $30 million in inventory that remained unsold.

Coca-Cola isn’t the first and only brand to experience a catastrophe like that. Nearly all major brands and small companies experience some kind of loss or failure when executing a project. When you plan a project, there’s no guarantee the end product will turn out exactly as you planned.

There are many reasons to be blamed. Poor communication, poor vision, and inability to plan the project precisely can have a devastating impact on your project execution. That’s why strategic project management is crucial to the success of projects of all complexity levels. In this post, we will discuss everything you should know about strategic project management, its benefits, and how to execute SPM. Read on.

What is Strategic Project Management?

Having a strategy for a project gives your teams and stakeholders a clear picture of what deliverables to expect and by when. No matter how small, projects executed without a strategy can lead to delays, overspending, and poor quality. So, what exactly is SPM?

Strategic Project Management shows you the bigger picture of how certain types of projects will affect the company’s efficiency and your business as a whole. It’s based on a belief that modern projects can be fulfilled better when considering all aspects of project management instead of just the three vitals-budget, scope, and time. This can be achieved by combining project management methodologies with different frameworks to achieve organizational objectives. The main purpose of SPM is to ensure that projects are completed successfully and each project is aligned with your company’s goals.

How to Implement Strategic Project Management?

There’s no major change in your current management approach. You just need to look at your management processes through a strategic lens. You will still follow the traditional methodologies and frameworks, but it has a broader scope and is focused on aligning your strategy with your business goals.

Align Your Strategies with Business Goals

The most common cause of project failures is the lack of proper alignment between your projects and business strategies. Experts believe your success rate for all kinds of projects will increase dramatically when your projects contribute to your company’s goals. The question is how to do that.

You must identify the pattern that causes project failure. Take a look at your finished project portfolios and see where you are lacking or what’s the main cause of these failures. For example, if a majority of your projects fail due to poor communication, set communication as the important criterion for understanding the project’s feasibility.

Before you start work on a project, make a list of all projects and identify the impact they have on your staff, different departments, and the organization as a whole. Projects that are not aligned with your business strategy won’t be profitable for you in the long run. Develop a strategic project management plan to figure out if the projects are good for your organization and if they have an impact on your business plans.

Build a Culture that Promotes Strategic Project Management

When you build a culture around Strategic Project Management, the stakeholders and other people involved in your business won’t face any trouble understanding their roles and responsibilities in the project. To achieve that, you must create a committee that’s responsible for bringing cultural change to the organization.

This committee should be responsible for ensuring that SPM is followed in all management operations. Building a culture where every employee, manager, leader, and stakeholder follows strategic project management will enable you to handle all management operations effortlessly.

Implementing the Right Project Management Methodologies

Project managers are task-oriented. The focus isn’t always on strategies but on the duties that each employee has to fulfill. While that’s important, managers need to see the bigger picture. How is their project planning affecting other areas of their business?

Are the project methodologies or frameworks they have adopted feasible? Do they generate the planned outcome? As mentioned earlier, anything that’s not aligned with the company’s objectives should be dropped immediately. There’s no point in executing a project that doesn’t benefit the organization.

Encouraging Knowledge Retention

Each project teaches you the right ways to finish manual tasks, the right management strategies, and a perfect framework that simplifies all complex management operations. This helps you manage your budget, allocate resources, and finish everything within the timeframe.

To reap the benefits of a project management strategy, it’s important that you retain knowledge. This can be done by documenting the lessons learned from each project. After completing the project, you should organize a meeting and invite each stakeholder and employee who worked on the project. Identify the areas you could have improved, whether the results are satisfying, and what you could have done better to improve the project outcome. All these factors are part of your Strategic Project Management.

Tips for Implementing the Strategic Project Management (SPM)

Build a system to execute strategic planning

Monitor if your project is achieving the organizational goals, budget, quality standards, regulatory compliance, and delivery targets.

Conduct regular meetings with your stakeholders and clients to see if your project management processes contribute to the company’s goals and how exactly these processes benefit your business.

Conclusion

Most companies have already selected and deployed their chosen project management methodologies years ago. However, they are looking at project management from a strategic perspective. The focus has been shifted to identifying projects that offer better value for the company and the clients. That’s how aligning your project goals with organizational strategies can benefit your business in the long run.

States Of Transaction In Dbms

Introduction to States of Transaction in DBMS

Hadoop, Data Science, Statistics & others

In this article, we will learn about different states of the transaction in DBMS and also see what rules are followed by each of the state and how each state defines the behavior of the transaction at that particular moment.

Different States of the Transaction in DBMS

Let us discuss different states of the transaction in DBMS.

1. States of Transaction

What happens on the DBMS transaction is that number of small read and write operations are performed on the database and together small tasks of read and write tasks performing similar work are collectively called as transaction. A transaction consisting of group of small tasks can either commit or fail. In between the initialization and completion of the transaction, there are different states involved in the process. The different states of the transaction are described below in detail –

2. Active State

Whenever any database operation is performed on the database like updation, deletion, or insertion which involves the change in the database or even the select operation which involves the retrieval of the data from the database the transaction is started and the beginning state of every transaction is the active state in which whatever instructions are involved in the transaction are and if all the operations are executed successfully then the transaction goes into the partially committed state. In case if any problem occurs while executing any of the instruction in the transaction then the transaction goes into the failed state.

3. Partially Completed State

The main purpose for having this state is that whenever we perform database operations the transactions may involve huge amount of changes to be made to the database and in case of power failure or any other technical issue if the system fails then the transaction will lead to inconsistent changes made to the database. To avoid this, we firstly make all the changes to the local repository or reader buffer or main memory for temporary basis so that in case of failure the changes can be reverted and transaction can be rollbacked. This will lead to the implementation of consistency property of the ACID properties of transaction which is the expected behavior for any transaction.

4. Failed State

In case if any of the instruction fails to perform correctly or the changes are being saved to the memory properly then the transaction comes into the failed state.

5. Aborted State

In case if any failure occurs while executing instructions or while making the changes to the memory the state of the transaction changes to aborted after failed where all the changes made to the local memory and the buffer reader are reversed that is deleted or rollbacked.

6. Committed State 7. Terminated State

If there is any problem while executing the transaction, instruction of change reflection and the transaction is in the failed state then the transaction the changes are rollbacked and the transaction comes into terminated state after aborted state. Also, in case if all the operations are performed properly and changes are reflected the database appropriately then the transaction comes into the terminated state after committed state. In the terminated state the transaction is finally completed and the system is ready and consistent for the upcoming transaction to be taken and the old transaction to be stopped.

In case if the transaction fails and goes to aborted state then the database recovery system of DBMS has two options. The first one being the uncompleted, inconsistently or partial the system can restart the same transaction from beginning or the system will kill the ongoing transaction and will make itself free for any further tasks or transaction that the user will begin.

Example

Consider a small example where the businessman wants to save the total sale of whole day in the database. In this case, the transaction will involve the retrieval of all the bill records for the day and then the transaction will enter into the active state where the calculation of the total sale amount of the day will be made. After that if calculation is done successfully then the transaction will move to partially committed state else will go to the failed state. In partially committed state the system will save the total calculated amount to the local main memory and if this is done successfully then the transaction will move on to commit state or will go to a failed state. In committed state the total amount will be saved permanently to the database and the transaction will move onto the terminated state. In case of the failed state transaction the changes are rollbacked and transaction goes to the aborted state where system becomes free.

Conclusion

A transaction in DBMS involves multiple states which define the condition and situation of the transaction at a particular moment. A single transaction can be only in a single state at a given time.

Recommended Articles

This is a guide to States of Transaction in DBMS. Here we discuss the introduction, Different States of the Transaction in DBMS respectively. You may also have a look at the following articles to learn more –

Understanding Call, Apply And Bind In Javascript

When it comes to JavaScript, there are three methods that are used to control the value of ‘this’ inside a function. These are: Call, Apply and Bind. Although they are similar in functionality, there are some key differences between them. In this article, we will explore the concept of Call, Apply and Bind in JavaScript, provide code examples to illustrate their usage, and explain related concepts that can help you understand the topic better.

What is the concept of Call, Apply and Bind?

Call, Apply and Bind are methods that are used to set the value of ‘this’ inside a function. When a function is invoked, the value of ‘this’ is determined by how the function is called. In other words, it depends on the context in which the function is executed. By using Call, Apply and Bind, we can change the value of ‘this’ to a specific object, which allows us to control how the function behaves.

Call

The Call method is used to call a function with a given ‘this’ value and arguments provided individually. It takes the object to be used as ‘this’ inside the function as the first argument, and the remaining arguments are the arguments to be passed to the function. Here is an example:

const person = { fullName: function() { return this.firstName + " " + this.lastName; } } const person1 = { firstName: "John", lastName: "Doe" } const person2 = { firstName: "Mary", lastName: "Doe" } console.log(person.fullName.call(person1)); console.log(person.fullName.call(person2));

In the example above, we have a ‘person’ object with a ‘fullName’ method. We also have two other objects, ‘person1’ and ‘person2’. We use the ‘call’ method to call the ‘fullName’ method with ‘person1’ and ‘person2’ as the ‘this’ value. This allows us to reuse the ‘fullName’ method for different objects.

Apply

The Apply method is similar to the Call method, but it takes an array of arguments instead of individual arguments. It also takes the object to be used as ‘this’ inside the function as the first argument. Here is an example:

const numbers = [5, 6, 2, 3, 7]; const maxNum = Math.max.apply(null, numbers); console.log(maxNum);

In the example above, we have an array of numbers. We use the ‘apply’ method to call the ‘Math.max’ method with the array of numbers as the arguments. This allows us to find the maximum number in the array.

Bind

The Bind method is used to create a new function with a specified ‘this’ value and arguments. It returns a new function that can be called later. It takes the object to be used as ‘this’ inside the function as the first argument, and the remaining arguments are the arguments to be passed to the function. Here is an example:

const person = { fullName: function() { return this.firstName + " " + this.lastName; } } const person1 = { firstName: "John", lastName: "Doe" } const person2 = { firstName: "Mary", lastName: "Doe" } const fullName1 = person.fullName.bind(person1); const fullName2 = person.fullName.bind(person2); console.log(fullName1()); console.log(fullName2());

In the example above, we have a ‘person’ object with a ‘fullName’ method. We also have two other objects, ‘person1’ and ‘person2’. We use the ‘bind’ method to create two new functions, ‘fullName1’ and ‘fullName2’, with ‘person1’ and ‘person2’ as the ‘this’ value. This allows us to reuse the ‘fullName’ method for different objects.

Related Concepts

There are some related concepts and methods that can help you understand Call, Apply and Bind better. These are:

Arrow Functions: Arrow functions do not have their own ‘this’ value. Instead, they use the ‘this’ value of the enclosing lexical scope. This means that they cannot be used with Call, Apply or Bind.

Function.prototype.call(): This is a method that is used to call a function with a given ‘this’ value and arguments provided individually. It is similar to the Call method, but it takes the function as the first argument instead of the object.

Function.prototype.apply(): This is a method that is used to call a function with a given ‘this’ value and arguments provided as an array. It is similar to the Apply method, but it takes the function as the first argument instead of the object.

Conclusion

In summary, Call, Apply and Bind are methods that are used to control the value of ‘this’ inside a function. Call is used to call a function with a given ‘this’ value and arguments provided individually, Apply is similar to Call but takes an array of arguments, and Bind is used to create a new function with a specified ‘this’ value and arguments. By understanding these methods, you can write more flexible and reusable code in JavaScript.

How To Download Your Transaction History On Crypto.com

This includes your buys, sells, bonuses, and more.

On chúng tôi you’ll be able able to export your transaction history to CSV and download it.

In addition, you can choose the transaction type to export.

This includes your crypto wallet, fiat wallet, and chúng tôi Visa Card.

However, you can only export one transaction at a time.

In this guide, you’ll learn how to find and download your transaction history on the chúng tôi app.

To download your transaction history on chúng tôi you need to navigate to “Accounts” and tap on the clock icon.

Next, tap on the export icon, select the transaction type, select the date range, and download your transaction history.

The transaction types include your crypto wallet, fiat wallet, and chúng tôi Visa Card.

However, you can only select one transaction type at a time.

The maximum date range you can set is 3 years.

You can set the date range by adjusting the “From” and “To” dates.

Then, tap on “Export to CSV” to export your transaction history to CSV.

Lastly, tap on “Export History” and tap on “Download” to download your transaction history.

1. Open the chúng tôi app

Firstly, you need to open the chúng tôi app.

Once you’ve opened the app, log in to your account if you haven’t already and enter your passcode.

You can also use the desktop version of chúng tôi for this.

However, in this guide, we’ll use the chúng tôi app instead because it’s more popular among users.

2. Navigate to “Accounts” and tap on the clock icon

After you’ve opened the chúng tôi app, you need to navigate to your transaction history.

To do so, tap on “Accounts” on the bottom navigation bar.

After you’ve tapped on “Accounts”, you’ll land on the “Accounts” page.

On the “Accounts” page, you’ll see your crypto wallet, crypto earn, and fiat wallet balances.

On the top navigation bar, you’ll see a couple of icons.

This includes the convert icon and the clock icon.

Tap on the clock icon to see your transaction history.

3. Tap on the export icon

After you’ve tapped on the clock icon, you’ll land on the “Transaction History” page.

This includes your buys, sells, bonuses, and more.

You can also export your transaction history.

To do so, tap on the export icon on the top navigation bar.

4. Select the transaction type and date range

After you’ve tapped on the export icon, you’ll land on the “Export” tab.

On the tab, you’ll see a dropdown box and a couple of fields.

Firstly, you need to select a transaction type.

For example, if you want to download your transaction history from your crypto wallet, select “Crypto Wallet”.

You can only select one transaction type at a time.

Secondly, you need to select a starting date.

To do so, tap on the “From” field and select the starting date (e.g. 1 Jan 2023).

Thirdly, you need to select an ending date.

To do so, tap on the “To” field and select the ending date (e.g. 9 Nov 2023).

Lastly, tap on “Export to CSV” to export your transaction history to CSV.

5. Download your transaction history

After you’ve tapped on “Export to CSV”, your transaction history will be exported to CSV.

To find it, tap on the “Export History” tab.

After you’ve tapped on the “Export History” tab, you’ll see a list of your exports.

Tap on “Download” next to the export to download your transaction history.

You’ve successfully learned how to find and download your chúng tôi transaction history!

Conclusion

The purpose of an account transaction history is to keep track of your trading activities.

You can then download those transactions for tax filing.

This is developed to adhere to the Canadian, North American, and Australian tax requirements.

For more information, you can reach out to chúng tôi support via the in-app chat.

Further reading

How to Download Your Coinbase Transaction History

How to Find Your Transaction History on Binance (For Taxes)

Best Binance Referral ID Code in 2023

How To Normalize Relational Databases With Sql Code?

Introduction

Data is the new oil in this century. The database is the major element of a data science project. To generate actionable insights, the database must be centralized and organized efficiently. If a corrupted, unorganized, or redundant database is used, the results of the analysis may become inconsistent and highly misleading. So, we are going to learn how to normalize an RDBMS with the help of SQL programming.

This article attempts to explain how to remove inconsistencies in relational databases using normalization and a dummy database, for example. Before delving deeper, one must know a few related terms.

Learning Objectives

Know why normalizing a database is important

Understand the normal forms used in the industry

Learn how to convert a given database into the desired Normal Form using SQL.

This article was published as a part of the Data Science Blogathon.

Table of Contents What is a Relational Database?

Imagine a retail store wanting to store and manipulate data on their sales. Ideally, this data will contain the Item Code, Item Name, Price of each unit, Number of units sold, Category, and Date of Purchase. So, all these attributes are the names of the columns, and a particular row contains data on the purchase of a particular product on that day. Hence, here interrelated data has been collected and stored in rows and columns. This is what we call a “Relational Database.” An RDBMS (Relational Database Management System) is used to store this data.

If we go for a formal definition of a database, it can be said that it stores interrelated data from which users can efficiently retrieve information. Relational databases are structured, self-describing, and storing data according to predefined relationships.

But real-time data stored in this model can be messy most of the time, making it inconsistent and hard to work with. So, to easily manipulate and work with these data tables, we normalize the database.

Terms that are Commonly used in an RDBMS

Now, let’s delve deeper into the practical aspects of normalizing a database. But first, we must know a few terms to help us on the journey.

Data Dictionary: Contains all information about the objects in a database.

Metadata: Contains information about what the data stores, sources of data, etc.

Schema: A Schema is the internal design of the database, i.e., how the data looks at different levels to users.

Primary Key: Primary key is that attribute used to identify a tuple or row in a database uniquely. If it contains more than one attribute, it is called a composite key. It cannot contain redundant or null values.

Candidate Key: The candidate key is the attribute or combination of attributes that can uniquely identify a tuple.

Super key: A Super key is a set of attributes used to identify a tuple in a related database uniquely. This being said, each attribute in the Super Key may contain repeated or redundant values (which is not in the case of primary).

Foreign Key: A foreign key is a primary key of another table that becomes an attribute or field in a different table.

This article mainly focuses on how to normalize a database. But first,

Why and When Should we Normalize a Relational Database?

We normalize a database because:

It makes it easier to organize the database,

It sequentially decomposes tables and hence, reduces data duplication,

It reduces the storage space taken by the database,

It removes specific anomalies and makes updating, deleting, and inserting data easy.

We normalize the data if:

The database is messy and takes up much storage space.

The database has multiple values in a single cell.

No such attribute or column is present in the database that can help identify every tuple uniquely.

Duplicate or repetitive values are present in the database.

How to Convert a Relational Database to Different Normal Forms?

So, let’s use a practical example to understand how normalization works. Consider a hospital that maintains its database in the form:

Step 1: First, you need to identify the relationships that exist between each attribute of the database. Here the relationships are:

One-to-one:

Patient_Id and Patient_Name

Disease_Id and Disease

Test_Id and Test

One-to-many: 

Category and Disease_Id/Disease

Category and Doctor_Name

Many-to-one:

Doctor_Name and Category

Many-to-many:

Doctor_Name and Patient_Id/ Patient_Name

Test and Patient_Name/ Patient_Id

We can create the table in PostgreSQL using the following SQL code:

create table Patient_Information( Patient_id varchar(10) NOT NULL, P_Name varchar(100), Age integer, Marital_Status varchar(10), Gender varchar(10), Disease_Id varchar(10), Disease varchar(100), Category varchar(100), Doctor_Name varchar(20), Test_Id varchar(10), Test varchar(40), Price float, PRIMARY KEY(Patient_Id) );

Note: All SQL codes mentioned further in the blog are extended from those mentioned earlier.

First Normal Form or 1NF

To convert the first table to 1NF, we must ensure that every column consists only of a single data, and the attribute domain must not change. So, the relationship will become:

Patient_Information:

1200

transformations to bring a database to 1NF before applying further normalization techniques.

Now we insert data into the table using the code:

INSERT INTO Patient_Information VALUES('P001', 'Anna Smith', 18, 'F', 'HYTSH', 'Hypothyroidism', 'Dr Rohit Shaw', 'T4', 'Thyroxine', 400);

Second Normal Form or 2NF

To convert into 2NF, the relation must be in 1NF, and no partial dependency should be chúng tôi let’s consider the table as follows:

Patient_Id Disease_Id Test_Id Test Doctor_Name

P001 HYTSH T4 Thyroxine Dr. Rohit Shaw

P001 HYTSH TSH Thyroid Stimulating Hormone Dr. Rohit Shaw

P002 HRTBLCK ECG Echocardiography Dr. Rima Dhara

P002 HYPTSN BP Blood Pressure Dr. Rima Dhara

P003 PCOD USG Ultrasonography Dr. Shalini Thakur

So, this part of the table stores the disease name with the disease’s id and the doctor’s id who specializes in treating that specific disease.

Secondly, by knowing the disease’s id, we can easily find which doctor is or can be allocated to the patient.

Primary key: Patient_id+Disease_id

But we see that the doctor’s name only depends on the disease’s id, not the patient’s. Hence a partial dependency will exist. To remove this partial dependency, we divide the table into two:Patient_Info:

Patient_Id Disease_Id Test_Id Test

P001 HYTSH T4 Thyroxine

P001 HYTSH TSH Thyroid Stimulating Hormone

P002 HRTBLCK ECG Echocardiography

P002 HYPTSN BP Blood Pressure

P003 PCOD USG Ultrasonography

Disease_Info:

Disease_Id Disease Doctor_Name

HYTSH Hypothyroidism Dr. Rohit Shaw

HRTBLCK Heart Blockage Dr. Rima Dhara

HYPTSN Hypertension Dr. Rima Dhara

PCOD Poly Cystic Ovarian Syndrome Dr. Shalini Thakur

We can do this in SQL using the code:

ALTER TABLE Disease_InfoADD PRIMARY KEY (Disease_Id);

Third Normal Form or 3NF

The database or relation is in third normal form if it is in 2NF and no transitive dependency is present.

Consider this part of the main table:

Patient_Id Disease_Id Disease Test_Id Price

P001 HYTSH Hypothyroidism T4 400

P001 HYTSH Hypothyroidism TSH 350

P002 HRTBLCK Heart Blockage ECG 1100

P002 HYPTSN Hypertension BP 50

P003 PCOD Poly Cystic Ovarian Syndrome USG 1200

Now, the table stores more information:

Primary key: Patient_id+Disease_id.

But the attribute Price depends on the attribute Test_Id. Both of them are non-prime attributes. Hence, a transitive dependency exists.

To remove it and convert the table to 3NF, we follow the approach:Patient_Info:

Patient_Id Disease_Id Disease

P001 HYTSH Hypothyroidism

P001 HYTSH Hypothyroidism

P002 HRTBLCK Heart Blockage

P002 HYPTSN Hypertension

P003 PCOD Poly Cystic Ovarian Syndrome

Test_Price:

Test_Id Test Price

T4 Thyroxine 400

TSH Thyroid Stimulating Hormone 350

ECG Echocardiography 1100

BP Blood Pressure 50

USG Ultrasonography 1200

We can achieve the following using the SQL code:

ALTER TABLE Test_PriceADD PRIMARY KEY(Test_Id);

Boyce-Codd Normal Form or 3.5NF or BCNF

For the database to be in Boyce-Codd Normal Form, it must be in 3NF. Also, in BCNF, A must be a super key for a functional dependency from A to B. Consider the part from our main table:

Patient_Id Category Doctor_Name

P001 Endocrinology Dr. Rohit Shaw

P001 Endocrinology Dr. Rohit Shaw

P002 Cardiology Dr. Rima Dhara

P002 Cardiology Dr. Rima Dhara

P003 Gynecology Dr. Shalini Thakur

Here, we see that:

Primary Key: Patient_Id+Category (since it can uniquely identify all the tuples in the table). That means the prime attributes here are: Patient_Id and Category.

So, to convert the relation to BCNF, we divide the table into two:Patient_Doctor:

Patient_Id Doctor_Id

P001 D110

P002 D200

P003 D201

Doctor_Info:

Doctor_Id Doctor_Name Category

D110 Dr. Rohit Shaw Endocrinology

D200 Dr. Rima Dhara Cardiology

D201 Dr. Shalini Thakur Gynecology

SQL Code:

ALTER TABLE Patient_Information ADD COLUMN Doctor_Id DEFAULT Dr; UPDATE Patient_Information SET Doctor_Id='D110', WHERE Doctor_Name='Dr. Rohit Shaw'; UPDATE Patient_Information SET Doctor_Id='D200', WHERE Doctor_Name='Dr. Rima Dhara'; UPDATE Patient_Information SET Doctor_Id='D201', WHERE Doctor_Name='Dr.Shalini Thakur'; CREATE TABLE Patient_Doctor AS SELECT DISTINCT Patient_Id, Doctor_Id, FROM Patient_Information; ALTER TABLE Patient_Doctor ADD PRIMARY KEY(Patient_Id, Doctor_Id); CREATE TABLE Doctor_Info AS SELECT DISTINCT Doctor_Id,Doctor_Name,Category, FROM Patient_Information; ALTER TABLE Doctor_Info ADD PRIMARY KEY(Doctor_Id);

Fourth Normal Form or 4NF

For a database to be in 4NF, it must be in BCNF, and no multivalued dependency should be present.

Consider another part of the main table:

Patient_Id Marital_Status Disease_Id

P001 U HYTSH

P001 U HYTSH

P002 M HRTBLCK

P002 M HYPTSN

P003 U PCOD

Here, there is no relation between Marital_Status and Disease_Id.Both are independent of each other. Hence, a multivalued dependency exists. So, we divide the table as follows:Patient_MaritalInfo:

Patient_Id Marital_Status

P001 U

P001 U

P002 M

P002 M

P003 U

Patient_Disease:

Patient_Id Disease_Id

P001 HYTSH

P001 HYTSH

P002 HRTBLCK

P002 HYPTSN

P003 PCOD

SQL Code:

CREATE TABLE Patient_MaritalInfo AS SELECT DISTINCT Patient_Id, Marital_Status, FROM Patient_Information;ALTER TABLE Patient_MaritalInfoADD PRIMARY KEY (Patient_Id),CREATE TABLE Patient_Disease AS SELECT Patient_Id, Disease_Id, FROM Patient_Information;ALTER TABLE Patient_DiseaseADD PRIMARY KEY(Patient_Id,Disease_Id);

Benefits we get from Normalizing this Data

The benefits we get from normalizing the dummy database are:

We now have tables containing patient attributes, doctor’s attributes, disease attributes, and test attributes separately.

Also, tables show the relationship between those attributes’ tables using foreign keys.

Hence, our database is more organized, and the relationships between each table are well-defined. This makes the data more efficient for analyzing, updating, and retrieving. For example: If the hospital has to update the age of a particular patient, then that record needs to be updated only in the table containing the patient’s attributes. Once that is done, this record will automatically be updated for all the related tables since they are related or modeled using foreign keys (these constraints can be declared in SQL).

Hence, now we can see how a messy database can be reduced to these normal forms. Normalization helps reduce space and makes updating, deleting, and working with the data easier. Well, there are other normal forms viz:

5NF: Here, the relation must be in 4NF, and no join dependency should be present.

6NF: Here, the relation must be in 5NF, and all join dependencies must be trivial i.e., the relation becomes irreducible.

But most databases can be organized with forms up to 4NF. Based on the business purpose, it is up to you to decide which normal form suits you best.

Conclusion

That’s it! You are all done with organizing your database. Now your database is efficient enough for further analysis. You can further transfer this database to a DBMS or from the SQL server; you can interact with your data using queries to meet the business requirements.

Here are some key takeaways from the blog:

Updation anomaly, insertion anomaly, deletion anomaly, and data redundancy make a database inefficient for analysis and querying. Hence, we use normalization to organize related data efficiently and eliminate anomalies.

Five major types of normal forms are commonly used in the industry: 1NF, 2NF, 3NF, BCNF, and 4NF. Normal forms like 5NF and 6NF are not commonly used in the industry. The normal forms must be used depending on the context.

Normalization helps organize interrelated data tables by decomposing them into sub-components or small tables based on constraints. This eliminates certain dependencies.

One of the best ways to achieve a normalized database is by using SQL (Structured Query Language). In this blog, you can see how one can normalize and decompose the dummy database using queries in SQL.

I hope this blog helps in your learning journey. Meanwhile, you can check more articles related to databases here.

Have a great learning ahead!

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion. 

Related

Update the detailed information about Understanding Transaction Management In Sql on the Moimoishop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!