Question No # 1 )What are keys?

A key is attribute or sets of attributes which identifies uniquely a tuple in a relation. In tables
keys are defined to identify tables to access or align the data fast and smoothly. Keys are also
used to create relationship between different tables.

For example, in Student table, Student ID is a key and it is unique for each student. While, all
the attributes in table of Person are keys and they are unique for each person.

Why we need Keys?

There are some reason for which we use keys in RDMS System:

 In tables to identify a row of data we use keys. In real world application a tables consists
of thousands of records and it could be duplicated. Keys helps us to identify a table
record along these challenges.
 Keys allows us to create and identify relationship between tables.
 Keys also need to enforce identify and integrity in the relationship.

2) Types of Keys:

RDMS has following types of Keys and each have their different functionality:

 Super Key
 Candidate Key
 Primary Key
 Alternate Key
 Foreign Key
 Compound Key
  Composite Key
 Surrogate Key

SUPER KEY:

Super key is attribute or set of attributes in relation which uniquely identify tuple in relation. A
super key is mostly a general type of key.
For example:

A relation STUDENT consists of different attributes like Registration NO, Name, F_Name,
Class and Address. In this the only attribute which identifies a tuple in the relation is Registration
No. In this Name attribute do not identifies tuple because two or more students may have the
same name. Father name, class, and address cannot be used to identify a tuple. Now Registration
No is the Super key for relation.

Any combination of attribute with super key is also a Super Key. Now, any attribute combine
with super key will also become a super key. The combination of two attributes like ( Reg no and
Name ) is also a super key.

Candidate Key
A Candidate key is a super key that do not contain extra attributes. It consists of minimum
attributes.

For example:

A super key like (RegistrationNO, Name) contains an extra field Name. it can be use to
identify tuple uniquely in relation. But it does not consist of minimum possible attribute as only
Registration No can be used to identify a tuple in relation.
Now, (RegistrationNO, Name) is a super key but it is not a candidate key because it cantains an
extra field. While, (RegistrationNo) is a super as well as a candidate key.

Primary Key
Primary key is a Candidate Key that is selected by database designer to uniquely identify tuples.
A relation may contain candidate key. When we select one of them to identify tuple in relation, it
becomes a Primary key. Now it means when there is any candidate key it WILL automatically a
Primary key.

There are some important points about a primary key:

 A relation may have one primary key.

 Each value in primary key must be unique.
  It cannot contain null values.

For Example:

Reg No Name Address

1 Hamza Fsd

2 Ali Lhr
3 Haddi Multan

In this table, there are different attributes such as Reg No, Class and Name. The attribute Reg No
is uniquely identifies each student and it can be used as Primary Key.

Alternate Key:
The Candidate keys that are not selected as primary key are known as Alternate Key. Alternate
key is also called “Secondary Key”.
For example:
suppose student relation contains attributes as Reg No, Class, Name and Roll No. The attribute
Reg No and Roll No can be used to identify students. If Reg No is selected as Primary key then
Roll No is selected as Alternate Key.

Reg No Roll No Name Class

12 1 Hamza Msc

13 2 Ali Bsc

Composite Key:

A primary key that consists of two or more attribute is known as Composite Key.
For example:
In table relation uses two fields Roll No and Subject to identify each tuple. In this table Roll No
and Subject are composite key.

Roll No Subject Marks

1 Math 52

1 English 55
1 Urdu 76

2 Math 89

2 Math 76

2 Compute 50
r
3 Compute 60
r
3 English 80

Foreign Keys:

Foreign key is attribute or set of attributes in relation whose values match a primary key in other
relation. The relation in which foreign key is created is known as child or dependent table. The
realtion in which foreign key refers is known as parent table.
For example:
in following table it show two relations. The Roll No attribute in parent relation and used as
primary key. The Roll No in child table is used as foreign key.

Table 1

Roll No Name Address

1 Hamza Fsd

2 Ali Lhr
 3 Haddi Multan

Roll No Subject Marks

1 Math 52

1 English 55
1 Urdu 76

2 Math 89

2 Math 76

2 Compute 50
r
3 Compute 60
r
3 English 80

Table 2

Surrogate Key:
surrogate key is used to uniquely identify the record in the table. It is an artificial key. For
example, in Sybase and SQL server based systems contain an artificial key also known as
“Identity”. Surrogate keys are just simple sequential number. Surrogate keys are only used to act
as a primary key.

Example:
Branch Id is a Surrogate Key in Branch Info table and Student Id is a Surrogate key of Student
Information table.

Question No#2: 1) What is normalization?

The production of simple or more reliable database structure is called normalization. It use to
create set of relations to store data. This process works through different stages known as normal
forms. There are 1NF, 2NF, 3NF and so on. Each normal form have certain requirements.
Conditions have to fulfill to bring database in normal form.

Task of database starts with unnormalized set of relations. The process of normaloization
identifies and corrects the problem and complexity in database.
2)there are 3 types of normalization which used mostly:

 1NF
 2NF
 3NF

These normalization are rarely used:

 BCNF
 4NF
 5NF

First Normal Form (1NF):

A relation is in said to be in first normal form if does not contain repeating group. Repeating
group is set of one or more data items that occur a variable number of times in a tuple. Each
value should be atomic andevery tuple should be unique. Each value in relation should contain
only one value.

Example:
table 1 is unnormalized because it contain repeating groups in STUD_State. It contains more
than one value. In order to convert this relation in 1NF, these repeating groups should be
removed.

Table 2 is in first normal form, because repeating groups are removed. In 1NF there is
Primary key. There should be more than one columns of keys. One or more keys are
composite key.

Second Normal Form (2NF):

the relation is in 2NF if it is in 1NF and if all of its non key attributes are fully functionally
dependent on whole key. It means that non key attributes are related to part of key.
Example-1:
Accountant Name, Age and group is determined by Accountant Number is not dependent on
Skill. The following relation can be created in which all attributes are fully dependent on primary
key Account Number.
Account Account Age Group Group Group
No Name No City Supervisor
21 Umer 53 35 ISD Babar
35 Ali 34 54 LHR Ghafor
55 Ahmad 51 24 LHR Ghafor
77 Hamza 44 34 ISD Babar

Similarly, another relation can be created in which all the fields are fully dependent on the primary key
as follows:
Skill no Skill category
113 Systems
179 Tax
156 Audit
204 Consulting

In above table, skill no is Primary Key.

Accountant Skill no Proficiency
no
21 123 3
34 113 5
54 224 3
77 234 6

The proficiency in INF was fully dependent on whole primary key. It requires to know the accountant no
and skill no. now, in this table there are three relations in 2NF. The attributes of all relations are fully
dependent on the Primary Key.

3rd Normal Form

A relation is in third normal form if it is in 2NF and no non-key attributes is dependent on

another non-key attribute. It means all non-key attributes are functionally dependent on primary
key. In this relation there should be no transitive dependency.

 Remove all attributes from the 2NF and record dependent on another non-key field.

 Place them in new relation with other attribute as Primary key.

For Example:

A new relation can be created as:

Account Account Age Group

No Name No
21 Umer 53 35
35 Ali 34 54
55 Ahmad 51 24
77 Hamza 44 34

now the above accountant table is in 3NF.

Now the 2nd table from the accountant table in INF as:

Group Group Group

No City Supervisor
52 ISD Babar
44 LHR Ghafor

Group no is selected as Primary key and above table is in 3NF. Both accountant
table and group no contains attribute Group no.

The skill table in 2NF contains no attribute, which is depending on non-key

attribute. It is aleady is in 3NF without any further changes.

Skill no Skill category

113 Systems
179 Tax
156 Audit
204 Consulting

The proficiency table in 2NF also contain no attribute which is depending on non-
key attribute. It is already in 3NF without any further changes.

Accountant Skill no Proficiency

no
 21 123 3
34 113 5
54 224 3
77 234 6

Boyce-Codd Normal Form (BCNF):

A relation is in Boyce-codd normal form if and only if every determinant is a candidate key.
BCNF is a stronger form of third form of normalization. A relation in BCNF is also 3rd NF but a
relation in 3NF may not be in BCNF.

4th Normal Form (4NF):

A relation is said to be in 4NF if it has no multi valued dependencies. A multi valued
dependency exists when a relation ha at least three attributes, in which two of them are multi
valued and their values depend on only third attribute.

5th Normal Form (5NF):

A relation is said to be in 5NF if it has no join dependency. 5th NF is also called project-join
normal form (PJNF).