AcademicInfo – Web Based Application for Consulting Academic Information Databases

1. AcademicInfo – Web Based Application for Consulting Academic Information Databases Alina Andreica, Daniel Stuparu, Ana Bara, Oana Timiş, Florentina Tufiş, Flavia Mureşan,Carmen Ciplea, George Hristodol, Monica Bojan ICT Department, “Babeş-Bolyai” University, Cluj-Napoca, Romania e-mail: infoadmin@ubbcluj.ro

2. AcademicInfo Aims &Principles • offering web access facilities to academic information for students, teachers and academic management • collecting on a central server specific information from dedicated databases located on faculties’ database servers • at present, students from our university can view their academic routein secured sessions, using anfriendly web-based interface • We‘ll extend our application with curricula / course management facilities for teachers and information synthesis for academic management

3. Topics • The Working Framework and the ApplicationObjectives • Architecture And Security Principles • Design &Implementation of the Application • Database Comparison Module • Data Transmission Module • Server module • The Database Server Component • The User Interface • Conclusions and Future Work

4. The Working Framework and the ApplicationObjectives • Aim: extending IT facilities offered within BBU for managing academic information • existent software includes dedicated applications installed and used in each faculty for managing educational information • The applications create, update and process specific MS Access databases by means of Visual Basic applications, with user friendly interfaces

5. The Working Framework and the ApplicationObjectives • AcademicInfo will • periodically centralize a dedicated database at the university level containing relevant information from faculties' databases • process this database in order to provide specific information facilities to BBU students, teachers and academic management • At this moment, there are implemented the facilities dedicated to students • Students may view their educational trajectory - disciplines with graduated exams and the corresponding grades

6. Architecture and Security Principles • hierarchical structure of the applications • strong point in ensuring security - it explicitly prevents updatable types of access into the currently used databases • Only the central database is accessible to user queries, through a dedicated and trustworthy application • For the server module we chose to work under linux operating system / PostGreSQL database engine / PHP web interface since this solution has a highly proficient quality / price rate

7. Architecture and Security Principles • This architecture is flexible and ensures an easily manageable implementation of appropriate security measures. Supplemental security facilities are ensured by a log mechanism • The applications locally installed on the database servers within faculties are implemented in Visual Basic + ADO, compatible with existing applications • we study the possibility of obtaining increased database efficiency by upgrading the database engine for the existing applications

8. The application architecture

9. Design And Implementation of the Application • structured on three main modules • local database comparison • data transmission • server component • The “local” modules • Retain the previous and actual version of the database • Compare the two databases and generate the necessary update information • Transmit this information to the server Locally installed

10. Design And Implementation of the Application • Sending only the updates is more efficient than the entire database Database Comparison Module • ActualizareInf compares the current database Actual with the previous one Manevra table by table and retains records with the necessary update information • Operations ("append record" - encoded 'A', "delete record" - 'S', "update record" - 'M' ) + corresponding argument information

11. Database Comparison Module • results are introduced into a dedicated database Modif, which is transmitted to the central server • Modif 's structure is similar to the other databases but contains only the tables with relevant information for our application • Modif's tables have a supplemental field for retaining the operation to be performed (append, delete, update corresponding record) • Comparison algorithm principles • Since database tables are indexed, and in order to gain efficiency we designed the main comparing algorithm based on classical merging principles for ordered sequences - Comparare procedure

12. Database Comparison Module • The module contains the following functions / procedures: • Initialization / closing subprograms • ·Function getParam(inifile As String, prm As String) As String) Returns the name of a connection, used in Initializare() • ·Sub Initializare(ByRef con As ADODB.Connection, prm As String) Sets the database connection and opens a database; it is called for each database • ·Sub Finalizare(ByRef con As ADODB.Connection) Closes a connection; it is called for each database

13. Database Comparison Module • Processing subprograms • ·FunctionCheie(ByRef tabel As ADODB.Recordset, cheia As Variant) As Integer • Returns the position of the primary key from the table given as parameter; it is called in procedure Comparare(…) • ·Sub inserare(ByRef tabel As ADODB.Recordset) • Adds into a specific table fromModif database data from the parameter table using an "INSERT" SQL instruction. The instruction is saved into a string and executed by connModif connection

14. Database Comparison Module • Processing subprograms • ·SubComparare(TabActual As String, ByRefconnA As ADODB.Connection, TabManevra As String, ByRef connM As ADODB.Connection, cheia1 As Variant, TabModif As String, Optional cheia2 As Variant) • Compares two tables with the same structure from Actual and Manevra databases. Computed update information is appended into the table with the same name from Modif database (operation set as"append record" - 'A', "delete record" - 'S', "update record" -'M') • the algorithm is based on merging ordered sequences principles, consequent to determining the primary key

15. Database Comparison Module • Processing subprograms ·SubComparare(…) • ·If the key from Actual is smaller than the one from Manevra, the record will be retained in order to be appended • ·If the key from Actual is greater than the one from Manevra, the record will be deleted • ·If the keys are equal, we test if updates were made and retain them • The procedure is not executed if Actual database is empty or if Modif database is not empty • If errors are encountered while executing the procedure, they are retained into a dedicated table along with the necessary information

16. Data Transmission Module • The transmission module Transmitere checks the existence of a network connection to the central server and sends the Modif database given as result by the previous module to the server • Modif database is sequentially parsed and appropriate information is sent • Correct transmission is ensured by an appropriate database connection string, which also checks the existence of a valid network link to the server

17. Data Transmission Module • Initialization / closing subprograms • Public FunctiongetParam(inifile As String, prm As String) As String Reads the connections from the initialization file (the batch file which launches the local applications) • Private Sub scrie_fisier() Checks the connection to the server • Private Sub Initializare(ByRef con As ADODB.Connection, prm As String) Initialization procedure • Private Sub golire(Tabcit As String, ByRef concit As ADODB.Connection, TabScrie As String, ByRef conscrie As ADODB.Connection, cheia As Variant, Optional cheia2 As Variant) Empties the tables in Modif database; is called at the end of the transmission

18. Data Transmission Module-Processing subprograms • Private Sub adauga(Tabcit As String, ByRef concit As ADODB.Connection, TabScrie As String,ByRef conscrie As ADODB.Connection, cheia As Variant, Optional cheia2 As Variant) Adds a record • Private Sub modifica(Tabcit As String, ByRef concit As ADODB.Connection, TabScrie AsString,ByRef conscrie As ADODB.Connection, cheia As Variant, Optional cheia2 As Variant) Updates a record Private Sub Sterge(Tabcit As String, ByRef concit As ADODB.Connection, TabScrie As String,ByRef conscrie As ADODB.Connection, cheia As Variant, Optional cheia2 As Variant) Deletes a record

19. Server module • The server module contains the database component, which includes the database engine - PostGreSQL and the databases, and the web interface which enables the user to interact with the application • The Database Server Component • For each faculty, a dedicated database which contains relevant information, is created on the server • In order to efficiently manage the database server, we created two user accounts with specific rights for each database • One has update rights and accesses the appropriate database only from the server machine of each faculty • The other is dedicated to the WWW user who can access the corresponding database only with select rights

20. Server module • The databases and their users with the corresponding rights are created by means of specific scripts. Tables are created according to existing database relationships • A dedicated database is also created as the application access / error log • Examples Example 1. Database creation script #must verify if the user introduce the name of the database if [ $# -ne 1 ] then echo "Trebuie introdus numele noii baze de date" echo "Folositi ./createBDFacultate nume_bazadate" exit 1 fi # create database with the name $1 createdb $1 # use the folowing scripts to create tables in $1 database psql -f createTable__Univ.sql $1

21. Server module - Examples Example 2. Table creation script CREATE TABLE Univ( Judet varchar(2), Cod integer primary key, Denumire varchar(100), Localitate varchar(30), Adresa varchar(50), Telefon varchar(40), Cod_postal varchar(6), Tip integer ); Example 3. User creation script #creez utilizatorii cu acces de selectie pe baza de #date Catalog, si de insertie, seletie, stergere, # modificare pe baza de date corespunzatoare psql Catalog -c "create user user$i password '$i'" #acordarea de drepturi select pe baza de date Catalog echo grant select on $j to user$i';' >> scriptCatalog.sql #execut fisierul format psql -f scriptCatalog.sql Catalog1

22. Server module - The User Interface • As web support, we used Apache web server and HTTPS protocol • The main web page of the application contains a brief description of the application and login dialog boxes

23. Server module - The User Interface • At this time, login is granted for students, who choose a faculty and authenticate themselves by CNP • The PHP code associated to the entering button validates the CNP and logs the access • For a valid CNP, we test if it exists in the database corresponding to the chosen faculty and if so, we select the information to be displayed for the student • The user screen contains of 3 frames • Display options: sortingcriteria in respect with semester, year, grade, exam date, discipline and selection criteria on semesters • General information: Faculty name, student name • Selected information as a table with the following header: Semester, Year, Discipline Code, Discipline Title, Exam Date, Grade, ECTS. If the student is enrolled in more than one specializations, all of them are displayed

24. AcademicInfo –student information form page

25. Conclusions and Future Work • AcademicInfo application is dedicated to BBU's students, teachers and academic staff and provides IT facilities regarding on-line viewing, by means of a friendly and accessible web interface, educational information • Currently, we implemented course and grade display facilities for students • AcademicInfo centralizes relevant information from dedicated databases located on faculties' servers

26. Conclusions and Future Work • AcademicInfo is modularly designed, flexible, secure and open to new extensions • Future developments • improving our efficiency studies regarding the locally installed applications • upgrading the local database engines • analyze the efficiency of security mechanisms implemented within the server component • Facilities for teachers and academic staff are currently under development