 Professor from Department of Electronics Engineering from Walton Institute of Technology, Sulapur. So, today we are going to have a session on data models, schemas and instances, three schema and architecture. Learning outcome. After the end of this session, student will be able to discuss the main characteristics of data model, differentiate between the database schema and database state and describe the three schema architecture. Design of database is called as a schema. In the following diagram, we have a schema that shows the relation between the three tables, course, student and the session. The diagram only shows the design of the database. It doesn't show the data present in these tables. The schema is only a structural view of the design of the database as shown in the figure. The figure here shows the database schema. You can see there are three tables, the course table, student table and the section. So, all the three tables are interconnected with each other. Database schema is also called as an blueprint. It only shows the design of the database. It doesn't show the data present in those tables. Schema is of three types, physical schema, logical schema and the view schema. The design of database at the physical level is called as a physical schema. How the data is stored in the blocks of storage is described at this level. The design of database at the logical level is called as a logical schema. The programmers and the database administrators work at this level. At this level, data can be described as certain types of data records get stored in the data structures. However, the internal details such as implementation of how data structure is hidden at this level. So, the physical schema describe how the data is stored in the storage blocks. The logical schema describe about the various data structure and how it is constructed and it is done by the programmers and the database administrators. Design of database at view level is called as a view schema. This generally describes the end user interaction with the database system. So, in the view schema, there are the end users who are going to work on the database. The data stored in the database at a particular moment of time is called as an instant of database. For example, let us say we have a single table student in the database, today the table has 100 records. So, today the instant of the database has 100 records. Let us say we are going to add another 100 records in the table by tomorrow. So, the instant of database tomorrow will be 200 records. In short, a particular moment the data stored in the database is called as an instant. That changes over time when we add or delete the data from the database. Database model defines the logical design and the structure of database and defines how data can be stored, accessed and updated in the database management system. There are different four models used for understanding the structure of database. First is the hierarchical model, network model, entity relational model and the relational model. Let us go one by one. Let us start with the hierarchical model. This database model organizes data in a tree like structure. When you have a single root to which all the other nodes are connected. The hierarchical starts from the root node and expands like a tree. Taking child nodes to the parents. In this model, a child node will only have a single parent. So, this is the hierarchical model. It is a tree like structure. You are having parent node further divided into the child node. This child node gets become a parent node and divided into the child node and so on. Next is the network model. This is an extension of the hierarchical model. In this model, data is organized more like a graph and are allowed to have more than one parent node. In this database model, data is more related as more relations are established in this database model. Also, the data is stored, hence accessing data is easier and faster. This was the most widely used database model before relational model was introduced. So, this is the figure of the network model which is an extension of the hierarchical model. We have seen the hierarchical model. There was only a single parent node for the child node. But as you can see in the network model, one child node can have more than one parent at a time. So, this is the difference between the hierarchical model and the network model. Next is the entity relational model. In this database model, relations are created by dividing the object of interest into the entity and its characteristic attributes. Different entities are related using relations. This model is good to design database which can be turned into the table in relational models. So, this is the figure of the entity relational model. So, student is the entity and it is branched as the different attributes called name, id, address and the age. So entity is related to the different attributes and it is very easier to convert this model into the tables. Next is the relational model. In this model, data is organized in two dimensional tables and the relation is maintained by storing common fields. This model was introduced by Codd in 1970 and since then it has been the most widely used database model. In fact, we can say that only database model used around the world. The basic structure of the data is the relational model in tables. All the information related to the particular type is stored in the form of rows of the table. Hence, the tables are known as relations in the relational model. So previously we have seen the entity relational model. Now that entity relational model is converted into the relational models in the form of tables. So the figure shows the three tables, the student table, subject table and the combination of student table and the subject tables into the marks. So you can see there are common field between the three tables. So all the three tables are integrated with each other and they are having some common fields. The student ID column is carried into the third table and the subject ID is carried into the third table. So these are the common fields and this is how the relational models are defined and it is most widely used till date. Let us start with the three schema architecture. The goal of the three schema architecture is to separate the user applications from the physical database. In these architectures, schemas can be defined at the following three levels. The internal level, conceptual level and the external level. So let's see all the three levels one by one. First is the internal level. The internal level has an internal schema which describes the physical storage of the database. The internal schema uses the physical data model and describes the complete details of the data storage and access path for the database. So this is the three schema architecture. As you can see the three schema architecture is divided into the internal level, the conceptual level and the external level. And this is about the internal level. You can see the internal level is the lowest level of the three schema architecture and it describes about the how data is been stored in the storage and even the access path is been defined in the internal levels. So whatever is the access path, depending upon the access path, the speed of the database is been calculated. Next is the conceptual level. The conceptual level has conceptual schema which describes the structure of the whole database for the community of the users. The conceptual schema hides the detail of the physical storage, structures and concentrates on describing the entities, data types, relations, operations and the constraints. Usually a representation data model is used to describe the conceptual schema when a database is been implemented. So this is the three schema architecture and the middle layer is the conceptual schema. In this conceptual schema, you are going to describe the different types of data structures, different entities, different data types, relations, operations and the constraints. So conceptual schema, whenever you are going to describe the conceptual schema or the conceptual level, all the physical layers or the physical schema is been hidden in the conceptual levels. Means the actual storage of the data is been hidden in the second level, the conceptual level. Next is the external level. The external or the view level includes the number of external schemas or the end users. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from the other group. As in the previous level, each external schema is typically implemented using representation of data models. So this is the external users. As you can see, in the external users, you have the end users who are going to work on the database. As we have studied in the characteristics of database, database management system provides the multiple views for each user. So whichever view is required for that particular user is been provided and all other details are hidden from that particular user. The references are taken from Fundamentals of Database Management Systems by Ramesh Inmasi and Shankar B. Naute. Thank you.