CHAPTER 8 – INTRODUCTION TO DATA BASE

MANAGEMENT SYSTEM
 INTRODUCTION:
DATA AND INFORMATION:

 Raw facts constitutes data. The facts may be related to person, place, or things.
 It may be stored in the form of text, graphics, audio or video.
 This data must be processed by any computing machine in a proper way to
generate the useful and meaningful information.
 Ex: Marks scored by students, weights, prices, costs, numbers of items sold, etc.
 Information is the processed or organized form of data.
 If data is not correct or accurate, the information obtained by processing such data
may not be correct.
 For example, marks obtained by students and their roll numbers is the data, while
the report card/sheet is the information.

DATABASE AND DBMS:

 A database is a collection of logically related data items stored in organised

manner.
 The information being stored in a database can be added, modified, deleted or
displayed according to the requirements of the user.
 The software that is used to create, update and retrieve data is known as database
management system (DBMS).

 It facilitates planning and maintenance of the database for the user.

 Some of the common examples of DBMS are MS Access, Open Office or LibreOffice
Base, Oracle, Ingress, MySQL.

 Example: Phone book can be considered a simple database and to manage this
database electronically, we will require a database management system or a
DBMS.

ADVANTAGES OF DBMS:

 Organised Storage: The data in the database is stored in an organised manner,

so that retrieval of the required data is fast and accurate.
 Data Analysis: A database helps in analysis of data based on certain criteria. It is
easy to find out maximum or minimum value, average or mean using a database.
 Data Sharing: If the same data set is required for different applications then the
database can be shared with other applications. Hence using a database means
making once and using it repeatedly for multiple applications.
 Minimal Data Redundancy: In the event of requiring the same data field in
several tables the data field might get repeated in number of tables. This is called
as data redundancy. This can be reduced by using DBMS tools.
 Data Consistency: By minimising data redundancy, chances of inconsistent data
being stored is reduced. For example, it should not happen that the name of the
student is changed in one table and not in another. Such inconsistency is reduced
by using a DBMS.
 Example: Murugan looks after the data management of ABC School. There are two
tables in his database-Admission table and Library table. Ram Lal Kumar' wants to
change his name to 'Ram Kumar' has recently shifted his house. So, he hands over
the application for the same to Murugan. In the absence of a DBMS, Murugan has
to change the name of the student separately in the admission table and also in
the library table. But as we can set a relation between the two tables using a
DBMS, any change in one table is automatically reflected in all the related tables.
There is no need to add another record with the changed name. The same record
will be updated. Hence, this reduces the chances of data redundancy and
inconsistency.

 Increases Efficiency: Since database tables are properly organised, saving,

reading and searching data can be carried out efficiently.
 Increases Accuracy: Since data redundancy and inconsistency can be minimised
in a database, the data is retrieved accurately from the database.
 Increases Validity: Properties of different data fields can be assigned when a
database is planned. So whether or not valid data is being entered can be checked
at the data entry stage. This increases the validity of the database. For instance,
we may set up a restriction while creating the table that the amount of fees being
entered should be more than 1000.
 Security: Unauthorised access can be controlled by assigning passwords to the
users. The data might be translated in such a manner that unauthorised users are
not able to read it. This is known as encryption. Both these measures increase the
security of the database.

 DATA MODELS:
 A database can be designed in different ways depending on the data being stored.
This structure of database is known as data model that describes the manner in
which data will be stored and retrieved.
 A data model consists of components for describing the data, relationships among
them and the constraints that hold data. There are different data models such as
hierarchical data model, network data model and relational data model.

HIERARCHICAL DATA MODEL

 In this model the data is organized into a tree like structure.

 The data is stored in the form of records.
 A record is a collection of fields and its data values.
 All these records are linked to each other at various levels, thereby forming
hierarchy.

NETWORK DATA MODEL

 In this model, multiple records are linked to same master file.

 It is also considered as an inverted tree where master is present in the bottom of
the tree and the branches contain information linked to the master.

RELATIONAL DATA MODEL

 This data model is based on principle of setting relationships between two or more
tables of the same database.
 It is the most commonly used database model.
 RELATIONAL DATA MODEL:
 The Relational Database Model was proposed in 1970 by E. F. Codd.
 Relational database model is the most common type of database model.
 The data elements are stored in different tables made up of rows and columns.
 The data in different tables are related through the use of common fields.
 So relations are set between tables based on common fields. That is why this
model is termed as relational database model.

RELATIONAL DATABASE TERMINOLOGY

1) Entity: It is a real-world object about which information is to be stored in a

database.

 Details associated with the entity are called attributes.

 Each entity is a collection of these attributes associated with it.
 Example: Roll number, name, admission number, etc., are attributes associated
with the entity student.
 ATTRIBUTES ARE REPRESENTED IN THE FORM OF COLUMNS.

2) Table: A table is a collection of logically related records.

 It is organised as a set of columns, and can have any number of rows.

 Example: Employee table can have columns, namely name, designation,
department and have records or rows having data of 100 employees.

3) Field or Columns or Attributes: A field is smallest entity in the database.

 A collection of fields make a record

 A collection of records make a table
 A collection of tables make a database
 Fields are individual record characteristics & are presented as columns within
table.
 Data values are stored in a database as fields.
 A field holds the data values of one type of data for several persons.

4) Data Values: Data values are the raw data represented in numeric, character or
alphanumeric form. Examples of data values are 'Abhinav Bindra', '26' 'shooting',
"Chandigarh", "10-12-2018", etc.
5) Record or Row: Data values for all fields related to person or object is called
record.

 It is presented as rows within a table.

 A record holds the data values of all the fields for a single person or object in a
table. For example, in the Employee table with the field names as Name,
Designation, Department, the data values of all the fields for an employee may be
('Abhinav', 'Manager', 'Finance') and this forms one record.

6) Primary Key: A primary key or simply a key is a field that uniquely identifies a row
in a table.

 The key identifier can be the value of a single column or of multiple columns.
 The primary key is a unique identifier for the table.
 Column or combinations of columns that form the primary key have unique values.
 At any time, no two rows in the table can neither have same values for the primary
key nor can data value for such field be left blank.
 Example: In a student table, each student has a unique roll no., which forms the
primary key. If, in a table we use more than one fields to identify a record, it is
known as a composite key. For example, we may form a composite key consisting
of fields roll no. and name.

7) Relational Database: A relational database is a collection of related tables

8) Foreign Key: If a field or a combination of fields of one table can be used to
uniquely identify records of another table, then that particular field is known as the
foreign key.

 This foreign key helps to build a relation between two tables.

 Example: In Student Registration Table, Enrolment Number' is the primary key and
in the Student Marks Table, 'Roll Number' is the primary key, whereas Enrollment
Number' is the foreign key. This foreign key can to set a relation between two
tables. be used

9) Candidate Key: All the field values that are eligible to be the primary key are the
candidate keys for that table, Such fields can neither be left blank nor can have
duplicate values. So in the table Student Marks, Enrollment Number and Roll
Number both are candidate keys.
10) Alternate Key: Out of the candidate keys, one or two are made as primary
keys. The others are the alternate keys. Hence, if Roll Number is made as the
primary key, Admission Number is the Alternate key.

 OBJECTS OF RDMS: An object in a database is a structure or a feature that is used to

store, represent or retrieve data. In fact a database is a collection of these objects that
work on multiple sets of data related to each other. Various objects in a database are as
discussed below:

Table: Table is the basic unit of any DBMS.

 The data is first stored in tables in row and column format.

 A column represents a field or an attribute while a row represents a record.

Forms A form is a feature of a database using which we can enter data in a table in
an easy and user-friendly manner.

 A form consists of text boxes, labels, radio buttons, list boxes, check boxes etc.
that give a user-friendly interface for entering data.
 The data entered through the forms is stored in tables.

Queries A query is used to retrieve the desired information from the database.

 In simple terms, it is a question asked from the database.

 For example, if we want to view the names of only those students who have scored
more than 50 marks, then we post a query. The data set matching the given
criterion is retrieved from the table and displayed on the screen.

Reports-The output of a query may be displayed in the form of reports.

 The usual result of the query is in the form of rows and columns.
 If we want the report to be formal and in proper layout, then we can use the
Reports feature of RDBMS.

CHAPTER 9 – STARTING WITH LIBREOFFICE BASE

 LibreOffice Base is a free and open-source DBMS.
 Available for both Linux and Windows operating systems. Data has to be stored in
an organised manner using a DBMS.

 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.

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.

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. For example, 10, -34.8, 90.6789 , -86 are of numeric data type.

3) Currency Data Type – The currency data type indicates the monetary values and
can be stored using currencies of various countries. For example $100, £ 500 or Rs.
25.50.
4) Date Data Type – This data type is used to indicate dates and time. For example
12/25/2019, 08:45 AM. The data and time can be stored in various formats.
5) Boolean – In Boolean data type there can be only two values- True or False. This
also can be given in multiple formats like Yes/No, True/False, On/Off.
6) Binary – The Binary data type used to store digitized images and sounds that
comes as long string of zeros and ones. It is possible to store photos of the
products or employees, or sound snippets or voice messages in Base database.

 USER INTERFACE OF LIBREOFFICE BASE:

• Title Bar – The title bar displays the name of a database and an application in which
it is made. The windows buttons to maximize, minimize or close the window are
located on the right corner of the title bar.
• Menu Bar – The menu bar appears below the title bar. It consists of seven menu
items – File, Edit, View, Insert, Tools, Window and Help. All these menu items contain
commands that help to perform various operations on the database.
• Standard Toolbar – It is located below the menu bar. It is used to access frequently
used tools.
• Status Bar – It is located at the bottom of the interface window. It displays
information about the type of view of the object in the database.
• Database Pane – The database pane is located on the left side of the window.
LibreOffice Base is the collection of related data objects known as Tables, Forms,
Queries, Reports and application modules. Depending on the object that is selected,
the respective Task Pane and Object Area displaying the created object appears

 OPENING A DATABASE:
To open an already created database, click File > Open. The Open dialogue box
appears as shown in Fig. 9.6. Browse for the folder where the database to be opened
is stored. Select the desired database and click on Open button. Tip: Use the keyboard
shortcut key Ctrl+O to open an already existing database

 CREATING A TABLE:
Once the database is created, we can start working with objects of the database. First
and foremost is the creation of the table and then entering data in the table. A table
in LibreOffice Base can be created using a wizard or using the Design view.

CREATING A TABLE USING WIZARD:

6. Click on Next button, that will take you to the complete the process of creating
table using wizard and display the screen. This screen will give you three choices. By
default the “Insert data immediately” option selected. Click on the Finish button to
complete the process of creating the table and using wizard.

CREATING TABLE IN DESIGN VIEW:

 Creating the table using wizard restricts us to use the same fields in the per-
designed tables. It may not solve the purpose in real scenario, as we may require
to create a table with the different fields for our purpose.
 Creating tables using Design View gives us more flexibility to do our work.

 To create table using Design View, in main Database window, click on the option
Create Table in Design View in the Tasks Pane and Table Design Window
 The screen is broadly divided into 2 sections or horizontal panes.
1) The upper half consists of a grid structure with 3 columns:
• Field Name – It is the name of the field assigned at the time of creation of table.
• Field Type – It allows to assign a data type to the field.
• Description – It allows to describe the purpose of the field. It is not the part of
database table, but it is meant for the user to understand the purpose of the field. We
may or may not enter field description.
2) The Field Properties pane is located at the bottom half of the window.
It displays the field properties assigned by the database designer. These properties
can also be changed as per the requirement and are used to control and validate the
data that is to be entered.
Step 1: Type the first field name in the Field Name column. Press Tab key. The
cursor moves to the second column i.e. Field Type.
Step 2: The Field Type column contains a list box. As you click on the down arrow,
it appears and we can select the desired data type from the list box. Select the
datatype
Step 3: Observe that certain properties appear in the Field Properties Pane as the
data type is selected. Some of the properties are Entry required, Length, Default
value, Format example. Set the desired properties for the entered field.
Step 4: Press Tab key to move to the next column. Add any description if you
want in the third column.
 Step 5: Once the properties for the field are set, press Tab key to move to next
row.
Step 6: Enter the next field by repeating steps 1,2 and 3. Repeat the process for
adding all fields in the table.

SETTING THE PRIMARY KEY:

To set a composite key, i.e. a primary key consisting of two fields, keep the Ctrl key pressed and then click
on multiple fields to select them. Thereafter right click on selected fields and choose Primary Key option
from the pop-up menu.

SAVING A TABLE:
After creating the table you need to save it on the disk. To save the table click on the
save button or follow menu option File > Save As. Enter the name of table and click
on OK button. Once the table design is complete, click on Close button on the toolbar
to return to the Database screen.
The name of the table will appear in the Tables Object Area .

ENTERING DATA IN A TABLE:

 To enter data in the table, double click on the created table Events icon in the
Tables Object Area on the 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. It displays the field names in the top row.
 These fields are displayed in the same order as they were added while creating the
table. If the number of fields are more and cannot fit in the single row, you can use
the horizontal scroll bar to view all the fields.
 The cursor will be blinking in the second row. Start typing the data value for each
field. Use Tab to move to next field.
 Once all the data values are entered for a single record, the cursor moves to the
next record. This process is called as data entry.

NAVIGATING THROUGH THE TABLE:

 EDITING DATA:
To edit or modify the previously entered data simply 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. Press Esc key to cancel the corrections made and
restore the original contents.

DELETING RECORDS FROM TABLE:

To delete any record from the table, open the table and select the record to be
deleted. The record can be deleted by pressing the Del key from the keyboard or
selecting the Delete Record option from the Edit menu.
Alternatively a record can also be deleted by right clicking on the record and clicking
on the Delete Rows option from the pop-up menu.
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 records in particular order on any filed is called as sorting
 Step 1. Open the Event table in datasheet view and select the field on which you
want to sort.
 Step 2. From the tool bar click Sort Ascending icon if the table has to be sorted in
ascending order of selected field. Alternatively select Sort Descending icon if the
table has to be sorted in descending order of selected field.
 Step 3. The table will be sorted in the ascending order of points.
 Sometimes we may need to sort the table based on more than one fields. In such
case, click Sort icon on the toolbar. The Sort Order dialog will be displayed. Select
the appropriate field name and field value according to which the records are to be
 sorted. Choose the order of sorting ascending or descending as required from the
Order drop down box. Multiple fields may be selected in this dialog box. Click OK
button once done.

 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.
 CHAPTER 10 – WORKING WITH MULTIPLE TABLE
TYPE OF RELATIONSHIPS:
The type of relationship between any two tables in a database is based on the number
of records that are present in the transaction table corresponding to the master table.
Primarily three types of relationships can be set up between two tables in a relational
database These are:
(i) One-to-One
 In this type of relationship, one specific record of a master table has one and only
one corresponding record in the transaction table.
 Example: Relationship between Student and Admission number (This is because no
two students will be given same admission number) and a person and his ticket
number will be one-to-one relationship.

(ii) One-to-many
 This is one of most common types of relationship between the tables in a database.
 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, one teacher can teach multiple students or multiple classes, or one
person can sell multiple products.
So we can say that there is a one to many relationship between a teacher and
class or teacher and student or seller and products.

(iii) Many-to-Many
 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.
 Generally this type of relationship is set when certain records have to be saved
more than once in both the related tables.
 For example, a teacher in a school may hold multiple responsibilities such as class
teacher, an activity in-charge or examination in-charge. For each responsibility the
teacher might be attached with multiple students. So this type of relationship will
be many to many relationship.
 Similarly a shopkeeper may sell multiple products to multiple customers. So many-
to-many relationship exists between a product and a customer.

ADVANNTAGES OF RELATING TABLES IN A DATABASE:

 A relationship can help prevent data redundancy.
 It helps prevent missing data by keeping deleted data from getting out of synch.
This is called referential integrity.
 Creating relationships between tables restricts the user from entering invalid data
in the referenced fields.
 Any update in the master table is automatically reflected in the transaction tables.

 REFERENTIAL INTEGRITY:
 According to the principle of referential integrity, no unmatched foreign key
values should exist in the database.
 Example: If any student leaves the school and his record is deleted from
Student_Details table, then there is no question of his appearing for exams and
having a result. Hence corresponding record in the transaction table
(Student_Result) should either have NULL value or should be deleted
 Once the relationship between the two tables has been set, the integrity of data
will be managed by the DBMS.
 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 4 options to choose from to maintain
referential integrity in such cases.
(i) 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.
(ii) 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.
(iii) Set NULL – This option assigns NULL value to all the related fields if the
master record is deleted or updated.
(iv) Set default – This option assigns any fixed default value to all the related
fields if the master record is deleted or updated.

 To set the relationship properties double click on the relation line joining the two
tables. A Relations dialog box will open. By default the radio button with No action
option will be selected. Choose any of the desired option and click OK to set the
referential integrity between the two tables.

CHAPTER 11 – QUERIES IN BASE

 A database is used to store data in an organized manner so as to retrieve it easily
and accurately from database.
 To search for the desired record and to retrieve the desired data, we have to give
its specifications to DBMS. Such specifications are given to the database in the
form of queries. Therefore, we can say that a query is a sort of question asked from
a database.
 Depending upon specifications given in the query, the specific records are
searched from the table(s) in the database and then displayed in the desired
manner.
 Such information may be difficult to find by just looking at a single table or multiple
tables. In fact as the number of records increase, finding the desired information
becomes more and more difficult. By using queries, we are able to retrieve data
without going individually through each record in the table(s) and also display
them in desired format.
 Queries
 A query is one of most important feature of any DBMS.
 Using a query, we can retrieve & display data from one or more tables in a
database.
 This is done by giving specific search criteria to DBMS so that we are able to view
exact information that we want.
 LibreOffice Base allows us to create a query & even save it as an object in a
database.
 This helps us to run the query multiple times as and when required.
 Using a query, we can specify the fields that we want to display and also the
criterion based on which the records to be filtered.
 The information may be retrieved from a single table or from multiple tables.
 Also the result of the query is displayed in tabular form with field names in columns
and the records in rows.

 A query can be created in three ways.

 (i) Using a Wizard
 (ii) In Design View
 (iii) In SQL view

CREATING A QUERY USING A WIZARD:

 Consider the following table: Table1 (created in database: School)
Rno Name Class Fees
1 Anil X 2500
2 Anuj XI 3000
3 Ashish XII 3200
Creating a query that will display “Rno”, “Name”, “Class” from table “Table1” whose
Roll No. is 3 in Design View.
Step 1: Open database. In the Database Design window, click on Queries button
present in the Database Pane on the left.
Step 2: In Tasks Area, click on Use Wizard to Create
Query… option.
Step 3: The Query Wizard will start. It contains Steps
Pane on left and Query Details Area on right.
Step 4: Select the required table and then select the
required fields (Name, Class and Rno) from the
“Available Fields” window to “Fields in the Query”

 Once the fields are added they can be moved up and down in order by clicking ∧
window and click on Next Button.

and ∨ buttons present on the extreme right of the wizard.

 Clicking on » button moves all fields to Fields in the Query area and « button
moves all the fields back to Available fields area.
Step 5: Set the sorting order. The result of the query can be displayed in
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.

Step 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.
 This is the step where actually the query is set up or the criterion is given to the
database.
 As per our query, the criterion is to display the records of “Table1” with student
having Roll No. 3
 NOTE: By default, the radio button with option Match all of the following is
selected. (Other Option: Match any of the following: If any one of the given
condition are to be matched for filtering the records)
 Select “[Link]” field from Fields drop down list, “is equal to” from Condition
drop down list and type the value as “3” and click on Click Next button.
 Three search conditions can be given at the most in the wizard.
Step 7: Steps 4, 5 and 6 given in the Steps Pane
deal with tasks like summarizing and performing
numerical calculations. Such steps are not required
if there is no numeric field involved in the query.
Step 8: The next step to give alias name i.e. the
column header name will be displayed when we run
the query.
 By default the field names will be displayed as
column headers.
 Many times field names are not user friendly, so an
alias name which is more readable, is chosen to be
displayed in the query output.
Step 9: The last step of Query wizard displays the
entire overview of the query. All the steps
performed till now are shown in a summarised
manner It includes the following:
• Name of the Query – By default, the name of the query is Query_Events by
default. If desired, type the new name in the text box.
• The action to be performed after the wizard finishes – By default Display
Query option will be selected. Click and select the Modify Query radio button if the
query has to be edited in the Design view.
• Complete detail of the query – This section contains a summary about the query
that has been created.
Step 10: Click on Finish button. The records with “Rno” as 3 will be displayed on
the screen. Once the query is created, it can be edited in Design view.

CREATING A QUERY IN DESIGN VIEW:

 This is a more flexible method to create query from either single or multiple tables
of a database.
 Consider the following table: Table1 (created in database: School)
Rno Name Class Fees
1 Anil X 2500
2 Anuj XI 3000
3 Ashish XII 3200
Creating a query that will display “Rno”, “Name”, “Class” from table “Table1” whose
Roll No. is 3 in Design View.
Step 1: Click Queries icon on the Objects Pane in the Database Window.
Step 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 is displayed
Step 3: Click on the “Table1” table to be used in the query and then click on Add.
Alternatively double click on the “Table1” table.
 The table will be added to Tables Pane present at the top of Query Design window.

Step 4: Click Close button in the Add Table or Query dialog box to close it. The
table “Table1” added will be displayed in the Table pane.
Step 5: Select 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.
 The field name along with the table name is displayed in the Design grid present in
the lower half of the Query Design window
 NOTE: 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.
 To sort the records in either ascending or descending order of a particular field, the
Sort row is given in the grid.
Step 6: In grid, there is a row titled Alias. It can be used to display meaningful
names in output. For example, instead of Rno, we like to display Roll Number.
Step 7: By default, data that is displayed as a result of the query is not sorted.
 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.
Step 8: Write 3 in Criteria row below the Roll number Column.
Step 9: 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.
Step 10: Click on Save button to save query. The Save As dialog box will be
displayed.
Step 11: By default, Query Name as Query1 will be displayed. Type a different
name if required. Click on OK button to save the query. The name of the query will
be seen in the Objects area in the Database window.
 To run the query again and see the results of the query, double click on the query
name. The results of the query will be displayed in a separate window. To close the
Query window, click on close button on the top right corner of the window

EDITING A QUERY:
Step 1: Click on the Query icon of the query that has to be edited.
 The list of queries that have been created will be displayed in the Objects Area.
Step 2: Right click on the Query Name in the Objects Area of the Database
window.
Step 3: Select Edit option from drop down menu. Query Design window will be
displayed.
Step 4: Make the required changes.
Step 5: Now save and run the query.

WORKING WITH NUMERICAL DATA:

Step 1: In the Database Window, click Create Query in
Design View… button to open the Query Design
Window.
Step 2: Select the required fields. For our query we
want to display Fees. So, in list box of “Table1” table,
double click on required field.
Step 3: Select the function “Maximum” from the drop
down list of functions under “Fees” column.
Step 4: Press F5 to run the query.
 CHAPTER 12 – forms and reports
Reports and forms are considered as objects of the database and are present in the
Database Pane of the LibreOffice Base User Interface.

 FORMS:
» DEFINATION: A form is an object of database that has a user-friendly interface
where data can be entered and seen in an attractive and easy-to-read format.
» For any database, it is the front end for data entry and data modification.
» It displays data in a layout design by us & not just in simple row and column format
» Form contains FIELD CONTROLS arranged in presentable and user-friendly
manner.

(i) Each field control consists of a label and field value text box.
(ii) Label: Piece of text that specifies data that should be entered in field value text
box.
(iii) A field value text box is linked to respective field in table.

» We may add all or selected fields from the table on the form.
» In addition to field controls, it 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 WIZARD:

» This is simplest way to create a form:

Step 1: Open database created in LibreOffice & click Form icon on Database
Pane.

 Click the option Use Wizard to Create Form… on the Tasks Pane.
 Form wizard will open along with blank database form in design view in
background.

Step 2: The step 1 of wizard is to select tables or queries for which the form has to
be created. As we are creating a form for Events table, select Events table from
“Tables and queries” list box.
Step 3: After selecting Events table, all fields of Events table will be listed in
Available Fields list box.
Step 4: As we require all fields to appear in Form, shift all fields of Event table
from Available Fields list box to Fields in the Form list box using >> button. All the
fields are shifted to Fields in Form list box. Click on Next button to move forward
Step 5: The second step consists of setting up a
subform, i.e. a form within a form. You need to check the
checkbox “Add Subform” to add the subform.
Step 6: The wizard skips the next two steps that relate
to the subform and moves on to step 5, if you did not
select the checkbox. This step arrange controls i.e. to set
up the design of the form.

 By default, all controls will be left aligned.

 Click Right Align radio button to align the controls from the right side of the form.
 As mentioned before, a field control consists of two parts – label and the field value
text box. So in this step we arrange the label and field value text boxes as we want
them to be visible on the screen.
 4 layouts are given in this step of wizard to choose from:

(i) Columnar display with Labels on the left of the field value
(ii) Columnar display with Labels on top of the field value
(iii) Display as datasheet
(iv) Block display with labels on. Also note that as we choose the Layout type, the
fields are arranged in the Form Design view also.

Step 7: Click Next button.

Step 8: The step 6 of the wizard asks whether the form will be used for displaying
data, entering data or both. As we go with the default settings (i.e. “The form is to
display all data”, so we click Next button
Step 9: The next step is to apply styles to the form being created. We can select
background colour (10 Options) & border type (3 Options) of field value text boxes

 By default the border of the field text value is displayed in 3D {Other options: No
Border and Flat}

Step 10: Click Next button.

Step 11: The next step is to set the name of the form. By default the name of
the form is same as the name of the table. Click in the textbox and type a
different name

 In the same step, 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.

Step 12: Click Finish button. The form with the first record will be displayed on the
screen in a separate window.

Name of the form will appear in the Object Area of LibreOffice User Interface window.

On left of Form Design window is Forms Control toolbar and at bottom is Records
toolbar.

1. Forms Control Toolbar: This toolbar contains various controls that can be added
to the form.
2. Records Toolbar: It contains navigation control buttons in extreme left. With
help of these buttons, we can traverse and view records in the file. As we move
from one record to another, the record number in the record text boxes
changes.

MODIFYING A FORM:

It is possible to modify the form in any manner once it is created. The modification
can be to change the background colour, font size and color of the text or even
positioning of various controls in the form.
(i) Changing the background color
Step 1: Open LibreOffice Base User Interface for and click on Forms icon in the
Database Pane. The name of the saved form will be displayed in the Objects Area.
Step 2: Right click on the form name and select Edit… option. A separate Form
Design View will open.
Step 3: To change the background color of the form, right click on the form and
select Page Style... option from the pop-up menu
Step 4: The Page Style dialog box will appear, where you select Area tab and
choose the desired color from the palette.
Step 5: Click on OK button. The selected color will be applied on the form.

(ii) Editing the labels To edit the labels, either by changing the text or by
changing the formatting effects:
Step 1: Place the mouse pointer over the label to change it.
Step 2: Press the keyboard shortcut key CTRL+Click to select the label. The
position boxes will appear around the label.
Step 3: Right click on selected label and select Control Properties… option from
pop-up menu
Step 4: The Properties: Label Field dialog box will appear. It contains various
properties of selected label. In the text box after Label property, type name. The
label caption on the form changes accordingly.
 Similarly we can change other properties of the selected label like width, height,
alignment, font style and font size.
Step 5: After making the desired changes close the Properties dialog box by
clicking the cross (x) button on the top right of the dialog box. The changes made
will be applied on the selected text.

(iii) Moving a control It is possible to move control to another location in the

form. Click on the control that has to be moved.
 Both the label and the text box for field value will be selected and position handlers
will be placed around the control.
 If only one of these have to be re-positioned then press CTRL key while clicking on
that control. Now, click and drag the control to move to the desired location.

(iv) Changing the size of the textbox control To change the size of the
textbox control, press CTRL button while clicking on the textbox.
 It will be selected with position handlers around it.
 Place the mouse pointer on any of these handlers. It will change to a double-sided
arrow. Click and drag them to the desired size.

(v) Adding a Tool tip A tool-tip is a small piece of text that is displayed when
the mouse pointer is placed on a particular control. This will make entering
data for the user easy. Such type of text that appears when the mouse
pointer is placed on a particular control is called the tool-tip text or help text.
Step 1: Press CTRL button and click on text box.
Step 2: Right click and select Control Properties… option. The Properties: Text Box
dialog box will be displayed
Step 3: In the dialog box, scroll down till Help Text property appears.
Step 4: Type the tool-tip/help text in the text box.
Step 5: Close the dialog box by clicking on cross (X) button.

FORMS CONTROLS TOOLBAR:

Forms control toolbar contains various tools to add or edit controls on the form.
 (i) Adding a calendar for the date field While filling up a form on a
computer, mostly a calendar is displayed. This is because it is easy to choose
a date rather than typing it.
Step 1: Place the mouse pointer over the Date text box and press CTRL+Click to
select it.
Step 2: Right click and select Control Properties…. option.
Step 3: In the Properties: Date Field dialog box, scroll down for Date Format
property. By default, Standard (short) format will be displayed.
Step 4: Click to open the list box and select Standard (long) format.
Step 5: Scroll down further till you find the Drop-Down property. By default its
value will be No. Select Yes.
Step 6: Close the dialog box. The selected date control text box on the form
changes to a list box with an arrow being displayed in the extreme right

(ii) Adding text to the form While designing a form, we may need to enter
titles, headings or subheadings. It is called as Labels. It is possible to create
Labels in the form while designing. Follow the following steps to insert the
title text in the form.
Step 1: Click the Label tool on the Form Controls tool box
Step 2: On the form, click and drag the mouse to create a label field box. It will
also have position handlers.
Step 3: Double click on box to open the Properties: Label Field dialog box.
Step 4: Type the title as “Data Entry Form” in the Label property
Step 5: Set the Font property by clicking the Font button in front of the Font
property. The Character dialog box will be displayed, where you can set the font
type, style and size. Choose the desired font style and size and click on OK button.
Step 6: Close the Properties: Label Field dialog box. The title with the selected
formatting effects will be displayed on the form. After applying and editing various
controls, the final form for entering data in the Events table looks

(iii) Adding a new record using a form After you have finished designing the
form, you can display or insert records using this form. For this purpose, you
have to shift from Design View to Form View by clicking on Design Mode
button on the Forms Controls toolbar. The Form View window appears with
the first record displayed in the respective text boxes.
 To add a new record into the table using this form, click on New Record button on
the Records toolbar.
 A blank form with the cursor blinking in the first text box will be displayed.
 Once the record is entered, click on Save Record button on the Records toolbar to
save the record in the table.
 DELETE: To delete any record, navigate to the record by either typing the record
number in the Record text box or by using the navigation buttons. Thereafter click
Delete button on the Records toolbar.
 The next record will be displayed in the form.
When we create a form in Design View, all the controls are placed on the form using
various tools given in the Forms Control toolbar.

 REPORTS:
Records that have been extracted using query are displayed in simple row & column
format. Instead, using a report we can present retrieved data in attractive and
customized manner.
We can create a report based on a table or a query or both.
Preferably, if a report has to be generated from multiple tables, a query should be
created first and then that query can be used to generate the report.
Let us create a report using the table Events from the Sports Day database. Follow the
following steps to create a report.
Step 1: In LibreOffice Base User Interface, click on Reports icon in Database Pane.
Step 2: From Tasks Pane, click Use Wizard to Create Report… option.
Step 3: The Report wizard along with two other windows will be displayed. One of
the window is Report Builder window and the other is Add Field dialog
box.
Step 4: The first step of wizard is to select table and corresponding fields that we
want to display in our report. From Tables or Queries list box, select the table.
 All the fields of table will be listed in the Available Fields list
box. Click >> button to shift all the fields to Fields in report list
box.
Step 5: Click on the Next button. The next step is to label the
fields. By default, the column headers will be displayed
as labels or column headers for the field values. As fields
names are generally shortened, to change to more self-
explanatory names, type the new names in the respective text
boxes.
Step 6: Click on Next button. Next step is to group the data based on any of the
fields in the report.
 To create a report that groups the retrieved data according to a particular field,
specify the field name according to which the retrieved data has to be grouped.

Step 7: Fourth step is to set Sort options. If data to be displayed in report has to
be sorted in either ascending or descending order of a particular field, specify the
field and sorting order in this step. The radio button for Ascending is already
selected. Select Descending radio button to display records in descending order.
Step 8: Click on Next button to move on to next step in which layout of report will
be selected.
Step 9: Layout is the manner in which the labels, field values and titles will be
displayed in the report. Out of various Layout options given, choose the desired
layout, say Tabular (Default) {6 Options} and also layout of headers and footers
(Default). You may also choose the orientation option Landscape or Portrait.
Default orientation option Landscape.
Step 10: Click on Next button to move to last step. Name the report and to specify
the manner in which we want to proceed after the wizard finishes. Type the name
of the report. By default the type of report is Dynamic. That means as the
field values in the base table or query change, the report will also change
automatically. If you don’t want automatic updation of the report, choose the Static
option. In this step, specify whether you would like to modify the report or create
the report once the wizard finishes. Default option “create the report now”
Step 11: Click on Finish button to display the report.
 INSERTING OTHER CONTROLS IN THE REPORT:
We can make report more presentable by inserting some more controls like titles,
author name, date of generation of report etc.
 Right click on the Report name on the LibreOffice User Interface and then select
the Edit… option. The Report Builder window will open. In this window, various
controls can be inserted using the Report Controls toolbar.

INSERTING TILES AND HEADINGS:

Step 1: Click on the Label tool available on the Report Controls toolbar.
Step 2: Bring mouse pointer on report. Click and drag to insert label textbox.
Step 3: Double click on it to open the Properties dialog box. Type the title text
that you want to display in the Label property textbox and also set the font style
and size using the Font property
Step 4: Close the Properties dialog box.

INSERTING DATE AND TIME:

Step 1: Click in the Page Header area to make it active.
Step 2: Click Insert > Date and Time… option
Step 3: The Date and Time dialog box will be displayed
Step 4: Select the desired format for date and time format and click on OK button.
–
 The date will be inserted on the top left corner of the Page Header area.
 You may click and drag it to reposition it in any place in the Page Header area.