UNIT-3: Database Management

System using LibreOffice Base

Chapter 8. Introduction to Database Management System
Introduction--Databases and database systems are essential parts of our life. When we go to
book railway tickets, to search for a book in a library, to get the salary details, to get the balance
amount after withdrawal of money from the ATM and so on, this list can run into several pages.

What is Data?
Data is a collection of raw, unorganized facts and details like text, observations, figures, symbols
and description of things etc. In other words, data does not carry any specific purpose and has no
significance by itself. Moreover, data is measured in terms of bits and bytes – which are basic units
of information in the context of computer storage and processing.

What is Information?
Information is the processed, organized and structured data. It provides context for data and
enables decision making. For example, a single customer’s sale at a restaurant is data – this
becomes information when the business is able to identify the most popular or least popular dish.
Similarly the temperature recorded is datawhich can be processed to find out the maximum or
minimum temperature of day and night.
In simple terms, we can conclude that data is an unorganized description of raw facts from which
information can be extracted.
Databases and DBMS
Database– A database is an organized collection of logically related data items. For example: -
In a stationary shop, detailed records of the materials available in the shop is database.
Similarly in a computerized system, we need to maintain several files; we would use database
(DBMS) programs such as Microsoft Access, LibreOffice Base, Oracle, Ingress and My SQL.
These database programs are used to organize the data as per our needs in the computer
system.
Database Management System (DBMS) - A database management system is a software package
with computer programs that controls the creation, maintenance and use of a database. A DBMS
allows different user application programs to concurrently access or retrieve the same database.
Some of the DBMSs are Oracle, IBM DB2, and Microsoft SQL server, Microsoft Access, Postgre
SQL, My SQL, Fox-pro and SQL Lite.

**Features of Database**
The database offers many features to the users. Some of them are as follows:
1. It stores the data and information in multiple tables.
2. Each contains data related to each category or items. For example, in school database tables
are students, teachers, exam etc.
3. Each table can have at least one column that holds unique values. For example in students
table adm_no (Admission Number) can be used.
In teachers table teacher ID or teacher code can be generated.
4. Some of the keys should be generated like primary key, foreign key etc.
Advantages of DBMS
Let us discuss some of the advantages of DBMS:
• Organized Storage – The data in the database is stored in an organized 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 minimizing 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.
• Data Integrity – Data integrity ensures that the data stored in the database should be valid,
accurate, error-free and consistent. Thus, interrelating data becomes very easy and flawless.
• Security of Data – Unauthorized access can be controlled by assigning passwords to the users.
The data might be translated in such a manner that unauthorized 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.

 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 a hierarchy.
For example, the data of a
company is stored using a
hierarchical data model.
  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 the principle of setting relationships between two or more tables of the
same database. It is the most commonly used database model. The data in different tables are
related through the use of common fields.
Relational Database Model-In this model, the data elements are stored in different tables made
up of rows and columns. It was proposed in 1970by E. F. Codd.
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-

• Entity – It is a real world object about which information is to be stored in a database. For example, if
we want to store information about an entity Student in a school, then we need to have his
admission number, roll number, name, father’s name, date of birth, etc. These details associated
with the entity are called attributes. Each entity is a collection of these attributes associated with it.
• Table – A table is a collection of logically related records. It is organized as a set of columns, and
can have any number of rows.

• Field or Columns or Attributes – A field is the smallest entity in the database. A collection of fields make
a record, a collection of records make a table and a collection of tables make a database. Fields
are individual record characteristics and are presented as columns within a table. Data values are
stored in a database as fields.
• 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.
• Record or Row – The data values for all the fields related to a person or object is called a record. It is
presented as rows within 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.
• 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. For example, in a student table, each student has a
unique roll no., which forms the primary key.

Note: -If, in a table we use more than one field 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.
• Relational Database – A relational
database is a collection
of related tables. For
example, in Fig. 8.3, the
database contains two
related tables.
• Foreign Key – If a field or combination of
fieldsof one table can be
used to uniquely
identifyrecords of
another table, then that
particular fieldis known as the foreign key. This foreign key helpsto build a relation
between two tables.
• 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.
• 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 an RDBMS- 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 – A table is the basic unit of any DBMS, in row and column format.
• 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 textboxes, labels, radio buttons, list boxes, and
checkboxes 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.
• 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. Butif 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. It can be downloaded from www.libreoffice.org
and is available for both Linux and Windows operating systems. Data has to be stored in
an organized manner using a DBMS.
Data Types- The nature of data to be entered for various fields areof different types. For example,
names are stored in theform of text, age in numbers, fees in decimal numbers,date of birth in date
format and so on. 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 described below:-
 Text Data Type – The
text data is a
combination of letters,
numbers or special
Characters.
  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.

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

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

 Boolean Data Type-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.

 Binary – The Binary data type used to store digitized images and sounds that come 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.

Starting with LibreOffice Base

In Windows, click Start >LibreOffice or double click on the LibreOffice icon on the desktop Select
Base Database option from the bottom left panel.A Database Wizard opens which allows you to
create a new database or open an existing database.
Since we want to create a new database, so click Create a new database radio button.
User Interface of LibreOfficeBase
• Title Bar – The title bar displays the name of a database and an application in which it is made.
• Menu Bar –The menu bar appears below the title bar. It consists of seven menu items – File, Edit,
View, Insert, Tools, Window and Help.
• 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.
Opening a Database-To open an already created database, click File > Open. You can also press
shortcut key Ctrl+O.
Creating a Table:-A table in LibreOffice Base can be created in two ways –
i) using a wizard or ii) using the Design view.
Using a Wizard-The Table Wizard of Base consists of ready-made tables.
Using the Design View-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.
Setting a Primary Key- To make a particular field as the primary key, place the mouse pointer
before the field name, and right click. A popup menu appears. Select the Primary Key option from
popup menu. A key icon appears before the field name indicating that it is a primary key.
Saving a Table
To save the table click on the save button or follow menu option File > Save As. A Save As dialog box
is displayed. Press Ctrl + S to save the table or click Save icon from the toolbar.
Entering Data in a Table- 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 to
enter the data.
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 using navigation buttons.
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. Press Esc key to cancel the corrections
made and restores the original contents.
Deleting Records from Table- 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 popup menu.
Sorting Data in the Table- 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. Data in a table can be arranged in
ascending or descending order.
 Chapter 10: Working with Multiple Tables
Editing and Deleting Tables-
Editing a table involves the task such as adding a new field or removing any field in a table or to alter any of
the field properties.
To edit a table, open the Database User Interface window. Selecting the Table object in Database
Pane, the list of tables will be displayed in the Table Area. Right click on the table name and select Edit
option from the pop menu.
To rename a table, right click on the table name in the Table Area and select Rename option from the
popup menu. A cursor will appear. Type the new name and press the Enter key.
Similarly to delete a table, right click on the table to be deleted, say Customer table and select the Delete
option from the popup menu.
Relationships between Tables
The most important prerequisite for setting a relationship is that there must be a common field(s) between
the two tables to create a relationship.
The record for a particular entity should neither be repeated nor different data values should appear for a
single entity in the database.
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.
For example-:The record pertaining to Admission No in Student_Details table is considered as the master
record while the corresponding record in the related table (Student_Result) is the transaction record.
Therefore Student_Details is called the master table and Student_ Result is called the transaction table.

Types 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 (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.
For example:- One person can have only one ticket to get entry into a stadium to view the match. So
relationship between a person and his ticket number will be one-to-one relationship.
One-to-Many relationship: -It is used to
create a relationship between two tables.
Any single rows of the first table can be
related to one or more rows of the second
tables, but the rows of second tables can
only relate to the only row in the first table.
It is also known as a many to one
relationship.

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

Advantages 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. This will ensure that information in one table matches information in another.
• Creating relationships between tables restricts the user from entering invalid data in the referenced fields.
• Any updating in the master table is automatically reflected in the transaction tables.
Creating Relationships between Tables-:
To create a relationship between two tables follow these steps:-
1. Click on Tools menu and select Relationships. The Add Tables dialog box appears.
2. Select the table and click Add button. Select another table and click Add button.
3. Click Close button to close Add Tables dialog box.
4. To create a relation between the two tables, we just have to drag the common field. A line connecting both the
tables with the common field appears on the screen.

Referential Integrity
According to the principle of referential integrity, no unmatched foreign key values should exist in the
database. So, referential integrity requires that, whenever a foreign key value is used it must reference a
valid, existing primary key in the parent 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.
Chapter 11: Queries in Base
Definition- A query is one of the most important features of any DBMS. Using a query, we can retrieve and
display data from one or more tables in a database. This is done by giving specific search criteria to the
DBMS so that we are able to view the exact information that we want.
The information may be retrieved from a single table or from multiple tables.
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

(i) Creating a Query Using a Wizard-

In the Database Design window, click on Queries button present in the Database Pane on the left side.
In the Tasks Area, click on Use Wizard to Create Query… option. The Query Wizard will start It contains the
Steps Pane on the left and the Query Details Area on the right.
Detailed steps are given in the textbook, read the chapter. Once the query is created, it can be edited in Design
view.
(ii) Creating a Query in Design View- This is a more flexible method to create a query from either single or a
multiple tables of a database.
Click Queries icon on the Objects Pane in the Database Window.
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.
Once the query is designed, Click Run Query button on the toolbar or press F5 key.
Detailed steps are given in the textbook, read the chapter.
Editing a Query
To edit any query, 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. Right click on the Query Name in the Objects Area of the Database
window.
Select Edit option from the drop down menu. The Query Design window will be displayed.
We can use the Criterion row to apply multiple conditions and can apply all relational operators like <, >, <=, >= , !=
and = as well as wild card searches in the query.
Working with Numerical Data
We can even use certain mathematical functions to find the count, sum, minimum, maximum or average of data
values.
With numerical data in the Database Window, click Create Query in Design View… button to open the Query
Design Window.
To group rows that have same values, Group function is used. It summaries data from the database in conjunction
with aggregate functions (sum, average, max, min etc.)

Chapter 12: Forms and Reports

Forms in BASE
LibreOffice Base provides the Form feature for data entry purposes.
A form is an object of the database that has a user friendly interface where data can be entered and seen in an
attractive and easy-to-read format.
A form contains field controls arranged in a presentable and user friendly manner. Each field control consists of a label
and the field value text box.
There are two ways to create a form:
• Using a wizard
• Using the Design View
Creating a Form Using a Wizard
This is the simplest way to create a form. Click the Form icon on the Database Pane.
Click the option Use Wizard to Create Form… on the Tasks Pane. The Form wizard will open along with a blank
database form in design view. Detailed steps are given in the textbook, read the chapter.
As mentioned before, a field control consists of two parts – label and the field value text box.
Modifying a Form:- If you wish to modify the form after the wizard finishes, click Modify the form option. It
demonstrates how to adjust and customize forms.
The modification can be to change the background color, font size and color of the text or even editing the labels or
positioning of various controls in the form.
Form Controls Toolbar - On the left of the Form Design window is the Forms Control toolbar
This toolbar contains various controls that can be added to the form like Adding a calendar for the date field,
Adding text to the form like titles, heading labels or sub headings, Adding a new record using a form- (For
this purpose, you have to shift or toggle between Design View to Form View by clicking on Design Mode button on
the Forms Controls toolbar.)
On the bottom of the Form Design window is the Records toolbar. The Records toolbar contains the navigation
control buttons in the extreme left. With the help of these buttons, we can traverse and view the records in the file.
Report in Base
A report is another useful feature of a database management system. 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, using a report we can present the retrieved data in an attractive and
customized manner.
Note- 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.
To create Report from the Tasks Pane, click Use Wizard to Create Report… option.

Inserting other controls in report

We can make it more presentable by inserting some more controls like titles, author name, date of generation of report
etc.
For this 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 Titles & Headings
To insert the title of the report, follow the following steps-:
Step 1. Click on the Label tool available on the Report Controls toolbar.
Step 2. Bring the mouse pointer on the report. Click and drag to insert the 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. Double click on the Events Report in the Reports of Database Pane. The
report will be displayed with the formatted effects.