Database Test 3

Front 1

What are the uses of a stored procedure?

Back 1

Allows code to be reused.

Front 2

How is a stored procedure created?

Back 2

Delimiter //

Create Procedure prName (in var int, out var int)

Begin

[statements go here];

End //

Delimiter ;

Front 3

How is a stored procedure called?

Back 3

Call prName (inVar, @outVar);

select @outVar;

Front 4

What is a user defined variable?

Back 4

A variable created by the user. Available anywhere in MySQL

Syntax:

Set @userVar := 2;

Front 5

What is a local variable?

Back 5

Variables used within stored programs. Must be declared at the beginning of the stored program and are only available in the program they were defined in.

Syntax:

Declare localVar int;

Set localVar = 2;

or

select SOME_VALUE into localVar

from someTable

where something = somethingElse

Front 6

How do you use an IF statement?

Back 6

if (2 < 3) then

[do something];

elseif (3 > 2)

[do something else];

else

[do this];

end if;

Front 7

How do you use a while loop?

Back 7

While a > 1 do

[do something];

end while;

Front 8

What are the uses of triggers?

Back 8

Triggers can protect the integrity of the database by checking data before and after insertions, updates and deletions.

Front 9

How is a trigger created?

Back 9

Delimiter //

Create Trigger trDoSomething [Before or after] [delete, insert or update] on someTable

for each row

begin

[do something];

end //

delimiter ;

Front 10

How do you use old/new in triggers and how?

Back 10

new.some_value is the value of the thing being updated or inserted. Old.some_value is that of the thing being replaced or deleted.

Front 11

What are the uses and advantages of views?

Back 11

Views are like lenses or masks that allow only certain information to be viewed from tables. This helps keep information secure. The logical virtual table created in a view does not actually store the same data from the original table again.

Front 12

How are Views created?

Back 12

Create view viewName as select [query]

Front 13

What are the criteria for updateable views?

Back 13

- must not reference more than one table, so no joins

- must not use group by or having

- must not use distinct

- must not reference a view that cannot be updated

- must not contain any expression (functions, computed columns)

Front 14

What is a transaction and what are they for?

Back 14

A set of tasks that are grouped into a single execution unit. If one of the tasks fails the entire transaction fails. Only changes the database data from one consistent state to another.

Front 15

What does ACID stand for?

Back 15

Atomicity - all or nothing

Consistency - One consistent state to another

Isolation - Transactions are independent from each other

Durability - The transactions results are permanently stored.

Front 16

What is Serializability in transactions?

Back 16

Applies to multi-user databases only. Ensures that multiple transactions are executed one after another.

Front 17

What is Commit and Rollback?

Back 17

Commit stores all changes permanently in the database and automatically ends a SQL transaction.

Rollback aborts all modifications and the database is restored to its previous consistent state.

Front 18

What is a Transaction Log?

Back 18

Used to track all transactions that update the database so that it is possible to restore it when a rollback statement is encountered or something goes wrong with the system.

Front 19

How do you use a multi-statement transaction?

Back 19

Start Transaction;

[update stuff];

[update more stuff];

[Rollback or Commit];

Front 20

What are savepoints?

Back 20

Intermediate points in a transaction where the current state is saved.

Syntax: Savepoint savePointName;

Rollback to savepoint savePointName;

Front 21

What is Concurrency control and how is it achieved?

Back 21

Ensuring the information in the database remains consistent by preventing transactions that access the same data from happening at the same time. Locking is how this is achieved.

Front 22

What are three potential consistency problems?

Back 22

1. Lost updates - when the results of one update are overwritten by another

2. Uncommitted data - when two transactions are executed concurrently and the first transaction is rolled back after transaction 2 has already accessed the uncommitted data.

3. Inconsistent Retrievals - when a transaction calculates some summary, or aggregate, functions over a set of data while other transaction are updating the data at the same time.

Front 23

What are the locking methods?

Back 23

Database Level

Table Level

Page Level

Row Level

Field Level

Front 24

What are the lock types?

Back 24

Binary Locks - it's either locked or unlocked, when locked there is no accessing it at all.

Shared/Exclusive Locks - shared locks allow read access and exclusive locks prevent two transactions from writing to the same data

Front 25

What is a deadlock?

Back 25

When two parts of data are locked and two transactions need to write to the other's data at the same time.

Front 26

What is business intelligence?

Back 26

Using data to make smart business decisions. Data to information, information to knowledge and knowledge to wisdom.

Front 27

What is the business intelligence framework?

Back 27

Operational Data to ETL to a data warehouse and mart to query & reporting to data analytics, monitoring and alerting and data visualization.

Front 28

What are the tools of business intelligence?

Back 28

Dashboards, portals, data-mining, data warehouses, OLAP

Front 29

What is a dashboard?

Back 29

Shows key business performance indicators in a single view.

Front 30

What is a Portal?

Back 30

integrate data using web browser from multiple sources into a single webpage

Front 31

What are the benefits of business intelligence?

Back 31

- Improved decision making

- integrating architecture

- common user interface for data reporting and analysis

- common data repository fosters single version of company data

- improved organizational performance

Front 32

What are the differences between an operational database and a data warehouse?

Back 32

amount of data stored, timeline of data stored, query based vs. update based, granularity

Front 33

What is a data mart?

Back 33

- Small single-subject data warehouse subset

- provides decision support to a small group of people

- lower cost and shorter implementation time

- technologically advanced

- inevitable people issues

Front 34

What is ETL and what is the ETL process?

Back 34

ETL: Extraction, Transformation, Loading is when data is taken from the operational database, normalized and then placed in the data warehouse or data mart.

Front 35

What is a Star Schema?

Back 35

- Data modeling technique

- maps multidimensional decision support data into a relational database

- creates the near equivalent of multidimensional database schema from existing relational database

- yields an easily implemented model for multidimensional data analysis

Front 36

What are the components of a star schema?

Back 36

- numeric values that represent a specific business aspect

- qualifying charactaristics that provide additional perspectives to a given fact

- used to search, filter and classify facts

- slice and dice: ability to focus on slices of data cube for more detailed analysis

- provides a top down data organization: drill-down/roll-up analysis

-e.g., region, state, city, store

Front 37

How are star schema represented?

Back 37

- facts and dimensions represented by physical tables in data warehouse database

- many to one relationship between fact table and each dimension table

- fact and dimension tables related by foreign keys and subject to primary and foreign key constraints

Front 38

What is the difference between a fact table and dimension tables?

Back 38

A fact table contains factual or quantitative data. A dimension table contains descriptions about the subjects of business and is denormalized.

Front 39

What does the size of the fact table depend on?

Back 39

The number of dimensions. The number of rows = product of number of possible values for each dimension associated with the fact table

Front 40

What is data analytics?

Back 40

Encompasses a wide range of mathematical, statistical and modeling techniques to extract knowledge from data.

Front 41

What is explanatory analytics?

Back 41

Focuses on discovering and explaining data characteristics and relationships based on existing data

Front 42

What is predictive analytics?

Back 42

Focuses on predicting future outcomes with a high degree of accuracy.

Front 43

What is data mining?

Back 43

Knowledge discovery using a blend of statistical, AI, and computer graphics techniques.

Front 44

What is the goal of data mining?

Back 44

To find hidden patterns, relationships in large databases and infers rules to predict future behavior

Front 45

What are the five types of information obtainable from data mining?

Back 45

- Associations

- Sequences

- Classification

- Clustering

- Forecasting

Front 46

What is OLAP?

Back 46

Online Analytical Processing. Uses the cube structure to view data using multiple dimensions

Front 47

What is cube slicing?

Back 47

Coming up with a 2-D view of data

Front 48

What is drill-down?

Back 48

going from summary to more detailed views