Database Tool Basics: Creating a Database, Tables, Fields and Records in Base

Hello students, welcome to Kwickprep. Your school keeps admission details for hundreds of students. Where does all that data live, and how do you build such a store yourself? Today, in LibreOffice Base, we will create a database and design a table. We will choose field types, set a primary key and work with records.

First, some words. A database is an organised collection of related data, like all admission details of a school. A DBMS, or database management system, is software that creates and manages databases. A table stores data about one topic in rows and columns. A field is one column, like Name, and a record is one row, holding all details of one student.

We will use LibreOffice Base. Base is a free and open-source DBMS that comes with the LibreOffice office suite. It saves the whole database in a single file with the extension .odb. That one file holds your tables, queries, forms and reports. Base works on Windows, Linux and macOS.

Let us create a database step by step. Open LibreOffice and choose File, then New, then Database. The Database Wizard opens, and we select Create a new database, then click Next. On the next step, keep Yes, register the database, and tick Open the database for editing, then click Finish. Base asks for a file name, so type School and click Save. The database window opens.

The main window has four objects listed on the left. Tables store the actual data. Queries ask questions of the data, like who scored above ninety. Forms give a friendly screen for entering data. Reports arrange data neatly for printing.

Click Tables on the left, and Base shows the tasks for tables. The first way is Design View, where you define every field yourself. The second is the Table Wizard, which offers ready sample tables to pick fields from. Design View gives full control, and it is the method exams expect. So click Create Table in Design View.

Design View shows a grid with three columns. In Field Name, you type the name of the field, such as Adm No, short for admission number. In Field Type, you choose what kind of data it holds, such as Integer. In Description, you may type a note that explains the field. Each row of this grid defines one field of the table.

Let us design a student table with five fields. Adm No is an Integer, and it will be our key. Name is Text, which stores letters. DOB holds the date of birth, so it is a Date. Fees is Decimal, because fees can have paise. Hostel is Yes or No, because a student either stays in the hostel or does not.

Base offers many field types, and these are the common ones. Integer stores whole numbers. Text stores letters and digits, like a name. Decimal stores exact numbers with a decimal point, good for money. Date stores a date. Yes or No stores only two values. Memo stores long text, like remarks about a student.

Click any field, and its Field Properties appear at the bottom of the window. Here are the most useful ones. Length sets the most characters a Text field can hold, like fifty for a name. Entry required, set to Yes, means the field can never be left empty. AutoValue, set to Yes on an Integer key, makes Base number each new record automatically. Default value is filled in when nothing is typed.

Every table should have a primary key. A primary key is a field whose value is different for every record, so it identifies each row. To set it, right-click the small grey box to the left of Adm No. Choose Primary Key, and a small key icon appears there. Name cannot be the key, because two students can have the same name.

Now save the design. Press Ctrl+S or click the Save button. Type the table name, student, and click OK. If you forgot the primary key, Base asks whether it should create one, so think before clicking Yes. Close Design View, and the student table appears in the Tables list.

Now let us enter records. Double-click the student table, and it opens in Table Data View, which looks like a spreadsheet. Type a value, then press Tab to move to the next field. When you move to the next row, Base saves the record automatically. The row marked with a star at the bottom is always ready for a new record.

Suppose Riya's fees were typed wrongly. Click that cell and type the new value. Move to another row, and Base saves the change. If you change your mind before moving away, press Escape to cancel. Remember, these changes are saved straight into the database, so there is no undo later.

Now suppose a student leaves the school. Click the grey box at the left of that row to select the whole record. Right-click and choose Delete Rows. Base asks you to confirm, so click Yes. Be careful, because a deleted record cannot be brought back.

Pause and predict. Adm No is the primary key of our table. A new record is typed with Adm No one zero four five. But one zero four five already belongs to Riya. What happens when you try to save? Base shows an error and refuses the record, because a primary key value can never repeat.

Let us revise what we learned today. File, New, Database runs the wizard and creates an .odb file. Create Table in Design View lets you type field names, types and descriptions. Choose the right field type, then right-click a field to make it the primary key. Double-click the table to enter, edit and delete records. Now build a table of your own, like a class library list.