Standard Statistical Methods for Quantitative Data

Business intelligence has many tools which deal with quantitative data, in the aim making better decision while using these data. Firstly, the data must be collected and gather from any related data sources to the organization. Once, the data collected, it must be mining and analysed , to determine which data going to be useful and which are not to the decision. Then, many business intelligence standard statistical methods can apply for this day.
There are many examples of standard statistical methods, like decision trees, predictive models, sensitivity analysis, and many others. However, not all of them can be used for all type of decisions, it depends about what data you have and what decision you are going to make, you must know these two things, then you can determine which tool will help to make the decision.
For example, a decision tree is an analysis method for structured decisions; appropriate for when actions are to be performed in a sequential order. An advantage is that it can be easily interpreted by a layman. However, a disadvantage is that it cannot be used for all scenarios, especially ones that are more complex.
Sensitivity analysis is a tool that enables a company to engage in forecasting based on historical data. This can be an advantage in that it allows company to predict likely outcomes and prepare for them. However, serious disadvantages include the fact that a sensitivity analysis system is only as good as it is programmed to be. Secondly, if the poor quality data is input, it is likely that the wrong outputs will be given as well.
Another example of statistical methods is the analytic hierarchy process. AHP, Processing allows for decision makers to create criteria whereby all the desired attributes are listed and how desired those attributed are. The all desirable attribute are given a weighting that add up to one, with the most favoured attribute given the biggest value. The advantage of this is that decisions makers can follow logical steps to reach a conclusion. However, a disadvantage is that the desirability of these attributes is very subjective; there is no universal measurement to determine how useful something is. Therefore, attribute A might be very desirable to person A but not desirable to person B.
Therefore, from the above three examples, it’s clear that not all tools can be used for any decision, it depends why do you want to use the tools, and what decision do you want to make. For instance, if you want to make decision based on forecasting the future, you are not going to use a decision tree, and instead you can use a sensitivity analysis, or what if analysis.
In addition, it is important to have clear, related, up to date data to make any decision based on those types of Business intelligence methods. Since if the input was not right input or related to the decision, then the decision is going to be based on this data which the company used. Therefore, they will have result which might cause many problems to them, especially if the company used the tool to predict something in the future, and pay money to prepare for it, and at the end what they were expecting does not happened.

Relational Set Operators

Relational algebra :

Defines theoretical way of manipulating table contents using relational operators

Use of relational algebra operators on existing relations produces new relations

• UNION INTERSECT
• DIFFERENCE PRODUCT
• SELECT PROJECT JOIN DIVIDE

• UNION: combines all rows from two tables, keeping out duplicate rows.
• INTERECT: yields only the rows that appear in both tables.
• DIFFERENCE: yields all rows in one table that are not found in other table.
• PRODUCT: yields all possible pairs of rows from two tables.
• SELECT (RESTRICT): yields values for all rows found in a table that satisfy a given condition.
• PROJECT: yields all value for selected attributes.
• JOIN: allows information to be combined from two or more tables.
• Natural join: links tables by selecting only the rows with common values in their common attributes.
• Left outer join: yields all of the rows in a table, including rows with now matching values from other table.
• DIVIDE: uses one single column as the divisor and one 2 column as the dividend.

Written by: Mohammed Alaslani

Some important Key Terms for attribute and entity

A required attribute: is an attribute that must have a value.
An optional attribute: is an attribute that does not require a value.
Composite attribute: is an attribute that can be subdivided to yield additional attributes.
Simple attribute: is an attribute that cannot be subdivided.
Single-valued-attribute: is an attribute that can only have single value.
multivalued attribute : an attribute can have many values.
A derived attribute: is an attribute whose value is calculated from other attributes.
The entities that participate in a relationship are also known as participants.
Cardinality: expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity.
An entity is said to be existence-dependant if it can exist in the database when it is associated with another related entity occurrence.
If an entity can exist a part from one or more related entities it is said to be existence-independent.
A weak relationship (non-identifying-relationship): exists if the PK of related entity does not contain a PK component of parent entity.
A strong relationship (identifying-relationship): exists if the PK of related entity contains a PK component of parent entity.
Optional participation: means that one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
Mandatory participation: means that one entity occurrence requires a corresponding entity occurrence in a particular relationship.

Written by : Mohammed Alaslani

Type of database

• Single-user database supports only one user at a time
• Multiuser database supports multiple users at the same time
• Workgroup database supports a small number
• Enterprise database supports a large number
• Centralized database: data located at a single site
• Distributed database: data distributed across several different sites
• Operational database: supports a company’s day-to-day operations(Transactional or production database )
• Data warehouse: stores data used for tactical or strategic decisions
• Unstructured data exist in their original state
• Semistructured data have been processed to some extent
• Extensible Markup Language (XML) represents data elements in textual format
• XML database supports semistructur

Databases can be classified according to:

–Number of users

–Database location(s)

–Expected type and extent of use

Written by : Mohammed Alaslani

How to create and insert values into table(SQL)

In this blog i will show how user an create table and insert data into the table he want.

First of all we must use this syntax :

CREATE TABLE (TABLE_NAME)

(

Then after the (

You an declare any attribute you want

So for example lets create the student table:

CREATE TABLE STUDENT

(

STUDENT_NAME VARHAR2(25),

STUDENT_NUMBER NUMBER(10);

)

This the result we will have:

STUDENT_NAME

STUDENT_NUMBER

Now let say we want to add value for that table

so we must use this syntax

INSERT INTO STUDENT VALUES (‘Mohammed Alaslani’ , 12982831);

This the final result will be for the student table

STUDENT_NAME	STUDENT_NUMBER
Mohammed Alaslani	12982831

* The values are example from me you an use whatever you want but the same type you have delared.

Written by: Mohammed Alaslani

Normalization

A normalization i s a process for evaluating and correcting table structure to minimize data redundancy.Where as denormalization is to get lower normal form. iwill be considring on this post 3 types of normalization only which are:

1NF

2NF

3NF

and the others are :

BCNF AND 4NF

second normal form is better than first normal form , and third normal form is better than second normal form.

So if you have bigger normal form it is better than less normal form.

i have created a table using Microsoft word , shows how to get 1NF, 2NF , 3NF:

Written by: Mohammed Alaslani

Cursors in PL/SQL

A Cursors is Pointer to memory location on database server , and DBMS uses to process a SQL query.

An example of cursor

DECLARE

CURSOR student_cur IS

SELECT student_name, student_id FROM student;

– declare record variable that represents a row fetched from the studentt table

student_rec student_cur%ROWTYPE;

BEGIN

– open the explicit cursor and use it to fetch data into student_rec

OPEN student_cur;

LOOP

FETCH student_cur INTO student_rec;

EXIT WHEN student_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(student_rec.student_name || ‘ , ‘ ||   student_rec.student_id);

END LOOP;

END;

Written by: Mohammed Alaslani

Anonymous Block PL SQL

In this blog i will give an example of creating Anonymous Block in SQL :

First of all you have to use the server output on so it can show you the result of your Anonymous Block .

Secondly you can declare any variable you want to use , and then begin work on it , you can use loops if want to , it is up to you what do you want with this Anonymous Block .

And finally you have to close everythig by using the keyword END;

This a simple example of Anonymous Block PL SQL that will print (Hello World).

SET SERVEROUTPUT ON

DECLARE

TXT VARCHAR2(20) = ‘Hello World’;

BEGIN

DBMS_OUTPUT.PUT_LINE(TXT);

END;

The result will be = Hello World

Written by : Mohammed Alaslani

How data base design fits into the overall systems development process

Database Design :
Is the method of producing a detailed data representation of a database.

The database design process is comprised of the next six steps:

Requirements collection and analysis:

The first step in designing a database is to choose what type of data require to be stored in the database, and what applications must be built on top of it, and what operations are most common and subject to presentation requirements.

Conceptual database design:

The aim is to create a simple explanation of the data that very much matches how users and developers imagine of the data.

Choosing a DBMS :

The Purpose of this step is to establish which the best framework for implementing the produced schema is. For example, type of DBMS (relational, network, deductive …). Or, what types of query languages.

Data model mapping(data model mapping) :

That is to alter the generic, DBMS independent conceptual schema in the data model of the selected DBMS.

Physical database design :

To decide the specific storage structures and access paths for the database files.

Database system implementation :

Database system implementation, consists of inflowing data and maintaining the DBMS

In order to have a good database design , usually those six steps must be considered on the design.

Written by: Mohammed Alaslani

PL/SQL user written functions

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s technical extension language for SQL plus the Oracle relational database. PL/SQL’s general syntax resembles that of Ada.

PL/SQL is one of three key programming languages entrenched in the Oracle Database, along with SQL itself and Java.

PL/SQL chains variables, conditions, loops and exceptions. Arrays are also supported, however in a somewhat strange way, involving the use of PL/SQL collections.

This is an example of simple Syntax of PL/SQL

The next picture shows how PL/SQL Runs

PL/SQL Program Blocks :

Header section : No header section for anonymous block

Declaration section : Optional

Execution section : Required

Exception section : Optional

Comment Statements : Using one of two ways — or Enclosed within /* and */

Written by :Mohammed Alaslani

References : Lecture slides and Wikipedia .