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!