Fetching latest commit…
Cannot retrieve the latest commit at this time.
|Failed to load latest commit information.|
== DBMS_Prototype # CSC I1000: Database I (graduate), Fall 2011 # Final Project # Student: Yung Lam (Dekus) # Email: email@example.com # Best run by Ruby Interpreter 1.9.3-p0 !!!RECOMMENDED!!! # Second best run by Ruby Interpreter 1.9.2-p290 # Require "ruport" gem!! Can install by "gem install ruport" and "gem install ruport-util" on terminal ---------------------------------------------------- You can enter "exit" or press CTRL + C any time any mode to terminate the program You can enter "show" any time to review all accepted tables you have entered You must enter "quit" if you want to switch mode. Then you will be directed to MODE OPTION to switch mode Since this DBMS is very strict about tuples, there is no such thing as NULL value in a tuple. All tuples must be solid ==================================================== Data Definition: There are 3 modes: Schema, Constraint, Query 1. SCHEMA MODE: a) You can only create tables in this mode. Using "CREATE tb_name ATTR attr(type) [, attr(type) [...]]" e.g. create tb_1 attr A(int), B(INT), C(string), D(STRING) b) A valid table name must begin with a letter. c) A valid table name only consist of letter "a" to "z", "A" to "Z", digits and "_". d) Attribute type can be declared either to lower or upper case only! No mixed cases! Only INT or STRING allowed e.g. A(INT) and A(int) will match while A(Int) and A(iNT) will NOT!! e) Syntax keywords are CASE-INSENSITIVE, that means Create == CREATE == create == CrEatE Please see sample program logs for example!! 2. CONSTRAINT MODE: a) In this mode you will enter Boolean Condition(s), FD(s) and MVD(s) constraints and Keys for the table. b) In order to pick a table to enter constraints, please use "EDIT tb_name" c) Let's state again for all: ALL SYNTAX KEYWORDs ARE CASE-INSENSITIVE!! d) Once you pick a table, the default starting constraint for you to enter is BOOL. You must enter "done" when you are done with each constraint and the key entry. A) BOOL: i) You will enter every AND connected boolean conditions line by line. ii) Each boolean condition must start with an uppercase attribute name, followed by an comparable operator, followed by the value which has the same type as the attribute in that table. iii) If the attribute is of type STRING, only "==" and "!=" are allowed. If the type is INT, you can use all among "==", "!=", ">", "<", "<=", ">=" e.g suppose you have bool conditions : A==2 and B!=" male " or A!=2 or C>=3 you will split them by OR, such as Line 1>> A==2 and B!=" male " Line 2>> A!=2 Line 3>> C>=3 Bool conditions will not matched if OR is present. Every new line is an OR logic bool conditions iv) No conflicting bool conditions will be accepted!! They will be ignored!! v) If you want to add bool condition constraint to a table which already has tuples, you will be prompted for confirmation about the need to erase the whole table because this DBMS is very strict and concerned about constraints!! B) FD: i) You will enter each FD line by line. Each FD starts with a set of attribute name followed by "->" then a set of attribute name. e.g. Line 1>> A->BD Line 2>> BC -> AD Line 3>> C -> A ii) Trivial FD will be ignored! Repeated FD will be automatically taken care of! iii) If you want to add FD constraint to a table which already has tuples, you will be prompted for confirmation about the need to erase the whole table because this DBMS is very strict and concerned about constraints!! iv) Once you "done" with FD constraint, program will automatically check if any of the FDs violate 3NF v) If 3NF is not reached by table's FDs, you will prompted to re-enter all the FDs from scratch!! C) MVD: i) You will enter each MVD line by line. Each MVD starts with a set of attribute name followed by "->>" then a set of attribute name. e.g. Line 1>> A ->> CD Line 2>> C->>A Line 3>> D ->>A ii) Trivial MVD will be ignored! Repeated MVD will be automatically taken care of! iii) If you want to add MVD constraint to a table which already has tuples, you will be prompted for confirmation about the need to erase the whole table because this DBMS is very strict and concerned about constraints!! D) Keys: i) Each key is just a set of attribute, format of "ABCD" ii) If you enter key which is not the minimal key(s), your key will be denied! And you will be given HINTs if you want. iii) You can simply enter "done" to accept program's generated Keys based on the FDs ==================================================== Data Manipulation: When displaying a table, all attributes that are of type string will be enclosed by '"' 3. QUERY MODE: a) Inside QUERY mode you can perform query to all existing tables with the following syntax: A) "LIST tb_name" i) "LIST" will list out all tuples on "tb_name" ii) "LIST tb_name" is alias for "SELECT * FROM tb_name" with no bool conditions applied e.g. QUERY>> list student_table B) "INTO tb_name INSERT val1 [, val2 [, val3 [, val4]]]" i) "INTO ... INSERT ..." will insert enclosed values to the corresponding attribute from "tb_name" ii) The number of value must match with the number of attribute present in the assigning table iii) The values must be in the exact order of the attribute iv) If the inserted tuple complied to any MVD constraint, extra tuples will be automatically generated by applying MVD concept with the newly inserted tuple v) If the new tuples generated by MVD constraint fail to follow FD constraint, the entire tuples insertion will be rolled back to before this insertion query!! e.g. If table "tb1" has attributes A(int), B(string) QUERY>> into tb1 insert "something", "something else" # this will fail because A takes integers only QUERY>> into tb1 insert 123, "abc" # this will be accepted as long as not failing Bool & FD constraints C) "GROUP tb_name BY single_attr" i) This will group "tb_name" based on the one attribute. ii) Namely this will sort all tuples based on that attribute, in ascending order. e.g. QUERY>> group tb1 by B D) "DROP tb_name" i) This will seriously asking for three confirmation before removing "tb_name" existence!! e.g. QUERY>> drop tb1 E) "SELECT */attr1 [, attr2 [, attr3 [, attr4]]] FROM tb_name WHERE complete_bool_conditions" i) You can select all attributes from the table by putting "*", else if selecting more than one attribute, you must comma-separate those attributes. ii) The bool conditions will be any bool conditions connected by AND & OR logic iii) The selecting attributes can be in any order, can even be repeated!! iv) The result will be a formatted table where each tuple satisfies all the given bool conditions e.g. QUERY>> select * from tb1 where A>8 and A<20 and B=="wow" or B!='wow' or C>=10 and B!='hey! ' QUERY>> select D, B from tb1 where A<-10 and C>20 or C<40 A>=-1 QUERY>> select C, B, A, B, C, A, B from tb2 where C<10 F) "DELETE FROM tb_name WHERE complete_bool_conditions" i) The bool condition rule is the same as above in "SELECT...FROM...WHERE..." ii) This will delete any tuple for which the given bool conditions evaluated to true iii) In order to keep MVD constraint valid, table will automatically remove any tuples that are related to the deleted tuples by MVD constraint concept. This DBMS will try to avoid erasing the whole table and try its best to find the solution to the problem brought by deletion iv) You will be prompted for confirmation before any deletion G) "CJOIN / NJOIN / UNION / INTERSECT / DIFF tb1_name , tb2_name" i) "CJOIN" will be "tb1_name" cross join "tb2_name" ii) "NJOIN" will be "tb1_name" natural join "tb2_name" only if they share common attribute, else, empty result iii) "UNION" will union "tb1_name" with "tb2_name" only if, under this DBMS, they have the same schema ( attribute name and type) iv) "INTERSECT" will intersect "tb1_name" with "tb2_name" only if, under this DBMS, they have the same schema ( attribute name and type) v) "DIFF" will take away from "tb1_name" common tuples from "tb2_name" only if, under this DBMS, they have the same schema ( attribute name and type) e.g. QUERY>> cjoin tb1, tb2 QUERY>> cjoin tb3, tb3 QUERY>> njoin tb1, tb3 QUERY>> union tb1, tb2 QUERY>> intersect tb2, tb1 QUERY>> diff tb2, tb1