SQL OPERATOR

A part from standard relational operators (= and >), SQL has some other operators that can be used in conditions.

Operator                                               What it does? BETWEEN value-1 AND value-2       Checks whether the value is in the
                                                              given range. The range is                                                                                                                           inclusive of the given values.
IN(list)                                                  Checks whether the value is matching  
                                                              with any one of the values given in the
                                                              list. List contains values separated by comma(,).
 LIKE pattern                                        Checks whether the given string is matching
                                                              with the given pattern. More on this later.
IS NULL and IS NOT NULL               Checks whether the value is null or not null.

Now, let us see how to use these special operators of SQL.

BETWEEN ... AND Operator  Checks whether value is in the given range. The range includes all the values in the range including the min and max values. This supports DATE type data also.

To display the list of course where DURATION is in the range 20 to 25 days, enter:

select name
from courses
 where duration between 20 and 25;
 NAME --------------------
Oracle database
C programming
ASP.NET
Java Language
Note: BETWEEN.. AND is alternative to using >= and <= operators.

IN Operator 

Compares a single value with a list of values. If the value is matching with any of the values given in the list then condition is taken as true.
The following command will retrieve all courses where duration is either 20 or 30 days.
select name
from courses
where duration in (20,30);
NAME --------------------
VB.NET
C programming
The same condition can be formed even without IN operator using logical operator OR as follows: Select name
from courses
where duration = 20 or duration = 30;
However, it will be more convenient to user IN operator compared with multiple conditions compared with OR operator.

LIKE operator 

This operator is used to search for values when the exact value is not known. It selects rows that match the given pattern. The pattern can contain the following special characters.

Symbol                                  Meaning 

% %. _ (underscore)       Zero or more characters can take the place
                                        of  Any single character can take the place
                                        of underscore. But there must be one letter.

To select the courses where the course name contains pattern .NET, enter:

select name,duration, fee from courses
where name like '%.NET%'
NAME          DURATION     FEE
-------------------- --------- ---------
VB.NET         30                   5500
ASP.NET       25                    5000
The following example selects courses where second letter in the course code is “b” and column PREREQUISITE contains word “programming”.

select * from courses where ccode like '_b%' and prerequisite like '%programming%';
CCODE            NAME          DURATION     FEE         PREREQUISITE
----- -------------------- --------- --------- ------------------------
vbnet                VB.NET            30                 5500          Windows and programming

Remember LIKE operator is case sensitive. In the above example, if CCODE contains value in uppercase (VB), then it won’t be a match to the pattern.

IS NULL and IS NOT NULL operators  

These two operators test for null value. If we have to select rows where a column is containing null value or not null value then we have to use these operators. For example the following SELECT command will select all the courses where the column FEE is null.
select * from courses
where fee is null;

Though Oracle provides NULL keyword, it cannot be used to check whether the value of a column is null. For example, the following condition will always be false as Oracle treats two null values as two different values.
select * from courses
where fee = null;

The above command does NOT work as fee though contains null value will not be equal to NULL. SO, we must use IS NULL operator. 

SELECTION OPERATOR

It is possible to select only the required rows using WHERE clause of SELECT command. It implements selection operator of relational algebra. WHERE clause specifies the condition that rows must satisfy in order to be selected. The following example select rows where FEE is more than or equal to 5000.
select name, fee from courses
where fee >= 5000

NAME                     FEE

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

VB.NET                 5500
ASP.NET                 5000

The following relational and logical operators are used to form condition of WHERE clause. Logical operators – AND, OR – are used to combine conditions. NOT operator reverses the result of the condition. If condition returns true, NOT will make the overall condition false.

Operator              Meaning 

 =                            Equal to
!= or <>                  Not equal to
>=                          Greater than or equal to
<=                          Less than or equal to
 >                           Greater than
<                            Less than
AND                     Logical ANDing
OR                        Logical Oring
NOT                     Negates result of condition.

The following SELECT command displays the courses where duration is more than 15 days and course fee is less than 4000.
select * from courses
where duration > 15 and fee < 4000;

CCODE    NAME       DURATION FEE      PREREQUISITE
 ----- -------------------- --------- --------- -----------------

c                  C programming      20 3500                 Computer Awareness

The following SELECT command retrieves the details of course with code ORA.
select * from courses where ccode = 'ora';

CCODE   NAME       DURATION FEE      PREREQUISITE
                                                                                                   

ora         Oracle database     25 4500               Windows

Note: When comparing strings, the case of the string must match. Lowercase letters are not equivalent to uppercase letters.

ORDER BY CLAUSE

It is possible to display the rows of a table in the required order using ORDER BY clause. It is used to sort rows on the given column(s) and in the given order at the time of retrieving rows. Remember, sorting takes place on the row that are retrieved and in no way affects the rows in the table. That means the order of the rows will remain unchanged.

Note: ORDER BY must always be the last of all clauses used in the SELECT command.

The following SELECT command displays the rows after sorting rows on course fee.

 select name, fee from courses order by fee;

NAME                      FEE
 -------------------- ---------
C programming         3500
XML Programming  4000
Oracle database        4500
Java Language         4500
ASP.NET                 5000
VB.NET                  5500

Note: Null values are placed at the end in ascending order and at the beginning in descending order.

The default order for sorting is ascending. Use option DESC to sort in the descending order. It is also possible to sort on more than one column. To sort rows of COURSES table in the ascending order of DURATION and descending order of FEE, enter:

select name, duration, fee from courses
order by duration , fee desc;

NAME              DURATION              FEE
-------------------- --------- ---------
XML Programming 15                      4000
C programming        20                      3500
ASP.NET                 25                      5000
Oracle database       25                      4500
Java Language         25                     4500
VB.NET                  30                      5500

First, all rows are sorted in the ascending order of DURATION column. Then the rows that have same value in DURATION column will be further sorted in the descending order of FEE column.

Using column position 

Instead of giving the name of the column, you can also give the position of the column on which you want to sort rows.

For example, the following SELECT sorts rows based on discount to be given to each course.

select name, fee, fee * 0.15
from courses
order by 3;

NAME         FEE            FEE*0.15
 -------------------- --------- ---------
C                 3500            525
XML          4000             600
Oracle        4500             675
Java            4500            675
ASP.NET   5000            750
VB.NET     5500            825

Note: Column position refers to position of the column in the selected columns and not the position of the column in the table.

The above command uses column position in ORDER BY clause. Alternatively you can use column alias in ORDER BY clause as follows:

select name, fee, fee * 0.15 discount
from courses
order by discount;

NAME        FEE              DISCOUNT
-------------------- --------- ---------
C                3500              525
XML          4000              600
Oracle        4500              675
Java            4500              675
ASP.NET   5000              750
VB.NET     5500              825

SELECTING ROWS FROM A TABLE

Let us see how to retrieve data of a table. SELECT command of SQL is used to retrieve data from one or more tables. It implements operators of relational algebra such as projection, and selection. The following is the syntax of SELECT command. The syntax given here is incomplete. For complete syntax, please refer to online documentation

SELECT [DISTINCT | ALL]
 {* | table.* | expr } [alias ]
 [ {table}.*| expr } [alias ] ] ...
 FROM [schema.]object
 [, [schema.]object ] ...
 [WHERE condition]
 [ORDER BY {expr|position} [ASC | DESC]
 [, {expr|position} [ASC | DESC]] ...]

schema is the name of the user whose table is being accessed. Schema prefix is not required if the table is in the current account. Schema prefix is required while we are accessing a table of some other account and not ours. The following is an example of a basic SELECT command.

select * from courses;

 CCODE     NAME DURATION     FEE         PREREQUISITE                                                                                                                                            

ora               Oracle database         25 4500      Windows
vbnet           VB.NET                    30 5500      Windows and programming
c                   C programming        20 3500       Computer Awareness
asp               ASP.NET                  25 5000       Internet and programming
java             Java Language           25 4500      C language
xml             XML Programming    15 4000      HTML,Scripting, ASP/JSP

The simplest SELECT command contains the following:

• Columns to be displayed. If * is given, all columns are selected.

• The name of the table from where rows are to be retrieved. 

Projection : Projection is the operation where we select only a few columns out of the available columns. The following is an example of projection. 

select name,fee from courses;
NAME                             FEE
-------------------- ---------
Oracle database              4500
VB.NET                         5500
C programming              3500
ASP.NET                        5000
Java Language                4500
 XML Programming       4000

Using expressions in SELECT command  It is also possible to include expressions in the list of columns. For example, the following SELECT will display discount to be given for each course.

select name,fee, fee * 0.15 from courses;
NAME                FEE               FEE*0.15
 -------------------- --------- ---------
Oracle database 4500                675
VB.NET            5500                825
C programming 3500                525
ASP.NET           5000                750
Java Language   4500                675
XML                  4000                600

Column Alias  The column heading of an expression will be the expression itself. However, as it may not be meaningful to have expression as the result of column heading, we can give an alias to the column so that alias is displayed as the column heading.
The following example will use alias DISCOUNT for the expression FEE * 0.15.
select name, fee, fee * 0.15 DISCOUNT from courses
NAME                    FEE                    DISCOUNT
-------------------- - -------- ---------
Oracle database        4500                    675
VB.NET                   5500                    825
C programming        3500                    525
ASP.NET                  5000                    750
Java Language          4500                    675
 XML Programming 4000                    600

The following are the arithmetic operators that can be used in expressions.
Operator            Description
+                        Add
 -                       Subtract
*                       Multiply
 /                       Divide

INSERTING ROWS INTO A TABLE

Now, let us see how to insert rows into COURSES table. SQL command INSERT is used to insert new row into the table. While inserting rows, you may enter value for each column of the table or selected columns. The following command inserts a row into COURSES table.

insert into courses
 values('ora','Oracle database',25,4500,'Knowledge of Windows');

Note:After inserting the required row, issues COMMIT command to make sure the changes are made permanent. We will discuss more about COMMIT command later in this book but for the time being it is sufficient to know that COMMIT command will make changes permanent. Without COMMIT, rows that are inserted might be lost if there is any power failure.

During insertion, character values are enclosed in single quotes. Unless otherwise specified we have to supply a value for each column of the table. If the value of any column is not known or available then you can give NULL as the value of the column. For example, the following insert will insert a new row with null value for PREREQUISITE column.

insert into courses
values('c','C Programming',25,3000,null);

Note: INSERT command can insert only one row at a time. For multiple row, INSERT command must be issued for multiple times.
DATE type values must be in the format DD-MON-YY or DD-MON-YYYY, where MON is the first three letters of the month (Jan, Feb). If only two digits are given for year then current century is used. For example, if you give 99 for year, Oracle will take it as 2099 as the current century is 2000. So it is important to remember this and give four digits if required.
The following is the complete syntax for INSERT command.
INSERT INTO tablename [(columns list)]
 {VALUES (value-1,...) | subquery }
We will see how to insert row into a table using a subquery later in this blog
Inserting a row with selected columns 
It is possible to insert a new row by giving values only for a few columns instead of giving values for all the available columns
The following INSERT command will insert a new row only two values.
insert into courses(ccode,name)
 values ('odba','Oracle Database Administration');
 The above command will create a new row in COURSES table with values for only two columns – CCODE and NAME. The remaining columns will take NULL value or the default value, if the column is associated with default value. We will discuss more about default value in the next blog

NULL value :

Null value means a value that is not available or not known. When a column’s value is not known then we store NULL value into the column. NULL value is neither 0 nor blank nor any other known value. We have already seen how to store null value into a column and when Oracle automatically stores null value into a column. We will discuss more about how to process null value later in this chapter.

ORACLE INSTANCE

Oracle instance is a collection of memory structures and processed that are used to manage oracle database. Each oracle database is to be accessed by one or more Oracle instances. If two or more instances are accessing the same database, it is called as parallel server architecture. In order to start using an oracle database, we must first start Oracle instance. Oracle instance will them open the database and make it available to users. It is beyond the scope of this book to discuss what Oracle instance actually contains. Please read “Oracle Concepts” manual for complete information about oracle instance. In nutshell every oracle installation contains at least one Oracle Instance and one oracle database

What Is Personal Oracle? 

Personal Oracle is one of the flavors of Oracle. This is not a product that is used by production system (systems where real data is stored). This is more like a learning tool. It runs on desktop PCs. In personal oracle, oracle instance, oracle database and client application all run on the same machine (see figure 3). Whereas in Oracle database server, only oracle instance and database reside on the server and client applications run on clients.

It is also possible to develop an applications using Personal Oracle on you desktop/laptop and deploy them in a client/server environment.

CREATING A TABLE

Creating a Table :

 A Table is a collection of rows and columns. Data in relational model is stored in tables. Let us create a table first. Then we will understand how to store data into table and retrieve data from the table.
 Before a table is created the following factors of a table are to be finalized.

• What data table is supposed to store.

• The name of the table. It should depict the content of the table. 

• What are the columns that table should contains

• The name, data type and maximum length of each column of the table. 

• What are the rules to be implemented to main data integrity of the table. 

The following is an example of creation of COURSES table. We actually have six tables in the application that we use throughout the book. We will discuss more about all the tables in the next chapter. But for now, let us create COURSES table and understand how to use basic SQL commands.
SQL> create table COURSES
 2 (  ccode       varchar2(5)
, 3 name          varchar2(30),
 4 duration       number(3),
 5 fee               number(5),
 6 prerequisite varchar2(100)
7 );
Table Created 

The above command creates a table called COURSES. This table contains 5 columns. We will discuss about rules to be implemented in this table in the next chapter, where we will recreate this table with all the required rules. For the time being I want to keep things simple. That is the reason why I am not taking you into constraint and remaining. Well, we have created our first table. If command is successful, Oracle responds by displaying the message Table Created. 

Rules to be followed for names : 

The following are the rules to be followed while naming an Oracle Object. These rules are applicable for name of the table and column. 

• The name must begin with a letter - A-Z or a-z.

• Letters, digits and special characters – underscore (_), $ and # are allowed

• Maximum length of the name is 30 characters.

• It must not be an SQL reserved word.

• There should not be any other object with the same name in your account. 

Note: A table can contain up to1000 columns in Oracle8 or above, whereas in Oracle7 a table can contain only 254 columns.  

Datatypes : 

 Each column of the table contains the datatype and maximum length, if it is length is applicable. Datatype of the column specifies what type of data can be stored in the column. The datatype VARCHAR2 is to store strings that may have different number of characters, NUMBER is used to store numbers. The maximum length, which is given in parentheses after the datatype, specifies how many characters (or digits) the column can store at the most. For example, column VARCHAR2 (20) would mean it can store up to 20 characters. Table-1 lists out datatypes available in Oracle8i along with what type of data can be stored and maximum length allowed.  

Displaying table definition using DESCRIBE  

You can display the structure of a table using SQL*PLUS command DESCRIBE. It displays then name, datatype and whether the column can store null value for each column of the table. The following is the syntax of DESCRIBE command.

        DESC[RIBE] objectname 

Displays the column definitions for the specified object. The object may be a table, view, synonym, function or procedure.
To display the structure of COURSES table, enter: 

SQL> DESC COURSES 

Name                                    Null?                         Type                     

 CCODE                                NOT NULL            VARCHAR2(5)
 NAME                                                                  VARCHAR2(30)
 DURATION                                                           NUMBER(3)
 FEE                                                                       NUMBER(5)
 PREREQUISITE                                                  VARCHAR2(100)

DESCRIBE is an SQL*Plus command and can be abbreviated to DESC. 

STARTING UP DATABASE

Before we access oracle database, we must start oracle database. Starting up oracle database means starting oracle instance and associating oracle instance with an oracle database so that oracle instance can access the database. The process is very length and complicated. Several steps are involved in it. But fortunately we do not have to know all that happens when a database starts. We just need to select an option or two to startup database. Generally you do not have to startup database in case of Oracle Server running on Windows NT/Windows 2000 as oracle server automatically starts in this case. However, if you ever have to start oracle database on Windows NT/Windows 2000,

•  follow the steps given below: 
• Start services program using Administrative Tools -> Service in Windows/2000 or Control Panel -> Service on Windows NT.
• If service OracleServiceOracle8i has not yet started, click on it with right button and select start option from popup menu. 

The exact name of the service depends on the name you have given to oracle instance at the time of installing it.

Note: Starting and shutting down the database is the job of Database Administrator. As this books assumes that you are an application developer, it doesn’t get into those details.

Starting up database in Personal Oracle : Unlike Oracle Server in Personal Oracle, Oracle Instance doesn’t start on its own. The Oracle Instance must be explicitly started. The following are the steps to start oracle on Personal Oracle:

• Select start database option in Personal Oracle8i for windows menu.
• When a dialog box is displayed wait until the message Oracle Instance Started appears.
• Click on Close button to close the dialog box. 

Starting SQL*PLUS : Sql*plus is a tool that comes along with Oracle. It is used to issue SQL and SQL*PLUS commands. It provides command line interface through which we can enter SQL and SQL*PLUS command.

To start SQL*PLUS, take the steps given below: 

• Select start->programs->Oracle - Oracle8i. Oracle8i is the name of the instance. It may be different on your system.
• Then select Application Development -> SQL Plus.
• . When Log On dialog box is displayed, enter username, password and Host string. Use tab key to move from one field to another. For more information about each of these fields, see next section
• Click on OK. 
• If the information supplied is valid then you enter into Oracle and SQL*PLUS will display SQL> prompt. 

Username, Password and Host String  

Oracle is a multi-user database. Whoever is access the database must log on to database. To log on we have to supply username and password. When the given username and password are recognized by Oracle, it will allow us to access data. A user can access only the data that belongs to his/her and not the data of others. However, it is possible for a user to grant privileges to others so that other can access his/her data.

Creation of users and management of overall security is the responsibility of Database Administrator (DBA). DBA is the person who makes sure that database is functioning smoothly. He is responsible for operations such as taking backup of the database, recovering the database in the event of failure, fine tuning database to get best performance. So, if you want to have a new account under your name, please consult administrator of your database. 

Username & Password    Every user who wants to access oracle database must have an account in the database. These accounts are created by DBA. Each account is associated with username and password. Oracle comes with a set of predefined accounts. 

Note: when you enter into oracle using either system or sys then you become DBA. That means you get special privileges to perform major operations such as creating users etc.  

Host String  Host string is a name that is used to access oracle server that is running on a different machine from client. This is required only when you are trying to access oracle server that is not on the current machine. That means, you never need to use host string for Personal Oracle as client and oracle always run on the same machine in Personal Oracle. Host string is required when you are trying to connect to Oracle Server running on remote machine. Host string is actually called as net service

Name. Net service name is a name that is stored in TNSNAMES.ORA file on the client to provide the following information.

 Host:  Name of the machine or IP address of the machine on which oracle server is running. 

Instance name   Name of the Oracle Instance running on the remote machine. Port Number Port number of the listener, a program that takes requests from clients. 

Port number    is an integer that uniquely identifies the program on the server. 

HOW TO ENTER SQL AND SQL*PLUS STATEMENTS?

How to enter SQL statements? :

SQL*PLUS allow to types of command to entered at the prompt - SQL and SQL*PLUS. SQL commands include commands of ANSI/ISO SQL and extra commands added to ANSI SQL by oracle. The following are the rules to be followed while entering SQL commands.

• An SQL statement may be entered in multiple lines.
• It is not possible to break a word across lines. 
• 
  
• SQL statement must be terminated by semicolon (;). 

The following is an example of SQL command. 

What this command does is not important at this moment.
In the above command, we entered the command in three lines. When you enter semicolon and press enter key then SQL*PLUS will take it as the end of the command. Also note that you have to press enter key at the end of each line.

How to enter SQL*PLUS statements?   : SQL*Plus statements are available only in SQL*PLUS. They are not part of standard SQL. SQL*Plus commands are mainly used for two purposes – editing SQL commands and formatting result of query.

The following rules are to be followed while entering these commands. :

• The entire command must be entered on a single line.
• No need to terminate command with semicolon (;). 
• Commands can be abbreviated. However, the amount of abbreviation is not fixed. Some commands are abbreviated to one letter some are abbreviated to 2 and so on.

Common Errors :The following are the common errors that you get while you are trying to log on to Oracle.

  

Ora-01017: invalid username/password; login denied 

The reason for this error is that you have entered a username or password that is not valid. Check whether username you are entering is really existing and password is correctly typed. Sql*plus gives you three chances to type username and password correctly. If you cannot log on successfully in three chances then Sql*plus will exit. However, you can restart Sql*plus again.

ORA-01034: ORACLE not available : The reason for this message is that Oracle Instance is not up and running. You have to first make sure you have started Oracle Instance. Actually there are a few other problems that occurs when Oracle Instance has not started successfully. If this is case in Oracle Server, notify administrator. If this is the case with Personal Oracle, make sure you start database as mentioned in “starting up database” section.
   

INTRODUCTION TO DBMS (DATABASE MANAGEMENT SYSTEM)

What is DBMS?:  Data is one of the most important assets of a company. It is very important to make sure data is stored and maintained accurately and quickly. DBMS (Database Management System) is a system that is used to store and manage data. A DBMS is a set of programs that is used to store and manipulation data. Manipulation of data include the following:

• Adding new data, for example adding details of new student.  
• Deleting unwanted data, for example deleting the details of students who have completed course.
• Changing existing data, for example modifying the fee paid by the student.

A DBMS provides various functions like data security, data integrity, data sharing, data concurrence, data independence, data recovery etc. However, all database management systems that are now available in the market like Sybase, Oracle, and MS-Access do not provide the same set of functions, though all are meant for data management. Database managements systems like Oracle, DB2 are more powerful and meant for bigger companies. Whereas, database management systems like MS-Access are meant for small companies. So one has to choose the DBMS depending upon the requirement.

Main Features Of DBMS :

The following are main features offered by DBMS. Apart from these features different database management systems may offer different features. For instance, Oracle is increasing being fine-tuned to be the database for Internet applications. This may not be found in other database management systems. These are the general features of database management systems. Each DBMS has its own way of implementing it. A DBMS may have more features the features discussed here and may also enhance these features.

Support for large amount of data : Each DBMS is designed to support large amount of data. They provide special ways and means to store and manipulate large amount of data. Companies are trying to store more and more amount of data. Some of this data will have to be online (available every time). In most of the cases the amount of data that can be stored is not actually constrained by DBSM and instead constrained by the availability of the hardware. For example, Oracle can store terabytes of data.

Data sharing, concurrency and locking : DBSM also allows data to be shared by two or more users. The same data can be accessed by multiple users at the same time – data concurrency. However when same data is being manipulated at the same time by multiple users certain problems arise. To avoid these problems, DBMS locks data that is being manipulated to avoid two users from modifying the same data at the same time. The locking mechanism is transparent and automatic. Neither we have to inform to DBMS about locking nor we need to know how and when DBMS is locking the data. However, as a programmer, if we can know intricacies of locking mechanism used by DBMS, we will be better programmers.

Data Security : While DBMS allowing data to be shared, it also ensures that data in only accessed by authorized users. DBMS provides features needed to implement security at the enterprise level. By default, the data of a user cannot be accessed by other users unless the owner gives explicit permissions to other users to do so.

Data Integrity : Maintaining integrity of the data is an import process. If data loses integrity, it becomes unusable and garbage. DBMS provides means to implement rules to maintain integrity of the data. Once we specify which rules are to be implemented, then DBMS can make sure that these rules are implemented always. Three integrity rules (discussed later in this chapter) – domain, entity and referential are always supported by DBMS.

Fault tolerance and recovery : DBMS provides great deal of fault tolerance. They continue to run in spite of errors, if possible, allowing users to rectify the mistake in the mean time. DBSM also allows recovery in the event of failure. For instance, if data on the disk is completely lost due to disk failure then also data can be recovered to the point of failure if proper back up of the data is available.

Support for Languages :  language for RDBMS (relational database management systems) is SQL. We will discuss more about RDBMS and SQL later in this chapter. DBMS implementation of SQL will be compliant with SQL standards set by ANSI. Apart from supporting a non-procedural language like SQL to access and manipulate data DBMS now a days also provides a procedural language for data processing. Oracle supports PL/SQL and SQL Server provides T-SQL.

Entity and Attribute :

An entity is any object that is stored in the database. Each entity is associated with a collection of attributes. For example, if you take a data of a training institute, student is an entity as we store information about each student in the database. Each student is associated with certain values such as roll number, name, course etc., which are called as attributes of the entity.
There will be relationship among entities. The relationship between entities may be one-to-one, one-to-many or many-to-many. If you take entities student, batch and subject, the following are the possible relationships. There is one-to-one relationship between batch and subject. One batch is associated with only one subject. Three is one-to-many relationship between batch and student entities. One batch may contain many students. There is many-to-many relationship between student and subject entities. A single student may take many subjects and a single subject may be taken by multiple students.  

DATA MODEL

Data model is a way of storing and retrieving the data. There are three different data models. Data models differ in the way they allow users to view and manipulate relationships between entities. Each has its own way of storing the data. The following are the three different data models:

• Hierarchical

• Network

• Relational 

Hierarchical Data Models:

In this model, data is stored in the form of a tree. The data is represented by parent child relation ship. Each tree contains a single root record and one or more subordinate records. For example, each batch is root and students of the batch will be subordinates. This model supports only one-to-many relationship between entities.

Network  data model :

Data is stored along with pointers, which specify the relationship between entities. This was used in Honeywell's Integrated Data Store, IDS. This model is complex. It is difficult to understand both the way data is stored and the way data is manipulated. It is capable of supporting many-to-many relationship between entities, which hierarchical model doesn’t.

Relational  data model :

This stores data in the form of a table. Table is a collection of rows and columns. We will discuss more about relational model in the next second. 

RElATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)

A DBMS that is based on relational model is called as RDBMS. Relation model is most successful mode of all three models. Designed by E.F. Codd, relational model is based on the theory of sets and relations of mathematics. Relational model represents data in the form a table. A table is a two dimensional array containing rows and columns. Each row contains data related to an entity such as a student. Each column contains the data related to a single attribute of the entity such as student name. One of the reasons behind the success of relational model is its simplicity. It is easy to understand the data and easy to manipulate. Another important advantage with relational model, compared with remaining two models is, it doesn’t bind data with relationship between data item. Instead it allows you to have dynamic relationship between entities using the values of the columns. Almost all Database systems that are sold in the market, now- a-days, have either complete or partial implementation of relational model.

Tuple / Row : A single row in the table is called as tuple. Each row represents the data of a single entity.

Attribute / Column : A column stores an attribute of the entity. For example, if details of students are stored then student name is an attribute; course is another attribute and so on.

Column Name :Each column in the table is given a name. This name is used to refer to value in the column.

Table Name : Each table is given a name. This is used to refer to the table. The name depicts the content of the table. The following are two other terms, primary key and foreign key, that are very important in relational model.

Primary Key: A table contains the data related entities. If you take STUDETNS table, it contains data related to students. For each student there will be one row in the table. Each student’s data in the table must be uniquely identified. In order to identify each entity uniquely in the table, we use a column in the table. That column, which is used to uniquely identify entities (students) in the table is called as primary key. In case of STUDENTS table (see figure 1) we can use ROLLNO as the primary key as it in not duplicated. So a primary key can be defined as a set of columns used to uniquely identify rows of a table.

Composite Primary Key : In some tables a single column cannot be used to uniquely identify entities (rows). In that case we have to use two or more columns to uniquely identify rows of the table. When a primary key contains two or more columns it is called as composite primary key.

we have PAYMENTS table, which contains the details of payments made by the students. Each row in the table contains roll number of the student, payment date and amount paid. Neither of the columns can uniquely identify rows. So we have to combine ROLLNO and DP to uniquely identify rows in the table. As primary key is consisting of two columns it is called as composite primary key.

Foreign Key: In relational model, we often store data in different tables and put them together to get complete information. For example, in PAYMENTS table we have only ROLLNO of the student. To get remaining information about the student we have to use STUDETNS table. Roll number in PAYMENTS table can be used to obtain remaining information about the student. The relationship between entities student and payment is one-to-many. One student may make payment for many times. As we already have ROLLNO column in PAYMENTS table, it is possible to join with STUDENTS table and get information about parent entity (student). Roll number column of PAYMENTS table is called as foreign key as it is used to join PAYMENTS table with STUDENTS table. So foreign key is the key on the many side of the relationship.

ROLLNO column of PAYMENTS table must derive its values from ROLLNO column of STUDENTS table. When a child table contains a row that doesn’t refer to a corresponding parent key, it is called as orphan record. We must not have orphan records, as they are result of lack of data integrity. 

INTEGRITY RULES

Data integrity is to be maintained at any cost. If data loses integrity it becomes garbage. So every effort is to be made to ensure data integrity is maintained. The following are the main integrity rules that are to be followed.

Domain integrity : Data is said to contain domain integrity when the value of a column is derived from the domain. Domain is the collection of potential values. For example, column date of joining must be a valid date. All valid dates form one domain. If the value of date of joining is an invalid date, then it is said to violate domain integrity.

Entity integrity : This specifies that all values in primary key must be not null and unique. Each entity that is stored in the table must be uniquely identified. Every table must contain a primary key and primary key must be not null and unique.

Referential Integrity : This specifies that a foreign key must be either null or must have a value that is derived from corresponding parent key. For example, if we have a table called BATCHES, then ROLLNO column of the table will be referencing ROLLNO column of STUDENTS table. All the values of ROLLNO column of BATCHES table must be derived from ROLLNO column of STUDENTS table. This is because of the fact that no student who is not part of STUDENTS table can join a batch

RELATIONAL ALGEBRA

A set of operators used to perform operations on tables is called as relational algebra. Operators in relational algebra take one or more tables as parameters and produce one table as the result.

The following are operators in relational algebra:

• Union 
• Intersect  
• Difference or minus
• Project 
• Select 
• Join 

   Union :This takes two tables and returns all rows that are belonging to either first or second table (or both).

                                                                                                                                           Intersect : This takes two tables and returns all rows that are belonging to first and second table.

Difference or Minus : This takes two tables and returns all rows that exist in the first table and not in the second table

Project :Takes a single table and returns the vertical subset of the table.

Select : Takes a single table and returns a horizontal subset of the table. That means it returns only those rows that satisfy the condition.

Join : Rows of two table are combined based on the given column(s) values. The tables being joined must have a common column.

Structured Query Language (SQL) :

 Almost all relational database management systems use SQL (Structured Query Language) for data manipulation and retrieval. SQL is the standard language for relational database systems. SQL is a non-procedural language, where you need to concentrate on what you want, not on how you get it. Put it in other way, you need not be concerned with procedural details. SQL Commands are divided into four categories, depending upon what they do.

• DDL (Data Definition Language) 
• DML (Data Manipulation Language)
• DCL (Data Control Language)  
• Query (Retrieving data)

DDL  (Data Definition Language): commands are used to define the data. For example, CREATE TABLE. 

DML (Data Manipulation Language) :commands such as, INSERT and DELETE are used to manipulate data.

DCL (Data Control Language)  : commands are used to control access to data. For example, GRANT.

Query (Retrieving data) :  is used to retrieve data using SELECT.

DML and Query are also collectively called as DML. And DDL and DCL are called as DDL.

DATA PROCESSING METHODS

Data that is stored is processed in three different ways. Processing data means retrieving data and deriving information from data. Depending upon where it is done and how it is done, there are three methods.

• Centralized data processing
• De-centralized data processing 
• Distributed data processing 

Centralized data processing :

In this method the entire data is stored in one place and processed there itself. Mainframe is best example for this kind of processing. The entire data is stored and processed on mainframe. All programs, invoked from clients (dumb terminals), are executed on the mainframe and data is also stored in mainframe.
As you can see in figure 6, all terminals are attached to mainframe. Terminals do not have any processing ability. They take input from users and send output to users. 

Decentralized data processing  : 

 In this data is processed at various places. A typical example is each department containing its own system for its own data processing needs. See figure 7, for an example of decentralized data processing. Each department stores data related to itself and runs all programs that process its data. But the biggest drawback of this type of data processing is that data is to be duplicated. As common data is to be stored in each machine, it is called as redundancy. This redundancy will cause data inconsistency. That means the data stored by two departments will not agree with each other.

Data in this mode is duplicated, as there is no means to store common data in one place and access from all machines.

                                                            

DISTRIBUTED DATA PROCESSING (CLIENT/SERVER)

In this data processing method, data process is distributed between client and server. Server takes care of managing data. Client interacts with user. For example, if you assume a process where we need to draw a graph to show the number of students in a given month for each subject, the following steps will take place:

• First, client interacts with user and takes input (month name) from user and then passes it to server.

• Server then will query the database to get data related to the month, which is sent to server, and will send data back to client. 
• The client will then use the data retrieved from database to draw a graph.  

If you look at the above process, the client and server are equally participating in the process. That is the reason this type of data processing is called as distributed. The process is evenly distributed between client and server. Client is a program written in one of the font-end tools such as Visual basic or Delphi. Server is a database management system such as Oracle, SQL Server etc. The language used to send commands from client to server is SQL .
This is also called as two-tier client/server architecture. In this we have only two tiers (layers) one is server and another is client.
The following is an example of 3-tier client server, where client interacts with user on one side and interacts with application server on another side. Application, which processes and validates data, takes the request from client and sends the request in the language understood by database server. Application servers are generally object oriented. They expose a set of object, whose methods are to be invoked by client to perform the required operation. Application server takes some burden from database server and some burden from client.

In 3-tier client/server architecture, database server and application server may reside on different machines or on the same machine. Since the advent of web application we are also seeing more than 3-tiers, which is called as n-tier architecture. For example, the following is the sequence in a typical web application.

• Client- web browser, sends request to web server. 
• Web server executes the request page, which may be an ASP or JSP. 
• ASP or JSP will access application server. 
• Application server then will access database server.    

Summary :

A DBMS is used to store and manipulate data. A DBMS based on relational model is RDBMS. Primary key is used for unique identification of rows and foreign key to join tables. Relational algebra is a collection of operators used to operate on tables. We will see how to practically use these operators in later chapter. SQL is a language commonly used in RDBMS to store and retrieve data. In my opinion, SQL is one of the most important languages if you are dealing with an RDBMS because total data access is done using SQL.