BSc (Hons) Computer Science Lecture 11 Revision PDF

BSc (Hons) Computer Science 5502CSQR Database Systems ByBy Dr. Ali Baydoun 5502CSQR \ Lecture 11 REVISION PART 01 for December 2024 Examination Key Ideas & Concepts (ACID Compliance) RDBMS products are said to be ACID compliant Atomicity All changes to data are...

BSc (Hons) Computer Science 5502CSQR Database Systems ByBy Dr. Ali Baydoun 5502CSQR \ Lecture 11 REVISION PART 01 for December 2024 Examination Key Ideas & Concepts (ACID Compliance) RDBMS products are said to be ACID compliant Atomicity All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are. For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account. Consistency Data is in a consistent state when a transaction starts and when it ends. For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction. Isolation The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized. For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither. Durability After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure. For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed. Concurrent Transactions (refer to Lecture 12) Concurrency refer to two or more transactions that appear to users as they are being processed against a database at the same time. Concurrency. Every DBMS has users, possibly a great many and we must ensure that only one can modify data at any one time (e.g. two people trying to edit the same data at the same time). Thusly, every DBMS must support a locking mechanism. Concurrency problems are lost updates and inconsistent reads. Transactions (refer to Lecture 12)  Transactions group a series of operations so they make a single logical unit of work. Implicit Transactions. ○ Any single DML statement (e.g. INSERT, UPDATE, DELETE). Explicit Transactions. ○ Any group of DML statements where the transaction’s scope is explicitly denoted by the START TRANSACTION & COMMIT (or ROLLBACK) statements respectively. Transactions (refer to Lecture 12) When working with explicit transactions, MySQL supports standardized statements, including : https://dev.mysql.com/doc/refman/8.0/en/commit.html START TRANSACTION. The AUTOCOMMIT setting is OFF until we explicitly issue a COMMIT (or ROLLBACK) statement. COMMIT [WORK]. Saves changes that were made by the transaction. Transactions (refer to Lecture 12) ROLLBACK [WORK]. Cancels changes that were made by the transaction, discarding any new data & restoring any orig. data (e.g. the data’s state before the START TRANSACTION statement). Note that the COMMIT & ROLLBACK statements don’t make decisions, they make a single logical unit of work. To make decisions, we use conditional statements (e.g. IF) or exception handlers as part of a code block (e.g. SPROC). See Lecture 12 _ Doc.docx on Canvas ! Database Recovery In the event of system failure, that database must be restored to a usable state as soon as possible. Typical recovery techniques: Recovery via rollback/rollforward Rollback/Rollforward Recovery via rollback/rollforward: Periodically save the database and keep a database change log since the save. Database log contains records of the data changes in chronological order. When there is a failure, either rollback or rollforward is applied. Rollback: undo the erroneous changes made to the database and reprocess valid transactions Rollforward: restore database using saved data and valid transactions since the last save Rollback Before-image: a copy of every database record (or page) before it was changed Rollforward After-image: a copy of every database record (or page) after it was changed Checkpoint A checkpoint is a point of synchronization between the database and the transaction log. DBMS refuses new requests, finishes processing outstanding requests, and writes its buffers to disk. The DBMS waits until the writing is successfully completed  the log and the database are synchronized. Checkpoints speed up database recovery process. Database can be recovered using after-images since the last checkpoint. Checkpoint can be done several times per hour. Most DBMS products automatically checkpoint themselves. Object locking Lock granularity refers to the size of the database object that a single lock is placed upon. Lock granularity can be: 1.Database-level: meaning the entire database is locked by one lock. 2.Table-level: meaning a table is locked by one lock. 3.Page-level: meaning a disk page is locked by one lock. 4.Row-level: meaning one row is locked by one lock. 5.Field-level: meaning one field in one row is locked by one lock. Database Security Database security ensures that only authorized users can perform authorized activities at authorized times. Developing database security: Determine users’ processing rights and responsibilities. Enforce security requirements using security features from both DBMS and application programs. DBMS Security DBMS products provide security facilities. They limit certain actions on certain objects to certain users or groups (also called roles). Almost all DBMS products use some form of username and password security. DBMS Security Model Data Warehousing Big Data/Data Analytics Big Data—the current term for the enormous datasets generated by Web applications such as search tools (for example, Google and Bing) and Web 2.0 social networks (for example, Facebook, LinkedIn, and Twitter). Although these new and very visible Web applications are highlighting the problems of dealing with large datasets, these problems were already present in other areas, such as scientific research and business operations. Business Intelligence (BI) Systems Business intelligence (BI) systems are information systems that assist managers and other professionals: To analyze current and past activities. To predict future events. Two broad categories: Reporting Data mining The Relationship of Operational and BI Systems Components of a Data Warehouse Characteristics of Operational and Dimensional Databases Reporting Systems: OnLine Analytical Processing [OLAP] An OLAP report has measures and dimensions: Measure—a data item of interest Dimension—a characteristic of a measure OLAP cube—a presentation of a measure with associated dimensions. An OLAP cube can have any number of axes. The terms OLAP cube and OLAP report are synonymous. OLAP allows drill-down—a further division of the data into more detail – EG Attribute Hierarchies (q15) Data Analytics Pros and Cons of BI Pros Cons You get to manage big data in real Cost will always be a potential issue. Even companies that use mobile or Cloud-based time Sometimes you need to make a solutions for their BI applications may struggle split-second decision with the costs involved in data management. Business Intelligence allows Data security is questionable. If you’re using mobile BI applications, then the threat of organizations to better track how hacking can put your sensitive or proprietary their Key Performance Indicators information at risk. Data hacks at Target, [KPIs] are being fulfilled. The Home Depot, and other retailers prove that umbrella of tools can look at the big other systems aren’t 100% safe either. Unless your system is completely disconnected from data, help develop useful metrics, an online portal, the threat of have a security and this ultimately makes a breach is something which must always be company more effective at what it proactively considered. does https://brandongaille.com/14-pros-and-cons-of- business-intelligence/