CS 564 Database Management Systems - Project Part 6: Minirel Query and Update Operators

CS 564 Project Part 6 CourseNana.COM

Minirel Query and Update Operators CourseNana.COM

See the following page for an overview of the Minirel project CourseNana.COM

This project has four stages: 3-6 (see the bottom of the page). So far, you have done Stages 3-4. As discussed in the class, you will NOT have to do Stage 5. Instead, you will move directly to Stage 6. You can find the link to Stage 6 at the bottom of the above page. The direct link is: CourseNana.COM

To do Stage 6, you do have to understand Stage 5. So you should read Stage 5 carefully before starting on Stage 6. The direct link to Stage 5 is: After reading Stage 5, start working on Stage 6 by downloading the Zip file called "part6.zip", under "Files" in Canvas. Submission Instruction Each project group should create a directory named after their group name. Suppose the group name is G6. Then the directory is "G6". In this directory, please place the files select.C, insert.C, and delete.C (which we have asked you to change) and a file G6.txt, which lists the full names and emails of all group members. Zip the directory into a file G6.zip, then upload to Canvas. Each group should have just ONE member uploading this zip file. To upload, click on "Assignments", then on Project Stage 6. You should see a button that allows you to upload the zip file. The above instruction is for a fictional group named G6. You should modify it accordingly, using your true group name. IMPORTANT: 1) You should have been editing only the above three files. So you should upload only those files (plus the G6.txt file). We will add your three files to our code and compile. Make sure that your code compiles and passes the tests ON CS MACHINES, as we will compile your code using CS machines. 2) You must submit by the deadline. No exception. Even if you have not passed all the tests, just submit what you have by the deadline. Introduction For this project stage, you will implement facilities for querying and updating Minirel databases. Specifically, you will implement selection, projection, insertion, and deletion. The parser that we will provide will parse the SQL-like commands and make the appropriate calls to the back-end. SQL DML Commands Minirel implements a simplified version of the SQL query language. The syntax of this language is described below using pseudo context-free grammar that is a continuation of the CourseNana.COM

grammar described in Part 5 of the project. Recall that optional parts of statements are enclosed in square brackets. A DML statement can either be a query or an update. CourseNana.COM

::= | We now describe the format of a query. ::= SELECT [into relname] FROM [WHERE ] The result of a query is either printed on the screen or stored in a relation named relname. To simplify the first case (i.e. print to the screen), the Minirel query interpreter creates a temporary relation which it then prints and destroys. Thus, in both cases, the name of a result relation is provided and will be passed to function SQ_Select (see below). If no qualification is specified, then the query simply prints the columns requested in the target list. ::= (relname1.attr1, relname2.attr2, ..., relnameN.attrN) This is a list of attributes attr1, attr2, …, attrN from relations relname1, relname2,... relnameN respectively that constitutes the target list of a query. All attributes in the target list should come from the same relation, except when the qualification is a join, in which case the attributes can come from the two relations begin joined . The "relnameN" qualifier can only be omitted if the query is on a single relation. It can be an alias of a relation if the alias is defined in TABLE_LIST. Please refer to (see below) for more of relation alias. The projection should be performed on the fly while the qualification is being evaluated, i.e. while the selection or join is being processed. You should not create a temporary relation with the result of the selection or join and then project on the desired columns. You do not have to worry about eliminating duplicates. ::= (relname1 [alias1], relname2 [alias2],... relnameN [aliasN]) This list defines the target table list of a query. Aliases for each relation can be specified optionally. Once an alias is specified, it can be used as relation qualifier in TARGET_LIST and QUAL clause. ::= | Qualifications are very simple, either a selection or a join clause. ::= relname.attr value A selection condition compares an attribute with a constant. The "relname" qualifier can be omitted if the query is on a single relation, or can be an alias if the alias is defined in TABLE_LIST clause. ::= relname1.attr1 relname2.attr2 A join condition compares the join attribute value of one relation with another.  The "relname" qualifier can be an alias if the alias is defined in TABLE_LIST clause. Non-equi joins can only be performed using the nested loops join method. 4/18/23, 10:07 PM Project Part 6 ::= | An update is either an insert or a delete operation i.e. you cannot use Minirel to modify the value of an existing tuple in a relation. ::= DELETE FROM relname [where ] This operations specifies the deletion of tuples that satisfy the specified selection condition. ::= INSERT INTO relname (attr1, attr2,... attrN) VALUES (val1, val2,... valN) This will insert the given values as a tuple into the relation relname. Note that the values of the attributes may need to be reordered before the insertion is performed in order to conform to the offsets specified for each attribute in the AttrCat table. You can do this by using memcpy to move each attribute in turn to its proper offset in a temporary array before calling insertRecord. Implementing the Relational Operators For this part of the project you will need to implement the following routines. They will be called by the parser with the appropriate parameter values in response to various SQL statements submitted by the user. Thus you should not add or remove parameters unless you are prepared to modify the parser. const Status QU_Select(const string & result, const int projCnt, const attrInfo projNames[], const attrInfo* attr, const Operator op, const char *attrValue) A selection is implemented using a filtered HeapFileScan. The result of the selection is stored in the result relation called result (a heapfile with this name will be created by the parser before QU_Select() is called). The project list is defined by the parameters projCnt and projNames. Projection should be done on the fly as each result tuple is being appended to the result table. A final note: the search value is always supplied as the character string attrValue. You should convert it to the proper type based on the type of attr. You can use the atoi() function to convert a char* to an integer and atof() to convert it to a float. If attr is NULL, an unconditional scan of the input table should be performed. const Status QU_Join(const string & result, const int projCnt, const attrInfo projNames[], const attrInfo *attr1, const Operator op, const attrInfo *attr2) Since time is short, you do not have to implement this operator. You might look at it to figure out how to implement select.C. const Status QU_Delete(const string & relation, const string & attrName, const Operator op, const Datatype type, const char *attrValue) This function will delete all tuples in relation satisfying the predicate specified by attrName, op, and the constant attrValue. type denotes the type of the attribute. You can locate all the qualifying tuples using a filtered HeapFileScan. const Status QU_Insert(const string & relation, const in attrCnt, const attrInfo attrList[]) 4/4Insert a tuple with the given attribute values (in attrList) in relation. The value of the attribute is supplied in the attrValue member of the attrInfo structure. Since the order of the attributes in attrList[] may not be the same as in the relation, you might have to rearrange them before insertion. If no value is specified for an attribute, you should reject the insertion as Minirel does not implement NULLs. Getting Started Download the Zip file for this stage (your instructor will give the precise instruction). This Zip file includes an implementation of the solution to part 5. Here is a list of the key files you need to implement. select.C - Stub for QU_Select. You have to implement this. join.C - Stub for QU_Join. We already implemented this for you. insert.C - Stub for QU_Insert. You have to implement this. delete.C - Stub for QU_Delete. You have to implement this. Testing The test files are named qu.1, qu.2, etc. in directory "testqueries". Files qu.1, qu.5, and qu.7 test selection/project, insertion, and deletion without indexing. At the minimum, your code should work for these files. Feel free to augment these files and use the augmented versions to further evaluate your code. Other files in "testqueries" test the above plus join, supposedly with indexing, except that we have turned off the indexing step for now. You can use them to further test your code. You can run the above files by manually feeding the commands in the files to your code, or you can modify the script "qutest", and then use it to run the above files.