What is Data

What is Data?


Datum is the singular form of "data". And refer to a standard position or level that measurements are taken from.

Data is a plural of datum, a single piece of information; data is used in singular and plural form of word. Data is often used for binary machine readable information

Data can be found in a variety of forms, as numbers or text on papers and as bits and bytes in electronic devices or facts stored in person’s mind.

Data can be divided into two types:

Qualitative Data : The term qualitative is used to describe certain types of descriptive information . Data can be observed but not measured. e.g. Colors, textures, smells, tastes, appearance, beauty, etc

Quantitative Data: The term quantitaive is used in which items are described in terms of quantity and in which a range of numerical values are used without implying that a particular numerical value refers to a particular distinct category. Data which can be measured. e.g. Length, height, area, volume, weight, speed, time, temperature, humidity, sound levels, cost, members, ages, etc.

Quantitative data can also divided into two types

Discrete Data: A type of data is discrete if there are only a finite number of values possible or if there is a space on the number line between each 2 possible values. It can only take whole numbers, which can only be counted.

Continuous Data: This is a type of data that is usually associated with some sort of physical measurement. Can take values in range, and is measured. One general way to tell if data is continuous is to ask yourself if it is possible for the data to take on values that are fractions or decimals. If your answer is yes, this is usually continuous data

Which data type to use for boolean columns

Always use number data type for boolean columns.

How to manage history data

In previous article ‘Tips to create table’, we create a table ‘EMP’. Purpose of this table was to store employee’s basic information.

Today’s tip will cover the functionality how to manage data that will be changed in future. If Employee’s department number changed from 10 to 20, no one knows this employee is transferred from department number 10.

To get this problem solved we will use another table called ‘EMP_DEPT_ASSIGN’

Script:

Create table EMP_DEPT_ASSIGN (
Emp_dept_assign_id number,
Emp_id number, -- Foreigh Key Column, master is EMP
Dept_id number, -- Foreigh Key Column, master id DEPT
Start_date date,
End_date date,
Created_by number,
Creation_date date default sysdate,
Last_updated_by number,
Updation_date date,
Enabled number(1) /*default value ‘1’*/ );

Create sequence SQ_EMP_DEPT_ASSIGN;
Create Or Replace Trigger TBI_EMP_DEPT_ASSIGN
Before insert on EMP_DEPT_ASSIGN
For each row
Begin
If :new.Emp_dept_assign_id is null then
Select sq_Emp_dept_assign.nextval into :new.Emp_dept_assign_id
from dual;
End if;
End;
/

Insert into Emp_dept_assign(
Emp_dept_assign_id,
emp_id,
dept_id,
start_date,
end_date,
created_by
,creation_date
,Enabled) Values
(null,
1,
10,
’01-SEP-11’,
null,
100, /* User id*/
Sysdate,
1);

Update Emp_dept_assign set end_date=sysdate, enabled=0
where emp_id=1;

Insert into Emp_dept_assign(
Emp_dept_assign_id,
emp_id,
dept_id,
start_date,
end_date,
created_by
,creation_date
,Enabled) Values
(null,
1,
20,
sysdate,
null,
100, /* User Id*/
Sysdate,
1);

Commit;

Select t.*,t.rowid from Emp_dept_assign t;

You will find employee’s joining dates to department 10 and 20, this will keep all history data and you will find actual picture of all employees.

Tips to create table

Create table with following default columns always

1. ID (Unique values)
2. Created_by
3. Creation_date default sysdate
4. updated_by
5. last_update_date
6. Enable default ‘1’ /* 1 = Enabled , 0 = Not Enabled */Create sequence for every table to generate auto number, with the same name as table, add sq_ as prefix.

Create trigger for unique column in table and use sequence to populate values.

Script:

Create table EMP (
Emp_id number, -- Primary Key Column
Name varchar2(100),
DOB date,
Created_by number,
Creation_date date default sysdate,
Last_updated_by number,
Updation_date date,
Enabled number(1) /*default value 1*/
);

Create sequence sq_emp;

Create trigger tbi_table_name
Before insert on emp
For each row
Begin
If :new.emp_id is null then
Select sq_emp.nextval into :new.emp_id from dual;
End if;
End;
/

Insert into Emp(
emp_id,
name,
DOB,
created_by
,creation_date
,Enabled) Values
(null,
'Amjad Ali',
'31-Dec-1974',
100, -- User id which would be available in other table user.
Sysdate,
'1');

Commit;

Select * from Emp;

Do not Delete record

You need to restrict user not to delete data from database, instead of that he mark data status as disabled. To do the same you must have "Enabled" column in all of your tables. If user want to delete record he must check that records as enabled='N'.

You must query all data in application using

Same record will be available in database but user should not view. To get this implemented you should create view on table and select all column in it like following statement.

Select * from table1 where enabled='Y';

user will only see enabled data just like he see after delete some data.

How to create Auto number functionality in Oracle

Some time we need to create MS Access like Auto number feature for Oracle tables. We can get the same functionality by using this script.

1) Create Table

CREATE TABLE activity (
activity_id NUMBER NOT NULL,
description VARCHAR2(50) NULL );


2) Create primary key constraint

ALTER TABLE activity ADD ( PRIMARY KEY (activity_id) ) ;

3) Create a sequence for table

CREATE SEQUENCE sq_activity;

4) Create before insert trigger on table

CREATE TRIGGER tbi_activity
BEFORE INSERT ON activity
FOR EACH ROW
BEGIN
IF :new.activity_id IS NULL THEN
SELECT sq_activity.nextval
INTO :new.activity_id
FROM dual;
END IF;
END;
/


5) Insert new record into activity table

Insert into activity (activity_id,description) values(null,'Test');
Insert into activity (activity_id,description) values(null,'Test 2');

6) Commit;

7) Select * from activity

8) if you have more than one tables you may use following statements to create sequences and triggers on tables.

SELECT 'create sequence sq_' || table_name || ' ;'
FROM user_tab_columns
WHERE column_id = 1 AND
data_type = 'NUMBER'
ORDER BY table_name
,column_id;




SELECT 'create trigger tbi_' || table_name ||
' before insert on ' || table_name ||
' for each row begin if :new.' || column_name ||
' is null then select sq_' || table_name ||
'.nextval into :new.' || column_name ||
' from dual; end if; end;' || chr(10) || '/'
FROM user_tab_columns
WHERE column_id = 1 AND
data_type = 'NUMBER'
ORDER BY table_name
,column_id;

How to Create Index on Foreign Keys (Script)

Script contacins two sections
1) Function that would combine multiple rows into single string
2) Script used to create "Create Index" statement.

CREATE OR REPLACE FUNCTION xx_concate_FIELDs (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
OPEN rec FOR sqlstr;
LOOP
FETCH rec INTO field;
EXIT WHEN rec%NOTFOUND;
ret := ret || field || sep;
END LOOP;
if length(ret) = 0 then
RETURN '';
else
RETURN substr(ret,1,length(ret)-length(sep));
end if;
end;
/


SELECT 'create index ' || t.owner || '.' || 'xx_' ||
substr(t.table_name, 1, 20) || xxhr.emp_tmp.nextval ||
' on ' || t.owner || '.' || t.table_name || '(' ||
t.concate_fields || ');' STRING
,t.owner
,t.table_name
FROM (SELECT DISTINCT uc.owner
,uc.constraint_name
,uc.constraint_type
,uc.table_name
,xx_concate_fields('select column_name from all_cons_columns where table_name = ''' ||
uc.table_name || '''' ||'AND constraint_name=''' || uc.constraint_name ||''' order by position ',',') concate_fields
FROM all_constraints uc
,all_cons_columns ucc
WHERE uc.constraint_type = 'R' AND
uc.constraint_name = ucc.constraint_name AND
uc.table_name = ucc.table_name) t;