Database Systems Kernel

1. Database SystemsKernel Data Organisation Database Recovery Access Mechanisms DBMS Kernel Database Security Transaction Management Query Management

2. Kernel, Interface and Toolkit DBMS Toolkit Application Development Toolkit DBA Toolkit Archiving, Backup & Restore Tool Application Programming Interface DBMS Interface (SQL) Authorisation Tool Database Design Tool DBMS Kernel Form Generator Performance Monitoring Tool Report Generator Import & Export Tool End-User Toolkit Natural Language Interface Visual Query Interface QBE Interface

3. Kernel Elements DBMS Kernel Data Organisation Access Mechanisms Transaction Management Other Kernel Functions

4. DataOrganisation Field Byte Ordered Record Bit Sequential Hashed Page Physical Organisation File Clustered Extent Logical Organisation Segment Table Tablespace

5. Storage Elements Tera-Byte Giga-Byte Mega-Byte Kilo-Byte Byte Bit

6. Physical Organisation File Record Field Byte Bit

7. Pages and Files Page Page File Page

8. DBMS, File manager and Disk Manager DBMS File Requests File Manager Logical Block Requests Disk Manager Physical Block Requests Disk

9. Physical Organisation Physical Organisation Sequential Files Ordered Files Hashed Files Clustering

10. Oracle Logical Data Structures TableSpace Table Segment Extent

11. Oracle Physical and Logical structures File 3 Physical Structure File 1 File 2 File 4 TableSpace A TableSpace B Logical Structure

12. Summary - Data Organisation • Data organisation concerns the way data is organised of physical storage devices • Data models are forms of logical data organisation • Database systems are heavily input/output intensive. Physical organisation can affect the efficiency of I/O activity • Data is organised physically in terms of files, records, fields and blocks/pages • The main types of file organisation are sequential files, ordered files, and hashed files • File organisation and access are intrinsically inter-linked • Clustering is an approach available in many contemporary DBMS for physically inter-leaving data on disk

13. AccessMechanisms B+ Tree Index Unique Drop Balanced Tree Create Index B Tree Indexes Trees Extent Indexes Multi-Level Indexes Indexed-Sequential Files Simple

14. A Simple Index Index File Page 1 956222 Student Record: 956222 . 956223 Student Record: 956223 . 956224 . Page 2 Student Record: 956224 956225 . Student Record: 956225 956226 . Page 3 956227 Student Record: 956226 . 956228 Student Record: 956227 . 956229 . Page 4 Student Record: 956228 Student Record: 956229

15. A Multi-Level Index Level 2 Index Level1 Index File 956222 . Page 1 Student Record: 956222 956223 . 956223 Student Record: 956223 . 956225 956224 . . Page 2 Student Record: 956224 956225 . Student Record: 956225 956226 . Page 3 956227 Student Record: 956226 . 956227 . 956229 Student Record: 956227 . 956228 . Page 4 956229 Student Record: 956228 . Student Record: 956229

16. An Example Tree Structure . . . . . . .

17. A B+-Tree Index 956225 . . 956223 956227 956229 . . . . 956221 956223 956224 956225 956226 956227 956228 956229 . . . . . . . . . . .

18. An Updated B+-Tree Index 956225 . . 956222 956225 956227 956229 . . . . . 956221 956222 956225 956226 956227 956228 956229 . . . . . . . . . . . 956223 956224 . . .

19. SQL - Create Index Statement Create Index <index name> on <table name> (<column name(s)>) Drop Index <index name>

20. Summary - Access Mechanisms • Access mechanisms are added to databases to improve retrieval performance • The most commonplace form of access mechanism is the index • The most popular form of index in contemporary DBMS in the B+ tree index • SQL has a CREATE INDEX and DROP INDEX command

21. TransactionManagement Inconsistent Analysis Problem Deadlock Lost Update Problem Uncommitted Dependency Problem Two-Phase Locking Transaction Manager Locking Concurrency Concurrency Control Transaction Log Rollback SQL Checkpointing Commit Recovery Durability Transactions Isolation Consistency Atomicity

22. Transactions ICT System Interface Management Subsystem Rules Management Subsystem Transaction Management Subsystem Data Management Subsystem Database DBMS Database

23. Properties of a Transaction Transaction Properties Atomicity Consistency Isolation Durability

24. Transactions in SQL-Based Systems INSERT INTO Registration (studentNo, moduleName) VALUES (34698,’Relational Database Systems’) UPDATE Modules SET roll = roll + 1 COMMIT

25. Concurrency • Lost Update Problem • Uncommitted Dependency Problem • Inconsistent Analysis Problem

26. Lost Update Problem

27. Uncommitted Dependency Problem

28. Inconsistent Analysis Problem

29. Concurrency Control • Locking • Two-Phase Locking

30. Lost Update Problem

31. Uncommitted Dependency Problem

32. Inconsistent Analysis Problem

33. Summary - Transaction Management • Transaction management involves ensuring concurrent access to a database, and ensuring the consistency of this database in a multi-user environment. • In a multi-user database system the procedures that cause changes to a database or retrieve data from the database are called transactions. • Any transaction should demonstrate the properties of atomicity, consistency, isolation and durability. • In SQL the statements COMMIT and ROLLBACK are used to delineate transactions. • An immediate consequence of the data-sharing property of database systems is that mechanisms must be provided for handling shared or concurrent access. A number of problems are introduced by concurrency including the lost update problem, the uncommitted dependency problem, and the inconsistent analysis problem. • Concurrency control aims for the objective of so-called serialisability. • Locking is a pessimistic approach to ensuring serialisability. • The transaction manager is a module within the kernel of a DBMS that handles the throughput of transactions against a database. One of the major functions of the transaction manager is to record the execution of transactions. The place where these records are stored is normally referred to as the transaction log. • Recovery is the process of ensuring that a database can achieve a consistent state in the event of failure. The basic unit of recovery in a database system is the transaction. • Checkpointing is a technique used to increase the efficiency of recovery. It involves force-writing database buffers to secondary storage at pre-determined intervals.

34. OtherKernelFunctions Syntax-Based Optimisation Statistically-Based Optimisation Compiled Interpreted Optimisation Optimisation Execution Queries Authorisation Query Management Validation Backup & Recovery Parsing Data Dictionary Management Meta-data

35. Information in a Data Dictionary Data Dictionary Relation Structure Inherent Integrity Constraints View Definitions User Groups Physical Organisation Procedures & Triggers

36. Structure of a Data Dictionary User View Index Table Column

37. Processing a Query User Interface SQL Query Parsing Stage: checking syntax Results/ Error Messages Validating tables and columns Checking authorisation Query Optimisation Execution Plan Data Dictionary Query execution

38. Query Optimisers Query Optimisers Syntax Based Statistically Based

39. Summary - Other Kernel Functions • The data dictionary is at the heart of a DBMS. Being a database it has a structure and stores the meta-data relevant to some database. • Query management involves the syntactic checking of a query, query optimisation and query execution. • Query optimisation is the process of determining the optimally most efficient execution plan of a query. • DBMS either compile queries or interpret them at run-time. • Backup and recovery facilities are important tools for database administration.