Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
48 Cards in this Set
- Front
- Back
What are the uses of a stored procedure? |
Allows code to be reused. |
|
How is a stored procedure created? |
Delimiter // Create Procedure prName (in var int, out var int) Begin [statements go here]; End // Delimiter ; |
|
How is a stored procedure called? |
Call prName (inVar, @outVar); select @outVar; |
|
What is a user defined variable? |
A variable created by the user. Available anywhere in MySQL Syntax: Set @userVar := 2; |
|
What is a local variable? |
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 |
|
How do you use an IF statement? |
if (2 < 3) then [do something]; elseif (3 > 2) [do something else]; else [do this]; end if; |
|
How do you use a while loop? |
While a > 1 do [do something]; end while; |
|
What are the uses of triggers? |
Triggers can protect the integrity of the database by checking data before and after insertions, updates and deletions. |
|
How is a trigger created? |
Delimiter // Create Trigger trDoSomething [Before or after] [delete, insert or update] on someTable for each row begin [do something]; end // delimiter ; |
|
How do you use old/new in triggers and how? |
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. |
|
What are the uses and advantages of views? |
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. |
|
How are Views created? |
Create view viewName as select [query] |
|
What are the criteria for updateable views? |
- 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) |
|
What is a transaction and what are they for? |
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. |
|
What does ACID stand for? |
Atomicity - all or nothing Consistency - One consistent state to another Isolation - Transactions are independent from each other Durability - The transactions results are permanently stored. |
|
What is Serializability in transactions? |
Applies to multi-user databases only. Ensures that multiple transactions are executed one after another. |
|
What is Commit and Rollback? |
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. |
|
What is a Transaction Log? |
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. |
|
How do you use a multi-statement transaction? |
Start Transaction; [update stuff]; [update more stuff]; [Rollback or Commit]; |
|
What are savepoints? |
Intermediate points in a transaction where the current state is saved. Syntax: Savepoint savePointName; Rollback to savepoint savePointName; |
|
What is Concurrency control and how is it achieved? |
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. |
|
What are three potential consistency problems? |
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. |
|
What are the locking methods? |
Database Level Table Level Page Level Row Level Field Level |
|
What are the lock types? |
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 |
|
What is a deadlock? |
When two parts of data are locked and two transactions need to write to the other's data at the same time. |
|
What is business intelligence? |
Using data to make smart business decisions. Data to information, information to knowledge and knowledge to wisdom. |
|
What is the business intelligence framework? |
Operational Data to ETL to a data warehouse and mart to query & reporting to data analytics, monitoring and alerting and data visualization. |
|
What are the tools of business intelligence? |
Dashboards, portals, data-mining, data warehouses, OLAP |
|
What is a dashboard? |
Shows key business performance indicators in a single view. |
|
What is a Portal? |
integrate data using web browser from multiple sources into a single webpage |
|
What are the benefits of business intelligence? |
- 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 |
|
What are the differences between an operational database and a data warehouse? |
amount of data stored, timeline of data stored, query based vs. update based, granularity |
|
What is a data mart? |
- 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 |
|
What is ETL and what is the ETL process? |
ETL: Extraction, Transformation, Loading is when data is taken from the operational database, normalized and then placed in the data warehouse or data mart. |
|
What is a Star Schema? |
- 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 |
|
What are the components of a star schema? |
- 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 |
|
How are star schema represented? |
- 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 |
|
What is the difference between a fact table and dimension tables? |
A fact table contains factual or quantitative data. A dimension table contains descriptions about the subjects of business and is denormalized. |
|
What does the size of the fact table depend on? |
The number of dimensions. The number of rows = product of number of possible values for each dimension associated with the fact table |
|
What is data analytics? |
Encompasses a wide range of mathematical, statistical and modeling techniques to extract knowledge from data. |
|
What is explanatory analytics? |
Focuses on discovering and explaining data characteristics and relationships based on existing data |
|
What is predictive analytics? |
Focuses on predicting future outcomes with a high degree of accuracy. |
|
What is data mining? |
Knowledge discovery using a blend of statistical, AI, and computer graphics techniques. |
|
What is the goal of data mining? |
To find hidden patterns, relationships in large databases and infers rules to predict future behavior |
|
What are the five types of information obtainable from data mining? |
- Associations - Sequences - Classification - Clustering - Forecasting |
|
What is OLAP? |
Online Analytical Processing. Uses the cube structure to view data using multiple dimensions |
|
What is cube slicing? |
Coming up with a 2-D view of data |
|
What is drill-down? |
going from summary to more detailed views |