Descripe the architecture of a DBMS in terms of 3 schema architecture?

Answer 1

We now discuss a conceptual framework for a DBMS. Several different frameworks have been suggested over the last several years. For example, a framework may be developed based on the functions that the various components of a DBMS must provide to its users. It may also be based on different views of data that are possible within a DBMS. We consider the latter approach.

A commonly used views of data approach is the three-level architecture suggested by ANSI/SPARC (American National Standards Institute/Standards Planning and Requirements Committee). ANSI/SPARC produced an interim report in 1972 followed by a final report in 1977. The reports proposed an architectural framework for databases. Under this approach, a database is considered as containing data about an enterprise. The three levels of the architecture are three different views of the data:

1. External - individual user view
2. Conceptual - community user view
3. Internal - physical or storage view

The three level database architecture allows a clear separation of the information meaning (conceptual view) from the external data representation and from the physical data structure layout. A database system that is able to separate the three different views of data is likely to be flexible and adaptable. This flexibility and adaptability is data independence that we have discussed earlier.

We now briefly discuss the three different views.

The external level is the view that the individual user of the database has. This view is often a restricted view of the database and the same database may provide a number of different views for different classes of users. In general, the end users and even the applications programmers are only interested in a subset of the database. For example, a department head may only be interested in the departmental finances and student enrolments but not the library information. The librarian would not be expected to have any interest in the information about academic staff. The payroll office would have no interest in student enrolments.

The conceptual view is the information model of the enterprise and contains the view of the whole enterprise without any concern for the physical implemenation. This view is normally more stable than the other two views. In a database, it may be desirable to change the internal view to improve performance while there has been no change in the conceptual view of the database. The conceptual view is the overall community view of the database and it includes all the information that is going to be represented in the database. The conceptual view is defined by the conceptual schema which includes definitions of each of the various types of data.

The internal view is the view about the actual physical storage of data. It tells us what data is stored in the database and how. At least the following aspects are considered at this level:

1. Storage allocation e.g. B-trees, hashing etc.
2. Access paths e.g. specification of primary and secondary keys, indexes and pointers and sequencing.
3. Miscellaneous e.g. data compression and encryption techniques, optimisation of the internal structures.

Efficiency considerations are the most important at this level and the data structures are chosen to provide an efficient database. The internal view does not deal with the physical devices directly. Instead it views a physical device as a collection of physical pages and allocates space in terms of logical pages.

The separation of the conceptual view from the internal view enables us to provide a logical description of the database without the need to specify physical structures. This is often called physical data independence. Separating the external views from the conceptual view enables us to change the conceptual view without affecting the external views. This separation is sometimes called logical data independence.

Assuming the three level view of the database, a number of mappings are needed to enable the users working with one of the external views. For example, the payroll office may have an external view of the database that consists of the following information only:

1. Staff number, name and address.
2. Staff tax information e.g. number of dependents.
3. Staff bank information where salary is deposited.
4. Staff employment status, salary level,leave information etc.

The conceptual view of the database may contain academic staff, general staff, casual staff etc. A mapping will need to be created where all the staff in the different categories are combined into one category for the payroll office. The conceptual view would include information about each staff's position, the date employment started, full-time or part-time, etc etc. This will need to be mapped to the salary level for the salary office. Also, if there is some change in the conceptual view, the external view can stay the same if the mapping is changed.

Answer 2

there are three levels of architecture in dbms

1. external level

2.conceptual level and,

3.internal level

External Level is described by a schema i.e. it consists of definition of logical records and relationship in the external view. It also contains the method of deriving the objects in the external view from the objects in the conceptual view.

Conceptual Level represents the entire database. Conceptual schema describes the records and relationship included in the Conceptual view. It also contains the method of deriving the objects in the conceptual view from the objects in the internal view.

Internal level indicates hoe the data will be stored and described the data structures and access method to be used by the database. It contains the definition of stored record and method of representing the data fields and access aid used.

Answer 3

Three Level Database Architecture

Data and Related StructuresData are actually stored as bits, or numbers and strings, but it is difficult to work with data at this level.

It is necessary to view data at different levels of abstraction.

Schema:

• Description of data at some level. Each level has its own schema.

We will be concerned with three forms of schemas:

• physical,
• conceptual, and
• external.

Physical Data LevelThe physical schema describes details of how data is stored: files, indices, etc. on the random access disk system. It also typically describes the record layout of files and type of files (hash, b-tree, flat).

Early applications worked at this level - explicitly dealt with details. E.g., minimizing physical distances between related data and organizing the data structures within the file (blocked records, linked lists of blocks, etc.)

Problem:

• Routines are hardcoded to deal with physical representation.
• Changes to data structures are difficult to make.
• Application code becomes complex since it must deal with details.
• Rapid implementation of new features very difficult.

Conceptual Data LevelHides details of the physical level.

• In the relational model, the conceptual schema presents data as a set of tables.

The DBMS maps data access between the conceptual to physical schemas automatically.

• Physical schema can be changed without changing application:
• DBMS must change mapping from conceptual to physical.
• Referred to as physical data independence.

External Data LevelIn the relational model, the external schema also presents data as a set of relations. An external schema specifies a view of the data in terms of the conceptual level. It is tailored to the needs of a particular category of users. Portions of stored data should not be seen by some users and begins to implement a level of security and simplifies the view for these users

Examples:

• Students should not see faculty salaries.
• Faculty should not see billing or payment data.

Information that can be derived from stored data might be viewed as if it were stored.

• GPA not stored, calculated when needed.

Applications are written in terms of an external schema. The external view is computed when accessed. It is not stored. Different external schemas can be provided to different categories of users. Translation from external level to conceptual level is done automatically by DBMS at run time. The conceptual schema can be changed without changing application:

• Mapping from external to conceptual must be changed.
• Referred to as conceptual data independence.

Data ModelSchema: description of data at some level (e.g., tables, attributes, constraints, domains)

Model: tools and languages for describing:

• Conceptual/logical and external schema described by the data definition language (DDL)
• Integrity constraints, domains described by DDL
• Operations on data described by the data manipulation language (DML)
• Directives that influence the physical schema (affects performance, not semantics) are described by the storage definition language (SDL)

Data IndependenceLogical data independence

• Immunity of external models to changes in the logical model
• Occurs at user interface level

Physical data independence

• Immunity of logical model to changes in internal model
• Occurs at logical interface level

Entity-Relationship ModelA semantic model, captures meanings E-R modeling is a conceptual level modelProposed by P.P. Chen in 1970s

• Entities are real-world objects about which we collect data
• Attributes describe the entities
• Relationships are associations among entities
• Entity set - set of entities of the same type
• Relationship set - set of relationships of same type

Relationships sets may have descriptive attributes

Represented by E-R diagrams

Relational ModelRecord- and table-based model Relational database modeling is a logical-level modelProposed by E.F. Codd

• Based on mathematical relations
• Uses relations, represented as tables
• Columns of tables represent attributes
• Tables represent relationships as well as entities

Successor to earlier record-based models-network and hierarchical

Object-oriented ModelUses the E-R modeling as a basis but extended to include encapsulation, inheritance

Objects have both state and behavior

• State is defined by attributes
• Behavior is defined by methods (functions or procedures)

Designer defines classes with attributes, methods, and relationships

Class constructor method creates object instances

• Each object has a unique object ID
• Classes related by class hierarchies
• Database objects have persistence

Both conceptual-level and logical-level modelObject-relational modelAdds new complex datatypes to relational model

Adds objects with attributes and methods

Adds inheritance

SQL extended to handle objects in SQL:1999

Semi-structured ModelCollection of nodes, each with data, and with different schemas

Each node contains a description of its own contents

Can be used for integrating existing databases

XML tags added to documents to describe structure

XML tags identify elements, sub-elements, attributes in documents

XML DTD (Document Type Definition) or XML Schema used to define structure

(Discussed later in the course in greater detail)