Midterm

Question 1

a database designed to enable and support business intelligence (BI) activities, especially analytics.

Answer 1

data warehouse

Question 2

The data warehouse (DW) can analyze data about a particular subject or functional area

Answer 2

Subject-Oriented

Question 3

The data warehouse (DW) creates consistency among different data types from different sources.

Answer 3

Integrated

Question 4

Data in Data warehouse (DW) represents the flow of data through time. It can be organized weekly, monthly, or annually, etc.

Answer 4

Time-variant

Question 5

Once data is in a data warehouse, it is stable and
does not change.

Answer 5

Non-volatile

Question 6

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

Answer 6

Benefits of a Data Warehouse

Question 7

Provides consistent information on various cross-functional activities. It is also supporting ad-hoc reporting and query.

Answer 7

Benefits of a Data Warehouse

Question 8

Helps to integrate many sources of data to reduce stress on the production system

Answer 8

Benefits of a Data Warehouse

Question 9

Helps to reduce total turnaround time for analysis and reporting

Answer 9

Benefits of a Data Warehouse

Question 10

Restructuring and integration make it easier for the user to use for reporting and analysis.

Answer 10

Benefits of a Data Warehouse

Question 11

Stores a large amount of historical data. This helps users to analyze different time periods and trends to make future predictions.

Answer 11

Benefits of a Data Warehouse

Question 12

a collection of operations that form a single logical unit of work.

Answer 12

transaction

Question 13

requires that all operations (SQL requests) of a transaction should be completed.

Answer 13

Atomicity

Question 14

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

Answer 14

Consistency

Question 15

The data used during the execution of a current transaction cannot be used by another transaction until the first one is completed.

Answer 15

Isolation

Question 16

ensures that once transaction changes are done and committed, they cannot be undone or lost.

Answer 16

Durability

Question 17

is the equivalent of a single SQL statement in an application program or transaction.

Answer 17

database request

Question 18

satisfies the constraints specified in the schema.

Answer 18

Database consistent state

Question 19

A DBMS uses this to keep track of all the transactions that update the database.

Answer 19

Transaction log

Question 20

guarantees exclusive use of data item to a current transaction.

Answer 20

lock

Question 21

used to retrieve important and relevant information about data and metadata.

Answer 21

Classification

Question 22

used to identify data that are like each other. This process helps to understand the differences and similarities between the data

Answer 22

Clustering

Question 23

Locks the entire database and prevents the use of any tables in the database to transaction T2 while transaction T1 is being executed

Answer 23

database-level lock

Question 24

The entire table is locked and Prevents access to any row by transaction T2 while transaction T1 is using the table.

Answer 24

table-level lock

Question 25

Less restrictive. Allows concurrent transactions to access different rows of the same table even when the rows are located on the same page

Answer 25

row-level lock

Question 26

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.

Answer 26

binary lock

Question 27

exists when access is reserved specifically for the transaction that locked the object.

Answer 27

shared/exclusive lock

Question 28

occurs when two (2) transactions wait indefinitely for each other to unlock data.

Answer 28

deadlock

Question 29

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.

Answer 29

SELECT Program, Campus, SUM(NumberOfStudents) AS 'TotalStudents' FROM Enrolled_Students GROUP BY ROLLUP (Campus, Program)

Question 30

Using the CUBE operator, we will display all possible combinations of columns in the Enrolled_Students table

Answer 30

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)

Question 31

Using the PIVOT operator, we will turn the unique values/rows in the Program column into multiple columns.

Answer 31

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

Question 32

an extension of the GROUP BY clause that is used to create subtotals and grand totals for a set of columns

Answer 32

ROLLUP operator

Question 33

Like ROLLUP, this generates subtotals for all the combinations of grouping columns specified in the GROUP BY clause.

Answer 33

CUBE operator

Question 34

allows you to write a cross-tabulation, which means you can aggregate your results and rotate rows into columns

Answer 34

PIVOT operator