Databases are characterized by a three-schema architecture because there are three different ways to look at them.
Each schema is important to different groups in an organization. The graphic below illustrates this architecture and the groups most involved with each schema.
User view is for interaction with database users,
logical schema is used by data modelers, and
physical schema is used by system programmers and application developers
Three Schema Architecture in Detail
The "Three Schema Architecture" is a fundamental concept in database design and management, aimed at promoting the concept of data abstraction and system independence. This architecture is divided into three distinct levels:
User Views (External Schema): This level is the highest level of abstraction in the database system. It consists of multiple user views, each tailored to the specific needs and requirements of different user groups or applications. These views provide a way to access only a subset of the database that is relevant to a particular user or application, thus ensuring security, ease of use, and data integrity. The external schema describes the various views of the database, presenting data in a way that is both understandable and relevant to the users.
Logical Schema (Conceptual Schema): This middle level represents the overall logical structure of the entire database as seen by the database manager. It is a complete view of the data requirements and relationships within the system, independent of physical storage details. The logical schema defines the tables, the fields in each table, and the relationships between tables, but it does not define how these tables and relationships are stored in the physical storage. This level of abstraction allows for changes to be made to the logical schema without affecting the user views or the physical storage.
Physical Schema (Internal Schema): The lowest level of the three-schema architecture, the physical schema, deals with the physical storage of data on a storage device. It specifies the exact storage structure and access methods used to store and retrieve the data, such as file structures, indexing techniques, and data compression methods. The physical schema is designed for optimal performance and storage efficiency, and changes at this level are typically transparent to users and applications.
The primary advantage of the Three Schema Architecture is the separation of concerns it offers. Changes at one level (like altering the physical schema for performance tuning) typically do not necessitate changes at other levels (such as the user views or logical schema), thereby ensuring system stability and flexibility. This architecture allows database administrators to manage physical data storage without affecting how data is presented to users, and also enables them to modify the logical schema without impacting user interfaces or applications.
1) User views
The notion of user views[1] was mentioned earlier. SQL is used to create (DDL) data definition language, which enables the creation of tables. The data manipulation language[2] is used to invoke user views. User views specify which users are permitted access to the specific data in a database. For example, an employee database might contain employee names, addresses, and phone numbers. An employee database is likely to contain employee salaries and other sensitive data as well. A user view can be created that enables a secretary, for example, to gain access to the names and addresses of employees. This is useful when it is time to send out company Christmas card but is not useful for salary information.
2) Logical Schema
A database's logical schema is its overall logical plan. This schema is developed with ERD diagrams that define the content of database tables and describe how the tables are linked together for data access. Database designers are responsible for creating the logical schema. Application developers and database administrators may find the logical schema useful for performing certain tasks.
The logical design[3] will be discussed later, is directly related to the logical schema. In fact, the most significant end-product of logical design is the logical schema.
3) Physical Schema and Design
The physical schema of a database refers to how data is stored on the computer on which it resides.
Generally (but not always), physical storage is handled by the RDBMS. Neither users nor database designers get involved in the physical schema. Storage manipulation is a task for the systems programmer, who interact with the RDBMS to perform basic CRUD operations.
The physical design will be discussed later is different from the physical schema. Physical design[4] involves tuning a database to optimize performance, which does involve the databased designer.
Three Schema Architecture consisting of 1) External View, 2) Conceptual Level, 3) Internal Level
Before moving on to the next lesson, click the link below to read more about database three-schema architecture. Three Schema Architecture
The next lesson introduces a special class of tools often used in database design.
[1]user views:Saved queries created with SQL and specifies which users are permitted access to specific data in a database.
[2]DML:DML (Data Manipulation Language) is a set of SQL commands used to manage and modify data within existing database tables, such as adding, removing, or changing records.
[3]logical design: The second stage in the DBLC: creating a logical schema, followed by data normalization.
[4]physical design: The third stage in the DBLC: tweaking data design elements to optimize database performance.
