Business Intelligence Overview Quiz

BSc (Hons) Computer Science - 5502CSQR

• Course title: Database Systems
• Lecturer: Dr. Ali Baydoun

Lecture 11 Revision Part 1 - December 2024 Exam

• Topic: ACID Compliance
• RDBMS products are compliant with ACID properties
• Atomicity: All changes are treated as a single operation (either all succeed or none).
• Example: Funds transfer, either both accounts update or neither do.
• Consistency: Data maintains consistency from start to end of a transaction.
• Example: Funds transfer, the total sum of both accounts remains the same before and after transfer.
• Isolation: Intermediate states of a transaction are hidden from other concurrent transactions.
• Example: Transaction A sees the updated accounts (either account 1 or 2), but not both at the same time.
• Durability: Changes persist after a transaction completes, even in system failure.
• Example: Funds transferred will still be reflected in the accounts even after a system failure.

Concurrent Transactions (refer to Lecture 12)

• Concurrency refers to multiple transactions operating simultaneously.
• Every DBMS needs to ensure only one user can modify data at a time (to avoid concurrency problems).
• Concurrency problems include lost updates and inconsistent reads.
• Locking mechanisms are crucial to avoid these problems.

Transactions (refer to Lecture 12)

• Transactions group operations into a logical unit of work.
• Implicit Transactions: Single DML statements (INSERT, UPDATE, DELETE).
• Explicit Transactions: Multiple DML statements, demarcated with START TRANSACTION, COMMIT (or ROLLBACK).

Transactions (refer to Lecture 12) - MySQL

• When using explicit transactions, AUTOCOMMIT is off until COMMIT (or ROLLBACK).
• COMMIT [WORK]: Saves transaction changes.
• ROLLBACK [WORK]: Reverts changes made in a transaction.
• Note: COMMIT and ROLLBACK are logical units of work, not decision-making statements themselves. Conditional statements are needed for decisions (e.g., IF/THEN).

Database Recovery

• In the event of a system failure, the database must be restored as soon as possible.
• Typical recovery methods include rollback/rollforward.

Rollback/Rollforward

• Rollback: Reverses changes made by a transaction to retrieve the original state. It uses "before-images."
• Rollforward: Restores the database to a modified state using saved data and valid transactions since the last save. Uses "after-images".

Checkpoint

• Checkpoint is a synchronization point between database and the transaction log.
• DBMS halts new requests, processes existing requests, and saves to disk. Waits for the entire process to complete before accepting new requests.
• Checkpoints speed up database recovery.
• Databases can be recovered from the last checkpoint onward.
• Most DBMSs automatically perform checkpoints periodically.

Object Locking

• Lock granularity refers to the size of the database object that a single lock affects.
• Types: Database-level, Table-level, Page-level, Row-level, Field-level

Database Security

• Database security ensures that only authorized users perform authorized actions at authorized times.
• Implementing database security involves determining user processing rights and enforcing security features in both the DBMS and application programs.

DBMS Security

• DBMS products provide security facilities.
• They limit actions on objects for specific users or groups (called roles).
• Usernames and passwords are nearly universal security methods in DBMS.

DBMS Security Model

• A diagram showcasing users, roles, permissions, and objects with relationships.
• Specific examples of user permissions are provided (e.g., "Eleanore Wu can execute Monthly End Stored Procedure").

Data Warehousing & Big Data/Data Analytics

• Big Data refers to voluminous data generated by web applications (eg. Search engines, social media platforms, etc).
• Big data problems are increasingly prevalent even outside of web-related applications, such as scientific research and business operations.

Business Intelligence (BI) Systems

• BI systems aid managers/professionals in analysing current/past activities & predicting future events.
• Reporting and Data Mining are the two main categories.

The Relationship of Operational and BI Systems

• Diagram showing the flow of data from Operational Databases to BI Systems.
  • Operational applications funnel transaction data into Operational DBMS.
  • Data is extracted and used for BI Applications.

Components of a Data Warehouse

• Diagram illustrating the components of a data warehouse (e.g., Operational Databases, Data Warehouse, Metadata, ETL system, Data Warehouse DBMS, BI Tools, and Business Intelligence Users.)

Characteristics of Operational and Dimensional Databases

• Summarizing the differences between operational and dimensional databases pertaining to data type, use, and update methods.

Reporting Systems (OLAP)

• OLAP reports use measures and dimensions like a data item of interest and characterisitc of that measure.
• OLAP cubes represent measures with associated dimensions.
• OLAP reports are synonymous with OLAP cubes.

Data Analytics

• Different types of data analytics:
  • Descriptive: Learn about past and current data, like who are the best customers?
  • Predictive: Forecasting future behavior of customers based on past data, like how will the customers behave in the future?

Pros and Cons of BI

• Pros: Big data management in real-time for quick decisions; Tracking KPIs; Helpful metric development.
• Cons: Cost is a potential issue, even in Cloud systems; Data security is a concern, especially with mobile and cloud-based data management.

Test your knowledge on the advantages, risks, and impacts of business intelligence systems. This quiz covers key concepts like OLAP, mobile BI applications, and data management issues. It's a great way to assess your understanding of how business intelligence affects organizations.