DBMS-Level of Abstraction

DBMS-Level of Abstraction 

Levels of Abstraction in a DBMS

The data in a DBMS is described at three levels of abstraction, as illustrated in Figure The database description consists of a schema at each of these three levels of abstraction: 

the conceptual, physical, and external schemas.

A data de nition language (DDL) is used to de ne the external and conceptual schemas. We will discuss the DDL facilities of the most widely used database language, SQL, DISK External Schema 1 

External Schema 2 External Schema 3 Conceptual Schema, Physical Schema

 Levels of Abstraction in a DBMS

standard. Information about the conceptual, external, and physical schemas is stored

in the system catalogs

Conceptual Schema

The conceptual schema (sometimes called the logical schema) describes the stored data in terms of the data model of the DBMS. In a relational DBMS, the conceptual schema describes all relations that are stored in the database. In our sample university database, these relations contain information about entities, such as students and faculty, and about relationships, such as students' enrollment in courses. All student entities can be described using records in a Students relation, as we saw earlier. In fact, each collection of entities and each collection of relationships can be described as a relation, leading to the following conceptual schema:

Students(sid: string, name: string, login: string, age: integer, gpa: real)

Faculty( d: string, fname: string, sal: real)

Courses(cid: string, cname: string, credits: integer)

Rooms(rno: integer, address: string, capacity: integer)

Enrolled(sid: string, cid: string, grade: string)

Teaches( d: string, cid: string)

Meets In(cid: string, rno: integer, time: string)

The choice of relations, and the choice of

Introduction to Database Systems 13

Physical Schema

The physical schema speci es additional storage details. Essentially, the physical schema summarizes how the relations described in the conceptual schema are actually stored on secondary storage devices such as disks and tapes.

We must decide what le organizations to use to store the relations, and create auxiliary data structures called indexes to speed up data retrieval operations. A sample physical schema for the university database follows:

Store all relations as unsorted les of records. (A le in a DBMS is either a collection of records or a collection of pages, rather than a string of characters as in an operating system.) Create indexes on the rst column of the Students, Faculty, and Courses relations, the sal column of Faculty, and the capacity column of Rooms.

Decisions about the physical schema are based on an understanding of how the data is typically accessed. The process of arriving at a good physical schema is called physical database design. 

External Schema

External schemas, which usually are also in terms of the data model of the DBMS, allow data access to be customized (and authorized) at the level of individual users or groups of users. Any given database has exactly one conceptual schema and one physical schema because it has just one set of stored relations, but it may have several external schemas, each tailored to a particular group of users. Each external schema

consists of a collection of one or more views and relations from the conceptual schema. 

The external schema design is guided by end user requirements. For example, we might want to allow students to nd out the names of faculty members teaching courses, as well as course enrollments. This can be done by de ning the following view: 

Courseinfo(cid: string, fname: string, enrollment: integer)

A user can treat a view just like a relation and ask questions about the records in the view. Even though the records in the view are not stored explicitly, they are computed as needed. We did not include Courseinfo in the conceptual schema because we can compute Courseinfo from the relations in the conceptual schema, and lead to inconsistencies. For example, a tuple may be inserted into the Enrolled relation, indicating that a particular student has enrolled in some course, without incrementing the value in the enrollment eld of the corresponding record of Courseinfo (if the latter also is part of the conceptual schema and its tuples are stored in the DBMS).