Chapter 9: Starting with LibreOffice Base
Data Types
A data type refers to the type of data that will be stored in that particular field.
The memory size of a field varies according to its data type. There are six
different types of data type in LibreOffice Base –
Text Data Types
Numeric Data Type
Currency Data Type
Date Data Type
Boolean Data Type
Binary Data Type
Text Data Types
The text data consists of a mix of special characters, numbers, and letters.
With text data, no mathematical operations may be carried out. PAN card
numbers, names, marks, and other data types are examples of text data.
The lists of various data types that can store textual data –
Name Data Type Description
Memo data type allows to store text data up to
Memo LONGVARCHAR
64000 characters.
Character data type is used to enter fixed number of characters
Text(fix) CHAR
for example AADHAR NO, ACCOUNT NO, PASSPORT NO etc.
Stores upto the specified length, the number of bytes allocated
Text VARCHAR
depends on the number of characters entered by the user.
Numeric Data Type
Numeric Data type contains numbers. The number can be integer, real, float
or double.
Name Data type Signed Range
Tiny Integer TYNYINT No 0-255
Small Integer SMALLINT Yes -32768 to 32768
Integer INTEGER Yes -2.14×109 to 2.14×109
BigInt BIGINT Yes -2.3×1018 to 2.3×1018
Number NUMERIC Yes Unlimited
Decimal DECIMAL Yes Unlimited
Float FLOAT Yes
Real REAL Yes 5×10(-324) to 1.79×10(308)
Double DOUBLE Yes
List of different numeric data types are –
Currency Data Type
Currency Data type contains monetary values and can store currencies of
different countries.
Date Data Type
This data type is basically used for date and time. There are various formats
used to store date and time.
List of various forms of date data types are –
Name Description
Date Stores the year, month and day as it is stored in the system.
Time Stores the time of the day as hour, minute and second.
Timestamp Stores date and time information at once.
Boolean
In boolean data type there are only two values – True or False, This also can
be given in multiple formats like Yes/No, True/False, On/Off.
Binary
The binary datatype is used to store digital images and sounds.
Starting with LibreOffice Base
LibreOffice base is a database management software belonging to LibreOffice
suite, It is free and open source software for creating and managing data. It
also allows you to create a query for retrieving desired results from the
database.
Steps for starting LibreOffice Base –
Step 1: Click on Start > LibreOffice base
Step 2: In Database Wizard, Click on Create a new database
Step 3: After Creating a new database, Click on Yes, register the
database for me
Step 4: Click on Finish, Now your database will be created
Creating a Table
After creating a database now you will start working with objects of the
database, The first object we are going to create is Create Table. There are
two different method to create table –
1. Create Table using design View
2. Create Table using a Wizard
Creating Table in Design View
Step 1: Click on Table
Step 2: Click on Create Table in Design View
Step 3: Enter Field Name, Field Type and Description
Step 4: Click on Save
Steps for Creating Table using a Wizard
Step 1: Click on Table
Step 2: Click on Use Wizard to Create Table
Step 3: Select the field which you want
Step 4: Click on Finish
: Working with Multiple Tables
After creating tables in a database, you can also create relationships between
two or more tables to control data redundancy and inconsistency. When you
create a relationship between two or more tables then adding or updating a
record in one table reflects the changes in all the related tables.
Master table vs Transaction table
If the School wants to manage student fees collection in a database, then they
will create two tables, one for Student Details and second for Free details,
here the Student Details will be master table and Fee details will be
transaction table.
Relationships between Tables
A relationship is a connection between two or more tables that contain data,
the relationships between tables are based on a common field. This
relationship helps to improve table structures and reduce redundancy of the
data.
Types of Relationships
The relationships between two or more tables in a database is based on the
number of records, Primarily there are three different types of relationships
that can be set up between two or more tables.
1. One-to-One
2. One-to-many
3. Many-to-Many
One-to-One relationship
In a One-to-One relationship, the master table and transaction table both have
one record.
One-to-Many relationship
In a One-to-Many relationship, the master table having one record and
transaction table having multiple records. This is a very common type of
relationship between the tables in the database.
Many-to-Many relationship
In a One-to-Many relationship, the master table has multiple records and the
transaction table has multiple records.
Advantages of Relating Tables in a Database
1. A relationship helps to prevent data redundancy.
2. Relationships between tables help to restrict from entering invalid data
in the referenced field.
3. It helps to prevent missing data by keeping deleted data from getting
out of sync.
4. Any update in the master table is automatically reflected in the
transaction tables.
Primary Key, Composite Primary Key and Foreign Key in a
Database
In the RDBMS data can be integrated using keys. These are Primary Key,
Composite Primary Key, and Foreign Key, Key are used to make the
relationship between the tables.
1. Primary Key – This unique field is called the Primary Key (PK). primary
key is a field or a set of fields that uniquely identify each record in a
table. A primary key must be unique and cannot contain null values.
2. Composite Primary Key – A composite primary key is a primary key
that consists of two or more fields that together uniquely identify each
record in a table.
3. Foreign Key – Foreign key is used to link one table to another table
using referencing where the first table will be primary key, and the
second table will be foreign key. It is used to make relationship between
two tables.
Referential Integrity
As per the principle rule of the database, no unmatched foreign key values
should exist in the database. Referential integrity processes concentrate on
ensuring data consistency and the link between tables.
LibreOffice Base gives us following four options to maintain referential
integrity –
1. No action – This is default option, this option states that no update or
delete are allowed in the master table if any related record exists in the
transaction table.
2. Update cascade – This option allows to delete or update the
referenced field but the related records that are also available in any
transaction tables will also be deleted or updated.
3. Set NULL – This option assigns NULL value to all the records in the
master record that are deleted or updated.
4. Set default – This option assigns a fixed default value to all the related
fields if the master record deleted or updated.
Queries in Base
In the database data is stored in an organized manner which helps to retrieve
data quickly and accurately. To search and retrieve the desired data from the
database, we have to give its specifications to DBMS. Such specifications are
given to the database in the form of queries.
Query
A query is the most important feature of DBMS. Query is a request for data,
query helps to retrieve and display data from one or more tables in a
database. This is done by giving criteria to the DBMS.
A query can be created in three ways –
1. Using a Wizard
2. In Design View
3. In SQL view
Database Languages
Database Languages having two type:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
DDL Statements:
Create: Using this statement, a database or set of tables can be
created.
Alternate: This statement is used to change the table’s structure.
Drop: This statement is used to remove database objects from the
system.
DML statements:
SELECT: The statement “SELECT” is used to get data from the
database.
INSERT: The statement “INSERT” is used to add a new record to the
database.
DELETE: The database can be cleaned out by using the statement
DELETE.
UPDATE: This statement is used to modify the database’s information.
Select Statement
Select statement is most important for retrieving and for displaying data from a
database.
Syntax of Select Statement is –
SELECT * FROM <TABLENAME>;
The SELECT statement has many optional clauses:
WHERE specifies which rows to retrieve.
ORDER BY specifies an order in which to return the rows.
Question 1 – Write a Query to display all record from the table;
Table Name – product
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Answer: Select * from product;
Question 2 – Write a Query to display product name from the table;
Answer: Select Product_Name from product;
Question 3 – Write a Query to display Product_Name
and Price from the table;
Answer: Select Product_Name, Price from product;
Question 4 – Write a Query to find the total no of
quantity available in table;
Answer: Select sum(quantity) from product;
Question 5- Display the total amount of each item.
The amount must be calculated as the price
multiplied by quantity for each item.
Answer: Select Product_No, Product_Name, Price * Quantity from
product;
Question 6- Write a Query to display the product
whose price is less than 90
Answer: Select * from product where price < 90;
Question 7- Write a Query to find the total amount
of the Shampoo product;
Answer: Select Price*Quantity from product where Product_Name =
‘Shampoo’;
Question 8- Write a Query to display the data whose
quantity is equal to 80.
Answer: Select * from product where quantity = 80;
Question 9- Write a Query to display a list of
Products whose Price between 40 to 120.
Answer: Select * from product where Price >= 40 and Price <=
120;
Question 10- Write a Query to display the list of
Product_Name in alphabetical order.
Answer: Select * from product order by Product_Name ASC;
Question 11- Write a Query to display the list of
Price in descending order.
Answer: Select * from product order by Price DESC;
UPDATE statement
Update statement is used to update existing records present in database. The
updated statement will apply using WHERE clause.
Syntax of Update Statement –
UPDATE <table name> SET = value [, column_name = value ...]
[WHERE ];
Table Name – product
Product_No Product_Name Price Quantity
25 Soap 40 80
31 Powder 80 30
45 Shampoo 250 25
52 Soap Box 120 100
Question – Write a Query to update the price of
Shampoo in the product table.
Answer: Update product Set Price = 300 where Price = 250;
Question – Write a Query to update the Quantity of
Powder in the product table.
Answer: Update product Set Quantity = 50 where Product_Name =
‘Powder’;
Forms and Reports
LibreOffice Base provides the Form feature for data entry purposes and report
feature for displaying the data.
Form
A form is a database object with a user-friendly interface that allows data entry
and viewing in a visually attractive and accessible method. It serves as the
front end for entering and editing data into any database.
There are two ways to create a form:
1. Using a wizard
2. Using the Design View
Creating a Form Using a Wizard
Creating a Form Using a Wizard is the simplest way to create a form in
LibreOffice base. To create a form using wizard, follow the following steps.
Step 1: Open Database
Step 2: Click on Use Wizard to Create Form
Step 3: Select the fields of your form
Step 4: Decide if you want to set up a subform
Step 5: Arrange the controls on your form
Step 6: Select the data entry mode
Step 7: Apply Style
Step 8: Set the name of the form
Step 9: Click on Finish
Forms Control Toolbar – This toolbar contains various controls that can be
added to the form.
Records Toolbar – The Records toolbar contains the navigation control
buttons in the extreme left.
Creating Reports using wizard
You can create report from the Report using a wizard. To create a report,
follow the following steps –
Step 1: Open Database
Step 2: Click on Use Wizard to Create Report
Step 3: Select, Which fields do you want to have in your report
Step 4: Enter, How do you want to label the field
Step 5: Add grouping levels in the Report Wizard
Step 6: Select sort option according to the data
Step 7: How do you want your report to look
Step 8: Describe how you want to processed
Step 9: Click on Finish