Database PowerPoint Presentation Lectures by Dr Patrick Ozoh Dept of ICT Osun State University

A transaction is a sequence of DML commands that forms a logical unit of work Example: transferring money from one bank account to another Transaction Management A transaction is a sequence of DML commands that forms a logical unit of work Example: transferring money from one bank account to another Some Definitions Atomicity - a transaction must execute completely or not at all Consistency - once a transaction completes successfully, the database must be in a consistent state Isolation: A transaction must not be affected by other transactions that are executing concurrently Durability: Once a transaction compketes successfully, its effect must persist even in the presence of system failures Concurrency control Concurrency control is a database management systems (DBMS) concept meant to coordinate simultaneous transactions while preserving data integrity. Control protocols ensures atomicity, isolation, and serializability of concurrent transactions Concurrency control protocols can be broadly divided into two categories − 1) Lock based protocols 2) Time stamp based protocols Lock-based Protocols Lock-based protocols use a mechanism by which any transaction cannot read or write data until it acquires an appropriate lock on it Locks are of two kinds − (i) Binary Locks − A lock on a data item can be in two states; it is either locked or unlocked. (ii) Shared/exclusive − This type of locking mechanism differentiates the locks based on their uses Exclusive lock is a lock is acquired on a data item to perform a write operation Shared locks are shared read locks are where no data value is being changed There are 6 types of lock protocols available − (1) Simplistic Lock Protocol (2) Pre-claiming Lock Protocol (3) Two-Phase Locking (2PL) (4) Strict Two-Phase Locking (5) Timestamp-based Protocols (6) Timestamp Ordering Protocol Types of lock protocols Simplistic Lock Protocol Simplistic lock-based protocols allow transactions to obtain a lock on every object before a 'write' operation is performed Transactions may unlock the data item after completing the ‘write’ operation Pre-claiming Lock Protocol Pre-claiming protocols evaluate their operations and create a list of data items on which they need locks Before initiating an execution, the transaction requests the system for all the locks it needs beforehand Transaction executes and releases all the locks when all its operations are over If all the locks are not granted, the transaction rolls back and waits until all the locks are granted Pre-claiming Lock Protocol Types of lock protocols (Two-Phase Locking 2PL) Two-Phase Locking 2PL divides the execution phase of a transaction into three parts - When transaction starts executing, it seeks permission for the locks it requires Transaction acquires all the locks As soon as the transaction releases its first lock, the third phase starts Two-phase locking has two phases Growing- all the locks are being acquired by the transaction Shrinking- locks held by the transaction are being released Types of lock protocols (contd) Strict Two-Phase Locking First phase of Strict-2PL is same as 2PL Strict-2PL does not release a lock after using it Strict-2PL holds all the locks until the commit point and releases all the locks at a time Timestamp-based Protocols Timestamp-based Protocols Most commonly used concurrency protocol Uses either system time or logical counter as a timestamp Start working as soon as a transaction is created Every transaction has a timestamp associated with it, and the ordering is determined by the age of the transaction Timestamp Ordering Protocol The timestamp of transaction Ti is denoted as TS(Ti) Read time-stamp of data-item X is denoted by R-timestamp(X) Write time-stamp of data-item X is denoted by W-timestamp(X) Timestamp ordering protocol works as follows: If a transaction Ti issues a read(X) operation If TS(Ti) < W-timestamp(X) Operation rejected If TS(Ti) >= W-timestamp(X) Operation executed All data-item timestamps updated If a transaction Ti issues a write(X) operation If TS(Ti) < R-timestamp(X) Operation rejected If TS(Ti) < W-timestamp(X) Operation rejected and Ti rolled back Otherwise, operation executed Distributed database management systems A distributed DBMS manages the distributed database in a manner so that it appears as one single database to users A distributed database is a collection of multiple interconnected databases which are spread physically across various locations that communicate via a computer network Features of a distributed database Databases in the collection are logically interrelated with each other Data is physically stored across multiple sites The processors in the sites are connected via a network A distributed database is not a loosely connected file system A distributed database incorporates transaction processing, but it is not synonymous with a transaction processing system Distributed Database Management System A distributed database management system (DDBMS) is a centralized software system that manages a distributed database in a manner as if it were all stored in a single location Features It is used to create, retrieve, update and delete distributed databases It synchronizes the database periodically and provides access mechanisms by the virtue of which the distribution becomes transparent to the users It ensures that the data modified at any site is universally updated It is used in application areas where large volumes of data are processed and accessed by numerous users simultaneously It is designed for heterogeneous database platforms It maintains confidentiality and data integrity of the databases DDBMS Factors Encouraging DDBMS Distributed Nature of Organizational Units Need for Sharing of Data Support for Both Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) Database Recovery Support for Multiple Application Software Advantages of Distributed Databases Modular Development More Reliable Better Response Lower Communication Cost Challenges Need for complex and expensive software Processing overhead Data integrity Overheads for improper data distribution Database privacy Database privacy governs how data is collected, shared amd used Data privacy revolves around the use and governance of personal data Data privacy is a branch of data security concerned with the proper handling of data – consent, notice, and regulatory obligations Why is Data Privacy Important? There are two reasons for why data privacy is one of the most significant issues in our industry Transparency in how businesses request consent, abide by their privacy policies, and manage the data that they’ve collected is vital to building trust and accountability Privacy is the right of an individual to be free from uninvited surveillance Business-Focused Data Privacy Tips Integrate training on data privacy Take advanyage of security tools Monitor network for suspicious activities Don’t underestimate hackers’ interest in your company Implement the zero trust model Database security Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious threats and attacks. It is a broad term that includes a multitude of processes, tools and methodologies that ensure security within a database environment Data security refers to ways organizations protect their data, including technical safeguards that help ensure data confidentiality, integrity and availability Data Security protects data from compromise by external attackers and malicious insiders Database Failure Database failure refers to any kind of bugs or hardware malfunction in the operating system or the database software Causes loss of content residing on the volatile storage such as main memory, cache memory, RAM, etc. Leads to hardware failure Causes of Database failure Power failure Disk Failure Human Error Software Corruption Virus Infection Natural Disasters Disgruntled Employees Database recovery Database recovery is the process of restoring the database and the data to a consistent state Crash recovery is the process by which the database is moved back to a consistent and usable state Crash recovery done by rolling back incomplete transactions and completing committed transactions that were still in memory when the crash occurred Database Recovery Techniques Undoing Deferred update Immediate update Caching/Buffering Shadow paging Backup techniques Full database backup Differential backup Transaction log backup Object-oriented databases An object-oriented database is a collection of object-oriented programming and relational database An object-oriented database is organized around objects rather than actions, and data rather than logic Examples are c++ and Java Polymorphism Programming language's ability to process objects differently depending on their data type or class No matter what shape an object is, applying the area method to it will return the correct results Inheritance Enables sharing attributes between objects such that a subclass inherits attributes from its parent class Subclasses must include the same database field (or fields) as the parent class for their primary key (although the primary key can have different names in these two tables) Encapsulation Object contains both the data structures and the methods to manipulate the data structures Ensures that changes in the internal data structure of an object does not affect other objects provided the public methods remains the same Abstraction Displaying only essential information and hiding the details Providing only essential information about the data Hiding the background details or implementation Client/server systems Client-server is a software architecture model consisting of two parts, client systems and server systems, both communicating over a computer network or on the same computer A client-server application is a distributed system made up of both client and server software The client-server model describes how a server provides resources and services to one or more clients Clients requests a resource and the server provides that resource Server may serve multiple clients at the same time while a client is in contact with only one server Examples Web servers, mail servers, and file servers Two - Tier Client/Server Structure 2 parts- a client tier and a server tier Client tier sends a request to the server tier Server tier responds with the desired information Example of a two tier client/server structure is a web server Web server returns the required web pages to the clients that requested them Advantages of Two - Tier Client/Server Structure This structure is quite easy to maintain and modify The communication between the client and server in the form of request response messages is quite fast Disadvantage of Two - Tier Client/Server Structure If the client nodes are increased beyond capacity in the structure, then the server is not able to handle the request overflow and performance of the system degrade Three - Tier Client/Server Structure Has three layers namely client, application and data layer Client layer requests information For exsmple GUI, web interface etc Application acts as an interface between the client and data layer Data layer contains the required data Advantages of Three - Tier Client/Server Provides better service and fast performance Can be scaled to requirements without problem Data security is improved Disadvantages of Three - Tier Client/Server Complex due to advanced features Data warehouse A branch of business intelligence A relational database that is designed for query and analysis rather than for transaction processing Process for collecting and managing data from varied sources to provide meaningful business insights Used for online analytical processing - Reporting and data analysis System that pulls together data from many different sources within an organization for reporting and analysis Reports created from complex queries within a data warehouse are used to make business decisions Data mining Known as Knowledge Discovery in Data (KDD) Practice of automatically searching large stores of data to discover patterns and trends that go beyond simple analysis Extracts information from huge sets of data Process used by companies to turn raw data into useful information Uses software to look for patterns in large batches of data Consists of data preparation , modeling , evaluation and deployment Data mining techniques - classification, clustering, regression, association rules, outer detection, sequential patterns, and prediction Used by companies with a strong consumer focus — retail, financial, communication, and marketing organizations Databases in electronic commerce E-business database used to hold and update information E-business database used to hold and update information on product descriptions and specifications, product reference numbers, prices, promotional offers and availability Database system helps e-commerce sites pinpoint potential customers based on compiled information E-commerce use databases for - transaction tracking; product catalogs; non-product content, such as blog posts and “about us” pages Darabases enables storing, analysis and management of information about products, sales etc Web database development Web database application development uses applications interface used to access the database server Dynamically driven web application uses a database to store web content (text, media, press releases, etc.) Web content is pulled from the database via an application server such as ColdFusion, ASP or Java and converted into a web page “on-the-fly” Sample databases for web development - - MySQL used in open source web projects that require a database in the back-end - PotgreSQL- open source object-relational database system - Oracle - best database for critical commercial application - SQLite - Microsoft SQL Server Database administration Function of managing and maintaining database management systems (DBMS) software Consists of everything required to manage a database and make it available as needed Include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery Database administrators (DBAs) use specialized software to store and organize data Database administration - whole set of activities performed by a database administrator to ensure that a database is always available as needed DBMS software such as Oracle, IBM DB2 and Microsoft SQL Server need ongoing management