 Assalamu alaikum dear students, today we are going to discuss the lecture number 8 of the database management system course. Before starting today's lecture, let's have a brief recap of the previous lecture. In the previous lecture, we started the discussion on the entity relationship data model. We studied what are the entity types and what are different types of entity types. In that, we studied strong entity types and weak entity types. In addition, what are the differences between these two and how they are used. And we also studied that in the ER data model, to represent them, which are the different symbols. In the previous lecture, we also studied the attributes. What is the purpose of the attributes? And how do they link with the entity type? In addition, we also studied the attributes of different types. In which we studied that there are simple attributes, multi-valued attributes, derived attributes, and composite attributes. Today, we will focus on the concept of keys. Dear students, keys are a very important topic in the database. And you should include this concept and this knowledge in the basics of this subject. This is the thing that I would like to tell you that you have very clear concepts. Why? Because the concept of keys is very frequently used in the database. And without having a proper concept of the keys, you cannot understand the database concepts properly. So currently, what we will discuss about keys today, you will try to understand it in a good way. Apart from this, we can also touch on the topic of relationship. So let us move on to today's lecture. These are the two main concepts that we are going to discuss today. Keys and what are different types of the keys. And secondly, we may also touch the nature of relationships. What is a key? Key is an attribute or set of attributes that are used to uniquely identify a particular entity instance. Dear students, what is its significance? That is, why is the key necessary in the database? Because when you look at the purpose of the database, what is its purpose? Its purpose is that you process the data. And in the process, storing the data, retrieving it, and doing all the different types of processing is a part of it. When you store it, after that, when you do any type of processing, almost all types of processing, it involves accessing the data from the database. When it becomes popular in the database, there can be countless records. A database could contain the records that say a few hundred to a few thousand and a few hundred thousand even. And millions of records can also be in the database. Now, if you have a database with a few hundred records or a few lakh records in it, when you want to do processing, the important thing is that you want to identify a few records from all those records. Or you can say that you want to access a few records. One condition is that you want to do all the records, some activity, some work. But one condition is that you want to identify a single record from all those records, whether it is a few hundred or a few lakhs. You want to identify a few records, and you want to identify a few records. Now, the question is that how will you identify the record that you want to access? The natural way of that is that you want to identify a record from any attribute that you want to identify. You want to identify a record based on its value. That is, you give a hint to the DBMS that give me a record that contains this value in that particular attribute. For example, if you look at the example of Wabda, then it is possible that in any city in the case of Wabda, when there is a billing system of Wabda, there are thousands of records. There can be millions of records. Now, when you talk about the bill that you want to print every month, there is an application. Now, this particular application may need access to all the records in the database of the Wabda, obviously. But there may be some different situations as well. First of all, at some point, you get a bill and you find out that there is some mistake in the bill. Now, obviously, you will go to the company and ask them that there is some mistake in the bill, kindly rectify it. Now, obviously, for this rectification, the people there will have to access your record in the database. Now, the records in the database are in the number of thousands or millions. From that, what is your record that you are trying to access or reach to it or remove it? For example, one way is that if we manual it, for example, if you see the manual, then you start printing each register. How will you do it? There are millions of records. Second way is that if you see in the manual system that you have an index maintained, the bill of this area is in the register. But when you say to this, when you apply the same thing to the computer, then one possibility is that you start seeing all the records in the database through the computer. Obviously, it will take a lot of time because there are thousands or millions of records. The second possibility is that it tells you what is your name. If you say that my name is this, then you say that my name is Aftab Ahmad. Now, the company or Wabda tries to find a record in the name of Aftab Ahmad in the database. Now, feel that in some city, how many people can be found in the name of Aftab Ahmad. So, what will happen is that if you find it in the name of Aftab Ahmad, then you will find it with countless records. If you want to fix an area, that I say that in Garisha, Aftab Ahmad is my home, then you can find it here. If you also find Aftab Ahmad in Garisha, then you will find Aftab Ahmad. So, you will have to check the records one by one again. And it is possible that you can identify them on the basis of your address, or on any basis, that Aftab Ahmad, the house number, the street number, that is basically my home, this is my record. So, you can fix it there. This means that this has become a situation. Apart from this, it is important that a person wants to cut off the perimeter of his house. He says that he is not staying here anymore, or he does not want to use it. That is another situation. It is important that a person has the nature of his perimeter. First, it was domestic, and now it has become a commercial application. He wants to change it. So, there could be so many situations in every environment where you have to identify a particular case, or you have to access a specific record. The criteria that you will give to the computer, the database, can be of any value, of any attribute. And I have told you that when you talk about a big database, it is obvious that if you want to find a record on any attribute base, then the value can be duplicated. So, it can be difficult to reach your desired record. It can take a lot of time. And if it is a broader base, then it can take a lot of time. In this situation, what is the ultimate solution? That is, you can identify such an attribute, fix such an attribute, that you know that if in this database, in this particular entity type, all the instances that are added, whether they are one, whether they are one lakh, whether they are ten lakh, all the instances that we want to add in this entity type, the value of that attribute of two entity instances cannot be duplicated. Again, if you decide that this is an attribute, the value of this attribute of two entity instances cannot be the same as this attribute of two entity instances. For example, if you look at the meter number in the Wabda case, the meter number is such that the company's number of customers is not the same. Another example of this is that if you have the company's number of customers, if you store the ID card number in the record of your customer, then the situation is that the ID card of two customers of this electric company, they would never be the same. Every customer's ID card number will be unique. Why? Because it is basically decided with the ID card or its basic philosophy that it will be unique for every citizen of Pakistan. This means that whenever you want to search a record uniquely, you can say that I want to reach that record, even if you do not have lakhs of records. Then you will choose an attribute based on which you are sure that whenever you apply the search in the DBMS on the value, on the basis of the value of that attribute, you are never going to have two records. You are not going to have a chance that you will have two records having the same value in that attribute. Like in this case, if you try to search on the meter number, you take your bill, you have seen from the bill that my meter number is this. Now you say from the DBMS that show me the record of this meter number. It is sure you are always going to get a single record. If you do not have a meter number, then you can tell your ID card number. If the ID card number is stored in the database, then definitely you are going to have a single record. This is the significance of the key. You have to define the key in every entity type. It should be in it otherwise, you can go against processing the data. Dear students, remember one thing especially, you never create an entity type. Perhaps never is not that correct. Most of the time, you do not create an entity type. Most of the time, it exists there. There means in the system, the system that you are developing. It is there. So, as a system developer, system designer, this is your job to identify that attribute. This attribute is going to work as a key in this particular entity type. This means that whatever activities you perform, including this activity, you define and identify the key attribute in an entity type. As I said, in most of the cases, it exists there. But in some very rare cases, it happens that the key is not there. So, there is a problem there. Sometimes it happens that you have to forcefully include an attribute into the entity type that will work as a key. Otherwise, your key attribute serves two purposes. Which one? The first purpose is that it expresses, it represents the information that is stored in its value. This is its objective. This is the information that is stored in it. For example, what is the meter number of a customer? It itself is an information. The second purpose is that it is also serving a second purpose. It is being used as a means to identify the records from the instances of that entity type. This means that the key's purposes are two-fold. I hope you have understood what the key's significance is and how we identify it on what basis. You will have to listen to the names of the keys and use them to define them. So, in order to make the proper use of these things, you must have a clear understanding of what they are and what is the difference between them. So, let's see which types of keys they are. Super key, candidate key, primary key, and secondary and alternate keys. We will read them again and again and we will also look at their examples so that they become clear to you. As you can see on the screen, I have written some data for you here which is basically the data of the students and you can see some of the students' records here. If you look at the attributes here, there are five attributes including student ID, student name, address, class name, and current semester. If you see here, we have the first column where students' IDs are written. For example, if you look at the first score record, address 10020 is the student ID of a particular student. Its name is Suhail Dar. Its address is, let's say, Maria Hassan. The class name is MCS and it is the fourth of the semester. Now, you can see all the records here. If you look at all these data, the quality of the key attribute is that all the records in this case will not duplicate. In any two records, the value of that attribute will not be the same. Now, keep in mind that the key you want to decide will be decided after analyzing the system or after discussing the system with the people who are running the system. For example, if you look at the data in front of you, if you pay attention to this, the values in the student ID are all unique to you. In any two records, the value of the student ID is not being duplicated. Similarly, if you look at the student name, the record in this case, the name of any two students is not the same. From this, we should not make this misconception or this misconception that the student name can also play the role of a key. In other words, even on the basis of the student name, the instances of this type of student can identify a particular record from these. Mind it. The decision of the key depends on the environment. For example, if you look at this, if you say that the five records we are looking at are not being duplicated, then let's declare the student name as key. If you do this, basically, you are putting a condition there that none of the two students can have the same name. That is, you are putting this restriction that two students cannot have the same name in one class. If this class is representing and if this university represents an institute, for example, there are 700,000, 2,000, 3,000, 5,000 people there, then if you declare the student name as a key, this means that in that institute, two students cannot have the same name. Obviously, this is such an unrealistic and unreasonable assumption that it cannot exist. Even you can have the same name of two people in the same house rather than talking about the institute. That is why it means that you look at a particular set of instances and do not decide what is its key. Possibly that could be the key, but until you understand that environment, you should not declare any attribute as the key. I hope you have understood this. Let us move on to the next slide. Simple and Composite. The types of the key we are going to read and mind it. The types are in different dimensions. On the one hand, the type is Simple and Composite. On the other hand, we will discuss all the types that we have read before. Before that, you should know this concept. The difference between Simple and Composite is that such key consists of only one attribute. In that, there is only one attribute. For example, the student ID key. If we declare it as key or if it can be declared as key in any system, in any environment, then in that case, we will say it contains a simple attribute that is why it is a simple key. But this is not necessary that the key consists of a single attribute. No, the key can consist of multiple attributes as well. How? Why? As I told you before, it depends upon the environment. That is, if in any place you are not identifying a single attribute or if there is no simple key, then you will say that it is okay. This means that there is no attribute that is unique in all records. Then you will say let us see the possibility of combining two different attributes and let us have a key. If we mix those two attributes, the condition is that the amount of the record that is entered according to that entity type, the amount of the stress that is entered according to that entity type, the value of those two records will not be the same. The basic criteria of the key is the same that the attributes that are declared and the value of the set of attributes should not be duplicated. So, what is a simple attribute? That consists of a single attribute. For example, if as I told you before, in an entity type, there are no attributes that are unique in all records. The second possibility is that you declare a composite key. For example, in any institution, we say this is our program. For example, we say MCS, BCS, BCE, BIT, MIT. Now, if we store which course is being offered in this program, if we declare that the program code should be the key, then it cannot be that you are offering multiple courses Similarly, if you say let's declare course code as key, that is also not possible, because the same course code can be offered in different programs. So, from here, you can understand that if we combine the program code and the course code, we say the key for this particular entity type is a composite key that consists of the program code and the course code. This means that this course is being offered in this program. Obviously, in one program, you cannot offer one course twice. Obviously, in one program, one course is offered once. This means that you offer as many courses as you want in one program. 15 to 20 courses are being offered. Similarly, in B.C.S., in B.C.E., in M.B.A., in B.B.A., in B.I.T., all are these different programs and all these courses are being offered. These are the codes that you will combine like M.C.S. and D.S. This means that M.C.S. program data structures. Then you said M.C.S. database system. D.B.S. Now, M.C.S. its value is twice. M.C.S. data structures. M.C.S. D.B.S. But the thing is that the course program code you have not declared that as key. So, if you have the value of M.C.S. twice, that is not violating the condition of the key. Because your key is the program code and the course code. What is the value of M.C.S. and D.S.? Data structures and M.C.S. database system. Similarly, the database system is also being offered in M.B.A. M.B.A. and D.B.S. Now, the thing is that D.B.S. is twice. But the thing is that these are the programs. The beauty of this component is that as a whole that value will never duplicate. This means that first of all you have B.C.S. record whenever you say B.C.S. and N.W. means network, you are always going to get a single record. That is the use of key and that is the beauty of the concept. Let us go ahead. Super key. This is the first type of key that we are going to study. Super key is that it is the same as of key that we just studied. An attribute or set of attributes that are used to identify a record uniquely. For example, if you talk about an employee's entity type then the employee ID can be used as a super key. Similarly, if we talk about a student then the student ID can work as a super key. Similarly, as we talked about in the course offering program code and course code, together they are working as a composite and we can also call it a super key. But the special thing about super key is that you will have an idea that will be clear from the next slide example. Let us go ahead. About super key, it is said that attribute or set of attributes can be used to uniquely identify record. As we talked about earlier, the student ID can be used as a key. But if you pay attention to it then you will understand one more thing. If we say that from student ID we can uniquely identify the record but at the same time we can also identify the record using the student ID and student name. If you go to confirm an air travel ticket then to uniquely identify if you just tell him or her your ticket number or your computer number that would be sufficient. But if you say that my computer number is this and my name is this then using these two jointly your record can also be identified. If you say that my computer number is this and my address is this or my phone number is this then again your record can be identified using all these attributes. This means that if you have a super key you said that your ticket number or the screen in front of you if you talk about student ID then student ID is your super key. But in terms of the definition of super key if we attach more attributes with super key from the point of view of access we don't need them. But still as far as the definition is concerned when you add one more attribute with it that would still be able to identify the record which means that it is also super key. This means that the record you are seeing in front of you you can identify a lot of super keys i.e. your student ID if we say that it uniquely identifies records if we add 1, 2, 3, 4 all these attributes all of them would be super key. I hope that you have understood that if we attach more attributes with a super key and basically some of them could be additional then they would still be the super key. Let's see what is the other type of key. Here you can try to understand this as I just told you that if you attach more attributes with a super key then they will also be your super keys. This means that if you have one super key and some other attributes then you can add one more attribute and it will become another super key. So by adding more attributes with the super key you can have some more super keys. Let's move ahead. On this slide you can see the definition of the candidate key which you have to understand a candidate key is a super key that does not contain extra attributes. It might have more than one attribute that uniquely identifies an entity. This means that the super key of the candidate will not have any additional attribute. As I told you the super key is that except for the minimum required attributes it will remain a super key. But the candidate key will have no extra attribute that means it will be your super key which is described in another way that a super key such that no proper subset of this attribute is itself a super key. You can have a super key whether it is simple or composite. But the thing is that it should be minimum. The one that you declared a super key and there are such set of attributes that if you take the same attribute then it will not be a super key. Again if you have a super key if it is a super key then this is by default a candidate key. I repeat this that if you have a super key in which there is only one attribute then obviously no proper subset is possible to be a key. If there is no false attribute then it is definitely a candidate key. First of all you have a super key in which there are two attributes that is two attributes together and we said that they are identifying the records and they are super key. Now if we reduce one of them then it will not be a super key that is it is not possible to identify those records uniquely. For example we gave an example of program code and course code. Now we have an offering that is being made by super key these two attributes are being made. Now if you see first of all we remove course code. Then we have program code. Now on the basis of program code you cannot uniquely identify the records or you can say that the program code that is not a super key. That is you will have such key records in which the program code will be repeated. For example, MCS you will get key records. In BCS you will get key records. Similarly, if you do first what we said that program code and course code remove the program code you can say that only the course code is used for the unique identification you cannot because the course code records the same value you will get. Like we said that the data structure is DSI. DSI is being offered in MCS and BCS. If you see the value of DS then it is appearing in more than one records. It means that only on the basis of its value you cannot uniquely identify the records. So in the case of super key both are being made. Program code because it is less than super key and less than this is not possible then we will call it candidate key. But first we say program code and course code we called these two attributes and first in this entity type there was another attribute in which we called marks allocated. In this program when we offered this course how many marks were there? If we include marks allocated as part of this key program code and course code then these three are also super key. But if you consider this super key there is an additional attribute that if we remove it then this super key which is made by program code, course code and marks allocated if we remove the attribute which is marks allocated then the other two attributes are enough to uniquely identify or they are super key. This means that this super key in which there was an additional attribute or the subset of this super key could also be uniquely identified. This means that this super key is not a candidate key. I will repeat that if there is a single attribute then this is for sure that it is a candidate key and if we remove the attribute then the thing that is left behind does not have the question of being a key therefore a super key consisting of a single attribute is definitely a candidate key. Let us move on to the primary key. A primary key is the main or the chosen candidate key from the possible set of candidate keys that is most suitable for entity identification. From here you have an idea that we can have multiple candidate keys in an entity type as we had said that any attribute is added to the super key which is going to have more and more super keys. But the candidate keys although they do not generate as I am saying but the data your attributes are present in an entity type that we have two different candidate keys i.e. if you look at the example of a student then the student ID is a candidate key if we first look at this same student entity type we have also stored an attribute named the national ID card number in this case when we put a value in this attribute the value in it will also be unique in this attribute so the value in it will be unique this means that you can use the national ID card number also to uniquely identify the records and since it is minimal it cannot be smaller than this because it is connected to a single record this means that this is your candidate key this means that in the student entity type we have got two different candidate keys similarly we have in the entity type more than 2 candidate keys although it is not so frequently but still theoretically it is possible that you have more than 2, 3 or 4 candidate keys in an entity type among those which you choose as a key which you declare that I will use it as key for this entity type as primary key once again it will show that whenever we use the term key we mean by it the primary key so if in any one table we have only one candidate key so obviously we have no other choice that we will declare that candidate key as primary key so this situation is simple but if we have got more than one candidate key 2, 3, 4 we will select one and declare that this is going to be used as key that would be called the primary key now the question is who should we declare as candidate key this is a technical and sometimes subjective decision as well that you have 2, 3 candidate keys if you ask anyone who should you declare as primary key different people may opt for different candidate keys and it is obvious that when we declare a candidate key then technically speaking all decisions are correct now the question is who should we declare as primary key the criteria and the for example you will see that such a candidate which is small in size small in size that has less number of attributes let us say we have 2 candidate keys 1 of them is single attribute 2 or 3 attributes one criterion is single attribute you can use it as primary key why? because when you have to type that value let us say for the sake of search for the searching of a particular record so user will find it easy to enter the value for 1 attribute rather than entering the value for 2 attributes mind it both are correct but one criterion which has a single attribute you can use it as primary key besides that first we have got 2 candidate keys and both are simple single attribute keys in that case it is easy to remember whose value is small in length you can use it as primary key so now this is a different decision or one more thing we have 2 or 3 candidate keys but 1 attribute the users the users of the organization are more popular to remember them or use them for the identification for example if in an employer's record your national ID card number is present but the people familiar with the system they do not generally use the national ID card number for the unique identification rather they are more familiar with the employee ID that is used in the organization that is organization specific so then you will prefer to use this type of attribute as the primary key so you should have a sequence that you have the first concept is of the super key and obviously if the super key is not all of your entity type if in a super key all of that entity type is not being made super key then you have multiple super keys in an entity type after that what is the candidate key by definition every candidate key is a super key but not the other way round ok and if you take this forward then every primary key is a candidate key but if you look at the candidate keys then one of them will be declared as primary key so every primary key is a candidate key but not the other way round let's move on the other thing which we will read in the future primary key has a characteristic that its value cannot be duplicated but along with that the primary key cannot be null null also has a concept which we will further define but for now you think of the value of an attribute as null that is declared as not defined not given the attribute value which you do not enter you can think the value of that attribute as null and one more thing that null which attribute you declared as numeric, as number for example you say age salary what is this attribute numeric type consider the date for example date of birth date of joining all the attributes the domain of that attribute you say name, father name address the domain of that attribute will be text now the null in all of these cases it will mean not defined but if we have a number of attributes like weight of a person if you have written zero zero does not mean null because zero is a definite value when zero has something it has a special meaning what you call as null it means not defined not given but zero has a special value that is why you call zero as a single space in the string case it will have a special value something that is not defined not given dear students, the concept of null is also a sophisticated concept you will read more about it now you have briefly discussed about null the purpose is you declare it as primary key that primary key cannot take null value what does this mean that by definition on primary key it can be duplicated there is another constraint that it cannot be null what does this mean when you introduce an entity instance you will have to give a special value in primary key you cannot say this is an entity instance I do not have at this moment but I definitely have got the value for the rest of the attribute this is not possible you will have to enter the value a valid value a legal value for the primary key what does this mean the first thing is that attribute can have value only out of its domain if you declare a number then you will only get a number if you declare a date when you enter the value then you will only get a date so one constraint is that attribute can have value only from its domain this is the first thing the second thing is that you must enter its value third thing is you cannot enter the null into the primary key or mind it if you have a primary key it is a composite key if you have 2, 3, 4 attributes in your primary key then none of them can take the null it is not just that if we have 2 attributes then we will say that we will not give a single null but we will give a single null at a time for example we have program code and course code if we declare them as the primary key then in that case you will not be able to give the program code nor the course code both of them in that instance you will have to give a legal value this is a very important point that you must remember and this is a very important characteristic of the primary key let us move on as I told you that in an anti-tribe we have multiple candidate keys now one of them on a different basis you selected a particular candidate key as the primary key they will be considered as the alternate keys so from here you got the concept of another type of key and that is called the alternate key the candidate key that is not selected as the primary key is called the alternate key the alternate key will be on the basis of which you can also uniquely identify the records but the thing is you do not choose it due to different reasons you can still use it to uniquely identify the records so this is another type of key the alternate key the data that you are seeing the records that you are seeing those are the examples that I am showing you again if you see we said that one of our candidate keys is student ID the second candidate key which we identify and establish obviously by discussing it with the user of the system of the organization so we identified that student name and the address these are both candidate keys now the same thing that you do not declare it seeing the entity instances because the uniqueness of the values of some instances could be just by chance so you should expect all kinds of data in the future and you should declare the uniqueness if it is confirmed from the user of the system here we said that there are two candidate keys student ID, name and address you should keep in mind that when we declare the two candidate keys what is the implication that they are supposing the implication is that from one house when we say address is representing the home address of the student so from one house two students cannot come with one name obviously logically we will say how can two people come with one name let's say two brothers or two cousins so the other name will not be there but when you are designing if you are making such decisions and declaring something as a candidate key then you should think of all possible scenarios for example if there is a house there are two portions and there are two families if we say that the address of the house is one there are two portions and in both families there are two children or two people and both of them should come to the same class there are many ifs but the thing is this is quite possible in real life so if such a situation comes and your design database your developed database is not able to handle that situation that means your design was wrong and your system that failed in a particular situation it shouldn't be like that if first you have declared that the student name and address were made as candidates then you should think if this situation also comes that two portions of the house are in the same place then we will identify those portions house number this, street number this area, portion A and the same value for and from here portion to portion B you make those addresses as different then it is possible otherwise if you think the address is the same thing in that case the design will fail that you are not able to handle this situation so we continue with our example that we have two candidates here now one of them which you declare as a primary key for example look at the student ID one is a simple attribute and the other is that it is small in size then it is easy to type similarly if it is small in size then it is easy to remember but if you declare the student name and address together or select it as primary key then it is a candidate key but if you select it as primary key then the problem will be that talking is relatively hard therefore in most of the situations you select the student ID as the primary key now student name and address jointly being the candidate key now we will call it as an alternate key so I hope you have understood that the primary key candidate key and the alternate key and obviously I hope you have understood that student ID is definitely itself is a super key student name and address yes definitely but student ID and student name they are super key but not the candidate key if it is not a candidate key then obviously it cannot be a primary key and not an alternate key similarly the student ID and class name but they are not the candidate key and when it is a candidate key then there is no question that it can be a primary key as you can see on the screen there are different types of keys that have been cleared on this slide you can see that there are two things that have been discussed the first thing is the need for key and I hope that you have cleared this that the logic of key is something that is 10 or 20 or that is 1,000,000 if you want to identify one of those records then you can identify it now apart from this another concept of key that we are going to read in this chapter is secondary key secondary key is a set of attributes that on the basis of which you access the records it is not necessary that the access you get is unique this means that the key whether it is a candidate key or a primary key they can be uniquely identified but due to some reason they are not available or they are not popular for the access purposes for example if you take your air ticketing example and also with the help of the ticket number but they are difficult to remember and may be a common man cannot remember both of them or they do not remember in that case obviously name is something that on the basis of which he will say that my name is this so please tell me whether my ticket is confirmed or not obviously when you try to access the records with the help of name then you will not find it unique but you can say that my flight is from Pindi Lahore so you will get another criteria that you said that name is this Abdul Wahid and your flight is from Pindi Lahore in that case if you mix these two things then it is possible that you will get 2-3 people in that flight so in further description the operator will say that Abdul Wahid is this your father name and then you can access that particular record so there is a secondary attribute that on the basis of which you access the records but it is not necessary that the answer is unique Dear students, here I will sum up today's lecture today we have a very important topic of the database which we have discussed and I will tell you that your notes or your books read them and look at the examples and try to understand these types of keys and especially the difference between them so that when you say candidate key like a flash, you get the whole concept you say primary key so immediately you will understand what is the primary key and what are the constraints and we will keep that in the next lecture till then, I will see you soon Allah Hafiz