UNIT 3: INSTANCES AND SCHEMES

1. Concepts of Instances and Schemes

The information in a database at a particular point in time is called an instance of the database. While the overall design of the database is called the database scheme.

 Link between Instances and Schemes

In order to grasp the key aspects of Instances and Schemes we would identify the link between these two concepts. Instances and Schemes are two terms closely associated with the mode of change database over time.

 Analogy with Programming Languages

 Instances and Schemes correlate with programming languages as follows:

•          Data type definition - scheme

•          Value of a variable - instance

 Categories of Schemes

There are several classes of schemes, corresponding to the levels of abstraction: 

•          Physical scheme

•          Conceptual scheme

•          Sub scheme (can be many)

2. Other Concept Associated with Instances and Schemes

1. Data Independence

 Data independence refers to the ability to modify a scheme definition in one level without affecting a scheme definition in a higher level.

 Classes of Data Independence 

There are two categories of data independence:

Physical data independence 

•              The ability to modify the physical scheme without causing

application programs to be rewritten

•              Modifications at this level are usually to improve performance

Logical data independence

•              The ability to modify the conceptual scheme without causing application programs to be rewritten

•              Usually done when logical structure of database is altered

•              Logical data independence is harder to achieve as the application programs are to a large extent heavily dependent on the logical structure of the data.

 2. Data Definition Language (DDL)

The data definition language (DDL) is a language used to specify a database scheme as a set of definitions expressed in a DDL. DDL statements are compiled, resulting in a set of tables stored in a special file called a data dictionary or data directory. This directory contains metadata (data about data). The storage structure and access methods used by the database system are specified by a set of definitions in a special type of DDL called a data storage and definition language. Basically, DDL statements enable developers hide the implementation details of the database schemes from the users. 

3. Data Manipulation Language (DML)

Data Manipulation could refer to any of the following: 

•              retrieval of information from the database

•              insertion of new information into the database

•              deletion of information in the database

•              modification of information in the database

A Data Manipulation Language (DML) is a language which enables users to access and manipulate data. The main goal of the DML is to provide efficient human interaction with the system.

Types of Data Manipulation Language (DML)

There are two types of DML: 

•              procedural: the user specifies what data is needed and how to get

it

•              nonprocedural: the user only specifies what data is needed

Easier for user

•              May not generate code as efficient as that produced by procedural languages.

•              The terms DML and query language are often used synonymously. This is due to the fact that a query language is a portion of a DML involving information retrieval only.

3. Database Administrator

 The term Database Administrator simply refers to a person having central control over data and programs accessing that data.

Duties of the Database Administrator

The duties of the database administrator include: 

Scheme definition: the creation of the original database scheme. This involves writing a set of definitions in a DDL (data storage and definition language), compiled by the DDL compiler into a set of tables stored in the data dictionary.

Storage structure and access method definition: writing a set of definitions translated by the data storage and definition language compiler

Scheme and physical organisation modification: writing a set of definitions used by the DDL compiler to generate modifications to appropriate internal system tables (e.g. data dictionary). This is done rarely, but sometimes the database scheme or physical organisation must be modified.

Granting of authorisation for data access: granting different types of authorisation for data access to various user

Integrity constraint specification: generating integrity constraints. These are consulted by the database manager module whenever updates occur.

4. Database Users

The database users fall into several categories:

1. Application Programmers

Application programmers are computer professionals interacting with the system through DML calls embedded in a program written in a host language (e.g. C, PL/1, Pascal). These programs are called application programs. 

The DML precompiler converts DML calls (prefaced by a special character like $, #, etc.) to normal procedure calls in a host language. The host language compiler then generates the object code. Some special types of programming languages combine Pascal-like control structures with control structures for the manipulation of a database. These are sometimes called fourth-generation languages. They often include features to help generate forms and display data.

 2. Sophisticated Users

 Sophisticated users interact with the system without writing programs. They form requests by writing queries in a database query language. These are submitted to a query processor that breaks a DML statement down into instructions for the database manager module.

3. Specialised Users

 Specialised users are sophisticated users writing special database application programs. These may be CADD systems, knowledge-based and expert systems, complex data systems (audio/video), etc.

4. Naive Users

Naive users are unsophisticated users who interact with the system by using permanent application programs (e.g. automated teller machine).