School Result Management System in LibreOffice Base [Projects]

2732 views

In this tutorial, I will walk you through the steps to create a simple school result management system in LibreOffice Base.

This article is part of a series of project articles as requested by several readers to teach LibreOffice Base in various non-profit schools and for students.

The steps mentioned here are basics and targets for absolute beginners. You can use this article as a starting point and customize it based on your needs.

School Result Management System in LibreOffice Base

Designing the database

The first step is to design the databases and tables which will hold the data. Since the result management system may sustain for many years, it’s important to focus on the types of student data you want to capture.

So, ideally, it requires a little bit of thinking on your side. Your primary focus would be what you want to include in your database. To simplify things, here is the table structure we will create for the main database.

If you want to verify the data types and lengths, visit this page.

Table: Student Details (student_details)

Columns:

  • Student ID (Primary Key) – Integer – Unique identifier for each student
  • Student Name – Text – Full name of the student
  • Gender – Text – Gender of the student (e.g. Male, Female, Other)
  • Date of Birth – Date – Date of birth of the student
  • Address – Long text – Address of the student

Table: Results (results)

Columns:

  • Result ID (Primary Key) – Integer – Unique identifier for each result
  • Student ID (Foreign Key) – Integer – ID of the student whose result is being recorded
  • Subject ID (Foreign Key) – Integer – ID of the subject for which the result is being recorded
  • Term ID (Foreign Key) – Integer – ID of the term for which the result is being recorded
  • Marks Obtained – Integer – Marks obtained by the student in the subject

Table: Subjects (subjects)

Columns:

  • Subject ID (Primary Key) – Integer – Unique identifier for each subject
  • Subject Name – Text – Name of the subject (e.g. English, Maths, Science)

Table: Terms (terms)

Columns:

  • Term ID (Primary Key) – Integer – Unique identifier for each term
  • Term Name – Text – Name of the term (e.g. 1st Term, 2nd Term, Final Term)

Create the database and tables

I have written in detail about creating databases and tables on this page.

However, to keep it simple, open LibreOffice Base and create a new database to get started. Go to File > New > Database, and choose to create a new database.

Name your database and choose a location to save it. Make sure to select the options “register the database” and “open the database for editing”.

School result management system database
School result management system database

Once you have created your database, it’s time to create the tables to store your data. In this case, we need a table to store student details and other data as per the design section above. Let’s start with the student details table.

To create a new table, go to Tables in the left-hand menu and click on Create Table in Design View. In the design view, add the fields you want to include in your table, such as Student ID, Student Name, Gender, Date of Birth, and Address.

You can also set the data types and primary keys for each field.

Note: You can read more about the data types in LibreOffice Base as per HSQLDB standard here. This page explains the size of each type, such as integer, varchar and so on.

Student details
Student details

Note:

You may want to set AutoValue = YES for the primary key field. It will auto-increment with an integer from 0 to the max size of the data type of that column. Alternatively, you may assign your own unique ID as well for the column. However, it’s better to set it to auto to avoid overhead.

Setting up autoincrement value for the primary key
Setting up autoincrement value for the primary key

For non-primary key fields, make sure to choose whether the column accepts a blank or null value. You can set it using the “Entry Required: Yes or No” field for each column. See the below image.

Make sure to set whether column accepts null value
Make sure to set whether the column accepts a null value

After creating the student details table, we need to create the rest of the tables as per the design. Repeat the same steps for results, subjects and terms table. Follow the data types from the above design section.

Furthermore, if you want to have your own design, you can do the same as well. It is not necessary to follow the exact table designs above.

Here are the rest of the table structures as per the above design.

results table
results table
terms table
terms table
subjects table
subjects table

Establish relationships

Once you are done creating the tables, it’s time to establish the relationships between the key columns. From the main Base window menu, select Tools > Relationships. And then select all the tables. In the relationship window, click on the relationship toolbar icon and connect the key columns.

You can learn how to establish relationships on this page. So, after establishing the relationship for this exercise, the relationship diagram should look like this:

Established relationships between tables
Established relationships between tables

Creating forms

At this stage, all the tables are empty. There are no data. If you already have data available you can start adding them using LibreOffice Base.

But, it’s better to use the forms functionality which is easy and better for usability. The forms allow you to enter, view, search and modify the data for your tables in a structured way.

Let’s start creating the first form for the student_details table.

To create a new form, go to Forms in the left-hand menu and click on “Use Wizard to Create Form..”. It’s better to use Wizard than designing on your own form because the wizard takes care of many items and is easy for beginners.

Option to create form inside LibreOffice Database
Option to create a form inside LibreOffice Database

The wizard will take you through a series of steps. First, select the table for which you want to create the form for. And add the columns which you want to display/modify using the form. Once done, hit next.

field selection
field selection

In the next screen, skip the subform creation for now. The arrange controls screen gives you how you want to display the fields and labels on the form. You can choose either grid, vertical or horizontal position. Choose what you want. See the below video.

Link to the video if it doesn’t play in Safari [control placement.webm]

Control placement in form design

In the “next data “Set data entry” option, choose the option for adding or viewing the table data. You can choose only one option for one form. If you want a separate form for adding data and viewing data, you need to create two forms.

Since the table is empty at the beginning, let’s create this to add data to student_details table.

Data entry options
Data entry options

Hit next and choose any style you want on the “Apply Style” panel. Then set the name of the form. Since this is to add data to student_details table, I am naming it as frmAdd_student_details so that I can identify them easily in the form listing.

Give a form name
Give a form name

Hit finish once done. You can now see the form in the LibreOffice Base main window under the Forms category.

Here you can find two main options in the context menu. You can select “Open” to run the form, i.e. adding data for this case. Or you can select “Edit” to customise the design of the form, such as colour, placement of controls and so on.

Adding data

Since the form we just created is for adding data, let’s select Open. Once you do that, the entire form executes and opens up in a separate window.

Add records using the forms and controls
Add records using the forms and controls.

You can now add the values for each field and hit TAB at the final entry field to save the record in the table. Once you do that, you can see the record count increased.

You can also use the navigation buttons to view the records. Alternatively, you can create another form to view the data only. Once the data entry is complete, you can simply close the form window. Here’s a video of adding two records for demonstration.

Link to the video if it doesn’t play in Safari [adding-records.webm]

Adding records using the form

Using the table view feature, you can verify the records as well from the Base main window.

Student details table showing added records
Student details table showing added records

Adding sample data via SQL

If you don’t want to use forms to add data, Or, you have thousands of data to be added, its better to use SQL Query. For demonstration, I want to add the following set of data for each table.

Student Details:

Student IDStudent NameGenderDate of BirthAddress
1John SmithMale2004-05-12123 Main St, Anytown
2Jane DoeFemale2005-08-22456 Oak Ave, Othertown
3Alex JohnsonOther2004-10-07789 Pine Rd, Anothertown

Results:

Result IDStudent IDSubject IDTerm IDMarks Obtained
111185
212192
321178
422189
531192
632187
711290
812294
921284
1022287
1131295
1232289
1311392
1412388
1521385
1622391
1731397
1832390

Subjects:

Subject IDSubject Name
1English
2Maths
3Science

Terms:

Term IDTerm Name
11st Term
22nd Term
3Final Term

To do that, I will use the following SQL statements.

INSERT INTO "student_details" VALUES (1,'John Smith', 'Male', '2004-05-12', '123 Main St, Anytown');
INSERT INTO "student_details" VALUES (2,'Jane Doe', 'Female', '2005-08-22', '456 Oak Ave, Othertown');
INSERT INTO "student_details" VALUES (3,'Alex Johnson', 'Other', '2004-10-07', '789 Pine Rd, Anothertown');
INSERT INTO "subjects" VALUES (1, 'English');
INSERT INTO "subjects" VALUES (2, 'Maths');
INSERT INTO "subjects" VALUES (3, 'Science');
INSERT INTO "terms" VALUES (1, '1st Term');
INSERT INTO "terms" VALUES (2, '2nd Term');
INSERT INTO "terms" VALUES (3, 'Final Term');
INSERT INTO "results" VALUES (1, 1, 1, 1, 85);
INSERT INTO "results" VALUES (2, 1, 2, 1, 92);
INSERT INTO "results" VALUES (3, 2, 1, 1, 78);
INSERT INTO "results" VALUES (4, 2, 2, 1, 89);
INSERT INTO "results" VALUES (5, 3, 1, 1, 92);
INSERT INTO "results" VALUES (6, 3, 2, 1, 87);
INSERT INTO "results" VALUES (7, 1, 1, 2, 90);
INSERT INTO "results" VALUES (8, 1, 2, 2, 94);
INSERT INTO "results" VALUES (9, 2, 1, 2, 84);
INSERT INTO "results" VALUES (10, 2, 2, 2, 87);
INSERT INTO "results" VALUES (11, 3, 1, 2, 95);
INSERT INTO "results" VALUES (12, 3, 2, 2, 89);
INSERT INTO "results" VALUES (13, 1, 1, 3, 92);
INSERT INTO "results" VALUES (14, 1, 2, 3, 88);
INSERT INTO "results" VALUES (15, 2, 1, 3, 85);
INSERT INTO "results" VALUES (16, 2, 2, 3, 91);
INSERT INTO "results" VALUES (17, 3, 1, 3, 97);
INSERT INTO "results" VALUES (18, 3, 2, 3, 90);

To run INSERT/UPDATE SQL, you need to go to Menu > Tools > SQL. In the Query window, enter the SQL query and hit Execute.

Executing update-insert query in LibreOffice Base
Executing update-insert query in LibreOffice Base

Repeat the above steps for each SQL DML statement to fill up all the tables. This gives you more flexibility, saving time than using the forms to update the tables.

Creating Query

One of the essential functions of LibreOffice Base is Query. The Query function allows you to create many custom queries, which you can execute with a single click to view the current data.

Let’s create a simple query to display all the data from student_details table.

Open the database, and on the left-hand menu, click “Queries”. Here you have three options:

  • Create a query in the design view
  • Create using wizard
  • Create using SQL
Query option in LibreOffice Base window
Query option in LibreOffice Base window

Let’s choose “Create query in design view”. It is simple to understand.

In the Query window, you should see a small dialog with all the tables in the database. Select a table where you want to run the query and hit Add. You can also double-click on the table to add it to the query canvas.

Add table to query view
Add table to query view

At the bottom of the window, you should see a tabular grid. Here you can select the columns of the table by dragging them down. You can either select “*” or individual columns.

For example, if I want to build a query to display only the name and gender, I will drag two columns down as per the below image.

Building a query
Building a query

That should complete the design process. Now, to run the query, press F5. Or select Edit > Run query. You should see the results at the top of the window.

Running the query
Running the query

Now, you can modify it again as per your need to build your output. If you want to remove the columns, you can right-click on the column at the bottom grid and hit delete.

Once you are ready, save the query using CTRL+S.

You should see the query name in the main window under the Query pane. You can now simply double-click on it to run.

What’s next

Using the above steps, you can create separate forms for each table in your design. Also, create separate forms for viewing the data. Repeat the steps outlined above.

Conclusion

In conclusion, creating a simple school result management system in the LibreOffice Base database is a great way to manage student data and analyze their results. By following the steps outlined in this tutorial, you should be able to set up a basic system that meets your needs. If you encounter any errors, such as data not being displayed or forms not functioning correctly, be sure to check your design and data entry for any mistakes. Also, you can drop a comment below. I will publish a video tutorial for this shortly for better clarity.

Join our Telegram channel and stay informed on the move.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments