 आस्टाम लेक्ठम, today we are in the lecture number 15 of the database management system course. The course code is CS403. In the last lecture, we started a very important topic of our course, which is the relational data model. I hope you are understanding, but just for your convenience and to remind you again and stress you, I will briefly come back to you. Dear students, until now our activity, please clear it in your mind that we are following the database design process in which first preliminary study, in which we understand the existing system after that we have analysis phase, in which we give our database design now we are coming on logical database design and see that our conceptual database design is generally in the EER data model. Why? Because EER data model is a semantic data model, that is semantic rich. It provides us different constructs, which we can design using the database. But there is two implementation. Implementation is that you do not find any DBMS based on the EER data model. On that space you have to transform the design of your EER data model into a data model whose DBMS you will ultimately use. Because according to today's trend, generally we use relational DBMS hence our logical database design will be in the relational data model. And again the same thing is that what is the benefit of introducing this stage. The benefit is that once you have got our logical database design in the relational data model after that when we want to implement this design we have got different options of different DBMS. So what will happen is that we can select whatever DBMS we want which our logical database design will be able to implement on all of them. So this is a reason, a logic to follow this sequence, which we are still following. Then this is that your logical database design will be in the relational data model. Last time we started implementing relational data models and we saw that a relation is a subset of two attributes of a cartesian product of a domain. And it was said that it is related to mathematical relations. We studied a little about mathematical relations so that you can get a connection between mathematical relations and database relations. Then apart from that we repeated the notation so that when you will see a relation scheme that how we define a relation, how we define its structure and then when we want to show its data, its instances then how we can write it in the form of a relation i.e. in the form of a mathematical notation then we see the tuples in the form of a set we can write it as such or if we represent this relation in a two-dimensional format in which there will be columns and rows then we will call it a table. And keep in mind that the table or relation is the same because the table is a special representation of the relation otherwise both are the same. So the definition is that rows, records and tuples are the same columns and attributes are the same. So this is all that we have covered in the previous lecture about the relational data model. Let us move ahead from here. As far as the definitions are related i.e. mathematical relations and database relations we have defined them and related them. The objective of this side is that the basic properties of the table i.e. the six basic properties about which i told you that everyone should come every time. So we will not see that we have related the database relations and mathematical relations but you see that in the reference of those properties what difference is this? Properties of database relations are similar to those of the mathematical relations except i.e. some properties are different otherwise the six properties of the database table or the database relations are the same properties of the mathematical relations. What is the difference between the properties? The order of columns in mathematical relations does matter. You will remember that among the six properties there were two characteristics that the order of the columns is immaterial and the order of the rows is immaterial i.e. i have explained to you that we have a stable database or the relation of the database if we change the columns and the order of the rows then it will have no effect on the table this is not in the mathematical relations the other five properties it contains the atomic values fine apart from that each column has a name it also applies that the sets that you have to consider are the names it is said that you do not have any duplicate it is the same thing that when you talk about mathematical relations there is no member of it when we even say that in the tables in the database tables the order of the rows is immaterial the same thing applies to the mathematical relations as well this means that if you have a mathematical relation in the tables in different order pairs or triplets if you take a table and change its position one table was before you put it on the third position you changed the position of the tables this will have no effect on the mathematical relations it will have no effect it will have the same relation it will have the same set but this is what is mentioned here that the order of the column does matter in the mathematical relation if you have a complex relation which has two sets A and B the Cartesian product A cross B is the subset their order should be the first set if A is written in A and B is written in B then the first element of A should be the first and after B it cannot be that you first do one and then do another or if you change all the orders i.e. in all the sets in all the ordered pairs the first number of B and after A now this will be called B cross A's subset if you talk about mathematical relations A cross B is not equal to B cross A i.e. the column i.e. your ordered pair the position of the members if you say that you can change the order that will be considered as another relation whereas we saw that there is no difference in the database if you change the column then your table will remain the same dear students we have just discussed that the properties of the database relation if we compare the properties of the mathematical relation then what is the difference we have to know that there is only one property where the column that matters whereas the column does not matter whereas every order does not matter let us move on simple definitions degree and cardinality as we have discussed degree and cardinality of the relationship here we are talking about degree and cardinality of the relation there is a difference we do not have to mix them we do not have to confuse them when we talk about degree of the relation that means there is a number of columns in the attribute defined that is called degree of the relation and when we talk about number of rows that is called cardinality these are small definitions but this is the only thing we do not mix it with the degree and cardinality of the relationship otherwise just simple definition dear students when we were discussing the ER data model we discussed different types of keys their purpose and importance in detail you must remember that we have discussed 5 different types of keys there super key, candidate key primary key, alternate key and secondary key all those keys definitions and their characteristics all of them they are applicable to the relation data model as such as there was definition the same role and purpose one type of key which we did not read and we will read it specifically this is important as well the first thing is which key is it let us see primary key we read the relation data model in its context and this is very important how to place it that is important to understand that is why you see its definition an attribute of a table B that is primary key in another table A so such attribute in a table B which in another table is being used as primary key so in B we will call it foreign key in B that attribute which is present in another as primary key we will call it foreign key and after this you can see foreign key can be multiple meaning in one table you can have multiple foreign keys and apart from this the relation in which foreign key is primary key in this relation foreign key is called home relation meaning if we have a relation B an attribute let us say X in another table A is primary key so when we see B we will say X is a foreign key and home relation is A in which it is primary key about foreign key we will discuss further let us move on now I am going to explain what is the use of foreign key and how we declare it first of all we have employee table in this you can see attribute, name, qualification and department ID now if you have another table we have department in this we have attributes department ID, department name and number of employees how many employees are there now if you see the primary key of employee we have underlined this notation which we will use you underlined with a solid line which is a single primary key and a composite primary key if there is one attribute underlined if there are two attributes three attributes then all of them will be underlined and whenever you see two or three attributes underlined by a solid line it means that this table has got a composite primary key or in its primary key you will see this attribute which you can have and practically aesthetically speaking it can have more attributes so in this we have employee table we have underlined with solid line it means this is the primary key of this relation now if you see department ID we underlined but with a dashed line broken line this is the symbol which we use to represent a foreign key now if you see the department ID is in the department table in the department relation which is the primary key now the department ID in the employee that will be considered as a foreign key dear students about foreign key for example we have seen the name of the foreign key and the primary key both have the same name this is just a chance this is just by chance so this is not required the foreign key and the primary key they can have different names there is no problem in it but the condition is that both must have the same domain now when you see that the foreign key links the two tables till the time they have the same domain values then keep in mind that the constraint on the foreign key is that they are not required to have the same name but they are required to have the same domain apart from this I have said that you can have multiple foreign keys for example for example we have an employee if it is linked to the department then it has a foreign key for example we have the employee manager or the project he is working on if the project is linked to the project table then the project table can be linked to the foreign key but there are more foreign keys there are two things one more thing as your keys as many keys you have most of the time they are used to identify a particular record or a secondary key you can have multiple records on the value of the attribute but the foreign key does not have a secondary key although the primary key is unique because it is being used as a primary key but the foreign key itself is used as a foreign key the value is not required to be unique and it is not unique these are some things about the foreign keys we will talk more about this later as we have said that integrity constraints are a component of relational data model relational data model provides two types of integrity constraints these are entity integrity constraints dear students integrity constraints are also among the basics of this course the person who has studied about relational data model should know about integrity constraints also this is what I will tell you that you should understand them and you should always know entity integrity constraints this is a story we have read it before when we were discussing the concept of key your entity integrity constraint which is specifically defined from the perspective of relational data model it says that primary key cannot have the null value you already know let me emphasize again that primary key cannot have null value means no part of primary key if the primary key is a composite key if you have three attributes even one of them cannot have null value not that it cannot have full null value but any component cannot have null value this is called entity integrity constraint second is referential integrity constraint it is very important you should remember this value of foreign key is either null or matches with a value in its home relation you must have felt that we have key identification or unique identification like super key, candidate key, primary key and alternate key like this we do not have this restriction other four attributes you should relate to the last two like this it is not considered that it cannot have null value foreign key by definition not by definition by this rule by referential integrity rule foreign key can have a null value second is if you have to add value then this value should be the home relation the value of primary key matches with any value this is compulsory compulsory look understand that your integrity constraints are so important that they have been declared they have been made a part of the data model itself see right now you are not studying any particular dbms you are studying the relational data model and when you are studying the integrity constraint as part of the relational model as a component of the relational model this means that it is a component so they are there always in any relational dbms because when your dbms is based on the data model the structures, language and constraint data model dbms will have to implement them because your integrity constraints they are the part of your data model this means that whatever your relational dbms will be the integrity constraints will be supporting this is a constraint referential integrity constraint this is imposed this is controlled by the dbms itself this means once you declare an attribute as a foreign key and then you relate it with the primary key in its home relation now dbms will make sure that you do not enter a value into the foreign key that does not match with the value of the primary key in its home relation why? let us move on now here in front of you you are seeing two tables one table we have an employee we have four attributes employee ID, employee name qualification and department ID as we have seen on the slide apart from this department ID, department name and number of employees in that department we have three columns now if you look at the data we have four rows in the employee table and we have three rows in the department table if you look at the employee table here the employee ID is underlined this means it is the primary key now due to the Entity Integrity Constraint it is certain that in this column we cannot enter null value otherwise if you look by definition the other attributes we have or qualification or department ID we can place null in it unless you declare or not null not null means you cannot enter null otherwise apart from the primary key there is a default constraint when you declare this is the primary key it is certain that you cannot enter null so in this in the employee ID in the employee name in qualification now department ID if you look at the attribute department ID you cannot see any value now if we want to place a value it has to be one of the three values of the primary key of the department table we can place D001 D002 D003 Dbms will make it sure if you want to enter a value as you will try to enter let us say D006 what will happen that it will try to match with the department table and in the department table if it does not get such a record that the department ID which is the primary key there is no D006 in the record so it will not let you enter that value if you do not want to increase you can do something that value cannot enter why the reason for that is that basically the foreign key is linking what does linking mean linking means when you want to store the employee data about the department one of the reasons is which department it works whether the department works or the department has other details for example how many employees are there one way of doing that is that in every employee's record store the name of the department and also store the number of employees that is written in the employee table department name and number of employees we do not do that there are many reasons which will be clear in the next lecture but you can still realize that if we have every record here department name and number of employees so if we have 10,000 records and one of them has sales in that 3,000 employees work so you have to write 3,000 records that sales and in that employees are 17 you have to write sales sales how many times? 3,000 times the solution which you have adopted is that we simply place department ID now this is if an employee works in sales department there are 17 employees so you have written department ID in the employee if you want to know what is the name of the department and how many employees are there simply take the attribute of department ID and by using that ID you come towards the department table and you will see the sales department and there are 17 employees now this information that there are 17 employees you have to write this 3,000 times you have written it just once and through this reference through department ID in reference of department ID through that reference this information is available for all those 3,000 records and same applies to the data of the other department as well that rather than copying data department name and the number of employees in each record you store the reference this means that if you have 10,000 records then you think how much you have saved in terms of storage there are other benefits which we will study in future here the thing is that when we talk about department ID I hope you have understood why it is necessary it is necessary that if you put this in a record department number D005 department ID D005 now if you want to know which department this employee works you would like to know the name of the department and to know the name of the department you take this value and then you consider the department table to locate the record with the key D005 there is no D005 what does it mean it means that the reference you have given does not exist so what is the proper time to stop or check this the proper time is when you are entering the data at that time someone should make it sure that whatever value you are entering it represents a valid reference because when you have declared it what is the purpose what is the objective to link the employee table and the department table now you have a value in the employee table that does not exist at all what are you doing if you look at the implication from another angle what is your point of view you say that you have declared a department of an employee that a department works when that department does not exist so through the foreign key through the foreign key constraint rather we should say through the referential integrity constraint DBMS makes it sure see the constraints there are many types we will see this in a while as a designer as a developer this is your job to make it sure that all constraints are properly placed there this is your job basically you have to make it sure now if you supply some constraints that you do generally through some codes but the application of a particular constraint becomes very definite it becomes very sure if you implement it through DBMS when you make a constraint a part of the schema rather than the application program wo then automatically it becomes the responsibility of the DBMS to make that thing sure if you first you do not declare the attribute without it you can make sure that the value in the foreign key matches with the primary key you have to add the program you have to add the code which will be activated when you enter the record and it will match but it will become your responsibility in this your responsibility is so much that you simply declare this is the foreign key and this is the home table of this foreign key and that is the primary key now your responsibility is over every time when you touch that foreign key when you change it, remove it or add it DBMS will make it sure that it is properly stored this will give you a big achievement you will be sure that the reference in our example the department that will come will always be a valid department when you enter the values in this column before that you have to add the record in the department table otherwise if the department table does not have a record no problem you can leave it all the records if you do not declare any employee's department you have not entered the data no problem but if you add the data then it is certain that you will add the same data which will match dear students this foreign key constraint or this referential integrity constraint this is so important in so many situations in almost all systems in almost all organizations that this has been declared as a part of the data model and when you have declared a part of the data model it is certain that all the relational DBMS will be supporting it I hope you integrity constraint and especially the refresher integrity constraint if there is any indication then it will be understood the same thing we have done that they help to maintain the validity and integrity of the database we have just read about the referential integrity constraint there are many examples of constraints like the null constraint the default value all these are the examples of the constraints the DBMS it supports the DBMS if you say something is not null so not null means you do not enter the null value in that attribute whether it is primary key or not because it cannot be null even if it is non key if it has been declared as not null then it cannot be null the default value is if you do not add any value then the default value will come and the domain constraint is a constraint this is the value we have read the three components of RDM we have read the structure only single one the relation and integrity constraint now the third part the manipulation language we will start after some lectures now we will touch something else see what it is dear students we have read about relational data model why we have read that because we have to use it as a tool for our logical database design logical database design is obtained from conceptual database design now you see the difference your conceptual database design how did you make it you studied your environment system you saw the people you interviewed them you circulated different questionnaires and many other means and from there you got a clear picture of the working of the existing system then you collected their requirements what they expect from the system and then from the understanding of the existing working of the system and by analyzing the requirements of the user we saw all the phases step by step the antitypes the relationships so it is a process when you define when you create something from the understanding of the existing system but when you come to the logical database design your conceptual database design the design in the ER data model that will provide you a base to get your logical database design so it is a matter of simply transforming mapping translating your ER database design into the relational database design or mind it do not get confused here that why do we do this when we have to transform when we have to transform then why do not we get the relational database design or our logical database design straight away because our conceptual database design is our first design at that time, at that moment we need something semantically rich that gives us more expressive power that makes it easier to understand our design to discuss our design so that is why we used the graphical symbols or different structures self-explanatory design now we are moving towards implementation of that design its implementation because we have the dbms that we have to use that is why we are establishing our logical database design in the relational data model a small thing keep in mind I hope you do not get confused but if you want such a data model in which you can make conceptual database design then object oriented is such a data model which is a semantic model and you have dbms in it so if you want to choose an object oriented dbms as your tool in that case your conceptual database and logical database design will not be different the first design which if you make object oriented data model from there you can straightaway go to the implementation of that design whereas in this case first you have designed your database design in the ER data model now you will transform it map it and convert it into the relational data model and from there you will implement the relational data model using a tool that would be a relational database management system generally it involves transforming ER data model to the relational model do not fix your mind that this always happens the thing I am emphasizing is its logic this is different most of the time most of the time you have it happens in ER and you convert it in logical in relational but that is not fixed it can vary you should have the understanding of the concepts so far we have studied both the data models the ER data model and the relational data model now we have to read that our extreme process is to take our logical database design so this is a phase how we will do this dear students this is a transforming this is a conversion it is a very simple and straight forward process there are certain rules defined clearly defined when you reach the stage of logical database design it is certain that you have to apply a simple rule and close your eyes straight away you have to transform it in logical database design I have said that you should not close your eyes let us see what is ahead transforming rules what are the rules that we map our conceptual database design into relational one yes this means we have tools available normally we say case tools if you give your database design it will automatically convert it into relational design because they are rules they follow them but evaluate the output of the case tool it is not that you accept it because you have got multiple options so you can see the option the case tool I have a property and some situations they are not properly handled by the case tools in that situation automatically involvement is done so this recommendation even if you are using some case tool evaluate that transformation the mapping or transformation it is not correct and you may need to make some changes in that output so now we are going step by step that you have components of your EER a conceptual database design we will discuss all those components how to transform it let us see first thing is that each regular entity type is transformed straight away into a relation simple rule you have seen your entity type regular entity type and you have transformed it into tables it is an easy rule it is simple if there are 5, 6, 7, 8 entity types in your EER design in your conceptual database design then it is straight away convert it into tables or relations and generally as far as the name comes that you will keep the name of this table generally it is used in the same name so give the same name then the primary key of the entity type is declared as primary key of the relation it is simple it was simple whatever and you have said that the primary key of a table is underlined with a solid then there are simple attributes simple stored attributes of your entity type which is transformed into a relation all the simple attributes are also attached to the table at this time the example is how we transform a regular entity type into tables here you can see an entity type student it has 3 attributes one is its primary a student ID and a date of birth you have seen straight away what is the table you have named it which was the entity type you have declared it as primary key and the other simple attributes student name date of birth you have represented them as the attributes of the table so this is simple you have transformed a regular entity type into a relation first thing you know what is a composite attribute which is made from the other attributes you have got an address we have taken an example of this sometimes you can call the name a composite attribute which is the first name and last name you can get an other attribute you will remember that the basic properties of the table is that the table can contain a single value of its atomic value so if you are planning to declare a composite attribute then bind it it cannot be a part of your table as you have made the simple attributes a part of your table no for this you will have to do a different treatment that is quality becomes a limitation we say that your relational data model its simplicity is its strength how? but where you will note that it has become a limitation that the composite attribute cannot be represented or cannot be done as you can do the simple attribute whereas if you see there the year data model the provision was that you can represent the simple attribute as well as the composite attribute here the simple attributes they are fitting just fine so the composite attribute there is no regular provision how? see here we have the same type of student the basic attributes simple attributes student id name rate of birth along with that there is a composite attribute its address we have stored the address its state number its city its city code area code now this is a composite attribute so you represent that as a composite attribute in your er design but no provision for a composite attribute as such in the relational data model now what you will have to do you will have to create a separate table and other table in that that your primary is of that table which you have created or you can call it as the primary of the student that will be used in this other table so if you have created a table for the student entry type which will be called as student you will create another table which will be called as ST address or student address now all the components of the composite attribute the component attributes will be stored and logically you will think that all the attributes will form an address a composite attribute and for reference you are using the primary key and the primary key of the other table will be the primary key which is the main table so what will happen first you are accessing the data of a student you need all those attributes i.e. its name, id, name date of birth and address so it will happen that you will come to the student table and from there you will take its name its id is there and from there its date of birth and from there when you want to access the address you will come to the second table and from there you will take the primary key which is the name of the guest and date of birth and from here you will be accessing the address of the student which you have designed you represented that as a composite attribute but when you come to the logical design there is no provision for the composite attribute you had to create a new table i hope you have understood in this section you will create a separate table come let us go ahead multi-valued attribute you will remember such attribute which has multiple values and in this we had eR data model we had a double lined ellipse for example there was a hobby qualification previous jobs these are all such attributes because there can be multiple values there is reading in hobbies there is skiing there is snoker there is cricket these are the hobbies if we talk about qualification right from the matrix then FSE then BIC then MSN further ahead these are all the multiple values so an entry type with a multi-valued attribute is transformed in this you have read in the basic properties that the attribute can have only the single or atomic values so this has been confirmed that your attribute cannot store multiple values then I will say that what you call multiple values if you enter a string then dbms is not considering it as multiple values dbms is considering it as a single value that contains some text if you have put this snoker skiing, reading they are multiple values to you when you see them but as far as the dbms is concerned it is considering as a single value and when operation is done it will do the same because it does it on any single value now what will you give what treatment you will give as it is written here you will basically create a separate relation for this multi-valued attribute so what will happen that entry type that has a multi-valued attribute what you will do is you will create one relation for the entity type itself one second, this is your attribute multi-valued attribute for this you will make a table now see you have made a table for the composite attribute and what is the difference when there was a composite attribute it had that every instance which is a record in your main table you have only one record in that table in which you implement the composite attribute so the problem here is that one against multiple values so from here immediately guess that there is a problem of the primary key where the original table which was created against that primary key is the primary key of the second table but here it cannot be used as the primary key because you are having multiple records multiple instances and it cannot be duplicated then it will happen that the second table is the primary key and the value of this attribute will be the primary key for example if we have a student and a multi-valued attribute then we will do this which will be the second table ST hobby and there will be two attributes student ID and hobby name so these two will be the primary key of this new table so when you have to access the student record then the other attributes will be taken from the student table but as far as the multiple values of the hobbies are concerned for that you will have to refer to the second table where the primary key which was student ID you can get 2, 3, 4, 5 records but there the primary key can be duplicated because this is not entirely the primary key this is the part of the primary key and the hobby ID and hobby name will be the primary key so based on this there can be multiple time and here you can store the multiple hobbies so this is the treatment that we gave to the multi-valued attribute dear students in today's lecture we continued our discussion about the relational data model in today's discussion we finished the study and then we started the development of your logical database design and about that basically what we have to do is to transform your conceptual database design on logical database design and lastly at the end of this lecture we were discussing what are the rules that you follow straight away to transform your conceptual database design in logical database design so these are the rules that we will cover in the next lecture now I would like to say goodbye