ITP 487

Subquery Limitation

The selected data can only come from the top-level table. We cannot use a subquery to obtain data that arise from more that one table.

IN/NOT IN

Creating conditions to list values within the list

Subqueries and joins are used...

to query multiple tables

HAVING

(Condition) The HAVING operator restricts the groups that are presented in the result.

WHERE

(Condition)

Generic Algorithms

"Survival of the fittest". Create initial population of individuals. Evaluate fitness of individuals. Select the individuals. Apply genetic operations: Crossover and Mutation. Repeat until complete.

Evolution Phase/Enabling Tech (1980s)

1. Data Access 2. Online Transaction Processing (Relational Databases)

Evolution Phase/Enabling Tech (1960s)

1. Data Collection 2. Data Processing Applications

Evolution Phase/Enabling Tech (today)

1. Data Mining 2. Advanced Algorithms/Increased computing power

Evolution Phase/Enabling Tech (1990s)

1. Data Warehousing & Decision Support 2. Online Analytical Processing (OLAP)

Data Mining Process

1. Data model needs to be defined 2. Mining data store needs to be filled 3. Mining runs need to be defined 4. The results need to be interpreted 5. Results need to be applied

Less than or equal

<=

Not equal

<>

Greater than or equal

>=

Transitive Dependency

A --> B, B --> C, then A --> C transitively.

Primary Key

A candidate key selected as the primary means of identifying rows in a relation: -There is one and only one primary key per relation -The primary key may be a composite key -The ideal primary key is short, numeric and never changes

Keys

A combination of one or more columns that is used to identify rows in a relation

Regression

A data mining technique used to predict a range of numeric values (also called continuous values), given a particular dataset. For example, regression might be used to predict the cost of a product or service, given other variables. Regression is used across multiple industries for business and marketing planning, financial forecasting, environmental modeling and analysis of trends.

SQL

A data sublanguage for creating and processing database data and metadata. Structured Query Language

Composite Determinant

A determinant of a functional dependency that consists of more than one attribute

Composite Key

A key that consists of two or more columns

Candidate Key

A key that determines all of the other columns in a relation

Neural Networks

A neural network consists of an interconnected group of artificial neurons, and it processes information using a connectionist approach to computation. In most cases a neural network is an adaptive system that changes its structure during a learning phase. Neural networks are used to model complex relationships between inputs and outputs or to find patterns in data.

Data Warehouse

A process and architecture that requires robust planning to implement a platform, which consists of the selection, conversion, transformation, consolidation, integration, cleansing and mapping of data (i.e., recent and historical) from multiple operational data sources to a target DBMS that supports an enterprise's decision-making processes and BI systems

Decision Trees

A structure that includes a root node, branches, and leaf nodes. Each internal node denotes a test on an attribute, each branch denotes the outcome of a test, and each leaf node holds a class label. The topmost node in the tree is the root node.

Relation

A two-dimensional table that has the following characteristics: -Rows contain data about an entity -Columns contain data about attributes of the entity -All entries in a column are of the same kind -Each column has a unique name -Cells of the table hold a single value -The order of the columns is unimportant -The order of the rows is unimportant -No two rows may be identical

Logical Operators

AND, OR, NOT

Ascending values

ASC (default setting)

Steps to create business intelligence

Access data --> analyze data --> derive insights --> draw conclusions --> make decisions

% or *

Any set of one or more characters

Application Server

Application Layer

Special Operators

BETWEEN, NULL, LIKE, IN, EXISTS

Data Warehouse Objective

Become the single source of truth. Single point of access to all enterprise information

Synonyms for field

Column, Attribute

AND/OR

Creating two different conditions

Descending values

DESC

SQL Subcategories

DML, DDL

Value Chain

Data --> Information --> Knowledge --> Wisdom --> Decision

DDL

Data Definition Language Used for creating tables, relationships, and other structures.

Database Server

Data Layer

DML

Data Manipulation Language Used for queries and data modification (We will concentrate on DML for this course!!!)

NULL

Doesn't show up or has a value of 0

__

Exactly one character

E (ETL)

Extract data from individual systems

ETL

Extract, Transform, and Load. Used to standardize data across systems, that allow it to be queried.

In general, place WHERE before _______

GROUP BY

Drivers of Business Intelligence

Globalization Decentralization Smaller margins Faster reaction to market Increasing importance of service

GROUP BY

Group BY is used to group the data based upon a certain characteristic or trait

SQL always places WHERE before ______

HAVING

Data Collection

Hard to access

Knowledge Management

How do you capture and share knowledge within employees of a company

How to make Data Mining model more intelligent

Increasing the amount of factors to incorporate/consider

Join is performed...

Join is performed when data are retrieved from more than one table at a time

DISTINCT

Limits values to unique values (only if they are different from one another)

Review Slide on ER Notation

Line with one dash - one Line with three branches - Many Line with two dashes - one and only one Line with circle and + - Zero or one Line with dash + branches - One or many Line with circle and branches - Zero or many

*

Lists everything

ORDER BY

Lists output in a specific order

L (ETL)

Load all data into the warehouse

Cardinality

Max number of times an instance in one entity can be associated with instances in the related entity

Ordinality

Min number of times an instance in one entity can be associated with instances in the related entity

INNER JOIN

Most common join! Will return all records in the left table (A) that have a matching recording in the right table (B) SELECT "column name" FROM left table a INNER JOIN right table b ON a.key = b.key

Functional Dependency

Occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s) A, B --> C, but A alone can't --> C by itself

Operational Data Source

One or more sources of data that will be used to populate the data warehouse

OLAP

Online Analytical Processing Supports managerial decision making (historical point-in-time snapshots and predictions)

OLTP

Online Transactional Processing Runs the business on a current basis (current representation of the state of business)

Data Warehousing Components

Operational data source Data conversion and extraction (ETL) Datawarehouse DBMS Datawarehouse administration BI tools

Client

Presentation Layer

Three-Tier Architecture

Presentation layer, application layer, data layer

Data Conversion and extraction

Provide the capabilities to perform the complex task of integrating data from multiple sources to create a consolidated view of the data, as well as the transformation of data for use by BI applications.

BI Tools

Queries Reports Graphs and charts Dashboard and cockpits

Words are put into...

Quotes (but numbers aren't)

Synonym for row

Record

Regression v. Classification

Regression is used to predict a numeric or continuous value while classification assigns data into discrete categories.

Data Mining Techniques

Regression, Decision Trees, Neural Networks, Association Analysis, Clustering/Classification, Generic Algorithms

Synonym for table

Relation

Natural JOIN

Returns all rows that have matching values in matching columns (eliminates duplicates). Used when tables share one or more common attributes with common names

JOIN USING

Returns only rows with matching values in the column indicated in the USING clause

RIGHT OUTER JOINS

Returns rows matching join condition Returns rows in right side table with unmatched values

LEFT OUTER JOINS

Returns rows matching the join condition Returns rows in left side table with unmatched values

Data Access Platforms

SAP, Oracle Summarizing sales, adding sales

Order of Syntax in statement

SELECT columnlist FROM tablelist [WHERE conditionlist] [GROUP BY columnlist] [HAVING conditionlist] [ORDER BY columnlist [ASC | DESC] ] ;

RIGHT JOIN

Same as left join, but will show all values regardless from (B) but will only have matching values from (A) SELECT "column name" FROM left table a RIGHT JOIN right table b ON a.key = b.key

LIKE

Search (similar to)

Data Warehouse/Decision Support

Slice and dice data, mobility to analyze by dimension

Entity

Some identifiable thing that users want to track (customers, products, sales orders)

Determinant

The attribute on the left side of the functional dependency. The determinant is the attribute that provides information about the attributes in the reset of the row

DBMS

System software for creating and managing databases

Self JOIN

Table is joined within itself

Datawarehouse DBMS

The RDBMS vendors (e.g., Oracle, Teradata, and Microsoft) have significantly increased the amount of research and development to improve support for data warehousing

Foreign Key

The primary key of one relation that is placed in another relation to form a link between the relations: -A foreign key can be a single column or a composite key -The term refers to the fact that key values are foreign to the relation in which they appear as foreign key values

Data Mining

The process of discovering meaningful correlations, patterns, and trends by sifting through large amounts of data stored in repositories.

Association Analysis

To analyze the strength and correction of relationships. Example: likelihood to survive Titanic sinking based on sex, age, and class.

T (ETL)

Transform all data into the same format

SELECT

Used to retrieve data from tables, generating information

Business Intelligence

User-centered process of exploring data, data relationships and trends - thereby helping to improve overall decision making for enterprises.

FULL JOIN

Will return all records from both tables, joining records from (A) that match from (B)

LEFT JOIN

Will return all records in the left table (A) no matter what, but will also return any values that have matching records from the right table (B) SELECT "column name" FROM left table a LEFT JOIN right table b ON a.key = b.key

Datawarehouse Administration

With the increasing number of subject areas and the increasing amount of historical data (i.e., more than five years) typically maintained, a data warehouse requires significant amounts of disk storage, memory and processing power. Someone makes sure the processes run every night

Alias

You add it to identify table source without typing out the entire thing. Add alias after table name in FROM clause

End SQL statements with a...

semi-colon (;)