| Lesson 5 | Schema architecture |
| Objective | User views, logical schema, and physical schema. |
Three Schema Architecture
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.
- 1) User view is for interaction with database users,
- 2) logical schema is used by data modelers, and
- 3) physical schema is used by system programmers
1) User views
The notion of user views was mentioned earlier.
SQL is used to create (DDL) data definition language, which enables the creation of tables. (DML)Data Manipulation language is used to invoke user views. User views specify which users are permitted access to what 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.
SQL is used to create (DDL) data definition language, which enables the creation of tables. (DML)Data Manipulation language is used to invoke user views. User views specify which users are permitted access to what 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.
- user view: Specifies which users are permitted access to specific data in a database.
- logical design: The second stage in the DBLC: creating a logical schema, followed by data normalization.
- physical design: The third stage in the DBLC: tweaking data design elements to optimize database performance.
2) Logical Schema
A database's logical schema is its overall logical plan. This schema is developed with 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.
Logical Design
The logical design 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.
In fact, the most significant end-product of logical design is the logical schema.
3) Physical Schema
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 (if the RDBMS supports storage manipulation).
Physical Design
The physical design will be discussed later is different from the physical schema. Physical design involves tuning a database to optimize performance, which does involve the databased designer.
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.
Three Schema Architecture
The next lesson introduces a special class of tools often used in database design.
