Lec-6: Three Schema Architecture | Three Level of Abstraction | Database Management System
Summary
TLDRIn this video, the concept of three-schema architecture in DBMS is explained, focusing on the external, conceptual, and physical levels. The speaker highlights how data is structured and abstracted from users, ensuring data independence. Using examples like Gmail and university systems, they demonstrate how different users (students, faculty, admins) interact with different views of the same data. The video emphasizes that while users see data in table form, it is actually stored in files on a physical level. This abstraction ensures users access data without needing to know its actual storage details.
Takeaways
- π The video introduces the concept of three schema architecture in Database Management Systems (DBMS), which includes the external schema, conceptual schema, and physical schema.
- π The primary purpose of the three schema architecture is to achieve data independence, ensuring that users do not need to know where or how the data is stored.
- π¨βπ« The video emphasizes the importance of data abstraction, where the details of data storage are hidden from the user, allowing for flexibility and security in data management.
- ποΈ The external schema, also known as the view level, determines how data is presented to the user, with different views for different roles, such as students, faculty, and administrators.
- ποΈ The conceptual schema acts as a blueprint for the database, defining the structure of the data, including tables, columns, and their relationships, without detailing the physical storage.
- π οΈ The physical schema, managed by the database administrator, deals with the actual storage of data, including file organization, storage location, and whether the data is centralized or distributed.
- π₯ The video uses the example of Gmail to illustrate data abstraction, where users are unaware of the physical storage location of their emails and data.
- π It explains the concept of views in the context of a university management system, where different users have access to different views of the data based on their roles and authorizations.
- π The video touches on the evolution of web and mobile applications, suggesting that while not directly based on the three schema architecture, many modern applications incorporate similar concepts.
- π The physical schema is crucial for database administrators who decide on data storage strategies, including the use of centralized or distributed databases.
- π The video concludes by highlighting the importance of data independence and the role of the three schema architecture in providing a layer of abstraction that simplifies user interactions with data.
Q & A
What is the three-schema architecture in DBMS?
-The three-schema architecture in DBMS refers to the external schema, conceptual schema, and internal schema, which are designed to provide data independence and abstraction between the user and the physical storage of data.
Why was the three-schema architecture introduced?
-The three-schema architecture was introduced to achieve data independence, which means users should not directly interact with the data storage details, thus providing a layer of abstraction.
What is the purpose of the external schema?
-The external schema, also known as the view level, determines how data is presented to the user. It provides a customized view for different users based on their authorization levels.
How does the conceptual schema differ from the external schema?
-The conceptual schema represents how data is actually stored in the database, including the structure and relationships among data, while the external schema focuses on how data is presented to the user.
What is the role of the internal schema in the three-schema architecture?
-The internal schema, also known as the physical schema, deals with the physical storage of data, such as where and how data is stored on the hard disk, and is managed by the database administrator.
Why is data independence important in database management?
-Data independence is important because it allows changes to the physical storage of data without affecting the users or applications that interact with the data, thus providing flexibility and ease of maintenance.
Can you provide an example of how the external schema is used in real life?
-An example of the external schema in real life is Gmail, where users interact with their emails, photos, and data without knowing the physical location or format of storage.
What is the significance of the E-R model in relation to the conceptual schema?
-The E-R model is significant in the conceptual schema as it helps in defining the structure and relationships among data entities, which is a blueprint for how data is organized in the database.
How does the three-schema architecture contribute to data security?
-The three-schema architecture contributes to data security by providing different views and authorization levels for different users, ensuring that sensitive data is protected from unauthorized access.
What is the difference between centralized and distributed data storage as mentioned in the script?
-Centralized data storage refers to keeping all data in one location, whereas distributed data storage involves storing data across multiple locations worldwide, which is more common in modern applications.
How does the database management system (DBMS) transform data storage from files to tables?
-DBMS provides a layer of abstraction that allows users to view and interact with data in the form of tables, even though the data is physically stored as files on the hard disk.
Outlines
π Introduction to Three Schema Architecture in DBMS
The speaker begins by welcoming viewers to the video on 'Gate Smashers' and introduces the topic of three levels of abstraction in Database Management Systems (DBMS), also known as the three-schema architecture. The speaker requests new viewers to subscribe, share, and enable notifications for the channel. The video aims to explain the concept of three-schema architecture, its purpose, and the meaning of 'schema' as a structure for storing data. A diagram is drawn to illustrate the three levels: external schema, conceptual schema, and physical schema. The main motive behind this architecture is data independence, ensuring that users do not directly interact with the data storage process. The speaker uses Gmail as an example of data abstraction, where users are unaware of the physical storage location of their data. The video promises to delve into the advantages and applications of each schema level.
π¨βπ External Schema: User Views and Data Representation
The speaker explains the external schema, also known as the view level, which determines how data is presented to the user. Different users, such as students and faculty, have different views of the data based on their roles and authorizations. The external schema is crucial for security, ensuring that users only see and interact with the data relevant to them. Examples include a university management system where students see their marks and attendance, while faculty can enter marks and manage attendance. The speaker emphasizes that modern web and mobile applications use similar concepts, even if not directly based on the three-schema architecture. The external schema represents the user's view of the data, which is abstracted from the underlying database structure.
π Conceptual Schema: Data Structure and E-R Model
The conceptual schema is described as a blueprint for how data is actually stored within the database. It defines the structure of the data, including the tables and their relationships. The speaker uses the E-R (Entity-Relationship) model as an example of a conceptual model, which helps in understanding the structure of data before it is physically stored. The conceptual schema is the responsibility of database designers who create a plan for the data's organization and relationships without worrying about the physical storage. This level is crucial for designing the database and ensuring that the data is organized in a way that supports the relationships and queries required by the application.
π οΈ Physical Schema: Data Storage and Database Administration
The physical schema is the layer that deals with the actual storage of data on physical media like hard disks. It is the responsibility of the database administrator to decide where and how the data is stored, including file names, storage locations, and whether the data is centralized or distributed. The physical schema is the most technical level, focusing on the efficiency and optimization of data storage. The speaker clarifies the difference between how data is viewed by users (in table form) and how it is actually stored (in files). The physical schema ensures data independence by abstracting the storage details from the users, allowing them to focus on data access rather than storage mechanisms.
π Conclusion: The Importance of Three Schema Architecture
The speaker concludes by summarizing the main concept of the three-schema architecture, emphasizing its role in data abstraction and independence. The architecture allows for flexibility in data storage and presentation, catering to different user needs and roles. The speaker hints at the upcoming discussion on the concept of mapping within this architecture, but for this video, the focus remains on explaining the three-schema architecture. The video ends with a thank you note to the viewers.
Mindmap
Keywords
π‘Three Schema Architecture
π‘Data Independence
π‘External Schema
π‘Conceptual Schema
π‘Physical Schema
π‘Data Abstraction
π‘Relational Database
π‘E-R Model
π‘Database Administrator
π‘Centralized vs Distributed Data Storage
Highlights
Introduction to the three-level abstraction in DBMS, also known as the three-schema architecture.
Explanation of the term 'schema' as it relates to the structure of stored data.
Description of how data is organized in a relational database using tables, columns, and rows.
Introduction of the three levels of schema: external, conceptual, and physical.
The historical context of the three-schema architecture's development in the 1970s.
The primary goal of the architecture: to achieve data independence.
The concept of data abstraction and its role in hiding data storage details from the user.
The practical example of Gmail to illustrate data abstraction in everyday applications.
The role of the external schema in defining user views and its importance in security and data representation.
Different views for different user roles, such as students, faculty, and deans, within a university management system.
The conceptual schema as a blueprint for data storage, including tables and their relationships.
The use of E-R models to understand the conceptual schema and its relation to the relational model.
The physical schema's focus on the actual storage of data, including file names and storage locations.
The responsibilities of database administrators in managing the physical schema and data storage decisions.
The difference between centralized and distributed data storage and their implications.
The distinction between how data is viewed by users in table form and how it is actually stored in files on the hard disk.
The importance of data independence in allowing users to access data without knowing the underlying storage mechanisms.
The concept of mapping in the context of the three-schema architecture, which will be discussed in future videos.
Transcripts
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.
Browse More Related Video
Lec-7: What is Data Independence | Logical vs. Physical Independence | DBMS
What is Database & Database Management System DBMS | Intro to DBMS
L-7.1: File System in Operating System | Windows, Linux, Unix, Android etc.
Welcome to the AI Brain Bank
What is DATABASE MANAGEMENT SYSTEM or DBMS | Intro to DBMS
AI-powered Documentation, Search and Discovery
5.0 / 5 (0 votes)