Skip to main content
Loading Explore guides…
Foreign Keys · Acedra
Home
Explore
Computer Science
Foreign Keys
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
68 segments
Copy
Hide video
0:16
So in order to talk about referential integrity constraints we introduced the notion of a foreign key. A foreign key is something in a scheme which is a group of attributes which would be used to refer to tuples in other relations. That is what we call a foreign key. And the referential integrity constraint basically says that this kind of referencing you know, should be to existing tuples in the in the database instance, and they should
0:51
not be dangling references okay. So if there are any questions in that, we can take it up. In this class, I am going to give you a set of an example database, an example relational database. And I am going to kind of continue using this example throughout this module and also in the SQL and other later modules. So it is going to be important to clearly understand this relational ahh database scheme and we will keep it. I suggest that you write
1:31
down this the just the relational relation and names of the attributes separately in a sheet of paper. So that it is accessible to you when we are discussing ahh queries and SQL and things like that okay. So ahh okay so let us go ahead ahh with this. So we have so this this database is is to capture the academic details about a ahh ahh in institute which has a number of departments and and students studying in the departments for various degrees. So, we
2:09
have student ahh we have student relation which has these are the following attributes for the student relation. We have roll number, name, degree, years, sex, department number and advisor. So, here the the conventional relational schemes when you write down relational schemes is to underline the the key attribute. So when an attribute or a group of articles written together are underlined, what it means is that it is a key okay. So roll number is is obviously the
2:48
the key for this particular. So there are multiple case then there will be multiple groups of attributes which are underlined in the relational scheme. So roll number is a key for this student relation because attribute the value of roll numbers can be used to uniquely identify a particular row or a particular tuple in the student relation, right. And so, name these are obvious, ahh you know, degree is a is the program, the
3:17
value of the degree attribute is is the strings that stand for the various programs that the student might have been enrolled for. So, so what are the degree for which he has joined the institute and the year is the year of admission and advisor advisor attribute is the employee Id, employee ID of a faculty member identified as the students advisor okay. Also all students required to have an advisor, so there is a advisor attribute for student which which will be which will take
3:53
values that are from the employee Id domain of employee Id of a faculty member. So that way you will be able to identify as to who is the advisor for the student. And of course, if you want to know more details about the students and advisor, then you have to look up the appropriate other relation. So, and here is the other other relations, the there are actually some more in the next slide. So department is another relation. It has department Id, name, hod, and phone.
4:30
So phone is is the department's office phone number, and hod is a faculty member who who is the head of that particular department who is kind of academically responsible for the running of the department. And what is the name of the department and what is an ID. So, we give some numbers for the department so that they are easily identifiable and serve as a key. In this case, even the name could serve as a key. But we choose to have a department Id which is a number. Now, professor, ahh employee
5:08
Id because professors are all employees of the institute. So, there is an employee Id and name, sex start year. So, start year is the year when the faculty member has kind of joined that particular department. So, all all faculty members work in departments. So, what is the department in which particular person is employed is given by the department number and then phone number of the particular professor. So, these are the various attributes we have for professor relation okay.
5:45
Now, next one is the ahh other important things that we need to keep track of course, a course is a is a subject of study, right and it is it has been named, it has been given a name c name, we call it it to just to ahh not confuse it with other names. So C name is the course name. And each course has certain credits, right be the number, the number of credits that you earn if you complete that course and then department number indicates the department that offers that particular course.
6:26
And, and then the course Id. So we use a course Id to kind of identify the courses so to like, act like a key for the course relation. And then we have the issue of roll numbers. Sorry, the issue of enrollments, enrollments. So students enroll for courses, students enroll for courses. Now in this case, we we see that there is a need for you know taking a key, which consists of 4 attributes, the reason being that you know, course Id alone cannot be, you know, roll number and course Id.
7:12
A student typically does several courses in a particular semester. And so ahh and of course gets offered course gets offered multiple times across the years, right, say so for example, you are now doing CS3700. The same course would have been offered last year and it will get offered next next next year and so on so forth. And so the year in which it is offered, and the semester in which it is offered, that is the one that is you know,
7:45
in which a particular student is enrolling and obtaining a particular grade. So because of this reason, you need roll number, course Id, semister in which this course is running, and the year all of them to be part of the key for this relation. And what we will assume here is that the same attribute star stands for semester, and it has the value either odd or even standing for the ahh the August semester and the the January semester
8:22
okay. So, so we are in the odd semester now, though there is actually nothing odd about the semester. Anyway, so the valuegrade will assume that the value grade will have null for the current semester. And then we will have appropriate values after the semester gets over, okay. And so for all the previous semesters, there will be some value for the the grade. So so that is how this is. Now so you can see that the enrollment, the the the issue of how students,
9:07
you know, enroll into courses and then applying grades is all captured by this enrollment relation. Then we have the from the faculty side we have, we want to keep track of who teaches a course, who teaches a course. So we have ahh a teaching relation, teaching relation, which gap basically captures that a particular employee, a particular professor is teaching a particular course, in sem in a particular semester and a year okay. For for example,
9:43
yeah, so, I am teaching the CS3700 course of the odd semester of 2019 in the classroom, CS25 like so that is one. So naturally you can also see that the need for all of the attributes to be the key here because no subset of this will be able to uniquely identify a row in this particular ahh relation and employee Id typically employee are typically teachers several courses across the that is you know employment and so, obviously, that itself is not a key for this and course
10:27
can be taught by several professors. And also so, that itself cannot be the and then you know, obviously, you need to have this semester and year only then you will see that a particular edition of the course is being talked about and so you have the unique way of capturing that particular topic okay. So, the identifying the keys are depends on the the domain that we are modeling and so in this case these are the keys. Now, finally we also have prerequisite relation which basically
11:10
has 2 attributes called preReqCourse and course Id. And here so, if some tuple C1C2 exist in this particular relation, what it basically indicates is that C1 is a prerequisite for C2, C1 should be successfully completed before enrolling for C2. So, that is the meaning of a prerequisite course. So, for example, data structures is a prerequisite for database systems course. So you should have completed data structures
11:47
before you take database systems course. So there will be a tuple like that here okay. So I hope the relational example relations scheme ahh is kind of clear. So we will use this relation scheme in this module and also in the SQL module. Now, I will show this entire relation scheme in one slide. So that you know we have it all in one place and now actually we will show what are the in the foreign keys here, and where are they, what are they referring
12:27
to okay, well let us identify the referential integrity constraints and the foreign key and then how with what a foreign key is refer to. So you can start identifying lots of these in each of these relations there are foreign keys. So for example, here department number is a foreign key right. Because this is the one ahh that we will use in the student tuples to kind of identify what is the department in which that particular student is available is is present. And then,
13:05
you know, we can use this to kind of link up with the appropriate department tuple okay. So to refer to the computer science department tuple, so we will use the computer science department Id and then put it here, right. That is how we refer to to the. So to indicate this we have. So, those things will come up now. So, I will now indicate them by using these rows. So for example, hod ahh is a ahh foreign key in the department
13:42
tuple ahh department scheme. And it is it refers to the employee Id of the professor. So, so one, one of the professors is the hod, and so you have the employee Id of that particular person sitting here. So, and that can be used to refer to the actual professor who is the the hod for that particular department. So, ahh you can see that this attribute has the you know, the qualities that we are looking for a foreign key, which basically is a bunch of attributes that we use to refer to tuples
14:21
in other relation and you know and the constraint here is that it either it should be null or it should should have values that are existing in the other column of the other referring the relation that we are referring to. So likewise now we can identify, so, so prerequisite course and course Id are both are both foreign keys referring to the course Id right. So, there are some more ahh one more one one one
15:02
thing that you will notice here is that the same the same attribute name the same attribute name might occur in multiple relations okay. And if there is a ambiguity as to which course Id attribute are we referring to we will use the conventional dot notation to disambiguate that name. So, if you say ahh course dot course Id then we are referring to this attribute attribute, if you say employment dot course Id then we are referring to this particular attribute
15:39
like that and also the same you know kind of quantity is actually being referred to by 2 different names like for example, here department number and department Id, you know, we know that they are, in some sense the same kind of thing. So, but then we give the freedom saying that you can use whatever name you want, but then this is a foreign key that refers to the primary key of the other relation okay. So, yeah so
16:16
even in such situations it is not compulsory that now this particular attribute name should be the same as this particular attribute name. That is what I am trying to emphasize okay. Now, let us identify some of this employee Id, obviously is a foreign key refers to ahh professors. And then roll number obviously, is a foreign key in in the in the enrollment relation, referring to student. So even though these are part of some keys and all that they can
16:56
independently be foreign keys department number is a foreign key refers to department Id. Then this department number here in the professor is also a foreign key reference department. Course Id here ahh is a foreign key referring to that. Obviously here also course it referred to. So this is also as a foreign key referring to that and then yeah, here advisor is a foreign key that refers to the employment Id. And I suppose that is all okay, okay. Department
17:36
number here in the course refers to the department Id remove, okay, that is it okay. So this diagram is what we traditionally call as a relational schema diagram, in which we have the relation names, attribute names, keys are underlined and then we have arrows to indicate the the foreign keys and what are the ahh foreign keys referring to, what are the relations they're referring to. So, this this particular slide, I want you to you know, note down in a separate page of your book.
18:24
So that you can refer to this particular diagram when you are talking about queries in either relational algebra or SQL, have it handy so that we can discuss this okay, any questions about this. So, I think, feel clear okay. Now, let me introduce, start introducing the ahh next major topic in this module, which is the topic of relational algebra. Now we have seen the relational model ahh relational algebra is part of this the whole relational approach to databases.
19:13
And is a ahh basically a set of operators set of operators that take relation instances as arguments and return new relation instances, new relations okay. So obviously it is an algebra, so it works on some domain of things. So he the the things, it works on our relations. That is why it is a relational algebra. So it takes relations and produces new relations. So each of the operators here are like that. So we have a bunch of operators, both unary and binary, that take relations since this
19:52
is arguments and return new relations. Now, what we will see here is that using these options operators will be able to express the the retrieval request will retrieval see database has a lot of information. And we want to retry certain information from the database right. So how do we express your requirement, how do we express your query, it is called query in the in the database parlance, we call it a query.
20:27
A query is basically a data retrieval request, we want this kind of data, this is what we want from the database to be retired. So how do they express that. So, so relational algebra operators give us one way of expressing what we want actually. So but interestingly you can also see as we go along that this is a procedural way of expressing what we want okay, a procedural way of expressing what we want, that means it kind of says that okay.
21:06
You do this operation here on this relation, do another operation on other relation, combine this like this and then combine it with something else and then give me this okay. So, when when you have an algebraic expression, you can think of it as a as a tree right. And so, you can think of information flowing up like that finally, the output comes at the root in some sense, we will we will understand this as we go along and then discuss the queries ahh.
21:39
So, relational algebra forms the core component of the relational query engine the relational query engine ahh, which is the heart of the relational database system, ahh uses these relational algebraic operators and efficient implementation of these operators. Right now we are in this module we will not really bother others with the implementation details about these algebra operators, we will think of them as as logical operators.
22:14
And will confine ourselves to the conceptual understanding of what is operators, we will just think of them as mathematical operators and then see how we can make use of this operators, later on of course, we need to we need to study as to how to exactly implement these operators, what are the how do you write a program to to realize the operator okay, and and those programs will go into the or they will all be available in the in the query engine okay.
22:51
So SQL queries so we will study SQL in detail and SQL ahh a declarative way of specifying queries, a largely declarative way of specifying queries, we will see that later. And that is internally translated into rational algebraic expressions as I was mentioning earlier also. And because of that it kind of provides it provides us an opportunity for ahh optimizing that relation, optimizing the way that particular ahh query gets actually executed okay.
23:30
So we will see all this in this particular set of slides. So what are the operators. It is surprising that we have just about half a dozen ahh you know operators, actually, we will see that the minimum set of operators is you know, we will come to that little later. But right now we just have 1 2 3 4 5 6, just about 6 7 operators. And, and if you feel understand these operators, this kind of sufficient for expressing all kinds of queries on relational databases.
24:04
So, so that is the power of this particular up the system of the algebra. So we have select ahh project which are unary operators and cross product operation, the union intersection the difference and you can also see that actually not all of them are really required, you can express some of them in terms of the other. So let us say union intersection and difference and then join operator. We will later discuss as to what is the minimum set of operators and things like that okay.
24:40
So let us start looking at these operators one by one and understand the meaning of what are those operators do, ahh for it is not necessarily that you should be familiar with SQL at this stage of the course. But if you are familiar with SQL, ahh do not confuse the select operator if you are going to talk about here with the select clause of SQL and the select clause of SQL actually corresponds to the the project operator okay okay. So
25:11
but even if you do not know SQL at this stage, it does not matter okay. So let us start with the select operator, it is a unary operator. And basically it can be used to select those tuples in the relation instance that satisfy a particular given condition okay. So it has a parameter which is the condition and what it does is to select all those tuples in the in the relation instance with satisfy a certain condition, the given condition.
25:48
And the notation here is to write it as sigma theta and the relation operator the input religion. So theta is the selection condition, ahh sigma is the select operator and r is the relation name. So it is a very simple operator. So, it basically takes a condition and then and then returns all the tuples in this relation instance r that satisfy that particular condition. So, it gives a new relation instance it gives a new relation instance and then it is easy
26:34
to see that the the scheme of the new religion instance is the same as the scheme of the input relation right. So, result is a relation with the same scheme as r, the same set of attributes, same sequence of attributes, same order of attributes as r consisting of those tuples in all that satisfy the condition theta and the operation is commutative. So, if you write sigma c 1 of sigma c 2 of r, okay notice that we can compose the operators and then get
27:18
algebraic expressions right. And that is the whole idea of algebra right. So, you could sigma sigma c 2 r is now a new relation. So I can now use that relation and then make another selection operation, right. So sigma c 1 is ahh and then inside that I am giving this new relay this relation, which is a result of this particular doing selection with c 2 and r. And you will notice that this is actually same as doing sigma c 2 on sigma
27:58
c 1 r. So if you are first selecting those tuples that satisfy c 1 okay. And then from there you are selecting the tuples that said c 2 is the same as doing first the tuples as c 2 and then choose from among them. So, basically the tuple sets were both c 1 and c 2 and so the order does not really matter and so, this operation is actually committed. So, it is this kind of identities involving this algebra operators that will
28:34
later help us I know in rewriting these ahh rewriting these algebraic expressions. So that it will it will be advantages for us to actually run the query in a efficient manner okay. So that is the simplest ahh operator select. Here are some ahh okay, let us go into the details about what exactly is the selection condition. So, the selection condition can be either a basic condition or a composite kind of condition, the basic condition ahh will can be either of these 2 forms ahh. So,
29:20
basically when you take a upper attribute and compare it with another attribute compared with another attribute or what are these attributes, where are these attributes from, obviously they are from the the relation scheme that is on which it is being applied, the relation on which it is being applied. So, these A I, A j are the attributes of the relation well, that is the input for this operator. And so, you compare these those 2 attributes, somewhat a pair of attributes
29:54
or you compare an attribute with a constant appropriate constant each each of these attributes come from a certain domain. And so you have if you take a particular string you you cannot probably compare it to the integer, but you will compare it with strings right. So, so, appropriate things and appropriate constants, constants of appropriate type. So, these are the kind of atomic kind of conditions, simplest of the conditions, the simplest of
30:24
the conditions will involve comparing an attribute with another attribute and or comparing an attribute with a constant. So, these are the simple conditions and then you can combine these conditions with the the logical operators, the logical operators are and or not are all allowed. And then make a complex condition or composite condition, we call it I am calling it as a composite condition. So, basic conditions combined with logical operators, the and these
31:00
are familiar to us, ahh we have been using them. So, earlier courses also, so and or and not that we used and so. So, all of them of course, are Boolean value right. So, this is this is a comparison operator is A 1 greater than A 2, either it is true or false right. So, these are Booleans ahh so so, they can be combined with Boolean operators and or not and then you can now get a composite condition and that is how you will use. So, so, this
31:36
theta ahh is of this form, the theta the selection condition is either a basic condition or a composite condition. And if it is composite, it basically uses logical operators and then combines this atomic conditions and the what are the competition operators. The commercial notice the operators are the obvious ones here are the less than less than equal to greater than greater than equal to and equal to not equal, the six possible comparison
32:12
operators which compare values basically they compare values and of course, we will. So, in order for ahh the we will assume that if you are applying this comparison operators the corresponding domains are you know linearly ordered sets like that. The order relations do exist on those attributes only then it will make sense for us okay. So, c is a constraint of appropriate data right okay. So let us start using this condition and then express a few data requests.
32:53
So let us look at some examples select expression. So obtain information about a professor with name Giridhar start with that, there may be several Griridhars in the institute. So so now we are saying seek name is a attribute of professor relation. So, this information is there in professor relation, the details of professor obviously or the professor relation. And so what we are saying and we want information that tuples that have Giridhar as the name.
33:29
So we are putting a simple condition here, name should be equal to the string Giridhar ahh in this relation professor and then obtain all those tuples one. So if there are multiple Giridhars, they will all be listed as. So the entire information about the professor tuples will be, so this ahh now this is now stands for a ahh a relation stands for a relation, which has the same scheme as professor okay, but contains only those tuples in which the name is exactly Giridhar this thing okay.
34:14
So it is a subset of the original set of tuples. Now, we have not had given a name for that particular result right. It can, we have to give a name in case we are using this ahh this relation that has been produced by this particular expression later on in some other context, we may actually want to give a name for it okay, so we will see that later. So obtain information about professors who joined the university between 80 and 85. Both inclusive, some numbers okay.
34:55
So there is a start year attribute in the professor which says that when the professor has started working in that particular ahh institute or department to be specific. So, here we are saying so, I I use this short way of you know expressing 9 ahh and and start here is either get an equal to 1980 or less and and it is actually less than equal to 85 between exactly between these 2 things. So, this will contain the all the professor
35:28
tuples, all the professor tuples whose start here lies exactly between 1980 and 85 inclusive. So now, these these are typical data data retrieval requests. And we can see that we can express them using this particular operator. So, moving on ahh let me also introduce project a project operator, the project operator while the select operator who actually working on the tuples and then choosing a subset of these tuples, what the project operator will do
36:06
is to help you to kind of get rid of unwanted columns in relation. Supposing you want a relation is pretty wide, and you want to focus on a only particular attributes. And you do not want to really look at all the attributes of the relation, then this project operator will be useful to you okay, so what project of project operator does ahh is to keep only the required attributes of a relation instance and throw away others
36:39
in some sense. So I do not want to, you know, other information so I will just throw it away okay, so here is the notation pi is the ahh symbol that we use for this operator pi. And here is a list of attributes that we you want a list of attributes we want. So obviously this and then this is the relation on which we want to apply that. So, obviously each of these A 1 through A k is part of the scheme of r okay. So ahh they need not be consecutive
37:19
ahh anything like that. So, some set of attributes from the original scheme which were interested in. So, the this L list L is the desired attributes in the scheme r and exactly what is the output or what is the result of this particular expression is given here. The result is the set of tuples v 1 through v k matching all these A 1 through A k such that v 1 belongs to the domain of A i okay for each of these case, and there is some
37:54
tuple t in r some tuple t in r. Such that t. A 1 = v 1 t. A 2 = v 2 and t. A k = v k okay. There may be multiple tuples actually. But as long as there is some tuple ahh in t such that this condition is satisfied, we take that bunch of values v 1 through v k ahh and then put them in the in the output okay. So, so basically remember that the result of a particular expression is a relation and what is our definition of a relation, it is a set of tuples it is a set of tuples, okay.
38:48
And so it will not have duplicates. It is a set. So there is no scope or duplicate elements okay. So that is why even if you let you know, there are multiple tuples that have these particular corresponding values v 1 through v k for these attributes A 1 through A k only one only one sub tuple of this v 1 through v k will exist in the in the relationship okay. Let me illustrate that ahh. So before we go further ahh, so if r r 1 is the new, you know,
39:26
relation name we are introducing and then assigning it as this one pi l r 2, then basically we are calling this pi l r 2 as this new relation r 1, then the scheme of r 1 is l. The projection list the, what is this scheme of the output, the scheme of the output is this projection list, right. Because that is what you are projecting. These are the attributes that you have chosen. The remaining attributes around being thrown
40:00
away. So it is kind of obvious that the the, you know, schema for the output is this projection list. So, here is the example, just look at this slide ahh student. So, we have Mahesh ahh in computer science department and there is a Mahesh in the mechanical engineer department, both of them are doing M.S etc. And so now you do a sigma degree M.S degree equals M.S on student. And project mean okay. So now degree M. S. So who So, sigma degree M.S on
40:57
student will give me this tuple, this tuple and the last tuple the 3 tuples. Because all of them are enrolling the M.S degree. Now, if you do a project name alone without projecting roll number, what is it means is that I I only need the column name or attribute name information of this particular relation and do not do not want anything else. So now if you do that, then you will actually lose information because only one Mahesh will exist, and the other tuple contribution Rajesh.
41:41
And that is it, you would have actually lost some information. They had to be careful about how to use it, but this just illustrates the the idea that if you have a desired set of attributes and you project that you get information corresponding to that. If you do roll number name from the student. Supposing I just want to ahh get only the names of the students I am not really bothered about degree and all the other details, then then you would do pi roll number name on on student.
42:20
And then you will see that you will get in fact you will get exactly the same number of tuples that are there in the student because ahh because there are no there cannot be any duplicates in this particular combination of roll number and name because roll number is actually a key. Since roll number is a key ahh you will get all these tuples okay. So, so in this basically in this lecture I have been able to introduce just these 2 operators, the selection operator and the project operator.
42:58
We will just close with one particular ahh point that if r1 is pi l r 2, then then what about the number of tuples in r1, the number of tuples will depend whether the projection list contains the key or does not contain the key. If the projection list contains the key, then the output will be the same, the size of the output be the same exactly the same as the input, otherwise it can be less. I wanted to list, we in the previous example
43:35
also, it is clear. So, with this will stop today, I will proceed with the rest of the operators in the next lecture okay.
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat