Algorithm to convert given SQL Query to Relational Algebra

For the benefit of those who were looking for a logic on how to go ahead and implement this for lab exercise.

- My program will take a SQL Query as one whole string in the command line.
- splitInputQuery will split the SQL Query based on space.
- parseSQLQuery will take one word at a time from the SQL Query and set some global flags.
- decideRelAlgebra will make a decision on what could be the Relational Algebra equivalent based on the flags set in parseSQLQuery.

Eg:
SQL: "select * from E"
RA: PROJECT E

SQL: "select name from student"
RA: PROJECT name student

SQL: "select * from student where name=neo"
RA: SELECT name=neo student

SQL: "select name,roll_no from student where name=neo"
RA: PROJECT name,roll_no(SELECT name=neo student)

Having shown the examples above, I am giving the algorithm(in the form of pseudocode) below. Hope its useful. Questions are welcome.

/Algorithm/
Algorithm used to convert simple SQL query to Relation Algebra:

/**********************************************************************************
* Description: This program is intended to take simple SQL queries and
* convert them to equivalent Relational Algebra
*
* Input: SQL Query as a single string Eg: "select emp_name from E"
*
* Output: Equivalent Relational Algebra Eg: PROJECT emp_name E
*
*********************************************************************************/
main(String[] args)
if (args.length <0)
return 0;
else {
/* args[0] will have the SQL Query */
if (splitSQLQuery(args[0]) == 0) {
call parseSQLQuery()
}
call decideRelAlgebra()
}

/**********************************************************************************
* Description: This procedure takes a simple SQL queries and split it based on space
* and stores them as a array of string
*
* Input: SQL Query as a single string Eg: "select emp_name from E"
*
* Output: split SQL Query as a array of Strings Eg: {"select" ,"emp_name","from","E"}
*
*********************************************************************************/

splitSQLQuery(inputSQLQuery) {
if (inputSQLQuery != null) {
/* if input is valid then split based on " " and save it in global array splittedInputQuery
splittedInputQuery = inputSQLQuery.split(" ");
} else {
return 1; /* return non-zero on error */
}
return 0;
}

/**********************************************************************************
* Description: This procedure takes split SQL Query array and parse them to find the
* equivalent Relational Algebra
*
* Input: Splitted SQL Query array
*
* Output: global flags are set to make a decision
*
*********************************************************************************/

parseSQLQuery() {
/* for all the strings in the splitedInputQuery do this */
for(int i 0 to no_of_strings in query) {
/* querystring is the current string form the splittedinputquery array */
if (querystring matches "select") {
/*then it could be select or it could be project in RA*/
set maybeProject to 1
continue to get next string;
}
if(querystring matches "*") {
set selectall to 1;
continue to next string;
}

if (querystring is of the pattern [a-zA-Z_]*) and (selectall != 0) {
set maybeAttribs to 1;
attribs = querystring;
continue to next string;
}
if (querystring matches "from") {
set maybeFrom to 1;
continue to next string;
}

if(querystring is of pattern [a-zA-Z-]*) and (maybeFrom is set) {
set maybeTable to 1;
tableName = queryString;
continue to next string;
}

if(querystring matches "where") {
set maybeSelect to 1
set gotWhere to 1
continue to next string;
}
/* to match with where patterns like Eg: name=N or roll_no=100 and so on
if(queryString is of pattern [a-zA-z_< > = <= >= !=0-9*]*) and (where is set to 1) {
set maybeConditions to 1
conditions = queryString;
continue to next string;
}
}
}

/**********************************************************************************
* Description: This procedure decides on the equivalent relational algebra for the given
* SQL Query based on the flags set by parseSQLQuery
*
* Input: SQL Query as a single string Eg: "select emp_name from E"
*
* Output: Equivalent Relational Algebra Eg: PROJECT emp_name E
*
*********************************************************************************/

decideRelAlgebra() {
if (gotWhere is not 1) {
set confirmedProject to 1
set relAlg to PROJECT
}
if (selectAll is 1) and (gotWhere is not 1) {
set confirmedProject to 1
set relAlg to PROJECT
}
if (gotWhere is 1) and (attribs is not null) {
set projectwithselect to 1
set relAlg to PROJECT (SELECT )
}

if (gotWhere is 1) and (selectall is 1) {
set confirmedproject to 1
set relAlg to SELECT
}
}

Very Interactive(VI) editor commands

This has been a request from couple of people, putting it down for them and for everyone who is looking for frequently used vi editor commands.

Edit commands:
-------------------
x - delete one character at a time
nx - delete n characters at a time, replace n with number you want
dd - delete/cut a line
ndd - delete/cut x lines (replace n with number you want Eg: 10dd would delete 10 lines)
yy - yank a line(copy to clipboard)
nyy - yank n lines(replace n with number you want Eg: 10yy would yank 10 lines)
p - paste from the clipboard(deleted lines are like copy too, they will be in clipboard, so p after dd or yy will paste whats there in the clipboard)

*Remember: All the above commands are relative to the current cursor position. i.e. If you are at line 20 and performing dd, line at that position will be deleted/cut. Similarly p operation will paste after 20th line.

More Edit commands:
--------------------------
dw - delete/cut a word
ndw - delete/cut n words (replace n with number you want Eg: 3dw will delete 2 words)
cw - change or replace word (move the cursor to the word you want to change and give cw, then type in the replacement word)
ncw - change n words at a time(Eg: 2cw will help you replace two words)
o - to insert a line below the line where the cursor is positioned
shift+o - to insert a line above the line where the cursor is positioned

Cursor positioning or movement:
---------------------------------------
<- or h - to move left -> or l - to move right
up arrow or k - to move up
down arrow or j - to move down
0(zero) - to move to the first character of the current line
$ - to move to last character of the line
w - to move cursor to the beginning of the next word
b - to move cursor to the first character of the preceding word
:0 - to move to the first line of the file(what you see here is zero)
:n - to move to the nth line of the file(replace n with number of your choice)
:$ - to move the cursor to the last line of the file

* CR- carriage return/Enter key in the keyboard

Scroll commands:
---------------------
^d (ctrl+d) - scroll half screen down
^u (ctrl+u) - scroll half screen up
^f (ctrl+f) - scroll full screen down
^b (ctrl+b) - scroll full screen up

(do it for yourself to see the difference, choose a bigger file to see the difference between these scrolls for yourself)

Search commands:
-----------------------
/string - to search for a string (replace string with your search string)
(n to find the next match, shift+n to find the previous match.

Search and Replace:
------------------------
:s/old_string/new_string - replaces the first occurrence of "old_string" with "new_string" in the current line (Eg:- :s/import/export will replace first occurrence of the word import with export in the current line)
:s/old_string/new_string/g - will replace all occurrences of "old_string" with "new_string" in the current line
:%s/old_string/new_string/g - will replace all occurrences of old_string with new_string in the file.

(**Remember everything is case sensitive here, to make your search & replace case insensitive see below)
:s/old_string/new_string/i
:s/old_string/new_string/gi
:%s/old_string/new_string/gi

see the 'i' at the end makes it case in-sensitive.

To perform search and replace between selected lines, you can use the commands below.
:l1,l2s/old_string/new_string/g - replace l1 and l2 with line numbers you want. (Eg:- :1,10s/import/export/g you know what this means... isn't it?)

Undo:
--------
u - to undo previous commands

Switching modes:
----------------------
i - insert mode
a - append mode
esc - to get out of any mode you already chosen

I will stop with these basic commands you would need to start.

In case if people need more useful commands at advance level, I will put a separate post later.

Happy VI'ing :))