Three Level Database Architecture

Data and Related Structures

Data 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 Level

The 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 Level

Also referred to as the Logical level

Hides 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 Level

In 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 Model

Schema: 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 Independence

Logical 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 Model

A semantic model, captures meanings

E-R modeling is a conceptual level model

Proposed 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 Model

Record- and table-based model

Relational database modeling is a logical-level model

Proposed 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 Model

Uses 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 model

Object-relational model

Adds new complex datatypes to relational model

Adds objects with attributes and methods

Adds inheritance

SQL extended to handle objects in SQL:1999

Semi-structured Model

Collection 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)