0:00	foreign
0:07	welcome back in this presentation we
0:10	will focus on the database schema the
0:13	reason for having an exclusive lecture
0:14	for database schema is that we need to
0:17	understand the important difference
0:18	between the database schema and the
0:21	relation schema before we step into the
0:24	topic of the day which is the database
0:25	schema we will just quickly revisit the
0:28	previous topic the structure of the
0:29	relational databases why because we need
0:33	to understand the important difference
0:34	between the database schema and the
0:36	relation schema we know basically
0:38	databases is the collection of tables
0:41	and we know tables are actually going to
0:43	store the data and also the relationship
0:45	among the tables and I have already
0:47	explained about the term relation why we
0:50	are referring it as relation instead of
0:52	table in the previous presentation
0:53	itself if you are directly watching this
0:55	lecture I request you to watch my
0:57	previous lecture for better
0:58	understanding and in relation rows are
1:01	referred as Tuple and columns are
1:03	referred as attributes and when we talk
1:05	about the relay additional database we
1:07	saw about the relation instance what is
1:10	a relation instance instance means it is
1:12	the data that is actually at a given
1:14	instant of time when we talk about
1:16	relation instance it is actually the
1:19	snapshot of the table or the relation
1:21	and the data present at a given point of
1:23	time which is actually referred as
1:25	relation instance please be noted that
1:27	relation instance is different database
1:30	instance is different anyway in this
1:32	presentation we are going to clarify
1:33	that and we also have seen about the
1:36	domains and we also have seen when a
1:38	domain is atomic and we have seen about
1:41	null values sorted and unsalted
1:43	relations in the previous presentation
1:45	in this presentation let's focus on the
1:47	database schema first we should
1:49	understand is in a database we are going
1:52	to focus on the database schema and
1:54	database instance remember when we have
1:57	a collection of tables all collection of
1:59	tables are in a single database and when
2:02	we talk about the database schema this
2:04
is actually the logical design of the
2:07
database before we go and execute the
2:09
physical aspects we logically create the
2:11
database design at the higher level
2:13
right so this is actually the logical
2:16
design of the database The Logical
2:18
design of the database is actually
2:20
referred as the database schema and
2:22
talking about database instance this is
2:25
actually the snapshot of the database it
2:27
means this database may contain n number
2:30
of relations all the data present in all
2:32
the relations collectively at a given
2:35
instant of time is called as the
2:37
database instance a normally databases
2:40
keep updating because relations or
2:42
tables keep updating and that's why
2:44
databases are also keep updating and
2:47
when we talk about the relation the
2:49
relation schema and relation instance we
2:51
have already seen about this in the
2:52
previous presentation the relation
2:54
schema is like the type definition in a
2:57
programming language variable say for
2:59
example if we are declaring a variable X
3:01
of integer type it means axis of integer
3:04
type so this relations schema is like
3:07
the type definition in the programming
3:09
language this is fixed but the value
3:11
that X is storing it keeps changing so
3:14
that is actually relation instance so
3:16
relation instance is like the value of a
3:18
variable which changes with time
3:20
when we talk about the relation schema
3:22
this is The Logical structure of the
3:25
relation what a relation will contain
3:27
all attributes what a database contain
3:29
all tables so this contains collection
3:32
of tables and its relationship and this
3:34
contains collection of attributes I will
3:37
show you two example Relations number
3:39
one the department relation and number
3:41
two the instructor relation when we talk
3:43
about the schema this department and
3:46
instructor all are relations but what is
3:49
a database collection of relations is
3:51
the database so when we talk about the
3:53
department relation just see the
3:55
department name the building and the
3:57
budget actually are the three attributes
4:00
under Department relation talking about
4:02
the instructor relation the instructor
4:04
ID the name of the instructor the
4:06
department name and salary are the
4:08
attributes in the instructor relation
4:10
every instructor will be belonging to a
4:13
department and that's why there is a
4:15
common attribute here the department
4:16
name in the department relation and the
4:19
department name in the instructor
4:20
relation and this is how the
4:22
relationship among tables or relations
4:24
are actually established the examples
4:26
that we are seeing without any data the
4:29
relation schema I am just presenting
4:31
let's take the instructor relation now
4:33
this instructor relation when it
4:35
contains the data it looks like this all
4:38
these are tuples and these are all
4:40
attributes ID name Department name and
4:43
salary are attribute at this time we
4:46
have nine rows with these values but we
4:48
can't guarantee that this relation will
4:50
be forever like this because databases
4:53
keep changing the relation keep changing
4:55
maybe the salary might be increased the
4:58
instructor might have quit the
4:59
University or a new instructor might
5:01
have joined so relation keeps changing
5:03
the data present in a relation at a
5:06
given time is the relation instance and
5:08
here is the relation schema I'll show
5:11
you one more example not the department
5:13
example what we have seen in the
5:14
previous slide I will show you something
5:16
different here which is the course
5:18
relation and this course relation
5:19
contains course ID title department name
5:22
and credits let's see one more example
5:25
the prerequisite table this prerequisite
5:28
table shows the course ID and what's the
5:30
prerequisite ID for that course say for
5:33
example if you want to undergo bio 301
5:35
course then you should have already
5:37
completed bio 2.1 course so this is how
5:40
the relation schema are actually
5:42
organized but What's the title of this
5:44
presentation database schema isn't it
5:48
but we are focusing on relation schema
5:50
actually a database is a collection of
5:53
relations I'll show you an University
5:55
database where we see multiple relations
5:58
the instructor relation the course
6:00
relation the department relation the
6:03
section relation the teachers relation
6:05
the student relation the advisor
6:08
relation the text relation the classroom
6:11
relation and the time slot and all the
6:13
instructor related data are stored in
6:15
the instructor relation which contains
6:17
ID name Department name and salary the
6:19
course related information in the course
6:21
relation department related information
6:24
in the department relation and section
6:26
related information in the section
6:28
relation they're talking about the next
6:30
relation that teaches this is actually
6:32
the relationship a teacher teaches an
6:35
instructor teaches to whom students so
6:38
this teaches is a relation that is
6:40
actually maintaining the relationship
6:42
between the teacher and the student the
6:45
teacher ID and what course he has taught
6:47
and for what section he has thought and
6:50
which semester I and which year and what
6:52
about student relation the student
6:54
relation contains student ID name
6:56
Department name and total credits earned
6:58
and coming to the advisor relation
7:00
student if they want any advice or if
7:02
the teacher or instructor wants to see
7:04
the progress of the student then we need
7:07
to know which student is belonging to
7:08
which instructor so this advisor
7:11
relation is actually having the
7:12
relationship between the student ID and
7:15
the instructor ID then text who takes
7:17
the course student right so the student
7:19
ID the course information then what
7:22
about classrooms every University will
7:24
have classrooms right so classroom
7:26
related information in the classroom
7:27
what are all the information the
7:29
building name the room number the
7:31
capacity of the classroom and what about
7:33
time slot say for example for certain
7:35
courses that University may offer the
7:37
timing from nine to five for certain
7:40
other courses it may be from 10 to 6 so
7:42
the time slot needs to be maintained so
7:44	we need to ensure what is the time slot
7:46	ID the day the start time and the end
7:49	time now all these relations are the
7:52	part of a database and the database that
7:55	we are addressing here is the University
7:56	database so this is how the databases
8:00	are constructed in real time remember
8:02	all these are logical structures The
8:05	Logical Design This University contains
8:08	all these relations so this is The
8:10	Logical design of this database but this
8:12	is just a representation only we have
8:15	schema diagrams also very soon we are
8:17	going to see about schema diagrams how
8:19	relations are organized in the database
8:22	and that's it guys I hope you guys
8:24	enjoyed this presentation and thank you
8:26	for watching
8:27	[Music]
8:27	[Applause]
8:30	[Music]