Introduction: Database Technologies 3Rd Year Subject

Front 1

Limitations of File-Based Approach

Back 1

Separation and isolation of data
Duplication of data
Program - Data dependence
Incompatible file formats
Fixed Queries/Proliferation of application programs
Others omissions

Front 2

Database Approach Arose because:

Back 2

Definition of data was embedded in application programs, rather than being stored separately and independently.

No control over access and manipulation of data beyond that imposed by application programs.

Front 3

Result of Previous

Back 3

the Database and Database Management System (DBMS).

Front 4

Basic Definitions

Back 4

Database:
A logically coherent collection of data with some inherent meaning in a domain of discourse used for a specific purpose. It has an intended set of users

Domain of Discourse (or Universe of Discourse or Mini-world):
Some part of the real world about which data is stored in a database. For example, student grades and transcripts at a university.

Database Management System (DBMS):
A software package/ system to facilitate the creation and maintenance an control access to the database

Database System:
The DBMS software together with the database itself. Sometimes, the applications are also included.

Front 5

Types of DBMSs that follow this model

Back 5

Early Database DBMSs:
The Hierarchical and Network Models were introduced in mid 1960s and dominated during the seventies.
A bulk of the worldwide database processing still occurs using these models, particularly, the

hierarchical model.
Relational Model based Systems:
Relational model was originally introduced in 1970, was heavily researched and experimented within IBM Research and several universities.
Relational DBMS Products emerged in the early 1980s.

Object DBMSs
Data is defined in objects organised to relate to objects within an object-oriented program

Front 6

A Generic Database System Model

Back 6

Front 7

Typical DBMS Functionality

Back 7

Data Definition (DDL) a particular database in terms of its structures and data types
Constraint Definition and enforcement

Access Control – defining user’s rights with regard to database objects and enforcement

Data Manipulation (DML) the database:
Retrieval: Read, generating reports
Modification: creations, deletions and updates to its content
SQL statement processing is also a key function of a DBMS

Restructuring and reorganisation - e.g. changing the definition of a table by adding a new column. Partitioning table is another example.

Front 8

Typical DBMS Functionality

Back 8

Other features:
Transaction Support – defining a transaction and ensuring that its properties are maintained

Concurrency Support permitting many user processes to access a database at the same time an ensuring they do not interfere with each other

Backup and Recovery to ensure integrity of database after a failure

Front 9

DBMS manages SQL Statement Processing

Back 9

Parse (Call)
Essentially the same as any programming language statement

Accepts a statement as a string of characters and checks that it is valid SQL
Involves examining each term in statement to ensure it is a SQL keyword or the name of a table or column or a literal
Also involves planning i.e. producing an execution plan that specifies how the statement should be executed.

Execute
The parsed SQL statement can now be executed against the database.

Fetch
The rows to be returned are fetched

Front 10

Oracle Parse Call –In more Detail

Back 10

SQL Statement is translated to verify it a valid statement
Many errors can be caught during this stage of statement processing.

The data is validated .
Data Dictionary Lookups to ensure tables columns exist

Locks are allocated.
Parse Locks must be acquired to make sure that object definitions don’t change during execution or the parsing i.e. Locks on the definitions in the Data Dictionary tables

Privileges are verified to ensure user has access to the database tables and any other database objects

Optimal Execution Plan is determined based on optimisation plans, hints and database analysis

Statement is then loaded into the shared SQL area ( Shared Pool) in memory
WE WILL TALK MORE ABOUT THIS LATER IN THE COURSE

Front 11

Three Schema Architecture

Back 11

Draw it man

Front 12

Three Schema Architecture (contd)

Back 12

Provides
Logical Data independence
When a change to a logical schema has no impact on a user process
Not always guaranteed

Physical Data Independence
Change in the storage schema does not impact the logical schema

Front 13

Main Characteristics of the Database Approach

Back 13

Self-describing nature of a database system:
A DBMS catalog ( aka Data Dictionary) stores the description of a particular database (e.g. data structures, types, and constraints)
This allows the DBMS software to work with different database applications.
It also aids the Database Administrator (DBA) in the maintenance of the Database.

Insulation between programs and data:
Called program-data independence. ( or logical data independence)
Allows changing data structures and storage organization without having to change the DBMS access programs in most cases!

Front 14

Main Characteristics of the Database Approach (continued)

Back 14

Data Abstraction:
A data model is used to hide
storage details and present the users with a conceptual view of the database.
Programs refer to the data model constructs rather than data storage details
We think of Tables in a database (tables are logical not physical but are stored in database files made up of o/s blocks)

Support of multiple views of the data:
Each user may see a different view of the database, which describes only the data of interest to that user.

Front 15

Main Characteristics of the Database Approach (continued)

Back 15

Sharing of data and multi-user transaction processing:
Allowing a set of concurrent users to retrieve from and to update the database.
OLTP (Online Transaction Processing) is a major part of database applications. This allows thousands of concurrent transactions to execute per second.