YT YouTube2Text

YouTube2Text → Transcripts → Introduction to Relational Model/1

Introduction to Relational Model/1 — Transcript

by Data Base Management System - IITKGP · 4,615 words · 882 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:19welcome to
0:21module 4 of database management systems
0:26in the last two modules ah we have
0:28introduced the basic notions of ah
0:31dbmss
0:34in this module and the next we
0:37make an introduction to the relational
0:39model which we said is a major data
0:42model that we are going to use
0:46so this is a
0:48what we did in the last module
0:51and
0:52in the current one our objective will be
0:54to understand
0:57key concepts of relational model that is
1:00attributes and their types
1:03the basic mathematical structure of
1:06instance schema and what is known as
1:10keys to familiarize with different types
1:14of relational query languages
1:18this is the
1:19module outline
1:21that we will follow
1:23so
1:25we again
1:27repeat this example from past
1:30this is an example of instructors
1:32in a university a table of instructors
1:36given by attributes or columns
1:39id name department name and salary these
1:42are the four columns the four ids
1:44and multiple rows which are specific
1:47rows or we often refer to them as
1:50tuples
1:51so you can say that since there are four
1:54attributes that is
1:56every row has four columns
1:58so this is a fourth tuple that we have
2:01and such a table
2:03is called a relation
2:05is as simple as that so this is it
2:08whenever we talk about a relation
2:10we have a number of
2:13fields number of attributes number of
2:15columns whatever way we say
2:18of a table
2:19and that table according to those
2:22columns it has
2:24multiple 0 1 or any number of rows
2:28of values filled in and that is what is
2:31a relation
2:33now
2:34so let us look at attributes more
2:37specifically
2:38so
2:39attributes
2:41each column is an attribute as we said
2:43this
2:44every attribute has a domain
2:47the domain is a set of possible values
2:49that attribute can take so if you just
2:51look into the example
2:53here so i am trying to define a
2:57table
2:58having different students
3:01so
3:03there is a roll number for a student
3:05there is a first name last name the date
3:07of birth dob
3:09the passport number the other card
3:11number the department to which the
3:13student belongs and so on so let us say
3:15this one two three four five six seven
3:17are the different attributes
3:19now if you look into every attribute
3:21then
3:23every attribute has a set of possible
3:25values of which some value is entered in
3:28a particular row for example the roll
3:30number is an alpha numeric string as you
3:33can see it has ah numerics as well as it
3:35has letters
3:37whereas the first name or the last name
3:39are simple alpha strings in fact we can
3:41also say that the roll number actually
3:43is not only alpha numeric it has a fixed
3:45length it has a length of
3:47here it says length of nine so you can
3:49say alpha numeric strength of length
3:51nine are eligible for
3:53being values of this domain there could
3:55be more restrictions but
3:57that the domain will be certain
3:59collection of values
4:01which are possible
4:02as values of that attribute
4:05when you talk about dob that certainly
4:07has to be a date
4:08so its written in the form of
4:11d d m m y y y y that is two digit date
4:15three letter month code and four digit
4:18year
4:20the passport number is a string
4:23a letter followed by seven digits
4:26the other number is a 12 digit number
4:28the department is alpha string and so on
4:30so
4:31the domain
4:33is a set corresponding to an attribute
4:36which define that all possible values
4:39that attribute can take
4:42ok now ah
4:44these attribute values if you look at
4:47they are
4:49atomic in nature that is you cannot
4:51divide them into smaller parts
4:54so what i mean is say when we are
4:56talking about date of birth
5:00the whole date of birth type the date
5:02type is one atomic value
5:08for example if you were to code this in
5:10c what you could do you could
5:12ah possibly create a structure with
5:14three fields one is a date one is a
5:16month one is the year and you will say
5:18that this composite record composite
5:20structure is actually my date
5:23you can do a lois type def you can do if
5:26you are working in c plus plus you will
5:28define a class called date which has
5:31these components and as well as
5:33operations with them but that kind of
5:35types are not allowed
5:37in a relational database
5:40it has to be an atomic type so a
5:42relational database will give you a
5:44atomic type called date
5:45where all of these are pre-specified and
5:48has to be taken as one unit
5:52other atomic types are ah integer
5:55ah
5:56like ah we do not have an integer field
5:59here there are strings there are
6:01numerical
6:03values which are kind of floating point
6:05values and so on
6:07now some attribute may have a special
6:12value called the null value
6:14which is
6:15ah the member of its domain
6:18actually every attribute
6:21ah of any domain
6:23can have this special value the null
6:26value is not actually a value it is
6:28actually an absence of a value so it
6:31says that this value is not known so if
6:34you look into the example
6:38above then you see that for passport we
6:40have said that the passport is a string
6:44letter followed by seven digits and it
6:46is nullable which means that in the
6:48passport field
6:50i may have a value may have this null
6:52value which means that
6:54it is not that the
6:56the passport is null
6:58what it is saying is that this passport
7:01number
7:02for this particular student the row
7:04number 2
7:05is not known is unknown
7:08now all fields may
7:10or may not be nullable for example we
7:12will not allow dob to be nullable
7:15data but has to be there will not allow
7:17roll number to be nullable will not
7:19allow first name to be narrable but we
7:21may
7:22allow last name to be nullable its been
7:25been a style of let not to use your
7:28last name many many people just use one
7:31name so you could allow that it is not
7:33known its not there
7:35whereas ah
7:36department may not be nullable it must
7:38be there so null is a very critical
7:40concept and
7:42what it actually does it ah
7:44actually ah creates a
7:48lot of issues ah
7:49and complications in terms of defining
7:52many operations so understanding null as
7:54a value in terms of an attribute is a
7:57critical requirement for the design
8:01now
8:01coming to the schema and instance we
8:03have ah
8:05discussed about the basic understanding
8:07of schema and instance
8:09so understanding them formally now we
8:12say that ah
8:14if we have a schema so its like a table
8:17having multiple columns say there are n
8:20columns having
8:21names a one two a n
8:24then this a 1 to a n are the attributes
8:27so
8:28these are the different attributes that
8:31it will have so ah i am
8:34these are the different attributes so if
8:36i have this then it it basically means
8:39that i have a table where
8:41the these are the columns a one a two a
8:43n like this
8:46ok so
8:50then
8:51a relational schema
8:53is a collection of these attributes so
8:55it is a
8:57collection of
8:58all these
8:59attributes
9:00so we say r is a relational schema which
9:03has attributes a one to a n
9:06now every attribute
9:09a i
9:11has a
9:13domain d i
9:16so for every attribute i have a set of
9:18values that are possible
9:20so
9:21if you
9:22ah
9:24if you recall then ah here
9:28ah we had
9:30different
9:31these are the different attributes
9:33and these are their different domains so
9:36dob is an attribute and the domain is
9:38date so any possible date
9:40other is an attribute and this is the
9:43domain
9:44which is
9:45so
9:46all
9:47attributes
9:48each attribute will need to have certain
9:51domain and those are marked by the d
9:54sets
9:55so we will say that a particular
9:58relation
9:59a particular relation r so r is a schema
10:04schema
10:05so a particular relation r
10:07is a subset of
10:10d one cross d two cross dot dot dot
10:14d n
10:16right
10:18so recall the mathematical notion of
10:22relation
10:23which
10:24say that a relation is basically a
10:26subset of s at
10:28so these are the possible values so the
10:30first attribute can take values from d
10:32one second attribute can take values
10:34from d two and so on and the nth
10:36attribute can take values from d n so
10:39any specific row any specific record
10:42is a set of
10:44values for a one a two a n
10:47and therefore is a member of
10:49this cartesian product
10:51and the relation is a subset of that so
10:54this is every value is an n tuple
10:57which is a subset of this a one
11:00a two
11:02a n this particular record
11:04is an element of
11:07this
11:09cartesian product set
11:12and
11:12are necessarily is
11:16a
11:17set of such
11:20tuples
11:22thats a mathematical view of the schema
11:25and the instance so this is the schema
11:28and this is the
11:29instance
11:31corresponding to that schema based on
11:33the
11:34different domains of the different
11:37attributes
11:38and this is ah the notion that we will
11:41continue using so
11:43ah please try to
11:45follow this carefully
11:50now whenever we have a an instance we
11:54mark that as a table and
11:56every
11:57such
11:58table so here you have now understood it
12:01very well so these are my attributes so
12:04this is a one this is a two this is a
12:06three
12:07this is a four and any one in a
12:11these are the different values a two a
12:13three
12:14a one a two a three a four
12:17nine eight three four five is a one kim
12:20is a two and so on
12:22now naturally this ah it is not ah
12:25visible from the instance because we are
12:27taking an instance view we are not being
12:30able to see what that domain is that
12:32will be visible if we look at the
12:34corresponding ddl the definition
12:37language description of the schema which
12:40master specified id as a
12:43numeric
12:44value the name as a string value the
12:46department name has another string value
12:48where a salary has a numeric value and
12:50so on
12:52now what is uh important to note here is
12:55ah
12:56a relation necessarily is a set as as
12:59you said is ah is a set which is
13:03the
13:04ah as
13:06the relation r is a set
13:10this is a set
13:12which is a subset of
13:14this set
13:16so a set we know the elements in a set
13:19are do not have any ordering they are
13:21unordered
13:23so a relation is necessarily unordered
13:25so it does not really matter
13:28that in terms of ah this collection of
13:31rows
13:32which row is at what position if i
13:34reorder them the relation does not
13:36change
13:37its just that they are a collection of
13:39these set of rules
13:40so that lack of ordering is a critical
13:43information that will have to remember
13:45in mind
13:48next concept is key
13:50so
13:52r as we have seen is a relational schema
13:56which is
13:57a collection of
13:59which is a collection of attributes a
14:01one a two
14:03a n
14:04now
14:06k
14:07let k be a subset of r so it is one or
14:10more attributes
14:12it has to be a non empty subset
14:16now
14:18we will say that k is a super key of r
14:21if
14:23if we consider the values of different
14:25tuples
14:27in the attributes of k
14:31and we find that
14:33there cannot be two tuples which
14:36are different
14:40but match
14:42on these attributes
14:44which mean
14:45that
14:46the values of the attributes
14:50of k
14:52uniquely identify each row
14:56of the
14:57relation
14:59then we will say that
15:01k
15:02is a
15:04k is a
15:05super key
15:07of r
15:10so
15:11the instructor table that you have seen
15:13id is a super key similarly
15:17so k can be taken as a singleton set of
15:20attribute id
15:22or k can be thought of as
15:26the set comprising id and name both of
15:28them are super keys of instructor
15:31now
15:34we say super key k is a candidate key
15:39if k is minimal
15:41so the idea is like this that
15:43this is
15:46a key super key this is also a super key
15:50but certainly this is
15:53a subset of this this is smaller than
15:54this
15:56so we will say this is a candidate key
15:58but this is
16:00not a candidate key
16:02because this does not
16:03satisfy the minimality condition
16:13there could be multiple candidate key
16:16in a relation
16:18if there are multiple candidates key
16:21then
16:22we select one
16:24to be the primary key
16:26now obviously there is a question of
16:27which one we select but any one can be
16:29selected as a primary key
16:32which is the key of the relation
16:36and we will see that in some cases ah
16:39there is concept of surrogate keys
16:43so if i have a relation where there is
16:45no attribute
16:47which can whose value can uniquely
16:50identify each and every row of the table
16:55then i might
16:56synthetically generate
16:58a value for example like a serial number
17:01i can generate a serial number and say
17:04that this is my value
17:07so
17:08that serial number
17:10or that computer generated
17:14field value
17:15has no business implication
17:19because the real world did not have this
17:21value
17:22its not like a other card number or like
17:24a passport number but its a value which
17:26is
17:27purely generated to identify every row
17:30uniquely
17:32so such
17:33keys are known as surrogate keys or
17:36synthetic keys
17:39now let us look at
17:40some examples
17:43this is again the same
17:44student database i just shown a while
17:47ago
17:48the same set of
17:50columns but i have added few more rows
17:54now if we look at what could be a super
17:56key
17:57there are several candidates but i have
17:59just written a few
18:01ah roll number is certainly a key
18:03because
18:05i am assuming that the university
18:07assigns role numbers to uniquely
18:09identify every student
18:11so there cannot be two rows in this
18:14table which match in the
18:16value of the roll number and does not
18:18match in the values of the other fields
18:21so roll number can uniquely identify
18:24if it can then any
18:27set of attributes which contain roll
18:29number will also be a super key so roll
18:31number and date of birth
18:33together is a super key that can also
18:34uniquely identify every row trivial
18:39what are the candidate keys
18:41now there are
18:42of course there could be several other
18:44super keys that has to be kept in mind
18:46the candidate keys are roll number is a
18:48candidate key
18:50the first name last name together we can
18:52say is a candidate key so we are saying
18:54that not only the first name but if we
18:56take this pair
18:57you remember the key the set
19:00of attributes forming a super key is it
19:03is a set it is not an individual field
19:05so i can say the first name last name
19:06together forms a key
19:09well this does make some assumption
19:12because if i say the first name last
19:14name together forms a key that means
19:16that there cannot be two records in this
19:19student table
19:20where the first name and last name match
19:23but the records are different
19:25so which mean
19:26that
19:27no two students having the same first
19:30name and last name
19:31can be enrolled in the university this
19:33is a restrictive assumption right but i
19:35am just making that assumption to
19:37illustrate
19:40the different possibilities
19:43then what is the other possibility
19:45passport number everybody has a unique
19:46passport number so passport number would
19:49also be a key could be a candidate key
19:53other number everybody has a unique
19:55other number so that can be a key and so
19:58on
19:59so these are called the candidate keys
20:02now of course we can observe that
20:05given the data it is clear
20:07and it was also mentioned when the
20:09schema was designed this passport number
20:12cannot be a key
20:13why can it not be a key can 2 students
20:16have different
20:18same passport number of course not
20:20every student has a unique passport
20:22number
20:23but it is possible that some student
20:26does not have a passport
20:27so if some student does not have a
20:29passport then
20:30the passport number field of that
20:32student
20:33is a null
20:35the passport number is a nullable field
20:37if the passport number is null then it
20:39is possible that multiple students
20:42may not have passports so as we can see
20:44here
20:45this student
20:46jatin chopra does not have a passport
20:50so
20:51similarly deepti that does not have a
20:53passport either
20:54so certainly if this were to be the key
20:57then for all
21:00records for which passport number is nil
21:03this value would not be able to
21:05distinguish them in terms of the rows of
21:07the
21:08table
21:10so
21:11we have
21:12to say that passport number cannot be a
21:15key or in other words we can say that no
21:18key can be a nullable field
21:20no key attribute
21:23or a participant to a key attribute
21:26could be a nullable field right so this
21:29is one observation government so that
21:30clearly also implies that
21:33if we
21:34say that other number is a valid
21:36candidate key that will mean that for
21:38admission to that
21:40university having other number would be
21:42mandatory if somebody does not have
21:43another number
21:45that will have to be null
21:46which is not allowed
21:50ok so lets
21:51move on
21:54so one of these candidate keys have to
21:58be made the primary keys let us say we
22:00make roll number the primary key
22:03and since we make roll number the
22:04primary key in the schema
22:07we underline the roll number attribute
22:09this would be a common way to show that
22:12roll number is a primary key
22:16so the others
22:18that are not taken as a primary key are
22:21called the secondary or alternate key so
22:23first name last name pair could be an
22:27alternate key other number could be an
22:30alternate key and so on
22:33a key is said to be simple if it
22:35consists of a single attribute
22:38so roll number is a simple key other
22:40number is a simple key if it were taken
22:42to be primary
22:44but first name last name pair if we take
22:46that to be a primary that will not be
22:48considered a symbol simple key because
22:50it has more than one attribute
22:54naturally the other if you have a sample
22:56key they have other side is a
22:59composite key a composite key is one
23:01which has more than one field
23:03such that
23:05none of those fields individually can
23:07act as a key
23:11but together
23:13they can act as a key so first name
23:15itself cannot be a key last name itself
23:17cannot be a key but together they can be
23:19a key of course under the assumption
23:21that
23:22those two students with the same first
23:24name last name are given admission
23:26so these are the different types of keys
23:28that can happen
23:30let us have some more
23:32views with the keys
23:34we extend the schema and besides the
23:37student i introduce two more schema
23:39one is called the courses
23:42which
23:43is
23:44given by course number course name
23:46credits
23:47ltp ltp is
23:49number of hours of lectures tutorials
23:51and practicals
23:52and the department so these are the
23:54different fields and
23:56from the
23:57convention already stated you can figure
24:00out that course number is the
24:02key primary key of this relation
24:06i use another schema which is enrollment
24:08which describes
24:10which student is attending which course
24:13so it has a role number and the course
24:15number
24:16so roll number of the student attending
24:18the particular course number
24:20and it also has the instructor id as to
24:22who is teaching that course
24:26given this as you can see that
24:29in
24:31in the enrollment relationship
24:33i have this pair roll number and course
24:36number
24:38which will certainly be the key for
24:40enrollment
24:42because if i have two rows in enrollment
24:45how they will be distinguished
24:47they cannot be distinguished by roll
24:48number
24:50because a particular student may take
24:52multiple courses
24:53so there will be multiple records having
24:55the same role number but different
24:56course number
24:59the course number by itself cannot be
25:00the key
25:01because every course will have multiple
25:04students so there will be multiple rows
25:06having the same course number but all
25:07different role numbers
25:09but if we take this together roll number
25:11and course number together then that
25:13forms a key
25:15now such a key
25:18such a key having roll number
25:21the roll number itself is a key of
25:24another relation
25:25the course number itself is a key of
25:28another relation
25:31so
25:32when we take
25:33the keys of other relations to form the
25:37key of a relation
25:39then we say that these are foreign keys
25:42so roll number and course number are
25:44foreign keys in student and course
25:47and
25:49since from enrollment the student and
25:52courses are being referenced are being
25:54referred so we say enrollment is a
25:57referencing relation
25:59and
26:00students and courses are the referenced
26:03relation
26:04and we will often like to also mention
26:08as to what is a foreign key of a
26:10relational schema
26:12because that will help us understand
26:15how the different schemas are
26:18interrelated
26:20and we will see that this will come out
26:22directly from the notion of entities and
26:26relationships of an er model of a er
26:29diagram
26:37a key is called to be said to be
26:39compound if it consists of more than one
26:41attribute to uniquely identify an entity
26:45occurrence so each attribute which makes
26:47up the key is a simple key in its own
26:49right
26:50mind you there is a subtle it sounds
26:53very similar
26:54we talked about composite key earlier we
26:57talked we are talking about compound key
26:59here
27:00the subtlety of the differences in a
27:01composite key every component attribute
27:04is not a simple key by itself
27:07but
27:08and the components come from the same
27:11table in a compound key the
27:14components are
27:16simple key by their in their own right
27:19in some other table
27:21and are put together as a compound key
27:23in the given table so
27:25the roll number course number in the
27:27enrollment table is a compound key
27:33so with this
27:34i would
27:36request you to spend some time with this
27:39relatively elaborated schema
27:42compared to what you have done already
27:44of the university database so every
27:48every rectangular box shows a relational
27:51schema
27:52on top
27:53of each
27:55in blue
27:56is written the name of that relation
27:58relational schema
28:00so it has a
28:02relational schema like
28:03courses
28:05the students
28:07the instructors
28:09the departments
28:11the prerequisites
28:14the time slots
28:16the classrooms and so on
28:19the sections
28:22and the relationships between them for
28:25example the relationship
28:27is takes is a relationship
28:32which relates students with
28:34different sections
28:36sections with courses
28:39teaches is another relationship which
28:41relates to instructors with sections so
28:45it is showing you directly as to
28:48how
28:49the
28:50keys of this what are the attributes
28:53what are the key attributes primary key
28:55attributes
28:56and also what are the foreign keys that
28:59we have in this for example in takes
29:02this
29:03is a foreign key which is featured here
29:07course id section id semester year are
29:11the foreign key part of the takes that
29:14exist here so please ah study
29:18the schema we will keep on regularly
29:20referring to the schema in future as
29:22well
29:23ah so this is what
29:25we have here
29:28now we move on to the relational query
29:30language we briefly talk about the
29:32relational query language now we will
29:35have to in this the key thing that we
29:37need to understand is ah the relational
29:41query language is
29:44somewhat different from the programming
29:46languages that you have studied so far
29:48which are procedural in nature
29:50in contrast the relational query
29:52language is non-procedure or declarative
29:55in nature
29:56a procedure programming language
29:58requires that the programmer tell the
30:00computer
30:01how to
30:03get the output
30:04given the input a pro program is about
30:07finding output for a given input
30:10and you write a procedure the sequence
30:12of steps that need to be done
30:14so that given the input you can compute
30:16the output so you say how
30:19the that computation has to happen and
30:21the programmer must know that algorithm
30:25in contrast in declarative programming
30:28you say what you want you do not say how
30:31that needs to be computed how that will
30:33be computed you may not even know that
30:36you may not even know a single algorithm
30:38to compute the output but you specify
30:40what output you need
30:42so this distinction between how and what
30:45of programming differentiates procedural
30:47and declarative programming so all that
30:50you have studied so far in terms of c c
30:52plus plus java python and all that are
30:55procedural programming where you
30:57necessarily have to specify how you will
31:00have necessarily have to specify what
31:02the algorithm is but in declarative you
31:05just say what you need
31:07so just a simple you know ah
31:10pathological example to understand this
31:12difference suppose we were interested in
31:14computing the square root of a number n
31:16assuming n is a positive number
31:18the procedural step could be something
31:20like this is an algorithm that you guess
31:22a an x naught which is a square root
31:25which is close to the root of n i mean
31:27ah some guess you make
31:29and then you repeatedly refine this
31:32estimate
31:33by
31:34taking the arithmetic mean of the
31:37estimate
31:38and the quotient of the
31:40division of n by this estimate so you
31:42take a arithmetic mean
31:44and find the new estimate
31:47and repeat the steps
31:49till
31:50you i mean as long as ah
31:53the difference between the two
31:54consecutive estimates is more than a
31:56certain value delta
31:58is a procedural one you are giving an
32:00algorithm so given n following this
32:01algorithm we will find the square root
32:04declaratively you can just say that ah
32:07what is the result i want i want a
32:09result m such that m square
32:11equals
32:12n so you are again
32:15asking for the same
32:16you are expecting the same output but
32:18the way you are saying is not an
32:20algorithm
32:21you are rather specifying a predicate
32:23which must be true in your output you
32:26are saying that the predicate is m
32:27square must be n so whatever m is
32:31that square of it must equal n so this
32:33style is known as declarative whereas
32:36the earlier style is known as procedural
32:38all query languages relational query
32:41languages are declarative in nature um
32:44we have talked about the pure languages
32:47ah are
32:48they are all equivalent we mentioned
32:50that earlier also and also again to
32:53remember that
32:54none of them are actually turing
32:57equivalent that means that not all
32:59algorithms can be expressed in
33:02them or specifically relational algebra
33:04which
33:05we will look at in more depth
33:08and
33:09the relational algebra will consist of
33:11six basic operations which we will
33:13discuss in the next module
33:16so to sum up we have introduced the
33:18notion of ah
33:20attributes and their types we have taken
33:22an overview of the mathematical
33:24structure of the relational model schema
33:27and instance we would say mathematically
33:29they are relations
33:31mathematically them in a mapping
33:33and we have introduced the very
33:36important concept of keys
33:39and in that
33:40very specifically what is a primary key
33:43as well as what is a foreign key
33:46in the next module we will discuss about
33:47the different operations of relational
33:50model relational algebra

About this transcript

This page contains the full transcript of Introduction to Relational Model/1 by Data Base Management System - IITKGP, generated from the public captions YouTube serves with the video. The transcript has 4,615 words across 882 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.