INF3703 EXAM PACK 2024

True & False The objective of SOL performance tuning on the client side is to generate an SOL query that returns the correct answer in the least amount of time, using the maximum amount of resources on the server side All database transactions must display at least the properties of Atomicity, consistency, convenience, and isolation Database recovery techniques are based on the isolation transaction property which indicate that the data used during execution of a transaction cannot be used by a second transaction until the first one is completed Database recovery techniques are based on the durability transaction property. Which ensures that once transaction (committed), they cannot be undone or lost changes are done In a distributed database, concurrency control becomes important because multi-site and multiple-process operations are more likely to create data inconsistencies and deadlocked transactions than single-site systems Open Database Connectivity (ODBC) is a Microsoft middleware that adds objectoriented functionality for access to relational and non-relational Replication transparency, hardware Independence, partition processing; and transaction processing are example of C J. DATE'S twelve commandments for distributed databases All transaction management commands are processed during the parsing and execution phases of query processing Data replication and partitioning is a star schema performance Improvement technique that splits and makes copy of a database table Platform as a Service (PaaS) is the cloud service provider that offers consumers the ability to provision their own resources on demand Data mining is a small, Single-subject data warehouse subsect that provides decision support to a small group of people Fragmentation transparency is the highest level of transparency where the end user or programmer does not need to know that a database is partitioned DBMS and end users interact through the use of systems to generate Information in different sequence Replication transparency, hardware Independence, partition processing; and transaction processing are example of C J. DATE'S twelve commandments for distributed databases The implementation phase of database design includes Installing the DBMS, creating the database storage structure, introduce changes and loading the database. The Data Administrator (DA) and Database Administrator (DBA) functions overlap The level of transparency supported by the DDBMS differs from system to system; location and local mapping are examples of distributed transparency. Attribute hierarchy is a component of star schemas that provide top-down data organization The first stage of logical database design is to translate the physical database design into a set of relational database structure SQL transactions are formed by several SQL statements and database requests. Each database request originates one 1/0 database operation SQL performance tuning is a set of activities and procedures designed to ensure that end-users query is processed by the DBMS in the minimum amount of time, while making best use of resources Working with data in data file is much faster than working with data caches because the DBMS does not have to wait for the hard disk to retrieve the data and no I/O operations are needed to work within the data file Query parsing is a phase in which all rows that match the specified condition(s) are retrieved, sorted and grouped Non-volatile ensures that data are never deleted and new data are continually added, so the warehouse is always growing Distributed DBMS (DDBMS) governs the storage and processing of logically related data over interconnected computer systems It also ensures standard commun1cat1on protocols at the database level The reason behind the development of star schema was because the existing relational modelling techniques such as ER and normalization did not yield a database structure that served advanced data analysis requirements well The data cache is where the data read from the database data files are stored before the data have been read or after the data are written to the database data file Cloud computing allows organisations to quickly and economically add information technology services such as applications, storage, servers, processing power, database and Infrastructure to its IT Portfolio Assignement 1 1 In which phase of the system development life circle are problems examined in greater detail? [1] Planning phase [2] Analysis phase [3] Detail systems design phase [4] Implementation 2 2 A transaction property that guarantees that committed transaction changes cannot be undone or lost, is ___. [1] durability [2] isolation [3] atomicity [4] consistency 1 3 A (an) _____ is reached when all changes to the database are permanently recorded? [1] update statement [2] delete statement [3] commit statement [4] rollback statement 3 4 What happens when a rollback is issued before the termination of a transaction? [1] The DBMS will only store the database for the transaction. [2] The DBMS will restore the database only for that particular transaction. [3] The DBMS will store the database for all the transactions. [4] The DBMS will restore the database for all the transactions. 2 5 The concurrency control problem of lost update occurs when _______. [1] two transactions are executed concurrently and the first one is rolled back after the second transaction has already accessed the data of the first transaction [2] a transaction accesses data before and after one or more other transactions finished working with such data [3] two concurrent transactions are updating the same data element and one of the updates is overwritten by the other transaction [4] transactions executed simultaneously in a multiuser database yield the same result 3 6 A process that ensures a database is immediately updated by operations during the transaction’s execution before the transaction reaches its commit point is called _____. [1] deferred buffer technique [2] write through technique [3] deferred write technique [4] write buffer technique 2 7 ____ occurs when you keep the same number of systems, but migrate each system to a larger system. [1] Scaling up [2] Clustering out [3] Scaling out [4] Clustering up and out 1 8 The characteristic of big data that refers to the changes in the meaning of data based on context, is known as _____. [1] velocity [2] variety 2 [3] volume [4] variability 9 A big data implementation technology designed specifically to distribute and process enormous amounts of data across vast clusters of services is known as _____. [1] visualization [2] Hadoop [3] Cloudera [4] Hortonworks 2 10 High volume, write-once, read-many, streaming access and fault tolerance are key assumptions of the ______ approach to distributed data. [1] reduced file system [2] MapReduce file system [3] Hadoop distributed file system [4] polyglot file system 3 11 Most BI vendors are dropping the term “data mining” and replacing it with the term ______. [1] predictive analytics [2] explanatory analytics [3] data analytics [4] knowledge acquisition 1 12 The end-users decide what techniques to apply to the data when using the ______ mode of data mining. [1] guided [2] prognosis [3] directed [4] automated 1 13 _______ is a collection of technologies used to access any type of data source and manage the data through a common interface. [1] DAO [2] ODBC [3] OLE-DB [4] UDA 4 14 In the ADO.NET framework, the _____ object is the in-memory representation of the data in the database. [1] DataCommand [2] DataReader [3] DataAdapter [4] Dataset 4 15 Which of the following is a reason why a DBMS’s daily operations must be clearly documented? [1] Documentation of daily operations helps a company set its long-term goals. [2] Documentation of the daily operations helps manage the manual data resources of a company. [3] Documentation of the daily operations helps pinpoint causes and solutions of database problems. [4] Documentation of the daily operations helps free the DBA from many lowerlevel technology-oriented tasks. 3 16 _____ deals with ensuring that data is protected against unauthorized access, and if the data is accessed by an authorized user, that the data is used only for an authorized purpose. [1] Integrating [2] Compliance [3] Availability [4] Confidentiality 4 17 The _____ component of a CASE tool produces the information system’s input/output formats. [1] analysis [2] graphics [3] program document generator 4 [4] screen painter and report generator 18 The DSO’s activities are often classified as _____ management, which includes all DBA activities designed to ensure data availability following a physical calamity or a database integrity failure. [1] disaster [2] efficient [3] effective [4] correct 1 19 One of the important functions provided by the database _____ is to reserve the resources that must be used by the database at run time. [1] security component [2] authorization manager [3] initialization parameters [4] administrator 3 20 In the context of developing a data administration strategy, a (an) _____ provides a framework that includes computerized, automated and integrated tools such as a DBMS and CASE tools. [1] access plan [2] active data dictionary [3] information systems architecture [4] security policy 3 1 Discovery of user requirements, existing system evaluation and logical system design are part of the _____ phase of the Systems Development Life Cycle (SDLC). [1] planning [2] analysis mming [3] detailed systems designed Database development [4] implementation 2 2 “Should the existing system be replaced?” is a question that is asked during the _____ stage of the Systems Development Life Cycle (SDLC). [1] planning [2] analysis [3] implementation [4] maintenance 3 Once the data has been loaded into the database, the _____ tests and finetunes the database for performance, integrity, concurrent access and security constraints. [1] programmer [2] manager [3] database administrator [4] system administrator 4 The feasibility study during the planning phase of the Systems Development Life Cycle (SDLC) must address the _______. [1] requirements of the current system’s end users [2] problems and constraints related to the company situation [3] questions about modification and the replacement of the existing system [4] technical aspects of hardware and software requirements 5 Which of the following is a hardware-induced database failure? [1] memory chip error [2] viruses [3] malware [4] abortion due to deadlock 6 A consistent database state is ______. [1] one in which all tables have foreign keys [2] one in which all data integrity constraints are satisfied [3] one in which all tables are normalized [4] one in which all SQL statements only update one table at a time 7 A single-user database system automatically ensures _____ of the database, because only one transaction is executed at a time. [1] serializability and durability [2] atmocity and isolation [3] serializability and isolation [4] atomicity and serializability 8 ANSI has defined four events that signal the end of a transaction. Of the following events, which is defined by ANSI as being equivalent to a COMMIT? [1] Five SQL statements are executed. [2] The end of a program is successfully reached. [3] The program is abnormally terminated. [4] The database is shut down for maintenance. 9 The implicit beginning of a transaction is ______. [1] when the database is started [2] when a table is accessed for the first time [3] when the first SQL statement is encountered [4] when the COMMIT command is issued 10 A DBA determines the initial size of the data files that make up the database. However, as required, the data files can automatically expand in predefined increments known as _____. [1] procedure cache [2] buffer cache [3] supplements [4] extends 11 The data cache is where the data read from the database data files are stored _____ the data have been read or _____ the data are written to the database data files. [1] after; before [2] after; after [3] before; before [4] before; after 12 A centralized database management is subject to a problem such as _____. [1] a growing number of remote locations [2] maintaining and operating small database systems [3] dependence on multiple sites [4] organizational flexibility of the database 13 A _____ contains the description of the entire database as seen by the database administrator. [1] distributed global dictionary [2] distributed data dictionary [3] distributed global schema [4] distributed data schema 14 Which of the following is a personal analytics vendor for BI applications? [1] IBM [2] Kognitio [3] Netezza [4] MicroStrategy 15 Operational data is commonly stored in many tables, and the stored data represents information about a given _____ only. [1] transaction [2] database [3] table [4] concept 16 In the context of big data, _____ relates to differences in meaning. [1] variety [2] variability [3] veracity [4] viability 17 Which of the following is NOT a key assumption of the Hadoop Distributed File System? [1] High volume [2] Write many, ready-once [3] Streaming access [4] Fault-tolerance 18 Which of the following is the first step in the exchange between a web browser and a database? [1] The HTML output is displayed on the client computer’s web browser. [2] The middleware receives the query result and creates the HTML-formatted page. [3] The client’s web browser requests a page from a web server. [4] The web-to-database middleware uses ODBC to connect to the database. 19 What is a disadvantage of using an API web server interface? [1] It is slower than CGI scripts. [2] It is inefficient because it uses DLLs. [3] An external program needs to run for each user request. [4] An API error can bring down the entire web server. 20 At the level of middle management, the database must be able to _____. [1] provide a framework for defining and enforcing organizational policies [2] provide the data necessary for tactical decisions and planning [3] provide feedback to monitor whether a company is achieving its goals [4] provide access to external and internal data to identify growth opportunities Assignment 3 The DBMS guarantees that the semantic meaning of a transaction truly represents the real-world event. True False F Durability requires that all portions of the transaction must be treated as a single, logical unit of work in which all operations are applied and completed to produce a consistent database. True False F The phenomenon of uncommitted data occurs when two transactions are executed concurrently and the second transaction is rolled back after the first transaction has already accessed the uncommitted data-thus violating the isolation. True False T Time stamping demands a lot of system resources because many transactions might have to be stopped, rescheduled, and stamped. True False T The last step in the write-through technique recovery procedure is to identify the last checkpoint in the transaction log. True False F A single-user database system automatically ensures serializability and isolation of the database, because only one transaction is executed at a time. True False T All factors must be checked to ensure that each system component operates at its optimum level and has sufficient resources to minimize the occurrence of bottlenecks. True False T Using index characteristics, a database designer can determine the best type of index to use. True False T Most current-generation relational DBMSs perform automatic query optimization at the client end. True False F DBMS performance tuning include global tasks such as managing the DBMS processes in primary memory and managing the structures in physical storage. True False T On the server side, the database environment must be properly configured to respond to client’s request in the fastest way possible, while making optimum use of existing resource. The activities required to achieve this goal are commonly referred to as DBMS performance True False T The SQL execution activities are performed by the query optimizer. True False F Table space is a logical grouping of several data files that store data with similar characteristics. True False T One of the advantages of a distributed database management system (DDBMS) is that the data is located near the site with the least demand. True False F A transaction processor (TP) is the software component residing on each computer that stores and retrieves data located at the site. True False F A fully distributed management system (DBMS) must perform all the functions of a centralized DBMS, and it must handle all necessary functions imposed by the distribution of data and processing. True False T The level of transparency supported by the distributed database management system remains the same for all systems. True False F A remote transaction, composed of several requests, may access data at multiple sites. True False F The two-phase commit protocol guarantees that if a portion of a transaction operation cannot be committed, all changes made at the other sites participating in the transaction will be undone to maintain a consistent database state. True False T The decision support database schema must support complex (non- normalized) data representations. True False T The attribute hierarchy provides a top-down data organization that is used for two main purpose namely aggregation and drill-down/roll-up data analysis. True False T For a data set to be considered Big Data, it must display all the “3 Vs” – volume, velocity and variety. True False F Characteristics that are important in working with data in the relational database model also apply to Big Data. True False T Flume is a tool for converting data back and forth between a relational database and the HDFS. True False F The only time the client and server computers communicate is when the client requests a page and the server sends the requested page to the client. True False T HTML is designed to facilitate the extraction and manipulation of data from structured documents over the Internet. True False F An enterprise database can be defined as the company’s data representation that provides support for all present and expected future operations. True False T Having a computerized DBMS guarantees that the data will be properly used to provide the best solutions required by managers. True False F Backups must be clearly identified through detailed descriptions and date information, assuring that the correct backup is used to recover the database. True False T When an organization employs both a DA and a DBA, the DA’s job has a technical organization, whereas the DBA’s job has a managerial orientation. True False F SECTION 2 QUIZLETS 33 Test Questions 1 Explain the DATABASE INITIAL STUDY PHASE of the DBLS 1. Analyze the company situation 2. Define the problems and constraints 3. Define the objectives 4. Define the scope and boundaries 2 Name the roles associated with a DBA in a technical role 1. Evaluate, select and install the DBMS 2. Design and implement DB and APPS 3. Testing and evaluating DB and APPS 4. Training and support user 5. Maintain the DBMS, DB and APPS 3 Briefly explain the conceptual design phase of the DB design process 1. Data analysis and requirements 2. Entity relationship modelling and normalization 3. Data model verification 4. Distributed database design 4 Name the steps required in the development of ER diagrams. 1. Identify, analyze and refine the business rules 2. Identify the main entities. 3. Define the relationships among the entities. 4. Define the attributes, PK and FKs 5. Normalize the entities 5 Explain the flow of phases in a query. 1. Parsing phase DBMS parses the SQL query and chooses the most efficient execution plan 2. Execution phase The DBMS execute the SQL query using the execution plan 3. Fetching phase The DBMS fetches the data and sends the result set back to the client 6 Name all the transaction properties 1. Atomicity 2. Consistency 3. Isolation 4. Reliability 5. Serializability 7 What is concurrency control and why is it necessary. It allows the simultaneous execution of transactions in a multi-user database system. Its objective is to yield consistent results 8 Name and explain 3 additional issues when designing DDBMS 1. Data Fragmentation 2. Data replication 3. Data Allocation 9 Data Security, privacy and integrity are important functions in authorization management. Name 7 activities that a DBA should do in his role as management. 1. Assign users to the DB 2. Assign each user a password 3. Define user groups 4. Define user views 5. Control physical access to the network 6. Control access to the DBMS 7. Monitor DBMS usage 10 Identify the critical success factors in the development and implementation of a successful data system. 1. Management commitment 2. Thorough company situation analysis 3. End-User involvement 4. Defined Standards 5. Training 6. Start with a small pilot project 11 Discuss the difference between the top down and bottom up strategies. The TOP DOWN strategy involves identifying the data sets first, then identifying the data elements for those sets. The BOTTOM UP strategy involves identifying the data elements first, then grouping them together in data sets. 12 What is cloud computing and how does it relate to cloud services. 1. Cloud computing refers to the delivering of applications, storage and other resources to users over the web. Users can use the cloud to access SAAS, PAAS and IAAS. 13 List 3 different types of implementations for cloud computing 1. Private Cloud 2. Public Cloud 3. Community Cloud 14 Discuss the 4 important concepts that affect data recovery 1. Write ahead log protocol 2. redundant transaction logs 3. Database Buffers 4. Database checkpoints 15 What is an exclusive lock and when can it be granted 1. exists when access is specifically reserved for the transaction that locked the object. It can only be granted if there is no locks on the data that is requested to be locked. 16 What is the difference between rule based and cost based query optimizer modes 1. Rule based query optimization uses a fixed set of rules to optimize a query. This leads to a 'fixed cost' at which queries are optimized, 2. Cost based query optimization uses a advanced set of algorithms based on database statistics and analytics to calculate the shortest possible execution path. The problem is that the overhead cost of the algorithms is much higher. 17 Describe the difference between SQL and DBMS performance tuning SQL - happens on client side. Better code for faster execution DBMS - happens on server side. Better management of resources including cache and memory. 18 Describe the difference between Data cache and SQL cache 1. Data cache - used by the DBMS to store fetched data from the DB. 2. SQL cache - used to store the most recent SQL statements that were executed. 19 Fully DDBMS must perform all the functions of a centralized DBMS. Name these functions 1. Receive application request 2. Validate, analyze and decompose the request. 3. Map the request's logical-to-physical data components 4. Search, locate, read and validate data 5. Ensure the DB consistency, security and integrity 6. Validate the data for conditions 20 What are the 4 star schema performance techniques 1. Normalize the dimension tables 2. Denormalize the fact tables 3. Maintain multiple fact tables 4. Replicate and Partition tables 21 Explain 6 measures you as DB Admin will take to ensure backup and recovery of the DB 1. Periodic backup of data and applications 2. Proper identification of what to back up 3. Convenient and Safe backup and storage 4. Physical protection of software and hardware 5. Limit personal access to control software and DB 6. Insurance coverage for data 22 Explain in detail the 3 stages of DB design Conceptual design 1. Data analysis and requirements 2. ERM and normalization 3. Data model verification 4. DDBMS Logical design 1. Create the logical data model 2. Validate the logical data model 3. Assign integrity constraints 4. Merge logical models for different parts together 5. review the logical model with the user Physical design 1. translate each relation into tables 2. Determine a suitable file organization 3. Define indexes 4. Define user view 5. Estimate data storage 6. determine db security for users 23 What is a deadlock It is when 2 transactions wait on each other to complete indefinitely. The one cannot continue without the other one finishing. thus there is a deadlock 24 Name 3 techniques used to deal with deadlock 1. prevention 2. avoidance 3. detection 25 What is a consistent state and how is it achieved how is it achieved 1. A db is in a consistent state whenever all the constraints are satisfied. A successful transaction takes a db from one consistent state to another. 26 What 3 levels of backup is used in DB recovery 1. Full backup 2. Differential backup 3. Transaction log backup 27 Describe the different types of DB requests and transactions 1. a request is a single SQL command 2. a transaction consists out of two or more requests. you could have a single request going to a single data source you could have multiple requests going to a single data source you could have multiple requests going to multiple data sources transactions include SQL statements like FROM, SELECT, UPDATE, COMMIT 28 Discuss a DDBMS transparency features 1. Transaction transparency 2. Distribution transparency 3. Failure transparency 4. Performance transparency 5. Heterogeneity transparency 29 What is a data warehouse, and name its components It is an integrated, subject oriented, time-variant, non-volatile - once data enter it never leaves 30 What is a star schema It is a modeling technique used to map multidimensional decision support data into a relational database. 31 Name the components of a Star schema 1. Fact Tables 2. Dimension tables 3. Attributes 4. Attribute hierarchy 32 Name the differences between the two database design approaches Top-Down approach Identifies the data sets first and then identifies the data elements and attributes within the sets Bottom up approach identifies the data elements ant attributes first, an then group them together in data sets 33 Name the 6 phases of the dblc 1. Initial feasibility study 2. DB Design 3. Loading and Implementation 4. Testing and evaluation 5. Operation 6. Maintenance CHAPTER 9 - DATABASE DEVELOPMENT PROCESS - LONG QUESTIONS Name the 5 phases of the SDLC (PADIM) 1. Planning 2. Analysis 3. Detailed Systems design 4. Implementation 5. Maintenance Name the 6 phases of the DBLC (IDITOM) 1. DB initial study 2. DB design 3. Loading and implementation 4. Testing and evaluations 5. Operation 6. Maintenance Define the initial study phase 1. analyze the company situation 2. define the problems and constraints 3. define the objectives 4. define the scope and boundaries Define the DB design phase 1. Create conceptual design a. Data analysis b. ERModeling and Normalization c. Data model verification d. DDBMS design 2. Create logical design a. create logical db model b. validate data using normalization c. assign and validate constraints 3. Create physical design a. Define view b. Define Indexes c. Estimate storage requirements d. Determine suitable file organization Name 3 types of maintenance preventative corrective adaptive name 2 approaches to DB design 1. Top down 2. Bottom up DBA as manager 1. Give end user support 2. Manage policies, procedures and standards 3. manage data security, privacy and integrity 4. manage backup and recovery 5. manage data distribution and use Technical manager 1. evaluating and selecting the DBMS 2. Design and implement the DB 3. Test and evaluate the DB 4. Operating the DBMS 5. Training the staff 6. Maintaining the DBMS CHAPTER 10: TRANSACTION MANAGEMENT AND CONCURRENCY (LONG QUESTIONS) Name 5 properties that every transaction should have: Atomicity Consistency Isolation Durability Serializability Name 3 main problems why we need concurrency control: Lost Updates Uncommitted data Inconsistent retrievals Locks helps to control concurrency. Lock granularity indicates the level of the lock. Name 5 lock levels that can be used. database-level lock table-level lock page-level lock row-level lock field-level lock Locks can either be a binary lock, a shared lock or an exclusive lock. Name 3 basic techniques to control deadlocks Deadlock prevention Deadlock detection Deadlock avoidance Name 2 methods of concurrency control with time-stamping 1. Wait/Die 2. Wound/Wait scheme Name 3 different levels of backup 1. Full backup 2. Differential backup 3. Transaction log backup Name the two properties that time stamps must have: 1. Uniqueness 2. Monotonicity Name different methods of concurrency control 1. Locks 2. Time-stamping 3. Optimistic method 4. Pessimistic method Name 4 important concepts used during transaction recovery: 1. Write-Ahead Protocol 2. Redundant transaction logs 3. Database Buffers 4. Database checkpoints CHAPTER 10: TRANSACTION MANAGEMENT AND CONCURRENCY (CONCEPTS) What is a Transaction? Logical unit of work that must be entirely completed or aborted What does a Transaction consists of? -SELECT statement -Series of related UPDATE statements -Series of INSERT statements -Combination of SELECT, UPDATE, and INSERT statements Consistent database state: -All data integrity constraints are satisfied -Must begin with the database in a known consistent state to ensure consistency Formed by two or more database requests Database requests: Equivalent of a single SQL statement in an application program or transaction Atomicity (ACIDS) -All operations of a transaction must be completed -If not, the transaction is aborted Consistency (ACIDS) -Permanence of database's consistent state Isolation (ACIDS) -Data used during transaction cannot be used by second transaction until the first is completed Durability (ACIDS) -Ensures that once transactions are committed, they cannot be undone or lost -Ensures that once transactions are committed, they cannot be undone or lost -Ensures that the schedule for the concurrent execution of several transactions should yield consistent results What are two SQL Statements that provide transaction support? COMMIT & ROLLBACK A Transaction sequence must continue until? -COMMIT statement is reached -ROLLBACK statement is reached -End of program is reached -Program is abnormally terminated The transaction log: keeps track of all transactions that update the database DBMS uses the information stored in a log for: -Recovery requirement triggered by a ROLLBACK statement -A program's abnormal termination -A system failure Concurrency control: -Coordination of the simultaneous transactions execution in a multiuser database system -Objective - Ensures serializability of transactions in a multiuser database environment Lost updates problems: -Two concurrent transactions update same data element -One of the updates is lost, overwritten by the other transaction Uncommitted data phenomenon: -Two transactions are executed concurrently -First transaction rolled back after second already accessed uncommitted data Inconsistent retrievals: -First transaction accesses data -Second transaction alters the data -First transaction accesses the data again -Transaction might read some data before they are changed and other data after changed -Yields inconsistent results Basic DBMS architecture: -Database data files stored in permanent secondary memory (hard disk) -DBMS processes running in primary memory (RAM) Concurrency Control with Locking Methods (Locking methods) Facilitate isolation of data items used in concurrently executing transactions. Concurrency Control with Locking Methods (Lock) Guarantees exclusive use of a data item to a current transaction Concurrency Control with Locking Methods (Pessimistic locking) Use of locks based on the assumption that conflict between transactions is likely Concurrency Control with Locking Methods (Lock manager) Responsible for assigning and policing the locks used by the transactions Lock Granularity -Indicates the level of lock use Levels of locking: -Database-level lock -Table-level lock -Page-level lock -Page or diskpage: Directly addressable section of a disk -Row-level lock -Field-level lock Lock Types (Binary) -Has two states, locked (1) and unlocked (0) -If an object is locked by a transaction, no other transaction can use that object -If an object is unlocked, any transaction can lock the object for its use Lock Types (Exclusive lock) -Exists when access is reserved for the transaction that locked the object Lock Types (Shared lock) -Exists when concurrent transactions are granted read access on the basis of common lock Problems in using locks: -Resulting transaction schedule might not be serializable -Schedule might increase deadlocks Two-Phase Locking (2PL): -Defines how transactions acquire and relinquish locks -Guarantees serializability but does not prevent deadlocks Two-Phase Locking (2PL)- Phases -Growing phase - Transaction acquires all required locks without unlocking any data -Shrinking phase - Transaction releases all locks and cannot obtain any new lock Two-Phase Locking (2PL)- Governing rules -Two transactions cannot have conflicting locks -No unlock operation can precede a lock operation in the same transaction -No data is affected until all locks are obtained Deadlocks Occurs when two transactions wait indefinitely for each other to unlock data -Known as deadly embrace Control techniques -Deadlock prevention -Deadlock detection -Deadlock avoidance Choice of deadlock control method depends on database environment Time Stamping: Assigns global, unique time stamp to each transaction -Produces explicit order in which transactions are submitted to DBMS Time Stamping- Properties -Uniqueness: Ensures no equal time stamp values exist -Monotonicity: Ensures time stamp values always increases Time Stamping- Disadvantages -Each value stored in the database requires two additional stamp fields -Increases memory needs -Increases the database's processing overhead -Demands a lot of system resources Concurrency Control with Optimistic Methods Optimistic approach: Based on the assumption that the majority of database operations do not conflict -Does not require locking or time stamping techniques -Transaction is executed without restrictions until it is committed Phases of Optimistic Approach (Read) Transaction: -Reads the database -Executes the needed computations -Makes the updates to a private copy of the database values Phases of Optimistic Approach (Validation) -Transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database Phases of Optimistic Approach (Write) Changes are permanently applied to the database Database Recovery Management -Database recovery: Restores database from a given state to a previously consistent state Database Recovery Management -Atomic transaction property: All portions of a transaction must be treated as a single logical unit of work If transaction operation cannot be completed: -Transaction must be aborted -Changes to database must be rolled back Recovery transactions are based on the atomic transaction property Concepts that Affect Transaction RecoveryWrite-ahead log protocols -Ensures that transaction logs are always written before the data is updated Concepts that Affect Transaction Recovery - Redundant transactions logs -Ensure that a physical disk failure will not impair the DBMS's ability to recover data Concepts that Affect Transaction RecoveryBuffers Transaction Recovery-Buffers Temporary storage areas in a primary memory