Lec-6: Three Schema Architecture | Three Level of Abstraction | Database Management System

Summary

In 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

00:00

📚 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.

05:03

👨‍🎓 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.

10:05

🔍 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.

15:07

🛠️ 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.

Keywords

💡Three Schema Architecture

Three Schema Architecture refers to the hierarchical organization of data in a Database Management System (DBMS). It consists of the External Schema, Conceptual Schema, and Physical Schema. This architecture is crucial for data independence, which means users interact with data without needing to know the details of its storage or structure. In the video, the speaker explains that this architecture abstracts the data storage details from the user, allowing for flexibility and easier data management.

💡Data Independence

Data independence is a principle in database design that aims to separate the logical and physical layers of data storage. This means changes in the physical storage of data do not affect the user's view or access to the data. The video emphasizes the importance of data independence in maintaining the integrity and usability of data, as it allows for modifications in the database's physical structure without impacting the users or applications that rely on the data.

💡External Schema

The External Schema, also known as the View Level, determines how data is presented to the user. It represents a user's view of the data, which can vary based on their role or authorization. The video uses the example of a university management system where students, faculty, and deans have different views of the data, tailored to their specific needs and permissions.

💡Conceptual Schema

The Conceptual Schema defines the overall structure of the data in a database, independent of how it is stored or accessed. It includes the definition of tables, relationships between tables, and the types of data stored. The video likens the Conceptual Schema to a blueprint, which is used by database designers to plan the database structure before it is physically implemented.

💡Physical Schema

The Physical Schema describes the actual storage details of the data, such as file organization, data placement on storage devices, and access methods. It is managed by database administrators who decide where and how data is physically stored. The video explains that while users see data in table format, the Physical Schema deals with how this data is actually stored in files on the hard disk.

💡Data Abstraction

Data Abstraction is the process of hiding the details of data implementation from the user. It allows users to interact with data without needing to understand the underlying storage mechanisms. The video discusses how data abstraction is facilitated by the three schema architecture, providing a layer of indirection that simplifies user interaction with the database.

💡Relational Database

A Relational Database is a type of database that stores and organizes data in the form of tables, where each table represents an entity and rows represent records of that entity. The video mentions relational databases in the context of organizing data in tables with columns and rows, which is a fundamental concept in database management.

💡E-R Model

The E-R Model, or Entity-Relationship Model, is a conceptual tool used to design databases by visualizing the relationships between different entities. The video suggests that understanding the E-R Model can help in grasping the Conceptual Schema, as it represents how entities and their relationships are structured within a database.

💡Database Administrator

A Database Administrator is a professional responsible for managing the physical implementation of a database. They handle tasks such as data storage, security, and backup. The video highlights the role of the Database Administrator in the Physical Schema level, where they decide the physical storage and organization of data.

💡Centralized vs Distributed Data Storage

Centralized Data Storage refers to storing all data in one location, while Distributed Data Storage involves spreading data across multiple locations. The video discusses how Database Administrators decide on the storage strategy, which can impact the performance and accessibility of the data. The concept is relevant to the Physical Schema, as it determines where and how data is physically stored.

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.