pasxsenger/build-our-dbsys

Course Project of DSCI 551

Build-our-DBSys

This is the course project of DSCI 551.
Author: Xuesong Shen & Haoze Zhu

System Structure

Function Diagram

Relational

Dataset

NBA games data

File Name	File Format	File Size
teams	csv	4 KB
players	csv	266 KB
ranking	csv	15.5 MB
games	csv	4.1 MB
games_details	csv	93.1 MB

Non-relational

Dataset

Indonesia's Top E-Commerce Tweets

File Name	File Format	File Size
bliblidotcom_stats	json	31.4 MB
bliblidotcom_tweet	json	20.9 MB
bukalapak_stats	json	27.9 MB
bukalapak_tweet	json	17.7 MB
lazadaID_stats	json	72.4 MB
lazadaID_tweet	json	43 MB
ShopeeID_stats	json	145.5 MB
ShopeeID_tweet	json	82 MB
tokopedia_stats	json	17.5 MB
tokopedia_tweet	json	11.1 MB

Query Language

0. Database and Table

Creating a Database

Format: Create a new database named [database name];
Example: Create a new database named NBA;

Using a Specific Database

Format: Switch to database [database name];
Example: Switch to database NBA;

Deleting a Database

Format: Delete database [database name];
Example: Delete database NBA;

Showing Databases

Format: Databases;
Example: Databases;

Creating a Table

Format: Set up a new table named [table name] with columns [column names];
Format: Set up a new collection named [collection name] with [json object];
Example: Set up a new table named teams with columns team_id, city, owner;
Example: Set up a new collection named teams with {"ID": "1", "Name": "USC"};

Dropping a Table

Format: Drop table/collection [table/collection name]; Example: Drop table teams; drop collection teams;

Showing Tables

Format: Tables; Format: collections; Example: Tables;

1. Retrieving Data (Projection & Filtering)

Format: Show [column/field(s)]/[all] of [table/collection] where [condition(s)] [line m-n];
Example: Show team_id, city of teams where city = LA line 2-10;

2. Connecting Tables (Block-based Nested Loop Join)

Format: Connect [table1/collection1] with [table2/collection2] based on [common feature];
Example: Connect teams with players based on team_id = team_id;

3. Grouping and Aggregation

Format: Summarize [column/field] on [column/field]/[all] from [table/collection] using [aggregation];
Example: Summarize yearfounded on teams_id from teams using min;
Aggregation: avg/sum (numeric only); count/min/max

4. Sorting Data (External Merge Sort)

Format: Sort [table/collection] by [column/field] in [asc/desc] order;
Example: Sort teams by yearfounded in asc order;

5. Inserting Data

Format: Add [row] to [table/collection];
Example: Add John Doe, Guard to players;

6. Updating Data

Format: Change [column/field] to [new value] for [table] with [condition];
Example: Change position to forward for players with name = John Doe;

7. Deleting Data

Format: Remove row with [condition] from [table];
Example: Remove row with name = John Doe from players;

System operation

Change the current path to “ourDB/Code”, then run “python3 main.py”.

To enter the SQL database system, run the command “sql”;

To enter the NoSql database system, run the command “nosql”;

Run the command “databases;” to see all databases in the database system.

To use the database nba in the SQL databases, run the command “switch to database nba;”.

To show the tables in the database, run the command “tables;”.

To use the database eco in the NoSQL databases, run the command “switch to database eco;”.

To show the collections in the database, run the command “collections;”.

After switching to a specific database, one can use other query operations or CRUD commands on that database.

To exit the SQL database system, run the command “exit”.

All the runtime files in the running folders will be deleted at the same time.

To exit ourDB, run the command “exit”.