Lecture: 03- Intro to ER Model| Database Management & Warehousing | GATE Data Science & AI #gate2024

This lecture introduces the Entity-Relationship (ER) model, a crucial database design concept. It covers ER model components (entities, relationships, attributes), attribute types (key, composite, derived, multi-valued), relationship types (one-to-one, one-to-many, many-to-one, many-to-many), and ER diagram symbols. The lecture also distinguishes between strong and weak entities and explains total and partial participation in relationships. https://www.geeksforgeeks.org/introduction-of-er-model/ Then we have a different components of ER model. Okay, so apart from entity and relationship, we have one more component that is attributes. Okay, we have entity, we have entity, and then we have this relationships. Attributes are associated with the entity and attributes are associated with the relationships. Also, okay, we'll discuss in the further lectures, but attributes are basically a properties of particular entity, Okay, that you can say, Okay, so generally there are two types of entity. First one is strong entity, and second one is weak entity. right? So what is strong entity? What is we can read, we will discuss. Now attribute first one is key attribute. So what is key will also see that what? Just note down this key attributes. Then we have for shield, key attributes. Then we have multi-valued attribute. Okay, Then we have derived attribute, we have compose it attribute. Okay. so these are the different types of attributes, right? So K, attribute, passage, attribute, multiple attribute derived attribute, composite attribute. Okay. we will discuss this one by one. Then we have different types of relationships. So generally we call this as a mapping cardinality. So relationship exists between two entities. Okay. So like first mapping that cardinality is one to one, it is one to one relationship. Then we have one too many relationship. Then we have many to one relationship. And then we have many too many relationship. Okay. so that's the overall uh, component for ER model. So we have different entities, we have attribute associated with it. We have relationships also. So there are different types of relationship or all We can also, there's mapping cardinalities is also there, right? And then we have different types of attributes. Okay, so we'll discuss this one by one. Okay. So now what is an entity? So entity is a thing or object in the real world that is distinguishable from all other objects, right? Say, for example, I can say student student is an entity, or any person is an entity, like employees, and also an entity, or you can say car, that car is also an entry. So now what is an entity? So entity is a thing or object in the real world that is distinguishable from all other objects, right? Say, for example, I can say student student is an entity, or any person is an entity, like employees, and also an entity, or you can say car, that car is also an entry. So any entities, anything on object in the real world, and is distinguishable from all other objects, and indeed is represented by a state of attributes. Right? So, so here you can see an entity is represented by a set of attributes. So student is having some attributes, like, for example, a student is having some name, they are having age, right? And they are having roll number. also, right? Similarly, person is having name, age, right, then hits hide, wait, click, these are the attributes of a person. Carl is having engine number, right? Then we have model, then we have company name. These are different attribute associated with the entity car. Now name is associated with the entity person, right? So these are the different types, different entities, right? In the real world. Now, what is an entity set here? Any entity set is a set of entities of same type. That's just the same property if we considered a student. So if you consider the state of students, say for example, S1 is to S3. These are the three different students, but they are sharing the same properties like name, age, role number. So then we can say students is a entity set. Okay. now what is relationship? So relationship is associated among the several entities. Okay. Say for example, we have one more entities course. The course is having some different attributes. For example, we have course IDs there. Then we have credit. Is there right Name is also there, these are different attributes of a course. And now what is the relationship is? the relationship is associated among the several entities. Suppose we have this student. And if we have course entity, the relationship among the student and course entity is, can be represented by a diamond shape. Right? So generally entity is represented by a rectangular shape, and a relationship is represent via this time and shape. Okay, so we can say, student, both we have two entities and the relationship, Say we can give any news, say, uh, enroll, right? Enrollment is a particular relationship associated with the these two entities. So if you look at the relationship set in the state of relationship of the same types, okay, similar to entity set. So I hope you understand what is entity, what is attribute, and what is relationship? This is a common terminologist using AR diagram. So please remember this. So, we have discussed what is entity, what is entity set, then the relationships and attributes. Now there are different PR diagram symbols, okay, used in a particular year model. So say for example this is roll number, right? And we can attributes represent that this attribute belong to this particular entity set. Similarly H is an attribute right? Similarly name is also an attribute. Okay, These are called single value attribute name, H, roll number. Also, here we can underline this whole number because roll number is a key here. So, what is key? What is the concept of key here? So key is basically attribute for a state of attribute. It is possible that a roll number and age can be a possible combination of a key, one name, and age also can be a possible combination of a T. So it depends on the case study, Okay, let us consider is a roll number here, in this case, because whatever the key will be, key will be having unique constraint. So key must be unique in our database. So all the students should have a different row number, right? So it should be unique. And also, it should not be a, not null, so it should not be a null value here. So every student should have a particular role number, right? That is what our database will ensure. So, what is key? So key will uniquely uniquely identify each row. Okay, So, for example, just consider, we have a student, we have his role number as an attribute. Now, this attribute is a key here, because it will uniquely identify each row in a table. So, if I know, if I know the rule number, I can use all the information of that particular student, because that is unique, right? So it can be a attribute or state of attribute, also, right, attribute or set of attributes. So, don't worry, we are going to have separate video on the video on the key. So, it's a very important concept we will discuss in the, for the lectures. So we will be accepted lectures on the key itself. So, as of now, just remember, key can be attribute or state of attribute. and it should be a unique. and so it shows that it should uniquely identify each row in attribute. Okay, so this is an entity represent by a rectangle, and the attributes are represented by an ellipse. So this underline will give us attribute, right? Now, what is composite attribute here? We have discussed composite. right? So this is basically a key attribute, right? Key and tribute, right? And this age name is a single valued attribute. Okay. You can see the single attribute. Now what is composite attribute? You will see, so composite attribute is represent like this, right? So this is we have when parent attribute, then we have this, its component, right? Like this, different types of components like this. Okay. Say for example, we have address, right? So address is a composite attribute having different components like we have pin code, right? We have street number, right? We have landmark. Also, right? Or you can say, for example, our full name, right? Full name can be also considered as a composite attribute. If they are having three components, So product, three component, that is first name, then we have middle name, then we have our last and that is surname, right? That is surname. So if we are storing first name, middle name, last name in three different column or three different attribute, we can say. Then we can say this whole name is a composite attribute, right? If or if you are just storing our problem in a single, a particular row, then it is just our single attribute, right? It's a single name. That's it. It depends on case to case study. So what are the configuration of a database? It depends on that. But address can be have multiple components like Pin code Street number, landmark. So we have two original three different attributes. So these are is the example of composite attribute. Okay. Now we have seen derived attribute also, right? So let's see what is derived attribute. So what is the derived attribute here? Attribute whose value is derived attribute whose value, or you can see a field is derived on derived from other attribute, right? Other entry computer, for example. Okay, so these are attributes is represented by an ellipse, dotted ellipse. So for example, age is a derived attribute. Okay. Suppose we have a student entity here represent by an rectangle, And this is our attribute. Okay. Now this age, attributes is derived from a stored attribute. That is date of birth, right? A student is having data stored attribute and 8 is a derived attribute, right? So we are not creating a separate that's separate for age at tables. We are designing this attribute from a date of birth, right? So that's what the left attribute is. So this attribute is depend on the date of birth attribute. that's why the attribute and represent by a particular dotted ellipse. Okay, now we have multi-value attribute, right? multi-malued attribute. Suppose it is represented by a double ellipse. Okay, this is double ellipse consideration, this double ellipse, this multi-valued attribute, for example, phone number. right? Now student or any employee can have multiple phone number. So suppose we have one column here, phone number in our database, right? So what is multiple attribute will solidif material attribute if we are storing or do the to both the row numbers in a particular cell only only in a one set, you know, only in a world field track is okay. suppose again we have this whole uh, number Again, we are having this one more number, but we are showing both the four number in a particular seven cell only in a single field. So that's what it is. Multi-valued attribute. Okay. So this is not recommended in a database because we can't update this phone number, right? So we'll discuss this in the when we are going to discuss about the normalization form. it's of not just note down that multiple attributes indicate by number ellipse. So this is the example like Sudan can have multiple phone numbers, right? It is stored in a single field. So that's what we have discussed. So initially we are discussed about composite attribute, you have attribute module attribute, then we have roll number is a key attribute, right? Yeah. So I think I'll discuss about entity relationship and different types of attributes. Okay and we also see what are the different components of a error diagram and this is a different symbol so please note it down. It is very important in this slide. Okay, now we'll discuss about the mapping constraints. Okay here you can see mapping or we can also call it as cardinality. Okay mapping cardinality both are the same thing. Now many too many relationship. So here instructor can have many students and student can have many instructors. Okay okay for example we have two entity language course and design is student, right? s1 then s2, then s3 Yes, instructor one instructor two instructor 3 instructor 4 the instructor is having multiple students visiting to s1 also s2 also now i3 sticking to s1. Also s2. Also similarly s2 is uh for s2 both i3 and i1 is are the instructors which is many to many relationships So on both the side, they should have at least two, uh, mapping, right? that's that is many too many relationships and student can have also many instructors and is represented by a single line only. Okay, there is no arrow in between. Now if we see about this one to many relationship, this arrow indicates one. Okay, so here on this on this, we can say say n and this is one here, Both the side we can say n and okay, it is n is to n and here 1 is 2. And right, so arrow indicates one, one here is that most one, okay, not exact one, not at least one, it is at most one with zero is also possible. Same example, let us take, uh, instructor one instructor to instructor three. And here also we take example like student one, student, two. Okay, so now in this case, what will happen, Instructor can have many students. So i one can have s1, i1 can have s2 also but it's not the reverse. Like s2 cannot have i2 or s1 cannot have i2 because it will become many too many relationship there. Now from this side, from student side, it is mainly to one. But when we read from a instructor said it is one to main relationship. Okay, so so in one, two menu, what happens like where each entity can be related to more than when entered in a particular relationship right? Now if we have this s3 also then i2 cantt s3, like this, right? But as to as to don't can have can't have instructor and so on or like i2 already this, uh, point to my relationship similarly, uh, many to our relationship right On this side we have instructor say i1 i2 i3 you say i put in this set we have student say s1 s2 s3 thanks so it's menu to one right so many instructors i2 is teaching s1 also and i2 i1 is using s1 and i2 is teaching s1 also similarly i3 is teaching 2 s2 and i4 is switching to s3 right so that's what a minute to one relationship right so many instructors are taking a particular one student right so s1 is now from s con student side it is one to many relationship right so for a particular student as soon as having many instructors same for s2 right this is what many to one relationship now one two relationship is simple this arrow is indicator one it's at most one here also one so it is one is to one here it is uh n is two one right similarly instructor can teach at most one student. And uh, that student can have at most one instructor, right? Say for example, say for example, again we are representing i1, i2, i3 as the instructors and we have s1, s2, s3. So each students are having only one instructor and instructors are only one students. That's why it is at most one. So i3, i4 can be an iphone may not be associated with any student. So that is also positive because it's at most one. that is what one to one relationship is. Now one more thing that about total and partial participation. So whenever there is a single line, you can see this partial participation. And when there is a double, then it will indicate total participation, right? So what is partial participation? What is total participation here? Suppose let us take the same example. Okay, we have this instructor entity saying i1, i2, i3 and we have student also here, right? That is s1, s2, s3. Okay, so what is total participation here like each entity in a set in a set, particularly because we are having some student side. So each entity in this particular set must participate in the relationship, right? So for example in the previous case you could tell s4 is don't have any an instructor that is also fine. Like here also s4, that is also fine if you don't have any instructions. But here every student must have one instructor. So this one should have or s3i2 or s3, uh, i3, anything can be possible. Every student will have some instructor. That is what total participation is. So it, so each entity in an entity set must participate in the relationship, right? So that's about the total participation. And what is passion participation the entity in the entity set. Like, for example, our instructors may or may not participate in the relationship. For example, we have one more instructor, say iphone and iphone is not associated with any student. So that is partial participation. Here you can say other partial participation. But here you can see we have indeed double length, double line indicates the total participation is every student. Every student must participate must have instructor, right? Must participate in this relationship. So must have advisor as an instructor, right? So that this is what our total and partial participation is. So I think, uh, let me know if you have any doubt up till now. Uh, you can just comment below. Uh, I will reply to that. So that is about the mapping constraint, right? So we have discussed what is entity, what is attribute? What is relationship, different types of attributes here that comes in both, and relationship. So first one is strong entity, then we have weak entity, right? So what is strong entity, basically an entity? So strong ant is generally represent by a rectangle, only, right? And weak entities represent by a double rectangle. Okay, Like this. Okay, so the strong entity here, so like it is the type of entity that has a key entry route. So this strong entity will have a key attribute. Okay, so this attribute is enough to uniquely identify each hero in this particular entity, right? This is, this is a key attribute. This is a key attribute of this particular top entity. So this strong entity does not depend on any other entity. So one, one main feature is that a strong entity does not depend s on other entity for their existence, right? So student or any employee can exist. And that is strong empty, because it can, it can, it is independent of any other entity. So we'll discuss one example also. now what is weak entity here? So we continue this associated with a partial key that is, uh, ellipse indicated by a dotted line. Now, this parachute case attribute is used to uniquely identify each hero in a particular weak entity, right? Say, for example, let us take one example. uh, we have, so employee, right, we have employee, and this is the entity, right? And once we go to the, any company, join as a join as an employee. So company will give us the insurance, right? insurance, right? When we are applying for insurance, we need to do nominee. Also, right? nominee Also, because while leading insurance form, we need to give nominee also, right. So normally can also be a part of one entity, right? So normally, what I am saying it is a entity, right? And we have a relationship between this is insurance. [music,] policy. Okay. Now, what do you see this normally entity exists only when there is a employee, right? Only when employee purchase and issue, then only this, how many will come, right? Because somebody cannot exist by a phone, It is not possible that we are having a normally, but we don't have a corresponding issues for insurance employee for that, right? So this insurance company will exists only when someone take the insurance, right? So this is one weak entity set is this is a weekend is the same. So normally is a weekend, is it because it's completely depend on this particular employee. If employer didn't purchase this insurance policy, then how will normally become that's, that is what each entity that is what weak entity cities, and it is represented by total participation, Because what is total participation? If you remember, every entity associated with this particular weak entity must have a association with other entities. So like every now, many must have a particular employee, right? Every insurance policy it must have, right? So this is what will indicate that total participation. Now, what is weak relationship with identifying relationship, we also discuss something like peak identifying relationship, right? That is unique. identifying identifying relationship, right? We have discussed this and it's represent by double diamond shape, right? Now, we identify relationship exists only when there is weak entity. Okay, now we speak identifying relationship is associated with the weak entity only. So here we are having the weak entity, which was this normally, because it cannot exist without employer without insurance policy. So this insurance policy will also become weak identifying relationship, identify relationship. And we generally indicated by a double ellipse, Sorry, double diamond shape. So this is what weak entity is. This is what we identify relationship. and this is a strong entity, the total participation, there is a partial participation, because employee, there may be employer who, who don't have any insurance policy, right? But if they are having the insurance policy, if they are having a nominee also, right, so I hope you understand the concept of strong interview.