Hey, everyone, welcome back to part 11 of topic four in our database class. In this video, we're going to focus on super-type and sub-type entities and understand their role in entity relationship models. So, at this point, we can transition into a new type of relationship, and that is to contrast, has relationships with, is relationships. Now, generally, most of the relationships that we've worked with thus far have been has relationships. So, we can say, for example, that like an employee has a locker, right, or a locker has an employee, or a department has employees, and employee has departments, right, and employee can have employee skills, right. So, these are all has a relationships, but there are also is a relationship that we'll get to here momentarily. And this is a different sort of concept that we need to learn how to represent it in our entity relationship diagrams. So, before we do that, let's talk about the difference between super-types and sub-types. Now, these are very common in the real world, and hence, it's good for us to have the skills to be able to model them, because we may find ourselves in a situation where a company needs us to model a super-type sub-type relationship as part of our database design. So, the basic idea here is one of inheritance. So, a sub-type is a special case of another entity, which is called a super-type. So, here's an example. If you think about all of the people that are associated with a university, right, we're all people, but we play different rules. So, we might say that maybe we take all the people associated with the university, and one way to possibly divide them would be employees versus non-employees. So, we might have a group of employees, we might have a group of students, right. Just like a company could say, like, employees versus customers. Now, they're all people, they're all human beings, right. They're just special types of human beings, one belongs to one group and the other belongs to some other group. At a university, a very common way of differentiating between the different special types of people that participate in the university community would be to label some of them faculty. Those are the people who teach, right. And then there are staff. Those are support personnel, administrators, etc. They don't teach, but they are nevertheless critical to the operations of the university. And students, right. So, we wanted to, we could divide the people at universities into three different subtypes. We could say the subtypes are students, faculty and staff. And then if we wanted to, maybe we further divide, say, students into undergraduate students versus graduate students. So, we can have subtypes of subtypes. But the idea here is that this is an inheritance relationship. Just because someone is a student doesn't mean that they are not also a person. Right. It doesn't matter if you're a student or a staff member or a faculty member, you're all people. And this is what is known as an is a relationship, right. Because a student is a person. A faculty member is a person, a staff member is a person. Or in more of a corporate context, an employee is a person, a customer is a person. Or maybe some other entity, like maybe it's an organization that's a customer, right. But the notion here is hopefully clear. And what we can do with these types of is a relationships is we can capture general information that is of interest to us about the supertypes. And then we can capture additional information about each subtype that is specific to each of those subtypes. So, for example, if I'm talking about people, regardless of whether you are a student or a faculty member or a staff member at the university, there are going to be some common things that I want to record about you in the database, like your campus wide ID, right. That applies to everybody regardless of whether you're a faculty member or a staff member or a student. And then the name, right. The your mailing address, probably your email address telephone, maybe your date of birth. Right. So there are these are common attributes that we want to know about all people that are a part of our university community, regardless of whether they are faculty or staff or students. Because they're common set of factors are common set of attributes and we record those as attributes for the super type. Now, and this is the key thing is depending upon which subtype a person might we're going to record additional information about them that is specific to that subtype. So let's say, for example, that you are a student. Well, if you're a student, we're probably going to want to record information about your academic performance and your goal. So students are going to have things like a major or a concentration. They'll have grades, courses that they've taken, the grades that they've earned and each of those courses and so on. And these are things that are specific to the student role. So I'm a faculty member. I'm probably not going to have a major or I'm not going to have grades or classes that I've taken at this current university. So we're not recording that information about faculty. We'll probably record additional information about faculty that is not relevant to students. What is their office location? What is their office phone number? When were they hired? What is their salary? Like these are all things that we want to record like faculty members that we would not want to record about students. So based on the specific subtype, we're going to record additional pieces of information, but we're going to have a common set of values or attributes that we record for our super types. Now one additional thing and then I'll show you some examples is that these subtypes can be either exclusive or inclusive. And the difference here is simply how many subtypes is a super type allowed to be simultaneously. So for example, if I run my university in such a way that my staff members cannot be students as well, like you must either be an employee of the university or a student. Well, in that case, it would be an exclusive super type subtype relationship because each person can only be one subtype at any given time. I cannot simultaneously be of two subtypes, but if it's possible, a safer example for someone to be both a faculty member and a student simultaneously at the university, then it's going to be an inclusive super type subtype relationship because it means that that person, remember in this example, the person is the super type, just the generic concept, that person could simultaneously be playing two roles at the university. They could be a student and a faculty member and that means they could simultaneously be members of two different subtypes or maybe even more. So this is an inclusive super type subtype relationship. All right, so I've talked about this in text form. Let's see some examples. This is easiest to understand with some lovely pictures here. All right, so what we have here then are two super types, which you will notice are strong entities. This is a student super type here and a student super type here and we're going to model two different relationships. The first one, which is shown over here on the left is an exclusive super type subtype relationship. So we can see that in all of these that we have this sort of circle symbol, right, that to then branches off into the various subtypes, but this circle symbol has an X in it. And if you can trace that with the one over here, you'll see this one does not have an X. So the way that I remember this and hopefully it works for you is X is exclusive. It doesn't really work out from a spelling perspective, but you can think of it. I don't know, maybe like this like exclusive, right. I think the big X. So that is our visual symbol that this is an exclusive super type subtype relationship, meaning that each person in this case, each student can only be one of the subtypes at any moment in time. So in this case, our subtypes are undergraduate versus graduate and it makes sense in a university scenario to say that a student must be either an undergraduate student or a graduate student. We cannot allow the same student to simultaneously be in both of those roles. So what this means from a data perspective is we're going to have this undergraduate table down here, right, this is a subtype table and we're going to have this graduate table and you can see that we use the student ID, which is the primary key from the parent table as the identifier. So in each of these cases, the student ID is serving both as the primary key of the subtype table and also as a foreign key link back to its parent table. However, because this is an exclusive super type subtype relationship, we need to know to which subtype does each student belong. And that is determined using this attribute right here. So we've created this attribute, you can see it's a simple Boolean or bit attribute, just going to hold like a true or false, yes or no one or zero indicating whether a particular student in the student table is a graduate student or not. If the value of that attribute is true, then we're going to follow this path and we can get additional information about that student by looking in the graduate student table. So this attribute is called a subtype discriminator because it tells us which of these paths we should follow. Only applies and is only used in scenarios where it is an exclusive super type subtype relationship, because we're only going to follow a single path to just one subtype. Now remember the idea here is it's just like inheritance. So if you studied object oriented program this idea of inheritance, you can have an abstract class, in this case an abstract concept like a student and then you're going to have specific types of students like undergraduate versus graduate, but the point here is to note that an undergraduate student is a student, a graduate student is a student. So if I'm looking at, I don't know, maybe student number seven and let's say that that student is a graduate student. And I said what attributes are associated with student number seven, the answer is student ID, last name first name and whether they're a graduate student as well as these additional attributes down here, which are only of interest. So to us as a university, if a student is a graduate student, right, like graduate students in business commonly take the GMAT. So if somebody is not a graduate student, but instead as an undergraduate student, I probably don't need to record their score on the GMAT because they're not a graduate student yet. A graduate student is a student if I ask you something like, you know, what attributes are associated with graduate students it's all of these, right, because our specific type of student graduate student inherits all of the attributes of this super type as well. Same with undergraduate here we have a set of specific attributes that apply only to undergraduate students, but we have this common set of attributes. that applies to all students. So if I said, you know, what are an undergraduate students attributes it's student ID last name first name, whether or not they're a graduate student and they're high school GPA. And their score on the SAT, which you know is an entrance exam for admission to the university as an undergraduate. So that is an exclusive relationship on this side over here on the right, we have an inclusive super type subtype relationship. So we still have our student table up here with student ID first name and last name note that there is no subtype discriminator attribute. So this is not used in the case of inclusive relationships. We do not have an X inside this circle. So it's not X, inclusive, that's how you can remember that X for X, this is an inclusive super type subtype relationship. In this case the subtypes are a tables about clubs. And you'll note again, we use a single identifier, a single key student ID, which is the primary key of the super type down here in the subtypes. It serves both as a primary key and a foreign key link back to the super type, the student super type. The idea here is this is an inclusive subtype. So each student potentially could be added to one or both of these tables. So if I'm a student, I can simultaneously be a member of the hiking club and the sailing club. I don't have to choose one or the other. Like I don't have to be an undergraduate or a graduate student. It's like you could be both simultaneously. In this case, I can simultaneously be a member of multiple student clubs. So this is an inclusive super type subtype relationship. So again, the difference is just, am I allowed, whatever the super type is, is an instance of that allowed to participate in more than one subtype simultaneously. Like we see here, if the answer is yes, it's an inclusive relationship. If the answer is no, we see over here it is exclusive, meaning that each instance of the super type student must be only one of the subtypes at any moment in time.