Midterm Flashcards
a database designed to enable and support business intelligence (BI) activities, especially analytics.
data warehouse
The data warehouse (DW) can analyze data about a particular subject or functional area
Subject-Oriented
The data warehouse (DW) creates consistency among different data types from different sources.
Integrated
Data in Data warehouse (DW) represents the flow of data through time. It can be organized weekly, monthly, or annually, etc.
Time-variant
Once data is in a data warehouse, it is stable and
does not change.
Non-volatile
Allows business users to quickly access critical data from some sources all in one place. Therefore, it saves the user’s time of retrieving data from multiple sources
Benefits of a Data Warehouse
Provides consistent information on various cross-functional activities. It is also supporting ad-hoc reporting and query.
Benefits of a Data Warehouse
Helps to integrate many sources of data to reduce stress on the production system
Benefits of a Data Warehouse
Helps to reduce total turnaround time for analysis and reporting
Benefits of a Data Warehouse
Restructuring and integration make it easier for the user to use for reporting and analysis.
Benefits of a Data Warehouse
Stores a large amount of historical data. This helps users to analyze different time periods and trends to make future predictions.
Benefits of a Data Warehouse
a collection of operations that form a single logical unit of work.
transaction
requires that all operations (SQL requests) of a transaction should be completed.
Atomicity
ensures that only valid data following all rules and constraints will be written in the database. When a transaction results in invalid data, the database reverts to its previous state
Consistency
The data used during the execution of a current transaction cannot be used by another transaction until the first one is completed.
Isolation
ensures that once transaction changes are done and committed, they cannot be undone or lost.
Durability
is the equivalent of a single SQL statement in an application program or transaction.
database request
satisfies the constraints specified in the schema.
Database consistent state
A DBMS uses this to keep track of all the transactions that update the database.
Transaction log
guarantees exclusive use of data item to a current transaction.
lock
used to retrieve important and relevant information about data and metadata.
Classification
used to identify data that are like each other. This process helps to understand the differences and similarities between the data
Clustering
Locks the entire database and prevents the use of any tables in the database to transaction T2 while transaction T1 is being executed
database-level lock
The entire table is locked and Prevents access to any row by transaction T2 while transaction T1 is using the table.
table-level lock
Less restrictive. Allows concurrent transactions to access different rows of the same table even when the rows are located on the same page
row-level lock
Has only two states: locked (1) and unlocked (0). If an object such as a database, table, or row is locked by a transaction, no other transaction can use that object.
binary lock
exists when access is reserved specifically for the transaction that locked the object.
shared/exclusive lock
occurs when two (2) transactions wait indefinitely for each other to unlock data.
deadlock
Using the ROLLUP operator, we will display the total number of students enrolled in specific campuses and the grand total of students enrolled in all campuses.
SELECT Program, Campus,
SUM(NumberOfStudents) AS ‘TotalStudents’ FROM Enrolled_Students
GROUP BY ROLLUP (Campus, Program)
Using the CUBE operator, we will display all possible combinations of columns in the Enrolled_Students table
SELECT COALESCE(Program, ‘All Program’) AS ‘Program’,
COALESCE(Campus, ‘All Campus’) AS ‘Campus’,
SUM(NumberOfStudents)
AS ‘TotalStudents’
FROM Enrolled_Students
GROUP BY CUBE (Program, Campus)
Using the PIVOT operator, we will turn the unique values/rows in the Program column into multiple columns.
SELECT ‘Total students in all campus:’ AS ‘Program:’, [BSIT], [BSCS] FROM
( SELECT NumberOfStudents, Program FROM Enrolled_Students )
AS SourceTable PIVOT
(SUM(NumberOfStudents)
FOR Program IN ([BSIT], [BSCS])) AS PivotTable
an extension of the GROUP BY clause that is used to create subtotals and grand totals for a set of columns
ROLLUP operator
Like ROLLUP, this generates subtotals for all the combinations of grouping columns specified in the GROUP BY clause.
CUBE operator
allows you to write a cross-tabulation, which means you can aggregate your results and rotate rows into columns
PIVOT operator
