1 / 17

Understanding Data Management in DBMS Architecture

Explore ANSI/SPARC architecture concepts, DBMS software components, kernel functions, and user programs in data management. Learn about interface communication, schema definitions, and admin tools.

rwalsh
Download Presentation

Understanding Data Management in DBMS Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MSc IT UFCE8K-15-MData ManagementPrakash ChatterjeeRoom 2Q18prakash.chatterjee@uwe.ac.ukhttp://www.cems.uwe.ac.uk/~p-chatterjee/modules/dm 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 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

  10. 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 Kernel (2) UFCE8K-15-M Data Management 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

  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 2013/14

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

  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 2013/14

  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

More Related