 Dear students, this is lecture number 25 of the database management system course. The course code is CS403, I am your teacher, Dr. Nair Masood. In the previous lecture, we concluded our discussion on the physical database design. There we discussed the vertical file partitioning and after that we discussed the clustering of files. And I emphasized in the previous lecture that in the physical database design phase, you have got different options to implement your normalized logical database design in an efficient manner. So, efficiency is the prime concern there. So, sometimes you have to even violate the normalization, but you have to be very clear why you are doing that. So, that was the physical database design. After that, we have to study the SQL, a tool, a mechanism to handle to process the data in the database. SQL is accepted as a standard language to manipulate relational databases. So, we discussed the history of the language in the previous lecture. Today, we will start with the discussion on the SQL. So, let us start today's lecture. These are the rules regarding the understanding or studying the format of a statement. This is applicable in almost all languages. Whenever you describe a language, whenever you mention a language, you write the general format of the statements of that language. So, there are certain rules to interpret the general format or the format of different statements of a language. Here I have written some very common, very general rules and whenever I write the SQL commands here, I will write the format of the command first. Even if sometime I may forget to write in that format, but this is the rule that applies everywhere. If you even see the SQL commands, SQL statement written in the book somewhere, these rules apply everywhere. So, I will try to explain these rules so you try to understand them and then it will be easier for you to understand how a statement will work, saying the format of the statement. So, if you look at it normally, the reserved words are written in capital, that is, the reserved words are written in the capital letters. The reserved words means that you have to give those words, those terms as such in the same spelling, in the same position, if they are in a specific position, you have to give them in the same order. One thing to note is that when we write the reserved words in capital case, in upper case, then that is a rule in the discussion of the format. This is not necessary that when you use the practical statement, then you must write that command, those words in capital letter. It varies from language to language. Most of the languages, they are not case sensitive. We don't even talk about languages in most cases. If we talk about SQL, in the case of SQL, the SQL server you are using, the general, your DBA message, they are not case sensitive. So, when you are writing the command, whether it is written in the upper case, in the lower case, or in the mix, it will not make any difference. It will interpret it correctly, it will interpret it in the same way. But by writing the format, we are following these rules that in the capital letter, it means that you have to write it in the same order and in the same spelling, in the same way. Practically, case does not matter. I hope that you have understood. User defined identifiers in lower case. When we discuss the format, we will see that some things will be written in lower case. It means that you have to provide those things over here. They could be the name of the tables, they could be the name of the attributes or conditions, anything. But basically, the user has to provide these things. So, that is why we write them in lower case and if you understand that some things are written in capital, some are written in lower case, some are written in capital, that you have to produce as such a case that you want. But in the format that is written in lower case, you understand that you have to write something from your side. So, you have to see that yourself. So, we will see that when we discuss the example, you will understand that. You have to take care of the rules regarding the valid identifiers. In every language, there are rules. They are combined with minor differences. But if we talk about this in SQL Server, or in SQL, there is a requirement that your name, for example, you are giving the attribute name or file name, there is a requirement that your first letter is that it should be an alphabet, from A to Z. Apart from this, it can also start from under score. And you will see in SQL Server, you will see some variables which we will call as local variables. It can also start with the rate symbol. So, from alphabet, under score, at the rate, and in some cases, from number sign. From these four, your identifier can start. Its length limit is up to 256 characters. You can give an identifier of this length. So, you have to take care of these rules. And one more thing is that it must not be reserved word. Reserved word means that such words, such terms, that SQL Server, the system itself has reserved for certain purposes. They have got certain meaning towards GBMS. So, you cannot use them as the identifier name. For example, if we say select. Now, select is a reserved word. This means that the select, which is more specific to SQL Server, cannot be used as the identifier name. Optionals in square brackets. In any statement, in general format, the things that you see enclosed in the square brackets. This means that they are optional. Optional means that if you need them, according to your situation, according to your requirement, if the things in the square brackets, if you need them, write them. If you don't need them, don't write them. Without them, your statement will be correct. If you look at it, as we mentioned earlier, like the things in the capital letters or in the lower case, if they are without the square brackets, it means they are required there. Without them, your statement will not be correct. But the square brackets are optional. Apart from this, you will see some items, which are curly braces, they will be enclosed in them. This means that the items in these curly braces, they are required. You need to give them to them. Now, the question is that I have just told you that the things written without brackets are also required. What will be the difference? The difference is that sometimes it happens that you have got options. You have three, four, five options. You can give one out of them. So, one of the options there requires a requirement. Curly braces show that you can give one option out of them. And what we have written in the front, which is a vertical bar, this means that we have options here. You can give three, four, five, if there are different options. You can give one out of them. You will see the first item, the first thing is vertical bar. The second thing is vertical bar. The third thing is n like that. This means that the options you can give one out of them. After this, you have seen the symbol that you can give dot dot dot or n without the square brackets. This means that whatever is written here before this, you can repeat it n times. So, these were some very general rules that you should use while interpreting, while understanding the format of a statement which you can see there. During this course, you can see the manual especially of the SQL. So, in that, whatever is written here, you will interpret them according to these rules. For example, if you see here a statement written here, select, it is not necessary that this is the format of the right select, but as an example, select is the first word that has been written in the capital letter, in the capital case. This means that it is written with the same spelling, with the same position because it is written in the beginning which means that it has to be the first word, first term. As I said earlier, when you write the command it is not necessary to write it in the upper case. By writing the command, you can write it in the lower case or in the mixed case. After that, there are some things that are all in between, vertical bar and then distinct. What does this mean? It means that these two things are optional. If it is necessary, then give it. If it is not necessary, then don't give it. What is their purpose? When we read the select statement, then we will discuss it. But here it is shown that all are distinct. One of them will give if required. Otherwise, it is optional. From this, you can see that there are two options. One is a sterisk and one is a select list. This means that since you are in the curly braces it means that this thing is required here. Since they gave you a vertical bar and they gave you two things, it means that you have to give one of them. Either you give the star, the sterisk or you give the select list. There is no explanation for the select list. Otherwise, the select list means that you will give a list of attributes that you have to select which attributes. After that, you can see that the form is shown after the curly braces. It means that this is required, this has to be there, and this has to be there as such. You can see its order and spelling. In this, all are distinct and the star after the select list you have to give form. After that, you can see that there are some things in the curly braces. Again, it means that you have to give one of them. What is that? You will give the table or you will give the view. You will give the table or you will give the view. After that, you can see that it is written in the square brackets comma dot dot n. It means that you can give multiple tables or views here. It is written before table and you can separate it with comma and repeat it. That is one table's name, comma, the other table's name, comma, the third table's name and like that. In this, you can see that certain things are written in the lower case. Like all are distinct, form is select. These are reserved. Compulsory in these will be written as such, which is optional. If given, it will look like this. But the things written in the lower case like select list, table, view, these things have to be provided by you. Depending upon your requirement, depending upon your query, you will do things according to that but you have to give the required things. This was a general rule and it was an example. I hope that you have understood how to look at the statement and how to understand it. Here, it is written as select star from student. In this statement, select has been written at the start of the statement because according to the format, the select term, the select word has to be the first word at the first place, the first position. After that, you have all or distinct options we ignored both of them. We didn't use either of them. We only had options. We didn't have to use them. Apart from that, we had two things in the curly braces, star and select list. We have to give one of them. We selected star in this example. After that, after the curly braces, it is written as from. This was the position, this was the turn of the from. We wrote here from and see, from here is written in the lower case. It doesn't make any difference. It is 100% correct. After that, if you see, there were some options in the curly braces. We had to give one of them. Here we have given the table name that is the student, STD. So see, STD is a user defined identifier. We have named it as a table. The rules we saw in the slide according to the rules of the identifiers. The first letter is alphabet. It is not a reserved word. Maximum length is less than that. So you can give it any name. But preferably, you should use the meaningful names. If you read its name, you will be satisfied with it. This is not a must, that you have a meaningful name. For example, if someone's name is Muhammad Sharif, then it is not necessary that he is Sharif. But you are storing the data inside of cars and vehicles. That is perfectly all right. There is no problem. But if you have this condition or you think that I will remember then mind it. It will disturb your condition and it will disturb you definitely. Because the problem is that the recommendations and rules we should follow them. Because the beginners don't follow these rules or they don't understand its importance. They start developing a system and understand that we are doing this and we will remember it. But the thing is that these systems, they span over a long time. When you have to modify it there are so many things that you cannot remember over a long period of time. You will definitely remember it. It is recommended that whenever you use an identifier name whether it is the name of the table or the attribute and you must have noted that so far whatever we use the name, either the name of the table or the entry type or the attribute. The name was related to the purpose of the attribute or the table. For example, in which we had to store the tables that we planned to use for the teachers we named it as teacher or faculty name. You will never object to the DBA that you have the name of the table but you are storing it there. This is for your own convenience. Technically you should do this and this is a very valid technical point that you should keep things in mind as soon as you see the name that something like this can happen. Let's move on. Data types in SQL Server here it is said that SS is our own invention which is not established or anything like that. We invented it in this course for the gravity for the efficiency we called it SS. It always means SQL Server. So, as I said the data types they are almost similar in most of the DBA but in that exactly what is their nature and their name can be different so you should have an idea that this type of data types are generally available in DBA especially in relational DBA and it is a requirement from SQL as well. So, all DBA messages at least the basic data types in SQL will be provided. So, what I am defining here or I am telling you they are specific to the SQL Server precisely speaking but if you change the DBA then there will be a minor difference especially the difference in the name used in different DBA like DB2 or Microsoft Access so you will see some differences especially in the name of the data types. So, if you understand the purpose then if you shift the DBA then you will find the tiny int the small int or the tiny int and like that. You will find such differences. For example the integer types have different data types. The integer will be able to read the language course that such a number without decimal point is called an integer. So, the type of integer in the different data types in SQL Server is a bit. A bit is a data type which stores 0 or 1 in a single bit. I have said that these are all they can store the value without decimal point. They can store the whole numbers only. So, the tiny int will store the value from 0 to 255 which means that it will take 1 byte. The small int from minus 3 to 7, 6, 8 from the positive side 3 to 7, 6, 7 this range. Now, the thing is what is the advantage of this range? What is its purpose? The thing is that you know what is the range of values that a particular data type can support. Now, let's say you have to assign a data type to an attribute, to a field. After that, you will see what data type will be most efficient for this field. One of them is the range. If you decide that this field is going to have the integer type values only. In the whole numbers, there will be only decimal point. So, first you decide it should be of integer nature. Then next thing is what is the range? For example, if you see how the difference is how the difference is like a tiny int. Tiny int takes 1 byte of storage. If you store it in memory, it will take 1 byte on the hard disk. If you do a small int it will take 2 bytes. If you say int it will take 4 bytes. If you say big int it will take 8 bytes. Now, obviously, a situation, a place where you have the range of values can be up to 30, 40, 50. You say, I give the flexibility to the field. I give it the maximum space so that the range becomes much larger. It is beneficial that you can enter any sort of value into that. But it is wrong from certain purposes. The first thing is that the range control will end here. Because there is an age. If you are going to store the age of a human being the age of a human being will be between 1,000,000 and 1,000,000. No. Obviously, to make the storage more efficient, it is better to give it a tiny int. It will take 1 byte in a field. If you call it big int in the other extreme, it will take 8 bytes. It means that every value you are wasting is 7 bytes. This will make the data types crucial. The type of value a variable is expected to store an attribute or a field is expected to store. According to that, the data type should be given. For example, a tiny int. A tiny int can have 0 to 255 values. But the thing is, even 200 is not a valid age for a human being. If you are a student or an employee, you are storing it. Obviously, you have a bit that can take 0 and 1. The next possibility is a tiny int. The question arises that it is still a problem that we have 0 to 255 but if we had to do it by mistake but we had to do it by 250 then this attribute will accept that value. The answer is there is a next step of imposing the constraint or check. You can also put a range check on it. So, you declare it as an age and you put a range check on it. The range of its value is maximum up to 90. If you take an example of a student, it may be up to 40. So, you have to decide these things together so that your implementation is as efficient as possible. Decimal or numeric This data type is used to store the fixed point real numbers. You can see that the real number is the decimal point 3.5, 4.6, 8. These are real values. Integer is against because there is no decimal point in integer. Normally we represent the real numbers in 2-3 ways. Fixed point is that the decimal point is fixed and you store the number in the form of whole number, dot, fractional part. But the floating point is that the real number with the decimal point is stored in terms of powers of 10. The advantage is that the range of values increases a lot in the floating point. The difference is that the precision is not as much as you want but the range increases a lot. This is the range of 10 to the power of 300. So, you have decimal or numeric. This is for the fixed point numbers and the accuracy is up to 4 digits after the decimal point. Apart from this we have data types in the range of text type in SQL Server. The basic purpose of text is that it handles textual data. Textual data means that any type of text can have alphabets digits special symbols like star characters in your character set can have text in it. You will know about this. The different flavors of text type in SQL Server are characters. If you declare a character then by default it is stored up to 30 characters. The maximum range of character type is 8000 characters or alphabets Varchar means variable character. The variable length is handled in text and the minimum length is 30 but the maximum length is 8000 and when you declare a variable in Varchar the idea is that the longer the length is the more it will be stored. If you declare 80 but the particular is 50 then it will use 50. Text is a type and this is your variable length and it adjusts its length automatically. With this along with Varchar and text you have 3 more types n character, n Varchar and n text. The behavior of these 3 is the same which is of character, Varchar and text. The difference is your data types they take as the character set unicode The underlying character set is unicode which means that they accept a much broader character set as input. When your character, Varchar and text are simply non-unicode text it can be S or S, but it is not unicode. The possible characters which you can enter in these 3 types that is relatively limited as compared to unicode. After that to handle monetary data there are many types of data types a small money it will store up to 6 digits in the whole number and up to 4 decimal points it will give you accuracy from 99999 up to 6 signs and up to 4 decimal points it will give you accurate value. If you say money simple then up to 15 digits you can handle this big value up to 15 digits and accuracy after decimal that is of 4 digits. In floating point as I told you the real value that is with the help of powers of 10. Float is relatively smaller range is up to 31 10 to the power 31 but the real range goes up to 10 to the power 300 plus. This is how you fix and mind it accuracy is not necessary up to this big value. In this small date time and date time as we had small date time accuracy gives you seconds but date time gives you more accuracy up to small scale. Dear students till now these are the types of data that we generally use for understanding. Other than this SQL Server also supports data types but we don't need their current state when it comes and when we need it I will tell you that this type of data type is enough for our use. Because this particular range of our particular session we are discussing basically SQL. When we want to read SQL we have to implement it the commands we will see rather we have to implement those commands on something. For that we need to have an example database. Now for the example database I will refer you back to our conceptual database design phase. In that the design that we produced as a practice session as an example there we will use it here. I will refer you back to that after that we will transform it into the relational design. Then we will check its normalization and after that we will try to implement it considering the decision during the physical data design as well. So we will go to this stage and see our example database. Dear students this is our example database and that is for the exam system of any educational institute or rather an institute offering the education at the higher level. Right. Graduates post education level is giving you education. Look at our entry types as you have already seen for clarity I have not included the attributes it is just the entry types and the relationships and their coordinates. Now student program as you can see is one to many because one student can enroll in a program and one program can enroll in a lot of students. After that see the program and course there is one to many again because one program contains many courses and one course is included only in one program. And I have already mentioned that as I said that database if database course is being offered at both the BCS level and the MCS level that would be stored twice the code would be different. In the same way we are considering and why are we doing this? Because our organization said that that is why we are doing this. This is not something that we have defined ourselves rather we had a system that if the same course title is there is definitely some difference in the contents. The contents are the same even but still if they consider the course is different then we have to treat them as different. The contents are related and there are many courses in a program. Then we offer some courses from the courses. There is a relationship between one to many because one course can be offered many times in different semesters. So this happened and basically this was our relationship which we have shown here as anti-type. After that we have a student who can enroll in a lot of the semester that a student who has a lot of course offers can be linked and a course offer enrolls in it. This is the relationship between a student and a course offer. This is basically an enroll a relationship. Then you see that courses are offered between a teacher. A course offer that is assigned to one teacher and one teacher may be allocated many courses. This is our diagram which I have briefly explained to you. Other than this semester and courses offered is also a many to many relationship. This means that in one semester, there are many courses offered and one course is offered in many semesters. For example, if you see this is a database course. Today a class B.C.S. offers a database course in 6. Now B.C.S. 5 and B.C.S 6 will come. This course will be offered again. It means that in a running institute, the same courses are offered almost in every semester. That is why many to many relationships have been shown here. Dear students, this design in general consideration and simplicity is correct. On one side, definitely I would like to point out that the relationship between course offered and semester If you see that if you see that a student has taken which courses. Obviously, you have this relationship between student and course offered. This will have a student's ID and course ID. This student has given this course. This relationship will be our table. The relationship between course offered and course offered. The table will be the primary student ID and course offered. But the problem is that first, the student gets failed in a course. But still it happens. The student fails in a course. Now, obviously the student will have to take that course again. If he takes that course again that means that the student's ID and the course ID will again come in this relationship which we will create in this relationship. But the thing is that the student's ID and course ID are primary. So you cannot repeat the primary. Obviously, this is a problem. So it will happen that either the first time you enrolled and then you delete it. The second time the primary key is to add something else. Otherwise, the second time the record will not be entered into the database. Because the duplication of the primary key is not allowed. So now, there are two solutions to this. One solution is that your student and the courses together the key of the student ID and the course ID add something else. Let's say serial number. Let's say that student S101 course C1035 and 1. It means that this student is giving this course the first time. If he gives the second time the second time it means that he has been filled before in this course. One solution is this. But another solution which we have taken which will make this design more beautiful is that you make a ternary relationship between student, course offer and semester. All three types will be involved in this. And the primary key will be the primary key of this relationship. This means that in this primary key which will be the enrol table of this relationship the primary key that would be consisting of three attributes. The primary key of the students the course offered and the semester. The benefit of this is that the course was offered in the semester and you said that the student's ID, course ID and semester ID will be unique. With that you enrol the student. God forbid if the student fails when the next time the student enrols the student ID will be the same. The course offered ID will be the same but the semester ID will be changed. In this way it becomes a new record. That will be legal Dear students Terny relationship is something that we do not discuss in very much detail when we were discussing the ER diagram The reason is that in the real life with Terny relationship they do not exist they do not face them too much in the real life. They are binary or unary. But still based on this discussion if we want to know how to define the cardinality to a Terny relationship If you look at the different books people adopt different ways One way is that your relationship you consider it as an entity type and after that all three of your entity types are involved in it everyone can see what their relationship is but everyone can have one to many one to many you will see that you will mention on both sides one way the other way which we have adopted in this diagram is that when you have a Terny relationship look at it then you should consider that you are one of the three you should suppose first we have to see what is the cardinality of A so you can see you should suppose if you are one of the three and if you are one of the three then how many instances can be related to A if we say if you are one of the three and if you are one of the three then how many instances can be involved then you should see for B if you are one of the three then how many instances can be involved and similarly for C we have applied this rule in this diagram look at it we have applied this rule in this diagram look at it in this you should see there is a Terny relationship between student, course offered and semester one course offered in one semester one course offered in one semester that one student how many students can be involved in this or how many students will be involved obviously in one semester one course will be involved in one enrollment in one semester so we have closed the cardinality and the minimum is zero and after that it is possible that a student will be enrolled in one semester then we have optioned it two, look for one thing what is the cardinality of semester in this relationship suppose you have one student and one course offered one student how many students can be involved in one semester for how many we have created a ternary relationship, because if one student fails in a course, then he will come in another semester, sometimes he fails again. There are such things, so he will come again. So we don't do more than this. It means that if there is one student and one course, then there can be multiple involvement in the semester. Multiple, that can be related. That is why semester's carnality is many. Similarly, if we see that there is one student and one semester, one particular student and one particular semester, how many courses will be involved? Obviously, one student takes a lot of courses in one semester. That is why I have not shown the carnality here. So in this way, you can do that the ternary relationship and the anti-types involved, you can determine the carnality. And then when we transform this into a relational model, the relationship that you have created will become a table. And all the participating anti-types, the primary key of all of them will become the composite primary key. One technical point, note that there should be at least one of these involvement in the carnality. Then it is possible that the primary key of all three will become the primary key of this relationship. Again, in a ternary relationship, the anti-types involved, there should be at least one of these involvement in this relationship. Even then, it will be possible that these three primary key will become the primary key of that relationship which is being created against this relationship. Otherwise, if I am not the three of them, then it will not be like that. Then you need to add some other attribute, as I discussed earlier, then you will have to add something else. Let us move on. On this slide, you can see that here we have transformed the same conceptual database design into the relational database design. In the ER diagram, I did not show the attributes because the diagram would have been complex. In this slide, you can see that all your relations have been shown here and all the attributes. In this slide, you can see that the program is the same program anti-type against course, anti-type against semester, courses offered in the same way. I told you that course offered is an associative anti-type. It is a relationship, but it will be treated as that. And one of its attributes, the faculty ID, is working on it as a foreign key. In addition, we have an enrol. Enrol is created against this third relationship. This relationship is made from there. The primary key of enrol is a composite key. It consists of three attributes, student ID, course ID, semester name. All three participating anti-types are the primary key. In addition to this, the further descriptive attributes are the midterm marks. The sectional marks, final marks, total marks, grade and grade point. These are the attributes of enrol. If we look at the diagram, we have a relationship between student and semester. This is a many to many relationship. Against this, we have created a relationship. It is called semester result. In this, student ID and semester name. These are the primary keys of the participating anti-types. The composite primary key is created. For the result of the semester, which will come specifically from enrol, we will take a semester result from enrol. How? First, we will take total credits. How will they come? They will come in such a way that we will know from enrol which courses we have enrolled. From course to course. After that, we will know from course the credits of this course. Look again. After enrol, a special student has enrolled which courses. The table of course is written there as course credits. After that, we will know the credits of this course. This way, a student has enrolled all the courses in a semester. When we sum them all up, that will be total courses. They will come in total credits. All the credits of the courses will come in total credits. All the enrol courses will be summed up here. Then, total GP. Where will it come from? It will come in the enrol. The sum of all the credits will come in total GP. And then, the credits and the GP. Using these two, you will calculate the GPA. Because degrees normally consist of more than one semester. So, it means that one student will have multiple semester results. Each semester has a GPA in this table in the semester result. Now, if you look at the student in this field, it is CGPA. That is cumulative grade point average. The CGPA will be calculated from those records of students that are there in the semester result. For example, if the student is in the third semester then in the semester result there will be two records, previous two semesters. So, those two GPAs you will calculate the CGPA and that will be stored in the student relation in the CGPA attribute. I hope that this design and the relations in it will be understood. And you should also pay attention to your slide when it is uploaded. And look at each table and look at the purpose of the attributes. Dear students, next step is to check the normalization of these tables. Now, I have not given the FDs specifically. But as I said that we are implicitly doing normalization. So, you take it for granted that this is a normalized design. We do not have any partial dependency on it. You can analyze it yourself and there is no transitive dependency on it. This is the design in the third normal form of BCNF. Let us move on. Broadly speaking, SQL commands can be divided in broad classes and categories. Data definition language is called the DDL Data Manipulation Language DML and Data Control Language DCL. The DDL is used to define the database DML to manipulate or DCL to control the data. The DML is used to create alter or draw tables or other database objects. You want to create database, table, views, users. For all of them, you use DMLs. When you want to change the structure of the database you want to add an attribute to it you want to change the structure of the database for that you use the DML commands. For using the DML commands one methodology is that you use the SQL commands which we will study here. Apart from this, almost every DBMS provides a graphical interface tool by using which you can perform these activities. For example, if you want to create a table if you use the SQL for that you will use the create table command. But the second possibility is that you can use the SQL server if you use access oracle. All of them are giving you a graphic interface which you do not need to use the SQL command. You give different instructions and the table is created automatically and the activity is performed automatically. The tools are not only easy to use but they also give you more features. But still, as a database expert, as a database professional you must have a very good command on SQL. Dear students, today's lecture we will conclude here. Basically we have entered into our session of the SQL. Today I have explained to you how do we read the format of an SQL command. And these format rules do not only apply on the SQL but rather they apply to any language. They are very general. Apart from this I have explained our design which we have to use during our SQL practice. And especially I did not discuss in detail and that was the ternary relationship. The treatment I have explained to you after that I have shown how we have transformed it into the logical database design and I have told you that it is normalized. The next lecture we will create tables and we will implement some of the physical design of the database. And after that we will use different queries of the SQL. I would like to leave here. See you in the next lecture.