Download CS425 Project: Database Parser and Query Processor and more Study Guides, Projects, Research Deductive Database Systems in PDF only on Docsity!
CS 425 Spring 2001
Single-user relational DBMS
Group members:
Zornitza Panayotova
Chirag Bhatt
Rahul Kumar
1. Introduction
This document explains the RDBMS project for CS425. It enlists the details of the implementation and results.
2. Project Requirements
1.The DBMS executes the following SQL Commands:
a. SELECT
- IN
- BETWEEN
- LIKE b. INSERT
- 1.single-row
- multiple-row c. UPDATE d. DELETE e. CREATE TABLE
- INTEGER
- CHAR
- STRING
- PRIMARY KEY
- FOREIGN KEY f. CREATE VIEW g. DROP TABLE h. DROP VIEW
i. SELECT
MIN, MAX, AVG, COUNT, DISTINCT
3. Design overview:
The system is built by using Microsoft Visual C++ 6.0. Type of data structures: apsring, string, char*, linked list, apvector, array.
- apvector allAttrList; // list of all attributes for the tables given in the query
- Catalog cat;
- List< test > mainList[5];// Test list
- List< test > queryList; // Select List
Global variables:
- bool primaryFlag=false; // if token equal to PRIMARY is found
- int tableIndex=0;// index for the tables
- int fromIndex=0;// index for the FROM function
- int startPos, // used for subquery SELECT โ start position of SELECT
- stopPos; // used for subquery SELECT โ end position of SELECT
Function prototypes:
- void start(); // reads the input line and strores it into one, which will be break into tokens;
- void parser( apstring allLines, apvector &token ); // breaks the lines into tokens;
- void error(); // outputs error message and asks the user to enter the correct query;
- int counter( apstring line ); // counts the number of tokens in a query;
- void select ( apvector token, int startPos, int numTokens ); // when SELECT is found in the query; it makes an selectList - apvector of the attributes names
- void from ( apvector token, int numTokens, int fromLocation, bool allAttr ); // takes all table names and stores them in apvector โ fromList;
- bool where ( apvector token, int numTokens, int whereLocation); // checks if WHERE is ound in the query;
- void insert ( apvector token, int startPos, int numTokens ); // gets the table name, checks if it exist in the catalog class and if the column number and the number of the values are the same;
- void valueListFunction ( apvector token, int numTokens, int valPosition, int &numValues, int numColumns ); // makes an apvector of the values to be inserted;
- void columnListFunction ( apvector token, int numTokens, int &lastColumnPosition, int &numColumns ); // makes an apvector of the column names
- void update (apvectortoken, int numTokens, apvector &setArray);
// gets the table name, checks if it exist in the catalog class and makes an apvector of setElement structure with fields attribute name and new value;
- void deleteFunction (apvectortoken, int numTokens); // gets the table name, checks if it exist in the catalog class and deletes the tuples, if WHERE found deletes only the tuples that satisfy the condition;
- void createTable ( apvectortoken, int numTokens ); // gets the table name, makes an apvector of the attr. names and their type check if primary key is found, if not calls error function; makes an apvector of structure for the foreign key โ name and reference table;
- void createIndex ( apvectortoken, int numTokens ); // parses the query for CREATE INDEX;
- void createView ( apvector&token, int numTokens ); // parses the query for CREATE VIEW;
- void dropTable ( apvectortoken, int numTokens ); // first asks user if he(she) wants to drop that table and that if answer is โyesโ โ delete the table and the information for that table in the catalog class;
- void dropIndex ( apvectortoken, int numTokens ); // parses the query for DROP INDEX;
- bool operators( apvector whereList ); // checks if AND, OR, NOT, LIKE, BETWEEN, IN, < , <=, =. >, >= found in the WHERE part of the query and calls the corresponding funtion;
- void setequal(char * attribute, char *value, apstring list); // sets a given attribute of given table to the new value in the UPDATE query;
- void load_info ( List tableList[5], const char *relation, int index); // adding info into the linked list;
- void unload_info ( List tableList[5], const char *relation, int index); // uploading info into the linked list;
- void showList( List mainList[5], int index ); // outputs the data;
- bool equal(char * attribute, char * value, apstring list); // gets the table index from the catalog and the index of the attribute and scans through the linked list to find if the attr. value is โ=โ to the given one from the query;
- bool lessthan(char *attribute, char *value, apstring list);
- void Create_table ( String table_name, String attribute[], int no_of_attributes,//TABLE OPERATIONS (UPDATES THE REQUIRED DATA MEMBERS)
- String type[], String p_k[], String f_k[], String f_k_table[]); //CREATING A TABLE
- bool Drop_table (String table_name); //DROPING A TABLE
- void View_tables(); //DISPLAY THE CONTENTS OF THE TABLE
- //FILE OPERATIONS
- void Write_File();
- void Read_File();
- bool validate_relation (String table_name); //CHECKS IF RELATION AND ATTRIBUTES PRESENT
- bool validate_attribute (String table_name, String attribute);
- //FOR FILE OPERATIONS
- void store_relation_data(String table_file, String column_file);
- void read_relation_data(String table_file, String column_file);
- //RETURNS THE ATTRIBUTES INDEX IN THE TABLE
- int getIndex(String table_name, String attribute);
- //RETURNS THE TABLE INDEX IN TABLES[]
- int getIndex(String tablename);
- //RETURNS INDEX OF PRIMARY KEY
- int getPKIndex(String tablename);
- //RETURNS VALUE OF PRIMARY KEY
- String getPrimary(String tablename);
- //RETURNS NO OF COLUMNS IN THE TABLE
- int no_Columns(String table_name);
- //SETS THE PRIMARY AND FOREIGN KEYS
- int setPrimary(String tablename, String attributename);
- int setForeign(String tablename, String attributename);
Public data members:
- struct tableInfo // TABLES String table; int noAttr;
- struct columnInfo // COLUMN String table; String attribute; String type; bool p_k; bool f_k; String f_k_table;
Private data members:
- String table_name;
- String attribute];
- int tableCounter, columnCounter;
ListNode class: // Facilitator class for the List class
Private member function:
- ListNode ( LE &elem, ListNode *nextPtr ); // Constructor
Private data members:
- LE element; // List element
- ListNode *next; // Pointer to the next element
- friend class List;
List class: Member functions:
- List ( int ignored = 0 ); // Constructor
- List( List &newList); // Constructor
- ~List (); // Destructor
- void insert ( LE &newElement ); // Insert after cursor
- void remove (); // Remove element
- void replace ( LE &newElement ); // Replace element
- void clear (); // Clear list
- void load_info (char *relation); // adding info to all nodes
- void unload_info ( char *relation);
- int empty () const; // List is empty
- int full () const; // List is full
- int gotoBeginning (); // Go to beginning
- int gotoEnd (); // Go to end
- int gotoNext (); // Go to next element
- int gotoPrior (); // Go to prior element
- LE getCursor () const; // Return element
- void operator=(List &newList);
- void showStructure () const; // Output the list structure -- used in testing/debugging
Private data members:
- ListNode *head, // Pointer to the beginning of the list
- *cursor; // Cursor pointer
- friend class test;
Component functionality:
- SELECT: The Parser() function breaks the query in to tokens; select() makes a list of the attributes names ( all tokens between SELECT and FROM ) , another list of the table names and calls the WHERE() function to find the tuples that satisfied the condition.
- INSERT: Takes the table name and the column list, and inserts the values. If the column list if omitted the values are inserted into the table by default โ in the attribute sequence as they are in the catalog.
- UPDATE: The update() function takes the table name and set the new values for the particular attribute. If WHERE statement is found โ it sets the values only for the tuples that satisfy the condition.
- DELETE: The delete() function deletes the tuples for the particular table but to not delete the table in the catalog. If WHERE statement is found โ it delete only that tuples that satisfy the condition.
- CREATE TABLE: The createtable() function creates a catalog table where are stored all table names, the attribute names, the attribute type, which of the attribute or attributes is a primary key, which one is a foreign key in another relation and the name of that relation. The user always has to specify the primary key for the table โ primary key cannot be null.
- CREATE VIEW: The createview() function creates the view as a table and adds the needed information into the catalog table.
- DROP TABLE: The droptable() function completely deletes the table. It deletes the tuples and the information about that table in the catalog table. The user can no longer insert or update that table.
- DROP VIEW: The dropview() function makes the same as the droptable() function.
Sample queries:
Use white space to separate the tokens and semicolon to end the query.
SELECT:
*SELECT ; FROM employee;
SELECT emp#, name, salary FROM employee;
SELECT empl#, name, age FROM employee WHERE age>21;
INSERT:
INSERT INTO employee VALUES (1256, Sam Smith, 10, 28);
INSERT INTO employee (emp#, name, salary, age) VALUES (6745, Ann Nicole, 15, 18);
INSERT INTO NewEmployee SELECT * FROM employee;
UPDATE:
UPDATE employee SET empl# = 1257, name = Sam Simpson, salary = 11, age = 42);
UPDATE employee SET salary = 16; WHERE emp#=6745;
DELETE:
DELETE FROM employee;