 Assalamu alaikum. This is Database Management System course. The course code is CS403. Today we are in the lecture number 11 of this course. In the previous lecture, we discussed the cardinality of the relationships. We discussed the importance and different types of cardinalities. Then how do we represent these cardinalities? Apart from the one main cardinality notation that we will use in this course. We also discussed different types of cardinalities and established the link between the one we will use and the other ones. After that, we also discussed the enhancement in the ER data model. And at the end of the previous lecture, we were discussing the supertype, subtype relationship between Henry types. Today, we will start our discussion in the continuation of the same topic. So, let us start today's lecture. Inheritance. Dear students, we get the concept of the phenomena of inheritance as a result of the generalization, specialization or supertype, subtype relationship. It is a very famous idea. It is very popular. It is very well known among the CS programming paradigms. And especially, it is the feature of object-oriented programming paradigm. What is inheritance? Inheritance is that all your supertypes will be inherited, and all the subtypes will come down. The same concept of inheritance in our normal life, in real life, is that we say that a person from his elders or from his father found this property, or this thing, inherited in inheritance. In the same way, when we talk about supertype, subtypes, all the attributes you have defined with supertypes, or with a general type, they will automatically come into all the subtypes which are linked to them. That is, all the subtypes, the supertypes are linked through supertype-subtype relationship. They inherit all the properties, attributes which are supertypes. This means that if we say that all our subtypes are common attributes, instead of defining them in different subtypes, we place them in a subtype and establish a link between them, this is the supertype subtype relationship. The advantage of this is that all the subtypes automatically come through inheritance, and apart from this, all the subtypes you want to attach to any of them, you attach them there. If you look at inheritance, all the object oriented items, programming, data model, all of them are discussed with detail. But where we have to know so much is that the attributes of supertype will automatically come into subtypes. At this time, the diagram shown on the screen, one of them is an employee entity type. There are two subtypes of this type, salaried or hourly. You can see that if we have some employees in any organization, you will feel that some of them are employees who are on a permanent basis, they will be called salaried, they are fixed or determined on some basis. Some of them are employees who work on an hourly basis. We have identified these two types in any environment. The attributes we have in salaried and hourly basis, both of them are common. For example, we said that the employee is an ID, the employee name will be salaried, the hourly basis will be the same, address, phone number, these are all those attributes that are associated with both, with salaried employees and hourly basis employees. We will attach all these attributes to our supertype employee. After that, all our subtypes are hourly basis and salaried. These are our subtypes. So we have done this with the specialized attributes, the particular attributes that we have associated and shown with these subtypes. As you can see on the screen, with salaried, we have attached the attributes that are grade and annual salary. Now, these are the attributes that are specific to salaried employees. Similarly, if you look at our hourly basis employees, with them you have the number of hours. Let's say, if you do it on a monthly basis, then how many number of hours does a particular employee have? Because on a salaried basis, how many hours has he worked? Let's say, in the whole week or month. And secondly, the hourly rate. This employee, a particular employee, what does the hourly rate give? So that you can calculate the salary of both of these attributes. Another example of this that you are seeing is in which you have shown a supertype employee and also there are two specialized anti-types which are student and faculty. Similarly, both of these types are student and faculty. Their common attributes, which are ID, name, address, you have associated them with the person. Now, through inheritors, the three attributes which are associated with the supertype, are automatically associated with the student and faculty. Now, here in this diagram, you are seeing two subtypes. Here, whatever is there, if you have three, four, five, whatever is there, they inherit all of them. Or these attributes are attached to all of them. Then you see that we have attached some additional attributes with the student who are specific to this type of anti-type. Similarly, the faculty we have here, for example, their qualification and grade. These two attributes, they are specific to the faculty anti-type. So they are shown here. But practically, what is happening is that those three attributes are associated with the person anti-type. Those three, and the two we have defined in the faculty, are these two. So this means that actually, the faculty is our anti-type, they have five attributes. Dear students, the beauty of this concept is that what you are seeing here is a hierarchy, what you are seeing here is a supertype subtype. It can go up to the depth of any level. You can see here that we have a supertype and on the next level, there are subtypes. First of all, if we take another level, for example, if we declare that the student we have is further two or three or four subtypes. For example, we say that morning student, evening student. So what will happen is that these are not subtypes of the anti-type student. For example, if we have a faculty anti-type, if we want to store any organization, we say that we have two subtypes. Regular faculty members and visiting faculty members. So this is how we have become subtypes. Now look here, the subtypes of your second level, all these attributes will inherit you. Those attributes associated with the person, and those attributes associated with the student, the morning student and evening student, they are combined. The technical thing is that the subtypes of your student, the morning student and evening student, those who are inheriting, they are inheriting from its immediate supertype, i.e. the student. Because the student through inheritance, the attributes associated with the person, those five attributes, of three persons and two students, these five are from this place, i.e. from the student, in the morning and evening student. Similarly, if you look at the faculty example, in the faculty, the regular faculty, the permanent faculty and the visiting faculty, those attributes will inherit both of them, but through the faculty. One more thing, do not confuse it kindly, as you can see, we are discussing an example, if you look at this, there are two subtypes of the person, the further discussion of the student, there are two subtypes of the student, and there are two types of faculty to discuss the further discussion, then it is not necessary that each type has two subtypes, or one level is the same, for example, if we are discussing the same thing, it is possible that your organization, the system where you are developing, the subtypes of the student, they want to show, but not for the faculty. This means that if you look at the faculty, they have reached level one, if you look at the student, then you have reached level one, which is the morning student and evening student, and if you want further discussion, then you can enhance them further. So, as I said earlier, it can go up to any level, so whatever your subtypes are, they are all right from the root of this hierarchy. All the attributes, that are coming from different supertypes, they will inherit all of them, and plus, apart from inherited attributes, the attributes that you want to associate, specifically, with that entity type, you can also associate with it. Let's move ahead. What is the advantage of this? Why is it so famous and popular concept? To know this, dear students, you will have to understand that, what is happening with inheritance, that whatever your subtypes are, at any level, the common properties of all of them are associated with supertypes, and now, you have one advantage, that those types, which were common among all these types, instead of writing them again and again, you have only written them once, so that your diagram becomes simpler, and its complexity will be reduced. Another thing is, if you do the first thing, you have to change something. What is the change? You want to add some attribute, or you want to remove some attribute, in all the subtypes. Instead of doing the first thing, there are three subtypes, and you have three attributes, which are common. The first thing is, instead of writing them separately, you have to write them once, plus, if you want to add an attribute, instead of adding that single attribute, you start adding in all three subtypes individually, what you will do, you simply add that attribute into the supertype. When you place the supertype, what happens is, through inheritance, it automatically applies to all the subtypes. With this, your management, overall design, it becomes easier, it becomes simpler. How to identify? There can be different ways to identify. About this, look at this thing, the user of the system, the organization, they will never tell you, they will never ask you, to arrange these things in the supertype subtype. No. This is your own decision, that is, if a designer, doesn't represent them in this way, in the supertype subtype form, in fact, whatever subtypes you have, that designer represents them as individual entry types, that is, they don't take the advantage of this property, and the attributes I mentioned, they do it individually with all the subtypes. No harm in that. The extra work, the extra labour, extra effort that has to be managed by the designer, obviously. But now, as a designer, you have seen that you can see some things in that environment, that what you have said, that there are some things that are common. Through abstraction process, which I have mentioned again and again, under which process, you identify the entry types, you identify the relationship between them. The thing is that you think about it, and with the passage of time, when you have training, you are able in a position, to identify such things. Secondly, the time you have identified your entry types, and after that, when you associate different attributes with them, when you have seen those entry types and their attributes, on the basis of that, you have said that there are different types, but there are some attributes that are common. On this basis, you can identify them. And then, another thing is that some things are natural. For example, if we look at some places, in an academic situation, we identify staff members, office staff, faculty members, students. And let's say you have a visitor. So, if you consider that there are some things that you can see naturally, that they are human beings, they are persons. In a building, when you identify the items, you see tables, chairs, computer tables, and rostrums. When you realize that they are all of a common nature. Similarly, if you have fans, tubes, computers, air-conditioners, then you say that they have electronic equipment. So, this may be something common to you. So, where you see such a situation, that what you identify naturally, if there are some things that are linked to some basis, then on this basis, you can think that there can be a relationship of super-type type. Plus, when you look at their attributes, they are further affirmed. So, in this way, this is how you identify the super-type-sub-type relationship. And then you arrange things. I hope this is not clear to you. You identify the general knowledge based on the attributes. When you once establish that there is a relationship of super-type-sub-type between these types, then you have to arrange them. Now, there are some further constraints that you want to specify about this relationship. Different situations. What do we use for them? The first thing is that what are those constraints. The first thing you see on the screen is completeness constraint. There are two types of completeness that are total specialization rule and partial specialization rule. To understand this, you have to understand that the super-type of your instances are necessarily of any type or any type that belongs to you. If you have an instance of the super-type, then do you have any sub-types that belong to any sub-type? Now, this is called the completeness constraint. This thing again, you won't define it yourself. This thing will exist in the environment and you would simply identify that. For example, we had a super-type employee and sub-type are salary and hourly basis. Now, the question is if we have an employee, do we necessarily have salary or hourly basis or any type of sub-type? What is the reason for this? What business do we do? There can be two main reasons for this. One reason is that a super-type has more sub-types but when you design it, instead of representing all the sub-types that are significant, you represent them. Now, when you have super-types that were significant or that exist more frequently, you rated them with sub-type or you linked them with sub-type. But those who were not significant, you didn't rate them with anyone. This means that we can have an employee who is neither in the salary category nor in the hourly basis category. So, if it is necessary that every instance should be linked with someone, then it is called Total Specialization Rule. And if it is not necessary that your super-type is related to any type, then it is called Partial Specialization Rule. And look, for a reason why it can be partial, it can be that the type of system you are designing at that time, you are not in a position to identify all possible sub-types. The type of design is possible but in the future there can be a sub-type which is not defined. So, its natural solution is that initially, you let it be in the super-type. But sub-type, because the particular sub-type does not exist or is not defined, for that you have to make a change in the database design. So, until it is implemented, you let it be floating or hanging. It has become an instance of the super-type but it has not been yet associated with the particular sub-type. So, this is how you define the completeness rule. The other is the disjointness constraint. There are two things in this disjointness rule and overlap rule. What does this mean? It means that if you have an instance in a super-type then the disjoint rule is that all the sub-types you have will be linked to one of them. Or when there is a super-type one of the specialized types will be of one type. So, if we say that we have an employee, they are both solid and original. So, the disjointness rule is that if we have an employee then either it will be of the original basis or it will be of the solid basis. It cannot be that we have an employee or an hourly basis. Again, a rule that has to be specified by the organization, by the company and you will simply define that. You will simply represent that. You do not have to play a role in its definition. You just have to represent it by discovering it that it exists in the environment. When you are talking about overlap what does it mean? It means that at one time it can be of two or three types. This means that if we have if we have 1,000 employees and it has two sub-types salary and hourly basis. So, if we have a disjoint situation i.e. if one employee is of the solid basis this means that if we have sub-types i.e. salary type and hourly type if we sum up the two instances then the maximum will be 1,000 because at one time the employee will be of the same type but if we overlap i.e. some of your employees are of the same type because you have a salary and an employee has an entity type instances but if you sum up the sub-types instances then the sum of them can exceed 1,000 because some of your employees have a record of both salary and hourly The different situations and examples of them will be shown in the next slide Let's see In this first example the sub-types are patient, outdoor patient and indoor patient Our super-types are patient and sub-types are outdoor and indoor patients This is a completeness a complete specialization rule i.e. your patient should either be outdoor or indoor What does it mean? It means we have identified all possible sub-types Secondly when you enter a patient's data in patient entity type the rest of the details will have to enter either in the outdoor patient or indoor patient i.e. in the patient you have patient ID These are the attributes of the super-types The first thing is the outdoor patient and the indoor patient will have these attributes automatically through the inheritance The second thing is the outdoor patient has a prescription i.e. the patient a doctor has seen it and gave it a prescription you gave it a description and prescribed it The second thing is the indoor patient We want to store what was in the ward and the date was discharged These are the attributes that are specific with the indoor patient otherwise both of them are inheriting from the patient The emphasis is that this is a completeness rule and its data does not go into any of them Dear students there is another thing that you might be seeing that the patient your entity type is linked to another entity whose name is Responsible Physician i.e. if we have a patient whether it is indoor or outdoor which physician treated that patient Now this is an independent entity type whose attributes are but this entity type is linked with the patient The beauty of inheritance is that this relationship will be automatically inherited i.e. this relationship because it is applied to the patient automatically will be applied to the indoor patient and outdoor patient This is if you want to establish a relationship either with the outdoor patient or indoor patient then you will show it specifically with them so obviously that that thing will be applied only on that type and the other thing that I should remind you is that inheritance is always in a particular direction i.e. the super type its attributes they are being inherited towards the subtype whatever you are defining in subtype the effect of that neither is it going on the super type nor is it going on the same level of the subtype the other entity types which are existing on the same level nor is it going on the subtype yes, if there are further subtypes of this subtype then obviously under the rule of inheritance they are being inherited below but on the side ways there is no effect of that so what we are seeing responsible physician this relationship is automatically being implemented on the outdoor and indoor patient but the attributes of the indoor and outdoor patient their relationship is only with this type their relationship is not with any other type in the second example what you are seeing in this a partial our rule the partial constraint is being implemented here you are seeing an entity type in the name of vehicle and you have identified some of the subtypes let's say you have identified car and truck but the thing is here we have a partial constraint in which way the stresses you have it is not necessary that it should be of any type then the same thing why did we do this because in an organization there may be many types of vehicles but they wanted to represent our trucks that organization owns motorbikes even bicycles or even they want to have a vehicle a bicycle so you have identified them as vehicles that we have motorcycles let's say you have noted that is also an asset you will note in your assets but what we need the detail the one that are more significant cars and trucks you have also identified some specific attributes of these entity types so when you are entering the data of a car or a truck into the vehicle the common attribute you will place them in it and the detail of it if it is a car then it will be in the car if it is a truck then it will be in the truck but if it is a motorbike if it is a bicycle then it will be in the vehicle but it is obvious that neither the truck nor the truck will be in it because their entity type is not identified so this is a partial constraint how do you represent them if you look at their difference where there is a complete constraint there is a double line which is your super type from there and the circle which links these types the line that is double and where your partial mapping is coming there is a single line that is how you identify the complete and partial super type sub type relationship and this is how you represent them let us move on how do we represent disjointness students in this we took an example of a complete constraint in which we showed patient, indoor patient and outdoor patient we take the same example disjointness as an over lapping example first we decide in the hospital there is a rule in the hospital that the patient who comes will either be an outdoor patient or an indoor patient the patient who we have but if we have an indoor patient he will not be an outdoor patient they are disjoint when we represent this the circle which is linking the super types and sub types between them we placed a D when you see a D in this circle how to write it down this means that the super type sub type relationship is disjoint what does disjoint mean that the super type will be linked with an entity type but if we have a situation that the super type can be linked with multiple sub types that is a situation that reflects an over lapping constraint in this case you will place O in that circle as you can see on the screen we have a part super type and it has two sub types manufactured and purchased the common properties that have been represented with the part and the specialized attributes are discussed that is what we have already discussed then the super type attributes are automatically being inherited by the sub types here you can see the super type sub types we have placed an O this is an over lapping situation between sub types and super types this means that a part can be related with either one of them or both of them that means we can manufacture and we can purchase but we can manufacture but also the quantity that we want is less than our production capacity so we can purchase it from the market so the instance that you have of the part and the type will be linked in both and in the manufacture and here you can see that when you see it with another anti type which we said is supplier so this supplier and the relationship between the purchased part is a normal relationship that we have already discussed I have told you that the super type sub type is also a relationship but a specific type of relationship which we have purchased part will be treated as a relationship and the rest of the relationship which you can see here is neither on the manufacture nor on the parts because this is specific to the purchased anti type I hope you have clear that our complete and partial disjoint and overlap means that the sub combination is independent what does it mean it means that the complete and partial are in their place and the disjoint and overlapping are in their place this means that there are 4 combinations which means that the relationship of sub type can be complete disjoint complete and overlapping similarly partial and disjoint partial and overlapping there are 4 combinations and the two properties they exist independent of each other when you represent them you get 4 different combinations let us go ahead sub type discriminator this means how we will establish in our design when we will implement this design how we will establish by saying an instance of the super type of what particular sub type this instance is this is our super type this is our sub types which one of them will relate with this is for sure that all your sub types are related with super type only in this relationship i.e. a sub type can relate with others like we have seen of purchase and supplier but if we talk about this particular relationship sub type definitely it would be related with the super type there is no doubt in this now super type can link with this relationship with any multiple sub types so from there how would we establish seeing an instance of the super type for its further detail where should we go for that we use techniques sub type discriminator to determine the sub type of a super type instance we say and the super type whose value determines the sub type type now there are two situations one situation is the disjoint or overlapping if you have a sub type super type relationship is disjoint i.e. a super type instance that can be linked only with one sub type in that case simply you define an attribute in a super type and its value determines that this particular instance in every instance you will place some value in that attribute and on that value you will easily know which sub type it is if you look at the next screen on this screen we have vehicle situation vehicle is a super type and its sub types are car and truck now if you have a vehicle or truck it can't be that it is a truck so you will simply place an attribute vehicle type and if it is a car you place simply a character C in the value of that attribute so when you apply the attributes that you have vehicle ID, model registration number along with that vehicle type this was the other type but in vehicle type you will place C for car T for truck that means when you see an instance of vehicle type the further detail is that car will go into entry type and if T is written there you will go into truck entry type because we had a partial constraint we may have vehicle type or truck type so obviously you can fix another character or leave it blank but in some instance we do not have its value from C or T so this will be assumed that this is our vehicle which does not relate to any type so this way you can represent it so this was the situation of subtype discriminator when we had the constraint on the rule of disjoint if we have a situation of overlapping that means your one super type can be of multiple subtypes it can have different situations different ways to represent it for example as we have an example on the screen we have decided that part could be manufactured and overlapping and it could be of both types at the same time that you define a composite attribute which you say that is part type and then you think of two component attributes in which you say this is manufactured or other is purchased if it is only purchased then place Y in the purchase attribute and know in the manufactured if it is manufactured place Y means yes in the manufactured and know in the purchase and if it is both purchase and manufacture then you place Y in both so this is the point that this particular instance of your super type the further detail you will get in both subtypes see what is the significance of subtype discriminator when you placed an instance in a super type now obviously you would like to see where is the further detail of subtype if you do not place subtype discriminator then you have no other way than searching each subtype individually and finding the link between the super type and subtype i.e. you have a part where we have an instance where you see the basic attribute which is common you have to think about the further detail for that if you want to go in the subtype or in the purchase type if subtype discriminator is not there then you have to go in one of the subtypes first you have to search if you find the link then you are fine otherwise you do not have to go in the other subtypes and now there are only 2 subtypes if there are 3 subtypes if there are 4 subtypes then you will realize that it could have been very problematic it had been very time consuming to avoid that you simply place an attribute simply place a single character valued you will be able to see which subtype will be linked so take the super type its common attribute and immediately jump the appropriate subtype to see the remaining details so in this way the subtype discriminator that makes the thing very easy for you dear students here we have concluded our discussion on the ER data model if you see in this you may find different mutations in some books or in some books you may find some other features as I told you there have been so many different features in the ER data model but here we have required and necessary basic data model and its enhancement we have covered it and in the rest of the course we will use the ER data model as much as we can and secondly the major features that we have already discussed and that we have covered in detail at this stage I have been discussing the ER data model here I will quickly present the running summary so that you can recall it and secondly this description is brief see to clear some of your confusion this semantic data model and you know what is the significance of the data model and the semantic data model because it is semantically richer it requires more construct that is why it is a semantic data model conceptual database design the conceptual database design is our first design which is comprehensive design and represents the whole system and it is the second stage of your database development process then it provides three main structures which we have said that entities, relationships and attributes the term entity it reflects three different things dependent on the system and on the person who is designing that diagram let us discuss what are the ways to make the level node diagram what is its methodology to make the level node diagram the first phase is to make the diagram for the system you can identify different modules and you can start showing it in detail when you represent those modules in the appropriate places there will be a language in those modules how can that link be created through entities through processes through data stores in any way you can draw entities involved in that and then write the process involved in it and in the same way you can make the data stores involved in it and when you represent one module then you take the other module one by one and then again apart from this what we have to keep in mind is that the process number that we write in the process symbol its format is you will give it number like 1, 2, 3, 4 like this and after dot or decimal, you will give it 0 that means your level 0 level node diagram the process you have always 1.0, 2.0, 3.0 in this way it will be numbering it depends on the process you have represented in the level node diagram there will be some processes that you have shown but the details are too much if you show the details of that process in the level node diagram it will have the same problem that the diagram will be complex which will be difficult to understand so you do that the big process along with that you show your level node diagram but the details of that process you explain it further in the detailed diagram and one more thing is that you can have just one context diagram for any system you can have just one level node diagram but you can have as many detailed diagrams as you like in a system as I told you that you have to label your data flows and one thing is that whatever data flows you show you will never show external entity to external entity in fact normally you generate data from an external entity it goes into some process and if you transfer the data to an external entity or if you want you can move it in a data store it is possible that you can transfer the data from one process directly to another without sending it to any other external entity this is a general rule which I have explained how to label node diagram this diagram is of a system the module we make first we identify application or applying second module we identify processing of the applications and third module we identify generating the admission list we identify these three modules first we represent application process module we represent application process module which deals with applying this module we are also showing how our processes are involved and finally this module which displays or issues and how these different modules are linked to different places and they give a complete representation and especially the number of processes note that it is 1.0, 2.0 and in level node diagram the process number is always in the same format Dear students we have shown the example of admission system I hope you have understood about level node diagram or in general about DFDs keep in mind that there is no wrong or wrong principle or criterion the rules to make DFDs the basic objective of the diagram is that it is self-explanatory and self-clearing that is the same what you understand about the system second thing is if you see any other person then you will understand the same thing that you have shown the purpose of DFD is to explain the system's workings processes in that the different phases of the development we discussed keeping a special process in mind the first phase we discussed the requirement analysis and the tool DFD we discussed the two types of diagrams context diagram we discussed that in the next lecture we will discuss the details of DFD see you in the next lecture goodbye