AS/A2 Computing: Database Management System (DBMS)

There are two models now used for database design (others have now been abandoned), these are:

Terms related to databases:

Relation: a table in a database where details of entities are stored.
Entity: a collection of objects that share the same attributes.
Attribute: part of a row (tuple).
Field: a group of characters that represent a single item of data in a file.
Record: a self contained part of a file consisting of one or more fields.
File: a set of related records stored together.
Relationship: a link between two entities.
Primary key: a field in a record that uniquely identifies every record in a relation (table).
Secondary key: a unique field not the primary key for a relation (table).
Foreign key: a reference held in one table to a primary key in another table.
Index: a reference used to locate records by a given field quickly. All primary keys are automatically indexed.
Referential integrity: form of validation routine that enforces rules about referring to records - they must exist.

Separate file approach

The programs are now sharing data from a single file so data is consistent.
However, problems still exist if a change is made to one program to the size, type or format in which data is stored the other programs will need re-writing as the programs communicate directly with the files. This is called program-data dependence as the applications are dependent on the physical structure of the files.
This causes "unproductive maintenance" as to change one application requires all applications to be changed.

The DBMS approach provides an interface (layer of software) between physical data and the applications.
Programs no longer refer to file names or structures.
This means the problems of redundancy, inconsistency and program-data dependence are solved.
The DBMS also:
- facilitates sharing of data between software.
- manages security and access rights.
- manages mapping between logical and storage schemas.
- maintains data dictionary (a description of the logical schema).
- manages sharing of data.

The DBMS has a distinct structure:

Raw data: stored as a database.
Storage schema: how data is stored and accessed for a given DBMS. Here is the view of software writers such as those at Microsoft who write Microsoft Access.
Logical schema: types and length of fields stored in a logical way (the entire database is available here). The logical schema answers the question "what is data and how is it stored?" This is a data dictionary and where the designer of software sets access rights for different users.
Schema: The view of data for a particular user/application, see part of the database (the bits it needs).
Applications: the user or applications that view, manipulate or process the data.

These notes are from a lesson on 22/06/2004.