DBMS Software

1
DBMS Software

• Week 7
• Conceptual Architecture - Ch 4
• Software Architecture - Ch 4
• Toolkit - Ch 11,12,13
• Week 8
• Kernel Ch 14, 15, 16, 17

2
ANSI/SPARC Architecture

• Problem of Data independence
• In classical systems
• Application programs know about how and where
  data is stored
• Problems when
• additional data stored but not of relevance to
  prog
• data reorganised for efficiency but no change in
  content

3
ANSI/SPARC (2)

• Two distinctions
• between the global logical data model and the
  way the data is stored in physical data storage
  on disk
• isolates issues of efficient storage such as
  indexes, replication of data for fast access and
  backup
• between local application programs and the
  global data model
• isolates functional areas from concern for the
  full conceptual model - remember ISIS has 250
  tables

4
ANSI/SPARC (3)

• External view - defines VIEWS which bring
  together a subset of the full model for a
  specific functional area - e.g. enrolment
• Conceptual model - full logical model of the
  organisation
• Physical - storage of data in underlying files
  using the appropriate file structures, and
  allocated to storage units

5
ANSI/SPARC example

• Lecturer view of ISIS
• those students which she teaches (restriction)
• relevant data items (projection)
• denormalised (eg. name and award name by module)
• Full model is 250 tables
• Physical data storage
• duplicates key files for fast retrieval
• builds indexes based on common queries

6
DBMS Software Components

• User programs
• user developed programs (eg. as VB Macros, PLSQL
  procedures, PHP scripts,stored Queries)
• DBMS Toolkit
• tools to help user build systems and user
  programs
• Interface
• language and protocol for communication between
  User programs and Kernel
• Kernel
• core Database functions - data storage and
  retrieval
• Operating System - file storage

7
Interface

• User programs need to communicate with the Kernel
  to
• update the Schema (Data Definition Language)
• update the Factbase (Data Manipulation Language)
• Interface must handle
• sending requests to the Kernel e.g. with SQL
• receiving results from the Kernel
• linking to Kernel, handling errors (e.g
  ODBC,JDBC)
• Receiving results is tricky
• send whole relation in some serialized format
• send pointer to temporary table and get each row

8
Interface (2)

• Scripting languages such as PL/SQL (Oracles
  application language) and PHP (a Web server
  language) send SQL and provide routines for
  access to the results e.g.
• the notion of a CURSOR a pointer to the next
  row of a table to be returned
• Access can automate this connection so that a
  table located on a remote server appears to be
  a local table.

9
Kernel functions

• CRUD - Create, Read, Update, Destroy
• at Factbase and Schema level
• Data Dictionary - Schema storage
• Transaction Management - run-unit completion
• Concurrency Control - multiple users
• Recovery - backup and restore
• Authorisation - users, passwords, areas
• Data Communications ( or by OS)
• Data integrity - foreign keys etc.
• Import/Export, Monitoring

10
Kernel (2)

• Schema
• table definitions
• integrity constraints
• views (stored queries)
• access control
• Stored in Data Dictionary
• DD is a Database itself, the System Catalog
• Manipulated with DDL and DCL

• Factbase
• tables
• Manipulated with DML
• Interface with operating system for file access
• Transaction control
• concurrency
• locking

11
Toolkit - Application Development Tools

• E.g. Access
• Graphical User Interface (GUI)
• QBE to generate SQL queries
• Form and report definitions
• VB Macros for user programs
• Natural Language Interface
• translation from natural language to SQL

12
User programs

• SQL command line interface
• SQLPlus for Oracle, similar for MySQL
• Pre-compiler (COBOL..)
• Program contains SQL statements which need to be
  compiled into calls to Kernel functions
• Scripts (PHP,Perl)
• Generation of SQL calls at runtime

13
CASE tools

• Select SSADM, Rational Rose, Oracle Designer 2000
• allow developers to develop schema from ER model

14
Admin tools

• Database software control
• starting and stopping Kernel
• backup and restore
• Reconstruct indexes
• Access control
• create users, user groups,
• assign user access rights to tables
• Import and Export of data and schema
• Schema migration - major schema change
• monitoring and tuning

15
Tutorial

• Normalisation exercise 6 from PDB 23
• Using a case tool

16
Next week

• Kernel details - Part 4 139-171
• esp Transaction control Ch 16