Online Database Management System

(1)

CPSC 624 Project

!

Online Database Management System

!

Bo Li / Runzhen Wang

!

Introduction

!

This is an online Database Management System that provide a friendly user interface to users to use the database. Users can manage their databases on the web page through a Internet browser, they don't need to interact with the command line interface, all they need to do is just click the buttons.

!

Background

!

This system beneﬁts the person who don’t have the basic knowledge of database, but have to manage data on databases.

!

As we know, in the daily life, many people should need to use a database to manage information, for example, a shop owner may use a database to manage the products of his shop, he may want to know how many products are sold everyday and other information. But unfortunately, the free and open source database MySQL only provides a ugly command line interface, and a shop owner may not have any knowledge of programming, it is very difficult for him to use MySQL database.

!

Motivations

!

As mentioned in the background section, the shop owner may feel it is difficult to use MySQL system, however, by using this online database management system, now, he can learn how to use MySQL database in minutes, and he can manage the database by clicking the buttons. That's cool!

!

User's Manual

!

This system provides a friendly user interface to users, all of the operations to the database can be done through the web page, and users don't need to login to the MySQL command interface.

!

This is the main page of our system, it has three important functions.

(2)

Insert Data

!

The first one is called Nightly Process, double click the link you will enter its interface.

!

As mentioned before, this manage system is based on a specific database as an example, the example is called Northwind Database. The Northwind Database is a database of customers and suppliers.

!

To insert information into the database, you just need to upload a text file which contains the data you want to insert, and click the “submit” button!

!

(3)

!

The data file looks like this:

!

col1 col2 col3 …! serperate by ‘\t’!

!

Download Data

!

You can download all the data of each table from the second interface, Download Data.

!

In this web page, you can download each database table easily.

!

Show Data

!

The third function of this system is the most important one.

(4)

!

You can browse your database through the web page interface, don't need any command!

!

First of all, I will introduce the function of each columns.

!

The most left column is the Table List, it shows all the tables in your database, in this case, the database is NorthWind.

!

The second column is the attributes of the table which you select from the table list. Once you click one item of the left table, then every attributes of the this table will be shown in the table_columns.

(5)

!

The third column is the most important column. By clicking the arrow, you will decide whether the attribute of the second column will display in this column.

!

Consider a SQL statement,

!

SELECT attr FROM table WHERE attr=somthing ORDER BY rules;

!

The Ouput is the attr field, it decides which attributes will be selected and displayed. For example, we can choose Customers City in the table_columns, and click the first arrow, that means the Customers

City attribute will be displayed in the SQL query result.

(6)

!

Of course, you also can select many attributes to be displayed in the query result, these attributes can from different items in table lists.

!

Then, you can continue to select one item of the table_columns, for example, Customers Country, and add it to the Order by column.

!

The Order by column corresponding to the ORDER BY key words in the SQL statements.

(7)

For example, we can order by the Production Information table's attribute Products Category ID.

!

The last one is the Constrains, in the SQL query statements, it corresponding to the WHERE key word, if we select a attribute as the constraint, then the SQL will return the entries which matches the

WHERE conditions.

!

For example, if we want to see the products which price is bigger than $10, we can do as follow.

(8)

!

One point I want to mention is that, when you click the Constrains arrow, it will not just put the attributes which you select from table_column in the input area, instead, it will display a dialog box, in this box, you can select many conditions, such as great than, less than, equal to, like and so on.

!

Once you select the condition and put in the value, click the 'done' button, the constrains will display in the column, it is very convenient.

!

However, the right side of the column also has some button, from the button's name, we can know what function they provide.

!

For example, by clicking the UP arrow button or DOWN arrow button, you can change the position of every attributes in the input area.

!

The original five items are arranged like this.

(9)

!

We can change their position, so after the

querying, database will return the

attributes in different order.

!

Clicking the ASC/DESC button, it will tells database to return result in Ascending Order or Descending Order.

!

The DELETE button can delete a item in the input area.

!

The Edit button can modify the constraint conditions.

!

After you have put in all the conditions, you can click the 'show SQL ' button to see the SQL statements which is generated by our database management system.

!

For example, if we want to see some information from the Product table, and we are only interested in the products whose price is greater than $10.

!

(10)

!

SELECT Product_ID, Product_Unit_Price FROM Product_Information WHERE

Product_Unit_Price > 10 ORDER BY Product_Unit_Price;

!

However, a person without any programming skill also can do this by clicking the mouse!

!

You can see that our database management system also print the same SQL statements!

!

Then you can click the 'submit' button to execute the SQL query statement, and the system will print the results.

(11)

!

Conclusions and future work

!

At present, we finished the basic functions of this system, and didn't do much work to make it website more beautiful, in the future, we will rewrite the user interface and use more web page design

technologies.

!