Ch-9 STARTING WITH Libre office 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. Some commonly used data
types are:
1. Text Data Type -
The text data is a combination of letters, numbers or special characters. No arithmetic
calculations can be performed on text data. Examples of text data type is PAN Card
Number, Name, Marks, etc. Various types of Text data types are:

2.Numeric Data type

Numeric data types consists of numbers. The numbers can be integer or real numbers
on which any type of arithmetic calculations can be performed. Following table shows
different numeric data types
3.Currency Data type:

The currency data type is used to store the numeric values with symbol of currencies of
various countries. For example $100, £ 500 or Rs. 25.50.
4. Date Data Type -
This data type is used to store dates and time. For example 12/25/2019, 08:45 AM.
Following table shows various forms of date data type.

5. Boolean Data Type -

In boolean data type there can be only two values- True or False, Yes or No
6.Binary Data type:
The Binary data type used to store digitized images and sounds that comes as long
string of zeros and
ones.
Field Properties :
To set the field properties: Select the table > Right click > Select the option Edit > the
table will open in Design View.
In design view there are different properties of fields according to the data type set for
each field.
The properties of numeric type data is given below:
1) AutoValue - if set to yes then field will get the auto numeric values.
2) AutoValue - if set to yes then field will get the auto numeric values.
3) Length - By default length of the field is 10 but the size of the field can be set to
maximum length.
4) Default Value - A default value can be set for a field if user don't provide any value
while entering the values in the table.
5) Format - This property helps to set the format of the data entered in the field such as
91-222-333.
The properties of character type data is shown below:
1) Entry Required - if set to yes then it will be must to insert the value in the field.
2) Length - By default length of the field is 10 but the size of the field can be set to
maximum length.
3) Default Value - A default value can be set for a field if user don't provide any value
while entering the values in the table.
4) Format - This property helps to set the format of the data entered in the field such as
91-222-333.
Starting with LibreOffice Base
1. In Windows, click Start > LibreOffice or double click on the LibreOffice icon on the
desktop.
2. A Database Wizard opens
3. We want to create a new database, so click Create a new database radio button and
click Next button
NOTE: If any database that has already been created is to be opened, then select
Open an Existing Database
4. The Next step gives the option to register our database with LibreOffice.org. Click
and select the radio button with option, 'No, do not register the database'.
NOTE: If we register the database, then our database is made public and hence can be
accessed by other people.
5. This step also asks whether you want to open the database for editing or want to
create a table using the wizard.
6. Click Finish button. The Save As dialog box appears.
7. Select the location and type the name in the File name text box and click on Save
button.
NOTE: The database in Base is saved with an extension .odb. The default name of
database is New Database.odb.
Creating a Table using a Wizard
1. Select "Use Wizard to create a table" option from the Tasks Pane. The Table Wizard
dialog box will open.
2. The Table Wizard of Base consists of ready-made tables. Select any one table, say
Customer.
3. After selecting the Customer table, The required fields from Available fields box can
be shifted one by one to Selected fields box using > button or in one stroke by clicking
on >> button.
4. Click on Next button and set types and formats.
5. Click on Next button and set Primary key.
6. Click on Next button, that will take you to the complete the process of creating table
using wizard and finally click on Finish button.
Setting the Primary Key.
To make a particular field as the primary key, place the mouse pointer before the field
name, and right click. A pop up menu appears and Select the Primary Key option. A
key icon appears before the field name indicating that it is a primary key.
Saving a Table
1. To save the table click on the save button or click File > Save As or press Ctrl + S
from the keyboard.
2. A Save As dialog box is displayed. Enter the name of table and click on OK button.
Entering Data in a Table
To enter data in the table, double click on the created table icon in the Tables Object
Area on the Q database screen. Alternatively, we can open the table by right clicking on
the desired table and then selecting the Open option from the drop down menu. The
datasheet view of the table will appear. In this view, we can enter the records.
Navigating through the Table
To navigate through various records of the table, we use the navigation box present at
the bottom of the datasheet window
Record Selector Box - This is the text box where the currently active record number is
displayed. We may enter the record number that we want to see in this text box.
Navigation Buttons - These are used to scroll vertically in the table.

Editing Data
To edit or modify the entered data, place the cursor on the field value that has to be
edited to edit and enter the new value. The Edit icon appears before the record that is
being edited. This icon is displayed till the table is saved after making the required
changes.
NOTE: Press Esc key to cancel the corrections made and restore the original contents.

Deleting Records from Table

Open the table and select the record to be deleted. Now press del key from the
keyboard or selecting the Delete Record option from the Edit menu or right clicking on
the record and clicking on the Delete Rows option from the pop up menu.
NOTE: Attempting to delete the record will display the Confirmation box. Clicking on
Yes button will finally delete the record, while clicking on the No button will not delete
the record.

Sorting Data in the Table

Data in a table can be arranged in ascending or descending order. This process of
arranging the records in particular order on any filed is called as sorting. Steps to sort
the table are:
1. Open the Event table in datasheet view and select the field on which you want to
sort.
2. From the tool bar click Sort Ascending or Sort Descending icon to sort in ascending
or descending order respectively.
NOTE: To sort the table based on more than one fields. In such case, click Sort icon on
the toolbar.

Closing LibreOffice Base

To close the application window of LibreOffice Base, click on the File > Close or click
on the cross (x) button of the LibreOffice Base window.
 Ch-10 Working with multiple tables
INTRODUCTION
After creating the table in a database, we may require to edit or delete the table.
Sometimes we have to setup the relations between tables to control data redundancy
and inconsistency.
If you set up relations between tables, then adding or updating a record in one table
reflect the changes in all the related tables.
Editing and Deleting Tables
We can copy, rename, edit and delete the table of database by right clicking on the
table name and using the appropriate option from the pop up menu.
Editing a table involves the task such as
1. Adding a new field.
2. Deleting any field in a table
3. Modify or Alter any of the field properties.
Steps to edit a table are:
1. Open the Database User Interface window.
2. Select the Table object in Database Pane.
3. Right click on the table name and select Edit option from the pop menu.
4. The design view window of the table will be displayed.
5. Do the required modifications and save the table.
Steps to Delete a table are:
1. Open the Database User Interface window.
2. Select the Table object in Database Pane.
3. Right click on the table name and select Delete option from the pop menu. 4. A
confirmation box to confirm for deletion of the table will be displayed. 5. Click on Yes
button to finally delete the table.
Steps to Rename a table are:
1. right click on the table name in the Table Area. 2. Select Rename.. option from the
pop up menu. 3. A cursor will appear.
4. Type the new name and press the Enter key.
Relationships between Tables
While working with multiple tables, we should check the redundancy and inconsistency
of data. This canbe done by setting relationship between the tables of a database.
Let us consider an example of a database containing following two tables-
Student_Details and Student Result
Table 10.1: Student_Details
In Table 10.1 (Student_Details), Admission No is the primary key. In table 2 (Student
Result), Roll No is the primary key and Admission No is the foreign key.
Each record in Table 10.2 has a value of Admission No. that corresponds to a record in
Table 10.1 with same value of Admission No.
NOTE: It is important to note that the data types of the common field in both the tables
must be same. If they are not same then LibreOffice Base will display an error message
and will not allow to set the relationship between the two tables.
After setting the relationship between two tables, once a student's record has been
entered in the Student_Details table, only then that particular Admission No can be
entered in the Student Result table. Therefore Student Details is called the master table
and Student Result is called the transaction table.
Types of Relationships
Three types of relationships can be set up between two tables in a relational database.
These are:
(i) One-to-One
(ii) One-to-many
(iii) Many-to-Many
One-to-One relationship: In this type of relationship, one specific record of a master
table has one and only one corresponding record in the transaction table.
One-to-many relationship: In this type of relationship, one specific record of the
master table has more than one corresponding records in the related transaction table.
For example
Many-to-many relationship: In this type of relationship, there will be multiple records
in the master table that correspond to multiple records in the transaction table as well.
Advantages of Relating table in a database
1. A relationship can help prevent data redundancy.
2. It helps prevent missing data by keeping deleted data from getting out of synch. This
is called referential integrity.
3. Creating relationships between tables restricts the user from entering invalid data in
the referenced fields.
4. Any updation in the master table is automatically reflected in the transaction tables.

Creating Relationships between Tables

• Click on Tools > Relationships...
• The Relationship Design screen will appear.
• In the middle of the screen there is Add Tables dialog box (as shown below).
• Select the table and click on Add table button.
• Close the Add Tables dialog box.
• Drag the common field from one table and drop it in another table.
• A line connecting both the tables with the common field appears on the screen (as
shown below).
Remove the Relationships
The relationships applied on the tables can be removed also with the help of Delete
option. Right Click on the relationship thread and select Delete option.
Referential Integrity.
Referential integrity is used to maintain accuracy and consistency of data in a
relationship. In Base, data can be linked between two or more tables with the help of
primary key and foreign key.
According to the principle of referential integrity if a record say Admission No as 1001 is
not present or deleted in the master table, then there should be no record with same
Admission no as 1001 in the transaction table.
LibreOffice Base will allow only that corresponding record to be entered in the
transaction table which already exists in the master table.
LibreOffice Base gives us following four options to choose from to maintain referential
integrity in such cases.

No action - This is the default option. This option states that a user should not be
allowed to update or delete any record in the master table if any related record exists in
the transaction table.

Update cascade - This option allows the user to delete or update the referenced field
but along with it all the related records in any of the transaction tables will also be
deleted or updated.

Set NULL - This option assigns NULL value to all the related fields if the master record
is deleted or updated.

Set default - This option assigns any fixed default value to all the related fields if the
master record is deleted or updated.
Referential integrity helps to avoid:
1) Adding records to a related table if there is no associated record available in the
primary key table.
2) Changing values in a primary if any dependent records are present in associated
table(s).
3) Deleting records from a primary key table if there are any matching related records
available in associated table(s).
 Ch-11 QuerIes
A query is one of the most important feature of any DBMS. Using a query, we can retrieve and display data from one
or more tables in a database.

Creating a Query

A query can be created in three ways. In this chapter you will learn the first two methods to create a query.

(i) Using a Wizard

(ii) In Design View

(iii) In SQL view

Creating a query using a Wizard:-

To create a query using a wizard, follow the following steps.

1. Open the School database. Click on Queries button present in the Database Pane.
2. In the Tasks Area, click on Use Wizard to Create Query... option
3. The Query Wizard will open.
4. Select the required table and then select the required fields(Name, Class and Rno)
from the "Available fields" window to "Field in the Query" window and click on Next
Button.
NOTE: Clicking on >> button moves all fields to "Fields in the Query" area.
5. Select the ascending or descending order of any particular field of the table. Since
we do not want to set in a particular order, so we click on Next button.
6. The next step is to set the search conditions or the criteria on the basis of which
records will be filtered from the table.
7. Steps 4, 5 and 6 given in the Steps Pane are not required if there is no numeric field
involved in the query. So skip these steps.
8. The next step to give alias name i.e. the column header name will be displayed when
we run the query. Click on Next after writing alias name.
10. The last step of the Query wizard displays the entire overview of the query.
11. Click on Finish button.
Creating a query in Design view:-

1. Click Queries icon on the Objects Pane in the Database Window.

2. Click Create Query in Design View... icon in the Tasks Pane. The Query Design
Window appears. In the middle of the window the Add Table or Query dialog box
3. Click on the "Table1" table to be used in the query and then click on Add Button.
Alternatively double click on the "Table1" table.
4. Click Close button in the Add Table or Query dialog box to close it.
5. Next step is to select the fields. For our query we want to display Roll number, Name
and Class. So in the list box of "Table1" table, double click on the required field.
NOTE: Observe that the Visible Check Box is by default selected. This means that all
these three fields will be visible when you run the query. In the grid, there is a row titled
Alias. It can be used to display meaningful names in the output. For example, instead of
Rno, we would just like to display Roll Number.
To sort the records in either ascending or descending order of a particular field, the Sort
row is given in the grid. Select ascending or descending from the drop down of Sort
row.
6. Write 3 in Criteria row below the Roll number Column.
NOTE: Once the query is designed, click Run Query () button on the toolbar or press
F5 key. The query result will be displayed in the Tables Pane area.
7. Click on save button to save the query.
NOTE: By default, the name of the Query is Query1. To run the query again, double
click on the query name. To close the Query window, click on close button on the top
right corner of the window.

Editing a Query.
1. Right click on the Query Name Which you want to edit in the Objects Area of the
Database window.
2. Select Edit option from the drop down menu. The Query Design window will be
displayed.
3. Make the required changes like change the Alias, apply criteria or edit the existing
criteria etc.
4. Click on Save and close the Query window.
Ch-12 Forms and Reports
Forms:
A form is an object of the database that has a user friendly interface where data can be
entered. For any database, it is the front end for data entry and data modification.
A form contains field controls arranged in a presentable manner. Each field control
consists of a label and the field value text box. A label is a piece of text that specifies
the data that should be entered in the field value text box. A field value text box is
linked to the respective field in the table.
A form may contain some additional text like titles, headings and names, graphics like
logos, list boxes and radio buttons.
There are two ways to create a form:
• Using a wizard
• Using the Design View
Creating a Form Using a Wizard
To create a form using wizard, follow the following steps:
1. Click the Form icon on the Database Pane. Click the option Use Wizard to Create
Form... on the Tasks Pane.
2. Select the table for which the form has to be created.
3. After selecting the "Table1" table, all the fields of the "Table1" table will be listed in
the Available Fields list box as shown below.
4. Shift all the fields of Event table from Available Fields list box to Fields in the Form
list box using >> button. (shown above)
5. The second step consists of setting up a subform, i.e. a form within a form. Since we
do not want to set up any subform, click Next button.
6. This step arrange controls i.e. to set up the design of the form. In this step we
arrange the label and field value text boxes as we want them to be visible on the
screen.
NOTE: By default, all controls will be left aligned. A field control consists of two parts -
label and the field value text box.
7. Click Next button
8. The step 6 of the wizard asks whether the form will be used for displaying data,
entering data or both. After Selecting appropriate option, we click on Next button.
9. The next step is to apply styles to the form. Select the desired background colour
and border type.
NOTE: By default the border of the field text value is displayed in 3D look. We can
select the options No Border or Flat if required.
10. Click Next button.
11. The next step is to set the name of the form.
12. Click Finish button. The form with the first record will be displayed on the screen in
a separate window.
NOTE:
1. By default the name of the form is same as the name of the table.
2. By default, the radio button with the option Work with the form is selected. If you wish
to modify the form after the wizard finishes, click Modify the form option.