Given project is a command line Database management system which supports DDL, DML, and VDL commands. It supports DDL commands like create database, create table, drop table, DML commands like insert, delete and update, VDL commands like select.
• Download and extract ntv170030_cs6360.rar.• Code files:
o DBMSPrompt.java
o FileUtils.java
o Records.java
• Go to MyDBMS/src directory and Compile all java files.
• Run DBMSPrompt.
• In the project all the code files must be in the MyDBMS/src and the databases will get created in MyDBMS/database. These both directories must exist in the project as per given structure.
• End Every SQL commands with semicolon.
• All commands are case insensitive.
• Our primary key is row_id which is auto incremental, and it is by default added in every table.
• We are not allowing the user to add any other Primary key.
• create database ;
• Create database with name .
• It creates 2 directories, user_data and catalog.
• Inside catalog, 2 .tbl files (table files) metadata_columns.tbl and metadata_tables.tbl are created.
2. Use database
• use ;• To perform any operation within database it is crucial command.
• To perform operations on table, use database first.
• If you create new database than from that point by default it will use that database.
3. Drop database
• drop database ;• It drops the database and all tables within that database
4. Show databases
• Show databases;• It displays all the databases exist in the system.
5. Create table
• create table ( [is null],….);• To create a table, a database must be selected with use command.
• Table should not already exist in the database.
• To create a table at least one column_name and its datatype is necessary
• Here, table file has been created with extension .tbl
• The name of table is stored in the metadata_tables.tbl file.
• The details of column with its row_id, table name, column name, datatype, position, IsNull are stored in the netadata_columns.tbl file.
6. Show table
• Show tables;• To show tables, a database must be selected with use command.
• It displays all the tables within that database.
7. Show columns
• Show columns;• To show columns, a database must be selected with use command.
• It displays all the columns within that database.
8. Drop table
• Drop table ;• To drop the table a database must be selected.
• Table must exist in the database.
• All the relevant entries of given table is removed from metadata_table.tbl and metadata_columns.tbl
9. Insert data
• Insert into values(, ,…);• Insert into (, ,…) values(, ,…);
• To insert values in table, a database must be selected with use command.
• Table name must be provided.
• Table should already exist in the database.
• If no column names are provided then number of values must be equal to total number of columns in the table.
• If the name of columns is provided then they must be equal to the name of columns in the database.
• Input datatype should be similar to the datatype provided in the table definition.
10. Select data
• Select * from ;• Select ,… from ;
• Select * from where =;
• Select ,… from where =;
• To select values from table, a database must be selected with use command.
• Table name must be provided.
• Table should already exist in the database.
• If where key word is written, then the condition must be provided.
• Condition columns and select columns must match to the columns in the tables.
• Only one condition after where clause is supported.
11. Update table values
• Update set =;• Update set = where =;
• To select values from table, a database must be selected with use command.
• Table name must be provided.
• Table should already exist in the database.
• If where key word is written, then the condition must be provided.
• Condition columns and select columns must match to the columns in the tables.
• Only one condition after where clause is supported.
• You can edit only one column at a time. (Multiple column updates are not supported).
12. Delete data
• delete from ;• delete from where =;
• To delete value from table, a database must be selected with use command.
• Table name must be provided.
• Table should already exist in the database.
• If where key word is written, then the condition must be provided.
• Condition columns and select columns must match to the columns in the tables.
• All the rows for which condition is satisfied are deleted.
• If no where clause is provided, then all the rows from the table are deleted.
13. Help
• Help;• Basic syntax of the above queries are provided as guideline.
14. Version
• Version;• Database name, version and name of author is provided.
15. Exit
• Exit;• To exit the database StormSQL exit is used.
- Quit • Quit; • To exit the database StormSQL quit command is used.