Web-DBMS
Web-based Database Management System
Study assignment for IP2, Internet Programming Course II
Martin Rimka
2004-12-06
1 Introduction
1.1 About Web-DBMS
Web-DBMS (or Web-based Database Management System) is a general term for applications which provide a web-based interface to one or more databases. Web-DBMS allows the user to administrate his/her database from home, office, at campus, or on the road. It can be accessed from any platform, including PC, Unix, and Macintosh systems. HTML-based graphical interface of the Web-DBMS applications is easy to use for both developers and users, and it allows to manage databases in a standardized fashion. The network access in Web-DBMS is transparent to the user - the user does not have to spend time or money for setting up expensive and complex network software. In short, the main advantages of Web-DBMS are: accessibility, platform independence, easy-to-use, standardized graphical user interface, and transparent network access.
1.2 Implementation
This Web-DBMS application is written in Java 1.4, standard SQL, and integrates Mixer 0.71, a HTML-template tool. It provides a graphical user interface to MySQL database and is run on Tomcat server. It is a part of the individual study assignment for the Internet Programming II course at DSV, Stockholms University/KTH.
1.3 Goals
The main goal of this application is to test the basic DBMS features in the HTML-based interface, such as creating and altering tables (Data Definition) as well as selecting, inserting, updating, and deleting records (Data Manipulation). The second goal of this application is to separate the graphical interface layer (HTML-source) from the application layer (Java-source). It also attempts to implement some basic user interface features, such as personalized settings. Among its drawbacks, this implementation pays less attention to the security and performance issues, however it enforces some basic authorization and account administration.
1.4 Main Features
Main features of this application are:
- Data Manipulation (select, insert, update, and delete records),
- Data Definition (create, alter, and drop tables),
- Personalized GUI Settings,
- Account Administration.
2 Features and Views
Scenario: To review the main features of the Web-DBMS application, we will use a sample database for a non-profit Formula-1 website. According to the scenario, the Formula-1 website is a collaborative study project between computer science students from several universities in different cities.
The students have decided to share the website development and maintenance workload. One student (administrator), who is in charge of the database and the Web-DBMS application, provides new Web-DBMS accounts for other students (support team members). These students can then login into the Web-DBMS application and begin work on creating the required database tables and completing it with data.
2.1 Login
Figure 1 presents the start page of the Web-DBMS application. In order to login, the users have to enter their username and password. In this example, we login as a support team member called 'Patrik'. The 'support' privilege allows users to enter/edit records in a database and to design/alter database tables.
Figure 1. The 'Login' view
2.2 View Records
In the beginning, the database contains four tables: 'Driver', 'GrandPrix', 'Results', and 'Team'. Patrik created 'Driver' and 'Team', whereas other student, Robert, created 'GrandPrix' and 'Results' tables. They discussed these tables in a brainstorming meeting on the phone one week ago. However, these tables are only in the prototype stage, they are not normalized, and therefore require more work.
Patrik selects (clicks on) the 'Driver' table in the table list on the left side of the application window (Figure 2). The table content is displayed in the center of the window. It has 7 columns ('ID', 'Name', 'Team', 'Country', 'Born', 'GPs', and 'Wins') and 11 records (previously, Patrik entered the driver data of the five best teams in the 2004 Formula-1 season).
Figure 2. The 'Driver' table in the 'View Records' view
2.3 Sort Records
Patrik wants to know who are the youngest drivers in this list. He clicks twice on the column label 'Born' on top of the table. The records get sorted in a descending order (Figure 3). Fernando Alonso is listed first, followed by Jenson Button and Kimi Raikkonen. In other words, records in a table can be sorted (both in ascending and descending order) by clicking on column labels on top of the table. Each time one clicks on a label, the sorting order is reversed. Patrik clicks once on the 'ID' column, to place all records in the original order.
Figure 3. The 'Driver' table, sorted by the 'Born' column in a descending order
2.4 Update Columns
Patrik recognizes that there is one problem with the 'Driver' table: it is not clear what does the 'Team' column refer to. Several drivers included in this table are going to change places in their teams or retire after this season. Juan-Pablo Montoya will drive for the McLaren team next year, while David Couldhard will quit McLaren. Furthermore, there are three drivers in the Renault team, although only two drivers have seats in each team (if we do not count the test driver). The fact is that Jarno Trulli already left the Renault team after Italian GP, whereas Jacques Villeneuve drove for Renault last three races this year - in China, Japan, and Brazil. Hence, the 'Team' column should rather be called 'CurrentTeam', to avoid ambiguity.
He clicks on the 'Design Table' link on top of the application window and opens the 'Design Table' view. Patrik types 'CurrentTeam' in the 'Column Name' text field (Figure 4), and presses the 'Update' button on the same row, to save the changes. The application confirms the changes with the message: "Column 'CurrentTeam' has been successfully updated!"
Figure 4. Updating columns in the 'Design Table' view
2.5 Update Records
After reviewing the driver positions in the Renault team, Patrik becomes less sure if Jacques Villeneuve (who drove only three races for this team and who supposedly has a new contract with the Sauber team for 2005) should have Renault as his current team. Patrik decides to change this record.
He clicks on the 'Edit Records' link on top of the application window and opens the 'Driver' table in the 'Edit Records' view. Before he can make any changes, he has to re-adjust the font size, in order to make the 'Driver' table (and all buttons) fit into the frame. To choose a smaller font size, Patrik selects '10' in the 'Font' menu and presses 'Submit'. Then he makes a new entry '(Renault)' in the 'CurrentTeam' text field next to 'Jacques Villeneuve' (Figure 5), and presses 'Update' to save the changes in the record.
Figure 5. Updating records in the 'Edit Record' view
2.6 Create Table
To reflect the changes in the drivers contracts, Patrik decides to create a new table, called 'Contract'. He clicks on the 'New Table' link below the table list and opens the 'Create Table' view. In the 'Table Name' field, he enters 'Contract' (Figure 6), and presses the button. The application confirms the changes ("Table 'Contract' has been successfully created!") and adds the new table to the table list on the left side of the window.
When the user creates a new table, the Web-DBMS application automatically adds a primary key column 'ID', of integer type and with the 'auto increment' function. This can make the process of creating new tables more user-friendly and faster.
Figure 6. The 'Create Table' view
2.7 Add Columns
As soon as Patrik presses the 'Create Table' button in the previous step, the application returns to the 'Design Table' view (reviewed in section 2.4). Now, when the new table is created, Patrik can add columns to it. He enters column information in the last row, and presses 'Add Column' after each entry (Figure 7). The application confirms the changes in the table design.
In this prototype version, only two column types are supported: 'integer' and 'varchar'. Column length is required for 'varchar'. The length value is later used in the 'Edit Table' view, to determine the length of the text fields.
Figure 7. Adding columns in the 'Design Table' view
2.8 Insert Records
From the 'Design Table' view, Patrik opens the 'Edit Records' view, and enters the new record concerning Jacques Villeneuve's time with Renault. He types in the related ID values from the 'Driver', 'Team', and 'GrandPrix' tables, and adds a short comment (Figure 8). Then he presses 'Insert Record'.
At this point, Patrik decides to end his work session and signs out. His personal font settings (Verdana, 10) are saved in the database for the next session.
Figure 8. Making new entries in the 'Edit Records' view
2.9 Administrate accounts
The database administrator can use the Web-DBMS interface to administrate the database accounts and privileges. For example, to create a new user account, he can simply add a new record in the table containing user information.
3 Bugs and new features
3.1 Known bugs and problems
Known bugs and problems:
- after changing the font settings in the 'Design Table' view, the application returns
to the 'View/Edit Records' views,
- the 'Drop Table' button can be confused with the 'OK' button, because it is situated where
the 'OK' button is usually placed,
- the user may accidentally erase the table without getting a warning first.
3.2 New features and tasks
New features and tasks:
- to fix known bugs and problems,
- add a 'Parrent Table' button next to each column in the 'Design Table' view (which allows
to define this column as a foreign key, related to the parrent table's ID),
- add a view for creating database views or queries, by joining two or more tables with
inner join on their primary and foreign keys,
- allow to update/delete several records at once (by using check boxes),
- allow to search records in a database (one or several tables) by providing a search string,
- allow the user to limit the number of displayed records in the 'View/Edit Records" views,
- implement a simple guestbook or news groups, in order to support the collaboration of the support team.