 Assalamu alaikum. Dear students, today we are in the lecture number 16 of the database management system course. The course code is CS403. In the lecture number 15, we were discussing the relational data model. We discussed a very important component of the relational data model and that was integrity constraints. We studied different types of integrity constraints. After that, we started the discussion on the design of logical database design. The logical database design is basically obtained by converting the conceptual database design into the relational model. So, it is a bit different from the conceptual database design where we design the system, we design the database from the scratch by analyzing the data flow diagram and other analysis tools. Whereas, in the logical database design, we simply transform it from the conceptual database design. We started the discussion on the logical database design by studying the transformation of the entry types and different types of attributes into the relational data model. Today, we will start a discussion by studying the conversion of different types of relationships into the relational data model. So, let's start today's lecture. Before transforming a relationship into the relational data model, you have to study, you have to analyze the cardinality and the degree of the relationship. So, this is going to be the pattern of our discussion about the transformation of the relationship that we study by different cardinalities, how it will transform different types of relationships. And we will start from the binary relationships. You must remember that the binary relationships are those relationships in which there are two entry types involved. So, first thing is we are studying such type of relationships that involves two entry types. But next thing is their cardinality. And we have studied that there are different types of cardinalities. There is a one-to-one cardinality, one-to-many and many-to-many. So, this is how we are going to start our discussion. Cardinality, one-to-many. And just as a reminder, I repeat here that a one-to-many cardinality is where one instance of one entry type may be related with many instances of the other entry type. And inversely, one instance of the second entry type is related only with one instance of the first entry type. So, this is a one-to-many relationship. So, how do we transform a one-to-many relationship into the relational data model? So, the primary key of the relation against many-side entry type contains the primary key of the relation for entry type on one side as foreign key. Dear students, as we have already discussed that whenever we want to transform an ER diagram into the relational data model, the first rule is we simply transform each entry type into a relation. So, so far in a binary relationship, we have transformed the participating entry types into a counter-relations. Now, it is turn of the relationship, a one-to-many relationship. In this, we are going to do that entry type. Now, look, here I would like to explain a little bit, so that we do not get confused in our discussion. Because we have already discussed that we transform entry type into the relation. So, now, when we talk about the relationship, when I talk about the entry type or the relation, then you will understand that I am talking about those entry types that we have already transformed. Dear students, here is another very important thing that you should remember that you must differentiate between a relation and a relationship. Yes, because this I have seen many times that students confuse these two things. They intermix them with each other, whereas there are two entirely different things. When we say relationship, technically speaking, this is purely a construct of the ER data model. And when we say the relation, it means the structure of the relational data model. So, there is a lot of difference between them. There is a huge difference between them. So, they are not the same. The context in which we are talking, the world in which they are different, the ER data model is the relational data model. So, do not mix these two together, they are different. So, we are talking that when there is one-to-many relationship, the participating entry or a binary relationship has already been transformed into the relations. The relationship that you have found on many sides, or the entry type that you have found on many sides, the primary key will be the one-sided primary key. On one side, the entry type or the relation that has been created by transforming the entry type on the one side of the relationship, the primary key will be the foreign key into the entry type or the relation against many sides. And as we have already discussed, what is the purpose of this? The foreign key is basically that it is an attribute that works as a link between two relations or entry types. So, here the one-to-many relationship, the entry type on one side of the relationship is the single attribute and multiple attributes. They will come and participate in the entry type which is on many sides and it will be used as foreign key. As we will declare it as foreign key, the referential integrity constraint will be imposed on it. What does it mean? It means that the foreign key, either it would be null or it would contain a value that matches a value in the primary key in its home relation. So, in this way, the relationship you have will be implemented. So, the one-to-many relationship will be implemented and mind it. We said in the start of this lecture that the transformation of the relationship, it depends upon the cardinality and the degree of the relationship. So, the binary relationship is one-to-many. You have seen how this will be implemented. Let us see an example of this. In this, you will see that we have a binary relationship in which there is an employee and a project. Now, what is happening here is that many employees work on one project. Now, you have a binary relationship whose cardinality is one-to-many. As we have read that first of all, we will say that the participating entity has transformed it into a relationship. So, we have a relation against an entity. This is our relation and that is the attribute associated with the entity. Similarly, the project entity has transformed it into a relation and all the attributes of the project will become a part of your project relation. Now, your relationship for that, because if you look at the diagram, you have an amp. On this side, you have a many symbol and on the project side, you have a one symbol. So, we just read that the relation or the entity type on one side will be the primary key as foreign key into the many-side entity type. What we will do is that the project is the primary key and we will include it into the employee as foreign key. The project ID is underlined with a dot because it shows that it is foreign key whereas the primary keys are underlined with a solid line. We have already said that you do not need to give the same name to the primary key and foreign key. They can have the different end but they must have the same domain. So, it means that when you declare it as foreign key, the reference and integrity constraint will be imposed on it. This means that the foreign key that you have in the employee relation of the project ID will either be value or null or you will have to match it with the value of the project ID in any of the records. I hope you understood what I said. Let us move on. As we have just discussed the one-to-many relationship and its conversion and transformation. There is a small but very technical and important point that whenever you are implementing a one-to-many relationship, the one-side, like the last example, the one-cardinality that we have on the project side, the minimum cardinality of that one side can be considered because it needs special attention. It needs special treatment. If your minimum cardinality of this relationship on one side, if it is optional, written as zero on your diagram, this means that if it is optional, the foreign key in that case can be null. By definition, it is also on the foreign key and the refraction constraint also imposes that the foreign key can be null. If compulsory, make foreign key not null. What is its significance? Dear students, it relates to a very important rule in the organization, in the system that you are developing, that you are developing the database for. The rule is that the anti-types involved in this and the anti-types on many sides, can a single instance exist without being related to the anti-type on one side? The question is, if it exists without being related, then obviously the minimum cardinality that would be zero or it will be optional. For example, if we think that if we have an employee who can exist in any organization without working on a project, then you are representing this by making the minimum cardinality on the one side of the relationship as optional or zero. In that case, the foreign key will be normal. What if the situation is that the employee cannot exist within the organization without being linked to a project? In that case, the minimum cardinality on one side of the relationship would be one. It means that it would be compulsory. If it becomes compulsory, then obviously you will not allow the employee to exist in the organization and according to the database, the record of that is in the employee-table, in the employee-relationship. So, you will not allow that. The way to do that is that the attribute that you declare as a foreign key, you will impose an additional constraint on that and that would be not null. That means that you declare the foreign key as not null. Why? Because now that you have an anti-type on the many side, for an instance, it is necessary to relate that on the last time or the first terminology we can say that it has become a weak anti-type. So, by definition, what is your foreign key, it can be null. Obviously, the referential constraint has also read that what is your foreign key, it can be null. It can have null value. So, in this situation, when you have an anti-type on the many side, it is necessary to declare the foreign key as not null. And the example we have discussed, the employer-projectory, if you look at that, the minimum cardinality in that is compulsory, i.e. 1. In that case, obviously, as I just said, you will declare the foreign key as not null. But the second example, we have a department or employee. In this, we suppose that a employee may be the one who has not been assigned to the department yet. So, in such a situation, when the employee has been hired of the organization, but he has not been assigned to the department yet. Here, this is a binary relationship and one-to-many. Because it is obvious that one employee will go to a department and a department boss can be the employee. But here, you see that on the one side, your anti-type, i.e. the department, the minimum cardinality with that is zero, it is optional. This means that the foreign key that your attribute will be assigned to the employee, the department's ID, that would be as a normal foreign key, i.e. that null value will be kept. I hope that what I have pointed out is that you have to pay attention to the minimum cardinality. You must have understood that this is a very technical thing. Let us move on. Many-to-many relationship. i.e. we have a binary relationship and the cardinality of this relationship is one-to-many. In order to revise it briefly, when we say a binary relationship, it means that the instance of one anti-type can relate to many instances of the other anti-type. Similarly, the second anti-type can relate to many instances of the first anti-type. So, this is a one-to-many relationship. Now, the first thing is that your participating anti-types, both of them, they have been transformed into the relations. And what was its primary, it has become its primary, and all the attributes of these anti-types have been included as the attributes of these relations. Generally, we do this that for a many-to-many relationship, you simply create a third table. i.e. you have a many-to-many relationship that will also become a third table. And the many-to-many relationship you have, we sometimes call it an associative anti-type. Then it is said that generally, the primary keys of the participating anti-types are declared as the primary key of this third relation. i.e. the third table we had, the primary key of these two participating anti-types that were their guest relations, both of them will be combined to become its primary key. And apart from this, as we have seen in the previous examples, in the previous cases, the relationship you have has its own attributes, which we call the descriptive attributes of the relationship. So, these attributes, they will become the part of this third table, they will be included in it. Let us see this example. This is a many-to-many relationship in which you can see that two anti-types are involved, book or student. This means that we describe it if in the language of the organization of the system, then we can say that one book can be issued by many students, one student can borrow many books. So, this is our relationship. Now, look at the attributes of both of your anti-types and attributes. So, when we transform this to the relation data model, then you see that the student you have has become the student table, the book anti-type has become the book table, and the relationship between them, what we have done in this case is that we have created a third relationship, the transaction, we have given the name of the transaction, and the primary keys of both of them will be combined to become the primary key of this table. So, in this case, we have seen how we transform a relationship, which is binary and many-to-many, we transform it into a relation. Now, as I told you, if there are properties of this relationship, its attributes, then what will be its treatment? Look at the relationship of these two attributes, there is an issue date and return date. The logic of this is that when an external book is issued, then when the issue is issued, when it is returned, then its return date will be noted. So, these attributes, they have declared as the attributes of the relationship. Why? Because, as I told you earlier, a logic was that whenever you have to think about the attribute, whether it should be associated with the participating entity types or with the relationship, then you see that to determine its value, if you need a single reference of the entity type, then it will be associated with that entity type, but if you need the references of both the participating entity types, then it is declared as the attribute of the relationship. For example, in this case, issue date, then you cannot know the issue date by just knowing the student ID. If you say, what is the issue date of this student? Obviously, no. If you say, what is the issue date of this book? Obviously, that book must have been issued by many students. So, to properly understand what is the issue date, you need to know both of them, the student ID and the book ID. So, when we say, what is the issue date of this book? What is the issue date of this book? This means that this attribute has to be associated with the relationship. So, the issue date and return date, they have been declared as part of the or as the attributes of the relationship. Similarly, if you think a little, then you can get more attributes of this relationship. For example, if you want to see that you have allocated a student to keep a book, then you have to allowed it for 15 days so that you can keep a book. So, if a student keeps a book for more than 15 days, then when he returns from there, you will see that he issued an issue to this state and returned to this state. So, it will be fine if this book has been kept for more than 15 days. So, it means that you can also associate more attributes with such relationships. In this case, you have seen that the relationship we have are the attributes when we transform them. So, both of them will be participating against two relationships which were your relationship against a third relationship. Both of them were a primary and the attributes that were descriptive have been made apart of the third table which we have created against the relationship as you can see on the screen. Binary 1 to 1. Two types of antitripes are involved. Carnality 1 to 1. You can understand this in a special form of 1 to many relationships in which you have the one on one side and the one on the other side which you have made and we call this a special form because it gets a treatment similar to the 1 to many relationship. So, the primary key of one antitipe has to be included in the other as a foreign key. So, both of them have 1 to 1 and one primary key will have to be included in the other as a foreign key. Now, the question is which side we have because both of them have 1. So, the primary key of one antitipe should be included as a foreign key. So, the rule of this is that the primary key of the compulsory side is included in the optional side. So, the 1 to 1 relationship will have most of the time an optional side. So, the type of antitipe on the compulsory side should be included as a primary key. The type of antitipe on the optional side should be included as a foreign key. Technically speaking this treatment can be inverse as well. So, the type of antitipe on the optional side can also be included in the compulsory side as a foreign key. But, why do we adopt this logic or this approach that the compulsory side should be included on the optional side. So, the relationship should be optional from this side. So, the time when that record will be related on the optional side and then the compulsory side should be related to that. So, the foreign key should be valid on the optional side as a foreign key. So, the compulsory side should be related to that. So, the primary key should be optional because the type of antitipe should be compulsory. So, whenever you will add an instance in this antitipe, against your relation, when you add a record, then the foreign key should be valid on the optional side. Whereas, if you reverse the treatment, that is, the compulsory side should be included on the optional side. So, it should be optional from the optional side. So, depending on the situation, the foreign key should be valid on the optional side. So, it's not a very big reason which you can't ignore. But a recommended approach is to avoid the null. Basically, you can say that there is a wastage of the storage. So, to avoid it, you should add the optional side to the compulsory side. Otherwise, if you put it on the screen, there is a one-to-one relationship between the student and the hobby. The hobby is optional on that side. It means that it is not necessary for every student to have a hobby. So, it is optioned there. The student has been declared as compulsory. It means whenever you place a hobby, whenever you enter it, it is compulsory. So, when you implement it, then the student has a student-relationship. The hobby is a hobby-relationship. The primary of the student will be included in the hobby as mentioned earlier. The junior relationship is as mentioned earlier. It says that depending upon the cardinality, the junior one-to-many introduces the primary key of the same anti-type as foreign key. It means that if you have a one-to-many relationship of the junior, the primary key will be repeated as foreign key or primary key as foreign key. So, you will have to name it differently in this case. Earlier, foreign key can have the same name but it can also have a different name but the domain must be the same. In this case, the foreign key which is included in it, it should be named as foreign key because this is one of the basic properties of the attribute name. It has to be unique. So, you will name it differently. After that, if the many-to-many junior requires creation of another relationship with a composite primary key. When you have many-to-many relationship of the junior, in this case, you will have the anti-type itself which is anti-type against a collectable. But against this relationship you will create another table. And in that your primary key will be composite. In which your primary key which is your main anti-type will be included in it. But along with that you will include another attribute because it is your primary key. This example will be clear to you on the next slide. For example, if you look at this slide, we have an anti-type employee. And in this, we have a recursive relationship in which managers. So, we said one employee manages many employees whereas one employee is managed by only one manager. So, this is an anti-type relationship which is a recursive unary. In this case, we will include our primary key which is an employee anti-type. We will transform this into a relationship which we said became an employee. All attributes will be included in the primary key. We have defined this primary key. Now we will put references into the table and we will give references to our primary key. This is the home relationship. But mind it, you will have to name it differently. For example, here we have a manager ID. This means that every employee who exists will exist. However, the manager ID of the manager itself can be null. It can be null or if you have this situation in the organization that every manager will be another manager or a chief executive executive. But every employee who is not the manager will have an employee who is the manager and record is in this table and if the manager itself is a manager can be a how you treat a one-to-many relationship. Now, if you are a unary man-to-many, for example, look at this, there is a unary relationship on the screen in front of you, in which our entry type is item. The idea is that you see that a factory offers different items. Now, every item is made up of many items. So, an item is also present there, and the items that are made up of these items are also present there. For example, their record is also present there. For example, if you look at the motherboard of a computer, there are many components on the motherboard of a computer. We can say that there are different ICs in it, and there are different resistors and capacitors in it. There are such things, there is a processor in it, there are slots in it. These are different things that are fixed on a motherboard. It means that our motherboard is such an item that is made up of other items. But the motherboard itself is part of another item that we call a computer. Now, if we look at a computer or a CPU, then the CPU will say that it has power supply, motherboard, hard disk, so all these things are made up of CPU. But then if we look at the computer, then the CPU is its component. For example, the CPU, the keyboard, the mouse, the monitor, all these things are made up of computer. If you look at it like this, then it is an anti-type, it is a kind of relationship in which many, many things are cut. What does it mean? That one item is made up of many items. It is a certain menu. And the other thing is that one item is used in many items. It is used in their creation. So, both sides are menu and recursive, because they are pointing themselves. In this case, when we transform it, we will treat it that the item that we have, against them straight away will form a relation in which we keep our item, all the attributes including the primary key are made up of that relation. This is done. Now, we will say that in this case, you will create another relation, which will be for this relationship. First of all, we will give it the name of the components or the compost. We will name it for this relation. After that, you do that, the primary key of the item, you do that. And if you look at it like this, what we have done is that we have made that primary key again. So, we said item number and component number. Now, what is the basic component number? It is the item number itself. But what I am showing you is that this component is included in this item. Again, what do you mean by two components? The first component number is named as item number straight away. It is known as plant item number. After that, the component number is named as component number. The component number is basically a foreign key, which is an id of another component. The first component, whose id is in the first attribute, is included in it. And the third attribute that you are seeing is quantity. What does this mean? It means that the first item you have, the second item, is included in so many categories. Similarly, if we say that if we make a CPU, then we said that the first attribute will be the CPU's id. In the second attribute, you will have the id of the hard disk, and you will get the detail of it from the items above. And the third attribute is quantity. It means that in a particular CPU, there are so many hard disks. It can be one or two, or three. Similarly, you will complement a juneary many to many relationship by creating a separate relationship, a separate table for the relationship itself. And when it comes to the primary and its anti-type, it will come at least once. In this case, as I have given you an example, it can also come twice. And if it creates another descriptive attribute of this relationship, it will also come in the same table. Let us move on. This supertype subtype relationship, we read that this is an extension in the basic ER data model. So, this is a special type of our relationship. Otherwise, it is just like a normal relationship. Otherwise, it is our relationship. But it has a special description that we have already read. So, let us see how this treatment happens while transforming into the relation data model. The first rule is that separate relations and tables are created for all these supertypes and the subtypes. There was a supertype, it had three subtypes. So, one of the relations will be created against the supertypes and three other relations will be created. Assign relevant attributes. What does relevant attribute mean? All three subtypes were associated with the supertype with the common attributes. What we have done is that that table, that relation that is created against the supertype, all the attributes will come in this table that table is created against the supertype. The other types, the attributes of specialized or specialized attributes will be associated with all the corresponding tables. And mind it, the approach that I am discussing here, that is one possibility. There can be other possibilities that you can find in the book but what we are adopting and which is a reasonable approach is that all the types against relations, attributes, subtypes and primary key of the supertype is included in all relations. That works for both the identity and the link. That means each of these relations will work as an identity and the other thing is that they will also link with each other. When you have a relation created against the supertype, you saw a record, now you want to see the particular subtype, the particular specialization that is of this instance. For that, the three subtypes that you created against the relation will come in one of them. So how will you link the value of primary key that you created against the supertype i.e. not in the subtype but in that relation that you created against the subtypes and in any of them or in multiple of them as we said overlapping or mutually exclusive disjoint in that relation that you created against the subtypes that will also create the value of primary key. The question is that the three subtypes against which we should go obviously it will be a bad thing it will be wastage that if we have to check all three of them for that you will remember we used a term discriminator attribute we include in the supertype just to keep a track that the specialization of this particular instance or the specific type which is of the subtypes so in that way you will get a link that you have an instance in the supertype what is its specialization because it is discriminator it will tell you. Now for example, on the next slide here you see that we have an employee a country type and then there are three subtypes one is salaried one is hourly basis and one is consultant in this all the common attributes that we have associated with the supertype employee and the special attributes are all associated with each other Now when we transformed the four we created the same name with all the attributes and also the discriminator we included in the supertype which we created and from there we will denote that the further detail of any particular instance whether it is hourly implied or it is permanent or it is consultant it will be stored there like let us say that we have P for permanent or S for salaried and H for hourly and C for consultant so from there in that case we can do any other character which means that this particular instance is not linked to any of them so this is how we implement the supertype subtype relationship I hope that you understood Dear students, here our topic ends our topic was how we transform conceptual database design and take logical database design and what is the idea? The idea is that you have got the EER database design the design in the EER data model which you also call conceptual database design the logical database design is that you transform it in that data model in which you ultimately implement your database that is the data model of your DBMS as I have already told you that generally we use the relational data model as our logical database design tool so in this topic we have studied how a design which was in the EER data model we transform it in the relational data model or mind it we have different situations different constructs in the EER data model whereas in the relational data model we have only one construct that is relation or table we have seen that when we had attributes first we started with anti-type so we said that we have weak and strong anti-type so how do we implement them? its simple rule is that every anti-type against you simply create a relation strong against weak against weak against but the weak anti-type after that we saw that there are different attributes so the simple multi-valued, composite or computed how do they give different treatments? we saw that after that we came to the relationship and in the relationship we had read that the basic significance of that is what is its cardinality and what is its degree first we started with binary relationship we saw that there can be different cardinalities one to many many to many and how we have to transform these three situations in some cases we use primary foreign key pair to implement the relationship and in some cases we create a new separate table for the relationship so we read this and in this I will tell you that sometimes when we have three anti-types involved we generally give a treatment that your participating anti-types you create a relation and one relation you will separately create for the relationship the same rule as in binary the primary keys of your participating anti-types will be the component primary key of this relation that you create against the relationship and finally we read that how we implement super-type-sub-type relationship and it was the same that all the types involved anti-types involved whether they are super-type or sub-types you create a separate table and the primary key we will use it as the primary key of all these and the primary key will be smaller and as the link will be smaller between the super-type and sub-types so we will create four relations against the sub-type and then we have discussed the role of discriminator which you will have in that relation against the super-type so when that data is available for all after reading that and when you will see which relation I will go for the further detail for which particular relation how you get your logical database design from your conceptual database design data manipulation languages dear students as we have read that our relation data model has three major components structures manipulation language and integrity constraints we had studied the relation data model and on that basis we also studied how we transform it from conceptual data design into the relation data model and you are also seeing the structure and integrity constraints the structure that you have is the relation that we used to transform it and the integrity constraint whether it is entity integrity or referential integrity constraint we used that when we implement relationships and we used integrity constraints especially now we will talk about its third component and that is data manipulation languages the languages basically are used to manipulate whether it is to create the data in it or to enter the database or to operations or any type of operation that you do on the database for that we need a data manipulation language now in this different books discuss the reduction for example a big debate which is confusing is that procedural and non-procedural languages you will often see procedural and non-procedural the first thing is what is the difference the procedural languages basically you can say their characteristics they tell you what to do and how to do in that you have to how to do the operation you have to tell the procedural languages and the non-procedural their characteristic is that you only have to mention what to do not how to do the part of how to do which we do not mention now the data manipulation languages for operation our relational model for operations the language used the standard is SQL structured language we will read about this later but your relational model with it the things associated right from the relational mathematics there are two major languages called relational algebra and relational calculus the algebra is a procedural language and the relational calculus is non-procedural the data manipulation languages that we are going to read we will first touch the theoretical languages the advantage of that is that the mathematical part of your relational model gets completed from the study point of view that when you read the mathematical it will get completed the second thing is the SQL that you have it is based on relational algebra or relational calculus some people say it depends on relational algebra some say it depends on relational calculus and some say it is based on both I think there is no significance on which it is based it could be a scholarly debate but it is based on both when we will read the theoretical languages especially in detail we will read the relational algebra after that the SQL that you have to use you will get an idea of its working you will get an idea of it and the similarities you will get an idea of in this part you will read the languages and mind it when you use dbms you do not use the SQL let us start relational algebra operations work on one or more relations to define another relation leaving the original intact look this is a very basic property which is called relational manipulation languages especially on relational algebra and the SQL they operate on relation and produce relation again they operate on one or more relations and they produce a relation means your relational algebra will have one or more relations and when it is output it will give one relation single relation means the original relations are multiple relations the input will be in the original state there will be no change the output will have a different relation not that the input will have an effect both the output and results one operation can become input to another relation another thing is your input also has relation process output also has relation if you follow this sequence you can understand your relation operations can be used how one input one output will be input output and this change can be taken as much as you want so this is also useful so he said allow expression to be nested just in arithmetic this property is called that you can nest one of the operations 5 basic operations in relational algebra selection, projection, Cartesian product union and set difference dear students the part of the manipulation of the relational data model we have touched on it today's lecture is coming to an end you have seen 5 basic operations which supports relational algebra especially the first 3 selection, projection and Cartesian product they should not be more important so you need to understand them in a better way especially you need to practice them when I teach you the examples when we apply them on different relations what output will come I will show you that whenever you have a data when you apply an operation see how you get that result when you apply an operation on a relation and you relate it to the output you can understand the functioning of that operation as I told you the relational algebra is related to the SQL then you will see that the proper concepts will be helpful in the SQL we have recommended books the book of Ricardo in the book of Ricardo the relational algebra is also discussed in detail before the SQL so if you see examples and your working if you want any mathematics books if you want to study the relational algebra you can get help from that because now you are practically going to operate on the database now you are entering into a stage where you will be practically implementing different operations on the tables and relations and try to clarify the size and obviously the clarity of these concepts is based on the last concept that is why you should clarify the basic concepts of the relational algebra now we will discuss these operations in our next lecture Allah Hafiz