ANSI/SPARC Architecture for DBMS: Solving the Problem of Data Independence

1. MSc IT UFCE8K-15-MData Management Lecture 9 : DBMS Architecture

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

8. Interface (2) • 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. • 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. UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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 UFCE8K-15-M Data Management 2014/15

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

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 UFCE8K-15-M Data Management 2014/15

15. Kernel operations • Underlying file types • Indexing • Transactions • Locks • Rollback • Query optimisation UFCE8K-15-M Data Management 2014/15

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

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