0:00	foreign
0:07	welcome back in this presentation we are
0:10	going to focus on schema diagrams before
0:13	we step into the topic of the day let's
0:15	see an analogy there is a popular saying
0:19	in English a picture is worth a thousand
0:22	words which means complex and multiple
0:25
ideas can be easily conveyed by a single
0:28
still image this still image can convey
0:31
its meaning and the organization of
0:33
things more precisely than a verbal
0:36
description let's take building
0:38
construction as an example before any
0:41
building construction floor plans are
0:43
normally created and these floor plans
0:46
help us to understand its organization
0:48
also these plants act as a work
0:51
blueprint for home planners and
0:53
construction Builders if the designs are
0:56
precise then obviously the output will
0:58
also be precise and perfect likewise
1:01
dbas or the database designers are
1:03
expected to design a clear and a concise
1:06
database so obviously they need some
1:09
diagrams in order to separate entities
1:11
and the relationship among entities also
1:14
these diagrams should contain
1:15
information about the constraints that
1:17
are applied on the databases and these
1:20
database designers create database
1:22
schema diagrams and these schema
1:24
diagrams are helpful for other users
1:27
like the application programmers or
1:29
analysts for proceeding the job related
1:31
to databases so we are now here in the
1:34
topic schema diagrams at first let's see
1:37
what is a schema database schema or
1:40
simply schema is actually the logical
1:42
view of the entire database we may have
1:45
a database and we want to represent the
1:48
database in a logical manner so
1:50
obviously diagrams help us to convey The
1:53
Logical structure so schema diagrams are
1:56
for representing the database schema
1:58
where these database schema is actually
2:00
the logical view of the entire database
2:03
and these schema diagrams are obviously
2:05
going to represent the entities like the
2:07
objects that are used an example object
2:10
is the table name or the relation name
2:12
and we say that there is a table
2:14
obviously this table will be comprised
2:16
of attributes so the schema diagram it
2:18
should not only display the table name
2:21
as well as the attribute name it should
2:23
also focus on the dependencies so in
2:26
simple terms a schema diagram should
2:28
contain the database schema plus the
2:31
primary key and the foreign key
2:33
dependencies and primary key and foreign
2:36
key are actually examples of constraints
2:38
I hope now we know what is a schema
2:41
diagram it is actually the database
2:43
schema along with the primary key and
2:45
foreign key dependencies and how we can
2:48
represent the database schema and also
2:51
the primary key and the foreign key
2:52
dependencies actually when we talk about
2:55
the database schema and this schema will
2:57
be actually having the relation name and
3:00
the attribute name so obviously schema
3:02
diagrams will certainly have the
3:04
relation name and the attribute name
3:07
relation name means it's the table name
3:09
attribute name means it's the column
3:11
name a table may have thousands and
3:13
thousands of rows we cannot show all the
3:16
data on the diagram it's practically not
3:18
possible but we can show what is the
3:21
table name and what is the attribute
3:23
name so we now understood how we can
3:25
represent the schema diagram with just
3:27
the relation name and the attribute name
3:29
and we know not only schema diagram is
3:32
going to have relation name and the
3:33
attribute name we should also focus on
3:35
displaying the primary key dependency
3:37
and the foreign key dependencies how
3:40
primary key and foreign key dependencies
3:42
are shown attributes that are having
3:44
primary key constraint or primary key
3:46
dependency are actually underlined then
3:49
what about foreign key foreign keys are
3:52
represented using arrows because foreign
3:55
key definitely involves two tables one
3:56
table is going to refer another table in
3:59
that case which table is referring the
4:01
other table that can be easily shown
4:03
using arrows and do we have only these
4:06
two constraints in reality in databases
4:08
the primary key and the foreign key now
4:10
we have other constraints can we have
4:12
present all other constraints in schema
4:14
diagrams no other constraints are not
4:17
explicitly shown in schema diagrams and
4:20
this is one of the drawbacks of having
4:22
schema diagrams of course we can
4:24
represent the logical view of the
4:26
database along with the primary key and
4:28
the foreign key constraint dependencies
4:30
but other constraints cannot be
4:33
explicitly shown in the schema diagrams
4:35
if that's the case do we have any
4:37
alternatives for the schema diagrams yes
4:40
of course we do have we have the entity
4:43
relationship diagrams simply ER diagrams
4:46
and these ER diagrams let us represent
4:48
several kind of constraints like any
4:52
other Technologies database technology
4:53
is also seeing a lot of growth in the
4:56
recent decades so obviously many
4:59
database systems provide design tools
5:01
with a graphical user interface simply a
5:04
GUI interface for creating schema
5:07
diagrams in chapter 1 we have seen a lot
5:10
of database software and most of
5:12
softwares provide a design tool which is
5:15
embedded with a graphical user interface
5:18
in order to create schema diagrams so
5:20
whatever we have seen so far is just the
5:23
theoretical aspects we need to see the
5:25
schema diagram right in chapter 1 and
5:27
chapter 2 which database we have taken
5:29
as an example the university database
5:32
right in this University database we
5:34
have a lot of relations and we also do
5:37
maintain the relationship among
5:39
relations in the relation itself let's
5:41
say how the relationship is maintained
5:42
the instructor is actually belonging to
5:45
a department right so instructor will
5:47
also have a department name and
5:49
Department relation will also have the
5:50
same attribute the department name and
5:53
how an instructor and a section relation
5:55
is linked the instructor is going to
5:57
teach to that particular section and
5:59
this section is going to contain some
6:01
students right so instructor is going to
6:04
deal with this section with this
6:06
relationship teachers and what about
6:08
student student is going to be related
6:11
with this course relation how with the
6:14
takes relationship because student takes
6:17
this course right there will be advisor
6:19
relationship also right so advisor is
6:22
the relationship that links two tables
6:24
what are these two tables the student
6:25
and the instructor table so this advisor
6:28
table will contain an attribute from the
6:30
student table and an attribute from the
6:32
instructor table what attribute the ID
6:35
attribute from student and the
6:36
instructor table just pause this video
6:39
for a while and just think about the
6:41
relationship that are established among
6:43
the relations
6:45
if the schema is represented like this
6:47
it's very vague but schema diagrams help
6:50
us to represent the logical view let's
6:52
see the schema diagram example now for
6:54
the same University database so here is
6:57
the schema diagram for the same
6:59
University database and if you see all
7:01
the relations whatever we have seen in
7:03
the previous slide
7:04
the student relation the course relation
7:07
the department relation the instructor
7:09
relation all these are relations what we
7:12
have seen in the previous slide but how
7:14
the previous slide and this slide
7:15
differs in the previous slide we
7:18
represented everything as a word but
7:20
here we are representing all the
7:21
relation in a diagram and what this
7:24
diagram is containing important three
7:26
things one is the relation name or the
7:28
table name in this case the table name
7:30
is student and what are all the
7:32
attributes or column names that this
7:34
relation contain the student ID the
7:36
student name the department name and the
7:39
total credits earned by the student
7:41
let's take the same student table the
7:44
student ID or simply ID name Department
7:47
name and total credits four attributes
7:49
the same is represented here student ID
7:52
name Department name and total credits
7:55
and can you see one of the attributes is
7:57
different from other attributes yes ID
8:00
is underlined what do we mean by this it
8:03
means this is a primary key attribute
8:05
and that's why I told you the primary
8:07
key attributes are shown with an
8:09
underline so here ID is underlined so
8:12
obviously ID attribute is a primary key
8:14
attribute just pause this video for a
8:16
while and think about the other primary
8:19
key attributes in the schema diagram
8:22
I hope you are done so whatever is
8:24
underlined all these are primary key
8:26
attributes
8:27
and coming to the arrow can you see here
8:30
this is student relation and this is
8:32
instructor relation and this instructor
8:34
relation also has ID name Department
8:36
name and salary so these four
8:38
information or four attributes are
8:40
belonging to the relation instructor how
8:43
this instructor relation and student
8:45
relation are linked every student is
8:47
assigned to an instructor with the
8:49
advisor relationship I have already
8:51
explained this in the previous lectures
8:53
so student and instructor are related
8:56
with this advisor relationship and what
8:58
are all the two important attributes
9:00
this advisor relationship should contain
9:02
it should contain this ID that I am
9:05
representing it as student ID but why I
9:07
am representing a student ID is that if
9:10
I simply take ID here and if I simply
9:12
take ID here this relation has two
9:14
column names with the same name id id
9:17
but we don't know what id it is whether
9:20
it is a student ID or instructor ID so I
9:23
am representing it as s underscore ID
9:25
which means this is student ID which is
9:27
this ID and also I underscore ID
9:30
represent that it is instructor ID which
9:32
is this ID so this is how relationship
9:34
are maintained let's see how the
9:37
instructor and section relationship are
9:39
maintained here is the instructor
9:40
relation and where is the section
9:42
relation here is the section relation
9:44
and how the instructor and section
9:46
relation are linked an instructor will
9:49
be teaching to that particular section
9:51
right so instructor teaches this
9:54
particular section and this section will
9:56
be in a classroom right so this section
9:58
is having a classroom and this classroom
10:00
will be in a particular building the
10:03
room number and the capacity of that
10:05
particular classroom and student takes
10:07
course right so where is the student
10:09
relation here so here is the student
10:11
relation here is the course relation and
10:14
student takes this course can you see
10:17
here this takes relationship is there
10:19
with the student we have seen underlined
10:22
represent actually the primary key and
10:24
can you see the foreign key constraint
10:26
just see this example the student
10:28
relation and the department relation in
10:31
the student relation we have an
10:33
attribute called Department name and in
10:35
the department relation we have an
10:36
attribute called Department name and
10:38
this is the foreign table and this
10:40
foreign table contains Department name
10:42
as the primary key attribute and this
10:44
primary key attribute in the department
10:46
table is actually linked to the student
10:49
table and this student table here it is
10:51
not a primary key attribute because in
10:54
the student relation there may be
10:55
multiple students belonging to the same
10:57
Department whereas in the department
10:59
table we need to have the department
11:01
entry only once and that is why this
11:04
department name in the department table
11:06
is having only one entry and that is why
11:09
this department name is a primary key
11:11
attribute here and the foreign key
11:13
representation here is the student table
11:16
is referring to which table the
11:18
department table that's why the arrow is
11:20
like this can you see student table is
11:22
referring the department table so
11:24
whenever any entry is made in the
11:26
student relation the department name
11:28
will be validated or the referential
11:31
Integrity is validated by referring to
11:33
this table suppose if we are entering a
11:36
department name here this department
11:38
name should exist in this table so I
11:40
hope now you understood the significance
11:42
of having schema diagrams in databases
11:45
and that's for the day in this
11:47
presentation we have focused on the
11:49
schema diagrams and the significance of
11:51
having schema diagrams but the drawback
11:53
is in this schema diagram we can
11:55
represent the relation name the
11:57
attribute name along with the primary
11:58
key and foreign key constraints can you
12:01
see here only primary key the underline
12:03
and the foreign key the arrows can be
12:05	represented and we are not able to
12:07	represent other constraints and this
12:09	drawback is overcome in which diagram
12:11	The Entity relationship diagram the ER
12:14	diagram we are going to focus on ER
12:16	diagrams in the next chapter and I have
12:19	not explained the relationship between
12:20	the section relation and the time slot
12:22	relation the section relation and the
12:24	course relation and the relationship
12:26	between the course relation and the
12:27	prerequisite relation this is the
12:30	homework for you just pause this video
12:31	for a while and just think how the
12:34	relationship is established in the
12:36	entire schema diagram
12:38	I hope the session is informative and
12:40	thank you for watching
12:42	[Music]
12:42	[Applause]
12:44	[Music]