Hello everyone, welcome back. In this presentation, we will focus on instances and schemas. These two are the important terminologies as far as database management systems is concerned.
In order to understand things, let's see an analogy. We know basically any software is developed using some programming languages. So obviously software programs are written.
Who writes the software program? The programmer. Let's assume a software programmer is defining a variable a.
So, this variable is obviously going to hold some value in it. Now, who is giving value to this variable? The software program that he has written, right?
And the value for this variable will be existing within the scope of the software program. Let's assume this software program is modifying this variable a as 20 now. And it doesn't mean that this 20 is always fixed to this variable.
It may be changing according to the situation. So, the variable a, now it is containing 20. The value of the variable will be varying and that's why it is named as variable. And what I mean to say here is, a software variable may contain any value in it.
But what about the type of value it is storing? It is actually storing integer type in this example. So, if you note here, the variable a is declared as integer type and let's assume this program is written in C language and let this variable be integer type.
So, when it is declared as integer in C language, the size of that variable is going to be 2 bytes, in other words 16 bits. So, what I mean to say here is the value will be changed but the structure and the memory that is assigned to this variable a is fixed which is of 2 bytes in nature. So, with this analogy, let's step into the topic of the day.
Please be noted that the value of the variable may change, but not the structure or the size of the variable that is fixed here. So, with this knowledge, let's step into seeing the definition of schemas. What is actually a schema?
It is actually the overall design of the database. What is a database? It is a collection of tables.
What is a table? It is a data structure where the data is organized in the form of rows and columns. So, when we have a table and when we have collection of tables, we refer that as database. And the overall design or the structure of the database is actually referred as schema. The overall design may involve multiple tables.
Say for example, if we consider a banking database, it's not only one table will be there in the banking database. Tables like customer, accounts, loan, assets and many more should be maintained in their banking database. Any database we take, we will have multiple tables.
So, the overall design or the structure of the database is referred as schema. And how this is related to the analogy that we have seen? So, here the database schema is actually referred as the variable declaration.
In the analogy we have seen, we have defined the variable as integer which is of two bytes when it is declared in C language. So, the data type is integer and the size of the data type is of two bytes because we have declared it as integer. integer in C language.
So the variable declaration is fixed. It may not be modified frequently. It's like the database schema.
So the database schema is also like the variable declaration where the schema is not frequently changed but the data present in the table may change. But we are talking about schemas. So this schema is like the variable declaration.
So this is like the type definitions we make in any programming languages. That's what we have seen in the analogy. And what about the changes or updations in the schema? Will it be frequently done or not at all or occasionally done?
Once the schema is fixed, it's not frequently changed and sometimes it's not at all changed because the schema is actually developed based on the requirement. Once the schema is fixed, it's not frequently changed or even it is not at all changed. But there will be scope for changing. And that's why I related schema with the variable declaration.
Once a variable is declared, the size is fixed and it will not be changed. But there are some dynamic memory allocation techniques in any programming language to change the data type. But not frequently done. Anyway, if there is a requirement to change the schema, of course the database administrator can change the schema. I'll show you an example type definition which will help us to understand about schemas.
So, we are going to define a record type of student. Here the record type that I am creating is student and what are all the fields that this student record is going to contain? The first one, the first field is the role number which is of numeric type and the maximum size is 5. The second field is the name of the student which is of char type and the maximum size of the student name is 25 characters.
And the last field is the class in which the student is studying. And this class field is also of char type and the maximum size is 10. And we are ending this. If you note here, this is the structure that we have created. This is the student record which contains three fields, role number, name and class. So, this structure is not frequently changed.
And that's why I told you schemas are like the structure or the overall design of the database. Now, I will correlate this with the real-time example. Let's take university as an example.
In a university, the department record or table will contain columns or fields like the department name, the department building, the budget, the name of the head of the department and other fields. Courses will contain fields like course ID, title, department name, credits and so on. And the student record or student table will contain fields or columns like the role number or the ID, name, department name, total credits, etc.
At the physical level, these tables or records can be described as a block of some consecutive storage locations. And obviously, the system or the compiler will hide all these level of details from the programmers. And that's what we call as data abstraction. It hides the complexity because programmer doesn't need all those internal details.
So, the database systems hides many of the storage level details from the database programmers. On the other hand, database administrators will be aware of certain additional details about the physical organization of the database. Because he is the database administrator, he has the complete control about the database and he has additional responsibilities.
So, we are done with the schemas. Let's now move on to the next definition, instances. And where we are going to talk about these instances?
Obviously, in the databases. What is a database? It's a collection of interrelated data. When we say that it is a collection of interrelated data and the collection of the information stored in the database at that particular moment is called as an instance. Say for example, databases can change over time.
The data may be added or the data may be deleted. So, the database is the same but the data present in the database is not the same in all the instances. I mean it's not the same throughout, isn't it? So, the data present in the database may change.
And that's why I said the collection of information stored in the database at a particular moment is referred as an instance. We know the structure of the database will not be changed frequently, but the data present in the database will be changed frequently. Let's assume I'm maintaining a university database. At this particular time, let's assume I have 1000 records. Maybe if 100 students are admitted tomorrow.
then the database size will be changed, the data present in the database will also be changed. But the structure is not changed frequently, isn't it? So, the collection of information at that particular moment is called as an instance. And how this is related to the analogy we have seen? We have seen the variable definition or the type definition is equivalent to the schema, the value present in the variable is equivalent to the instance.
So, the database instance means it's equivalent to the value of the variable that we have seen in the analogy. Because the value of the variable keeps on changing as per the need. And obviously, databases can grow and shrink. I hope now you are clear with what is an instance. What are the three levels of abstraction we have seen?
The view level, logical level and physical level. Likewise, when we talk about the schema and instances, we should be knowing that there exist sub-schemas at the view level. There exist multiple views. isn't it?
And then we have the logical schema at the logical level and then we have one more schema at the bottom most level. What's that? It's actually the physical schema where the database actually reside. It's actually the database where the data are actually stored in the physical medium. Data how they are stored it is in the physical schema.
How they are organized in the application or in the language it is done in the logical schema. how it is presented to the user and how many views are there that is dealt in the sub schema. I hope now you can understand what are schemas and instances.
I'll see you in the next presentation and thank you for watching.