Use SQL Transaction in ASP.Net | Stop whole SQL operations when get error | SQL Server

Super key, Candidate Key (minimal key), Primary Key

Super Key (SK): 
A super key is a set of columns that uniquely defines a row.
Let’s consider below table,

From above table we can see,


Consider one column : 

At column “BookID” all fields are unique, So BookID is a SK. [BookID]
At column “Name” all fields are not unique (XYZ, ABC are duplicate). So this is not SK.
At column “Author” all fields are not unique (A1, A2, A3 are duplicate). So this is not SK.

Consider tow Columns :

“BookID” and “Name” the each row’s value is unique. So [BookID, Name] is SK.
“BookID” and “Author” the each row’s value is unique. So [BookID, Author] is SK.
“Name” and “Author” the each row’s value is unique. So [Name, Author] is SK.

N.B : Lets add a new row under Book_Table which value is B7, ABC, A1

If so that [Name, Author] is not SK. Because we get duplicate rows which value is ABC, A1 (At row 2 and 7).

Consider three Columns :

“BookID”, “Name” and “Author” the each row’s value is unique. So [BookID, Name, Author] is SK.

N.B : We know that BookID is unique, so in [BookID, Name, Author] this relation we can easily say that whatever the value of Name and author is [BookID, Name, Author] is always unique because of BookID. So in this relation Name and Author are redundant attributes.


Candidate Key : 

Candidate Keys are super keys for which no proper subset is a super key. In other words candidate keys are minimal super keys.
1. A superkey without redundancy.
2. Not reducible.
3. More than one possible candidate key we may found in one solution. 

 
Example :

R (A, B, C, D, E, F)

A => C    [A determines C], C => D    [C determines D], D => B    [D determines B], E => F    [E determines F]

Here candidate key is AE. Because by using AE we can take all the tuples or columns or items of R.

AE => ACDBEF
So AE is candidate key.

N.B: Candidate keys are always one type of super key.

Primary Key :
When we design a database system than we should take candidate key which is known as primary key. 
But if we get 2 candidate key (AB and AC) and if database designer chooses a candidate (lets consider he choose AC) for design purpose than AC will consider as primary key. 

N.B : Primary is one of a candidate key that is used for next implementation by database designer.

Database normalization’s restrictions



1NF restriction : 

          1. Value should be atomic (Unique).

2NF restriction : 

          1. All the non-prime attributes should be fully functionally depended on each candidate key.

3NF restriction :
          1. There should not be any transitive relation or dependency among non-prime attributes.

BCNF or 3.5NF restriction :

          1. Part of primary key should not be fully functionally dependent on non-primary key.

Boyce–Codd normal form (BCNF or 3.5NF)

BCNF : 
In BCNF part of primary key should not be fully functionally dependent on non-primary key.

Conditions :

          1. Relation must be in 3NF.
          2. If X determines Y (X->Y) than X must be a super key.


Example :
        A -> BCD………….. (i)
      BC -> AD……………..(ii)
        D -> B………………..(iii)
 
Here key is A and BC. And this example is now in 3NF form.
 
From example we can see that A is a super key because it determines all the elements.
BC is a key.
 
But   D -> B is violating our condition, because D is not any key. So it should be in different table. 
R2(D,B)
 
N.B :  Here R2 indicates a table.
 
Another table should be : R1 (ABC) (ADC) [ Get ADC because  from (iii) ]
 
Now from R2(D,B), D -> B means D is a key and B is not a key. So D should be in R1 table.
 
For that R1 (ABC) is incorrect, because D is absent.
Finally we can see that R1 (ADC) is correct.
 
Final Result :
     From R1 (ADC) and R2 (D, B)
     We get :   
        A -> DC
        C -> AD (From BC -> AD, B is now not a key.)
 
This is now in BCNF from.
 
Here A and C is now candidate key.

Difference among 1NF, 2NF and 3NF


Concept 01 :

             1NF: Only one value per column.
             2NF: All the non primary key columns in the table should depend on the entire primary key.
             3NF: All the non primary key columns in the table should depend DIRECTLY on the entire primary key.


Concept 02 :
1NF is the most basic of normal forms – each cell in a table must contain only one piece of information, and there can be no duplicate rows.

2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said in his response:
The data depends on
                 1. The key [1NF],
                 2. The whole key [2NF] and
                 3. Nothing but the key [3NF] and
                 4. So help me Codd

2NF

Say you have a table containing courses that are taken in a certain semester, and you have the following data:
|-----Primary Key----|               uh oh |
                                           V
CourseID | SemesterID | #Places  | Course Name  |
------------------------------------------------|
IT101    |   2009-1   | 100      | Programming  |
IT101    |   2009-2   | 100      | Programming  |
IT102    |   2009-1   | 200      | Databases    |
IT102    |   2010-1   | 150      | Databases    |
IT103    |   2009-2   | 120      | Web Design   |
This is not in 2NF, because the fourth column does not rely upon the entire key – but only a part of it. The course name is dependent on the Course’s ID, but has nothing to do with which semester it’s taken in. Thus, as you can see, we have duplicate information – several rows telling us that IT101 is programming, and IT102 is Databases. So we fix that by moving the course name into another table, where CourseID is the ENTIRE key.
Primary Key |

CourseID | Course Name |
—————————|
IT101 | Programming |
IT102 | Databases |
IT103 | Web Design |

No redundancy!

3NF

Okay, so let’s say we also add the name of the teacher of the course, and some details about them, into the RDBMS:
|-----Primary Key----|                           uh oh |
                                                       V
Course  |  Semester  |  #Places   |  TeacherID  | TeacherName  |
---------------------------------------------------------------|
IT101   |   2009-1   |  100       |  332        |  Mr Jones    |
IT101   |   2009-2   |  100       |  332        |  Mr Jones    |
IT102   |   2009-1   |  200       |  495        |  Mr Bentley  |
IT102   |   2010-1   |  150       |  332        |  Mr Jones    |
IT103   |   2009-2   |  120       |  242        |  Mrs Smith   |
Now hopefully it should be obvious that TeacherName is dependent on TeacherID – so this is not in 3NF. To fix this, we do much the same as we did in 2NF – take the TeacherName field out of this table, and put it in its own, which has TeacherID as the key.
 Primary Key |

TeacherID | TeacherName |
—————————|
332 | Mr Jones |
495 | Mr Bentley |
242 | Mrs Smith |

No redundancy!!
One important thing to remember is that if something is not in 1NF, it is not in 2NF or 3NF either. So each additional Normal Form requires everything that the lower normal forms had, plus some extra conditions, which must all be fulfilled.
 
For more detail click here.