0:00	foreign
0:07	welcome to one of the important topics
0:09	in dbms the Keys in idbms before going
0:14	into the topic of the day the key is in
0:16	rdbms let's first understand why do we
0:19	need keys in rdbms let's assume there is
0:23	a relation or a table and this is the
0:25	employee table and we have the
0:28	attributes in the employee table the
0:29	employee ID the employee name the SSN
0:32	the social security number we can also
0:35	consider this as an other number in
0:37	India the salary the phone number and
0:40	email and we have nine tuples here and
0:43	these nine tuples are just for example
0:45	purpose now why do we need keys in rdpms
0:48	let's assume I am going to increment the
0:51	salary of Jon by 10 percent in that case
0:54	I am informing the database with a query
0:56	that increase the salary of John by 10
0:59	percent and obviously database will be
1:02	incrementing the salary of John by 10
1:04	percent but the problem is there are
1:07	three Johns that are updated in the
1:08	database 101 John 105 John and one not
1:12	eight John
1:13	do I want all John's salary to be
1:16	incremented no actually I wanted 105
1:19	John's salary to be incremented but what
1:22	did I tell the database to do I
1:24	instructed the database to increment the
1:26	salary of Jon by 10 percent but all
1:29	three John's salary will be incremented
1:31	by 10 percent if I provide a query by
1:33	referring the name of the employee which
1:35	is John instead if I give update the
1:38	salary of Jon who is belonging to a
1:40	specific department and that may be okay
1:42	to some extent what if there are two
1:44	Johns in the same department and we
1:47	wanted to increment the salary of only
1:48	one John again that will bring some
1:50	inconsistency to the database right so
1:53	the real problem here is identifying the
1:56	unique tuples if I want to do some
1:58	operation with the database I want to
2:00
uniquely identify the tuples in this
2:03
case if this John that I am referring it
2:06
should be clear that this John's record
2:07
only is updated and not any other John's
2:10
data and they instead think this way we
2:13
know John salary is 62 000 and we want
2:15
to update the salary of John by 10
2:17
percent for this John and if I give a
2:19
query like this update this salary who
2:22
is having the existing salary as 62 000.
2:24
obviously many people may be having the
2:26
same salary so the real problem is
2:29
identifying the unique tuples and that
2:32
is why we need keys so here we confirmed
2:34
that name cannot be a key because there
2:36
can be multiple people with the same
2:38
name so if I use ID number and if we are
2:42
sure that this ID attribute is unique in
2:44
the table then definitely that
2:47
particular Tuple will be identified and
2:49
that particular Tuple only will be
2:50
updated just think here update the
2:52
salary of the person I need not even
2:55
give the name of the person update the
2:57
salary of the person who is having the
2:59
ID number as 105 so obviously this
3:02
particular salary only will be updated
3:03
because I am very clear with my query
3:06
that I want to update the salary of a
3:08
person who is having the ID number 105
3:11
and it is obviously this John who is
3:13
have having the ID number 105 because
3:16
there will not be another one or five ID
3:18
in the same table this is how we can
3:20
uniquely identify the tuples so I hope
3:23
now you understood why do we need keys
3:25
because these keys are actually required
3:27
to identify the tuples uniquely let's
3:30
now step into the topic of the day the
3:32
case in rdbms when we talk about Keys we
3:36
clearly understood that we want to
3:38
uniquely identify the Tuple so for that
3:41
we need keys when we discuss about the
3:43
relational database management system we
3:46
might come across a lot of key terms the
3:48
key terms related to key are super key
3:51
candidate key primary key alternate key
3:55
unique key composite key and foreign key
3:59
in this presentation we will see all the
4:02
keys one by one let's first start with
4:04
the first key the Super Key what is a
4:06
Super Key actually the super key is like
4:09
the superset and this super key is
4:12
actually the all possible keys that can
4:14
be formed from a relation or a table so
4:16
if we have an employee table and we can
4:19
create any number of key combinations
4:21
from this table say this ID number alone
4:23
can be a key or we can combine ID number
4:25
and name as a key we can combine name
4:28
and salary to work as a key so this is a
4:30
superset so why we are creating a Super
4:33
Key of course this is a key basically
4:35
and we are going to uniquely identify
4:37
the Tuple and that is the real need for
4:39
having key and super key is not an
4:41
exception it is going to uniquely
4:43
identify the Tuple in the relation then
4:46
why do we need Super Keys why not
4:48
primary key or candidate key or any
4:49
other key because super key is the
4:52
superset from which we can derive other
4:54
keys if you are not sure anyway we will
4:56
see that with an example shortly and
4:58
let's take this example IDE and if this
5:01
ID is going to have a null value I'll
5:04
put a question to you can there be a
5:06
null value in this ID
5:07
I mean to say can there be an employee
5:10
without an employee ID no any
5:13
organization will not encourage that
5:15
because we want to uniquely identify
5:16
that particular employee in that case
5:18
every employee will be associated with
5:21
the employee ID will every employee have
5:23
a name yes every employee will have a
5:26
name so obviously ID will be unique and
5:29
this will not be null so I can say ID
5:31
attribute is unique and not null and
5:34
coming to this name attribute if I make
5:36
this name attribute as a unique
5:38
attribute it won't work because in a
5:40
table there may be multiple employees
5:42
with the same name so in this case this
5:44
will not be unique at the same time
5:46
every employee will have a name so
5:48
obviously this attribute will also be
5:50
not null and coming to SSN the social
5:53
security number or the other number the
5:56
reason why I am picking SSN is in
5:59
certain countries every citizen will be
6:01
assigned a social security number like
6:04
in India we have other number with other
6:06
number we can uniquely identify by that
6:08
particular person so this will also be
6:10
unique and this will also be not null to
6:12
some extent why I am saying not null if
6:15
a person who has not applied for other
6:16
so you will not be having another number
6:19
but we need to ensure that every person
6:21
is having other number so anyway let's
6:23
not go deep into this for now let's
6:25
consider this SSN is also unique and not
6:28
null and coming to salary every employee
6:31
will be having a salary and coming to
6:33
phone number employee may have a phone
6:36
number or may not have a phone number at
6:38
the same time this can be unique but
6:41
null values also should be accepted in
6:43
this field what if the employee claims
6:45
that he or she does not have a phone
6:47
number of course we are in the
6:49
Contemporary world every employee will
6:50
have a phone number but there are
6:52
chances that employee may not have a
6:54
phone number so we can't enforce that
6:56
every employee should have a phone
6:57
number isn't it so in that case this
6:59
attribute should accept null value and
7:01
coming to email ID every employee will
7:04
be having a unique email ID at the same
7:06
time if there is no email ID this column
7:08
should accept null value as well now why
7:11
I am saying all this is that the key
7:13
attribute whatever we are figuring out
7:14
it should accept null values also when
7:17
we talk about the Super Key now this
7:20
super key is going to contain either one
7:21
attribute or set of attributes and there
7:25
can be null values in the attribute
7:27
say for example if I am saying name and
7:29
phone number as the super key so name
7:31
will not have null values whereas phone
7:34
number may have null values in that case
7:37
the attribute or attributes which we are
7:39
considering as a Super Key attribute may
7:41
contain null values so in this I can say
7:44
ID is a Super Key because it is unique
7:47
as well as there is no null values of
7:50
course we may have an attribute which
7:51
may contain null values also one example
7:53
for super key here is ID and why name is
7:57
not a Super Key I am saying name is
7:59
definitely not a super key because there
8:02
may be multiple people with the same
8:03
name when there is no uniqueness in the
8:06
attribute which is acting as a key then
8:08
definitely that attribute cannot be a
8:10
key attribute so name attribute cannot
8:13
be a Super Key let's see some example so
8:16
from this we can confirm that a Super
8:18
Key is a collection of attribute or a
8:21
set of attributes so that we can
8:23
uniquely identify the Tuple and one of
8:25
the important points to note about Super
8:27
Key is that it may contain extraneous
8:30
attributes say for example ID alone may
8:33
work as a key but I can combine ID plus
8:35
name together in order to form a key or
8:38
I can combine ID plus SSN or I can
8:41
combine name and SSN the social security
8:43
number to work as a key so what I mean
8:46
to say here is super key may contain
8:48
extraneous attributes simply extra
8:51
attributes the example for super keys
8:53
are remember it is actually a superset
8:56
so here ID alone is acting as a key so
9:00
ID alone is a key or SSN number is also
9:03
unique right so in this case SSN also
9:05
can be a key or I can combine ID and
9:08
name to work as a key right so ID and
9:11
name put together is also a key and
9:13
coming to the next one ID and SSN works
9:15
as a key so ID and SSN can also be a key
9:18
so what I mean ID and SSN I am combining
9:21
together
9:22
and ID and phone number can also be a
9:24
key so this ID and this phone number is
9:27
joined together as a key so we can have
9:29
multiple keys in a relation and that's
9:32
why I told you this super key may
9:34
contain extraneous attributes in this
9:36
case ID alone is enough or I can combine
9:39
with other attributes this is actually a
9:41
superset it may have n number of Super
9:43
keys so anyway why we want all the super
9:46
Keys just to uniquely identify the Tuple
9:49
let's take one example name and email if
9:52
you see name is definitely not a unique
9:55
attribute because many people may have
9:56
same name but with email ID it can
9:59
uniquely identify a tuple right and this
10:02
super key is actually a super set I hope
10:05
things are clear to you now we are done
10:07
with the super key with this knowledge
10:09
let's step into the next key the
10:11
candidate key so just now we had seen
10:13
that super key is the superset which
10:16
contains all possible keys right all
10:18
possible combinations whatever we create
10:20
everything is the superset now from the
10:23
superset we are going to derive the
10:25
candidate key and how we are going to
10:28
derive it the minimal super key is
10:30
actually the candidate key remember the
10:33
minimal super keys are called as
10:35
candidate keys so let's see this example
10:37
now we have a lot of keys here I mean
10:39
Super keys from this we will derive the
10:41
minimal Super Key let's take the first
10:43
Super Key the ID it has only one
10:46
attribute yes of course we can choose
10:47
this so the candidate keys can be ID
10:50
then I have one more attribute here SSN
10:53
I can take this also as a candidate key
10:55
remember if I take this SSN ensure that
10:58
the previously taken key is not having
11:00
this attribute the previously chosen
11:02
category is ID which is this so this is
11:05
not having SSN with it so ID is a
11:08
separate candidate key now we are taking
11:10
SSN as a separate candidate key let's
11:12
come to this key ID and name put
11:15
together working as a key now in this
11:17
case we can't take it as a candidate key
11:19
because there is an attribute ID which
11:21
is already a key so we cannot take this
11:25
key as a candidate key of course it is a
11:27
Super Key but this is not a candidate
11:29
key let's come to this ID and SSN
11:31
already ID and SSN are chosen as the
11:34
candidate key so this super key cannot
11:36
be a candidate key ID and phone number
11:38
no because ID is already chosen name and
11:41
phone number yes because the already
11:44
chosen candidate keys are ID and SSN so
11:46
we can have this name and phone number
11:48
can be a candidate key and again ID and
11:51
email ID no ID is already chosen name
11:54
SSN and phone number no because name and
11:57
SSN are already chosen name and email no
12:00
name is already chosen ID SSN phone no
12:04
ID SSN are already Chosen and phone is
12:06
also already chosen simply email ID yes
12:09
with email ID also we can uniquely
12:11
Identify some tuples right so email ID
12:13
alone can also be a Super Key in that
12:15
case since all the candidate keys are
12:18
not having email ID I mean all the
12:20
already chosen candidate keys are not
12:22
having mail ID attribute this email ID
12:24
alone can be a candidate key so what do
12:27
we mean by a candidate key the minimal
12:29
Super Key
12:31
remember the list of candidate keys that
12:33
you are taking from the super key there
12:35
should be no repetitions of the
12:37
attribute so if ID is there and it is
12:39
acting as a candidate key no other
12:41
candidate key should have this ID
12:43
attribute so we are done with the super
12:45
key and the candidate key let's now move
12:48
on to the next key the primary key now
12:50
we have seen the list of candidate key
12:52
and this primary key is used to denote a
12:55
candidate key what is a candidate key
12:58
just now we have seen right the minimal
12:59
super key so we have already derived the
13:01
list of candidate keys from the Super
13:03
Key pertaining to this table employee
13:06
now the candidate keys are ID SSN name
13:09
and phone number and email now just
13:11
pause this video for a while and think
13:13
which can be a primary key remember
13:16
primary key should be unique and it
13:19
should not contain null values
13:21
so ID can we choose ID yes ID will be
13:25
unique and it will not have null values
13:27
can we choose SSN SSN yes it is unique
13:31
and this SSN is Incorporated in a
13:33
country where every person in the
13:35
country is having an SSN number
13:37
and of course in that case this SSN will
13:39
be 100 unique and not null coming to
13:43
name and phone number there is an
13:44
attribute which contains null values so
13:47
this key name and phone number they may
13:50
be unique but they are having null
13:52
values in it because there is an
13:54
attribute which may take null values and
13:56
coming to email of course with email we
13:58
can uniquely identify the Tuple but
14:00
unfortunately there may be null values
14:02
in the email because employee may have
14:04
an email ID or may not have an email ID
14:06
so in that case which will be the
14:09
primary key a primary key which is
14:12
chosen should be unique and it should
14:15
not accept null values in this case I am
14:17
preferring to go with ID because this ID
14:20
column in the table or this ID attribute
14:23
in the table will be 100 unique in the
14:25
table so there will be no duplication of
14:27
values if one or one is there there will
14:30
be no employee in the same table with
14:32
the same ID value or not one so only one
14:35
time that value will be existing in the
14:37
table so this is hundred percent unique
14:39
there will be no duplication and at the
14:42
same time every employee will have the
14:44
ID value so it is unique and not null
14:47
from the Super Key we derived the
14:49
candidate keys from the candidate Keys
14:51
we are going to choose one of the keys
14:53
as the primary key remember the primary
14:56
key is chosen from the set of candidate
14:58
Keys we have four candidate Keys here
15:00
and I am choosing one of the keys as the
15:02
primary key which is ID and this ID is
15:05
chosen based on two factors one is its
15:08
unique and the other one it is not null
15:10
now who is going to choose that I can
15:13
either go for ID or I can even go for
15:15
SSN right then who is choosing this
15:17
primary key it is chosen with Care by
15:20
the database administrator see such a
15:22
powerful position is having in terms of
15:25
databases he is the one who controls the
15:27
entire database so he is the one who
15:30
chooses the primary key from the set of
15:32
candidate Keys remember in a table we
15:35
can have n super keys and we can derive
15:37
a lot of candidate keys from the
15:40
candidate Keys we can have only one
15:42
primary key because this key attribute
15:44
will never be changed or very rarely
15:47
changed remember there is an employee
15:49
with the id101 so we will not change the
15:51
value of this ID or not one to a new
15:54
value because this is how a database
15:56
should be constructed we are choosing
15:58
the primary key attribute it's not only
16:00
just based on uniqueness and not null
16:02
features but we should also consider
16:04
that the values present in that
16:06
attribute should not be changed never be
16:09
changed or very rarely changed remember
16:12
candidate key with null value is not
16:15
chosen as the primary key in the set of
16:17
candidate Keys we have chosen ID as the
16:20
primary key because it is unique and not
16:22
null we may also consider SSL but we
16:24
cannot consider this name plus phone
16:26
number or email as the key the reason is
16:29
this candidate key name plus phone
16:31
number or name phone number is having an
16:34
attribute which can support null values
16:36
at the same time email also can support
16:38
null values so one of the keys in the
16:40
set of candidate Keys is chosen as the
16:42
primary key and the set of candidate
16:44
keys with null value is not the primary
16:47
key because primary key should contain
16:50
unique values also there should be no
16:52
null values simply primary key is unique
16:56
plus not null
16:58
we are done with the third key the
17:00
primary key let's now focus on the
17:02
fourth key the alternate keys so far we
17:04
have seen Super Key candidate key and
17:06
primary key and for this employee table
17:09
only we derived some keys the Super Key
17:11
candidate key and we have chosen the
17:13
primary key now what is an alternate key
17:15
in the set of candidate Keys the key
17:18
other than the primary key is actually
17:20
the alternate keys we have the super key
17:23
from the Super Key we derived the
17:25
candidate Keys let's say there are four
17:27
candidate keys and one of the candidate
17:29
case is chosen as the primary key so
17:31
there are three candidate keys left and
17:33
these candidate keys are referred as the
17:36
alternate case so alternate keys are the
17:38
keys which are not primary case in the
17:41
example that we are dealing we have
17:43
already chosen the candidate Keys as ID
17:45
SSN name and phone number and email
17:47
there are four keys and which key is
17:49
chosen as the primary key ID is chosen
17:52
as the primary key now what are the
17:54
remaining Keys SSN name and phone number
17:56
and email so these keys are actually the
17:59
alternate case what are they SSN name
18:02
and phone number and email so we are
18:05
done with the alternate Keys let's now
18:07
focus on the next key the unique key see
18:10
there are situations where we may need
18:12
to choose a key that key is unique in
18:14
terms of values but it can also have
18:17
null values in that case we are calling
18:19
that as a unique if you are not clear
18:21
just see this example we have already
18:23
chosen the candidate Keys ID SSN name
18:26
phone number combo and email and we have
18:29
chosen the primary key which is ID so
18:31
obviously the alternate keys are SSN
18:33
name and phone number and email now what
18:35
is a unique key just pause this video
18:37
for a while and think which of the keys
18:40
may contain 100 unique values but it can
18:43
also support null values SSN if it is
18:47
100 unique and hundred percent not null
18:50
so this can be a unique key remember
18:52
this will not accept null value and
18:55
coming to email this is unique every
18:57
employee will have their own email ID
18:59
but if employee does not have an email
19:02
ID then it can have null values so it is
19:05
unique but it can have null values and
19:08
coming to this example name and phone
19:10
number there is an attribute in the key
19:12
that can support null values but there
19:15
is an attribute which says unique plus
19:17
not null but there is an attribute which
19:19
can take null values it can give
19:21
uniqueness right so the unique key can
19:23
be name phone number or even email ID
19:26
and now let's move on to the next key
19:28
the composite key I'm not going to show
19:31
that in a new slide if you see in the
19:33
set of candidate keys this is only one
19:35
attribute one attribute is working as a
19:38
key here and only one attribute is
19:40
working as a key here but in this key
19:43
can you see here there are two
19:44
attributes forming the key so when you
19:47
have a key that is comprised of more
19:49
than one attribute that key is called as
19:51
a composite key in this name phone
19:53
number is the key that contains more
19:56
than one attribute we can also have
19:58
another composite key SSN plus phone
20:00
plus email so that is also a composite
20:03
key because it contains three attributes
20:04
in it that key contains three attributes
20:06
more than one attribute so we are done
20:09
with the unique key and the composite
20:10
key as well before we sign out let's see
20:13
the last key the foreign key now what is
20:15
this foreign key this foreign key is
20:18
unlike other keys that we have seen
20:19
because in the all the other keys that
20:21
we talked about those key deals with
20:24
only one table but foreign key actually
20:26
deals with two tables let's take student
20:29
table and let's also take Department
20:31
table just see this department table
20:33
contains two attributes Department code
20:35
and Department name and this department
20:37
code will be obviously unique in this
20:39
table and let's say this department code
20:41
is the primary key attribute in this
20:43
table and we also have a student table
20:45
and obviously student ID is going to be
20:47
the unique attribute or the primary key
20:49
attribute in this table we know student
20:52
table is having four attribute student
20:53
ID student name Department code and
20:56
credits and Department table contains
20:58
Department code and Department name now
21:00
whenever I am going to insert some
21:02	record here say for example 105 104 I
21:05	cannot insert already one or four exist
21:07	because student ID is a primary key
21:09	attribute let's say I am inserting one
21:11	or five name is Rachelle the department
21:13	code is 200 this row will not be
21:16	accepted because I am trying to insert
21:18	the department code 200 where this 200
21:21	is not existing in the parent table the
21:23	department table so only all these four
21:26	values 101 10200 and 304 only are
21:30	accepted in this table and whenever any
21:33	row that is inserted that row should
21:35	contain the department code which exists
21:37	in the parent table only these four
21:40	values are accepted so in that case one
21:43	table is referring another table that is
21:46	the student tables Department code is
21:49	referring to the department tables
21:50	Department code so this Integrity
21:53	constraint we call as referential
21:55	Integrity where the insertion in one
21:57	table is actually depending on the
21:59	values to be validated in the other
22:01	table that value should exist in the
22:03	other table and foreign key offers
22:06	referential integrity anyway when we see
22:08	SQL Topic at the time I will be
22:10	explaining more about all the Integrity
22:13	constraints for now just understand
22:14	referential Integrity is achieved by
22:17	using foreign Keys before we sign out I
22:20	wanted to say one thing please
22:21	understand the concept of keys clearly
22:23	because when we deal with the chapter
22:25	normalization there we are going to deal
22:28	with the functional dependencies at the
22:30	time the concept of case should be clear
22:32	and that's it guys I hope you liked the
22:35	video and thank you for watching
22:37	[Music]
22:38	[Applause]
22:40	[Music]