GSD: a web application for teacher timetable management - dissertation report

Universidade do Minho Escola de Engenharia Departamento de Informática João Reis GSD: A Web Application for Teacher Timetable Management Dissertation Report April 2020 Universidade do Minho Escola de Engenharia Departamento de Informática João Reis GSD: A Web Application for Teacher Timetable Management Dissertation Report Master dissertation Master Degree in Computer Science Dissertation supervised by Pedro Rangel Henriques Maria João Varanda April 2020 AUTHOR COPYRIGHTS AND TERMS OF USAGE BY THIRD PARTIES This is an academic work which can be utilized by third parties given that the rules and good practices internationally accepted, regarding author copyrights and related copyrights. Therefore, the present work can be utilized according to the terms provided in the license bellow. If the user needs permission to use the work in conditions not foreseen by the licensing indicated, the user should contact the author, through the RepositóriUM of University of Minho. License provided to the users of this work Attribution-NonCommercial CC BY-NC https://creativecommons.org/licenses/by-nc/4.0/ i STATEMENT OF INTEGRITY I hereby declare having conducted this academic work with integrity. I confirm that I have not used plagiarism or any form of undue use of information or falsification of results along the process leading to its elaboration. I further declare that I have fully acknowledged the Code of Ethical Conduct of the University of Minho. João Reis _________________ ii ACKNOWLEDGEMENTS It would not have been possible to write this master thesis without the help and support of the good people around me, to only some of whom it is possible to give a particular mention here. To begin with, I’d like to thank my coordinators Prof. Pedro Rangel Henriques and Prof. Maria João Varanda for all the help, advice, knowledge and the availability they always offered me. I couldn’t ask for a better guidance. To my mother and my father, I’d like to thank them for all the unconditional help, sacrifice and trust invested in me through all these academic years; to my brother and my sisters for all the patience they surely had to have; to my cat, for keeping me company through all these endless working nights. I also want to acknowledge all my friends for doing what friends do best. It wouldn’t be fair to single somebody from this long list, but I’d like to mention those who kept company all the time through my classic foolishness. Finally, to the beautiful city of Budapest and all the great friends I made there – thank you! You helped me live one of the best times of my life which I will never forget. A special mention to the Prof. Zoltan Porkolab for making this possible. iii ABSTRACT This document presents a Masters Thesis in Software Engineering, in the area of Academic Management, Support Tools and Web Software. In a given higher education institution, the teaching service is assigned by it’s department twice a year, following a model of their own self-re-creation. Initially, it is intended to standardize the information in a transversal way to all the departments and create a model that will serve as the basis for the implementation of a web application. This application will allow it’s users to enter and verify information, generating database entries in a DSL of their choice (such as SQL), feeding a timetable construction platform. iv RESUMO Este documento tem como propósito apresentar uma tese do Mestrado em Engenharia Informática, na área de Gestão Académica, Ferramentas de Apoio e Software Web. Numa determinada instituição de ensino superior, o serviço docente é atribuído por cada departamento aos docentes em funções, duas vezes por ano, seguindo um modelo da sua própria auto-recriação. Pretende-se, numa primeira instância, normalizar a informação de forma transversal a todos os departamentos e criar um modelo que servirá como base para a implementação de uma plataforma web de especificação da distribuição de serviço docente. A aplicação permitirá aos seus utilizadores inserir e verificar informação, gerando entradas para uma base de dados usando uma DSL (Domain-Specific Language, ou, em Português, Linguagem de Domínio Específico) da sua escolha (como SQL). As tabelas geradas servirão de base a plataformas de construção de horários. v CONTENTS 1 introduction 1 1.1Objectives 2 1.2Research Hypoteshis 2 1.3Document Structure 3 2 current timetabling problems and solutions 4 2.1REDOSPLAT 4 2.2School Timetabling in Theory and Practice 5 2.3Current Solutions 6 3 proposed approach 8 3.1Introduction and Goals 8 3.1.1Requirements Overview 8 3.1.2Quality Goals 11 3.1.3Stakeholders 11 3.2Constraints 11 3.2.1Technical Constraints 12 3.2.2Organizational Constraints 12 3.2.3Conventions 12 3.3System Scope and Context 12 3.4Solution Strategy 18 3.4.1Database 20 4 gsd:development 28 4.1Building Block View 28 4.1.1Back-end server: Node.js + Express 30 4.1.2Front-end application: NEXT.js 35 4.1.3Output generator 42 4.2Runtime View 46 4.2.1General Data 46 4.2.2Association 47 4.2.3Shifts 47 4.3Deployment View 50 4.4Concepts 51 5 gsd:tests and results 53 5.1Experiment setup 53 vi contents vii 5.2Results 53 5.2.1Login 53 5.2.2Administrator 54 5.2.3Head of Department 60 5.3Discussion 64 6 conclusion 66 6.1Conclusions 66 6.2Prospect for future work 67 2.3. Current Solutions 7 With Section Scheduler by CourseLeaf, staff can coordinate across multiple departments and offerings to build a schedule of classes that is consistent across campus [ 5 ]. This solution comes closer to what is intended, claiming also a 90 percent decrease in data entry by their registrar’s office in an institution - one of the main objectives. Many of its highlights are interesting and desirable such as visual representation and a interactive interface, giving the power to centralize the control of timetabling. Then again, it’s highly aimed for the American system and incompatible with the Portuguese one - which can easily be verified checking the institutions list, containing a high percentage of American or other country institutions using the same system. It also doesn’t allow importing and/or exporting databases and revolves around timetabling solving or constructing which it isn’t desirable. •Schedule My Teachers Having the same flaws as the former ones - American system aiming, lack of data baste export and imports - Schedule My Teachers is also developed having in mind schools and not the high education problematic [18]. 3 PROPOSED APPROACH When it comes to software development, documentation constitutes one of the key aspects of it. Making a valuable documentation goes through a long process of numerous steps and components, such as requirements and the different types of modeling, which makes it a complex task. It’s a common practice to follow already established and reputable templates such as arc42 - which is what’s going to be done in order to achieve it. Following this methodology will result in a final satisfactory product to each party involved, given it’s quality architecture and flexibility to changes. In this chapter, through the next sections, will be presented the complete arc42 [2] template to this project. 3.1 introduction and goals 3.1.1Requirements Overview Through this document it has been discussed our objectives and main features without going in further detail or putting it in a formal manner. In this section we’ll set it clear, branching them into two - functional and non-functional. Requirements elicitation and subsequent writing is essential and should be done carefully to ensure every party involved understands it without ambiguities. For that same reason this document follows the procedure present in the book written by Fernandes and Machado. Requirements source arise mainly from meetings with the project’s co-supervisor, given one’s field specialization - although not exclusively, as the previous chapter exposes. As regards to it’s writing, requirements should be written in a standardized way. Not every requirement has the same importance when it comes to implement it. Some of them are more urgent than others and that’s why the MoSCoW method [ 22 ] was considered as seen in the book written by Wiegers and Beatty. This method was used as a technique to reach a consensus between the stakeholders involved when it comes to establish the importance which they put into the delivery of each requirement. 8 3.1. Introduction and Goals 9 The application will initially be developed in order to try to deliver all the requirements such as Must, Should, and Could, but Should requirements will be developed to the detriment of Could, if the delivery schedule is threatened. To catalog the requirements, we’ll them as following: M Defines a requirement that must be satisfied in order for the final solution to be acceptable. S Defines a requirement that must be satisfied in order for the final solution to be acceptable. This is a high risk requirement that should be included if possible within the delivery time. C This is a desirable requirement that would be good to have if there is time and if the resources allow it. The solution must be accepted if the functionality is not included. W This category represents requirements that stakeholders want to have, but agree that the requirement most likely won’t be implemented in the current version of application. Having all this in mind, as functional requirements, we have: F.1MThe timetabling responsible should register the institution in the platform; F.2M The timetabling responsible should add institution staff to platform after registering it (F.1); F.3MThe timetabling responsible can add institution teachers to the platform; F.4M The timetabling responsible should add departments to the platform after registering it; F.5M The timetabling responsible should assign a head of department for each department; F.6SThe timetabling responsible can add institution restrictions; F.7MThe timetabling responsible should add teachers to its department; F.8SThe head of department should add teachers to its department; F.9MThe timetabling responsible should add degrees; F.10 MThe timetabling responsible should add courses to a department; F.11 MThe timetabling responsible should add classes to a course; F.12 MThe head of department should assign shifts to each class; 3.1. Introduction and Goals 10 F.13 MThe head of department should assign shifts to one or more degrees; F.14 MThe head of department should assign teachers to each shift; F.15 CThe teacher can add desired days off; F.16 CThe teacher can add desired working days; F.17 CThe teacher can add desired working hours; F.18 CThe teacher can add desired non-working hours; F.19 CThe staff should add institution buildings to the platform; F.20 CThe staff should assign rooms to each building; F.21 SThe staff can add institution restrictions; F.22 CThe system can warn about restrictions not being followed; F.23 CThe system can enforce restrictions; F.24 MThe timetabling responsible should assign which components are required; F.25 MThe timetabling responsible can load a pre-existent database file; F.26 MThe system should interpret the database loaded by the timetabling responsible; F.27 M The system should make a visual representation of a database loaded by the timetabling responsible; F.28 MThe timetabling responsible should assign each database entry; F.29 MThe timetabling responsible should assign which output is generated; When it comes to non-functional requirements, they’re split as following: NF.1MThe system should be responsive; NF.2SThe system interface should be simplistic; NF.3CThe system’s features should be done in few steps; 3.2. Constraints 11 3.1.2Quality Goals In the next time are represented the main quality goals of the software. # Quality Goal Scenario 1Simplicity The product should be simple enough to any user understand how it works at first sight 2Efficiency The product should be fast and dynamic, without long loading times; 3Attractiveness The product should be attractive to the general masses; 4Interoperability The product should be able to work with other software; mainly with schedule solvers. 3.1.3Stakeholders Stakeholder Context João Reis Being the author of the thesis, aims to successfully implement what is proposed and do it in high quality standards; Pedro Rangel Henriques The thesis supervisor intends to guide the thesis author to successfully accomplish his goals and have every party involved satisfied Maria João Varanda Representing Instituto Politécnico de Bragança interests and being this thesis co-supervisor, aspires to have a valuable and working software to support the making of timetables for the given institution Teachers Teachers who want a simple and interactive way to expose their preferences when it comes to working time Timetabling Managers Every institution or department has someone responsible to deal with timetabling. They’d be interested in saving time with paperwork and communicative bureaucracy. 3.2 constraints The few constraints on this project are reflected in the final solution. This section shows them and if applicable, their motivation. 3.3. System Scope and Context 12 3.2.1Technical Constraints Constraint Context 1OS independent development The final product should be able to run on any operating system - Windows, Mac and Linux distributions 2Web Application The software should be running exclusively on any modern browser 3Deployable to a Linux server The application should be deployable through standard means on a Linux based server 3.2.2Organizational Constraints Constraint Context 1Team João Reis 2Time Schedule Started at the first semester of 2018/19 scholar calendar, it is planned to have a finished and tested version by 2months before the thesis delivery deadline. 3Version control/management As the software is being worked on, it should be pusged to a git private repository with a complete commit history; 4Testing The final version should have been subjected to rigorous testing 3.2.3Conventions Convention Context 1Documentation Language The documentation should be written in english 2Software Language The software should be written in Portuguese 3.3 system scope and context Before implementing software or even before modeling it, it’s important to have a full understanding of the domain it’s being worked on. A popular way to achieve it is by 3.3. System Scope and Context 13 building a domain model - making it possible to reach a consensus between all stakeholders involved since it’s quite a simple approach which everyone can understand. For this reason, a domain model was built as it shows in the figure 2on the next page. Analyzing the model, it seems more complex than it actually is. The easiest way to understand it is to break it down into simple steps. Starting with highest entity, we have Institution . It represents the academic institution that will be using the product. •In a given Institution, there are one or more Department; • An Institution can impose multiple Restriction , which should be abided across all departments and courses; • In the institution there are multiple Degree . By Degree , it’s intended to designate a degree program you can take when joining university, e.g., a law degree; •It’s also possible to map the every Building that the Institution has. Down the hierarchy, there’s the Department. • The Department will also organize multiple Course which multiple Degree s can attend to. Putting in perspective, let’s assume a Mathematical Analysis course - multiple degree programs can include that course; •In a Department there are multiple teachers which are responsible for; •Due to exclusive Restriction to each Department, each own can impose theirs. It’s relevant to explain the role of the described entities that are responsible for imposing restrictions. By its name, it’s easily accessed its function, e.g., there can be restrictions for number of hours of classes a student can attend per day, the schedule of a class regime, between many others. It’s possible to map every institution building using the entities Building and Classroom . An Institution has multiple Building, each one having multiple Classroom too. Going back to the Degree , it’s intended to define multiple degrees that are offered by the institution, e.g., a law degree, as it was said said before. •Every degree has to abide to rules imposed either by the Institution or Department; •Obviously, along the course of a Degree, there are multiple Subject. As we said before, each Degree has multiple Subject , and each Subject has multiple Class . The entity Class makes part of the core entities, due its relevance in the whole process. 3.3. System Scope and Context 14 Figure 2: Domain model. 3.3. System Scope and Context 15 • Each Class can be taught in different Shift s. This can be due to multiple reasons, such as high number of attendees or just giving the various opportunities to everyone to attend. • Each Class is related to a Course . It’s important to make it clear that a Course can be attended by different degrees. • It’s also possible to make it possible to select a preferred Classroom given the necessities aClass can have. AClass Shift can be taught by one or more Teachers. •Teacher s can register their preferences when it comes to its free time. This means, they can choose between by working or not working in a given interval. • A teacher can also be responsible for a department and for defining the teaching service of that department. In order to have a full understanding of the system and the features available for its users, the use case diagram as seen in the figure 3was built - a simple representation of a user interaction with the system. The system users’ are split in two - the administrator and the head of department. To get in detail with the former, there are six sections for it: Department,Degree,Course,Data, Association and Configuration. The Department section contains two use-cases: •Department management : The user gets a list of departments currently in the system, having the possibility to consult each one’s information or delete it, if requested. •Upload department database : The user can a upload file containing a database of departments for the system to load and insert in batch; The second section is Degree, containing two use-cases: •Degree Management :The user gets a list of degree currently in the system, having the possibility to add a new one, edit or delete one degree previously inserted. •Upload degree database : The user can upload a file containing a database of degrees for the system to load and insert in batch; As for the third section, Course there’s one use-case: •Course Management : The user gets a list of courses currently currently in the system, having the possibility to add a new one, edit or delete one course previously inserted. 3.3. System Scope and Context 16 Figure 3: System Use-Case diagram 3.4. Solution Strategy 23 teacher This table contains information for every teacher in the system; Column Type Context id integer Primary key for each table entry; name text Teacher’s name local_identifier text Short identifier for each teacher; email text Teacher’s e-mail; password text Teacher’s account password; password text Foreign-key to department(id) , corresponding to the department it belongs to; _ipb_cod_escola integer IPB’s particular required fields; _ipb_emp_num integer IPB’s particular required fields; course This table contains information for every course in the system; Column Type Context id integer Primary key for each table entry; name text Course’s name; x semester integer Semester when it occurs; department_id integer Foreign-key to department(id) , corresponding to the department it belongs to; abbreviation text Course’s abbreviation; _ipb_cod_escola integer IPB’s particular required fields; _ipb_cod_curso integer IPB’s particular required fields; _ipb_n_opcao integer IPB’s particular required fields; _ipb_n_disciplina integer IPB’s particular required fields; _ipb_n_plano integer IPB’s particular required fields; class This tables contains the correlation between courses and types - basically, the type of classes each course has. 3.4. Solution Strategy 24 Column Type Context pair_id integer Data entry identifier; course_id integer Foreign-key to course(id) , corresponding to the course it belongs to; Composite with type_id, the primary key of with the table; type_id integer Foreign-key to type(id) , corresponding to the type of class it is; Composite with class_id, the primary key of with the table; degree This table contains the information for every degree in the system; Column Type Context id integer Primary key for each table entry; name text Degree’s name; abbreviation text Degree’s abreviation; _ipb_cod_escola integer IPB’s particular required fields; _ipb_cod_curso integer IPB’s particular required fields; _ipb_n_plano integer IPB’s particular required fields; shift This table contains the information for every shift in the system; Column Type Context id integer Primary key for each table entry; sys_version_id integer Foreign-key to version(id) , corresponding to the version it belongs to; counter integer Number of classes attending this shift; When zero, a trigger will remove the respective row. year This table was built in order to normalize the data relative to scholar years; It’ll be inserted by the administrator; Column Type Context id integer Primary key for each table entry; name text Year’s assigned name; abbreviation text Year’s assigned abbreviation; 3.4. Solution Strategy 25 types This table was built in order to normalize the data relative to class types; It’ll be inserted by the administrator; Column Type Context id integer Primary key for each table entry; name text Type’s assigned name; abrev text Type’s assigned abbreviation; color text Type’s assigned color; priority integer Type’s assigned priority; poweruser This table contains the information to the administrator’s account. Column Type Context id text x Primary key for each table entry, and it’s id; password text Administrator’s password; The many-to-many relations between tables, make the rest of the tables - such as: course_teacher , degree_course , assigned_degree_course , shift_class , shift_teacher and class_number: course_teacher This table relates the tables course and teacher ; For a given course, its possible to find the teachers that got assigned to it; Column Type Context teacher_id integer Foreign-key to teacher(id) , corresponding to an assigned teacher for a given course; course_id integer Foreign-key to course(id) , corresponding to the course it belongs to; pair_id integer Data entry identifier; degree_course This table relates the tables degree and course ; Its main purpose is associate the for a given course the plan of courses it has; 3.4. Solution Strategy 26 Column Type Context course_id integer Foreign-key to course(id) , corresponding to the course it belongs to; degree_id integer Foreign-key to degree(id) , corresponding to the degree it belongs to; year_id integer Foreign-key to years(id), corresponding to the scholar year that the course that was assigned it belongs to; semester integer Semester which when it occurs; x pair_id integer Data entry identifier; assigned_degree_course This table relates the tables degree and course ; Although the previous table does the same, this one has one specific entry that changes every year - the number of attendants. Column Type Context degree_course_id integer Foreign-key to degree_course(id), corresponding to the course and degree it belongs to; attending integer Number of students for that given degree attending the course; sys_version_id integer Foreign-key to version(id) , corresponding to the version it belongs to; shift_class This table relates the tables shift and degree ; For a given shift, its possible to find the degrees that got assigned to it; Column Type Context shift_id integer Foreign-key to shift(id) , corresponding to the shift it belongs to; class_id integer Foreign-key to class(id) , corresponding to the class it belongs to; shift_teacher This table relates the tables shift and teacher ; For a given shift, its possible to find the teachers that got assigned to it; 3.4. Solution Strategy 27 Column Type Context shift_id integer Foreign-key to shift(id) , corresponding to the shift it belongs to; teacher_id integer Foreign-key to teacher(id) , corresponding to the teacher it belongs to; sys_version_id integer Foreign-key to version(id) , corresponding to the version it belongs to; shift_teacher This table is used to separate the various classes that exist per week for each class; Column Type Context class_id integer Foreign-key to class(id) , corresponding to the class it belongs to; number integer Represents the number of class it is; duration integer Duration of said class; id integer Primary key for each table entry; 4 GSD: DEVELOPMENT Along this chapter, the development circumstances, problems and decisions that together compose the final product are going to be addressed and studied into detail. 4.1 building block view Before making any choices into development, it should be reminded that the user-interface should be running on web, while very dynamic. The traditional web solutions couldn’t apply since the problem is of a very specific domain. For this same reason, the solution goes through the use a modern framework to build the web application. Since the used data isn’t static but manipulated across usage, this system comes across as a full-stack development solution - this means that the development of both server-side back-end) and client-side (front-end) portions of the web app [8] is required. Currently, there are a large number of front-end technologies that could be used to implement the client-side, but react-js was chosen: •Being a JavaScript library, has available a substantial online documentation; •Makes use of NPM (Node Package Manager) which contains numerous useful tools; • It’s very reusable - its component approach makes a simple piece-by-piece building method; •It fits every usage requirement; •The developer experience has past with it; As usual in web-applications and as a adequate way to keep inoperability between servers, communication between the back and front-end servers will be performed via REST (Representational state transfer). Therefore, the back-end is going to work as a REST API server. AREST API server should attend the following rules [15]: 28 4.1. Building Block View 29 •Client–server : The client and the server should be separate from each other and allowed to evolve individually and independently; •Stateless : Requests can be made independently of one another, and each request contains all of the data necessary to complete itself successfully; •Cache : The REST API should be designed to encourage the storage of cacheable data; •Uniform Interface : In order to obtain a uniform interface, multiple architectural constraints are needed to guide the behavior of components. •Layered System : The REST API should adopt a layered system, in which each layer has a specific functionality and responsibility; As for the back-end server, Node,js was chosen to implement it. Node.js is a JavaScript runtime environment which allows the infrastructure to build and run an application. It’s a light, scalable, and cross-platform way to execute code. It uses an event-driven I/O model which makes it extremely efficient and makes scalable network application possible. It offers its users a set of advantages, such as [11]: • Abstraction: Node.Js ’ single-threaded, event-driven architecture allows it to handle multiple simultaneous connections efficiently - while abstracting the developer of its implementation and make it simple to use; • The ever-growing NPM ( Node Package Manager ) gives developers multiple tools and modules to use; An important usage of this package manager was the Express.js framework, which its usage is going to be explained later in the document; • Being implemented in the same language as the front-end - JavaScript - it allows to keep a bigger inoperability, while increasing the efficiency of the development process; As mentioned before, the Express.js framework is used along Node.js . Express.js is a minimal and flexible Node.js web application framework that provides a robust set of features to develop web and mobile applications. Its main features [19] are: • Allowing to set up middlewares to respond to HTTP Requests - meaning that it’s possible to implement a RESTful API using this framework; • Defining a routing table which is used to perform different actions based on HTTP Method and URL. • Allowing to dynamically render HTML Pages based on passing arguments to templates; 4.1. Building Block View 30 Having chosen the technologies in which the application servers will be running, it is time to establish which database is going to be used. There are multiple SQL databases available to use, but PostgresSQL was chosen. These factors were taken in account: • Concurrency and Performance: Being a web-application, multiples accesses at the database will be done at the same time; Having a database that automatically takes control of the concurrency is a big advantage; • Data Types: Having modern data-types, PostgresSQL has shown itself as a major candidate for the job; • Popular choice to go along Node.js - being a popular choice means there’s a fair amount of frameworks to make its connection easy and well documented; 4.1.1Back-end server: Node.js + Express Being the front-end application implemented adopting React.js , the system’s back-end will use Node.js + Express as its back-end application. Together, these entities and its relationship will result in client-server architecture, as a requirement of a conventional RESTful API Server, where there back-end is need for a. business logic that shouldn’t be exposed as source code to the frontend application; b. establishing connections to third-party datasources - such as the database. The back-end directory has the following structure: --controllers --file-generate-logs --migrations --models --routes --views .env app.js auth.js config.js institutiondb knexfile.js middleware.js package.json 4.1. Building Block View 31 passport.js app.js is the main entry point of application, serving HTTP requests that reach the servers - these requests can be GET , POST , PUT , PATCH and DELETE . In order to keep an organized and understandable code, multiple endpoints were created for main parts of the database: /auth Endpoint for authentication requests, being the only public one; /teacher Endpoint for teacher-related requests; /degree Endpoint for degree-related requests; /course Endpoint for course-related requests; /department Endpoint for department-related requests; /assigned Endpoint for assigned-related requests; /class Endpoint for class-related requests; /shift Endpoint for shift-related requests; /types Endpoint for type-related requests; /generator Endpoint for output generator; /years Endpoint for years-related requests; /institution Endpoint for institution-related requests; Each one of these endpoints will have their own controller, inside of the controllers directory. For example, for every endpoint starting in /auth : const auth = require(’./controllers/auth’) const app = express() app.use(’/auth’, auth) it’s possible to find every correlated sub-route in the controllers/auth.js file: router.post(’/login’, async (req, res, next) => { /*database middleware */ }) router.post(’/ping’, async (req, res, next) => { /*database middleware */ }) 4.1. Building Block View 32 Database ORM One key aspect of using a back-end server is using it as platform to a database. A solution often opted for is the use of an ORM - Object-Relational-Mapper - which implements a technique that lets querying and manipulating data from a database using an object-oriented paradigm, or, in this case JavaScript. There are some ORM s available for PostgresSQL , such as sequelize , TypeORM and objection [ 13 ]. Taking in consideration and analyzing the previous options, objection [ 17 ] was chosen for taking full power of SQL and the underlying database engine while making it simple javascript queries, with an astounding documentation hat leaves no room for doubts. Having the database built, it’s required to establish relations for the ORM to work. For that same reason, the directory /models was made to contain a file for each main table in database, and each file containing a Model for that same table. A Model subclass represents a database table and instances of that class represent table rows. Model are created by inheriting from the Model class - provided by the objection package. An objection class can define relationships to other models using the static relationMappings property. As example, in the file /models/teacher.js there’s the models for teacher table, as follows: class Teacher extends Model { static get tableName () { return ’teacher’ } /* */ } This way the ORM assigns the class Teacher for the table teacher . Having in mind that the table teacher has relations with other tables, such as department (meaning that a given teacher belongs to given department), it’s appropriate to establish their relation: static get relationMappings() { const {Department} = require(’./department’); return { teacher_department: { relation: Model.BelongsToOneRelation, modelClass: Department, join: { from: ’teacher.department_id’, 4.1. Building Block View 39 /*props */ /> <div className="content"> <Header /*props */ /> <div className="body"> <Component /*props *// > </div> </div> </div> ) } } } Upon inspection, there are three main components along the HTML code in the render() function - <Sidebar/> , </Header> and <Component> - the former two were imported after being implemented as separate components, and later one comes as a parameter for the function which implements the Dashboard . When given page uses the Dashboard layout, it calls the withDashboard(component) function e.g. the /pages/teachers.jsx file: import withDashboard from ’../layout/dashboard’; class Teachers extends React.Component { /*implementation */ } export default withDashboard(Teachers) This way each file has only to render the code for the content part of the Dashboard. Authentication Part of authentication process was already discussed section 4.1.1, in respect to the back-end part of the procedure. In a quick rundown, the back-end after a successfully log-in sends the 4.1. Building Block View 40 user information as well the JWT token. The figure 16 resumes as an activity diagram how the procedure goes. Figure 8: Login procedure Once the login is successfully made, both the user’s profile data and JWT token are saved in the browser’s cookies. Initially, the aim was to save user profile data in Local Storage (browser storage in disk); however it revealed to be a problem from browser to browser. As the data to save is very small in size, it is kept in cookies as it works perfectly. As mentioned before, /pages/_app.js takes a major role for keeping an user authenticated. Next.js uses the App component to initialize pages. Overriding it lets take control of the page initialization. The procedure that occurs during a page initialization is easily described using a diagram, as seen in figure 9. Every time a user tries to access a page that isn’t the login page, the system will check the token and the route that is being accessed (and if the user has access to it). 4.1. Building Block View 41 Figure 9: Authentication procedure 4.1. Building Block View 42 Page Styling - SASS While the React.js generates the HTML to render a page, styling is still needed to make them look structured. Usually, CSS is the language chosen to style the HTML pages, and that’s no exception in this case, although with a help of a CSS pre-processor, SASS . It enables the use variables, mathematical operations, mixins, loops, functions, imports, and other interesting functionalities that make writing CSS much more powerful [3] - and easier. The styling folder /stylesheets has three subfolders - modules , partials and vendor . • Besides the subfolders, /stylesheets contains the main.sass , which is the primary SASS file which imports the remaining files; • The /modules sub-folder contains the files for common modules, such as the header in the dashboard; •The /partials sub-folder contains the files for each page; •Finally, the /vendor sub-folder contains third-party files. 4.1.3Output generator The output generator is an important feature of the system, given that it produces the final result that’s going to be imported by other software. In a point of view of development, it was one of the most difficult features to implement due to its extensive planning. Initially, it was established which database the system is going to adapt to; for this same reason the developer requested a sample of the database for Instituto Politécnico de Bragança, the target user of this system. This part required a manual analysis work, and building an excel spreadsheet revealed itself an excellent employer for the job. The analysis work was split in 4parts: a. Inspect the database file and understand which tables needed to be supplied with data; Upon confirmation by coordinator the developer proceeded to next step; b. For each table needed do provide data to, the developer linked each column in that given table to the systems database containing the same data; c. For each table needed do provide data to, it was established its relation to other tables (foreign-keys); d. Having the information from the step before, settled the order by which each table will be fed data. 4.1. Building Block View 43 The item b. of the previous list exposed a problem in the system’s database - it was missing some IPB ’s specific fields. However, this problem was not of bigger extent because its fix was actually simple - add columns to existent tables, no need to create new tables or relations. This new columns were added with an underscore prefix, _ , meaning they’re specific fields. E.g., the Teacher table added the new columns _ipb_cod_escola and _ipb_emp_num . JSON to SQL: A failed approach The first approach taken to implement this feature was to build tables with JSON and later convert it to SQL . Given the project is implemented mainly using JavaScript , it was intended to follow the same strategy for generating the output. The ORM once queried, presents its results in JSON . The plan was to build a representation of a SQL database using JSON - a JavaScript Object Connotation. By the definition of both, it seems already a detrimental solution to the problem - but it was possible, and so, the plan proceeded. The related tables would be nested, and its properties stored in variables. There are tools that would transform JSON into SQL - such as JSON-SQL [ 1 ] , which was used. As the implementation continued, the code got chaotic, hard to read and debug - it was hard to keep track of code and the data. At the end, it seemed pointless and this approach was abandoned. ETL - Extract, Transform, Load After discarding the last plan, a new research was made in interest to find a solid solution to this problem. The procedure that correctly translates what’s intended is ETL - which is short for extract, transform, load - three database functions that are combined into one tool to pull data out of one database and place it into another database. After inspecting numerous documentation for it online, an article [ 10 ] described a simple yet detailed method to do it, as well as a real-word example. The first step is to analyse both data models, and work out a mapping for the data between - which already has been done, as stated before, with the help of the spreadsheets. The next step was to design the architecture for the custom ETL solution. As it is possible to check in the figure 10, there are two new schemas - migrate and gal. Figure 10: ETL Architecture 4.1. Building Block View 44 The migrate schema has the structure of the destination database, containing also the identification from the originating table i.e. the systems corresponding table. In the other hand, the gal schema has only the structure of the destination database, ready to import. The migrate schema is used in order to assert the many relations in the structure of the destination database. To do this, a SQL file script was made and loaded into the database, which migrates the data from one schema to another. In a simple example: CREATE TABLE migrate.departamento ( id SERIAL PRIMARY KEY, old_id int NOT NULL, nome varchar(100) NOT NULL DEFAULT ’’, abrev varchar(10) NOT NULL DEFAULT ’’, ipb_cod_escola int DEFAULT NULL, ipb_emp_ccusto int DEFAULT NULL ); CREATE TABLE migrate.docente ( id SERIAL PRIMARY KEY, old_id int NOT NULL, id_depart int DEFAULT NULL REFERENCES migrate.departamento (id) ON DELETE CASCADE, nome text NOT NULL DEFAULT ’’, abrev text NOT NULL DEFAULT ’’, eti float NOT NULL DEFAULT ’1’, mail text DEFAULT NULL, credito float NOT NULL DEFAULT ’0’, ipb_cod_escola int DEFAULT NULL, ipb_emp_num int DEFAULT NULL ); The migrate.docente table needs to reference migrate.departamento(id) . This is easily done accessing the old_id from migrate.departamento and retrieving the actual new id. A new SQL script was imported to the database, this one being responsible for migrating from the contents from migrate to gal. This step was simpler as it will replicate the same values of migrate, excluding only the old_id for each table. 4.1. Building Block View 45 Having now the data transformed into the structure of the destination database, the next step is to gather said data and create a script to load it. As opposed to the system’s database - which is written using PostgresSQL - the destination database is running on MySQL , adding a new level of complexity. The PostgresSQL database offers the command pg_dump that, for given a database, creates an output containing an SQL script to load the data. After performing that command and save the output to a file, the final step was to convert it to MySQL accepted script. The tool PG2MySQL Converter [ 4 ] solves that problem, as it uses a command-line solution to solve the problem. It’s now a matter of joining all the procedures above mentioned in a single, executable and repeatable script that would work every time. Thus, a shell script was assembled: clear; printf "${RED}-> creating migration schema...${NC}\n" psql -U admin -d institutiondb < /home/admin/db/migrate.sql printf "${RED}-> migrating data..${NC}\n" psql -U admin -d institutiondb -c "select proc1();"; printf "${RED}-> creating gal schema...${NC}\n" psql -U admin -d institutiondb < /home/admin/db/gal.sql printf "${RED}-> migrating data..${NC}\n" psql -U admin -d institutiondb -c "select proc2();"; pg_dump --no-acl --no-owner --format p --data-only institutiondb -n gal -f /home/admin/db/pgfile.sql; php /home/admin/db/converter/pg2mysql_cli.php /home/admin/db/pgfile.sql /home/admin/db/mysqlfile.sql Breaking the script in steps: a. Imports the migrate schema to the database; if it already exists, deletes the existent and its data; b. Migrates the data from the public schema (the system’s database) to the migrate schema c. Imports the gal schema to the database; if it already exists, deletes the existent and its data; d. Migrates the data from the migrate schema to the gal schema 4.2. Runtime View 46 e. Dumps data from the gal schema to a file; f. Using PG2MySQL Converter , convert the SQL above generated into a MySQL appropriate script. When the user requests the MySQL file, the back-end server executes the script above, waits for it to finish, loads the mysqlfile.sql and sends it to the user. 4.2 runtime view In this section will be covered the user’s interaction with the system. 4.2.1General Data Figure 11: Activity Diagram for data insertion There are two possible ways to insert data into the system - either manually or through file uploading. The figure 11 shows an activity diagram that helps understand the interaction when loading data into the system. a. The user inserts data - manually or uploading a file; 4.2. Runtime View 47 b. The user submits the data above inserted; c. The front-end validates the submitted data; a) If the data is invalid or missing one or more fields, the user is warned and the data not sent to the back-end server; b) Otherwise, the data is sent to the back-end server; i. The back-end-server attempts to insert it into the database - it could fail due to conflicts; •Some examples of conflicts could be e.g. some repeated values; c) The front-end receives response from the back-end server; i. If the response is positive, indicates success; ii. Otherwise, indicates which fields cause conflict; 4.2.2Association There’s always a course for each different degree, i.e. the same Analysis will result in two different courses for two different courses, each own having their own code and a code for the degree associated. Initially, the system would load both independently and later the user would link them; later in development, the system links them automatically. The activity diagram shown in 12 clarifies the interaction between the user and system in the section in charge of managing the association between degrees and courses. In a brief rundown, the link is made when a user inserts a new course: •The system validates the data, as seen previously in 11; • The system checks if the degree code associated with the course exists in the database: –If it doesn’t exists, the data isn’t inserted into the database; ∗The system displays error, detailing the degree missing; –If it does, the system inserts the data into the database and displays success; 4.2.3Shifts The shift management section is an important feature of the system. For that reason, it was designed an activity diagram - shown in 13 - to better illustrate its interaction with the user. a. The user gets a selection of courses from its department and picks one; 4.2. Runtime View 48 Figure 12: Activity Diagram for assigning a degree to a course b. The system displays the number of participants that were assigned to the selected course, as well the existent shifts; c. The user selects the class; a) The user can select a shift if more than one exist; i. The user can delete the selected shift; ii. The user can assign one or more teachers the selected shift; iii. The user can join this course with another one; •The course to join has to be from the same department; •The course has to have the same number of classes; •The course has to have the associated teachers; b) The user can add a new shift; 5.2. Results 55 There are two sub-menus in this page - one for class types and another for the academic years, respectively. This allows for a superior data normalization. Both sub-menus support a manual or mass insertion, the later through a file upload. The chosen format data for data uploading was .csv , for every section as well. Deleting one of these will lead to a CASCADE delete - this means that the relation course-type is deleted for every type deleted. Later, the administrator shall update the courses to make sure they have the correct types. Next in the menu, there is the Departments management. In this page, as displayed in the figure 18, the administrator should upload the departments along the department’s responsible teacher. Figure 18: Login Page When a department list is successfully inserted, the system generates a random password for each responsible teacher added, and returns it to the screen for later use. In this same page there are the features to delete or see extra information for each department. Deleting a department will lead to: •Delete every teacher attributed to that department; •Delete every course attributed to that department, and consequentially: –deleting every degree association made to it; –deleting every shift made to it. 5.2. Results 56 Going further the list there is the Degrees page, as seen in figure 16. In this page it’s possible to insert data both manually or through file uploading. For every courses added, there is the possibility to edit it - rendering a form such as the manually inserting one, but having already having the fields filled. When a degree is deleted the following occurs: •Every association for that degree is deleted; • If a shift contains that same degree, it’ll be deleted; However if, that shift is shared with another degree, it’ll be kept. To test the system’s error handling, a file containing a value already in database was loaded. As expected, the system didn’t allow the insert, pointing out the fields in conflict. The figure 20 illustrates this instance. Figure 19: Degree management page 5.2. Results 57 Figure 20: Degree management page - conflict Next in the menu is the course management page, as the figure 21 shows. Figure 21: Course management page In this page it is possible to add, edit and remove courses. The figure illustrates the editing of an existing course. Similarly, to add courses manually the input box to render is the same as editing - however, the fields are empty. It is also possible to insert batch data - through file uploading. 5.2. Results 58 When a new course is submitted, the system analyzes if the degree code which is associated to already exists in the system - if it exists, it is inserted into the database; if not, the user is warned and not added. Removing a course will lead to: •Delete every class created by the given course; Therefore, resulting in: –Deleting every shift created for each class; •Delete every degree association for the given course; Figure 22: Course associations page The figure 22 illustrates the page which displays the associations between degrees and courses. For a given degree, year and semester, the pages displays the courses attributed for that selection. Next on the available pages for the administrator, is the configuration page - as the figure 23 suggests. There two sub-menus in this page, for each section: •Versioning The administrator can consult, change or create a new version; If a version is deleted: –Every association and shift for that given version is deleted; •Information The administrator can change the name that shows up in the sidebar; Currently it is Testing 5.2. Results 59 Figure 23: Configuration page Lastly, there’s the data export page for the administrator, as seen in the 24. Here, the administrator requests the system to generate a SQL file given every data worked in the system; 5.2. Results 60 Figure 24: Data export page 5.2.3Head of Department The first accessible menu for the head of department is teacher management - as seen in 25. In this section, the user can insert teacher data either manually or via file upload, to batch upload. When a teacher is deleted from the system, the system will delete classes associated to them to avoid problems - and therefore shifts for that same class will also be deleted. 5.2. Results 61 Figure 25: Teacher management page The second item on the menu for head of department is class management, as 26 suggests. Four each course selected, the head-of-department can: Figure 26: Class management page •Set the types of classes existent for that course; For each type of class added: –The number of classes and the duration of each; 5.2. Results 62 •Associate teachers for that course; • Replicate the data inserted in the previous points via another course - as seen in the 28 If a class has already shifts attributed to it, the system wont let the user edit or delete it - to avoid data mismanagement. Figure 27: Class management page - data replication Next on the list, there is the shift management page. In this page, for a class of a given course, the head of department can: •Add a new shift; •For each existent shift, it’s possible to: –Select the teachers responsible for it; –Join it with another course: ∗ For this, both courses should have the same attributed teachers and number (as well duration) of classes; Otherwise the system won’t let the user join them. – Delete the selected course from this shift - if however the course is the only constitute of the shift, the system will delete the whole shift; –Delete the whole shift; 5.2. Results 63 Figure 28: Data export page The last option of the menu selection available for the head of department is Costs, as seen in 29. In this section, it’s listed the number of weekly hours attributed for each teacher in the department. From that number, it is also possible to consult how it is distributed. For a quicker navigation, there are three ways to filter the list: •by teachers with no workload i.e. zero weekly hours; •by name or id; •by number of hours. 5.3. Discussion 64 Figure 29: Costs Page 5.3 discussion The results above allows a bigger perception of the system developed and to extrapolate some important aspects of its functionalities and features. Reinstating the quality goals set before its implementation, it may be regarded as certain that the system fullfills all of them: the simplicity is evident, as every feature in the system offers a self-explanatory use; the features are well distributed and easily accessible without any bureaucracy, revealing its efficiency ; the sleek and modern design displays a high degree of how attractive it is; and the all the testing made previously how much testable it got. In the scale of MoSCoW previously used to set the priority of the requisites, it is possible to say with certainty that all the must requirements - i.e. level 1priority - were implemented with success, as well a fair part of the should . Unfortunately a feature that wasn’t implemented on time - the possibility for the teachers add their preferences - but further addressing on this subject will be done in the future work. The main objective of this dissertation was to normalize the input of data into a system, which is accomplished. The main shared data is restricted from the beginning i.e. the types and academic years. With the use of types, every type has to be in conformity with those offered by the institution. This system assures that every data is inserted in the same way, as opposed as is currently done - a table done by each department with no normalization from department to department - which allows to save time not only establishing the teacher service, but also when translating these tables to SQL manually.