BI

A foreign key may have either a null entry, as long as it is not a part of its table's pri-
mary key, or an entry that matches the primary key value in a table to which it is
related. [Every non-null foreign key value must reference an existing_primary key value.)

A customer might not yet have an assigned sales representative lnumberl, but it will be
impossible to have an invalid sales representative [number].

An attribute (or combination of attributes) that uniquely
identifies each row in a table.

A minimal (irreducible) superkey. A superkey that does not
contain a subset of attributes that is itself a superkey

A candidate key selected to uniquely identify all other
attribute values in any given row.
Cannot contain null entries.

An attribute (or combination of attributes) in one table
whose values must either match the
primarv kev in another table or be null.

Connectivity
— Describes the relationship classification
Cardinality
— Expresses minimum and maximum number of entity occurrences
associated with one occurrence of related entity

Existence dependence
— Entity exists in database only when it is associated with
another related entity occurrence
— an entity is existence-dependent if it has a mandatory
foreign key (NOT NULL)
— “EMPLOYEE claims DEPENDENT”
Existence independence (strong or regular entity)
— Entity can exist apart from one or more related entities
— “PART is supplied by VENDOR"

Weak (non-identifying) relationships
- Exists if PK of related entity does not contain PK
component of parent entity
— Depicted by placing a dashed relationship line between
the entities.

Strong (identifying) relationships
- Exists when PK of related entity contains PK component
of parent entity
— Depicted by a solid line between the entities.

 Optional participation
— One entity occurrence does not require corresponding
entity occurrence in particular relationship
— “COURSE may generate CLASS”: at least some courses
do not generate a class.

Mandatory participation
- One entity occurrence requires corresponding entity
occurrence in particular relationship

SELECT INV_NUMBER 
FROM LINE 
GROUP BY INV_NUMBER 
HAVING SUM(LINE_PRICE)>50; 

SELECT  
  inv.CUS_CODE,  
  inv.INV_NUMBER,  
  prod.P_DESCRIPT,  
  lin.LINE_UNITS,  
  lin.LINE_PRICE,  
  lin.LINE_UNITS*lin.LINE_PRICE AS Subtotal 
FROM INVOICE inv 
INNER JOIN LINE lin 
  ON inv.INV_NUMBER = lin.INV_NUMBER 
INNER JOIN PRODUCT prod 
  ON lin.P_CODE=prod.P_CODE 
ORDER BY  
  inv.CUS_CODE,  
  inv.INV_NUMBER; 

SELECT  
   SUM(CUS_BALANCE) AS [TotalBalance] 
  ,MIN(CUS_BALANCE) AS [Minimum Balance] 
  ,MAX(CUS_BALANCE) AS [Maximum Balance]  
  ,AVG(CUS_BALANCE) AS [Average Balance] 
FROM CUSTOMER; 

DECLARE @avg_Balance numeric(28,2); 
BEGIN 
  SET @avg_Balance = (SELECT AVG(CUS_BALANCE) FROM CUSTOMER) 
END 
SELECT CUS_CODE 
FROM CUSTOMER 
WHERE CUS_BALANCE > @avg_Balance; 

SELECT  
  inv.CUS_CODE,  
  inv.INV_DATE 
FROM INVOICE inv 
INNER JOIN LINE lin 
  ON inv.INV_NUMBER = lin.INV_NUMBER 
INNER JOIN PRODUCT prod 
  ON lin.P_CODE=prod.P_CODE 
WHERE  
  prod.P_DESCRIPT='7.25-in. pwr. saw blade' 
  AND inv.INV_DATE > '2008-01-16'; 
 

ALTER TABLE EMPLOYEE 
ALTER COLUMN EMP_AGE numeric(28,2) NOT NULL; 

ALTER TABLE EMPLOYEE 
DROP COLUMN EMP_TITLE 

ALTER TABLE PRODUCT 
ADD CONSTRAINT PK_PRODUCT PRIMARY KEY NONCLUSTERED (P_CODE) 
GO 

ALTER TABLE LINE 
ADD CONSTRAINT PK_LINE PRIMARY KEY NONCLUSTERED (INV_NUMBER, LINE_NUMBER), 
  CONSTRAINT FK_PRODUCT FOREIGN KEY (P_CODE)  
    REFERENCES PRODUCT (P_CODE) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE 
GO 

CREATE TABLE [dbo].[CUSTOMER2]( 
  [CUS_CODE] [int] NOT NULL IDENTITY(1,1), 
  [CUS_LNAME] [varchar](15) NULL, 
  [CUS_FNAME] [varchar](15) NULL, 
  [CUS_INITIAL] [varchar](1) NULL, 
  [CUS_AREACODE] [varchar](3) NULL, 
  [CUS_PHONE] [varchar](8) NULL, 
  [CUS_BALANCE] [real] NULL 
) 

BI is albroad category of technologieslthat
allows for gathering, storing, accessing &
analyzing data to help business users make
better decisions and analyse business
perfomtance through data-driven insight.

BI is albroad category of applications,‘
which include the activities of (1) decision
support systems, (2) query and reportin,
(3) online analytical processing (OLAP), (4)
statistical analysis, forecasting, and data
mining.

Visual analytics is the science of analytical
reasoning facilitated by interactive visual
interfaces. Visual analytics brings together
computer science, information visualization,
cognitive and perceptual sciences,
interactive design, and graphic design.

A decision support database that is maintained
separately from the organization's operational
database
A consistent database source that bring together
information from multiple sources for decision
support queries
Support information processing by providing a solid
platform of consolidated, historical data for
analysis
“A subject-oriented, integrated, time-variant,
and non-volatile collection of data in support of
rnanagement‘s decision-making process” (lnmon,
1993}
Subject-oriented data - the warehouse is
organized around the major subjects of the
enterprise (e.g. customers, products, and sales)
rather than the major application areas (e.g.
customer invoicing, stock control, and product
sales).
Integrated data - the data warehouse integrates
corporate application-oriented data from
different source systems, which often includes
data that is inconsistent. The integrated data
source must be made consistent to present a
unified view of the data to the users.

Time-variant data - data in the warehouse is only
accurate and valid at some point in time or
over some time interval. Time-variance is also
shown in the (1) extended time that the data is
held, (2) the implicit or explicit association of time
with all data, and (3) the fact that the data
represents a series of snapshots.
Non-volatile data - Data in the warehouse is not
normally updated in real-time (RT) but is
refreshed from operational systems on a
regular basis. However. emerging trend is
towards RT or near RT DWs. New data is always
added as a supplement to the database, rather
than a replacement.

A data mart is the access layer of the data
warehouse environment that is used to get data
out to the users.
The data mart is a subset of the data warehouse
and is usually oriented to a specific business
line or team.
Whereas data warehouses have an enterprise-wide
depth, the information in data marts pertains to a
single department.

DWH

Holds multiple subject areas
Holds very detailed information
Works to integrate all date sources
Does not necessarily use a dimensional model but
feeds dimensional models.

Data Marts

Often holds only one subject area (e.g. Finance or Sales)
May hold more summarized data (although many hold full
detail)
Concentrates on integrating information from a given
subject area or set of source systems
ls built focused on a dimensional model using a star
schema.

OLAP

Major task of data warehouse system
Aims at efficient multidimensional processing
of large data volumes
Fast, interactive answers to large aggregate
quenes
OLAP is part of the broader category of business
intelligence, which also encompasses relational
database, report writing and data mining

OLTP

Major task of traditional relational DBMS
Day-to-day operations: purchasing, inventory,
banking, manufacturing, payroll, registration,
accounting, etc.
Aims at reliable and efficient processing of a large
number of transactions and ensuring data
consistency

Case 2: Retail
Behavioural profiling based on consumer
habits from history of past purchases
Tesco mailings of vouchers and coupons
resulting in an increase from 3% to 70%
in rate of coupon redemption
Tesco historical sales, weather data
and using predictive analytics
to forecast sales by product for each store
Tesco was able to save 100 million pounds
in stock that would have otherwise
expired and thus wasted.

Case 4: Security
Decrease crime rate
Examined 13 million crimes
recorded over 80 years
in order to predict
where crimes would occur
in the future
12% decrease in property crime
26% decrease in burglary

Data Problems
/ lack of data
/ problem with data divergence
/ inability to access data
/ noisy and incomplete data fields
/ obscure and complex data formats
/ massive files processing (> 5 GB)
/ “traps” of machine learning“black box
/ ethical and privacy issues

pro

•  Simple data drag-and-drop visualization system is the key highlight  
•  Can visualize one data set or blend multiple data sets together  
•  Can pull this data from a back end system automatically, which saves the 
user time and effort  
•  Can also automatically calculate conversion rates and other things you’d 
manually do in Excel or other reporting systems 

Cons

•  Difficult for advanced analysis and application - working 
around R scripts if you’re looking for in depth analysis  
•  There are issues with the tool being a complete reporting 
solution (e.g. no proper report scheduling) 

Pros

•  Easy to debug code as error messages are usually very comprehensible  
•  SAS language gives a broad range of flexibility and analytics options - 
overall highly comprehensive analytics capabilities  
•  Implemented algorithms thoroughly tested under a wide range of 
conditions  
•  More jobs as SAS is a market leader in analytics industry 

Cons

•  Algorithms used in SAS procedures are not open to public so 
you cannot do research on that  
•  Steep learning curve with SAS programming language  
•  Extreme defragmentation of SAS software - need to purchase 
special SAS Modules and Miners to do Text Mining, and 
Machine Learning, or SAS Visual Analytics for online reporting 
– makes it potentially very expensive and created access gaps  
•  Graphs very ugly by default [thats my subjective impression] 

Pros

•  Qlik Sense is all-in-one analytics / reporting solution  
•  Qlik is a part of a large organization that can use its many tools in 
different departments  
•  Good for large sets of data where there is a need for powerful, 
multifaceted software  
•  Advanced data filtering options, and data manipulation 

Cons

•  Can be difficult to learn and operate when you first get started 
because of its many facets and intricacies  
•  The back end isn’t quite as user-friendly and intuitive as the 
front end  
•  Tracing any errors when the data load crashes is complicated 

pros

•  Free. No setup costs or license fees.  
•  Comprehensive statistical multi-platform tool  
•  Graphics – used by The Economist, Washington Post, Guardian, etc.  
•  Interactive graphics through external packages – support for Google API 
and javascript libraries  
•  Imports data of different formats Works with databases (SQL/NoSQL 
supported)  
•  Can work with large datasets (up to 100 million elements for 4GB RAM)  
•  Add-on packages (Sep’16 ~ 9,100)  
•  Programming language – user can create own “grammar”  
•  Very active community 

Cons

•  No official support, if you have troubles you have to hope for 
the community