Desirable features in an information system

1
Lecture 3
2
Desirable features in an information system

• Integrity
• Referential integrity
• Data independence
• Controlled redundancy
• Security
• Privacy

3
File systems

• Sequential or serial
• Indexed sequential
• Relative

4
Database definition

• a computerised record-keeping system
• used by a range of users who have different
  requirements
• minimal enquiries
• in-depth updating
• restructuring
• A well-implemented database will have data
  integrity, data independence, controlled
  redundancy, security and privacy, for all users.

5
Uses of a Database

• Generally used for on-line transaction processing
  (OLTP)
• Data Warehouses are a hybrid of databases which
  are used for On-line analytical processing (OLAP)

6
Structure of a database
External Schema
Conceptual Schema
Internal Schema
Physical Schema
7
External level

• Level visible to user
• Multiple views of the system
• e.g. View an order - see limited product and
  customer information
• Only the database Administrator may access the
  whole database at this level

8
EXTERNAL SCHEMA

• Each external view is defined by means of an
  external schema
• Provides definitions of each external view.
• Written in a Data Definition Language
• individual to the user
• accessed through a 3GL, a query language or a
  special purpose forms or menu-based language

9
Conceptual level

• CONCEPTUAL - represents the entire information
  content of the database
• Consists of multiple types of conceptual record.
  This level preserves the data independence of the
  database.
• CONCEPTUAL SCHEMA - defines each of the various
  types of conceptual record, in a conceptual Data
  Definition Language.

10
Internal level

• INTERNAL - a low-level representation of the
  entire database it consists of multiple
  occurrences of multiple types of internal record.
  It is the stored record, inasmuch as it contains
  all but the device-specific information on the
  storage of the database.
• PHYSICAL - the physical device and block
  addresses for each of the records.

11
Mappings

• Each level maps onto adjoining levels
• conceptual / internal mapping specifies how
  conceptual records and fields are represented at
  the internal level
• Changes can be made in the internal level
  without affecting the conceptual level
• external / conceptual mapping defines the
  correspondence between an external view and the
  conceptual view

12
DBMS - Database Management System

• software handling access to the database
• allows both the database administrator and all
  users the access to the database to which they
  are entitled

13
How requests are processed

• User issues request (e.g. through SQL)
• DBMS intercepts and analyses request
• DBMS inspects user's external schema, external to
  conceptual mapping, conceptual schema,
  conceptual to internal mapping and the storage
  structure definition.
• DBMS executes operations on stored database.

14
DATABASE ADMINISTRATOR (DBA)

• Decide on the storage structure and access
  strategy
• Liaise with the users
• Define security and integrity checks
• Define a backup and recovery strategy
• Monitor and respond to performance

15
Utilities used by the DBA

• Load routines
• Dump/Restore routines
• Reorganisation routines
• Statistics routines
• Analysis routines
• Data dictionary (containing METADATA, which gives
  data descriptions and mappings)

16
Relational database

• Data is independent from programs and from other
  data
• Data is represented in TABLES rather than files.
  (one entity corresponds to 1 table)
• Column headings are described as DOMAINS. (i.e.
  attributes)
• Items of information as TUPLES or ROWS rather
  than records (i.e. occurrences of the entity)