Understanding Three Schema Architecture in DBMS

Hello friends, welcome to Gate Smashers . The topic is three level of abstraction in DBMS or we call its three schema architecture. Before starting this topic i just want to request you all & specially who are watching my videos first time, to please subscribe my channel, please share with your friends and please press the bell button to get the latest notification. So from this video firstly we are going to discuss, what is three schema architecture and why it is used. what is the meaning of three schema? I draw a diagram here, three schema. Firstly, To understand the word what is the schema, schema means structure, structure means the data we are storing, means that data is of any student's data, means that data is of any student's data, faculty's data, railways's data or Flipkart, Amazon is saving our data, whatever data we are storing, what is the structure to store that data, structure means any data like if i have a data 1, A, 20, Now this data means roll number is one name is A, age is 20, i am taking it just for an example. Now what i did to save this data, i put it in a table, table means put it in a 2 dimensional table where i have columns on this side, like I have written here roll number, name and age and here is my row means we are talking about relational database, which I have already told you earlier that where we store data in table form. So here what is schema, What is the structure, table and what comes in that structure, columns and rows. What is in the columns, roll number. So what is roll number actually, roll number is an integer. So what is the size of the integer, 2 byte, 4 byte means whatever we are describing about the structure, that structure is actually known as schema. Now here are 3 levels external schema or external level, conceptual level or conceptol schema, physical level or physical schema which we also called internal level. Now why these 3 levels have been made here? Actually in 1970 this concept came of 3 schema architecture, what is the main motive of this architecture is data independence. Data independence means user and data should not directly interact with each other. Means the user should not know where the data is stored, although the user or data is related to each other. Because It's user's data, the user will call that data whenever they wants, but where is that data stored, how is it stored, we keeping all this information abstract from the user, we are calling it data abstraction. Means the user knows that my data is stored somewhere, but where is it stored, how is it stored, we are hiding this thing from the user. We are hiding this thing from the user. If you are using this, it's main example is gmail. We all are using Gmail, we have our mails in Gmail, we also keep our photos in it, we also keep important data in it, but But where is the mail actually stored? physically you know where it is stored, in Delhi, in Kolkata, in Mumbai, in Bangalore, in USA, in UK, where is it stored? We do not know its exact location, means, is it stored in the files? what is the format? We keep all these thing hidden from the user. To keep these things hidden, we have created 3 levels in the middle, means user and data, this is the database, where all the data is stored physically and this is the user, user means us. Now 3 levels made between the user and the data. Its main motive is to make data independent means data independence, so that we can provide abstraction to the user. Now first is my external schema, what is the advantage of, what is the use of external schema? We also called it view level. I have already mentioned here view level. View level means how to show the data to the user, how to represent the data to the user, that is the work of this level. Although it is a theoretical model But somewhere practical are based on these theoretical models only, means that all the web applications or mobile applications are being made in today's time, I am not saying that they are directly based on this. But yes this concept came in 1970, after that a lot of refinements took place. But majorly all the web applications we are using today, on their backend, somewhere these schemas, these concepts have been used. So I am telling the same point, if I tell with a simple example The university has a management system like if I taken admission in the university, then my login id password is made there. Now when I feel that login id password, then what information show to me, my marks, my Attendance, my fee structure, how much I have paid, means all this is a view. That view will be given to the user, but will this view be common to all? No. If all the users had the same view, then I did not need to create separate views here, only one view would have worked. But if I talk about the Faculty, Faculty in the same university, then Faculty is also looking at the attendance, Faculty also checking their marks, No. The students have their own view and the faculty has their different view. What can faculty do? enter the marks, register the student's attendance, apply for leave, check their own salary and their own details. but can the student check the same details, No. So to prevent that thing, from the security point of view, we have made a view. So Whose view is this, student's. in which some authorization is their, If I speak in technical term, then authorization means that we have authorized something, that you can also see it, you can also see that, you can read something but cannot be updated like the marks I put that can only be seen Can't be updated. So this is the student's view. One such view will be of the faculty, the faculty has its own authorization, similarly if there is a dean, then they will have their own view because they have more authorization, so what have we actually done provide their own view to everyone. Whatever the applications you are using today, they also have a view, One is normal view for us normal users, another view of admin means , the database can also have administration, the network can have a separate administration, there may be different views for different profile means job profiles, so in this example student's their own view, faculty's their own view, dean's their own view. This view is only helping me whether I am able to see the data, means as I turned on my laptop, I entered the login and password of my university URL, then what is coming on the first front page, that front page is actually a view. Or if I tell you that you are using any application like on flipkart or amazon, you are login in it or you are using paytm, then what is the first view you get when you login, that view is actually a external schema. After that comes my conceptual schema, what is the meaning of conceptual schema? The way we are representing the data there, means how the data actully stored. My data is here but how is its structure. like i tell a simple thing, there is a student's data, then what are the columns in the student's data. Like roll number, name, age, the address, there can be many columns. So we have to define this thing in advance. When we create database, first we have to create its conceptual view. You can understand schema & conceptual with simple example E-R model. If you have already read about this E-R diagram or E-R model, then you will easily understand this otherwise I will explain to you in another video, E-R model. Here you just Understand that E-R model is a conceptual model. Data is actually stored here but how is that data stored, we have it in the form of a table like what does E-R model do, it makes different entities. That's how the relational model comes to, what do we do in the relational model, we use in majority for structure data, in relational model we create tables. So which tables we have used, information of those tables, student's table, faculty's table, library's table, like book's table. There are many tables that can be used, all that information is here. The relationship between those tables, relationship means how the table is connected? Now students and if I talk about course, are student and course interconnected? Obviously. There is a table of students which contains the information of the student and there is a table of courses which contains the information of all the courses of the university. Is there any relation between the student and the course, obviously. Student are studying the course, so yes there are some relationship between the table. What is that relationship? we have to mention here at this level, means we are already making a conceptual view of that data, you can say like blueprint, like before building a house, we make blueprint from the architecture, that is actually the Conceptual schema. To whom we give that view or print, we give it to the contractor or builder, that builder then starts building by taking his material, that is what we are here are working on. What is this conceptual schema, it is a blueprint, in which, what tables we have to keep, which relationships, what should be the size of those tables, everything will be mentioned in this. But after that the main story comes here, where is the data stored actually, we have stored data here, in which files we have stored those data, what is their size, means that, the data is actually stored in which hard disks. What is the store location, where is all these information? In the physical schema. From the name "physical" itself, it is known that where is the data physically present. So who are actually here, database administrator. Database administrator decides at this level, which data is to be kept in which particular location, in which particular drive, how to fragment it, how to store it. Where we decides all these information, in physical schema. so that means designer, normal database designer, where are they working, at this level. The interface designer who works on the front end like someone is working on PHP, then if I am creating an interface on PHP's frontend or I am creating a simple interface using Java or I am creating an interface using HTML, then where am I sitting, at this point. So if in a company, the work is going on in 3 levels, so the people from the front end who are creating an interface using PHP or Java on the front end, they are working here. Who are working on the second level, the database designers, who only have to design the data, do not have to store them actually, only have to design them and give them a blueprint, this one. And at the third level, actually the database administrator. The administrator who is administering the entire data, those who have complete control. They are actually sitting here and deciding in which location to store the data because data can be stored in two ways, centralized or distributed, The administrator who is administering the entire data, those who have complete control. They are actually sitting here & deciding in which location to store the data because data can be stored in two ways, centralized or distributed, Centralized means all the data is at one place, we are picking up the data from there only, storing the data at the same place. Distributed which we are using in today's time a lot, that instead of keeping the data in one place, We use it all over the world in multiple locations. We keep that data all over the world. So that concept is little different. Here you understand we have kept centralized and these people are deciding, that where to store the data actually. what are their files, what are the names of those files. And actually the most important point is, you must be thinking that here I am talking about the file and here i am talking about table. This is the biggest trick and most of the students gets very confused at this point, When we see that data, when I am viewing that data as a user, then in which format am I seeing, in the table form. But actually in database or in hard disk, it is not getting stored in the form of table. In hard disk, it is getting stored in the form of files. but we put a layer of database management system on top of that. So if we look at the data of Indian Railways in today's time, then how do I get the information of the trains, in the form of table, train number, train name, departure time, arrival time etc., a whole table comes, but what do you think it is stored in hard disks in the same way? No, in hard disk, actually it is being stored in the form of files. But How it is show to us, whatever view they have decided, which they have decided at the admin level. So the same point is here, the physical schema here tells what data is actually stored, That data, the actual data, the real data, whose job it is to store that data, the physical schema.. So here the concept of data independence is that, all these users are not connected directly to the database, in the middle we have put 3 layers, so these layers help in data abstraction, means the user know that there is data store, data is coming, it is coming from somewhere, but where is it coming from, where is the store, how is it coming and we don't even want to know this thing. Have you ever asked anyone in Google or in Facebook? No, Because we know that the may be cloud used, cloud computing used. I don't know any technologies in today's time, I just want to find that my data is available whenever I want, at which time I call the database, my mail should be show to me, that is shown to me. So that is the main concept of three schema architecture, and after that, we will see here the concept of mapping. But in this video i am telling only, what is actually 3 schema architecture. Thank you.

Hello friends, welcome to Gate Smashers
. The topic is three level of abstraction in DBMS or we call its three schema architecture. Before starting this topic  i just want to request you all &amp; specially who are watching my videos first time,  to please subscribe my channel, please share with your friends and please press the bell button to get the latest notification. So from this video firstly we are going to discuss, what is three schema architecture and why it is used. what is the meaning of three schema? I draw a diagram here,  three schema. Firstly, To understand the word what is the schema, schema means structure, structure means the data we are storing,
means that data is of any student&#39;s data, means that data is of any student&#39;s data, faculty&#39;s data, railways&#39;s data or Flipkart, Amazon is saving our data, whatever data we are storing, what is the structure to store that data,  structure means any data  like if i have a data 1, A, 20, Now this data means roll number is one name is A, age is 20, i am taking it just for an example. Now what i did to save this data, i put it in a table, table means put it in a 2 dimensional table where i have columns on this side, like I have written here roll number, name and age and here is my row means we are talking about relational database, which I have already told you earlier that where we store data in table form. So here what is schema, What is the structure, table and what comes in that structure, columns and rows. What is in the columns, roll number. So what is roll number actually, roll number is an integer. So what is the size of the integer, 2 byte, 4 byte means whatever we are describing about the structure, that structure is actually known as schema. Now here are 3 levels external schema or external level, conceptual level or conceptol schema, physical level or physical schema which we also called internal level. Now why these 3 levels have been made here? Actually in 1970 this concept came of 3 schema architecture, what is the main motive of this architecture is data independence. Data independence means user and data should not directly interact with each other. Means the user should not know where the data is stored, although the user or data
is related to each other. Because It&#39;s user&#39;s data, the user will call that
data whenever they wants, but where is that data stored, how is it stored, we keeping all this information abstract from the user, we are calling it data abstraction. Means the user knows that
my data is stored somewhere, but where is it stored, how is it stored,  we are hiding this thing from the user. We are hiding this thing from the user. If you are using this,  it&#39;s main example is gmail. We all are using Gmail, we have our mails in Gmail, we also keep our photos in it, we also keep important data in it, but But where is the mail actually stored? physically you know where it is stored, in Delhi, in Kolkata, in Mumbai, in Bangalore, in USA, in UK, where is it stored? We do not know its exact location, means, is it stored in the files? what is the format? We keep all these thing hidden from the user. To keep these things hidden, we have created 3 levels in the middle, means user and data, this is the database, where all the data is stored physically and this is the user,  user means us. Now  3 levels made between the user and the data. Its main motive is to make data independent means data independence, so that we can provide abstraction to the user. Now first is my external schema, what is the advantage of, what is the use of external schema? We also called it view level. I have already mentioned here view level. View level means how to show  the data to the user, how to represent the data to the user, that is the work of this level. Although it is a theoretical model But somewhere practical are based on these theoretical models only, means that all the web applications or mobile applications are
being made in today&#39;s time, I am not saying that they
are directly based on this. But yes this concept came in 1970, after that a lot of refinements took place. But majorly all the web applications we are using today, on their backend, somewhere these schemas, these concepts have been used. So I am telling the same point, if I tell with a simple example The university has a management system like if I taken admission in the university, then my login id password is made there. Now when I feel that login id password, then what information show to me, my marks, my Attendance, my fee structure, how much I have paid, means all this is a view. That view will be given to the user,  but will this view be common to all? No. If all the users had the same view, then I did not need to create separate views here, only one view would have worked.  But if I talk about the Faculty, Faculty in the same university, then Faculty is also looking at the attendance, Faculty also checking their marks, No. The students have their own view and the faculty has their different view. What can faculty do?  enter the marks, register the student&#39;s attendance, apply for leave, check their own salary and their own details. but can the student check the same details, No. So to prevent that thing, from the security point of view, we have made a view. So Whose view is this, student&#39;s. in which some authorization is their, If I speak in technical term, then authorization means that
we have authorized something, that you can also see it, 
you can also see that, you can read something but cannot be updated like the marks I put that can only be seen Can&#39;t be updated. So this is the student&#39;s view. One such view will be of the faculty, the faculty has its own authorization, similarly if there is a dean, then they will have their own view because they have more authorization, so what have we actually done provide their own view to everyone. Whatever the applications you are using today,  they also have a view, One is  normal view for us normal users, another view of admin means , the database can also have administration, the network can have a separate administration, there may be different views for different profile means job profiles, so in this example student&#39;s their own view, faculty&#39;s their own view, dean&#39;s their own view. This view is only helping me whether I am able to see the data, means as I turned on my laptop, I entered the login and password of my university URL, then what is coming on the first front page, that front page is actually a view. Or if I tell you that you are using any application  like on flipkart or amazon, you are login in it or you are using paytm, then what is the first view you get when you login, that view is actually a external schema. After that comes my conceptual schema, what is the meaning of conceptual schema? The way we are representing the data there, means how the data actully stored. My data  is here but how is its structure. like i tell a simple thing, there is a student&#39;s data, then what are the columns in the student&#39;s data. Like roll number, name, age, the address, there can be many columns. So we have to define this thing in advance. When we create database, first we have to create its conceptual view. You can understand schema &amp; conceptual with simple example E-R model. If you have already read about this E-R diagram or E-R model, then you will easily understand this otherwise I will explain to you in another video, E-R model. Here you just Understand that E-R model is a conceptual model. Data is actually stored here but how is that data stored, we have it in the form of a table like what does E-R model do, it makes different entities. That&#39;s how the relational model comes to, what do we do in the relational model, we use in majority for structure data, in relational model we create tables. So which tables we have used, information of those tables, student&#39;s table, faculty&#39;s table, library&#39;s table, like book&#39;s table. There are many tables that can be used, all that information is here. The relationship between those tables, relationship means how the table is connected? Now students and if I talk about course, are student and course interconnected? Obviously. There is a table of students which contains the information of the student and there is a table of courses which contains the information
of all the courses of the university. Is there any relation between
the student and the course, obviously. Student are studying the course, so yes there are some relationship between the table. What is that relationship? we have to mention here at this level, means we are already making a conceptual view of that data, you can say like blueprint, like before building a house, we make blueprint from the architecture, that is actually the Conceptual schema. To whom we give that view or print, we give it to the contractor or builder, that builder then starts
building by taking his material, that is what we are here are working on. What is this conceptual schema, it is a blueprint, in which, what tables we have to keep, which relationships, what should be the size of those tables, everything will be mentioned in this. But after that the main story comes here, where is the data stored actually, we have stored data here, in which files we have stored those data, what is their size, means that, the data is actually stored in which hard disks. What is the store location, where is all these information? In the physical schema. From the name &quot;physical&quot; itself, it is known that where is
the data physically present. So who are actually here, database administrator. Database administrator decides at this level, which data is to be kept
in which particular location, in which particular drive, how to fragment it, how to store it. Where we decides all these information, in physical schema. so that means  designer, normal database designer, where are they working, at this level. The interface designer who works on the front end like someone is working on PHP, then if I am creating an
interface on PHP&#39;s frontend or I am creating a simple interface using Java or I am creating an interface using HTML, then where am I sitting, at this point. So if in a company, the
work is going on in 3 levels, so the people from the front end who are creating an interface using PHP or Java on the front end, they are working here. Who are working on the second level, the database designers, who only have to design the data, do not have to store them actually, only have to design them
and give them a blueprint, this one. And at the third level, actually the database administrator. The administrator who is administering the entire data, those who have complete control. They are actually sitting here and deciding in which location to store the data because data can be stored in two ways, centralized or distributed, The administrator who is
administering the entire data, those who have complete control. They are actually sitting here &amp; deciding in which location to store the data because data can be stored in two ways, centralized or distributed, Centralized means all the data is at one place, we are picking up the data from there only, storing the data at the same place. Distributed which we are
using in today&#39;s time a lot, that instead of keeping the data in one place, We use it all over the
world in multiple locations. We keep that data all over the world. So that concept is little different. Here you understand we have kept centralized and these people are deciding, that where to store the data actually. what are their files, what are the names of those files. And actually the most important point is, you must be thinking that  here I am talking about the file and here i am talking about table. This is the biggest trick and most of the students gets very confused  at this point, When we see that data, when I am viewing that data as a user, then in which format am I seeing, in the table form. But actually in database or in hard disk, it is not getting stored in the form of table. In hard disk, it is getting stored in the form of files. but we put a layer of database management system on top of that. So if we look at the data of  Indian Railways in today&#39;s time, then how do I get the information of the trains, in the form of table, train number, train name, departure time, arrival time etc., a whole table comes, but what do you think it is stored in hard disks in the same way? No, in hard disk, actually it is being stored in the form of files. But How it is show to us, whatever view they have decided, which they have decided at the admin level. So the same point is here, the physical schema here tells what data is actually stored, That data, the actual data, the real data, whose job it is to store that data, the physical schema.. So here the concept of data independence is that, all these users are not connected directly to the database, in the middle we have put 3 layers, so these layers help in data abstraction, means the user know that there is data store, data is coming, it is coming from somewhere, but where is it coming from, where is the store, how is it coming and we don&#39;t even want to know this thing. Have you ever asked anyone in Google or in Facebook? No, Because we know that the may be cloud used, cloud computing used. I don&#39;t know any technologies in today&#39;s time, I just want to find that my data is available whenever I want, at which
time I call the database, my mail should be show to me, that is shown to me. So that is the main concept of three schema architecture, and after that, we will see here the concept of mapping. But in this video i am telling only, what is actually 3 schema architecture. Thank you.

Transcript for:Understanding Three Schema Architecture in DBMS

Transcript for:
Understanding Three Schema Architecture in DBMS