Requests by a user process

The DBMS function used by all user processes is that of data manipulation. The action of retrieving and updating data is a primary purpose for a database. A user process has one purpose, the data retrieved/acted on is restricted to the user task. Users are not aware of DBMS processes/requests. DBMS use a data manipulation language for requests. DML statements are included in the user process by the developer using a standard programming language. (DML statements are therefore embedded)

A database tool can also interact with the DBMS. These tools are general purpose to many databases and as such have their own methods of requests. One method is by forms, included in the specification of the tool's definition. The form contains the specification of the data for manipulation and this is passed by the software to the DBMS. Users are unaware of the DBMS requests.

A second method is by direct user input via a database tool of the specification of the required data manipulation. This is achieved using and executing a statement expressed in a query language. (SQL, a widely adopted International Standard)

Executing statements

SQL states the data manipulation required but the DBMS controls how it is done. Although SQL is an international standard, it is unable to communicate directly with all DBMS. This is because there are many software manufacturers implementing their own DBMS methods and formats etc. To enable any DBMS to understand SQL statements it is necessary to bolt on an ODBC (Open Database Connectivity aka SQL/CLI) interface via an ODBC driver.

Data definition

The specification of how data is defined is expressed through data definition language (DDL) in conjunction with SQL. In defining a database - there are two representations:

• provide the initial specification either direct from the user or from a database tool. The properties for a new table are entered and used to generate an SQL DDL statement. This can be direct or from a form.
• a schema, resulting from the processing of the SQL DDL statements stored for use by DBMS in tables. (schema aka system tables)

SQL DDL statements can be used to modify existing schemas. These are processed by the DBMS and the changes to the data definitions are stored in a schema. Schemas also include properties about constraint and access control.

Three-schema architecture

General architecture applicable to any DBMS - not just relational.

Logical schema - the central component, defines logical properties of the database. (determines what data is available) Concerned with representation of data and constraints that are independent of how it is stored in or accessed from files. A database has only one logical schema used by DBMS to manage all data. In a relational database, SQL DDL can be used to define logical properties i.e. tables are a logical representation of data.

Storage schema - defines how a database is stored in files and accessed. (determines how data is processed in files) The ability to store is determined by a mapping connection to the logical schema. No mapping, no storage. A database has only one storage schema.

External schema - defines data from a database for a user process interacting with the DBMS.(concerned with data appearance outside of the database) External schemas must be mapped to the logical schema. Logical schema defines the data for the external schema. The number of external schemas correspond to the number of user processes and are associated with the DBMS. Logical and storage schemas are linked to the DBMS only.

Logical data independence

Exists when a change to a logical schema does not impact on user process due to each user process being dpendent on an external schema. A change to a logical schema does not affect the data defined by an external schema if it does not include changed data or if the change can be taken care of by changed mapping.

Logical data independence is important in allowing for extending the range of data. It is not always possible to logical data independence.

Physical data independence

Exists when a change to a storage schema does not impact on user process. Because the change does not affect a logical schema, it can be taken into account by mapping. Allows for increases in the amount of data being stored and improvements in processing efficiency. File characteristics and indexes on columns can be changed with no effect on user process operations.

A computer OS stores data for a DBMS using files. Data is transferred to and fro in blocks (aka pages) of a fixed size. Organised to allow for retrieval and storage as required. DBMS interacts with OS in terms of blocks.

In a relational database rows are stored in blocks for storage in a file. These rows are accompanied in a storage record by row identifiers and pointers to other storage records containing related data. there may also be indexes for more efficient searching.

Comments, suggestions, ideas to
Stuart Banner