 Assalamu alaikum dear students, today we are going to do lecture number 7 of the database management system course. In the lecture number 6, we concluded our discussion on the data flow diagrams. There finally we discussed the detailed DFT and how they are linked with the higher level diagrams. After that, we also discussed the second phase of the database design process where we discussed the design phase. We discussed why do we have different types of database design. We also discussed the data models. What is the link between data model, database management system and the database design? Why to have different data models and what are the different types of data models? At the end of our previous lecture, we were about to discuss the ER data model that is basically a conceptual database, conceptual data model. Today we will start our discussion on the ER data model. So let us start our today's lecture. As we have already discussed in the previous lecture that ER data model is a semantic data model. That is, it is a data model that provides you more constructs, more tools to fit in in different situations. So the advantage is when you draw, when you make a design using the ER data model, it is more explanatory. It is clearer to understand, it portrays, it models more information into the design. As you can see from here the definition, a semantic data model used for the graphical representation of the conceptual database design. As I have told you before that your graphical symbols, since they have very precise meaning, clear meaning, so there is no conflict what they are representing. Second thing is the objective has always been that in this type of tools, we have fewer number of symbols. So with less symbols, we can give more meaning, more work, this objective is our conceptual data model. Let us move forward. What are the major components of the ER data model? It is the entities, the number of attributes, and then the relationships. These are the major components of the model, with which we make different designs. But there are also other things that are added in it, these are all different types that all of them together make their own meaning, which we will study in the next lectures and in this lecture. Dear students, Entity is such a term that I would like you to have clear concepts in your mind. This is also among the basics about which I always expect you to be clear about those things. Entity terms are basically three different things on different occasions that are used to give meaning to them. Now these three meanings should come to you, I will explain them to you. After that, it is better that whenever you use a term, you use the same term that you would like to use. But first of all, if you are using an entity term or you are using an entity term, then you should have in your mind that at this moment in this particular scenario or this particular context, which particular meaning of the entity you are using? What are those three different meanings that we mean by the term entity? Let us see them. First one is entity type. Second is entity instance. And third one is entity set. One term is entity. These three meanings are used in different occasions and different scenarios. And to understand a scenario, you need to know that at this particular moment, what is the meaning of entities? But otherwise, I would recommend that at every moment, you should use a special term, i.e. whatever you mean by these three meanings. But this does not mean that if you only use the term entity, it will be wrong for any of these meanings. By the way, let me also tell you that when we use the term entity, that is generally meaning the first two meanings. Usually, that means entity type and entity instance. Now, it depends on you whether you see the figures or the context. What is the meaning of that? In this course, when we use the term entity, that means entity type. If we take the first two meanings in the books or in different places, let us see what each of them means. First of all, entity type. Entity type is a label or name assigned to items or objects that exist in an environment and that have similar properties. It could be person, place, event or even concept. Dear students, this is a technical work. With an experience, you will be aware of how do we identify entity types? How do we declare the entity types? When you observe any system, all the things that exist in any place have properties of each of them. You can easily identify them. For example, if you see any event in your bedroom, you will see different things there. For example, there is a bed, there can be a chair, there can be a maze, there can be a fan in your room, there can be a bulb in your room, there can be a TV in your room or a CD player in your room. For example, there is a picture in your room. There are different things that can be in any room or bedroom. Similarly, you can take any place or any system. Now, if you talk about your bedroom, there are properties of each of the different things. For example, if you look at the bed, it is made of a special material. It has a special purpose. You look at the chair, its material is its own, its structure is its own, its purpose is something else. It is such a table, it is such a bulb. Each of these things has its own properties. The properties of each of these things are common. They are the same. You can describe the properties of each of these things using the same properties. So, most tentatively, most probably, those things would be belonging to the same entity type. For example, if we talk about the bedroom, some of the things that I have mentioned, which can be in your bedroom, some of them you can say that these are the furniture. Similarly, some of the things you can say are electronic or electrical equipment. So, when you observe any system, when you analyze it, the things that you see there are common properties. Based on those common properties, you can say that these things fall in some category, we give them this label. These are the things, because they have their own properties, so we fall them in this category. And look here, one thing to keep in mind is that the property and the value of property, they are two different things. For example, when we say that we said bed and chair. Now, the common thing is that they are made of wood, for one purpose, for seating, or for bedding. But that chair is a bed. So, although both belong to the furniture, we can say that they are furniture, but they are different. If I give you another example, if you look at a classroom, in any classroom, if you look at the things that you see, in that, obviously, you can see a teacher, there will be a rostrum, there will be a whiteboard, there will be a blackboard, there will be chairs, there will be students. So, when you analyze these things, when you observe them, all the things that are present there, if you look at their properties, based on those properties, they relate to each other, or they can say that they feel the same way. So, if the human beings here, the people here, you can say that these are the persons. Then the thing is that the other things, they could be the electrical equipment, they could be the furniture, they could be the fixtures. Similarly, you can say that there are teaching aids, there is a whiteboard, there is a marker, there is multimedia, there is a computer lying there. So, when you analyze any environment like this, and from there, you make a different group based on the common properties of things, and you give that group a label. That label is basically a name of an entity, or you can say that it is an entity type. You can identify an entity type on another basis, that when your users tell you their requirements, you can identify those different entity types. Why? Because those requirements are something that is common in them. In the last lecture, we talked about the cross-reference matrix. In that, I told you that the different reports, the documents, the transactions, the different data items, the attributes that you need, you can make a matrix, a table. Similarly, the things that come on a single report, the things that are involved in a transaction, the things that are involved in a document, most likely, not necessarily, but most likely, they can be declared, they can be identified as a single entity type. Or, it is possible that they are two different entity types, but they are somehow linked with each other. This will be clear to you, but at least you have noticed that the cross-reference table that we made, the cross-reference matrix, you can use it here as well, which are the things that you can identify as entity type. Whether you make a cross-reference matrix, or not, even then, you can identify the entity types from an environment. Let's move forward. What is special about them is that based on these properties, you can differentiate them from the other entity types in that environment. For example, you will not call a bulb a furniture, why not? Because the properties of furniture that you identify, the properties associated with furniture, they are not in the bulb. You won't declare a chair as an electrical equipment, unless it is an electrical chair, unless it is an electrical chair, but by the way, a sitting chair that you won't declare is it as an electrical equipment. You will not call the air-condition as something in which you drink water. You will not declare it as a utensil, you will not call it as a vessel. This means that when you find an entity type, when you declare an entity type, you will be able to differentiate it from the other entity type, based on their properties. Entity types are identified through a process called abstraction. Abstraction is a term that is defined in different places. But what we define in this scenario is that it is an thinking process. It is an intellectual process through which you separate and identify the common properties of things. Again, the abstraction is a thinking process, an analysis process in which you identify the common properties of the things that exist within an environment. So, as I told you earlier, if you have a tool whether it is a dictionary or a cross reference matrix that can help you, that can work as an aid and the second is your abstraction process. The abstraction process relates to your thinking ability and the second is experience. Initially, it is possible that you have a problem identifying an entity type or a proper entity type but as you experience it in the database development process it is possible that you can do it in a better or easier way. Another factor is that if you have got experience on working on the same type of system once, twice, first you have worked on the banking system twice, three times. So, this will never be the same design or the same process but your abstraction process that will become easier because you have already worked on that kind of environment or you have already identified the entity types of that environment. Let us move on. A very important point. The entity or the entity type in the ER data model and the external entity in the DFD, they are different. This thing is confusing for many students because in both of them there is a lot of similarity but the biggest difference is that the external entity in DFD you define anything that generates data to the system or accepts data from the system that is an external entity or entity in the DFD and here we call the entity type or entity that is a distinguishable thing in an environment based on its properties that is the entity type or entity. Another thing that is confusing is that the symbols are the same both are represented by the rectangles but one thing to note is that their definition is that in DFD anything that receives data or accepts data from the system is an external entity. One thing to note is that there is a single entity that exists in that environment. For example let us talk about the bank. The manager he is a single person he has a single existence in that environment but if that banking system accepts data or produces data obviously he does then he will be represented there. If we talk about a library system then the librarian is a single thing similarly if we talk about the university then the VC the registrar he has a single existence he is a single person in that environment. But if he is producing or receiving data then he will be represented in DFD. If we talk about ER data model or when you are designing a database using ER data model or your conceptual database design then you will not show those entities their single instance. This means if you are developing a banking system you won't generally represent the manager. If you are developing a library system you won't represent the librarian. If you are representing a university system you won't show the type the registrar you won't show the entity type because the instance where the data will be a single record. The representation of such things using your ER data model you won't represent the conceptual data design whereas these things will be represented in your DFD The DFD provides you a basis so that you can identify the entity types. It is absolutely possible that the many entities of your DFD external entities you can take your conceptual database design using the ER data model but it is not necessary that all the external entities will come in your ER database design. Now let's see what is the entity instance and entity set. We have discussed the entity type You define entity instance as a particular object belonging to a particular entity type For example we have identified an entity type employee because what we have seen there are many common properties based on that there is an entity type which is an employee Now any entity related to that entity will be called an instance For example An entity instance Muhammad Sharif all the data is an instance of the entity type The entity type defined a category based on the properties and after that any property that keeps those properties has no value For example the entity type has a name the entity type has a date of joining has a qualification has a sex has a date of birth All the properties we have identified and based on that we have an entity type employee All the employees have their own name their date of birth their qualification and date of joining usually that is an instance of the entity type employee entity set all those instances which belong to a particular entity type if we see them jointly that will declare as an entity set This means that in a factory all the employees working jointly we will say this is an entity set whether it is 1, 2, 10 or 1,000 we will say entity set We have explained what are the different meanings in which we use the term entity entity type, entity instance and entity set I hope these terms are not clear to you I will repeat whenever you use the term it is better to use the precise term but if you use the term entity then you should be clear what is your meaning Let's move on Types of entity types You have said what is entity type that we have given a name based on properties but in the ER data model there are different types Let's see what they are Broadly We divide them into two categories One is Strong or Independent or Regular entity types and the other is Weak or Dependent entity types What is the difference between these two Let's move on to the next slide We are first going to discuss the principles of common life Here you have a little more technical An entity type whose instance cannot exist in a system as long as it is not written by any other entity type We will say it is a weak entity type or it is a dependent entity type This means the instances of that entity type cannot exist independently In addition such entity types whose instances can exist independently without being linked to the instance of any other entity type we will say Strong entity type or Independent or Regular entity type I will give you some examples of Strong and Weak entity types For example if we see we have a factory we have employees The rule of the factory is that the dependents of any employee who are their parents who depend on them are also storing their data Obviously if we say we want to store the data of any employee's dependents they cannot exist without the existence of employee as long as you do not link the dependent with any employee how can the dependents come if you can describe it that the factory the business the link with any employee that is through the employee If there is an employee then it will be dependent There is no question that there are dependents but who is dependent? The business would have no concern with those dependents This means that you cannot put the instances you cannot think of the instances of the weak entity type as long as they cannot be linked they cannot be associated with any strong entity type like I have given you an example of dependents and employees It can be an employee type Employees can exist independently If there are no dependents of any employee he is all alone or his parents are not dependent we have employees but they are not dependent but it cannot be an employee It means that dependents cannot exist until they are linked This is an example of the weak entity type and the strong entity type Another technical thing which is more clear that the strong entity types have their own identity that is you can identify them There are some properties that you can identify but normally the weak entity types do not have their own identity and to identify them you have to find you have to relate it with the identification of the strong entity type like we have an example of dependents and employees that if you want to identify a particular dependent then you have to link it for that the employee that is dependent you have to link it with their identity This is also a difference between the strong entity type and the weak entity type These are the naming conventions for the entity types and mind it these are just recommendations these are the rules these are the steps that normally professionals follow but this is not necessary for you for example it is said that for entity types use singular nouns like student, class registration or employee and mind it that you do not have to use the names according to your own needs but you have to see the convention at the organization what is being used there what customers call it the common term used in the organization is the client it is good to say you can call the client a customer but the recommendation is to use the same terminology that is used in the organization what will be the benefit of it that when you are discussing the design, the diagram or the general scenario you will use the same terminology so the user will quickly pick it and easily understand it if you change the terminology then it is possible to relate the user it is recommended that the name of the entity type is written in the capital letter sometimes you will see that it is written in the mix or in the lore but it is recommended that it is written in the capital sometimes you can use abbreviations that if the name of an entity type is very big if you are making a diagram and it is not fitting then you can use it in abbreviations but one more thing that in this you have to be consistent if you do the first thing you make the employee EMP or you make the student STD or you make the registration REG then you use the same terminology in every design so you can use something else and something else so if you get different spellings it is possible that they become different things from it although you meant one thing but they will become two different things till now you had seen what are the strong entity types and what are the weak entity types now since we have talked about that your ER data model is a graphical representation it consists of the symbols now what you can see on the screen these are two different symbols that you use for weak entity type the difference is the strong entity type you use a solid line and the weak entity type has a solid line but it has a double line so the weakness to give it more power you close it in two lines in the middle we can see the name here you will write the name of the entity type for example as you can see employee and dependents the line between them is basically linked now we have not drawn the line correctly we will read the rules of the drawing later but for now consider that this diagram shows that the employee is linked with the dependent one thing that the employee is a strong entity type and the dependent is a weak entity type and this is the beauty that you have a semantic data model with some symbols you have given a lot of meaning to describe that you need a paragraph more explanation for example you have books but there are multiple copies so in that you want to store the data separately about the book and the copies separately why do we do this? maybe you do not understand but think as you can see the figure shows that the book is an independent entity type and the book copy can be one or ten or twenty the question of the existence of the copy of the book is not there so in the same way the copy entity type is a weak entity type and the book is a strong entity type let us go ahead attribute is the other construct that is provided in the ER data model attribute basically shows the property we have already talked that when you identify an entity type that you identify based on the properties of the entity type the properties of the entity type you represent them due to the attribute that is the term which is used in the database world especially in the ER data model language the attribute means the property associated with a particular entity type for example if we say we have an entity type the property of the entity type is its name in the data model language we will say the entity type employee has got an attribute name we will say the property of an employee we will say the entity type employee has got an attribute named date of birth and this can be another example one more thing is you have defined the entity type then you will associate the different attributes associated with the entity type for example now all the instances all the instances will have that attribute applicable when you say that the employee has a name, a father name, an address date of birth, a sex, a baby date a joining date, an experience all these are attributes of the entity type employee when you define the entity type after that all the instances associated with the entity type will be associated the difference is that in the case of instances these attributes will have values meaning what you have identified the entity type, the attributes when the instance will come then all these attributes will have value and that will be the entire instance domain of an attribute before that when we identify the attribute when we define it the entity type has a name the attribute name the property name name, date of birth these are basically the names of the attributes the second thing is that every attribute has a domain what does it mean by domain domain means set of possible values for an attribute when you have identified an entity type after that when you take the instance of that entity type then the value of every instance in that particular attribute where will that value come from what set of values where possible an instance will take the value of that attribute this is a very useful thing this is a sort of check on the value of the attribute for example age is an attribute now the question is if we have 10,000 employees each of them has an attribute of age where will that value come from what set of values where this age value can come from tell me can you think that XYZ is a valid age value no XYZ does not mean it is a valid age value it can be but otherwise normally XYZ or ABC or star-dollar these are not the valid values for the age attribute domains also mean after identifying the attribute after defining the attribute you associate a domain with it when the domain is associated on that attribute then it becomes clear that the value of this attribute can come from any of the possible values for example what is the value of this attribute let us say aslam hussain aslam hussain is not a proper value for the salary obviously the value of this attribute can be a figure this attribute its value should come from the numbers and the domain gives you an additional beauty that you can check the boundary through it for example if we say that our electricity or gas bill how much is our bill in a month the first thing we say is that it will be a number but is it possible that someone's bill will be minus 2000 what does minus mean gas also means that wabda is giving us money that you have used our business money no it means that the value on the minus of the bill is not a legal value then you associate the domain with another check with the domain that it cannot be negative it will always be positive so that we pay wabda that we have used our business money when you say that we have an entry type its instances are called entity set so all instances one attribute when you define the domain the value of that attribute will be assigned to all instances from the same domain that means if you say that the age will be the number then after that you add thousands of lakhs of instances against that entity set all the attributes when your attribute is age the value of that attribute will come from the same domain the domain you have defined with that attribute that means when you have an entity or an attribute you have to be careful you should think what is possible value of this attribute this can also cause a serious problem for example if you say that you have an attribute we talk about the value of that attribute or the salary you have said that it can increase from 4 digits and cause a serious problem now you have defined you will be a bit careless you have defined but you have realized that maybe in some exceptional case the value of that attribute can increase from 4 digits so when that situation comes that entity type that value cannot be accepted there can be two cases one case is that it will give you an error if you give an error then it is a very good thing because you will solve it but the most dangerous thing is that it will not give you an error message and it will do an unpredictable activity which may not direct you and this is very dangerous in a working environment and it cannot detect because it can be detected at a very serious place when it has done a good damage so I hope that you have realized the importance of the domain Dear students the slide you are seeing you can see there are different types of attributes that we use in the ER data model the first category is Single vs Composite this means when you design your database then some attributes value as a single thing as a single value which means their value is on one thing on a single thing while some attributes are like which are made from other attributes we call them composite attributes for example like age birth these are single attributes because there is only one value but the address in the address we have an attribute which includes other attributes for example street number house number sector, city, province and it can contribute so all these things can form a single attribute called the address these attributes that jointly make the address attribute they will be called the components of this attribute the address or the address this will be called a composite attribute here again I will tell you that there is no hard and fast rule which thing you will model as a single attribute depends upon your system what is the requirement there as you will remember when we did an example in which we had a user he was looking at the name as first name and last name so if you have a requirement like most of the people they see it as a breakup so even you can define the name it consists of first name like I have given you an example of address if you have a requirement like most of your application the user's requirements the address is seen as a single thing like okay what is the address of the student what is the address of the student instead of that you can model it as a composite attribute what is the need for you to make extra effort or to break up you can store it as a single string as a text in which it will say house number E2 street number 33 sector G61 Islamabad Pakistan so it will come as a single thing in this case even address that is being modeled as a single attribute but if your application is like whose requirement is that they want a different house number so in that case what you will do is you will store it as a composite attribute what is the benefit of that the benefit is that whatever applications you want they will be present in your database design here is another thing first you have 25 applications that use the address now the thing is there are some applications that use the whole there are some applications that use different addresses now you can use the address as a single attribute or as a composite attribute you can justify both the applications so what you should do the question is what are the requirements for example there are 24 applications that use the address as a single thing so what you will do is you will do it over you will do when you analyze the address and break it up but the other 25 the other 24 you will hold the address and show it to them if you break up the address i.e. you break it up as a composite attribute then what will happen that one application is easy that you already have you can show it to them but the other 24 applications you will have to mix them and then add the address you will not have to do any labor but this is an extra activity this is some additional processing that is happening in many applications in other cases you will have to do only one application i hope you have understood what is the difference and what should we do Dear students do not get confused that in any environment there is nothing to do there is always a whole lot of options this is correct you should know that there are different ways you should know the benefit of every way then it depends on your choice depending on your thinking depending on your analysis whatever is better in an environment you should adopt it let us move on the second type of attributes is single-valued and multi-valued the difference is that sometimes your attributes have one value but some attributes have multiple values for example if you say a name aslam but but if you see there are hobbies hobbies can be multiple 1, 2, 3 4, 5, 6 if you see degrees or qualification there is a attribute in which people have multiple values some attributes are single-valued some are multiple-valued similarly stored versus derived some attributes usually your attributes are whose value you have stored but some attributes are whose values you do not store but you derive them from any other attribute for example if you associate with an anti-type date of birth and age you have stored date of birth but you did not store the value in fact whenever you access the age attribute there is a routine which will take value from age date of birth and calculate it so this is an example of stored versus derived attributes I hope you have understood right now the slide on the screen you can see there is a symbol of an anti-type you can see an oval shape this oval shape that is used to represent an attribute in the ER data model and this indicates that you will use an oval for every attribute if there are some approaches for the brevity purposes they combine different attributes but normally this is not recommended there are many attributes with an anti-type you will represent every one with an oval this oval is linked with the anti-type as you can see here we will see which symbols are used for different attributes which attributes are used this is used for the simple attributes which have a single value this is used for the composite attributes you can see as I told you in the last slide the symbol of your attribute is an oval that would be linked with an anti-type but in the case of a composite attribute the component attributes will be linked with the attribute the component attribute will be linked and this is the only case when the oval is not linked with the rectangle and oval is linked with another oval that means the component attributes are linked with the composite attribute and the composite attribute this will be linked with the anti-type associated with it this symbol is used for multi-valued attributes as I gave you the example of hobby or qualification this symbol is used for the multi-valued attributes all these attributes are linked with an anti-type this is an example of an anti-type employee all the attributes are shown here for example experience the experience is shown as a derived attribute because the experience is derived from the attribute date hired if you see the employee ID since the single solid line is shown it means it is a simple attribute single-valued attribute it means it is a simple attribute single-valued attribute if you look at the address if you look at the address it is represented as a composite attribute these attributes are street and house number and the symbols of both the attributes they are not linked with the anti-type employee rather they are linked with the attribute address the address here becomes a composite attribute when you have an employee qualification it is basically a multi-valued attribute as we have shown with double oval obviously your date is hired this is also an attribute with a single line it means it is a single-valued and a simple attribute apart from this if you look at the naming convention you will get an idea where the employee entity type is named it is in the capital letters but the attribute you have is normally represented in the lower case and if you look at the two words as I have mentioned here amp name we have made amp in the lower case just to show that there is another word we have made amp in the capital so this is a recommended convention it is not necessary to follow dear students we will wind up today's lecture as you have seen in today's lecture entity relationship data model this is a semantic data model which has a feature which is a semantically rich data model you use this for conceptual data-based design today we have read some constructs in which there was entity type we have read different entity types after that we have read another construct attribute and then what is the difference and how are the links in the next lecture we will discuss the relationship Allah Hafiz