Forms and Relationships Modules for A Java Applet-Based Database Management Interface

snottybugbearΛογισμικό & κατασκευή λογ/κού

3 Νοε 2013 (πριν από 3 χρόνια και 9 μήνες)

78 εμφανίσεις

1

Florida State University
College of Arts and Sciences





Forms and Relationships Modules for
A Java Applet-Based Database Management Interface


by Harsha Avinash Panasa
Spring, 2013









A Master’s project submitted to the
Department of Computer Science
In partial fulfillment of the requirements for the
Degree of Master of Science



Major Professor: Dr. Daniel G. Schwartz
Committee: Dr. Mike Burmester, Dr. Peixiang Zhao








2

TABLE OF CONTENTS


ACKNOWLEDGEMENTS .......................................................................................................................... 3
ABSTRACT .................................................................................................................................................. 4
1. INTRODUCTION ................................................................................................................................ 5
1.1. Background and Motivation.......................................................................................................... 5
1.2. Anatomy of this Document ........................................................................................................... 5
2. SYSTEM REQUIREMENTS ............................................................................................................... 5
2.1 Task List for project ...................................................................................................................... 5
2.2 Functional Requirements .............................................................................................................. 6
2.3 Nonfunctional Requirements ........................................................................................................ 6
3. SYSTEM DESIGN ............................................................................................................................... 7
3.1. Graphical User Interface ............................................................................................................... 7
3.1.1. Modifying Existing Layout ................................................................................................... 7
3.1.2. Relationship Module Design and Working ........................................................................... 7
3.1.3. Forms Module Design and Working ................................................................................... 11
4. SYSTEM IMPLEMENTATION ........................................................................................................ 19
4.1. Overview ..................................................................................................................................... 19
4.2 Test Cases ................................................................................................................................... 20
5. FUTURE WORK ................................................................................................................................ 20
6. CONCLUSION ................................................................................................................................... 21
7. REFERENCES ................................................................................................................................... 21





3


ACKNOWLEDGEMENTS

The first person I need to thank and be grateful to is my major professor, Dr. Daniel G.
Schwartz. The help, advice and support he has provided throughout has helped me successfully
finish this project. Meeting him was always a wonderful experience and being with him helped
me learn a lot of things on both professional and personnel level.
Next, I would like to thank Dr. Mike Burmester and Dr. Peixiang Zhao for agreeing to
serve on my committee and for their constant criticism and advice.
There are many other people who have helped me directly or indirectly in the completion
of this project that I need to thank. I also need to thank the whole faculty of the Computer
Science Department at Florida State University for providing me with the resources needed.
Finally, I need to thank my parents P. Rama Mohan Rao and P. V. Padmaja and my
best friend S. Saketh Theerdha for standing by me all the time and encouraging me throughout
which made my work really easy in completing this project successfully. This would have never
been possible without your love and support.



4

ABSTRACT
This Java Applet-Based Database Management Interface is a java based application which was
inspired initially from the way Microsoft Access 2003 works. This interface has been designed
such that it can be operated within a web browser and can be easily used in order to manage and
maintain the SQL friendly databases remotely over a network. The architecture of this system is
developed in a client-server paradigm. Here, all the MySQL relational databases are created and
maintained by the server side of the system and the client is a Java applet that runs under a web
browser. This interface can run independently on any interface as it has been developed using
JAVA. SOAP technology which is a versatile technology and which can adapt to various
transport protocols has been used while designing the communication between the client and the
server.
This project has mainly concentrated on the FORMS and the RELATIONSHIPS modules of the
interface with the special emphasis of providing the editing, saving and manipulating the data in
the forms module. Through this interface now the users can create relationships between two
tables and can select various options for specifying check constraints, on delete and on cascade
options for foreign keys successfully. Once the relationships have been created, they can go
ahead and create forms based on these related tables. The forms which are created are made
editable such that the user can easily manipulate, add, or delete data from the form interface
only. The user has a flexibility to choose among the table view or the justified view while
creating the forms. Also a lot of changes have been made to the table’s module in order to allow
adding, changing and deleting data from the interface itself. Additionally, the help module has
been updated in order to include the required information that can be used by the user in getting
familiar with how this system can be used.
The graphical user interface has been redesigned so that the whole interface looks more cleanly
and close to Microsoft access 2003. In all, the overall contributions of the project have greatly
expanded the capabilities of the system and made it more user-friendly.











5

1. INTRODUCTION
1.1. Background and Motivation
Microsoft Access has been in market for quite a few years now and has many features that make
working with databases quite user friendly for many users who intend to create databases and
tables or who want to run queries on an existing database. It provides excellent graphical user
interface which can be used by the programmers in order to get the functionality that they intend
to get without a need for them to code. Right from creating a database to querying or creating
forms to reports everything is handled by the system. The main motivation for our project came
from this idea, which intends to make the path really easy for the users by giving them an
interface which can be used by a naïve user with no prior knowledge of coding in achieving the
desired results. Many students have been working on this project with a goal of creating an
interface which is very similar to that of the Microsoft Access 2003. In this project we have
worked on the forms and the relationship module. The existing system had a lot of bugs while
creating a relationship between two tables and in this project we have made sure that the
relationship module is working in the way it was intended to work and allow users to define
check constraints and on-cascade and on-delete options on the foreign keys. Also, the forms
module in the existing system was only static, whereas now in this project we have made editing
possible in the form itself where a user can save, modify or delete the data via the form. This
editable feature was missing in the previous version of the project. This feature makes the forms
module very similar to that of the forms module in the Microsoft Access in both look and feel.
1.2. Anatomy of this Document
The document is divided into numerous sections as follows. Section 2 discusses about the
various system requirements that are required by this project. In this section we discuss about the
list of tasks that have been set up for completing this project and then we discuss about the
various functional and non-functional requirements for the project. Section 3 discusses about
how the system was designed and modified in order to incorporate all the required features into
the project. It clearly explains the working of the modified relationship and forms module.
Section 4 discusses about how the project was implemented and evaluated. We have created a
number of test cases that were used to verify and make sure that the project was running in the
way it was desired to work. Section 5 discusses some of the works that can be carried out in the
future in order to improve the project in many ways which make it more useful.
2. SYSTEM REQUIREMENTS
2.1 Task List for project
We have developed a task list in order to make sure that the completion of the project is smooth.
Let us briefly discuss what the task list contained,
• Reading java book
• Reading various e-books based on Java Swings
• Reading a book based on MySQL
6

• Study the forms module in the MS Access 2003 Bible
• Studying, understanding and reviewing the existing project Code
• Changing the layout of the existing project
• Creating and modifying the module for relationships
• Creating and modifying the module for forms
• Deleting the previous unused modules
• Generating various test cases and verifying our project based on it
• Modifying help
• Writing the document
I have started by reading a lot of books based on Java and MySQL to get familiar with what I
was working with. I have collected a lot of information of what features I could incorporate in
my project. Moving on I have studied the forms module in MS Access 2003 Bible to get a closer
look and feel of how closely the forms module in this interface should look like. After I have
gathered all the paper knowledge that I needed, I moved on to explore and understand the
features of the existing code and made a note of all the points that could possibly be extended.
In the next phase, based on the observations I have made on studying the code I have made
changes to the relationship module in order to eliminate the existing bugs and make sure the
tables were related in a right way. Also I made sure that the user could add check constraints and
on cascade and on-delete options for the foreign keys. Once I have made sure that the
relationship module is working right, I moved on to develop the forms module. Now, the
developed forms module does not limit the user only in creating forms but it allows him in
adding, modifying, saving and deleting data directly from the forms.
Following these, I have cleaned the code to make sure there were no unused pieces of code or
any previously developed code from the current project. Finally, I have developed many test
cases in order to make sure that this interface works as desired and updated the help module to
help users in getting to know how the interface works.
2.2 Functional Requirements
For this project there are three main functional requirements which are as follows,
• The existing relationships module must be modified in order to make sure it relates the
tables in a right way and allows the user to specify the various check constraints and on-
cascade and on-delete options on the foreign keys.
• The existing forms module must be modified in order to allow users to add, modify, and
delete data directly from the form itself.
• Any unused buttons or code must be eliminated.
2.3 Nonfunctional Requirements
There are also a number of non-functional requirements for this project. Some of which are
mentioned below,
• The user interface must be very user friendly such that any user with minimal
programming skills can work his way through.
7

• The look and feel of the interface must be as close as possible to that of the MS Access
2003.
• The project should be flexible and extensible such that any programmer in the near future
can extend the functionalities of this project without facing much difficulty.
3. SYSTEM DESIGN
3.1. Graphical User Interface
There have been a lot of changes made to the existing graphical interface and the functionalities.
Below are the complete descriptions of these changes.
3.1.1. Modifying Existing Layout
Keeping in mind the main requirement which was to maintain the look and feel of the MS
Access 2003 software I have made a few changes. First one was to limit the empty columns in
tables and forms to just one. Secondly I made sure that while creating a new form only the
required tables are shown and the base tables are avoided. Next, I added the delete button in the
justified view for the form. Also I have made changes to the status and notification messages
which now lead the user to a better understanding of what his status is in or notifies him about an
error he made. I have made modifications to the relationship base table such that it now displays
the exact relationship between the tables. Now, let see the working of the new features in both
relationship and forms module.
3.1.2. Relationship Module Design and Working
3.1.2.1. Relationship Handling
The Relationship module is common for table’s module, queries module and also forms module.
The relationship module gets activated whenever any of these modules are activated. The
relationship window pops up whenever you click the relationship button.

Figure 1 Relationship window

There are mainly two panels in the relationship module. One panel is used to display the existing
relationships and contains four columns describing what tables are related on what columns.
Each row of this panel represents a relationship. The second panel is used to display the buttons
8

which can be used to create a new relationship, or delete an existing relationship, or edit an
existing relationship or close the window. The edit and delete buttons are passive if there are no
relationships existing.
3.1.2.2. Creating a New Relationship
On clicking create button, the user will see a new relationship window. This relationship window
can be used by them to create a new relationship between the existing tables.

Figure 2 New Relationship

There are four panels in this window. The first panel consists of four combo boxes which are
used to select the tables that they want to create and the columns on which they want to relate the
tables on. Once the tables and columns are selected, the relationship type (One-One, One-Many,
Many-One, or Many-Many) is automatically determined based on the primary key and displayed
in the relationship type label below.
9


Figure 3 New Relationship

3.1.2.3. Maintaining Referential Integrity
The next panel gives the user an option to maintain referential integrity. The Cascade Delete and
Cascade Update options are enabled only when the Enforce Referential Integrity option is
selected. If none of the options are selected then the relationship is useful only for query usages
and cannot handle data consistency.

Figure 4 Referential Integrity in Relationships

10

The next panel allows users to specify the join conditions that are useful in querying the data.
The user has an option to select between three join conditions which are Inner join, Left outer
Join, Right outer Join.
3.1.2.4. Deleting an Existing Relationship
In order to delete an existing relationship, the user has to select the existing relationship and then
click on the delete button. There will be a successful deletion message that is displayed to notify
the user that the relationship has been successfully deleted.

Figure 5 Relationship deletion

3.1.2.5. Modifying an Existing Relationship
In order to edit an existing relationship, the user has to select the existing relationship and then
click on the edit button. The changes can be made and saved. The user is notified with a message
displaying that the changes have been successfully made.

Figure 6 Modifying a Relationship

11

3.1.3. Forms Module Design and Working
3.1.3.1. Forms Handling
The following shows you the forms window. The main panel displays the forms that are created. If
there are no forms then the panel is empty. The next panel has the Open, Design, Delete, New, Help,
Exit and Relations button that can be used by the user in order to create a new form or open, design
or delete an existing relationship.

Figure 7 Forms Window

3.1.3.2. Creating a New Form
In order to create a new for the user has to select the new button. Once the new button is selected, a
new form window opens up. The first panel allows you to select the tables. After the table is selected
all the corresponding columns are displayed in the available fields in the second panel. Now the user
has a flexibility to choose the fields he need into the selected fields. I have modified the code in order
to make sure that the primary keys of the tables are automatically added into the selected fields. This
would make sure that while the user adds data through the form later then the primary key violation
is avoided.
12


Figure 8 New Form


Figure 9 New Form

13

When user clicks next, he gets another window where he can choose the layout of the form he wants
to create. There are two types of options that he can choose from. The first one gives you a justified
view and the second one gives you a tabular view.

Figure 10 New Form: Layout

The next step in the form creation is to choose a name for the form and then clicking save which will
save the form.

Figure 11 New Form: Naming

14


Figure 12 New Form: Saving


Figure 13 New Form: Success Message

Once the form is saved it is displayed in the main panel.

Figure 14 Display Form

15

3.1.3.3. Editing an Existing Form
There are two layouts of forms. The first one is the tabular layout and the next one is the justified
layout. Let us see how these layouts look and how a user can edit data in them.

3.1.3.3.1. Tabular Layout
The form’s tabular layout displays the data in the form of columns and rows where the columns
represent the various fields selected while creating the form and the rows contain the actual data.

Figure 15 Form: Tabular View

The user now has an option to add, modify or delete the data from the form itself. He can add a new
row by entering the data inside the empty row that is available, he can alter the existing data by
selecting it and writing it again and then he can delete a row by selecting it and clicking on the delete
button. Once he clicks save the corresponding changes happen and the results are stored in the base
table.

Figure 16 Success Message

16

3.1.3.3.2. Justified Layout
The second layout is the justified layout where the form is displayed in a justified view.

Figure 17 Form: Justified Layout

In order to add data inside the justified view the user has to go all the way to the end of the fields and
then fill in the required information and can delete the information by clicking on the delete button. If
he intends to change the existing information, then he modifies the information by clicking on it.
Once he has done making the changes he has to click on save button which makes sure that the data
entered is saved in the base tables.

Figure 18 Form: Editing Justified Layout

17


Figure 19 Success Message

3.1.3.4. Deleting an Existing Form
In order to delete an existing form, the user has to select the existing form and then click on the
delete button. There will be a confirmation dialogue box that requires the user to confirm
whether he is sure about deleting the form or not. The form is deleted only when the user click
on yes.

Figure 20 Form: Deletion

3.2.4. Drop Modules
There were some unused buttons in the older version of this project which lead to some weird
behavior of the interface. All these buttons were cleared and the code is made really neat. Now the
interface does not have such unused buttons which lead to unnecessary error or warning messages.
3.2.5. Modifying Help
The help module has also been modified and updated such that all these changes that have been made
to the project are reflected in it. I have added a new user manual for relationship creation and forms
18

creations. User can easily scroll through this help menu and find all the information they need while
creating, modifying or deleting relationships or forms. The following screenshots show how the help
is displayed.

Figure 21 Help: Form Operation

Figure 22 Help: Form Record Operation
19


Figure 23 Help: Form Field Operation

4. SYSTEM IMPLEMENTATION
4.1. Overview
This project is developed using Java and uses the java swing features extensively in order to create
the user interface. It is largely based on the Simple Object Access Protocol (SOAP) and uses the
MySQL server as the backend. Now let us discuss about the various tools and technologies that we
used for developing this project,
• Java Swing:
Java swing classes have been widely used in creating the graphical user interface for our system. We
have used a java applet which runs under the Java Virtual machine in order to run as a client on any
web browser. As JVM executes only the java byte code, any application that is developed by java can
run on any system independently. This makes our interface very portable and we can run our
interface on any system.
In this project, we have vastly made use of the Java swing API’s such as JTables, JTextArea,
JButtons, JLabel and many more. These rich swing classes have helped us in creating a very user
friendly user interface. One advantage of using java swing API’s is that these are not limited to
specific platforms and can run on any different platform. Also one plus point of using java applets is
that they are more interactive and can perform in various operating systems and can execute faster as
most of the browsers can cache the applet data.
20

• MySQL
We have used MySQL as the back end for this interface. There are numerous reasons as to why we
have chosen MySQl. Its available for free of cost and can run on many operating systems and it has a
very user friendly graphical interface which can be used by the users to run queries or create and
delete the tables and databases and also provides a DOS interface to run commands. It is a relational
database management system that suits our project needs.
• SOAP
The communication between the client and the server paradigm is completely based on the Simple
Object Access Protocol (SOAP). SOAP is a protocol that is based on XML and can be easily
configured to run with various protocols such as HTTP, SMTP etc. Many popular software such as
Apache Axis are purely based on SOAP.
• Net Beans:
We have used NetBeans IDE 7.0 for implementing and developing this interface. There are many
IDE’s such as MyEclipse etc. which provide similar environment for development. But NetBeans
comes with many features that allow the user the complete resources he need to develop large scale
software. One of the most advantageous features of NetBeans is that it has a very user friendly
interface. Also NetBeans is available for free of cost on web and there are various versions available
for Linux, Solaris and Windows.
4.2 Test Cases
We have created many test cases in order to check the working of various functionalities of our
developed modules. We have run our interface against these test cases and figured out the program
ran as expected and made sure that there are no bugs in it. First set of test cases included relating
various tables and to see whether the relationships are created as expected or not. Next set of test
cases included in checking whether the forms are being created as expected and whether the user is
able to enter, modify and delete the data from the form itself or not. The interface had passed all the
test cases and is working as it was intended to.
5. FUTURE WORK
Many students have worked on this project before on various modules and tried their best in
developing the interface as close as possible to feel and look like the Microsoft Access 2003. I have
mainly worked on the Relationship and Forms module and added additional functionalities. Still
there is much more improvement that can be done on this interface.
Students can work on developing a graphical interface where users can just drag and drop the fields
or connect them using some graphical connectors in order to create relationships and forms. This
would be make the user experience much better and would give them more flexibility. Another
aspect where development can be done is the area related to security. Nothing much has been done in
that particular area and students can work in providing a safe and secure environment in which the
interface can run avoiding various malicious attacks. It is highly advised to work on the security
aspect of the project in order to avoid the attacks from hackers.
21

Additionally, students can concentrate on developing the user interface and redesign the whole
interface as to make it look very new and fancy that could help the user experience.
6. CONCLUSION
Quite a few students have worked on developing various modules for this interface. The previously
developed relationship module had many bugs and was not able create check constraints and add on-
cascade and on-delete options on the foreign keys. I have made sure that the code is modified in such
a way that now the tables are related in the way they were supposed to be related and allow users to
create check constraints and define on-delete and on-cascade options on the foreign keys. Also the
existing forms module did not allow the users to edit and modify the data directly from the forms
module. Now I have added and modified code that enables this functionality and allows the users to
directly add, modify and delete data from the forms module itself. There is still a lot of scope for
development that can be done and I hope students take up this project and start working on the
additional functionalities. On a finishing note, I have learnt and gained a lot of knowledge by taking
up this project and would sincerely thank my advisor in leading me through all the while.
7. REFERENCES
[1] Dietel, H.M. Dietel & P.J. "Java How to Program: Fourth Edition ." Upper Saddle River, New
Jersey: Prentice Hall, 2002.
[2] Eckel, Bruce. "Thinking in Java: Third Edition." Upper Saddle River, New Jersey: Prentice Hall,
2003.
[3] Horstmann, Cay S. "Computing Concepts with Java 2: Second Edition ." New York : John Wiley
& Sons, Inc., 2000.
[4] MySQL. <http://www.mysql.com>.
[5] Silberschatz, Abraham. "Database System Concepts: Fourth Edition." Mc Graw Hill, 2002.
[6] SOAP. <http://www.w3.org/TR/soap12-part1/#intro>.
[7] Ullman, Larry. "PHP and MySQL for Dynamic Web Sites." Peachpit Press, 2003.