IIT Bombay

Welcome to the DELite
Database for Embeded Light weight system.



DELite is an Open Source Light Weight Database Management System developed by Department of Computer Science, IIT Bombay.














Documentation

Table of Contents:


Publications


1. Rajkumar Sen and Krithi Ramamritham, Efficient Data Management on Lightweight Computing Devices(Poster Paper),
Proc. of 21st IEEE International Conference on Data Engineering (ICDE),
Tokyo, Japan, April, 2005.


2. Krithi Ramamritham and Rajkumar Sen, DELite: Database Support for Embedded Lightweight Devices (Keynote paper),
EMSOFT2004: Fourth ACM International Conference on Embedded Software, Pisa, Italy, September 27-29, 2004.


References

[AHK85] A. Ammann, M. Hanrahan, and R. Krishnamurthy.
Design of a Memory Resident DBMS. In IEEE COMPCON, 1985.


[BBPV00] C. Bobineau, L. Bouganim, P. Pucheral, and P. Valduriez.
PicoDBMS: Scaling down Database Techniques for the Smartcard. In VLDB, 2000.


[BK02] Stephen Blott and Henry F. Korth.
An Almost Serial Protocol for Transaction Execution in Main Memory Database Systems. In VLDB, 2002.


[BPA03] L. Bouganim, P. Pucheral, and N. Anciaux.
Memory Requirements for Query Execution in Highly Constrained Devices. In VLDB, 2003.


[CK97] Michael Carey and Donald Kossmann.
On Saying Enough Already in SQL. In ACM SIGMOD, 1997.


[DKO + 84] David J DeWitt, Randy H Katz, Frank Olken, Leonard D Shapiro, Michael R Stonebraker, and David Wood.
Implementation Techniques for Main Memory Database Systems. In ACM SIGMOD, 1984.


[DVRM99] Anindya Datta, Debra VanderMeer, Krithi Ramamritham, and Bongki Moon.
Applying Parallel Processing Techniques in Data Warehousing and OLAP. In VLDB, 1999.


[GH90] Le Gruenwald and Margaret H.Eich.
Choosing the Best Storage Technique for a Main Memory Database System. In IEEE Conference on Information Technology, Jerusalem, 1990.


[Gig01] E. Giguere.
Mobile Data Management: Challenges of Wireless and Online Data Access. In ICDE, 2001.


[Gra93] G. Graefe.
Query evaluation techniques for large databases. In ACM Computing Survey, 1993.


[HSS00] A. Hulgeri, S. Seshadri and S. Sudarshan.
Memory Cognizant Query Optimization. In COMMAD, 2000.


[Kel85] Arthur M. Keller.
Algorithms for Translating View Updates to Database Updates for Views Involving Selections, Projections and Joins. In ACM PODS, 1985.


[KFW03] M. Kersten, M. Franklin, G. Weikum, D. Keim, A. Buchmann, and S. Chaudhuri.
A Database Striptease or How to Manage Your Personal Database. A Panel Discussion. In VLDB, 2003.


[KLLP01] J.S. Karlsson, A. Lal, C. Leung, and T. Pham.
IBM DB2 Everyplace: A Small Footprint Relational Database System. In ICDE, 2001.


[Lan90] Rom Langerak.
View Updates in Relational Databases with an Independent Scheme. In ACM PODS, 1990.


[LC86] T. Lehmann and M. Carey.
A Study of Index Structures for Main Memory DBMS. In VLDB, 1986.


[MFHH02] S. Madden, M. J. Franklin, J. Hellerstein, and W. Hong.
TAG: a Tiny AGgregation Service for Ad­Hoc Sensor Networks. In Intl. Conf. on Operating Systems and Implementation, 2002.


[ms83] M. Missikov and M. Scholl.
Relational Queries in a Domain Based DBMS. In ACM SIGMOD, 1983.


[OLi] Oracle Corporation,
Oracle 9i Lite, Oracle Documentation.


[PVJ90] P. Pucheral, P. Valduriez, and J.M.Thevenin.
Efficient Main Memory Data Management using the DBGraph Storage Model. In VLDB, 1990.


[RR99] Jun Rao and Kenneth A. Ross.
Cache Conscious Indexing for Decision support in Main Memory. In VLDB, 1999.


[SACL79] P. Selinger, M. Astrahan, D. Chamberlin, and R. Lorie.
Access path selection in a relational database management system. In ACM SIGMOD, 1979.


[Ses99] Praveen Seshadri.
Honey I Shrunk the DBMS. In ACM SIGMOD, 1999.


[SG00] Praveen Seshadri and Phir Garrett.
SQL Server for Windows CE- A Database Engine for Mobile and Embedded Platforms. In ICDE, 2000.


[Sim]
The Simputer. http://www.simputer.org.


[Sma]
Small Databases are Beautiful, Database Trends and Applications, August 2003. http://www.dbta.com.


[SSK02] S. Sudarshan, A. Silberschatz, and H. Korth.
Database System Concepts. McGrawHill International Fourth Edition, 2002.


[Sen04] Rajkumar Sen.
An Open Source DBMS for HandHeld Devices. Masters Dessertation, Indian Institute of Technology Bombay 2004.


[RHS95] Gautam Ray, Jayant R. Haritsa and S. Seshadri.
Data Compression: A Performance Enhancement Tool. COMAD, 1995.


[WKHM98] Till Westmann, Donald Kossmann, Sven Helmer and Guido Moerkotte.
The Implementation and Performance of Compressed Databases. SIGMOD, 1998.


[Rao05] Ashwini Rao.
Compression in Memory Constrained DBMSs . Masters Dessertation,Indian Institute of Technology Bombay 2005.


[AB04]Amar Agrawal and Jatin Bharadia.
Project Report for Implementation of Compression in Simputer DBMS. MTech. Course Project Report, IIT Bombay, 2004.


[PS04]Kriti Puniyani and Neela Sawant.
1-Phase Optimizer for the SimputerDB. MTech. Course Project Report, IIT Bombay, 2004.




DELite

DELite is an open source database. It is developed for handheld devices like Simputer. It has a unicode support for Hindi and Marathi languages.

Features of DELite

Some of the features of our database are as follows.
  • Flash memory is used as read buffer and RAM is used as write buffer.

  • Query processing technique based on the memory allocation algorithms generates
    only fully piplined, left deep tree query execution plans.

  • Index structures are generated in memory during query execution.

  • Select, Project, Join, update and aggregate queries are supported.

  • Pointer based storage model like ID-based and Domain based are supported.




Simputer

The Simputer developed by IISC, Bangalore and Encore Software, is one
of the most powerful handheld devices today. It uses the Intel Strong
Arm processor which is 32-bit and runs at 200MHz. It has a 32MB DRAM and
a 24MB Flash Memory. The Flash Memory is used for the permanent stable
storage and the operating system resides in it. The Simputer uses a 320- 240
monochrome Display panel which is used with a stylus. It recognizes smart-cards
which are the personalization agents. The Simputer uses the Linux operating
system. Linux is an open source operating system and is available for the Strong Arm
processor.





Getting started

How to Use it:

This topic provides an overview of how to do SQL to perform simple operations. This tutorial is only intended to give you an introduction. Instructions to download the database and to run the SQL queries are given in Technical Details section. There are two executable files "initdb.exe" and "sqlplus.exe". "initdb.exe" initializes the catalogs. "sqlplus.exe" processes the SQL queries.
DELite is a relational database management system . That means it is a system which manages the data stored in relations(tables). Each relation is a collection of rows. Each row of a relation has the same set of named columns, and each column is of a specific data type.




The SQL Commands






CREATE TABLE

Create a Table:

The syntax of the CREATE Statement is:
CREATE TABLE tablename ( colunmname datatype,.......);
datatype: INTEGER, FLOAT or CHARACTER STRING can be given.
A table can be created by specifying the table name, along with all column names and their data types . e.g.,
CREATE TABLE STUDENT( NAME CHAR(10), ROLLNO INTEGER, GRADE FLOAT);
CHAR(10) specifies a data type that can store character strings up to 10 (whatever number is specified)characters in length. INTEGER is the normal integer type. FLOAT is a type for storing floating-point numbers. Table name and attribute name should be of string or alphanumeric type.





INSERT INTO TABLE

Insert the rows in Table:

The INSERT statement is used to populate a table with rows. The syntax of INSERT Statement is:
INSERT INTO tablename VALUES (value1, value2.....);
An example with a table name STUDENT with three values is shown below.
INSERT INTO STUDENT VALUES ('xyz',15,9.6);
This will add one row in the table STUDENT. The syntax used requires to remember the order of the column names in the table and their data types. To avoid that, list of columns can be given explicitly. For example, the following commands will have same effect as the one above.

INSERT INTO STUDENT (NAME, ROLLNO, GRADE) VALUES('xyz',15,9.6);

INSERT INTO STUDENT (ROLLNO, GRADE, NAME) VALUES(15,9.6,'xyz');
If some columns of the table are not there in the column list then those columns are filled with the default (null) values. For Example, In the following query,column NAME is not there in the column list, so the value of NAME column will be filled with default (null) value in the table.
INSERT INTO STUDENT (ROLLNO, GRADE) VALUES(15,9.6);





UPDATE

Update a Table:

Updates can be done in the record by using the UPDATE command. The Syntax of the UPDATE Statement is:
UPDATE tablename SET set value WHERE where condition;
set value: attribute name=value (the new value to be written in the colunm)
where condition: attribute name=value ( the value to be matched to reach to the record where we want to update)
Suppose we want to update the grade of one student from 9.6 to 9.8. We can update the data as follows:
UPDATE STUDENT SET GRADE=9.8 WHERE NAME='xyz';
It also supports AND/OR features to specify more than one conditions. If WHERE clause is not there then all the records in the table are updated. For example, this command will update all the discount values to 10 in table Product.
UPDATE Product SET discount = 10;



ALTER TABLE

Alter a Table :

When the table is already filled with data and we need to modify the table then we can alter the definition of the table by
(i) renaming the tablename or
(ii) renaming the column

By Renaming a Table:

It allows the user to rename an existing table. There is no effect on the data stored in the table. The Syntax of Renaming the table is:
ALTER TABLE oldtablename RENAME TO newtablename;
Suppose we want to change the existing table EMP with new name EMPLOYEE, then the SQL will be:
ALTER TABLE EMP RENAME TO EMPLOYEE;

By Renaming Column Name:

We can rename the column name as:
ALTER TABLE tablename RENAME COLUMN columnname TO new_columnname;
Suppose we want to change the column empid to employee_id in table EMPLOYEE, Then the SQL will be:
ALTER TABLE EMPLOYEE RENAME COLUMN empid TO employee_id;



DELETE

Deletions:

Rows can be removed from a table using the DELETE command. The syntax of the DELETE Statement is:
DELETE FROM tablename WHERE where condition;
where condition: attribute name = value
Suppose we are not interested in student xyz 's record anymore. Then we can do the following to delete those rows from the table:
DELETE FROM STUDENT WHERE NAME='xyz';
This comand will delete the records in which name colunm has matching value xyz and move the last record in the empty place. It also supports AND and OR features (for defining more than one condition).

If we write:
DELETE FROM STUDENT;
It will delete all the records from the specified table.




SHOW TABLE

Show Table:

All the rows of the table can be displayed using the SHOW command. The syntax of the SHOW Statement is:
SHOW TABLE tablename;
or
SELECT * FROM tablename;
It also supports WHERE conditions with AND/OR/LIKE and LIMIT features(to see the matched records).
Suppose we want to see the whole record of a table STUDENT. Then we can do the following :
SHOW TABLE STUDENT;
This will list all the records of table STUDENT.


with WHERE CLause:

We can see the matched records using WHERE Clause.The syntax is:
SELECT * FROM tablename WHERE where_clause;
where_clause: WHERE search_condition
where search_condition is value expression. Some examples of WHERE clause are as follows.
WHERE rollno = 10002
WHERE rollno > 2000
WHERE rollno < 2000
WHERE rollno != 10002
Pattern matching can be done by using LIKE option. Suppose there is a table "STUDENT". We want to see the records having name column starts with "ab", then,the syntax will be;
SHOW TABLE STUDENT WHERE NAME LIKE 'ab';
or
SELECT * FROM STUDENT WHERE NAME LIKE 'ab';

with LIMIT and OFFSET:

This feature allows to see the limited number of records. The syntax is:
SELECT * FROM tablename limit_clause;
limit_clause = LIMIT limit_value OFFSET offset_value | LIMIT limit_value| OFFSET offset_value | LIMIT limit_value,offset_value
If the LIMIT is given, then only that many records will be listed. If OFFSET is given, then records from the offset value to the last record will be listed. If both LIMIT and OFFSET values are given then the records from offset value to limit value will be listed.
Suppose we have a table named "EMPLOYEE" having 50 records. We can give the queries as follows;
SELECT * FROM EMPLOYEE LIMIT 40 OFFSET 10;
This will show records from 10 to 40;
SELECT * FROM EMPLOYEE LIMIT 30;
This will show the records from the beginning till 30.
SELECT * FROM EMPLOYEE OFFSET 20;
This will show records from 20 onwards till end.
SELECT * FROM EMPLOYEE LIMIT 35,15;
This will show records from 15 to 35. If the value of LIMIT or OFFSET is 0 then it will show all the records.

with ORDER BY :

This feature gives the sorted result. The syntax is:
SELECT * FROM tablename [where_clause] ORDER BY columnname [ASC/DESC];
It will sort the result by the specified column in ascending or descending order. Suppose we have a table name "EMPLOYEE" and one of the columns in the table is "EMPID". If we want to get the output in ascending order of "EMPID" column, then the query will be:
SELECT * FROM EMPLOYEE ORDER BY EMPID ASC;
For getting the output in descending order, the query will be:
SELECT * FROM EMPLOYEE ORDER BY EMPID DESC;
If we don't mention ASC/DESC in query then it will sort the rows in ascending order.
We can give the ORDER BY Clause with WHERE condition and with LIMIT/OFFSET. The Syntax is:
SELECT * FROM tablename WHERE where condition ORDER BY column name [ASC/DESC] limit_clause
ORDER BY clause can be given with only WHERE condition or with only LIMIT clause also.
**NOTE: ORDER BY clause sorts the columns in ascending/descending order only with the INTEGER datatype.

Show Tables Command:

We can see the number of tables present in the database by using the following command:
SHOW TABLES;



COMPRESS/DECOMPRESS TABLE

Compress/Decompress a Table

We can keep the records in a compressed form to reduce the space used for storing the data. The syntax of the COMPRESS Statement is:
COMPRESS TABLE tablename;
Suppose we want to compress the table STUDENT. Then we can do the following:
COMPRESS TABLE STUDENT;
This will keep the table STUDENT in a compressed form. We can insert a new record in a compressed table using INSERT statement, see all the records using SHOW statement and delete the records using DELETE statement.
If we want to use the table in decompressed form we can write the following statement:
DECOMPRESS TABLE STUDENT;
Once the table is decompressed, we can do all the operations mentioned above.


DROP TABLE

Drop the Table:

The DROP TABLE statement will drop the table created by CREATE TABLE Command. The table is completely removed and can not be recovered. The syntax of the DROP Statement is:
DROP TABLE tablename;
Suppose we want to remove a table named STUDENT. Then we can do the following :
DROP TABLE STUDENT;
This will remove the table with tablename STUDENT from the database catalog. Empty space created in catalog file is filled automatically with the last catalog info in the catalog file and last catalog is deleted. This statement will remove the table even if it is in compressed form.



QUERY

Querying a Table:

To retrieve a particular data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into three parts: a select list (the part that lists the columns to be returned), a table list (the part that lists the tables from which to retrieve the data), and the qualification (the part that specifies any restrictions). The syntax of SELECT Statement is:
SELECT colunmname FROM tablenames WHERE qualifiers;
colunmname: tablename1.colunmname1, tablename2.colunmname2....
tablenames: tablename1,tablename2,.......
qualifiers: tablename1.attributename=value (AND) tablename2.attributename=value..........;
To retrieve all the rows of table STUDENT with qualification GRADE=9, we do the following.
SELECT STUDENT.NAME ,STUDENT.ROLLNO,STUDENT.GRADE FROM STUDENT WHERE STUDENT.GRADE=9.0;

Alternatively we can use '*' in place of specifying each field of the table to be listed.
SELECT * FROM STUDENT WHERE STUDENT.GRADE=9.0;
To see all the rows and columns of the given table; the following command can be given:
SELECT * FROM STUDENT;

Join Query:

A query that accesses multiple rows of the same or different tables at one time is called a join query. A query can be "qualified" by adding a WHERE clause that specifies which rows are wanted. The WHERE clause contains a Boolean expression, and only rows for which the Boolean expression is true are returned. The Boolean operator AND is allowed in the qualification. As an example, the following retrieves the doctors who prescribed Antibiotic. To do that, we need to compare the DOCID column of each row of the DOCTOR table with the VISITID column of all rows in the VISITtable, the DRUGID column of each row of the DRUG table with the VISITID column of all rows in the VISIT table, the VISITID column of each row of the PRES table with the VISITID column of all rows in the VISIT table, and TYPE column of all rows in the DRUG table and select the pairs of rows where these values match. OR feature is not provided yet.
SELECT DOCTOR.NAME FROM DOCTOR,PRES,DRUG,VISIT WHERE DOCTOR.DOCID=VISIT.DOCID AND DRUG.DRUGID=PRES.DRUGID AND PRES.VISITID=VISIT.VISITID AND DRUG.TYPE='Antibiotic' ;


Aggregate Function:

An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows.
Count Operator:
Count operator counts the total number of rows present in the table. The syntax is,
SELECT COUNT (*) FROM tablename [where_clause][LIMIT/OFFSET];
To see the total number of rows present in the table named "EMPLOYEE", query will be:
SELECT COUNT (*) FROM EMPLOYEE;
To see the number of records matched with a given condition in table "EMPLOYEE",query will be:
SELECT COUNT (*) FROM EMPLOYEE WHERE empname='abcde';

Security Related Commands






CREATE USER

Creating a user:

The syntax for creating a new user is:
CREATE USER username IDENTIFIED BY password;
When a new user is added in the users list, he/she can login into the database system and has all the privileges on the table he/she creates. Only ADMIN can CREATE a user. If we want to add a new user named "DELITE" then;
CREATE USER DELITE IDENTIFIED BY DELITE;
Here the user name is DELITE and password is DELITE.



DELETE USER

Deleting a user:

The syntax for removing the user from the users list:
DELETE USER username;
When a user is deleted from the user list, he/she is dropped from all the privillages. If we want to delete a user named "DELITE" then;
DELETE USER DELITE;
Here the user name is DELITE. Only ADMIN has access to delete the user.



CHANGE PASSWORD

Changing the password

The syntax for changing the password is:
CHANGE PASSWD username oldpassword newpassword;
By this command user can change his/her password. For password, combination of alphabets and numbers are allowed. First letter should be alphabet. If user DELITE wants to change his password then;
CHANGE PASSWD DELITE DELITE DELITE123;
Here the user name is DELITE,old password is DELITE and the new password is DELITE123.



CREATE ROLE

Creating a rolename:

The syntax for creating a role is:
CREATE ROLE rolename;
A role can be either a database user, or a group of database users, depending on how the role is set up. Roles can own tables and can assign privileges on those tables to other roles. If we want to create a role with a name manager then;
CREATE ROLE MANAGER
The Role will be created with the rolename MANAGER.



DELETE ROLE

Deleting a rolename:

The syntax for deleting a rolename is:
DELETE ROLE rolename;
With this command rolename will be deleted from the ROLE list. Once the rolename is deleted, all the privillages it had( including the members of rolename ) will aoutomatically be canceled. Suppose we want to delete a role with a name manager then;
DELETE ROLE MANAGER
The rolename will be deleted with the name MANAGER and all its privillages will also be dropped out.



ASSIGN ROLE

Assigning a user to a rolename:

The syntax for assigning a rolename is:
GRANT rolename TO username;
The user becomes a member of a rolename. One rolename can have many users or other rolenames. Suppose we want to assign a user named DELITE to a rolename MANAGER then;
GRANT MANAGER TO DELITE;
The user DELITE will be a member of rolename MANAGER. Whatever privillages the rolename has, user will also have the same privillages. When the assigned privillage is revoked from rolename, it will be automatically revoked from the user assigned the rolename. If the rolename is deleted, then the rolename as well as assigned user's privillages will be canceled.



REVOKE ROLE

Revoke a user from a rolename:

The syntax for revoking a user from rolename is:
REVOKE rolename FROM username;
The user will be removed from rolename list. All his/her privillages assigned via rolename will be dropped out. Suppose we want to revoke a user named DELITE from a rolename MANAGER then;
REVOKE MANAGER FROM DELITE;
The user DELITE will not be a member of rolename MANAGER anymore. Whatever privillages it had through rolename, will be automatically revoked from the user.



GRANT PERMISSION

Granting a permission to do specific operation on a specified table to user/rolename:

The syntax is:
GRANT PERM ON tablename TO username [WITH GRANT OPTION];
or
GRANT PERM ON tablename TO rolename [WITH GRANT OPTION];

PERM: INSERT/DELETE/UPDATE/SHOW/SELECT/DROP/ ALL
GRANT command gives specific privileges on a specified table to one or more users/roles. These privileges are added to those already granted, if any. If WITH GRANT OPTION is specified, the recipient of the privilege may in turn grant it to others. Without a grant option, the recipient cannot do that. There is no need to grant privileges to the owner of the table (the user who created it), as the owner has all privileges by default. The table owner may of course revoke these privileges. ADMIN or owner of the table can grant the permissions. The privileges are:
SELECT: Allows SELECT operation on specified table.
INSERT: Allows INSERT of a new row into the specified table.
UPDATE: Allows UPDATE of any column of the specified table.
DELETE: Allows DELETE of a row from the specified table.
All: Grant all the above privileges at once.
Suppose ADMIN grants insert operation on table EMPLOYEE to a user DELITE then we will write as below:
GRANT INSERT ON EMPLOYEE TO DELITE;
The user DELITE is given permission to perform only insert operation on the table EMPLOYEE.



REVOKE PERMISSION

Revoking a permission from a user/rolename:

The syntax for revoking the permission is:
REVOKE PERM ON tablename FROM username
or
REVOKE PERM ON tablename FROM rolename;

PERM: INSERT/DELETE/UPDATE/SHOW/SELECT/ALL
The REVOKE command revokes previously granted privileges from one or more users/roles. If a user holds a privilege using WITH GRANT OPTION and has granted it to other users then the privileges held by other users are dependent privileges. If the privilege held by the first user is being revoked and dependent privileges exist, then dependent privileges are also revoked. A user can only revoke privileges that were granted directly by that user. It can be done by the role also.
Example: Revoke insert privilege from DELITE on table EMPLOYEE:
REVOKE INSERT ON EMPLOYEE FROM DELITE;



Technical Details

DELite Report


People

Principal Investigators:

Prof. Krithi Ramamritham and Prof. S. Sudarshan.

Team Members:

MTech Students:

Rajkumar Sen(Alumni)
Ashwini Rao(Alumni)
Aditee Badge(Alumni)
Prajakta kelkar(Alumni)
Santhosh Kumar D.

Research Staff:

Vivek Patel (Past)
Aruna Adil
Jagrati Agrawal(Past)


Last updated August 02, 2007