Integrated Sri Lankan University Information System B.P. Manage and G.N. Wikramanayake Department of Statistics and Computer Science, University of Colombo, Colombo 3. E-mail:
[email protected] and
[email protected] ABSTRACT The Internet is changing the way that people communicate which each other and the way they obtain their necessary information. Currently there is so much information available on the Internet and people tend to depend on it to locate the necessary information. Information providers for the web must ensure that they provide the necessary information for its users in a style that will help them to locate the information easily. This applies to the web sites on Sri Lankan Universities where the limited information available on them is not consistent among the different departments, faculties and universities. This paper describes the design and implementation of an integrated Sri Lankan University information system that is accessed and maintained through the Internet. A university information system of this nature will not only provide easy maintenance of such sites, but also provide useful integrated information. This will allow people to find out suitable universities to follow a particular course, to identify resource persons available in a particular field of study and to determine the job opportunities. The information of this system is maintained using an Oracle database. The users access the integrated data through a Java-enable web browser. The coding for each sub task is done using Java applets. The database is connected to the web browser using an Oracle JDBC Thin driver. Oracle PL/SQL is used to retrieve the data from the database. As universities are geographically distributed through out the country, remote database updating facilities are provided to authorised university personnel over the Internet.
1. INTRODUCTION For something as vast as a universe, the World Wide Web [BAR95, DEC95, KRO95, TUR95] is surprisingly finds your way around in. Today, more and more people offer their services and resources through the Web making all type of information available on the Internet. Even in Sri Lanka more and more organisations are offering their services on the Web [LEARN, LIS, LSP, NWS] giving a competitive advantage over their competitors. Since the inception of the web technology [TUR95] the universities around the world have made use of the Web to offer information resources and services to the Internet community at large as well as for the scholars. In most developed countries all most all their universities are on the Web. However in Sri Lanka few universities are maintaining Web sites [CMB, MOR] in a proper manner. These web sites provide descriptive information regarding their universities, but none of them provide comparative information, which is very useful for general public including persons who are willing to apply for their higher education. All state universities are under the University Grants Commission (UGC) of Sri Lanka and admissions to universities are processed by the UGC. Hence, each state university in Sri Lanka is a college or campus of a single university system. An organisation like the UGC could host such an information, so that it will facilitate in numerous ways for the public that are desperately searching for such information. It will reduce the searching time significantly and facilitate by providing summarised information about all the universities at one web site. Individual universities will be the information providers for this system, but they could continue to maintain their own web sites by providing respective details. This paper describes the design and implementation of an integrated Sri Lankan University information system that is accessed and maintained through the Internet. 2. METHOD An integrated university information system on the Internet will allow potential users to
query and obtain the desired information. The data of this system will be stored in a central database and maintained by the respective universities. Java [DEI97, JEP97, LAU96, THO96] was chosen to create the front-end of the system and Oracle [BUR96, TOM96] for the back-end database. The graphical user interfaces of the front-end will use Java applets [JTHP] to access and update of the back-end Oracle database using Oracle PL/SQL [BUR96, EME89]. The frontend and the back-end will be connected using an Oracle JDBC Thin driver [JDBC97, ORA]. The information of this system will be viewed using a Java-enable web browser [TUR95]. This information is to be presented as hypertext documents [HES95, MAT98] containing Java applets. The security of the database [ELM94] can be maintained at different levels. Initially, the username and password is required to download the Java applets that performs the data updating tasks. Secondly, data security features of the database can be used to control update operations of various persons. This is important to ensure that authorised users of a particular university do not tamper any information of their counterparts. 2.1 Domain Name Servers Every computer on the Internet has an Internet Protocol (IP) address associated with it. IP address has four parts (e.g. 192.248.16.193) to allow unique identification of the site. Web users don’t need to know much about these addresses as they are specified at the software installation stage. However users need to be familiar with the Internet’s Domain Name System (DNS) [TUR95] (e.g. sepalika.cmb.ac.lk) as it is used to locate and access information provided by those machines. 2.2 How the Web Works A web server maintains information presented through the web. Users (client) access this information from a remote location somewhere else on the Network. The web browser is the front-end user interface of this system and its information is obtained from the back-end web server. This approach is based on the client / server technology [MAR95].
2.2.1 Web Browser For most end-users, their web access software, known as a web client or a browser, is the most important piece of the Internet software they use. A browser handles user requests for information from a known source by connecting to the required web site, translating the user requests into formal computer communications and handling communication and delivery errors that occur from time to time. It also has the task of interpreting and converting character-based information into graphical form and properly combing the many sources of information into hypertext documents that the web support. Today, there are a lot of web browser software packages such as Netscape, Internet Explorer and HotJava. 2.2.2 Web Server Web clients request for information by specifying an address (URL) that identifies the location of the information source. When a URL request is specified to a web site, the web server of that site will deliver the user some specific information across the Internet. A web server listens for specific requests, for web resources that arrive through the network and interpreting valid requests as they arrive. Then it attempts to locate the requested resources while keeping a log of the Clients’ requests. It then responds with the requested resource (or an error message if the requested resource is not available) to the client. 2.3 Java and JDBC 2.3.1 Java Language Java is a technology [JTHP] that makes it easy to build distributed applications, which are programs executed by multiple computers across a network. Java attempts to expand the Internet’s role from an arena for communication to a network on which full-fledged application can be run. Java simplifies the construction of software agents, programs that move across a network and perform functions on remote computers on behalf of the user. Java, being robust, secure, easy to use, easy to understand, and automatically downloadable on a network, is an
excellent language basis for database applications. Java uses a compiler to convert human-readable source code into executable programs. It generates architecture independent byte-codes. The byte-codes can only be executed by Java enable software. Java byte-code files are called class files because they contain a single Java class. Vast majority of Java programs will be composed of more than one class file. Java programs can be embedded into web documents and turn the static web pages into applications that run on the client computer. Java based web documentation can include simulations, working models, and even specialised tools. 2.3.2 Java DataBase Connectivity (JDBC) Application programs execute database queries on different databases using an ODBC driver [MSHP] to achieve independence between the DBMS and the application program. Java application program uses a JDBC driver to execute SQL statements. JDBC driver consists of a set of classes and interfaces written in the Java programming language. The combination of Java and JDBC lets a programmer writes database applications once and run it anywhere. 2.3.3 JDBC Thin Driver Oracle's Thin driver is a JDBC driver that uses Java sockets to connect directly to an Oracle database. It provides its own implementation of a TCP/IP version of Oracle's SQL*Net. This Thin driver is written entirely in Java and hence is platform-independent. Note that there are other Oracle JDBC drivers such as OCI which are platform-dependent as not full written in Java. The Thin driver does not require Oracle software on the client side. It connects to any Oracle database of version 7.2.x and higher. The driver requires a TCP/IP listener on the server side. Web browsers such as Netscape 3 and higher can use the JDBC thin driver in Java Applets.
2.3.4 Java Development Kit (JDK)
The Java classes for are provided as JDK to develop the necessary Java program. As newer versions of JDK will consist of more Java classes it is necessary to specify the JDK version when programming with Java. 2.3.5 Using JDBC Thin Driver with JDK 1.1.1 When using JDBC thin driver with JDK the program must execute three steps before using the JDBC Application Program Interface (API) to access the database. Import the JDBC classes (e.g. java.sql); register the JDBC Thin driver (e.g. DriverManager.registerDriver), and open a connection to the database (e.g. DriverManager.getConnection) are these three steps. The necessary processing for the Java applet may be done before or after establishing the database connection. A database like Oracle will require a user login and password to establish the database connectivity. 2.3.6 Java Applets An applet is a small program that runs embedded in a Web browser’s HTML page. As such, any applet has a drawing or work area equal to an imaginary picture situated in the same spot. When applet code start running, it can immediately use its applet area without any initialisations or checks. The Java applet code given below tells the viewer or browser to load the applet whose compiled code is in “MyApplet.class” (in the same directory as the current HTML document), and to set the initial size of the applet to 100 pixels wide and 140 pixels high.
3. DESIGN Identifying the main entities, their attributes, relationships and constraints, commences the design of a database application. A university information system should include information on courses offered, resources available and academic details. This includes details of faculties and departments, courses and degrees offered and their subjects, student facilities, employment vacancies, staff personnel and research details. Characteristics of some of the entities used are given in table 1.The full details are available in [MAN98].
4. IMPLEMENTATION University Information System is implemented using two levels. The first level is the information retrieval part and the other is database maintenance part. All the database updating are handled remotely by the database maintenance part by authorised users through the web.
Entity
Characteristics
University Master Table (UniMast)
University ID (UniId) University Name (UniName) Location (Location)
University Detail Table (UniDetail)
University ID (UniId) Department Number (DeptNum) Subject Number (SubNum)
University Faculty Table (UniFac)
University ID (UniId) Faculty Number (FacNum)
Faculty Master Table (FactMast)
Faculty Number (FacNum) Faculty Name (FacName)
Department Master Table (DeptMast)
Department Number (DeptNum) Department Name (DeptName)
Subject Master Table (SubjMast)
Subject Number (SubNum) Subject Name (SubName)
Table 1: Selected Entity / Characteristics of a University Information System The first part consists of the information retrieval, which has separate modules for each subtask. There is a separate interface for each sub module and they were implemented using Java applets. In each case user has a choice of obtain detail information regarding a particular University or summarised information regarding all the available Universities. Database maintenance, part has the same type of modules, but the general public does not have the access permissions to them. There is a separate user validation process that allows only authorised persons in each University to update the database. 4.1 Connecting the Database
Several difficulties were faced when attempting to connect the Oracle database to the Browser. Some JDBC bridges and JDBC drivers (e.g. Jet Connect [JETC97]) supported the connecting to Oracle, but they did not allow authorised database update through some Javaenable web browsers (e.g. Netscape), while others allowed (e.g. HotJava) bur had unexpected disconnection. This was due to the in-built browser security features. However, finally we achieved the connectivity and remote database updating using the Oracle JDBC Thin driver. This driver was able to automatically remove in-built browser security features to allow database update. Figure 1 shows that the connection to the Oracle database using the Oracle JDBC Thin driver. The database is physically located at the machine with the IP address 192.248.16.193, using port 1521 for database system identifier “orcl”. User name of the Oracle database is “scott” and the password is “tiger”. The code of figure 1 is part of a Java program “faculty.java” used to retrieve the faculty details. This program when complied will become the Java applet “faculty.class”.
4.2. Information Retrieval The user will be able to list the faculties for any University and also search whether a given faculty exists using the applet for retrieval of faculty details. The input data for the faculty name and the university name is extracted from the applet dialog boxes using getText() and assigned to variables FacNameData and UniNameData as shown in figure 2. The university name that the user input is validated and if it a valid than the identity of the university (e.g. UniId) is stored in UniIdData. The SQL query is constructed as a string and assigned to a variable Query. A statement is created next (i.e. createStatement) and the query is executed (e.g. executeQuery). Finally, the result set (i.e. rset) is processed and displayed as output text (cf. figure 3). Other information retrieval of this system is done in a similar manner and includes retrieval of courses offered, research interests and job vacancies [MAN98].
4.3 Database Maintenance
Only the authorised persons can insert new entries to the database. There is a user validation before any database updating. This process is handled by the respective Java applets. It consists of validation of user input data such as the university name, department name and subject name. A variable binding process is carried out when preparing an update queries, as they need to be match to appropriate attributes of the database tables. Finally this prepared query statement is executed. import java.sql.*; /* import the JDBC classes */ import ………… public class faculty extends java.applet.Applet { static final String driver_class = "oracle.jdbc.driver.OracleDriver"; static final String connect_string = “jdbc:oracle:
[email protected]:1521:orcl", ”scott”,”tiger”; …………… /*variable definitions */ public void init () { …………… /* applet initialisations */ } public boolean action (Event ev, Object obj) { …………… /* query construction */ if (conn == null) /* check whether it already connected */ { output.appendText ("PLEASE WAIT .....\n"); output.appendText ("Connecting to the Database\n\n"); Class.forName (driver_class);
/* register the JDBC Thin driver */
conn = DriverManager.getConnection (connect_string); /* open a connection to the database */ } …………… /* query execution */ …………… /* out display */ }
Figure 1: Java Source code to Retrieve Faculty Details 5 CONCLUSIONS
A University information system of this nature will not only provide easy maintenance of such sites, but also provide useful integrated information. This will allow people to find out suitable Universities to follow a particular course, to identify resource persons available in a particular field of study and to determine the job opportunities. String FacNameData = facname.getText() ; String UniNameData = universityname.getText() ; String Query = "SELECT um.UniName , fm.FacName FROM UniMast um , FacMast fm , UniFac uf WHERE uf.UniId = um.UniId AND uf.FacNum = fm.FacNum AND fm.FacName LIKE " + "''%" + FacNameData + "%'" + " AND uf.UniId = '" + UniIdData + "'" ; Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery (Query);
Figure 2: Query for Selecting Faculty details for a given University
Figure 3: Results of the Query for Selecting Faculty details for a given University
One of the important features of this system is the easiness of the data maintenance and enforcing standards. The remote database updating facilities ensure the retrieval of consistent up-to-date information. This type of systems uses the latest technologies that are not very much used for this type of information systems and avoid the need to go through very lengthy web pages. This system provides an interactive session with the user and it will reduce the time taken for retrieving required information, as the user can directly specify the area of interest. Such systems are useful to most organisations as they have a hierarchical structure in which each division or department performs their activities in a similar manner. One of the problems associated with this type of system is the time taken to load the Java applet and present the data from the database. However, as the information presented always include those searched for, the users will ultimately gain as they need not go through series of unwanted pages to locate the necessary information. 6. REFERENCES [BAR95]
Barron B., Ellsworth J.H. and Saretz K.M., "Internet", Sams. Net Publishing, 1995.
[BUR96]
Burleson D.K., "Oracle Database Applications", Coriolis Groups, 1996.
[CMB]
University of Colombo Web, http://www.cmb.ac.lk.
[DEC95]
December J. and Randall N., “World Wide Web Unleased”, Sames net Publications, 1995.
[DEI97]
Deitel H.M. and Deitel P.J, “Java How to Program”, Prentice-Hall, 1997.
[ELM94]
Elmasri R. and Navathe S.B., “Fundamentals of Database Systems”, 2nd edition, Benjamin/Cummings, 1994.
[EME89]
Emerson S.L., Darnovsky M. and Bowman J.S., "The Practical SQL", Addison-Wesley, 1989.
[HES95]
Heslop B. and Budnick L., "HTML Publishing on the Internet", Ventana, 1995.
[JDBC97] JDBC Documentation, Oracle Corporation, 1997 [JEP97]
Jepson B., "JAVA database programming", John Wiley & Sons, 1997.
[JETC97] Jet Connect JDBC-ODBC driver Documentation, XDB Systems Inc., 1997 [JTHP]
Java Technology Home Page, http://www.javasoft.com.
[KRO95]
Krol E.D. and Ferguson P., "The Whole Internet", O'Reilly & Associates, 1995.
[LAU96]
Vanhelsuwe L., Phillips I., Hsu G.T., Sanker K., Ries E., Rohaly T. and Zukowski J., “Mastering Java”, BPB Publications, 1996.
[LEARN] Lanka Educational and Research Academic Network, http://www.ac.lk. [LIS]
Lanka Internet Services, Sri Lanka Web Server, http://www.lanka.net.
[LSP]
Lanka Super Pages - The Ultimate Business Connection, http://www.lsplk.com.
[MAN98] Manage B.P., “Integrated Sri Lankan University Information System”, B.Sc. dissertation, University of Colombo, Colombo, Sri Lanka, 1998. [MAR95] Martin J. and Leben J., “Client/Server Databases”, Prentice-Hall, 1995. [MAT98]
Matthews M.S. and Poulsen E.B., "Frontpage 98: The Complete Reference", Osborne McGraw-Hill, 1998.
[MOR]
University of Moratuwa, http://www.mrt.ac.lk.
[MSHP]
Microsoft Home Page, http://www.microsoft.com.
[NWS]
National Web Server, http://www.lk.
[ORA]
Oracle Home Page, http://www.oracle.com, Oracle Corporation.
[THO96]
Thomas M.D. and Patel P.R., “JAVA Programming for the Internet”, Ventana Communications Group, 1996.
[TOM96]
Tommie L., “Essential Oracle 7”, Prentice-Hall, 1996.
[TUR95]
Turligton S.R., "Walking the World Wide Web", Ventana, 1995.
