MIS 325 Exam #3

Is NoSQL faster or slower queries ?

Faster

Cons of Triggers

Invisible from client applications Impose load on server Not reccomended for high velocity of data

What does OLTP stand for?

Online Transactional Processing

Until the mid 1980'2 Enterprise datbases stored only ___________ and what is this?

Operational Data - data created by business operation involved in daily management processes such as purchase management and invoicing

An OLTP query works as ....

Operational query (operational databases)

What are the 5 object privilges?

SELECT UPDATE INSERT DELETE EXECUTE

What does it mean when people say queries are not known in OLAP

You can search by anything .... youre joining all fields so the querie can be anything you think of

What does it mean when people say NoSQL is schemaless

You dont have to define fields in that section You dont decide what is required

Whats the con of NoSQL in terms of horizontal scaling

There could be multiple version of the database on different servers so the syncing can be lagged by a little but and therefore can make it lack consistency... Resulting in transactional uncertainty

WHat are subjects?

They are users with roles

Online Transaction Processing (OLTP)

High performances Large number of concurrent users Queries are known Data domains are limited Create, read, update and delete data More interested in current data rather than historical data

Whats the problem with vertical scalability?

If you want that single server to have more power you have to add more processing speed and more hardware, more storage and more RAM

Attributes of classes can have complex data types (i.e. including other classes) creating ...

Immense flexibility

Main advantage of Three Layer Architecture

It creates a common reference data model for a whole enterprise and sharply seperate the problems of course data extraction/integration from those of data warehouse population

Although a view behaves like a virtual table...

It does not store any data. Always refers back to the base tables

When you update a 'view' what does that do to the base table?

It updates it as well

Wide Column NoSQL

Key Value but adds familys of values... slightly more structred than key value pairs

How do applications connect to servers like people?

Log in, send SQL queries, resulst returned, read w/ code

Built on a multi dimentional database

MOLAP

What two parts does a star diagram create?

Measures and Dimensions

Social Network

Model nodes as people or groups and links show relationhips or flows among nodes

Basic Mongo DB facts

No Schemas No Joins No transactions

Problem with RDBMS: Change management complexity... solution?

NoSQL, Schema-less database Key-value pairs

What is a measure?

Numeric values on which math functions work (Quant sold, Sale total) quantitative

Updates: Operational Databases vs Data Warehouses

O - Continuous W - Periodical

What are triggers design purpose?

To be the relational database response to active databases

The goal of encapsulation

To ensure that the structure of an object is completely hidden from external view and access to the object is restircted to the methods defined for the class

Star Diagram Method: why was it created?

To provide the needs of a merged, flexible, multidimentional OLAP model Within the structured, 2 dimensional RDBMS format

What are triggers commonly used for?

To store information about events that occur in a database so these events can later be reviewed - PL/SQL

In trigger syntax what are the INSERT UPDATE and DELETE statements in the body called?

Trigger Predicates

OOD: If the class structure is restricted to a tree...

We have single inheritance

Why would you use a data mart?

When a company wide data warehouse can become too big and complicated

When does ETL take place?

When a data warehouse is populated for the first time, then it occure every time the data warehouse is regularly updated

Arhitecture properties essential for a data warehouse system:

Separation Scalability Extensibility Secuirty Administirability

What is three layer architecture doing?

Seperating out single layer of enterprise systm and extracing them so we can move analytics into its own single server so it doesnt affect the traffic of the other systems

Data warehousing

The collection of methods, techniques and tools use to support knowledge workers to conduct data analyses that help with performing decision-making processes

Every object and method must belong to ....

The same class

OOD: If the class structure can be an acyclic directed graph

Then the system supports multiple inheritance. Allows for a very flexible model

What does OLAP stand for?

online analytical processing

What does it mean that data domain is limited in OLTP?

OLTP's focue on nices of certain data... CRM's LMS's

Object-oriented programming has become the norm but not ____

OODB's

OLAP operation: Pivot

Rotates the cube to show a partifulcar face

Whats a trigger called without a FOR EACH ROW ... Only executed once for each statement.

statement-level trigger

Transformation

the core of the reconciliation phase - it converts data from its operational source format into a specific data warehouse format

Pros of Normalization

- reduced redundant data that leads to increased data integrity - increase the number of questions that can be asked - Segment sensitive data - Grant granular permission by table

A date warehouse is..

- subject oriented - integrated - historical - non-volatile helps suport management's decision making

Authorization Policy Concepts

1. Access limitations 2. Closed/Open systems 3. Authorization Management (centralized/decentralized) 4. Access control (mandatory/discretionary)

Pros of denormalization

Performance - data is combined into one table so that reading and updating the data does not require join Analysis - Data is combined in one table so that it can be analyzed withot knowledge of the database structure

What does Amazon's RDS stand for

Relational database server

Extraction

Relevant data is obtained from sources bases on its quality

Biological Networks

Represents biological information whose volume, management and analysis has become an issue due to the automation of the process of data gathering

When you 'GRANT ALL' to a user or role, what all does that give them?

SELECT INSERT UPDATE DELETE FLASHBACK DEBUG

When you work w/ system proveleges, granting a CREATE privelege often implicitly grants ....

The corresponding ALTER and DROP privelege

Star Diagram description

The multidimensional modeling in a relational database... the tangible concept of the 3d cube

Whats the asset most compromized in data security?

The server

What tool is similar to SQL Developer but used to connect to a MySQL DBMS versus an Oracle DBMS?

Workbench

Why are day to day updates more costly in NoSQL?

They require more effort and cost to maintain the data once its updated

What is NewSQL

a class of RDMS that seeks to provide the scalability of NoSQL systems for online transaction processing(OLTP) while maining the reliability and consistency guarantees of a traditional database system

Cons of normalization

- More difficult for business to understand - Queries require joins - Read performance impacted as you join more tables

What is a view?

A select statement that stored as a datbase object... you can think of it as a virtual table that consists only of the rows and columns specified from the view

What is youtube an example of?

A text or document database

How does a web-based system work?

A web browser on a client sends a request to a web server over the internet. Then, the web server processes the request and passes any request for data on to the database server

Essential requirements for a data warehouse process

Accessibility Integration Query Flexibility Information conciseness Multidimensional Representation Correctedd and Completeness

System Privileges

Allow the user to connect to a database and create, alter, or drop objects such as tables, sequences, views and stores procedures

Object Priveleges

Allows a user to use database objects such as tables, views, sequences, and store procedures. The privileges that are available for an object depend on the type of object

What are the tables in the FROM clause of a view called?

Base tables

Why doesnt OLAP need as much speed?

Because there are not as many concurrent users

The measures table in a star diagram almost serves as a...

Big linking table

When you think data warehouse, think

Business Decision Making

Application servers are typically used to store what?

Business components that do part of the processing of the applications

How is a view defined?

By using a SELECT statement

DDL statements

CREATE ALTER DROP

The three system privileges

CREATE ALTER DROP

Polymorhic Code

Classes can inherit code Code can apply to different types and subclasses (bc structure is inherited)

Windows based system (drawn out)

Client(user interface) <--> Application Server <--> Database server

Web-based system (drawn out)

Client(web browser) <--> internet <--> Web Server <--> Database server

Problem with RDBMS: Scalability costs... solution?

Cloud computing

Advantages to ORM tools

Code reuse and Maintainability

Whats the mongoDB equivalent of the word 'Tables'

Collections

HOLAP

Combo of ROLAP and MOLAP

What is the 1st authorization a user is granted?

Connecting to a database

Pros of Triggers

Consistency of Data Useful for catching errors Auditing Alternative way to run scheduled tasks

What must you always balance when designing data security

Convenience and Security

C.R.U.D.

Create, Read, Update, Delete

Problem with RDBMS: Business Analytics... solution?

Data Warehousing Relational Online Analytical Processing

The 4 benefits of using views

Data security Design independence Simplified queries Updatability

Whats the mongoDB equivalent of the word 'database'

Database

If youre collecting data from portions of two different dimensions

Dice

mongoDB is known as a ___________________ type of NoSQL database

Document Store

Whats the mongoDB equivalent of the word 'Rows'

Documents

Most frequent data mistakes and inconsistencies

Duplicate data Incosistent values that are logically associated Missing data Unexpected use of fields Impossible or wrong values Incosistent values

NoSQL databsses get rid of the need to join data which leads to...

better query performance BUT costly updates on duplicated data

Data mart

contains a subset of data warehouse information that is focused on the needs of one department with selected subjects

Object-oriented databases relate data through a/an ________ relationship

is a

Authentication

is a process that validates the identity of the user in order to permit access to database management system (i.e. user exists)

Three Layer Architecture

"best practice" - ETL is happening in two steps - Advantage of this approcah is that issues with metadata can be easily diagnosed - Reconciled data can be used for some operational processes

Cons of Denormalization

- Redundant data impacts data quality and integrity - Higher data storage costs - More difficult to govern and control data semantics bc all data is in one place instead of broken out by entities - Analyzing data company-wide is overhwhelming

In a client-server arechitecture, what kind of additional servers may be included?

Application Servers and Web Servers

One caveat dealing with authentication and authorization

Authorization can only be performed after the authentication of a user is complete

NoSQL document structure

Extension of key-value model where each value is a document Mongo, Amazon

What does ETL stand for?

Extract, Transform, Load

Text or Document Database

are used to store the full-text of documents with the purpose of aiding intelligent retrieval

Authorization

is a process that determines whether the user is permitted to perform the requested function, these represent the privileges and rights that have been granted to the user (i.e. user granted privs)

Acive Database Management System

monitors events in the environment and with SQL executing against the database to automatically initiate actions on the database or on the environment

OLAP operation: Drill down, Roll down

Go from higher level summary to lower level detailed data (seeing detailed sales for a single product or city)

What is the most often technique used for compromising servers and other forms of data security?

Hacking

Information networks

Model relations representing information flow (the internet)

Whats a reason to use After Triggers

- So that the next time a problem occurs you can review the data in the audit table to identify the cause of the problem - So you can check the type of DML statement that caused the trigger to fire

what is ETL?

-Extracting data from mulitple operational sources - Validating, selecting, cleaning, and aligning the data to the standardizes Data Warehouse data model (transform) - Designing and implementing the process for loading the data into the data warehouse on a regular schedule

Key Value Pairs

1 key(filename, URI..) has a value chained to it which contains data

Technological Networks

Model situations where the spatial and geographical aspects of the structure are dominant

NoSQL avoids separating entities and puts all related data in a single store location which facilitates the benefit of ________ but also contributes to the disadvantage of ______

Faster Queries Making data updates costly

OLAP query

Features dynamic, multidimensional analyses that needs to scan a huge amount of records to process a set of numeric data sumpping up the performance of an enterprise

Two examples of use cases for active database management?

Fraud Protection Amazon orders - to see if products are in stock

To do analysis, OLTP results in

Siloed applications and Data which can be time consuming and expensive Data inconsistencies data is exhausting not fueling

Selecting from portions of one dimension

Slice

The 4 Types of Networks

Social Information Technological Biological

Subclasses inherit what

Structure and Methods..... BUt can also have their own

OLAP operation: Roll Up

Summarizes data total sales by year by product by region

ROLL UP

Summarizing multiple dimensions

Cleansing phase

Supposed to improve data quality which is normally quite poor in sources

T of F: Authentication does not allow a user to connect to a database.

T

Data Warehousing definition

The concept of moving all the sources of data into a single location... Centralized repository

What layer hosts the ETL process?

The data staging layer

Authentication Best Practices

1. User creation policy should be documented 2. Convenience needs to be balanced with security risks 3. User access should only be what is needed 4. Password polucy should be documented - Password rules and resets 5. Multifactor authentication with senesitive or elevated privilges

Time coverage: Operational Databases vs Data Warehouses

operational is current data only warehouses are current and historical data

Whats a trigger called with a FOR EACH ROW clause... will fire for each row that is modified.

row-level trigger

OLAP operation: Slice and Dice

Select a whole dimension or combo of 2 (sales of a single product across all time and geographic dimensions)

ROLAP

OLAP model on a relational database via the star diagram

What type of database was designed to more clearly accommodate the way we, as humans, think?

Object Oriented

Problem with RDBMS: Real-world modeling challenges... solution?

Object oriented datbases Demantic databases

In relational databases, they have entities defined as tables... what about in object-oriented databases?

Objects that are defined by classes

The interaction between priveleges roles and users

Priveleges are assigned to roles and roles are assigned to users

Business Inteligence (BI)

Provide reporting, sophisticated data modeling, and analysis for organizational decision making from data in the warehouse

What does this do: Classes have methods to model behavior

Provides encapsulation

What is a dimension?

Qualitative values on which functions do not work (name, city, data) Who what when where categorical

What does OLAP need

Queries are NOT known Focused on historical data Read only Performance is not as much of a concern Non-normalization

An OLAP query works as..

Query for data warehouses

Built on a relational database which is not multi dimenstional

ROLAP

How do we structure data warehousing?

ROLAP via star diagram

ETL process operation as a whole are often defined as ...

Reconciliation

Two ways of loading in a data warehouse

Refresh - completely rewritten. Older data types replaced Update - only those changes applied to source data are added to the data warehouse

What are the three tablespaces that are created when Oracle is installed

SYSTEM USERS TEMP