YT YouTube2Text

YouTube2Text → Transcripts → Introduction to DBMS/2

Introduction to DBMS/2 — Transcript

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

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:16welcome to module 3
0:19of
0:20database management systems course
0:23we started
0:25discussions
0:26introducing the database management
0:28systems in module 2
0:31this is the
0:32second and concluding part of that
0:34discussion
0:36so
0:38this is what these are the aspects that
0:40we had discussed earlier starting from
0:42level of abstraction
0:44to the
0:46outline of database design
0:49in the current module we would like to
0:51understand the models of database
0:53management systems little bit more
0:57and we will try to familiarize with the
1:00concept of
1:02major components of ah database engine
1:05we will elaborate on those
1:07and will familiarize so with
1:09the basic
1:12architecture of a database management
1:14system some of the internal components
1:16and will present a brief historical
1:18perspective of the dbmss
1:22so this is the outline that we will
1:24follow
1:26so we have already discussed about the
1:28database design i would like to raise a
1:31few
1:32further issues about
1:34that so we have seen that
1:36there is a logical design which is
1:39driven by the business decisions and
1:42refined by the computer science
1:43decisions there is a physical design as
1:45well
1:46and based on that
1:47we had
1:50presented this particular table asking
1:53whether this database is
1:56this is whether this is a good design or
1:58not
1:59so let us have a little ah look into
2:01this for example
2:04we have introduced the department name
2:08and the building in which the department
2:10is housed
2:12so if we look at there are multiple
2:14instructors say let us say
2:16professor einstein
2:18who teaches in the physics department
2:20that is housed in the watson building
2:24and if we look through there is a
2:26professor gold
2:28who also teaches in the physics
2:30department and naturally that is housed
2:32in the watson building
2:34now the question is so
2:37physics department if it is housed in
2:39the watson building then all the
2:41instructors in this in this table
2:44all the instructors who are
2:46part of the physics department would
2:48have their department housed in the
2:50watson building
2:52so there is a certain issue of between
2:55these two there are certain issue of
2:57redundancy
3:03that is the same information is given
3:05more than once which is not a very
3:07desirable thing
3:09the consequence of this could be suppose
3:12tomorrow
3:14the university decides to move this
3:17physics department from
3:20watson to the taylor building
3:23this will mean that
3:25once this is moved then this watson will
3:28have to be changed to taylor
3:30also this watson will also have to be
3:32changed to taylor all instances of
3:34watson
3:36that corresponded to the physics
3:38department in this table will have to be
3:40changed to taylor
3:42and that is not a good scenario so its
3:44not only that we have redundancy
3:46we have
3:47potential
3:51for anomaly
3:54that is
3:56we might the application program the
3:58application programmer might forget
4:00to update
4:03the building say for this entry
4:06then we will be in an inconsistent
4:08database so
4:10to put it
4:12in simple terms that this is not a good
4:15design and there are several issues to
4:17consider
4:18in terms of whether some design is good
4:21or some design needs refinement
4:24so need to come up with a methodology
4:27to ensure that each of the relations in
4:30the database is good
4:33so
4:34we primarily follow two approaches in
4:36doing this one is using the entity
4:39relationship model
4:41which models the enterprise as a
4:43collection of entities
4:45or concepts
4:47or if you are familiar with the object
4:49orientation classes
4:52and the relationships that hold between
4:54these entities so in an university
4:56database the entities are students
4:58courses teachers
5:01and the relationships are
5:03a teacher
5:04teaches a set of courses
5:06a student attends a set of courses and
5:09so on the teachers supervise a set of
5:11students for projects and so on
5:14and then represent them diagrammatically
5:16in terms of an er diagram entity
5:18relationship diagram
5:20and once that has been done
5:23then we try to
5:26follow a certain
5:27normalization theory
5:29this normalization theory tries to
5:31capture that what are the properties
5:34that must
5:36hold in this database design that must
5:38be satisfied on this database design
5:41in terms of what is known as database
5:44dependencies there are varied forms of
5:46dependencies functional dependencies
5:48multivalued dependencies joint
5:50dependencies and so on
5:52and try to formalize and evaluate
5:56whether a design is good or its bad
5:59test them for quality and they normalize
6:02to make them
6:03better
6:04make them the best possible that can
6:06happen so that is this is something
6:09ah that is
6:11starting from the entity relationship
6:13model which captures the real world to
6:15the actual database schema there is a
6:18process of
6:19representation and then
6:21ah capturing of ground truths
6:25that hold in the database
6:27should hold in the database system and
6:29then normalize the database is a basic
6:31requirement of the design approach
6:35we have talked about ah object
6:37relational data models fewer few more
6:39points about that
6:41that in a relational model everything is
6:43flat every value is atomic in the sense
6:45that everything if you look back and
6:48think in terms of c
6:51then every
6:52field is a value which can be a simple
6:56you know built in type like integer like
7:00fixed length string variable length
7:02string a floating point number like that
7:04but
7:05i cannot have a composite you know ah
7:08object kind of ah fields
7:10but in a relational data model we extend
7:13in the object relational data model we
7:15extend the relational model by including
7:18the object orientation and the
7:19constituent constructs ah to deal with
7:23added data types higher data types
7:26ah where attributes are allowed to have
7:28complex types non atomic values
7:32that may allow things like nested
7:34relation that is a value could itself be
7:37a relation could itself be a table
7:39and but we try to preserve the
7:42relational foundation ah and we will see
7:45what those foundations mean and provide
7:48upward compatibility to to existing
7:50relational databases so this is what the
7:53basic concept of object relational data
7:56models are and as i said that we will
7:59just glimpse through it but this is not
8:01the primary
8:03objective that we will try to cover
8:06in contrast xml extensible
8:08markup language was
8:11defined by w3c and it was originally
8:14intended for marking up document
8:17languages
8:18not as it is not it was not designed as
8:20a database language it was designed for
8:22marking up so it is kind of saying that
8:25this particular element
8:28should be put in capital this should be
8:31in blue color this means a verb this
8:34means a paragraph there should be a page
8:36break here those kind of markups but
8:38subsequently it turned out that
8:41the way xml deals ah
8:44with different components
8:47in terms of tags
8:48and
8:49the ability to create nested tags makes
8:52a
8:53great
8:54language for exchange of data as i
8:57explained in the last module also so it
9:00is
9:01become the basis for all kinds of
9:04new generation data interchange format
9:07so as i explained that any database
9:10should be able to convert the data
9:13instances of the tables in terms of
9:16corresponding xml format
9:19and then you take it to some other
9:21database where you
9:22with which you are intending to
9:24interchange the data
9:26and
9:27that target database should be able to
9:29import from that external structure and
9:32it becomes
9:33it is become
9:34widely available that you have different
9:36tools for parsing browsing
9:38and querying xml content document data
9:41and so on so if you are
9:44familiar with c programming i hope so
9:46you are ah you can
9:48look up certain xml parsing and
9:51try out they are great tools to learn
9:55moving on let us briefly look at what is
9:58the core of a database management system
10:00the database engine
10:02the database engine primarily
10:04contains ah three major components ah
10:08the storage manager the query
10:10processing engine sub engine and the
10:13transaction manager
10:15the storage manager
10:17is a is a module or collection of
10:19modules in a database management system
10:22that provide the interface
10:24between the low level data
10:26and the application program so we have
10:29looked at the storage manager is the one
10:31which is a bridge between the
10:33physical level of abstraction and the
10:36logical level of abstraction then
10:39finally to the view level of abstraction
10:41so the storage manager has to deal with
10:43the
10:44interactions with the operating system
10:46on which the dbms is
10:49kept
10:50the file manager of the operating system
10:53it is responsible for efficient storage
10:56retrieval update of the data it is
10:58responsible to make sure
11:00that if there are certain problems in
11:03the file system then the data is not
11:05corrupted and so on
11:07so the issues certainly
11:09that involve are the access to the
11:11storage the organization of the files
11:14and very importantly indexing and
11:16hashing and we will talk about
11:19the concept of indexing later in the
11:22course it primarily says that if i want
11:24to
11:25for example you can simply understand
11:28that
11:28if you
11:29are
11:31if you have a large chunk of data that
11:33you want to organize
11:35for efficient search
11:37then you can use a binary search tree in
11:40simple algorithm terms the binary search
11:43tree needs to be organized in terms of
11:45one data component we say that well
11:47there is one value based on which you
11:49can say that comparison is done so that
11:51at every node if that value is smaller
11:54you go to the left sub tree if that
11:56value is larger you go to the right sub
11:57tree and so on so in a if we want to
12:00organize the records of a database
12:03system in terms of such a binary search
12:05tree then the question certainly is
12:08which field do i use
12:10for the search tree comparison
12:13now whatever field i use for search tree
12:15comparison on that field the searching
12:17would be very efficient but if i want to
12:19search on a value for a different field
12:22the searching would not remain that
12:24efficient so indexing is a mechanism by
12:27which
12:28you can actually create auxiliary search
12:31trees on multiple fields
12:33so that the search on multiple fields
12:36can be made efficient and we will talk
12:38about this ah later when that particular
12:40module comes up but the storage manager
12:42has to deal with such issues
12:45moving on ah
12:46the query processing ah is
12:49if we have we have already talked about
12:52the language the ddl the dml the query
12:54language so its some kind of like the c
12:57program its some kind of a text based
13:00programming code so naturally that code
13:03needs to be parsed and translated as
13:06we typically do in a c compiler so there
13:09needs to be a query compiler so it
13:11passes
13:13and
13:14analyzes the code but translated
13:18unlike the c program which translates
13:20the c program into an
13:22intermediate code and then ah into ah
13:26the binary instructions of the machine
13:28the assembly binary instructions of the
13:30machine the query translator
13:33translates the query into relational
13:35algebra expressions i said that there
13:38are two kinds of languages the
13:39commercial query language and the pure
13:41language so you translate it in terms of
13:44a program in the pure language it which
13:46could be a relational algebra language
13:49and then it tries to optimize so that's
13:52that's a that's a that's a critical term
13:54to be noted that there is an optimizer
13:57so this optimizer is
14:00a critical component
14:01which
14:02tries to make sure that the query when
14:04it is run on your data will run with the
14:08most
14:09in a least amount of time in an
14:11effective manner so
14:12and then an execution plan needs to be
14:15decided we will you will be able to
14:18understand this when we go to the actual
14:20relational algebra
14:21execution plan basically says that if
14:24there are multiple operations in that
14:26query to be performed then how those
14:29operations in which order they should be
14:31performed and where should temporary
14:33tables be used where they should be
14:34skipped and so on and then once that has
14:37been done then it passes on to an
14:39evaluation engine which actually runs
14:41that query on the data that you have the
14:44instances of the data that you have and
14:46that brings out the resultant query
14:49output which is another table of results
14:52that we get so this query processing is
14:55a core part of a database engine which
14:59actually allows us to write text based
15:02queries and reactive data efficiently
15:05change update data efficiently insert
15:07data efficiently and so on
15:09so
15:13so when we do this that we need to look
15:16at alternative ways of evaluating a
15:18query
15:19there could be different ways to write
15:21the same thing these are called
15:22equivalence expression equivalent
15:24expressions and what are the good
15:26algorithms for doing each and every
15:28operation
15:31there is a cost between good and bad way
15:33of evaluating so this has to be
15:35understood that
15:38the same thing you can compute in a you
15:40have seen this similar concepts in in
15:43normal programming languages also i mean
15:45ah we have seen for example for sorting
15:48there are several ways to sort and some
15:49are better some are not as efficient so
15:53ah the similar things in terms of a
15:55query needs to be evaluated and the cost
15:58between good and bad ways need to be
16:00figured out
16:04so
16:10then we need to estimate the cost of
16:13every operation it depends on
16:15the information of what has happened in
16:18the past the statistical information
16:20and need to estimate those statistics
16:23for intermediate result these are the
16:25couple of things that the
16:27query processing
16:28sub engine in a database will do
16:32so beyond the storage
16:34manager and the query processor ah we
16:36have a transaction management system
16:38which is very very critical and core of
16:40the database system
16:42it is a primarily
16:44has to deal with two fundamental issues
16:47of a database one what if a system fails
16:51see database systems unlike the programs
16:54that you have written so far a program
16:56starts
16:58executes and ends the program always
17:00deals with transient data the data did
17:02not exist before your program started
17:05it ceases to exist after your program
17:07ends
17:08so a program however complicated however
17:11important
17:12has a
17:13limited lifetime a database in contrast
17:17has a much longer lifetime it deals with
17:20persistent data
17:22that is very important to understand
17:24that is
17:25the each application whether i am doing
17:28a bank fund transfer whether i am
17:31making a credit card payment whether i
17:33am checking the balance
17:35or i am booking a railway ticket whether
17:38i am
17:40purchasing a book from amazon each one
17:43of the applications are like the normal
17:45program it has a fixed lifetime i
17:47started i do certain operations i am
17:49done with it but the data that is behind
17:52it the data of my accounts my
17:55account balance my transactions my
17:58different bank charges all that
18:01need to stay on and on and on and beyond
18:04every
18:05particular operation that i have done on
18:08the database
18:09so which means that
18:11if
18:13this database system fails at some stage
18:16for some reason
18:18then we have a enormous impact of that
18:21and that is not something that we can
18:24absorb that something that we can accept
18:28so
18:29a database system has to come with the
18:31concept of recovery it must be possible
18:34if the system fails it must be possible
18:36to recover
18:38it to a certain earlier point where it
18:42is consistent
18:43so transaction management system is
18:45responsible to guarantee this kind of
18:48recoverability of databases
18:51then
18:53the other question that we have
18:55discussed about
18:56earlier also is
18:59multiple users are you accessing the
19:02same database the same set of data at
19:04the same time
19:05so what how to make sure that more than
19:08one user can concurrently use and update
19:11without the data getting inconsistent
19:13that is
19:14as i had mentioned there is only one
19:16seat available one bath available on a
19:19particular train on a particular date
19:22and two users at the same time has
19:24initiated a booking it should not happen
19:27that both of them get
19:29the booking so one should get one should
19:31not get and that needs to be
19:34the complexity is high for this kind of
19:38you know decisions because ah in a the
19:41databases
19:42applications are significantly
19:44distributed
19:45indian railways have no idea of who is
19:48going to do what booking of which part
19:50from where at which point of time so
19:53transaction management system is
19:56as the name suggests defines something
19:58called a transaction which always keeps
20:01the database consistent and operable
20:05so its a collection of a transaction is
20:07a collection of operation that performs
20:09a single logical function in a database
20:12application this is this is very very
20:14critical
20:15its a collection of operations
20:17and performs a single logical function
20:20so it does not do anything and
20:21everything it just does a single
20:23particular logical operation
20:25and
20:26ah
20:27that's what forms the transaction
20:31so
20:36a transaction management system is a
20:38component that ensures that
20:41with all these transactions happening
20:44hundreds and thousands of transactions
20:46happening every second in a database
20:47system in a typical database system
20:50the data
20:51should still remain consistent
20:54in a consistent state in spite of
20:56failures in spite of concurrencies it
21:00must
21:01make
21:02sure that at no point of time
21:05it should happen that an amount has been
21:08debited from an account and has not been
21:09traded to account
21:11or an amount has been created to an
21:12account and has not been debited to the
21:14corresponding account or the same seat
21:17same birth is booked by two persons at
21:19the same time and so on so forth
21:22so
21:24this also includes concurrency control
21:26manager which controls the interaction
21:28among different concurrent transactions
21:30which ensures the consistency in the
21:32database and provides the total safety
21:36so
21:37in total we have seen the different
21:39components of the database engine
21:41comprising the storage manager
21:43comparison
21:44the query processor and the transaction
21:47manager
21:48now we move on to ah we just have a
21:50quick look in terms of what the typical
21:52users
21:53of a database system
21:55so if we
21:57see the grossly the users of a database
21:59system can be grouped into
22:02i mean you can group it in multiple
22:03different ways but this is a typical way
22:05to group that you have the nav users
22:08those
22:09like those the the ah
22:13secretarial staff who sits at the tailor
22:15of the bank
22:16now that person just needs
22:18ah does not know database management
22:20system but that person just needs to
22:22know the particular application he knows
22:25a few set of screens graphical screens
22:28what needs to be filled up where which
22:29button needs to be clicked and so on and
22:31can use
22:33this database through an application
22:35interface so this is a lowest level of
22:37user
22:38then you have the set of application
22:40programmers about whom i talked about in
22:42my ah
22:44course overview
22:45ah presentation that application
22:47programming is a big chunk of
22:50you know it services that databases need
22:53who actually write the application
22:56programs while the nav user is similarly
22:59using it application programmers are
23:01responsible for writing coding this
23:03application program so they need to
23:04understand the database designs they
23:07need to understand
23:08how to ah write the query language how
23:11to fit with the application data input
23:14output all the systems
23:17the next level are the analysts who are
23:20called the sophisticated users
23:22so
23:23they
23:25design different kinds of query tools
23:28they are responsible
23:30for the design of the database that is a
23:32schema the ins different
23:35ah constraints
23:37the
23:38authorizations and so on and manages
23:41that over a period of time when the
23:44application requirements change they
23:45might need to redesign the schema
23:48migrate the data from an old schema to a
23:50new schema
23:51so analysts are higher level of
23:54programmers they have
23:56ah
23:57far more solid understanding of the
23:59database management system to be able to
24:03design different kind of query tools
24:05that the application programmer will use
24:08and and there are database
24:10administrators so database
24:12administrators are
24:15people with
24:16specialized rights who can do
24:19a lot of privileged operation on the
24:22database for example ah taking backups
24:26of databases for example creating
24:28different users
24:30ah for example if there has been a
24:33failure then ah how to
24:35do the failure recovery scripts
24:38recovering the database and so on so
24:40they do all kinds of administration
24:42tasks but not the usual day to day data
24:46maintenance and you know query
24:48processing and so on
24:50so
24:51if we
24:52if you would like to know your positions
24:54then i would say that by
24:57through this course you are going to
24:59position yourself amongst the
25:01application programmers and the analyst
25:03and as i mentioned that the first half
25:05of the course
25:07is focused on application programming
25:09aspect and the second half would be more
25:11focused on the analysis and some of the
25:14administrator we will do little bit of
25:16administration but not really serious
25:18administration tasks
25:21now
25:31we will take a quick look into the
25:33database ah internals and architecture
25:35so i will take you to this diagram i am
25:38sorry this diagram is little
25:41small in terms of the script size ah so
25:44please refer to the actual presentation
25:46so if you if you look at the top ah here
25:49is the users
25:51so
25:52just trying to show what different users
25:55use this is a query processor that
25:58we have known
26:00so the query processor gets a query and
26:04so that naturally this query comes from
26:06the application program so the compiler
26:09link
26:09these
26:11these are the basic processing then the
26:13op
26:14compiler organization the evaluation
26:16engine which actually takes care of the
26:19processing of the whole query and then
26:22it goes to the storage manager which is
26:24now taking this
26:26whatever the evaluation engine needs to
26:28do has to go through different
26:31modules in the storage manager which we
26:34will talk about these modules when we
26:36now we do have a discussion module on
26:38the storage management later in this
26:40course
26:41and we will then talk about what is a
26:42file manager and what is authorization
26:44and so on but these are the sub
26:46components these are the sub components
26:48the storage management needs to do
26:50and then the storage manager is only one
26:52who deals with the actual data the
26:54actual disk storage the different files
26:57and so on so as you can see that the
26:58whole system is kind of ah layered
27:02in terms of so this is this is your your
27:05basic physical layer that you have
27:09and
27:10this is
27:11your final
27:12view layer that you have and in between
27:15this
27:16is a logical layer that you that you
27:18deal with so you can you can see that
27:21the abstractions as we had
27:23talked about are also mapped in terms of
27:25the
27:26way the actual database system
27:28architecture is managed and finally the
27:31data stays in the disk storage which ah
27:33ensures that whatever data i have is
27:36actually persistent in nature it does
27:38not go away any data that stays within
27:42here
27:43or within the application interface is
27:45transient
27:46so that data these data are
27:48transient
27:52they will disappear as soon as the
27:53application is over but these data are
27:56persistent
27:58they exist
27:59beyond this and architecture supports
28:02that whole gamut
28:03from
28:04of all applications or transiency of the
28:07applications based on the persistency of
28:10the data and the storage so that is a
28:12basic
28:13architectural view of a typical database
28:16systems the actual architecture will be
28:17far more complex but we just want to
28:19take a schematic view so that we can
28:21understand it better
28:25so it ah the actual architecture may
28:27again ah vary based on the computer
28:30system that you are using
28:32it could be centralized we will talk
28:34about some of these at later modules ah
28:37centralized in the sense that there
28:39could be one database server or you know
28:42a group of database servers at the same
28:44physical location connected together
28:47to which all applications all user will
28:49connect to
28:51it could be in terms of a client server
28:53model which is a very typical client
28:54server model that the programming
28:56systems have so that ah typically for
28:59for example any of the
29:01net based internet based database
29:03applications we are looking at are
29:05necessarily client server in nature what
29:07you are doing in the browser is a client
29:09and there is a server back
29:11far back there the multiple tiers in
29:13between them
29:15ah databases could be single processes
29:19or for performance they could be in
29:21terms of multiprocessor
29:23ah parallel databases the data
29:26sets themselves could be so large that
29:29it may not be possible to search on them
29:31through a single processor in a
29:33reasonable time
29:34they could be
29:35distributed the data itself could be
29:37distributed tables could get so large
29:40that i may not be able to keep them at a
29:43single point so these are different
29:44aspects of you know complex ah real life
29:48database system that exist
29:50and
29:51we will deal with some of those some of
29:53those aspects over the course of time
29:55but
29:56you have to keep in mind that a final
29:58architecture of a database
30:00and its associated application will have
30:03some of these factors that you will need
30:05to know and maybe decide on
30:12ah in terms of ah history
30:14well i mean i will not ah go through
30:17each and every point here this is
30:19more for completeness and to give you an
30:22a sense of how things have been going so
30:24database system started in the 50s and
30:26early 60s then major developments of
30:30these relational models
30:32and all that started happening
30:34in the 70s and
30:37in 80s really it proliferated
30:40large in terms of prototypes and
30:41commercial systems parallel distributed
30:43systems object based systems started
30:45happening in 80s
30:4690s ah it really exploded in terms of
30:50large decision support data mining
30:52applications ah
30:54you know applications ah widespread
30:57use of
30:59internet-based
31:00data applications and and systems
31:04emergence of google and all that
31:06from twenties uh early to i mean early
31:08two thousand it has been
31:10xml and automated database
31:12administration and now what we are
31:14facing in are the big data which are
31:17certainly ah aspects of other courses
31:20ah but at the back of back of back at
31:23the last layer there
31:25often is a strong relational system that
31:27exists
31:29so to summarize uh in this module we
31:32have introduced the models of database
31:34management system the major components
31:36of
31:37a database engine
31:38and
31:39discussed about the internals and
31:41architecture
31:42and
31:43this will conclude our discussion on the
31:46internals of database management systems
31:49and next we will move on to
31:52exposing more on the relational model
32:01you

About this transcript

This page contains the full transcript of Introduction to DBMS/2 by Data Base Management System - IITKGP, generated from the public captions YouTube serves with the video. The transcript has 4,473 words across 827 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.