DBMS (Database Management Systems) (lecture 6)

Database - a shared collection of logically related data

DBMS - software system to define, create, maintain and control access to one or more databases

Application Program - program that interacts with the database by requests to the DBMS (Oracle)

Data mining – process of discovering new patterns and relationships and data by using statistical and AI methods.

Data warehouses – integrated data collection choose to support decision-making, largely static but holding historic data.

Metadata - data about data

DBMS deal with the management of large amounts of persistent, reliable and shared data

large - all data cannot fit into memory

persistent - data persists between sessions

reliable - data is recoverable despite memory or system failures

shared - multiple users have simultaneous access

available - online all the time

security - authorisation and authentication

DBMS creation

A CREATE DATABASE statement creates:

data files - stores user metadata (table information), and data (rows and values)

control files - to store metadata specifying the physical structure of the database

Re-do log files – to store all changes to the database e.g. data that cannot yet be written to permanent storage. Can reconstruct the database

Undo data file - stores previous iterations of the database (rollback)

DBMS schemas

User schema - users part of the database (their tables... tiggers...)

Tablespace - contains all schema objects

Database - contains tablespaces

^^^ these are broken down into blocks or pages (a number of bytes)

Datafiles - tablespaces are mapped onto one or more datafiles (system, users, temp, sysaux, undotbs)

Block and Page Structure

page dictionary - stores the information on each page, i.e. where does the next table start, OR how much data does each row takeup

Row information - is the data itself

Has pointers kinda like a linked list

Vertical DBMS Structure

External Level, Conceptual Level, Internal Level, Physical Data Organisation

The DBMS is responsible for the Map above

All layers must be independent of each other, so that if you make change in one layer it wont effect the other layers

Linking the layers above:

External/Conceptual - Maps the names in the use of view to the relevant data in the conceptual level

Conceptual/Internal - Records in physical storage a logical version of the tables (conceptual data)

Physical data independence - the conceptual schema is immune from changes in the internal schema

Logical data independence - The external schema is immune from changes in the conceptual schema

DBMS subsystems (what is it made up of?)

what is needed?

Data definition language – the data model

Data manipulation language – for data storage and retrieval

Control mechanisms (baso Maintenance) - A transaction model, data consistency maintenance, recovery, backing up and archiving backups, secondary storage management, access control (how users access the data), security, distribution management in the network

Dictionary data - keeps a record of the metadata

Whats going on here:

Query Processor - Transform's queries into a set of low-level instructions for the database manager.

Database Manager - Interfaces with the use of submitted application programs and queries. Examines external conceptual scheme is to determine what conceptual records are required.
- Includes transaction manager, scheduler (for concurrent operations), query optimiser, buffer manager, recovery manager.

File Manager - Maintains physical schema: storage files, space allocation, index structures. Sent request to the operating system.

Dictionary Manager – managers and queries the metadata in the data dictionary.

DML (data definition language) processor – converts DML statements embedded in an application into a set of low-level instructions for the database manager.

DDL (data manipulation language) processor – converts DDL statements into a set of tables containing the metadata.

DBMS Types and People

Relational DBMS (RDBMS)

Tables of data, that can have user defined types

Adv:

Loads of big ones like Oracle

Stable; based on maths

Fleiable (tables added easily)

Well suited for data structured in tables

Well established in the marketplace

Dis:

No user defined types

No complex data types or inheritance

Object-Oriented DBMS (OODBMS )

Stores user-defined objects

Business Intelligence & Data Analytics

OLTP (Online Transaction Processing) Systems

Systems designed to maximise transaction processing (max out speed)

Data is dynamic but usually only current values are stored

OLAP (online analytical processing) systems

Systems which analyse data using complex, multidimensional views for assessing and forecasting data

SQL extensions for aggregating and analysis