MSc IT UFCE 8 K15 M Data Management

MSc IT UFCE 8 K-15 -M Data Management Lecture 9 : DBMS Architecture

ANSI/SPARC Architecture o Problem of Data independence o In ‘classical systems’ o Application programs ‘know’ about how and where data is stored o Problems when o additional data stored but not of relevance to prog o data reorganised for efficiency but no change in content UFCE 8 K-15 -M Data Management 2014/15 2

ANSI/SPARC (2) o Two distinctions o between the global ‘logical’ data model and the way the data is stored in physical data storage on disk o isolates issues of efficient storage such as indexes, replication of data for fast access and backup o between local application programs and the global data model o isolates functional areas from concern for the full conceptual model - remember ISIS has 250 tables UFCE 8 K-15 -M Data Management 2014/15 3

ANSI/SPARC (3) o External view - defines VIEWS which bring together a subset of the full model for a specific functional area - e. g. enrolment o Conceptual model - full ‘logical’ model of the organisation o Physical - storage of data in underlying files using the appropriate file structures, and allocated to storage units UFCE 8 K-15 -M Data Management 2014/15 4

ANSI/SPARC example o Lecturer view of ISIS o those students which she teaches (restriction) o relevant data items (projection) o denormalised (eg. name and award name by module) o Full model is 250 tables o Physical data storage o duplicates key files for fast retrieval o builds indexes based on common queries UFCE 8 K-15 -M Data Management 2014/15 5

DBMS Software Components o User programs o user developed programs (eg. as VB Macros, PLSQL procedures, PHP scripts, stored Queries) o DBMS Toolkit o tools to help user build systems and user programs o Interface o language and protocol for communication between User programs and Kernel o core Database functions - data storage and retrieval o Operating System - file storage UFCE 8 K-15 -M Data Management 2014/15 6

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

Interface (2) o Scripting languages such as PL/SQL (Oracle’s application language) and PHP (a Web server language) send SQL and provide routines for access to the results e. g. o the notion of a ‘CURSOR’ a pointer to the next row of a table to be returned o Access can automate this connection so that a table located on a remote server ‘appears’ to be a local table. UFCE 8 K-15 -M Data Management 2014/15 8

Kernel functions o CRUD - Create, Read, Update, Destroy o at Factbase and Schema level o o o o 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 UFCE 8 K-15 -M Data Management 2014/15 9

Kernel (2) o Factbase o Schema o o table definitions integrity constraints views (stored queries) access control o Stored in Data Dictionary o DD is a Database itself, the System Catalog o tables o Manipulated with DML o Interface with operating system for file access o Transaction control o concurrency o locking o Manipulated with DDL and DCL UFCE 8 K-15 -M Data Management 2014/15 10

Toolkit - Application Development Tools o e. g. Access o o Graphical User Interface (GUI) QBE to generate SQL queries Form and report definitions VB Macros for user programs o Natural Language Interface o translation from natural language to SQL UFCE 8 K-15 -M Data Management 2014/15 11

User programs o SQL command line interface o SQLPlus for Oracle, similar for My. SQL o Pre-compiler (COBOL. . ) o Program contains SQL statements which need to be compiled into calls to Kernel functions o Scripts (PHP, Perl) o Generation of SQL calls at runtime UFCE 8 K-15 -M Data Management 2014/15 12

CASE tools o e. g. Select SSADM, Rational Rose, Oracle Designer, QSEE, My. SQL Workbench, o allow developers to develop schema from ER model o Allows for the generation of SQL DDL for target DB o speeds up development process o reduces costs, especially in maintenance UFCE 8 K-15 -M Data Management 2014/15 13

Admin tools o Database software control o starting and stopping Kernel o backup and restore o Reconstruct indexes o Access control o create users, user groups, o assign user access rights to tables o Import and Export of data and schema o Schema migration - major schema change o monitoring and tuning UFCE 8 K-15 -M Data Management 2014/15 14

Kernel operations o Underlying file types o Indexing o Transactions o Locks o Rollback o Query optimisation UFCE 8 K-15 -M Data Management 2014/15 15

Physical organisation o Secondary storage (on disk) divided into blocks - say 16 K in size o For a given file system, blocks have control info(index to records in block. . ) and free space as well as user records o structured blocks are called ‘pages’ o Data is transferred between main memory and secondary storage in pages o Pages can be held in memory (cached) for speed of access UFCE 8 K-15 -M Data Management 2014/15 16

Typical components modules of a DBMS. Dotted lines show accesses that are under the control of the stored data manager UFIE 8 K-15 -M Data Management 2010/11 17