Basic understanding of DBMS and RDBMS and Database and its Architecture

Dear Friend

As I stated in my previous blog, Let's start with our first item(SQL).
Let's Focus on the  Basic understanding of DBMS and RDBMS and Database and its Architecture

What is Data:-

The data is stored representation of objects and events which has the meaning .It can be structured or unstructured.

What is Information:-It is the data in the processed form which will increase the knowledge of end user.

What is Metadata:-It is the data which describes the properties or characteristics of the user data and its context.

What is database:-Database is the collection of data in one or more data file.

DBMS:-Database management system is the software which is used to create or manage the database and provides the controlled access to the database.

Database from Oracle point of View:-Database is the collection of data in one or more data file.

It is the collection of Logical structure and Physical Structure which are configured to maintain the integrity of the system.

Logical structured:-In the database State, The Logical structured are the representation of the actual metadata which are in the form of Table space, data dictionary.

Table space:- It is invisible in nature and bridge between the oracle database and Data File structure.

Data dictionary: - It is also a logical structure which basically captures the metadata of the Objects.

Physical Structure: - In the database State, The Physical structure are in the form of Table,synonym,view etc.

 Table:- It is called as the unit of database.

Architecture:-

 Different types of DBMS:-

Flat file DBMS
Hierarchical DBMS
Network DBMS
Relational DBMS
Object oriented DBMS
Object Relational DBMS
Data Warehousing
Web Enabled DBMS

Features of DBMS

1.       Creating the database

2.       Updating the database

3.       Removing the unwanted data from the database

4.       Providing the Controlled access to the database.

RDBMS:- Relational Database management System is a software which is used to establish the relationship among the database objects.

The relational Model was first defined by the E F Codd in 1970.

Benefits of RDBMS:-

1.       Sharing Data across the application

2.       Reduce the cost of Maintenance

To communicate with database and the language of RDBMS is Structured Query Language (SQL).

Architecture:-

SQL :- It is structured Query Language which is used to communicate with Database

Types of Data type of SQL:-

1.       Character Data type

a.       Char

b.      NChar

c.       Varchar2

d.      Nvarchar2

2.       Date Data type :-Date

3.       Number Data type:- Number 

4.       Rowid DataType.

5.       Large object  Data type

a)      CLOB

b)      NCLOB

c)       BLOB

d)      NBLOB

Difference between CHAR and VARCHAR2:-

Char data type will consume the memory as specified at time of creation the table whereas VARCHAR2 will consume only that amount of memory which is required.

Types of SQL Statement

1.       DRL(Data Retrieval Language)

a.       SELECT

2.       DML(Data Manipulation Language)

a.       INSERT,

b.      UPDATE,

c.       DELETE

NOTE:-DML is not the auto commit

3.       DDL(Data Definition Language)

a.       Create

b.      ALTER

c.       Truncate

d.      Drop

NOTE:-DDL is the auto commit

4.       TCL(Transactional Control Language)

a.       COMMIT

b.      SAVEPOINT

c.       ROLLBACK

5.       DCL(Data Control Language)

a.       GRANT

b.      REVOKE

Basic structure of SELECT Command:

SELECT clon_list FROM …WHERE …ORDER BY…

select * from dept

Select empno,ename,job,sal from emp where deptno=20;

DDL(Data Definition Language)

create table emp_info

 (employee_no Number,Employee_name Varchar2(20),Sal Number,Job varchar2(20),

 HIredate date,DOB date,Phone Number,Address Varchar2(30) );

Table created.

Alter table emp_info modify Address varchar2(10);

SQL> alter table emp_info add age number;

Table altered.

SQL> ALTER Table emp_info drop column age;

Table altered.

SQL> desc emp_info;

SQL> insert into emp_info ( EMPLOYEE_NO    ,EMPLOYEE_NAME,SAL,JOB,HIREDATE,DOB,PHONE,ADDRESS)

2  values(1000,'JANET,1000,'Teacher','10-OCT-12','23-OCT-1979',9784563210,'Bangalore');

1 row created.

SQL> commit;
 Commit complete.
SQL> insert into emp_info values (&EMPLOYEE_NO,&EMPLOYEE_NAME,&&SAL,&&JOB,&&HIREDATE,&DOB,&PHONE,&AD
DRESS);
Enter value for employee_no: 1001
Enter value for employee_name: 'GHANSHYAM'
Enter value for sal: 2000
Enter value for job: 'Developer'
Enter value for hiredate: '03-OCT-2012'
Enter value for dob: '03-OCT-1985'
Enter value for phone: 9611668092
Enter value for address: 'Hyderabad'
old   1: insert into emp_info values (&EMPLOYEE_NO,&EMPLOYEE_NAME,&&SAL,&&JOB,&&HIREDATE,&DOB,&PHONE
new   1: insert into emp_info values (1001,'GHANSHYAM',2000,'Developer','03-OCT-2012','03-OCT-1985',
1 row created.
SQL> /
Enter value for employee_no: 1002
Enter value for employee_name: 'Merry'
Enter value for dob: '4-DEC-2012'
Enter value for phone: 9845369851
Enter value for address: 'Chennai'
old   1: insert into emp_info values (&EMPLOYEE_NO,&EMPLOYEE_NAME,&&SAL,&&JOB,&&HIREDATE,&DOB,&PHONE
new   1: insert into emp_info values (1002,'Merry',2000,'Developer','03-OCT-2012','4-DEC-2012',98453
1 row created.

SQL> select * from emp_info;
Commit complete.
SQL> insert into emp_info(EMPLOYEE_NO,EMPLOYEE_NAME,SAL,JOB)
2  values(1003,'Mark',3000,'Clerk');
1 row created.
Rollback;
Rollback complete.
SQL> insert into emp_info(EMPLOYEE_NO,EMPLOYEE_NAME,SAL,JOB)
2  values(1003,'Mark',3000,'Clerk');
1 row created.
SQL>  select * from emp_info;
SQL> commit;
Commit complete.
tuncate emp_info;

Diffrence between Delete and Truncate: Truncate is DDL command and delete is DML command;
truncate cannot be roll backed and delete can be roll backed.

What is drop:-  removes rows from the table
SAVEPOINT:-
rollback savepoint s2;
Grant
Revoke
SQL> update emp_info set DOB='04-DEC-1987' where EMPLOYEE_NO=1002;
1 row updated.
SQL> select * from emp_info where EMPLOYEE_NO=1002;
EMPLOYEE_NO EMPLOYEE_NAME               SAL JOB                  HIREDATE  DOB            PHONE ADDRESS

----------- -------------------- ---------- -------------------- --------- --------- ---------- ----

1002 Merry                      2000 Developer            03-OCT-12 04-DEC-87 9845369851 Chennai

SQL> commit;
Commit complete.
SQL> delete from emp_info where employee_no=1003;
1 row deleted.
SQL> select * from emp_info;
SQL> rollback;
SQL> rollback to s2;
Rollback complete.
SQL> select EMPNO,ENAME,JOB,SAL from emp where job='SALESMAN' and sal<2000;
SQL> select EMPNO,ENAME,JOB,SAL from emp where comm is null;
SQL> select EMPNO,ENAME,JOB,SAL from emp where comm is not null;
SQL> select EMPNO,ENAME,JOB,SAL from emp where sal between 2000 and 3000;
select EMPNO,ENAME,JOB,SAL ,deptno  from emp where deptno IN (10,20);
SQL>  select EMPNO,ENAME,JOB,SAL from emp where ename like 'SMITH';
SQL> select EMPNO,ENAME,JOB,SAL from emp where ename like 'S%';
SQL> select EMPNO,ENAME,JOB,SAL from emp where ename like '%S%';
SQL> select EMPNO,ENAME,JOB,SAL from emp where ename like '_D%';
SQL> select EMPNO,ENAME,JOB,SAL from emp where ename like '%E_';
SQL> select EMPNO,ENAME,JOB,SAL from emp where ename NOT like '%E_';
SQL> select EMPNO,ENAME,JOB,SAL from emp order by sal;
SQL> select EMPNO,ENAME,JOB,SAL from emp order by sal desc;
SQL> select EMPNO,ENAME,JOB,SAL from emp order by ename;
SQL> SELECT DISTINCT SAL FROM EMP;
select * from emp where deptno IN(10,20,90);

WHAT IS NULL:-It is something

1.       Which is Void
2.       Which is Unknown
3.       Which is Unavailable
4.       Which is  Unassigned

NOTE:- NULL can not be equal to Null

 

Thanks for reading my blog .You are welcome to share your thoughts

Thanks
Ghanshyam M