Lecture 3 - Database Keys

Keys are very important concept in database. They allow us to extract, sort, manipulate and create relationships in the database.

Candidate Key

An attribute or a set of attribute that is/are capable of uniquely identifying a record in a relation is called a candidate key.

AdmNo	Name	Father’s Name	Class	Address	Phone
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567

Table 6 - Candidate Key Explained

Potential candidate keys in above relations are AdmnNo and Name because they are not repeated in the above table.

Here comes a question Why use Name as a key, because Name can be repeated. That’s right but at the moment we are discussing candidates in this particular relation.

Primary Key

An attribute or a set of attribute that is/are selected to uniquely identify a record in a relation is called a primary key.

AdmNo	Name	Father’s Name	Class	Address	Phone
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567

Table 7 - Primary Key

Now! I shall select AdmnNo as a primary key. I shall use AdmNo as primary key for data manipulation.

Pre-requisites for a Primary Key:

1.       A relation can only have ONE primary key

2.       Each value of the attribute must be unique

3.       Primary key cannot contain NULL values i.e. empty fields

 

Composite Primary Key

A primary key which spans over multiple attributes is called a composite primary key.

For example in following table I had choose Name and Father’s Name as a primary key. The combination of Name and Father’s name never gets repeated therefore it is a good candidate for a primary key.  Composite primary key is not limited to just two attributes, it can span over more than two attributes.

 

AdmNo	Name	Father’s Name	Class	Address	Phone
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567

Table 8 - Composite Primary Key

Alternate Key

An alternate key is an attribute or set of attributes that can be used as an alternate of primary key.

For example in the following relation, primary key is AdmNo. Instead of using primary key we can manipulate database using alternate key Name and Father’s Name.

AdmNo	Name	Father’s Name	Class	Address	Phone
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567

 

Sort/Control Key

An attribute on the basis of you sort your data in relation is called a sort key.

AdmNo	Name	Father’s Name	Class	Address	Phone
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313

Table 9 - Sort Key (AdmNo )

 

 

Now sorting on the basis of Name

AdmNo	Name	Father’s Name	Class	Address	Phone
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311

Table 10 - Sort Key (Name)

Now sorting on the basis of Class

AdmNo	Name	Father’s Name	Class	Address	Phone
50165	Hasnain Mehdi	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
50179	M. Faheem Ahsan	M. Ahsan	XI	HQ 15 Div	052-1341313
50017	M. Rizwan	M. Ramzan	XI	Dalowali	052-1234568
50164	Naqi Abbas	Shafaqat Hussain	XI	Sublime Chowk	052-1234567
10996	Rana Ubaid Ashraf	M. Ashraf	XI	Askari 2	052-3434311
13001	Ibrar Zeb	Jehanzeb Khan	XII	Sadiq Lines	052-9784661
12096	M. Waqas Latif	M. Latif	XII	Narrowal	052-7841561
13056	M. Zulqarnain	M. Anar	XII	New Sadiq Lines	052-1234567

Table 11 - Sort Key (Class)

Foreign Key

An attribute which is used to create a relationship with the parent table is called a foreign key.

Figure 1 - Foreign Key

 

There are two relations in above figure Student and Grades. The primary key in relation Student is AdmNo and the composite primary key in Grades is AdmNo and Subject

You can see that the AdmNo is being repeated in Grades relation. But it is unique in Student relation. In this case the Student relation (table) is called parent or independent relation, whereas grades relation is called child or dependent relation.

1.       Foreign key can be repeated

2.       A relation can contain multiple foreign keys from different parent relations

3.       A child relation can be related to its child relation

4.       Foreign key cannot be NULL

A little questionnaire now

1.       What are the marks acquired by Mr. M. Zulqarnain in Computer?

___________________________________________________

2.       How many students of Class XI study Computer?

___________________________________________________

3.       Which student is studying Economics?

___________________________________________________

4.       What is the father name of the student having 79 marks in Physics?

___________________________________________________

5.       Who has highest marks in computer?

___________________________________________________

6.       How can we identify a particular tuple in above table?

___________________________________________________

7.       What is/are the criterion for uniquely identifying a record?

___________________________________________________

8.       What two attributes can be used to tell which students are siblings?

___________________________________________________

9.       What is the telephone number of student having 63 marks in physics?

___________________________________________________

10.   What are candidate keys in grades relation?

___________________________________________________