JAWAHARALAL NEHRU TECHNOLOGICAL UNIVERSITY HYDERABAD

A [Mini] Project Report on Automatic Database Schema Generation .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Submitted in partial fulfillment of the requirement for the award of the Degree of Bachelor of Technology in Information Technology By
<M.PRAVEEN KUMAR <B.ARUN KUMAR <CH.RAJESHWAR REDDY <G.ABHILASH 08C11A1229> 08C11A1206> 08C11A1233> 08C11A1201>

Under the guidance of Mr.P.NiranjanKumarM.Tech

DEPARTMENT OF INFORMATION TECHNOLOGY

ANURAG ENGINEERING COLLEGE

(Accredited by NBA, New Delhi & Approved by AICTE, New Delhi & Affiliated to JNTU, Hyderabad)

ANANTHAGIRI (V), KODAD (M), NALGONDA (DT). A.P. INDIA -508206 Ph: 08683- 272555, 272456, 272454

www.anurag.ac.in 2010 2011

A.E.C-I.T.E

Page 1

ANURAG ENGINEERING COLLEGE

(Accredited by NBA, New Delhi & Approved by AICTE, New Delhi & Affiliated to JNTU, Hyderabad)

ANANTHAGIRI (V), KODAD (M), NALGONDA (DT). A.P. INDIA -508206 Ph: 08683- 272555, 272456, 272454

www.anurag.ac.in

CERTIFICATE
This is to certify that the [Mini] Project Report entitled AUTOMATIC DATABASE SCHEMA GENERATION is submitted by
M.PRAVEEN KUMAR B.ARUN KUMAR CH.RAJESHWAR REDDY G.ABHILASH 08C11A1229> 08C11A1206> 08C11A1233> 08C11A1201>

In the partial fulfillment of the degree of Bachelor of Technology in Information Technology from Jawaharlal Nehru Technological University, Hyderabad during the year 2011- 2012. Date:

Mr.P.NiranjanKumarM.Tech Associate Professor

Y.V.R. Naga Pawan M.Tech. Associate Professor & HOD

Dr. Akash N. Patwari M.Tech., Ph.D.

PRINCIPAL

EXTERNAL EXAMINER

A.E.C-I.T.E

Page 2

ACKNOWLEDGEMENTS
This report will certainly not be completed without acknowledgements paid to all those who have helped me in doing my seminar work. due

I express my sincere thanks to my supervisor Mr. NIRANJAN KUMAR, Assistant Professor for giving me moral support, kind attention and valuable guidance to me throughout this seminar work. It is my privilege to thank Mr. Y.V.R. NAGA PAWAN, Associate Professor & Head of the Department, I.T.E. for his encouragement during the progress of this seminar work. It is my privilege to thank Dr. AKASH N. PATWARI, Principal for his support during the progress of this seminar work. I would like to express my gratitude to our college ANURAG ENGINEERING COLLEGE, KODAD for providing required facilities during this seminar work. I am thankful to both teaching and non-teaching staff members of CSE Department for their kind cooperation and all sorts of help to bring out this seminar work successfully. I would like to thank my parents and my friends for being supportive all the time, and I am very much obliged to them.

A.E.C-I.T.E

Page 3

ABSTRACT

The Automatic Database Schema Generation is a system, which allows the developers to focus on creating the database using the GUI interface .The user needs to fulfill the requirements in the interface and he can perform the database operations which he wants and can create the database. This project aims at creation of an automatic database schema generation. This project will be accessible to all developers and its facility allows developers to focus on creating the database schema on the basis of JSP while letting the application server define table based on the fields in JSP and relationships between them. This system provides the following facilities. This facilitates the user to focus much on application database aspects. This project allows users to generate database schema generation without having much knowledge of database Aspects. aspects leaving behind the

Existing system
There are many Database Management systems available today. The Database designer is familiar with any one of the database Management Systems. Let us consider a condition when a database designer required to design the schema for an application on different DBMS. He required to learn all the DBMS User Interfaces. Where some of them are GUI (Graphic User Interface) based and others are CUI(Character User Interface). DRAWBACKS OF EXISTING SYSTEM Through this approach to alter or to edit a large database schema, It consumes more time for a Database Designer. The operations like altering the table structure, Editing the table, Dropping columns, searching for a column name, searching for a data in the table.. etc.,

A.E.C-I.T.E

Page 4

To Design and alter the Database schema there exists different user interfaces for different DBMS.

Proposed System:
Functionality of the proposed systems The Automatic Database Schema Generation System provides the following features. 1. The Automatic Database Schema Generation System provides a Common User Interface to interact with all the databases. 2. Here the user interface is Graphical User Interface. 3. This application is a Web based Application. 4. Being a web based application it doesnt require any client side installation. 5. Any number of users can interact with the system simultaneously. 6. Centralized database connectivity. 7. Using Session management the interaction more flexible and secure.

A.E.C-I.T.E

Page 5

CONTENTS
1 .Analysis 1.1. Problem Statement 1.2. Solution strategy 1.3. Technical Challenges 1.3.1. Web Technologies: 1.3.2. Middleware Technologies 1.4. Feasibility Report 1.4.1. Technical Feasibility. 1.4.2. Operational Feasibility. 1.4.3. Economic Feasibility. 1.5. Requirement Specifications. 2. Design. 2.1. Unified Modeling Languages Diagrams 2.1.1. UseCase diagram: 2.1.2. Class Diagram: 2.1.3. Sequence Diagrams 2.3. Data Dictionary. 2.3.1. Use cases description: 3. Coding. 4. Results. 5. Testing. 5.1. Levels of Testing 5.2. Types of Testing

A.E.C-I.T.E

Page 6

5.2.1. White Box Testing 5.2.2. Black Box Testing 6. Maintenance. 6.1. SQL Query Pane Module 6.2. Import Option Module 6.3. Export Option Module 6.4. Operations Module 6. 5. Search Database Module 7. Conclusion. 8. Future work. 9. Bibliography. 10. Index.

A.E.C-I.T.E

Page 7

List of Figures
Fig 1.1: basic HTML tags. Fig 1.3:two-tier architecture. Fig 1.4: tree-tier architecture. Fig 2.1: usecase model diagram. Fig 2.2: Usecase diagram of Automatic DB Schema Generation. Fig 2.3: Class Diagram of Automatic DB Schema Generation. Fig 2.4: Sequence diagram for Designer login. Fig 2.5: Sequence diagram for Designer DB operation. Fig 2.6: Sequence diagram for Designer press Structure Button. Fig 2.7: Sequence diagram for Designer press Properties Button. Fig 2.8: Sequence diagram for Designer press SQL Button. Fig 2.9: Sequence diagram for Designer press Import Button. Fig 2.10: Sequence diagram for Designer press Export Button. Fig 2.11: Sequence diagram for Designer press Operation Button. Fig 2.12: Sequence diagram for Designer press Search Button. Fig 3.1:sample login java program. Fig 4.1: DataBase login screen. Fig 4.2: Structure of Scott DataBase screen. Fig 4.3: Structure of Bonus DataBase screen. Fig 4.4: Browse a DataBase screen. Fig 4.5: Properties of SCOTT schema. Fig 4.6: SQL Query Pane. Fig 4.7: Result of SQL Query
A.E.C-I.T.E Page 8

Fig 4.8:Import from SQL file. Fig 4.9: Import scott_emp_ data schema. Fig 4.10: Import succeeded message. Fig 4.12: To save the Export file. Fig 4.13: Save on Desktop. Fig 4.14: Create the table. Fig 4.15: Search the DataBase Fig 4.16: Search Results. Fig 4.17: Logout from the DataBase.

A.E.C-I.T.E

Page 9

List of Acronyms
AWT - Abstract window toolkit. CUI -Character User Interface.

HTTP Hyper Text Transfer Protocol. UML JFC GUI AWT RFT - Unified Modeling language. - Java Foundation Class. - Graphical Interface. - Abstract Window Toolkit. - Rich Text Format.

HTML - Hyper Text Markup Language. API CGI - Application Programming Interface. - Common Gateway interface.

SAPI -Servlet Application Programming Interface JSP URL JDBC SQL - Java Server Pages - Uniform Resource Locator - Java Database Connectivits - Structure Query Language

ODBC - Open Database Connectivity DBMS - Database Management System ACID RAID DDl DML IDE
A.E.C-I.T.E

- Atomicity Consistency Isolation Durability - Redundant array of inexpensive Disk - Data definition Language - Data Manipulation Language - Integrated Development Environment
Page 10

JDT ECJ RCP SWT

- Java Development Tool - Eclipse Compiler for java - Rich Client Platform - Standard widget toolkit

CDT -C/C++ Development tools GET JST PDT -Graphical Editing Framework -J2ee Standard tools -php development tools

WST -web standard tools XML -extended markup language GMF -graphical modeling frame work TPTP -text performance tools platform BIRT -business intelligence and report tools project SQL -software quality assurance

IEEE -Institute of electrical and electronics engineering SDLC -software development life cycle DTD -Document type definition

A.E.C-I.T.E

Page 11

List of keywords

A.E.C-I.T.E

Page 12

organization

A.E.C-I.T.E

Page 13

1 .Analysis
1.1. Problem Statement
1. Through this approach to alter or to edit a large database schema, It consumes more time for a Database Designer. The operations like altering the table structure, Editing the table, Dropping columns, searching for a column name, searching for a data in the table.. etc ., using queries. 2. To Design and alter the Database schema there exists different user interfaces for different DBMS.
3. when a database designer required to design the schema for an application on different DBMS. He required to learn all the DBMS User Interfaces. Where like CUI(Character User Interface).

1.2. Solution strategy

The Automatic Database Schema Generation System provides a Common User Interface to interact with all the databases,and it provides Graphical user interface to easily done the operations like altering the table structure, Editing the table, Dropping columns, searching for a column name, searching for a data in the table.. etc ., Being a web based application it doesnt require any client side installation, Any number of users can interact with the system simultaneously. Using Session management the interaction more flexible and secure

1.3. Technical Challenges

The Automatic Database Schema Generation System is a web based application and it requires Centralized database connectivity, so this project use the web technologies and middleware technologies .

1.3.1. Web Technologies: This project consists following web technologies I. HTML and SGML.

A.E.C-I.T.E

Page 14

II. III. IV.

Java Scripts. Servlets. Java server pages.

I.HTML and SGML Hypertext Markup Language (HTML), the languages of the World Wide Web (WWW), allows users to produces Web pages that include text, graphics and pointer to other Web pages (Hyperlinks). HTML is not a programming language but it is an application of ISO Standard 8879, SGML (Standard Generalized Markup Language), but specialized to hypertext and adapted to the Web. We can navigate through the information based on our interest and preference. HTML can be used to display any type of document on the host computer, which can be geographically at a different location. It is a versatile language and can be used on any platform or desktop. HTML provides tags (special codes) to make the document look attractive. HTML tags are not case-sensitive. Using graphics, fonts, different sizes, color, etc., can enhance the presentation of the document.

Basic HTML Tags : <! ---> Specifies comments Creates hypertext links Formats text as bold Formats text in large font. Contains all tags and text in the HTML document Creates text Definition of a term
Page 15

<A>.</A> <B>.</B> <BIG>.</BIG> <BODY></BODY> <CENTER>...</CENTER> <DD></DD>

A.E.C-I.T.E

<DL>...</DL> <FONT></FONT> <FORM>...</FORM> <FRAME>...</FRAME> <H#></H#> <HEAD>...</HEAD> <HR>...</HR> <HTML></HTML> <META>...</META> <SCRIPT></SCRIPT> <TABLE></TABLE> <TD></TD> <TR></TR> <TH></TH>

Creates definition list Formats text with a particular font Encloses a fill-out form Defines a particular frame in a set of frames Creates headings of different levels Contains tags that specify information about a document Creates a horizontal rule Contains all other HTML tags Provides meta-information about a document Contains client-side or server-side script Creates a table Indicates table data in a table Designates a table row Creates a heading in a table Fig 1.1: basic HTML tags

II.Java Scripts JavaScript is a script-based programming language that was developed by Netscape Communication Corporation. JavaScript was originally called Live Script and renamed as JavaScript to indicate its relationship with Java. JavaScript supports the development of both client and server components of Web-based applications. On the client side, it can be used to write programs that are executed by a Web browser within the context of a Web page. On the server side, it can be used to write Web server programs that can process information submitted by a Web browser and then updates the browsers display accordingly.
A.E.C-I.T.E Page 16

JavaScript statements can be included in HTML documents by enclosing the statements between a pair of scripting tags

<SCRIPTS>.. </SCRIPT>. <SCRIPT LANGUAGE = JavaScript> JavaScript statements </SCRIPT> Fig 1.2: sample javascript program. Here are a few things we can do with JavaScript:
Validate the contents of a form and make calculations. Add scrolling or changing messages to the Browsers status line. Animate images or rotate images that change when we move the mouse over them.

III.Servlets Servlets provide a Java-based solution used to address the problems currently associated with doing server-side programming, including inextensible scripting solutions, platform-specific APIs, and incomplete interfaces. an HTTP Servlets can be used to generate dynamic HTML content. When you use Servlets to do dynamic content you get the following advantages:
Theyre faster and cleaner than CGI scripts They use a standard API (the Servlets API) They provide all the advantages of Java (run on a variety of servers without needing to be

rewritten).
Easily configured using the GUI-based Admin tool Can be loaded and invoked from a local disk or remotely across the network.

A.E.C-I.T.E

Page 17

A client can invoke Servlets in the following ways: The client can ask for a document that is served by the Servlet. The client (browser) can invoke the Servlet directly using a URL, once it has been mapped using the Servlet Aliases section of the admin GUI. The Servlet can be invoked through server side include tags. The Servlet can be invoked by placing it in the Servlets/ directory. The Servlet can be invoked by using it in a filter chain.

IV.Java server pages Java server Pages is a simple, yet powerful technology for creating and maintaining dynamiccontent web pages. Based on the Java programming language, Java Server Pages offers proven portability, open standards, and a mature re-usable component model .The Java Server Pages architecture enables the separation of content generation from content presentation. Java Server Pages files can be run on any web server or web-enabled application server that provides support for them. Dubbed the JSP engine, this support involves recognition, translation, and management of the Java Server Page lifecycle and its interaction components. 1.3.2. Middleware Technologies: The automatic database schema generation requires following middleware technologies, I.Java Database Connectivity. JDBC is a Java API for executing SQL statements. (As a point of interest, JDBC is a trademarked name and is not an acronym; nevertheless, JDBC is often thought of as standing for Java Database Connectivity. It consists of a set of classes and interfaces written in the Java programming language. JDBC provides a standard API for tool/database developers and makes it possible to write database applications using a pure Java API. Using JDBC, it is easy to send SQL statements to virtually any relational database. One can write a single program using the JDBC API, and the program will be able to send SQL statements to

A.E.C-I.T.E

Page 18

the appropriate database. The combinations of Java and JDBC lets a programmer write it once and run it anywhere. What Does JDBC Do? Simply put, JDBC makes it possible to do three things:
Establish a connection with a database Send SQL statements Process the results.

The JDBC provides database-independent connectivity between the J2EE platform and a wide range of tabular data sources. JDBC technology allows an Application Component Provider to: Perform connection and authentication to a database server Manager transactions Move SQL statements to a database engine for preprocessing and execution Execute stored procedures Inspect and modify the results from Select statements

II.JDBC versus ODBC and other APIs At this point, Microsoft's ODBC (Open Database Connectivity) API is that probably the most widely used programming interface for accessing relational databases. It offers the ability to connect to almost all databases on almost all platforms. In the two-tier model, a Java applet or application talks directly to the database. This requires a JDBC driver that can communicate with the particular database management system being accessed. A user's SQL statements are delivered to the database, and the results of those statements are sent back to the user.

A.E.C-I.T.E

Page 19

JAVA Application JDBC

Client machine

DBMS-proprietary protocol

DBMS

Database server

Fig 1.3:two-tier architecture.

Java applet or Html browser

Client machine (GUI) HTTP, RMI, or CORBA calls

Application Server (Java)JDBC

DBMS

Server machine (business Logic) DBMS-proprietary protocol Database server

DBMS

Fig 1.4: tree-tier architecture. In the three-tier model, commands are sent to a "middle tier" of services, which then send SQL statements to the database. The database processes the SQL statements and sends the results back to the middle tier, which then sends them to the user. JDBC versus ODBC and other APIs At this point, Microsoft's ODBC (Open Database Connectivity) API is that probably the most widely used programming interface for accessing relational databases. It offers the ability to connect to almost all databases on almost all platforms. So why not just use ODBC from Java? The answer is that you can use ODBC from Java, but this is best done with the help of JDBC in the form of the JDBC-ODBC Bridge, which we

A.E.C-I.T.E

Page 20

will cover shortly. The question now becomes "Why do you need JDBC?" There are several answers to this question: 1. ODBC is not appropriate for direct use from Java because it uses a C interface. Calls from Java to native C code have a number of drawbacks in the security, implementation, robustness, and automatic portability of applications. 2. A literal translation of the ODBC C API into a Java API would not be desirable. For example, Java has no pointers, and ODBC makes copious use of them, including the notoriously error-prone generic pointer "void *". You can think of JDBC as ODBC translated into an object-oriented interface that is natural for Java programmers. 3. ODBC is hard to learn. It mixes simple and advanced features together, and it has complex options even for simple queries. JDBC, on the other hand, was designed to keep simple things simple while allowing more advanced capabilities where required. 4. A Java API like JDBC is needed in order to enable a "pure Java" solution. When ODBC is used, the ODBC driver manager and drivers must be manually installed on every client machine. When the JDBC driver is written completely in Java, however, JDBC code is automatically installable, portable, and secure on all Java platforms from network computers to mainframes.

1.4. Feasibility Report

1.4.1. TECHINICAL FEASIBILITY: Evaluating the technical feasibility is the trickiest part of a feasibility study. This is because, at this point in time, not too many detailed design of the system, making it difficult to access issues like performance, costs on (on account of the kind of technology to be deployed) etc. A number of issues have to be considered while doing a technical analysis. i) Understand the different technologies involved in the proposed system: Before commencing the project, we have to be very clear about what are the technologies that are to be required for the development of the new system. ii) Find out whether the organization currently possesses the required technologies: o Is the required technology available with the organization?
A.E.C-I.T.E Page 21

o If so is the capacity sufficient? For instance Will the current printer be able to handle the new reports and forms required for the new system?

1.4.2. OPERATIONAL FEASIBILITY: Proposed projects are beneficial only if they can be turned into information systems that will meet the organizations operating requirements. Simply stated, this test of feasibility asks if the system will work when it is developed and installed. Are there major barriers to Implementation? Here are questions that will help test the operational feasibility of a project: Is there sufficient support for the project from management from users? If the current system is well liked and used to the extent that persons will not be able to see reasons for change, there may be resistance. Are the current business methods acceptable to the user? If they are not, Users may welcome a change that will bring about a more operational and useful systems. Have the user been involved in the planning and development of the project? Early involvement reduces the chances of resistance to the system and in General and increases the likelihood of successful project.

Since the proposed system was to help reduce the hardships encountered. In the existing manual system, the new system was considered to be operational feasible. 1.4.3. ECONOMIC FEASIBILITY: Economic feasibility attempts 2 weigh the costs of developing and implementing a new system, against the benefits that would accrue from having the new system in place. This feasibility study gives the top management the economic justification for the new system. A simple economic analysis which gives the actual comparison of costs and benefits are much more meaningful in this case. In addition, this proves to be a useful point of reference to compare actual costs as the project progresses. There could be various types of intangible
A.E.C-I.T.E Page 22

benefits on account of automation. These could include increased customer satisfaction, improvement in product quality better decision making timeliness of information, expediting activities, improved accuracy of operations, better documentation and record keeping, faster retrieval of information, better employee morale. 1.5. REQUIREMENT SPECIFICATION: The application Automatic Database Schema Generation, which is design to connect different DBMS using common Interface. All the major activities that are carried are used to Design the database schema transparent to the DBMS by using a GUI.

Required Hardware
Pentium IV Processor. 256 MB RAM. 40GB Hard Disk space. Ethernet card with an Internet and Internet zone.

Required Software
Server Side: Platform Independent (Based on Operating System). Internet explorer 5.0 or above or Netscape navigator. Any Centralized Database. JDK 1.4 or higher. Apache Tomcat Web Server 4.0 or above. TCP/IP Protocol suite.

Client Side: Any Web Browser on any operating system.

A.E.C-I.T.E

Page 23

2. DESIGN
2.1. UNIFIED MODELING LANGUAGE DIAGRAMS: The unified modeling language allows the software engineer to express an analysis model using the modeling notation that is governed by a set of syntactic semantic and pragmatic rules. A UML system is represented using five different views that describe the system from distinctly different perspective. Each view is defined by a set of diagram, which is as follows. User Model View i. This view represents the system from the users perspective. ii. The analysis representation describes a usage scenario from the end-users perspective.

Structural model view In this model the data and functionality are arrived from inside the system. This model view models the static structures.

Behavioral Model View It represents the dynamic of behavioral as parts of the system, depicting the interactions of collection between various structural elements described in the user model and structural model view. Implementation Model View

In this the structural and behavioral as parts of the system are represented as they are to be built.

Environmental Model View

A.E.C-I.T.E

Page 24

In this the structural and behavioral aspects of the environment in which the system is to be implemented are represented. UML is specifically constructed through two different domains they are

UML Analysis modeling, which focuses on the user model and structural model views of the system. UML design modeling, which focuses on the behavioral modeling, implementation modeling and environmental model views.

Use case Diagrams represent the functionality of the system from a users point of view. Use cases are used during requirements elicitation and analysis to represent the functionality of the system. Use cases focus on the behavior of the system from external point of view. Actors are external entities that interact with the system. Examples of actors include users like administrator, bank customer etc., or another system like central database.

A.E.C-I.T.E

Page 25

Use case Model.

SYSTEM NAME

Use case 1

Actor

Actor

Use case 2

Fig 2.1: usecase model diagram

Use case n

A.E.C-I.T.E

Page 26

2.1.1. Use Cases diagram:

AUTOMATIC DB SCHEMA GENERATION

Database Login
SCHEMA MODIFICATION

DATABASE SEARCH

Database Schema Designer

EXPORT SCHEMA IMPORT SCHEMA

Database logout

Fig 2.2: Usecase diagram of Automatic DB Schema Generation.

A.E.C-I.T.E

Page 27

2.1.2. Class Diagram: Class diagrams describe the structure of the system in terms of classes and objects. The servlet api class diagram will be as follows.

Fig 2.3: Class Diagram of Automatic DB Schema Generation.

A.E.C-I.T.E

Page 28

2.1.3. Sequence Diagrams

Sequence Diagrams Represent the objects participating the interaction horizontally and time vertically.

Sequence Diagram 1

: Login
DB Schema Designer

: Load All

: Log Out

Use url Press login button

Press disconnect Button

Time

Fig 2.4: Sequence diagram for Designer login.

A.E.C-I.T.E

Page 29

Sequence Diagram 2

: Login
DB Schema Designer

: Load All

DB Operations

Use url Press login button

Press Operations Button

Fig 2.5: Sequence diagram for Designer DB operation.

A.E.C-I.T.E

Page 30

Sequence Diagram 3

: Login
DB Schema Designer

: Load All

: Sturcutre

Use url Press login button

Press Structure Button

Fig 2.6: Sequence diagram for Designer press Structure Button.

Sequence Diagram 4

: Login
DB Schema Designer

: Load All

: Properities

Use url Press login button

Press Properities Button

Fig 2.7: Sequence diagram for Designer press Properties Button.

A.E.C-I.T.E Page 31

Sequence Diagram 5

: Login
DB Schema Designer

: Load All

: SQL

Use url Press login button

Press SQL Button

Fig 2.8: Sequence diagram for Designer press SQL Button.

Sequence Diagram 6

: Login
DB Schema Designer

: Load All

: Import

Use url Press login button

Press Import Button

Fig 2.9: Sequence diagram for Designer press Import Button

A.E.C-I.T.E

Page 32

Sequence Diagram 7

: Login
DB Schema Designer

: Load All

: Export

Use url Press login button

Press Export Button

Fig 2.10: Sequence diagram for Designer press Export Button.

Sequence Diagram 8

: Login
DB Schema Designer

: Load All

:Operations

Use url Press login button

Press Operations Button

Fig 2.11: Sequence diagram for Designer press Operation Button.

A.E.C-I.T.E Page 33

Sequence Diagram 9

: Login
DB Schema Designer

: Load All

: Search

Use url Press login button

Press Search Button

Fig 2.12: Sequence diagram for Designer press Search Button.

2.3. DATA DICTIONARY

2.3.1. USECASE description: Use case name Participating actors Flow of events Database Login Schema Designer

DB Schema Designer gives the database driver name, selects the url, enters user name and password. On success connection is established to the database.

Entry Condition Exit condition

A.E.C-I.T.E

The DB Designer Logged into the system automatic database schema generation. The DB Designer Disconnects from the system automatic database
Page 34

schema generation Quality Requirements Must provide respective error messages during connection.

Use case name Participating actors Flow of events

Schema Creation Schema Designer

The DB Designer uses the user interface, to create tables, generate the schema, the database responds on successful schema creation The user must enter the table names, column names and their datatypes.

Entry Condition Exit condition Quality Requirements

Successful creation User Interface must be user friendly,

Use case name Participating actors Flow of events

Schema Modification Database Designer

The DB Designer uses the user interface to modify the tables, column names, column content, data types. The user must select the respective table, column and data for the modification Successful modification Best Error Handling techniques.

Entry Condition Exit condition Quality Requirements

A.E.C-I.T.E

Page 35

Use case name Participating actors Flow of events

Search Database Designer

The DB Designer enters the search key and must select the criteria to search like column names, data or both. Optional is the search condition too. User must have knowledge of what he required

Entry Condition Exit condition Quality Requirements

Result set displayed on the User interface. Easy viability in displaying the records.

Use case name Participating actors Flow of events

Export schema Database Schema Designer

The user must select the schema or tables or columns to be exported in the required formats like into sql,html,excel and also what to be exported like structure, data, both. User must be ready with what tables to be exported.

Entry Condition Exit condition Quality Requirements

Save the file in the specified location. File insertion must be error free.

A.E.C-I.T.E

Page 36

Use case name Participating actors Flow of events

Import schma Database Schema Designer

The user select the file to be imported here it will support only SQL format. The user must search for where the sql file exists

Entry Condition Exit condition Quality Requirements

Successful import of the schema from sql file. The file must be read the delimiters carefully for successful import

A.E.C-I.T.E

Page 37

3. CODING
Sample code: Login.java:import java.lang.String; import java.io.IOException; import java.io.PrintWriter; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpSession; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class Login extends HttpServlet { public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { String message = req.getParameter("message"); res.setContentType("text/html"); PrintWriter writer = res.getWriter(); writer.println("<HTML>"); writer.println("<HEAD>"); writer.println("<TITLE> Myadmin </TITLE>");
A.E.C-I.T.E Page 38

writer.println("<META NAME=Author CONTENT=vamsi>"); writer.println( "<STYLE>" + "#t1 { " + " " " " " " " " " position:absolute; " + margin-left:210px; " + margin-top:50px; " + width:340px; " + height:33px; " + background:#336699; " + font-family:Serif; " + font-weight:bold; " + font-size:20px; " + " " " " " "} " + "#t2 { " + " " position:absolute; " + margin-left:210px; " + color:#ffffff; " +

text-align:center; " + border-style:double; " + border-color:black; " + border-width:2px; " +

A.E.C-I.T.E

Page 39

" " " " " " " " " " " "} " +

margin-top:84px; " + width:340px; " + height:180px; " + background:#f5f5f5; " + font-family:Tahoma; " + font-weight:bold; " + font-size:14px; " + color:black; " + border-style:groove; " + border-color:black; " + border-width:1px; " +

"#msg {" + " " " " " " " "} " + background:#abcdef; " + font-family:Tahoma; " + font-size:14; " + font-weight:bold; " + color:black; " + text-align:center; " text-transform:Capitalize; " +

A.E.C-I.T.E

Page 40

"input { " + " " " "} " + "select { "</STYLE>"); writer.println( "<SCRIPT LANGUAGE=javascript>" + " " var onImages = new Array(); " + font-size:16px; }"+ height:26px; " + width:240px; " + font-size:14px; " +

var offImages = new Array(); " +

"function verify() { " + " " " " " " "} " + "function isEmpty(id) { " + " " if(id.value==null || id.value==\"\" || id.value==\" \") { " + alert(id.name + \" must be entered.\"); " + }"+ else document.loginform.submit(); " + var ur = document.loginform.url.value; " + if(ur == null || ur == \"\" || ur == \" \") { " + alert('URL must be entered.'); " + document.loginform.url.focus(); " +

A.E.C-I.T.E

Page 41

" " "} " + }"+

id.focus(); " +

"function empty() { " + " " " "} " + "function down(id) { " + " 1].src; " + " 1].src; " + "} " + "function up(id) { " + " 1].src; " + " 1].src; " + "} " + "function load() { " + " onImages[0] = new Image(); " + if(id == 2) document.loginform.pic2.src = offImages[idif(id == 1) document.loginform.pic1.src = offImages[idif(id == 2) document.loginform.pic2.src = onImages[idif(id == 1) document.loginform.pic1.src = onImages[iddocument.loginform.reset(); " + document.loginform.url.value = \"jdbc:odbc:mydsn\"; " + document.loginform.userid.focus(); " +

A.E.C-I.T.E

Page 42

" " " " " " " " " "} " +

onImages[1] = new Image(); " + onImages[0].src = \"/httpadmin/pics/login2.jpg\"; " + onImages[1].src = \"/httpadmin/pics/reset2.jpg\"; " + offImages[0] = new Image(); " + offImages[1] = new Image(); " + offImages[0].src = \"/httpadmin/pics/login1.jpg\"; " + offImages[1].src = \"/httpadmin/pics/reset1.jpg\"; " + document.loginform.url.value = \"jdbc:odbc:mydsn\"; " + document.loginform.userid.focus(); " +

"function setURL() { " + " " \"jdbc:odbc:mydsn\"; " + " \"jdbc:mysql:/localhost/mysql\"; " + " else if(i == 2) document.loginform.url.value = else if(i == 1) document.loginform.url.value = var i = document.loginform.driver.selectedIndex; " + if(i == 0) document.loginform.url.value =

\"jdbc:oracle:thin:@localhost:1521:oracle8i\"; " + "} " + "</SCRIPT>"); writer.println("</HEAD>"); writer.println("<BODY BGCOLOR=#f5f5f5 onLoad=load()>");

A.E.C-I.T.E Page 43

writer.println("<HR WIDTH=80%>"); writer.println("<TABLE WIDTH=80%"); writer.println("<TR>"); writer.println("<TH id=msg>" + message + "</TH>"); writer.println("</TR>"); writer.println("</TABLE>"); writer.println("<HR WIDTH=80%>"); writer.println("<FORM ACTION='/httpadmin/LoadAll'>"); writer.println("<TABLE LOGIN</TD></TR></TABLE>"); writer.println("<TABLE align=center id=t2>"); writer.println("<TR><TD align=center>Driver</TD>"); writer.println("<TD align=center>"); writer.println("<SELECT name=driver onChange=setURL()>"); writer.println("<OPTION selected>sun.jdbc.odbc.JdbcOdbcDriver</OPTION>"); writer.println("<OPTION>org.gjt.mm.mysql.Driver</OPTION>"); writer.println("<OPTION>oracle.jdbc.driver.OracleDriver</OPTION>"); writer.println("</SELECT>"); writer.println("</TD>"); writer.println("</TR>");
A.E.C-I.T.E Page 44

ALIGN=center

CELLPADDING=4

BORDER=0

NAME=loginform

METHOD=post

align=center

id=t1><TR><TD>DATABASE

writer.println("<TR><TD align=center>URL</TD>"); writer.println("<TD align=center><INPUT type=text name=url size=32></TD></TR>"); writer.println("<TR><TD align=center>Username</TD>"); writer.println( "<TD align=center><input type=text name=userid size=32 " +

"onFocus='isEmpty(document.loginform.url)'></TD></TR>"); writer.println("<TR><TD align=center>Password</TD>"); writer.println( "<TD align=center><input type=password name=pass size=32 " +

"onFocus='isEmpty(document.loginform.url)'></TD></TR>"); writer.println("<TR><TD align=center colspan=2>"); writer.println( "<IMG name=pic1 src='/httpadmin/pics/login1.jpg' border=0 " + "onMouseOut='up(1)' onMouseDown='down(1)' " + "onMouseUp='up(1)' onClick='verify()'>&nbsp&nbsp&nbsp"); writer.println( "<img name=pic2 src='/httpadmin/pics/reset1.jpg' border=0 " + "onMouseOut='up(2)' onMouseDown='down(2)' " + "onMouseUp='up(2)' onClick='empty()'>"); writer.println("</TD></TR>"); writer.println("</TABLE>"); writer.println("</FORM>");
A.E.C-I.T.E Page 45

STYLE='cursor:hand'

STYLE='cursor:hand'

writer.println("</BODY>"); writer.println("</HTML>");

writer.close(); } } Fig 3.1:sample login java program.

A.E.C-I.T.E

Page 46

4. RESULTS

SCREEN SHOT:

Fig 4.1: DataBase login screen.

A.E.C-I.T.E

Page 47

Structure of DataBase:

Fig 4.2: Structure of Scott DataBase screen.

A.E.C-I.T.E

Page 48

Structure of Bonus DataBase:

Fig 4.3: Structure of Bonus DataBase screen.

A.E.C-I.T.E

Page 49

Browse a Database:

Fig 4.4: Browse a DataBase screen.

A.E.C-I.T.E

Page 50

Properties of SCOTT schema:

Fig 4.5: Properties of SCOTT schema.

A.E.C-I.T.E

Page 51

SQL Query Pane:

Fig 4.6: SQL Query Pane.

A.E.C-I.T.E

Page 52

Result of SQL Query:

Fig 4.7: Result of SQL Query.

A.E.C-I.T.E

Page 53

Import from SQL file:

Fig 4.8:Import from SQL file.

A.E.C-I.T.E

Page 54

Import scott_emp_ data schema:

Fig 4.9: Import scott_emp_ data schema.

A.E.C-I.T.E

Page 55

Import succeeded message:

Fig 4.10: Import succeeded message.

A.E.C-I.T.E

Page 56

Export the Table:

Fig 4.11: Export the Table.

A.E.C-I.T.E

Page 57

Export the SQL file:

Fig 4.11: Export the SQL file.

A.E.C-I.T.E

Page 58

To save the Export file:

Fig 4.12: To save the Export file.

A.E.C-I.T.E

Page 59

Save on Desktop:

Fig 4.13: Save on Desktop.

A.E.C-I.T.E

Page 60

Create the table:

Fig 4.14: Create the table.

A.E.C-I.T.E

Page 61

Search the DataBase:

Fig 4.15: Search the DataBase.

A.E.C-I.T.E

Page 62

Search Results:

Fig 4.16: Search Results.

A.E.C-I.T.E

Page 63

Logout from the DataBase:

Fig 4.17: Logout from the DataBase.

A.E.C-I.T.E

Page 64

5. TESTING

Testing is the process of detecting errors. Testing performs a very critical role for quality assurance and for ensuring the reliability of software. The results of testing are used later on during maintenance also. Psychology of Testing The aim of testing is often to demonstrate that a program works by showing that it has no errors. The basic purpose of testing phase is to detect the errors that may be present in the program. Hence one should not start testing with the intent of showing that a program works, but the intent should be to show that a program doesnt work. Testing is the process of executing a program with the intent of finding errors. Testing Objectives The main objective of testing is to uncover a host of errors, systematically and with minimum effort and time. Stating formally, we can say,

Testing is a process of executing a program with the intent of finding an error. A successful test is one that uncovers an as yet undiscovered error. A good test case is one that has a high probability of finding error, if it exists. The tests are inadequate to detect possibly present errors. The software more or less confirms to the quality and reliable standards.

5.1. Levels of Testing

In order to uncover the errors present in different phases we have the concept of levels of testing. The basic levels of testing are as shown below
A.E.C-I.T.E Page 65

Acceptance Testing

System Testing

Integration Testing

Unit Testing

Client Needs

Requirements

Design Code

Fig 5.1:Levels of Testing.

System Testing The philosophy behind testing is to find errors. Test cases are devised with this in mind. A strategy employed for system testing is code testing. Code Testing: This strategy examines the logic of the program. To follow this method we developed some test data that resulted in executing every instruction in the program and module i.e. every path is tested. Systems are not designed as entire nor are they tested as single systems. To ensure that the coding is perfect two types of testing is performed or for that matter is performed or that matter is performed or for that matter is performed on all systems.

5.2. Types of Testing:

A.E.C-I.T.E Page 66

 Unit Testing Link Testing Unit Testing Unit testing focuses verification effort on the smallest unit of software i.e. the module. Using the detailed design and the process specifications testing is done to uncover errors within the boundary of the module. All modules must be successful in the unit test before the start of the integration testing begins. In this project each service can be thought of a module. There are so many modules like Login, HWAdmin, MasterAdmin, Normal User, and PManager. Giving different sets of inputs has tested each module. When developing the module as well as finishing the development so that each module works without any error. The inputs are validated when accepting from the user. In this application developer tests the programs up as system. Software units in a system are the modules and routines that are assembled and integrated to form a specific function. Unit testing is first done on modules, independent of one another to locate errors. This enables to detect errors. Through this errors resulting from interaction between modules initially avoided. Link Testing Link testing does not test software but rather the integration of each module in system. The primary concern is the compatibility of each module. The Programmer tests where modules are designed with different parameters, length, type etc. Integration Testing After the unit testing we have to perform integration testing. The goal here is to see if modules can be integrated properly, the emphasis being on testing interfaces between modules. This testing activity can be considered as testing the design and hence the emphasis on testing module interactions. In this project integrating all the modules forms the main system. When integrating all the modules I have checked whether the integration effects working of any of the services by
A.E.C-I.T.E Page 67

giving different combinations of inputs with which the two services run perfectly before Integration. System Testing Here the entire software system is tested. The reference document for this process is the requirements document, and the goal os to see if software meets its requirements. Here entire ATM has been tested against requirements of project and it is checked whether all requirements of project have been satisfied or not. Acceptance Testing Acceptance Test is performed with realistic data of the client to demonstrate that the

software is working satisfactorily. Testing here is focused on external behavior of the system; the internal logic of program is not emphasized. In this project Network Management Of Database System I have collected some data and tested whether project is working correctly or not. Test cases should be selected so that the largest number of attributes of an equivalence class is exercised at once. The testing phase is an important part of software development. It is the process of finding errors and missing operations and also a complete verification to determine whether the objectives are met and the user requirements are satisfied.

5.2.1.White Box Testing

This is a unit testing method where a unit will be taken at a time and tested thoroughly at a statement level to find the maximum possible errors. I tested step wise every piece of code, taking care that every statement in the code is executed at least once. The white box testing is also called Glass Box Testing. I have generated a list of test cases, sample data, which is used to check all possible combinations of execution paths through the code at every module level.

5.2.2. Black Box Testing

A.E.C-I.T.E Page 68

This testing method considers a module as a single unit and checks the unit at interface and communication with other modules rather getting into details at statement level. Here the module will be treated as a block box that will take some input and generate output. Output for a given set of input combinations are forwarded to other modules. Criteria Satisfied by Test Cases Test cases that reduced by a count that is greater than one, the number of additional test cases that much be designed to achieve reasonable testing. Test cases that tell us something about the presence or absence of classes of errors, rather than an error associated only with the specific test at hand.

A.E.C-I.T.E

Page 69

6. MAINTENANCE
Introduction to Modules

1) SQL Query Pane Module Here we can type SQL queries and get the data from Database 2) Import Option Module We can import .sql files only into the Database 3) Export Option Module We can export sql Structure, sql Data or both based on some conditions into 3 types of files i. ii. iii. .sql files .html files .csv files(Excel Files)

4) Operation Module i. ii. iii. iv. v. vi. Here we can create table with constraints Alter the existing table like Alter column Rename column Drop column Empty column

5) Search Database Module i. ii.

A.E.C-I.T.E

Search based on keywords Search looks for column names only

Page 70

iii.

Search looks for data only

Number of Modules:

6.1. SQL Query Pane Module This module is used to retrieve data from the database by using SQL Queries. The User interface is designed with a query pane to type the query, where a user can type the sql query. The sql query is taken by the module as input and generates the output of that query. The output is displayed in a tabular manner.

6.2. Import Option Module This module is developed to retrieve schema from the external files. The supported files are .sql files. .sql files contains the set of queries that can be executed. This module uses the FTP protocol which will enable the web server to load the files.

6.3. Export Option Module The user can export sql Structure, sql Data or both based on some conditions into 3 types of files.

.sql files .html files .csv files(Excel Files)

A.E.C-I.T.E

Page 71

6.4. Operations Module The user can create table with constraints, Alter the table, Rename the Table, Drop table. The user can Alter the existing table like by using following keys. o Alter column o Rename column o Drop column o Empty column

The user can rename the table by giving the old name and new name. The user can drop the table by selecting the table name from the select box. 6. 5. Search Database Module The user must search based on keywords o Search looks for column names only o Search looks for data only

A.E.C-I.T.E

Page 72

7. CONCLISION
The entire project has been developed and deployed as per the requirements stated by the user, it is found to be bug free as per the testing standards that are implemented. Any specification untraced errors will be concentrated in the coming versions, which are planned to be developed in near future. The system at present does not take care off the money payment methods, as the consolidated constructs need SSL standards and are critically to be initiated in the first face; the application of the credit card transactions is applied as a developmental phase in the coming days. The system needs more elaborative technicality for its inception and evolution.

A.E.C-I.T.E

Page 73

8. FUTURE WORK

Scope of the Development Project:

Database Tier: The concentration is applied by adopting wide range of DBMS. User Tier: The user interface is developed in a browser specific environment to have web based architecture. The components are designed using HTML standards and Java server pages power the dynamic of the page design. Java Script for Loading graphics and Validations. Data Base Connectivity Tier: The communication architecture is designed by concentrated on the standards of servlets and JSPs. The database connectivity is established using the JDBC.

A.E.C-I.T.E

Page 74

9. Bibliography

References for the Project Development Were Taken From the following Books and Web Sites:

1. JAVA Technologies, 2. JAVA Complete Reference by Herbert Sheildt and Patrick Naughton, 3. Java Script Bible Gold Edition, 4. Web Enable Commercial Application Development Using Java 2.0 by Ivan Bayross, 5. Professional Java Server Programming Subhramanyam Allamaraju. , 6. Inside Servlets. 7. Java.sun.com tutorials. 8. Software Engineering by Roger Pressman. 9. Object-Oriented Software Engineering Using UML, Pattern and Java Second Edition by Bernd Bruegge, Allen H.Duetoit.

A.E.C-I.T.E

Page 75