KEYS

Keys: Key is an attribute or combination of attributes which can be used for uniquely identification of each
row of data in a table.

Sno Sname AadharNo SEmail

101 Ramu 123456789 abc@gmail.com

102 Rani 987654321 xyz@gmail.com

103 Ramu 4546321789 mno@gmail.com

 SNo, AadharNo, SEmail are Keys.

Why use keys in DBMS
 To identify each row of a table uniquely.
 To maintain data integrity.
 To maintain relationship between tables.

Keys Types
There are different types of keys available in RDBMS. They are
1. Super Key
2. Candidate Key
3. Primary Key
4. Alternate Key
5. Foreign Key
6. Composite Key
7. Compound Key
8. Surrogate Key
1. Super Key or Key:
a super key is nothing but a key. It is an attribute or combination of attributes for uniquely identification
of each row in a table.
Example:

Sno Sname AadharNo SEmail

101 Ramu 123456789 abc@gmail.com

102 Rani 987654321 xyz@gmail.com

103 Ramu 4546321789 mno@gmail.com

From the above table attributes are “SNo”, “Sname”, “Aadharno”, “SEmail”
Super Keys for the above table
SNO
AadharNo
SEmail
SNO+Aadhar
SNO+SEmail
AadharNo+SEmail
Sno+AdharNo+SEmail
2. Candidate Key:
 It is nothing but a minimal subset of a super key.
 Minimal means “As minimum as possible”
 Candidate key is an attribute or combination of attributes for uniquely identification of each row in a
table.
Example:

Sno Sname AadharNo SEmail

101 Ramu 123456789 abc@gmail.com

102 Rani 987654321 xyz@gmail.com

103 Ramu 4546321789 mno@gmail.com

Super Keys for the above table

SNO
AadharNo
SEmail
SNO+Aadhar
SNO+SEmail
AadharNo+SEmail
Sno+AdharNo+SEmail
Candidate Key from the above table:
Minimum attribute of a super key. So from the above table SNO, AadharNo, Semail are candidate Key.
But not combination of attributes are not candidate key because its not minimum number of attributes of a
super key.
3. Primary Key:
A primary Key is a candidate Key which can be used for uniquely identification of each row of a table
Example:

Sno Sname AadharNo SEmail

101 Ramu 123456789 abc@gmail.com

102 Rani 987654321 xyz@gmail.com

103 Ramu 4546321789 mno@gmail.com

From the above table candidate keys are

Sno
AadharNo
SEmail
The above candidate keys any of one attribute as a primary key.
4. Alternate Key:
The candidates Key which are not chosen as Primary Key are called Alternate Key.
Example:
Sno Sname AadharNo SEmail
101 Ramu 123456789 abc@gmail.com
102 Rani 987654321 xyz@gmail.com
103 Ramu 4546321789 mno@gmail.com

From the above table candidate keys are

Sno
AadharNo
SEmail
The above candidate keys any of one attribute as a primary key. We can select SNO as a Primary Key, than
remaining Candidate keys (AadharNo, SEmail) are alternate keys.
5. Foreign Key:
 It is used to maintain relationship between two tables.
 Foreign Key must be a Primary Key in the same table or in the other table.
6. Composite Key:
The combination of attributes for uniquely identification of each row in a table
7. Compound Key:
If a composite key contains any one of the attribute as a foreign key attribute, that attribute is called as
Compound Key.
 Normalization
The process of dividing a Relation (Table) into smaller well structure Relations (Tables) without
having any Redundancy.
 It removes/ reduces redundancy and eliminates insertion. Updating and deletion anomalies.
 It divides larger tables into smaller tables and links them using relationships.
 It was proposed by E.F. Codd.
Why the need of Normalization
It the table is not properly normalized & data having redundancy then it will take extra memory space and
also difficult to handle and update the database, without facing data loss.
In RDBMS having 5 types of normal forms. They are
1. 1st Normal Form (1st NF)
2. 2nd Normal Form (2nd NF)
3. 3rd Normal Form (3rd NF)
4. BCNF
5. 4th Normal Form (4th NF)

1st NORMAL FORM (1st NF)

For a table to be in 1NF, it should follow the following 2 rules.
1. It should have single valued attributes.
2. Each record should be unique.

EmpNO EmpName Subject

1 Sri C, C++
2 Ram Java
3 Abhi C,Java
 From the above table EmpNo, EmpName and Subject are attributes of a table. In that table “Subject”
attribute having multiple data values. But in based on1NF rules, every attributes having single value.
But in that table having multiple values.
 We are applying 1NF form rules in that above table, we can design the above table as below.
EmpNO EmpName Subject
1 Sri C
1 Sri C++
2 Ram Java
3 Abhi C
3 Abhi Java
Note: Now days from the above method is not good, by using another method to applying 1NF rules.
 EmpNO EmpName Subject1 Subject2
1 Sri C C++
2 Ram Java Null
3 Abhi C Java

2nd Normal Forms (2nd NF)

A Relation (Table) is said to be in 2NF, if it follow the following Rules.
1. That Relation (Table) should be in 1NF.
2. All Non-key attributes are fully functionally dependent on the entire primary key (P.K) but not on
part of P.K. i.e, if there are any partial dependencies they have removed.
Sid Sname Saddress SubjectID Subject Name Marks
01 Ramu KKT 101 C 75
02 Ramu KKT 102 C++ 80
03 Rani WNP 101 C 65
04 Rani WNP 102 C++ 73
Form the above table Sid, Sname, Saddress, SubjectID, SubjectName and Marks are Attributes of table. In
that table already shoul follow 1NF rule. But not follow 2nd rules in 2NF. Based on 2NF rule, it table having
only non-key attributes are fully functionally dependency on the primary key. It means the table having any
partial functional dependency than it remove. But in that above table having partial dependency, so we can
diving the table as follow 2NF rules.
Table1:
Sid Sname Saddress SubjectID Marks
01 Ramu KKT 101 75
02 Rani KKT 102 80
03 Ramu WNP 101 65
04 Rani WNP 102 73

Table2:
SubjectID SubjectName
101 C
102 C++
 3rd Normal Forms (3rd NF)
A Relation (Table) is said to be in 3NF, if and only of it follows below rules.
 Relation should be in 2NF.
 If should not contain any transitive Dependency. It means to remove any transitive Dependency in a
table.
Example:
EmpId EmpName EmpCity EmpZip
101 Ram WNP 509103
102 Rani KKT 509381
103 Ram HYD 500008
104 Raju MBNR 509001
From above table to follow 1NF and also 2NF so to identify the transitive Dependency than
Super Key: {EmpId}, {EmpId,Empzip},{Empid,EmpName}, {EmpId,EmpZip…etc
Candidate key: {EmpId}
Primary Key: {EmpId}
Functional dependency: EmpIdEmpZip
EmpZipEmpcity
Than EmpIdEmpCity its transitive dependency.
To remove transitive dependency in a table than re design the table as follow
Table1:
EmpId EmpName EmpZip
101 Ram 509103
102 Rani 509381
103 Ram 500008
104 Raju 509001

Table2:
EmpCity EmpZip
WNP 509103
KKT 509381
HYD 500008
MBNR 509001
 BCNF(Boyce Codd Normal form)
It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table
complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key
of the table.
Example: Suppose there is a company wherein employees work in more than one department. They store
the data like this
EmpID EmpName DeptName DeptNo
E01 Ramu CS C01
E02 Rani Maths M01
E03 Ravi Accounts A01
E04 Rani Botony B01
E05 Sony CS C01
E06 Sandhya Accounts A01
E07 Sagar Telugu T01
Functional dependencies in the table above:
EmpIDEmpName
DeptNoDeptName
Candidate key: {EmpId and DeptNo}
The table is not in BCNF as either EmpID or DeptNo alone are keys.
To make the table fulfill with BCNF we can break the table in three tables like this:

Table3:

EmpID DeptNo
E01 C01
E02 M01
E03 A01
E04 B01
E05 C01
E06 A01
E07 T01