Unit-3

Database
Management
System
using
LibreOffice Base
Information Technology (Class-X)
 Chapter 8

Introduction
to
Database
Management System
 Data and Information
• The raw facts constitutes data. The
facts may be related to any person,
place, activity or things.

• Information is the processed or

organized form of data.
 Databases and DBMS
• A database is a collection of logically
related data items stored in an 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).
 Databases and DBMS
• Some of the common examples of
DBMS are MS Access, Open
Office or LibreOffice Base,
Oracle, Ingress, MySQL.
 Advantages of Database
• Organised Storage
• Data Analysis
• Data Sharing
• Minimal Data Redundancy
• Data Consistency
• Increases Efficiency
• Increases Accuracy
• Increases Validity
• Security
 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.
 Different Data Models
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.
 Different Data Models
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.
 Different Data Models
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 Relational Database Model was proposed in
1970 by E. F. Codd.
• Relations are set between tables based on
common fields.
 Different Data Models
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 Relational Database Model was proposed in
1970 by E. F. Codd.
• Relations are set between tables based on
common fields.
 Relational Database Terminology

Entity
• It is a real world object about which
information is to be stored in a database.
Example Student, Teacher etc.

• Each entity is a collection of attributes

associated with it.
• These attributes are represented in the form
of columns.
 Relational Database Terminology

Table
• A table is a collection of logically related
records.
• It is organised as a set of columns and
rows.
 Relational Database Terminology

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.
• A collection of tables make a database.
• A field holds the data values of one type of
data.
 Relational Database Terminology

Data Values

• Data values are the raw data represented in

numeric, character or alphanumeric form.
 Relational Database Terminology

Record or Row

• The data values for all the fields related to a

person or object is called a record.
 Primary Key (PK)

• A primary key is a unique value that

identifies a row in a table.
 Composite Primary Key

• When primary key constraint is

applied on one or more columns
then it is known as Composite
Primary Key.
 Composite Key

create table s
(
rno int,
admno int,
name char(20),
marks float(10,2),
primary key(rno,admno)
);
 Foreign key (FK)

• 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.
• A foreign key is used to represent the
relationship between two tables.
• A non-key attribute , whose values are
derived from the primary key of some other
table is known as foreign key in current table.
 Foreign key (FK)
Table - Student
Roll No Name Stream_code Foreign Key
1. Parneet S101
2. Inpreet S102
3. Gurshan S103
Table Stream
Primary Key Stream_code Stream_Name
S101 Humanities
S102 Commerce
S103 Science
Foreign Key

create table teacher1

(
tid int primary key,
tname char(20),
tsubject char(20)
);
 Foreign Key

create table student1

(
sid int,
sname char(20),
tid int,
foreign key(tid) references teacher1(tid)
);
 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.
 Alternate Key

• Out of the candidate keys, one or two

are made as primary keys. The others
are the alternate keys.
 Objects of an RDBMS

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.
 Objects of an RDBMS

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.
 Objects of an RDBMS

Queries

• A query is used to retrieve the

desired information from the
database.
 Objects of an RDBMS

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.
Let’s Practice
 Chapter 9

Starting
with
LibreOffice Base
 Data Types

• A data type refers to the type of data

that will be stored in that particular
field.
• The memory size of a field varies
according to its data type.
 Text Data Types
The text data is a combination of letters, numbers
or special characters. No arithmetic calculations
can be performed on text data.
 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.
 Data Type
Currency Data Type – The currency data type indicates
the monetary values and can be stored using
currencies of various countries.

Date Data Type – This data type is used to indicate

dates and time. For example 12/25/2019, 08:45 AM.
 Data Type
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.

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.
Practice Work
 Starting with LibreOffice Base

• Click Start > LibreOffice or double click on the

LibreOffice icon on the desktop.
• A Database Wizard opens
 Starting with LibreOffice Base

• Click Create a new database radio button or Open

an Existing Database option
 Starting with LibreOffice Base

• The Next step gives the option to register our

database.
• Select the radio button with option, ‘No, do not
register the database
 Starting with LibreOffice Base

• Click Finish button to complete the database

creation process.
• The Save As dialog box appears.
 Starting with LibreOffice Base

• Browse for the drive and folder where you want to

store your database.
• Type the name in the File name text box.
 Starting with LibreOffice Base

• Click Save button. The database in Base is saved

with an extension .odb.
• Default name of database is NewDatabse.odb.
User Interface of LibreOffice Base
Title Bar
Menu Bar

Standard Toolbar

Database Pane
 Opening a Database

Click File -> Open

Tip: Use the keyboard shortcut key Ctrl+O to open an

already existing database.
 Creating a Table

• Use Wizard to Create Table

• Create table in Design View
Use Wizard To Create Table
Use Wizard To Create Table
Use Wizard To Create Table
Use Wizard To Create Table
Use Wizard To Create Table
Use Wizard To Create Table
Use Wizard To Create Table
Creating table using Design View
Creating table using Design View
Creating table using Design View
Creating table using Design View
Creating table using Design View
Creating table using Design View
Setting the Primary Key
Composite Primary Key
 Saving a Table

File -> Save As

Tip: Press Ctrl + S to save the table or click Save icon

from the toolbar.
Entering Data in a Table

Use Tab to move to next field

Use Shift + Tab to move to previous field
Navigating through the Table

New

First Previous Next Last

 Editing Data

Edit Icon

Tip:- Press Esc key to cancel the corrections made and

restore the original contents.
 Deleting Records from Table

• Pressing the Del key from the keyboard

• Delete Record option from the Edit menu

• Right clicking on the record and clicking

on the Delete Rows option

Sorting Data in the Table

Sort Ascending Sort Descending

 Closing LibreOffice Base

Click on the File -> Close

Or
Click on the cross (x) button
 Chapter 10

Working
with
Multiple Tables
Editing and Deleting Tables

Editing a table involves the task such

as:-
• adding a new field in a table
• removing any field in a table
• to alter any of the field properties
• rename a table
Editing and Deleting Tables

• adding a new field in a table

Editing and Deleting Tables

• removing any field in a table

Editing and Deleting Tables

• alter any of the field properties

Editing and Deleting Tables

• rename a table
 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.
 Types of Relationships

• ONE to ONE

• ONE to MANY

• MANY to MANY
 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.
 One-to-Many relationship

• This is one of the 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.
One-to-Many 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.
Many-to-Many relationship
Advantages of Relating Tables in a Database

• A relationship can help prevent data

redundancy.
• Referential Integrity.
• Creating relationships between tables
restricts the user from entering invalid
data in the referenced fields.
• Any updation in the master table is
automatically reflected in the transaction
tables.
Creating Relationships between Tables

Table 1
Table 2
Creating Relationships between Tables

Parent Table

Child Table
Creating Relationships between Tables

click on Tools -> Relationships…

Creating Relationships between Tables

Add Table dialog box appears

Creating Relationships between Tables

Add Tables dialog box, click Events table

and EventCategory table then click Add
button
Creating Relationships between Tables

Drag the common field CategoryID from the

Events table and drop it in EventCategory
table
Creating Relationships between Tables

The line is labeled as ‘l’ on the primary key

side and as ‘n’ on the foreign key side.
 Referential
Integrity

According to the principle of referential

integrity, no unmatched foreign key values
should exist in the database.
Creating Relationships between Tables

To set the relationship properties double

click on the relation line joining the two
tables.
Four options to maintain referential integrity

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.
Four options to maintain referential integrity

Error Updating while changing data

Four options to maintain referential integrity

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.
Four options to maintain referential integrity
Four options to maintain referential integrity

Data updated from 100 to 1001

Four options to maintain referential integrity

Set NULL

• This option assigns NULL value to all the

related fields if the master record is

deleted or updated.
Four options to maintain referential integrity
Four options to maintain referential integrity

Right Click and Select Delete option

Four options to maintain referential integrity

Category ID set as NULL

Four options to maintain referential integrity

Set default

• This option assigns any fixed default value

to all the related fields if the master record

is deleted or updated.
Four options to maintain referential integrity
Chapter 11

Queries
in
Base
 Queries

• Using a query, we can retrieve and display

data from one or more tables in a
database.
• Using a query, we can specify the fields
that we want to display and also the criteria
based on which the records to be filtered.
 Creating a Query

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

• Open the
database.
• In the Database
Design window,
click on Queries
button present in
the Database
Pane.
• Click on Use
Wizard to Create
Query option.
Creating a Query Using a Wizard

Select fields from the respective tables.

 Creating a Query Using a Wizard

Clicking on the Next button will display the

screen to select the sorting order.
 Creating a Query Using a Wizard

Clicking on the Next button and select the

criteria.
 Creating a Query Using a Wizard

Steps 4, 5 and 6 given in the Steps Pane deal

with tasks like summarizing and performing
numerical calculations.
 Creating a Query Using a Wizard

The next step to give alias name i.e. the

column header name
 Creating a Query Using a Wizard

The last step of the Query wizard displays the

entire overview of the query.
Creating a Query Using a Wizard

Output
 Creating a Query in Design View

Click Queries icon on the Objects Pane in the

Database Window.
 Creating a Query in Design View

Click on the table to be used in the query and

then click on Add button.
Creating a Query in Design View
Creating a Query in Design View

Click on the Run Query option.

Creating a Query in Design View

Output
 Editing a Query

To edit any query, Right click on the Query

icon and then select Edit option.
 Working with Numerical Data

we can even use certain mathematical

functions to find the count, sum, minimum,
maximum or average of data values.
Working with Numerical Data
Chapter 12

Forms
and
Reports
 Forms
• 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.
• For any database, it is the front end for
data entry and data modification.
• A form contains field controls arranged in a
presentable and user friendly manner.
 Forms

There are two ways to create a form:

• Using a wizard
• Using the Design View
 Creating a Form Using a Wizard

Open the database -> Form icon -> Database

Pane -> Use Wizard to Create Form…
 Creating a Form Using a Wizard

select the tables or queries

 Creating a Form Using a Wizard

shift all the fields of table from Available Fields list

box to Fields in the Form list box using >> button.
 Creating a Form Using a Wizard

No need to Add Sub Form -> Click on Next Button

 Creating a Form Using a Wizard

The wizard skips the next two steps that relate to

the subform and moves on to step 5.
 Creating a Form Using a Wizard

Arrange the controls to your subform

 Creating a Form Using a Wizard

Select the data entry mode

 Creating a Form Using a Wizard

Apply the style of your form

 Creating a Form Using a Wizard

Set the name of the form

 Creating a Form Using a Wizard

Form created
 Records Toolbar
Total No. of Records

Form created Previous Record Last Record

Absolute Value First Record Next Record New Record

Data Source as Table

Delete Record Sort Form Based Filter

Modifying a Form - Changing the background color

Right click -> form name ->Edit…option -> Form

Design View will open.
Modifying a Form - Changing the background color

Right click -> Page Style

Modifying a Form - Changing the background color

Page Style dialog box -> Area tab

 Modifying a Form - Editing the labels

• Press the keyboard shortcut key Ctrl+Click to

select the label.
• Right click on the selected label and select
Control Properties… option
 Modifying a Form - Editing the labels

• The Properties: Label Field dialog box will appear

 Modifying a Form - Moving a control

• Move both Label & Text Box

• To re-positioned one then press Ctrl key

Changing the size of the textbox control

• To change the size of the textbox control, press

Ctrl button while clicking on the textbox.
 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.
• Right click on the selected Text Box and select
Control Properties… option
Adding a Tool tip
Adding a Tool tip
 Adding a calendar for the date field

• Place the mouse pointer over the Date text box

and press Ctrl+Click to select it.
• Right click on the selected Text Box and select
Control Properties… option
Adding a calendar for the date field
Adding a calendar for the date field
Adding a calendar for the date field
Adding a calendar for the date field
Adding text to the form
Step 1
Step 3

Step 2
Adding text to the form
Step 4 Step 6
Step 5
 Adding a new record using a form
Design Mode

New Record
Adding a new record using a form

Delete Record
 Reports
• Using a report we can present the retrieved
data in an attractive and customized
manner.
• We can create a report based on a table or
a query or both.
 Creating a Report Using a Wizard

Open the database -> Report icon -> Database

Pane -> Use Wizard to Create Report…
Creating a Report Using a Wizard
Creating a Report Using a Wizard
Creating a Report Using a Wizard
Creating a Report Using a Wizard
Creating a Report Using a Wizard
Creating a Report Using a Wizard
Inserting other controls in the report
Inserting other controls in the report
Inserting Titles and Headings
Inserting Date and Time

Insert Menu-> Date and Time

Inserting Date and Time

Output
Create Report Group Wise
Create Report Group Wise

Output