compositekey-vs-superkey-featureimg

Composite key vs Super key

Hello Friends,

In this blog post, I am going to let you know about the difference between the composite key and super key.

Super key is the combination of one or more attribute where a minimal super key would be candidate key. In a simple language in super key collection attribute, one attribute must be a key. and then we can create a different- key combination of a super key. A minimal candidate key is a super key.

super-vs-composite
Super vs Composite

Whereas, the composite key is the combination of one or more attribute where no attribute is a key then for making a unique key we join one or more attribute. This is known as a composite key.

Conclusion:

So the main difference between super key and the composite key is that super key has minimum one key element or candidate key whereas composite key does not have any key attribute which can easily identify all the tuple value of the table.

In the case of any queries, you can write to us at a5theorys@gmail.com we will get back to you ASAP.

Hope! you would have enjoyed this post super key vs composite key.

Please feel free to give your important feedbacks in the comment section below.

Have a great time! Sayonara!

security-in-dbms

How to achieve Security in DBMS?

Hello Friends,

In this blog post, I am going to explain about security in DBMS. Security has been always a primary concern whether it is security in networks and security in DBMS.

DBMS has the collection of large data which can be of either category. There is some data in DBMS which is highly confidential like bank detail, credit card, and debit card number and etc. It is must ensure security in DBMS to save these type confidential data from the hackers or phisher.

Security in DBMS protects the data from a various malicious virus which always seeks for stealing and misusing of data. DBMS ensures security at various levels which are explained below.

Databases system level:

Here security of DBMS is achieved by using authorization and authentication which allow specific users to enter into the system to access required data.

Operating System Level:

Here security in DBMS is achieved by updating the superuser permission granted as per the role of a superuser on the specific data with appropriate permission. As the operating system allows a full grant or permission over the system. So good security is required at the level of superuser with a good operating system.

Network Level:

Here the security of DBMS is achieved by an encryption method:

Eavesdropping

(Unauthorized reading of message):

Masquerading:

Pretending to be an unauthorized user or sending message supposedly from an authorized user.

Physical level:

Here the security in DBMS is achieved by identifying the intruders who can damage and steal our computer data. We can use a traditional lock and key security to avoid this data attack.

We should also protect our computer system by natural hazards like flood and fire.

Human-level:

Here the security in DBMS is achieved by screening the users to ensure that an authorized user can not give access to the intruders in the system.

User should be well trained for the use of effective password selection and its secrecy.

Conclusion:

Security in DBMS is the primary concern while designing a database in any terms. There are several levels in the communication where the security in DBMS is required. It could be an operating system level and network level for example. It is must to secure confidential data in DBMS.

Please feel free to share your comment in the comment section if you have liked this post. If you have any query regarding this blog post then you can write to us at a5theorys@gmail.com.

Hope! you would have enjoyed this post of security in DBMS.

Have a great time!

datareplication-featureimg

What is Data Replication?

Hello Friends,

In this blog post, I am going to discuss data replication. I addition I will also discuss data replication advantage, disadvantage, and examples. First, you need to understand what is replication.

Replication is a process where we create a mirror image of any object. In a simple way, let’s assume that we have created one excel file containing all student address information of the school. Now if create one more excel file with the same or different name then paste the content of our first excel file then this second excel file will be a plica of our first excel file. This is also called as data replication.

data-replication

Actually, e often do this in our daily life too. For example in our computer system, we create multiple copies of important file and folder and keep them at different-2 places. So this data replication helps us in a case when we lose our data due to some reasons.

Advantage of Data Replication:

There is two major advantage of data replication which is explained below.

Availability:

Suppose one site is having a relation R or having a data table R and due to some reasons this site or server gets fail then the query of several users can still be entertained by using a replica of R present at another server or site despite the failure of the first site using data replication.

Parallelism increased:

There could be multiple queries to the table R in parallel, If there would be more replica or table R then we can achieve high parallelism to fulfil these queries. If we have Five replicas of R at different-2 server or site then we can have more no. of request or queries which can be fulfilled with the help of all these servers in parallel using the concept of data replication.

A disadvantage of Data Replication

This data replication can increase the overhead of update. For example, if we have five replicas of R at different server or site. Now we make a change in the main copy of the relation or table R then it must be updated to all the replica of R present at another server.

Data Replication Example:

There could be various example of data replication in our surrounding. So we just pick up one example of a banking system. If a particular account detail is replicated to several sites then the balance of this account must be the same for all the sites.

This account replication will increase the performance while serving the read operation and increase the availability of data to read the only transaction. But update transaction can increase a high overhead

Conclusion:

Data replication is a concept of keeping multiple copies of one data file at different access point or site and server. If there will be multiple resources to access the data or read the data then processing speed will be high and can be served in parallel too. And it will also be secured in a case of data file crashes or fail on a single server. Data file update will be one overhead or hectic work to do as we need to keep all the file updated within replica also in all the server.

Please feel free to share your feedback in the comment section if you liked this blog post. You can also write to us at a5theorys@gmail.com

Hope! you would have enjoyed this post of Data Replication.

Have a great time!

dba-functions

What are the DBA & Functions of DBA?

A DBA in DBMS is a person having central control over the database system. DBA full form is ‘Database Administrator’ A dba is assigned with a certain set of functions which he needs to control and monitor all the time.

There are several functions of dba which helps in creating maintaining whole database management cycle to have all the transaction in a proper way.

The functions of DBA are explained below:

SCHEMA DEFINITION:

This is the very first step where DBA creates the database module or schema by executing the set of data definition language or statement which is DDL.

SCHEMA & PHYSICAL ORGANIZATION MODIFICATION:

Once the starting schema is set then DBA can make some changes to the schema as per the requirement of physical organization structure and this change reflect in the database. Mean to say the DBA always try to alter the records to make better performance of the data module or related body or organization.

GRANTING DATA ACCESS PERMISSION OR AUTHORIZATION:

As the database of any organization body is generally huge and can have several modules in it. Then it is the responsibility of DBA to provide the authorization for all users based on their task on a particular database module. Say DBA provide only read permission to user A to just analyzing the data view. Whereas he has given permission to B to alter the data if it is wrong. So there could be several permissions on a specific or several modules decided and regulated by a database DBA.

ROUTINE MAINTENANCE:

There could be lots of situations where data can be damaged or distorted by several causes, it could be human mistakes or a natural disaster. so to fight with such situations DBA ensures to have a strong backup for database.

A periodic backup, it could be either on tapes disc, tape or on the remote server to prevent the loss of data in case of any casualty maintained by DBA.

DBA also ensures that there is enough space on the disc so that normal operation can easily be performed and it can be upgraded easily.

DBA ensures that all the task or database jobs run in a smooth way. and never an expensive or heavy job affect the running jobs with smooth. Mean to say that no job should affect the processing or execution of another job.

Conclusion:

DBA full form is a database administrator. The role of DBA in DBMS is just like a manager or boss who has control over all the activity happening in the system. DBA functions is a list of all the task or jobs that a DBA executes or ensures as a procedure while doing any transaction. functions of DBA are just to create a database schema and then modify it as per the requirement and then provide the necessary authorization to all the users as per their role. and at last, Take care of the routine maintenance to prevent the loss of data due to any disaster.

What is ACID properties in DBMS?

Hello Friends in this blog post I am going to let you know about the ACID properties in DBMS system. ACID properties are associated with consistency, atomicity, durability, and isolation. These acid properties of dbms are used to make our database consistent and authentic. Now I will explain all these four properties below one by one.

ATOMICITY:

This is the first acid properties as per the naming convention. this atomicity property states that if a database transaction occurs then either it will be completed or failed. there would not be any middle state like the transaction status is showing that it is partially completed. So there could be only states in database transaction as per this atomicity one is a success and another is a failure, aborted and etc.

For example, Sunny has 1000 Rs. in his account and Ravi has 5000 Rs. in his account and now Ravi make a transfer of 2000 Rs. from his account to sunny account and unfortunately, this transaction got failed but we talk about the transaction state:

  • Money had been deducted from a Ravi account
  • but before it could be processed to sunny account the system was failed.

So, in this case, the reflecting money in the sunny account is 1000 Rs. Whereas the money reflecting in the Ravi account is 3000 Rs. But the total amount as sunny + Ravi does not preserve the original value that is 6000.

I can make you understand this acid property with a real-life atomicity example. In this ear of ATM machine to withdraw money from, almost we all go to this machine to money. Sometimes it happens that your transaction gets failed but you receive a message on your phone that you have been deducted a certain amount that you tried to withdraw. But in actual your money has not been deducted in real you just get a triggered SMS then the money gets deducted but as the system gets fail before sending your money to out in the ATM tray box. So here atomicity property takes care of your transaction and revert it back and as an effect, you got your money back in your bank account.

So to avoid these cases the atomicity should be ensured for each and every transaction. Where these uncompleted transactions would be rolled back to its original value that they had preserved before the transaction. this acid properties in SQL ensures the authenticity of some very confidential data transaction like money transaction and safety too.

CONSISTENCY:

This is the second acid properties as per the naming convention. And this property ensures that the sum of A+B should be unchanged by the completion of the transaction. Here A and B stand for a transaction. As per this consistency property, there should not be any impact on data residing at the database before and after the execution of the transaction.

for example, let’s say the money is transferred from account A to account B then the sum of money as A+B should be unchanged.

DURABILITY:

This is the fourth acid properties as per its naming convention. As per this durability property, the system should be durable enough so that it could persist the updated value even after the system failure. Here the execution of data is successful but before updating it into a database the system gets fail. But as per this durability property, the information would be updated once the system is back into action or repaired.

You can understand this acid property by a simple durability example of mobile balance recharge. Some time we recharge our mobile phone and the recharge gets successful but in our mobile APP the mobile data does not update or our recharge plan does not update. But in reality, it has been recharged. But due to some problem like server down, a technical problem in code or machine, low internet connectivity you mobile recharge status does not reflect on your mobile APP. Here durability properties ensure that your data will be updated for sure once the system is up or the system update cycle has been scheduled.

ISOLATION:

This is the Third acid properties as per the naming convention. As per this property, if there are simultaneous and parallel execution of transaction then the execution of transaction only will be carried out if it will be the only transaction in the system. That means no transaction will impact on the existence of the other execution.

For example: if A is transferring the money to B and at the same time before completion of execution someone makes a transaction request for A+B then it could be an inconsistent data if the update of A and B would have happened. So to avoid this type of case we ensure the isolation property properly.

Concurrent control component system ensures the isolation property to make a transaction consistent.

So by using these acid properties, we can make consistent database transactions.

Conclusion:

The acid properties of DBMS are made up of four component that is atomicity, consistency, durability, and isolation. These all properties make our database records and database transactions consistent and authentic. These acid properties take care of all the system failure, aborted and tracks of transaction states before and after the execution of a transaction. Here you can also find a few real-life atomicity example and durability example.

Hope! you would have enjoyed this blog post of acid properties. Please feel free to comment and share if you like this post. For any further query, you can write to us at a5theorys@gmail.com.

Have a great time!

composite-key

What is a composite key? composite vs super?

A composite key is the combination of one and more attribute which can uniquely identify a tuple value.

For example: if a student relation has attribute student id, student name, student class, student project id. The data of this relation is given below.

Table: Student

student idstudent namestudent classstudent project id
1Ravi9th101
2Rita9th102
1Shyam10th103
3Anil11th101

Here in the above table we can don’t have any unique column or candidate key which can uniquely identify the tuple value so, here we need to join two column value or attribute value to make a key which can easily identify the tuple in the above-given relation. So, we make student id and student as a primary key of this table and this primary key is also known as a composite key of this table.

What is the difference between the composite key and super key?

If you will see the definition of a super key which says that the combination of one and more attribute uniquely identifies a tuple value is called a super key you will confuse it with the composite key definition which is just looking similar at one glance.

But it is not like this, there is a difference between composite key and super key. In super key, the combination attributes have at least one candidate key which could be a primary as well as a unique key. But when we don’t have a candidate key in our table then we have to make a set of combine attribute which can easily identify a tuple uniquely. And now this combined key is called a composite key.

A candidate key is also known as minimal super key but in case of the composite key, there is no candidate key in the table.

Conclusion:

When we are in a situation where we are unable to identify a tuple value uniquely with the help of one single column or we don’t have a candidate key or primary key in our table then we need to combine more than one attribute or column to make a primary key which can fetch thee tuple result, this type of primary key is known as composite key. We can say that super key is also a composite key but there is some minor difference between them.

Hope! you would have liked this blog post. Please feel free to share and comment on your reviews. If you have any query related to this blog post you can write to us at a5theorys@gmail.com.

Have a great time!

null-in-database

What is the null value in DBMS & problems with null? 2019

A null is basically used to represent the unknown and missing values. But it does not mean that the null value is equivalent to zero or a blank text string. See the example below for the same.

Table: Client

Client IDFirst NameMiddle initialLast NameCityCountry
1JohnBcornerAllensparkNull
2MaryNulllevisWiselandEast Midlands
3TimmyJFincoPoolshireMersybindo
4HalkNullJamurSillyvelly Null
5YenNullhuiNewcity North Brazil
6NayanKBansalMumbaiIndia

How null values are created or inserted into the database?

It is good to have null values in the table whenever they are just for showing purpose like Middle name or stating some information. As you can see in the above client table null is useful to show the middle initial field and in the country field. If we want to use this null in a proper way then we should know the reason for their occurrence.

The very first reason for null values could be a manmade error. As while entering the data in the table, we forget to ask a particular client say John that which country does he live in. thus this missing data will be shown including a null value. You can see the country column for john in the above table.

There could be lots of reasons to fill a field with the value null. The value has not been defined for that field could be one of the reason. Let’s understand this scenario with an example related to the above table. Assume that we asked for john about his country information he is living in but unfortunately he does not know his country name and you also don’t have any idea about his country then we have to set a null value in his country column.

There could be another case where we need to insert the null value for a field. For example, in a company, an employee is working in both day and night shift, there are the fields total hour worked for the day and night shift both. Assume that he is at night shift for one month then his total hour worked in day shift will be always null for one month. So, in this case, null value come as per the condition.

Problems with null:

In general null value does not impact on anything until it is used just for showing the status of any field. But once that field is used in mathematical calculation with some other logical field then it can impact on the outcome result.

(50 x 3) + 10 = 160
(Null x 4) + 25 = Null
(20 x Null) + 500 = Null

With the help of below product table, you can see the impact of null value to a mathematical calculation.

Table: Product

Product CodeProduct NameProduct Category PricestockStockValue
1Fight with TruthBooksNull10Null
2 Fight with Truth Books30.955154.75
328 LEDNull120NullNull
4HP Printer Ink TankNull7010700.00
5Videocon LCD with home theaterAudio/Visual90NullNull
6Hard Disc 1TbDevice5515825.00

You can easily understand the problems due to null value by inspecting the value of above product table. Here stock value is derived by multiplication of price and stock field like[Price*stock]. So, for product 1 where the price is null, the stock value will always show null.

This problem can be more critical when you need to calculate the total value of all the item which are kept current in stock then you can not get an accurate result for the same.

to address this problem you need to make sure that price and stock field can’t be assigned as null.

See another example where null value is affecting an aggregate function.

Null values will also affect aggregate function that incorporates the value of a given field. If we were using an aggregate function, for example, a count function, the result will always contain a null if the field contains a null. The table below shows the results of performing a summary calculation that will count the number of occurrences of a category in the products table.

Let’s take an aggregate function which is counting the Product category column.

Category calculas

CategoryDescriptionTotal no. of Occurrences
0
Audio/Visual1
Books2
Device1

After applying the aggregate function we can see the effect of null value clearly.

Count ProductCategory

Here in the result of category calculus, we can observe that the occurrence of the unspecified category is 0, whereas if we see the product table then we can easily find that there are two product which does not show any category.


Conclusion:

So, in this blog, you have learned how the null value inserted in the database table. It can be entered by human mistake and some time data is unknown at present and we leave that field and by default, it is filled with null. And some time information does not match a particular column and thus filled with null. And you have also read the effect of these null value in mathematical operation and aggregate function.

Hope! you would have enjoyed this blog post. Please share your reviews and comments with us. You can also write to us at a5theorys@gmail.com if you have any query regarding this blog.

Have a good time!

superkey-feature-img

What is super key in DBMS?

Super key is the combination one and more attribute to uniquely identify a tuple. For example, we have a student table with attribute (stu_rollno., stu_name, stu_add, stu_branch, stu_Aadharno.)

Here we can have several sets of a superkey as given below.

stu_rollno. + stu_name
stu_rollno. + stu_name + stu_branch
stu_Aadharno. + stu_name
stu_rollno. + Stu_Aadhar
stu_Aadharno. + stu_name + stu_branch
stu_rollno. + stu_name + stu_add

These were a few combinations of superkey that can identify the tuple value obsoletely.

However, stu_rollno. and stu_Aadharno is also a candidate key which is also capable to fetch the tuple value uniquely. So stu_rollno. and stu_Aadhar will be a minimal superkey which can easily identify the whole tuple value.

See, below another example for the same.

superkey-dbms
SuperKey Example

So, here we can say that super is key is the strong combination of attributes that which provide a guarantee to provide the unique tuple value.

Super key is different from the composite key. If in a case we don’t have any candidate key in our relationship then the combination of a possible attribute which can uniquely identify a tuple value is known as a comosite key.

Conclusion:

Super key is the strong key which assures a guarantee to identify the tuple uniquely. Let’s have a relation with one candidate key with some other attributes which are not a key. Then a super key can be all possible combination of candidate key with the other attributes of the relation.

Hope! you would have enjoyed this blog post. Please feel free to write to us at a5theorys@gmail.com if you have any query regarding this blog post.

Have a great time!

candidatekey-feature-img

What is a candidate key in DBMS?

An attribute or a column in a table that can identify a full tuple or row records in a data table is considered as a candidate key.

Now the question is that all the other keys like primary, alternate, super, unique, foreign also used to uniquely identify a tuple value so are they all a candidate key?

Yes, any key that is capable to identify a tuple value is considered as a candidate key though they could also have their own features like a primary key does not allow to enter a null value. Whereas unique key allows doing the same.

candidatekey-in-dbms-img
DBMS-KEYS-CANDIDATE KEY

In a very simple language, we can say that a candidate key is a generalized concept to have unique value to identify the whole row record. thus any DBMS key fulfilling this criterion will be a candidate key.

Conclusion:

Every key that can easily identify a tuple value with the property of uniqueness, mean to say that every tuple has a unique value for a particular attribute value is called a candidate key. There may be one and more candidate key in the database table.

Hope! you would have enjoyed this blog post. Please feel free to write to us if you have any query regarding this blog.

Have a great time!

key-dbms

What is Key in DBMS?

Hello Friends,

In this blog post, I will let you know about one of the most important and interesting topics of a database that is KEY.

While reading the same subject or sometimes having relevancy with the technology you would have heard about this technical word key if not exactly the same then you would have heard like a primary key or foreign key.

So those who have been studying the database or have studied should know this technical word key and all further classification related to this word key.

Like its name key which is useful in opening a lock in our real life, it is used to find the particular tuple and attribute information in relation in DBMS.

In the database we have a table, in a table, we have several rows and column. Here key can be a particular column or attribute value which is used to identify whole row or tuple value.

A column which is made a key will have a unique value in all the row. On this basis, we can say that an attribute which uniquely identifies a tuple value is known as a key.

There are several types of keys in the database which are given below.

dbms-keys
DBMS-Keys

Candidate key: Unique tuple value for each attribute.
Primary key: uniquely identify a tuple, does allow null.
Foreign key: Help to access other table data.
Unique key: Uniquely identify tuple, allow null value.
Super key: composite key identify a tuple value. a minimal superkey is candidate key.
Alternate key: Uniquely identify the tuple value but not a primary key.

I will discuss each of these above-given keys one by one in detail in my upcoming blog.

Conclusion:

You should be confused with a key in the database. it is simply a column which has a unique value in each row and on the basis of this value we fetch the detail of a particular row. We may fetch one or more row result with the help of this key and can show the result as per our requirement.

Hope you would have enjoyed this blog post. Please feel free to write to us at a5theorys@gmail.com if you have any queries about this blog.

Have a great time!