Advance Database System PDF

Advance Database System 01. Active State iii. If a transaction requires a. in this state, a transaction stays in this state to access to several tables, each table...

Advance Database System 01. Active State iii. If a transaction requires a. in this state, a transaction stays in this state to access to several tables, each table may be locked. perform READ and WRITE operations iv. Two (2) transactions can 02. Atomicity access the same database a. requires that all operations (SQL requests) of a as long as they access transaction should be completed. different tables. v. Transactions T1 and T2 b. requirement: If the transaction fails after step 3, cannot access the same and before step 6, the system should ensure that table even when they try to its updates are not reflected in the database, an use different rows. inconsistency will result. e. A low level lock: 03. Classification i. Less restrictive a. used to retrieve important and relevant information ii. Allows concurrent transactions to about data and metadata. access different rows of the same table even when the rows are located on the 04. Clustering same page a. used to identify data that are like each other. iii. Improves the availability of data but This process helps to understand the requires high usage of space because a differences and similarities between the data. lock exists for each row in a table of the 05. Committed State database a. after all the operation has been completed 09. Data Mining 06. Consistency a. refers to analyzing massive amounts of data in a. ensures that only valid data following all rules and a data warehouse or other sources to uncover constraints will be written in the database. When a hidden trends, patterns, and relationships. transaction results in invalid data, the database This explains the past and predicting the future reverts to its previous state for analysis. 07. CUBE Operator 10. Data Mining Implementation Process a. Like ROLLUP, this generates subtotals for all the a. Business Understanding: In this step, combinations of grouping column s specified in the the goals of the businesses are set, GROUP BY clause. and the important factors that will help 08. Currency Control with Locking Methods in achieving the goal are discovered. a. A lock guarantees exclusive use of data b. Data Understanding: This step will item to a current transaction. collect the entire data and populate the i. Transaction T2 does not have access to a data in the tool (if using any tool). data item that is currently being used by c. Data Preparation: This step involves transaction T1. selecting the appropriate data, cleaning, ii. The lock is released (unlocked) constructing attributes from data, integrating when the transaction is completed. data from multiple databases. b. The database-level lock: i. Locks the entire database d. Modeling: Selection of the data mining ii. prevents the use of any tables in the technique such as decision-tree, generate database to transaction T2 while test design for evaluating the selected transaction T1 is being executed model, building models from the dataset, iii. Good for batch processes, but not suitable and assessing the built model with experts for multiuser DBMS as it would slow down to discuss the result is done in this step. the data access if thousands of transactions had to wait for the current e. Evaluation: This step will determine the transaction to be completed degree to which the resulting model meets c. The table-level lock: the business requirements. The model is d. reviewed for any mistakes or steps that i. The entire table is locked. should be repeated. ii. Prevents access to any row by f. Deployment: In this step, a deployment plan transaction T2 while transaction is made. The strategy to monitor and T1 is using the table. maintain the data mining model results to  check for its usefulness is formed. Final iii. The treasurer found out that they reports are also made, and a review of the had a mistake in the corresponding whole process is done to check any mistake transaction. The transaction had and see if any step is repeated. been rolled back, and Mary's salary 11. Deadlock is back to ₱15,000. The ₱20,000 a. occurs when two (2) transactions wait indefinitely salary is now considered dirty data. for each other to unlock data. c. Non-repeatable read - a transaction reads a 12. Deployment given row at time T1, and then it reads the a. In this step, a deployment plan is made. The same row at time T2, yielding different strategy to monitor and maintain the data mining results. The original row may have been model results to check for its usefulness is formed. updated or deleted. Example: Final reports are also made, and a review of the i. In Transaction 1, Mary read her whole process is done to check any mistake and own salary of ₱15,000, and the see if any step is repeated. operation was not completed. 13. Durability ii. In Transaction 2, the Treasurer modified a. ensures that once transaction changes are done Mary's salary to ₱20,000 and submitted and committed, they cannot be undone or lost. the transaction. 14. Evaluation iii. In Transaction 1, when Mary read a. This step will determine the degree to which the her salary again, her salary resulting model meets the business requirements. changed to ₱20,000. The model is reviewed for any mistakes or steps 18. Phantom Read that should be repeated. a. a transaction executes a query at time T1, 15. Failed State and then it runs the same query at time a. if one of the operations cannot be done or proceed T2, yielding additional rows that satisfy 16. Lock Types the query. a. Has only two states: locked (1) and unlocked (0). 19. Four Transaction Isolation Levels b. If an object such as a database, table, or row is a. Read Commitment: locked by a transaction, no other transaction can i. is the least restrictive isolation level use that object. c. As a rule, the transaction must unlock the object ii. it ignores locks placed by other after its termination. transactions. d. These options are automatically managed by the iii. Can read modified data values that have not yet been committed by other DBMS. transactions; these are called "dirty" e. User does not require to manually lock or unlock reads. data items. b. A Read Commited f. A shared/exclusive lock: i. Default isolation level for SQL Server g. An exclusive lock exists when access is reserved ii. Prevents dirty reads by specifying that specifically for the transaction that locked the statements cannot read data values object. that have been modified but not yet h. A shared lock exists when a transaction wants to committed by other transactions read data from the database, and no exclusive iii. Other transactions can still modify, lock has held that data item. insert, or delete data between i. Using the shared/exclusive locking concept, a lock executions of individual statements can have three states: unlocked, shared (read), and exclusive (write). within the current transaction, resulting 17. Transaction Isolation Level in non-repeatable reads, or "phantom" data. a. Transaction isolation levels are described by the c. A Repeatable Read type of “reads” that a transaction allows or not. i. A more restrictive isolation level than The types of read operation are: READ COMMITTED b. Dirty Read - a transaction can read data that is ii. Encompasses READ COMMITTED not committed yet. Example: iii. Ensures that no other transactions can i. 1. The original salary of Mary was modify or delete data that has been ₱15,000. The treasurer changed read by the current transaction until Mary's salary to ₱20,000 but did not the current transaction commits commit the transaction. iv. Does not prevent other transactions from inserting new rows into the tables ii. 2. Mary read her wages and found an which have been using in the existing increase in her wages by ₱5,000.  transaction, resulting in “phantom reads”. d. A Serializable Isolation i. Ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction. ii. Most restrictive level and gives solution to the phantom read. Problem. 20. Non-Volatile a. Once data is in a data warehouse, it is stable and does not change. 21. Partially Committed State a. where the final statement in queries has been executed. 22. ROLLUP a. an extension of the GROUP BY clause that is used to create subtotals and grand totals for a set of columns 23. Subject-Oriented a. The DW can analyze data about a particular subject or functional area. i. Subjects can be products, customers, departments, regions, etc. ii. The functional area can be sales, marketing, finance, distribution, etc. iii. Focuses on the data rather than on the processes that modify the data 24. Terminated a. corresponds to the transaction leaving the system and if it is either committed or aborted 25. Uncommitted a. occurs when two transactions, T1 and T2, are executed concurrently, and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data.