Database Management System Class 10 Notes

Data is most important for any organization or in a company, the organization’s primary
objective is to store data, manage data and retrieve data from computers and this is only
possible with database management systems.
Chapter 8: Introduction of Database Management System
A database is an organized collection of data that is maintained electronically. Any kind of
data like text, numbers, pictures, videos and files can be stored in a database. This data can be
stored, retrieved and edited using software known as a database management system.
Data and Information
 Data – Data is a collection of facts which do not have any meaning. This data is
unstructured information such as numbers, text, symbols and images.
 Information – Information depends on data, this information has some meaning. This
Information refers to organized and structured data.
Databases and DBMS
 Database – A database is a collection of logically related data items stored in an
organized manner, It is also considered as a data bank.
 DBMS (Database Management System) – This software that is used to create,
update and retrieve data is known as database management system. Some of the
common examples of DBMS are LibreOffice Base, MS Access, Open Office Base,
Oracle, MySQL.
Advantages of DBMS
 Organized Storage – In database data are stored in an organized manner, so that
retrieval of the data is fast and accurate.
 Data Analysis – In database you can retrive the data based on certain criteria for
example average, maximum or minimum value etc.
 Data Sharing – Databases can share the data with other applications.
 Minimal Data Redundancy – In database duplicate data is not allowed, it means that
same data will not be repeated in the table.
 Data Consistency – In database if any record is changed in any one of the tables then
the same data will update in other tables also.
 Increase Efficiency – In the database the data is organized which helps to retrieve
and search data efficiently.
 Increases Accuracy – In the database the data is retrieved accurately from the
database.
 Increases Validity – In the database the valid data is being entered and checked by
the database.
 Security – Unauthorized access is not allowed in the database.
Data Models
A database can be designed in different ways dependent on data storage. This structure of the
database is known as a data model. The data models describe the relationships between the
data.
There are different data models such as –
 Hierarchical data model
 Network data model
 Relational data model
Hierarchical Data Model
In Hierarchical Data Model the data is organized like tree structure and data is stored in the
form of a table.

Network Data Model

In this model, multiple records are linked to the same master file. This network model is
considered as a tree where the master table present in the bottom and other tables linked to
the master table.

Relational Data Model

In relational data models are based on relationships between two or more tables of the same
database. This is the most popular data model.
Relational Database Model
E. F. Codd given the concept of Relational Database Model in 1970. The most popular type
of database model is the relational model. In relational database models the data elements are
stored in different tables, this table is related through the common fields.
Relational Database Terminology
Some of the common terms used in RDBMS.
 Entity – Entity is a piece of data that is stored in the database, for example student
rollno, name and age is the entity of the student record. These details associated with
the entity are called also called attributes.
 Table – Table is a collection of logically related records, It is organized as a set of
columns with records.
 Field or Columns or attributes – The smallest entity in the database is known as
field, a collection of fields is known as record, a collection of records make a table
and a collection of tables make a database.
 Data Values – Data values are raw data represented in character, numeric or
alphanumeric.
 Record or Row – The collection of data values of all the fields are known as record.
 Primary Key – The field which uniquely identifies a row in a table. The primary key
used to be a unique identifier for the table.
 Relational Database – A relational database is a collection of related tables.
  Foreign Key – The 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.
 Candidate Key – All the field values that are eligible to be the primary key are the
candidate key for that table. Such fields can neither be left blank nor can have
duplicate values.
 Alternate Key – One or two fields in the table are made as primary key but others are
the alternate key.

Objects of an RDBMS
The database is a collection of object or a feature that is used to store, represent or retrieve
data, different types of objects in a database are given below –
 Table – Table is a collection of row and column which is used to store data. The data
in the database is first stored in a table.
 Form – A form is used to enter data in a table, form is the interface between database
and users, it is an easiest method to enter data in database using user friendly
manners. A form consists of a text box, radio button, labels, check box etc.
 Queries – Queries are used to retrieve the desired result from the database. In simple
terms, it is a question asked from the database.
 Reports – The output of any query in the database may be displayed in the form of
reports. But, 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

Data Types
A data type refers to the type of data that will be stored in that particular field. The memory
size of a field varies according to its data type. There are six different types of data type in
LibreOffice Base –
 Text Data Types
 Numeric Data Type
 Currency Data Type
 Date Data Type
  Boolean Data Type
 Binary Data Type
Text Data Types
The text data consists of a mix of special characters, numbers, and letters. With text data, no
mathematical operations may be carried out. PAN card numbers, names, marks, and other
data types are examples of text data.
The lists of various data types that can store textual data –

Name Data Type Description

Memo data type allows to store text data up to

Memo LONGVARCHAR
64000 characters.

Character data type is used to enter fixed number of

characters
Text(fix) CHAR
for example AADHAR NO, ACCOUNT NO, PASSPORT
NO etc.

Stores upto the specified length, the number of bytes

Text VARCHAR allocated
depends on the number of characters entered by the user.

Numeric Data Type

Numeric Data type contains numbers. The number can be integer, real, float or double.

Name Data type Signed Range

Tiny Integer TYNYINT No 0-255

Small Integer SMALLINT Yes -32768 to 32768

Integer INTEGER Yes -2.14×109 to 2.14×109

BigInt BIGINT Yes -2.3×1018 to 2.3×1018

Number NUMERIC Yes Unlimited

Decimal DECIMAL Yes Unlimited

Float FLOAT Yes

 Name Data type Signed Range

Real REAL Yes 5×10(-324) to 1.79×10(308)

Double DOUBLE Yes

List of different numeric data types are –

Currency Data Type
Currency Data type contains monetary values and can store currencies of different countries.
Date Data Type
This data type is basically used for date and time. There are various formats used to store date
and time.
List of various forms of date data types are –

Name Description

Date Stores the year, month and day as it is stored in the system.

Time Stores the time of the day as hour, minute and second.

Timestamp Stores date and time information at once.

Boolean
In Boolean data type there are only two values – True or False, this also can be given in
multiple formats like Yes/No, True/False, On/Off.
Binary
The binary datatype is used to store digital images and sounds.
Starting with LibreOffice Base
LibreOffice base is a database management software belonging to LibreOffice suite, It is free
and open source software for creating and managing data. It also allows you to create a query
for retrieving desired results from the database.
Steps for starting LibreOffice Base –
 Step 1: Click on Start > LibreOffice base
 Step 2: In Database Wizard, Click on Create a new database
 Step 3: After Creating a new database, Click on Yes, register the database for me
 Step 4: Click on Finish, Now your database will be created
Creating a Table
After creating a database now you will start working with objects of the database, The first
object we are going to create is Create Table. There are two different methods to create
table:
1. Create Table using design View
2. Create Table using a Wizard
Creating Table in Design View
 Step 1: Click on Table
 Step 2: Click on Create Table in Design View
 Step 3: Enter Field Name, Field Type and Description
 Step 4: Click on Save
Steps for Creating Table using a Wizard
 Step 1: Click on Table
 Step 2: Click on Use Wizard to Create Table
 Step 3: Select the field which you want
 Step 4: Click on Finish
Sorting Data in the Table
Data in a table can be arranged in ascending or descending order. This process of arranging
the records in particular order on any filed is called as sorting. Steps to sort the table are:
1. Open the Event table in datasheet view and select the field on which you want to sort.
2. From the tool bar click Sort Ascending or Sort Descending icon to sort in ascending or
descending order respectively.
NOTE: To sort the table based on more than one fields. In such case, click Sort icon on the
toolbar.

Setting the Primary Key

To make a particular field as the primary key, place the mouse pointer before the field name,
and right click. A pop up menu appears and Select the Primary Key option. A key icon
appears before the field name indicating that it is a primary key.

Saving a Table

1. To save the table click on the save button or click File > Save As or press Ctrl + S from the
keyboard.
2. A Save As dialog box is displayed. Enter the name of table and click on OK button.

Entering Data in a Table

To enter data in the table, double click on the created table icon in the Tables Object Area on
the database screen. Alternatively, we can open the table by right clicking on the desired table
and then selecting the Open option from the drop-down menu. The datasheet view of the
table will appear. In this view, we can enter the records.
Navigating through the Table
To navigate through various records of the table, we use the navigation box present at the
bottom of the datasheet window as shown below.

The various components of Navigation Box are as follows:

Record Selector Box – This is the text box where the currently active record number is
displayed. We may enter the record number that we want to see in this text box.

Navigation Buttons – These are used to scroll vertically in the table.

Press |◄ button to move to the first record.

Press ► and ◄ to move to the next and previous records respectively.
Press ►| button to move to the last record.
Editing Data
To edit or modify the entered data, place the cursor on the field value that has to be edited to
edit and enter the new value. The Edit icon appears before the record that is being edited.
This icon is displayed till the table is saved after making the required changes.

NOTE: Press Esc key to cancel the corrections made and restore the original contents.

Chapter 10: Working with Multiple Tables

After creating tables in a database, you can also create relationships between two or more
tables to control data redundancy and inconsistency. When you create a relationship between
two or more tables then adding or updating a record in one table reflects the changes in all the
related tables.
Master table vs Transaction table
If the School wants to manage student fees collection in a database, then they will create two
tables, one for Student Details and second for Free details, here the Student Details will be
master table and Fee details will be transaction table.
Relationships between Tables
A relationship is a connection between two or more tables that contain data, the relationships
between tables are based on a common field. This relationship helps to improve table
structures and reduce redundancy of the data.
Types of Relationships
The relationships between two or more tables in a database is based on the number of
records, Primarily there are three different types of relationships that can be set up between
two or more tables.
1. One-to-One
2. One-to-many
3. Many-to-Many
One-to-One relationship
In a One-to-One relationship, the master table and transaction table both have one record.
One-to-Many relationship
In a One-to-Many relationship, the master table having one record and transaction table
having multiple records. This is a very common type of relationship between the tables in the
database.
Many-to-Many relationship
In a One-to-Many relationship, the master table has multiple records and the transaction table
has multiple records.
Advantages of Relating Tables in a Database
1. A relationship helps to prevent data redundancy.
2. Relationships between tables help to restrict from entering invalid data in the
referenced field.
3. It helps to prevent missing data by keeping deleted data from getting out of sync.
4. Any update in the master table is automatically reflected in the transaction tables.
Primary Key, Composite Primary Key and Foreign Key in a Database
In the RDBMS data can be integrated using keys. These are Primary Key, Composite Primary
Key, and Foreign Key, Key are used to make the relationship between the tables.
1. Primary Key – This unique field is called the Primary Key (PK). primary key is a
field or a set of fields that uniquely identify each record in a table. A primary key must
be unique and cannot contain null values.
2. Composite Primary Key – A composite primary key is a primary key that consists of
two or more fields that together uniquely identify each record in a table.
3. Foreign Key – A composite primary key is a primary key that consists of two or more
fields that together uniquely identify each record in a table.
Referential Integrity
As per the principle rule of the database, no unmatched foreign key values should exist in the
database. Referential integrity processes concentrate on ensuring data consistency and the
link between tables.
LibreOffice Base gives us following four options to maintain referential integrity –
1. No action – This is default option, This option states that no update or delete are
allowed in the master table if any related record exists in the transaction table.
2. Update cascade – This option allows to delete or update the referenced field but the
related records that are also available in any transaction tables will also be deleted or
updated.
3. Set NULL – This option assigns NULL value to all the records in the master record
that are deleted or updated.
 4. Set default – This option assigns a fixed default value to all the related fields if the
master record deleted or updated.
Chapter 11: Queries in Base
A database is used to store data in an organized manner. Queries are used to retrieve the
desired data easily and accurately from database. In other words, we can say that a query is a
sort of question asked from a database.
A query is one of the most important features of any RDBMS. 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 criterion based
on which the records to be filtered. For example, In a Student database, if we want to display
the names and marks of the students in a particular class from the Result table, who have
scored less than 320 aggregate marks.

Query
A query is the most important feature of DBMS. Query is a request for data, query helps to
retrieve and display data from one or more tables in a database. This is done by giving criteria
to the DBMS. After creating the Queries you and edit and do modification as your
requirements.
A query can be created in three ways –
1. Using a Wizard
2. In Design View
3. In SQL view
Creating a Query Using a Wizard
1. Open the School database. Click on Queries button present in the Database Pane.
2. In the Tasks Area, click on Use Wizard to Create Query… option
3. The Query Wizard will open.
4. Select the required table and then select the required fields(Name, Class and Rno) from the
“Available fields” window to “Field in the Query” window and click on Next Button.
NOTE: Clicking on >> button moves all fields to “Fields in the Query” area.
5. Select the ascending or descending order of any particular field of the table. Since we do
not want to set in a particular order, so we click on Next button.
6. The next step is to set the search conditions or the criteria on the basis of which records
will be filtered from the table.
NOTE: Since we want to display the detail of student having roll number 3 so we set roll
number as 3 in above step
7. Steps 4, 5 and 6 given in the Steps Pane are not required if there is no numeric field
involved in the query. So skip these steps.
8. The next step to give alias name i.e. the column header name will be displayed when we
run the query. Click on Next after writing alias name.
NOTE: By default the field names will be displayed as column headers. Some times field
names are not user friendly, so an alias name which is more readable is required. For example
change the default field name Rno to Roll Number.
9. The last step of the Query wizard displays the entire overview of the query.
10. Click on Finish button.
Creating a Query in Design View
Another way to create a query is using the Design view. This is a more flexible method.
1. Click Queries icon on the Objects Pane in the Database Window.
2. Click Create Query in Design View… icon in the Tasks Pane. The Query Design Window
appears. In the middle of the window the Add Table or Query dialog box is displayed as
shown below
3. Click on the “Table1” table to be used in the query and then click on Add Button.
Alternatively double click on the “Table1” table.
4. Click Close button in the Add Table or Query dialog box to close it.
5. Next step is to select the fields. For our query we want to display Roll number, Name and
Class. So in the list box of “Table1” table, double click on the required field.
NOTE: Observe that the Visible Check Box is by default selected. This means that all these
three fields will be visible when you run the query. In the grid, there is a row titled Alias. It
can be used to display meaningful names in the output. For example, instead of Rno, we
would just like to display Roll Number.
To sort the records in either ascending or descending order of a particular field, the Sort row
is given in
the grid. Select ascending or descending from the drop down of Sort row.
6. Write 3 in Criteria row below the Roll number Column.
NOTE: Once the query is designed, click Run Query ( ) button on the toolbar or press F5 key.
The query result will be displayed in the Tables Pane area.
7. Click on Save button to save the query.
NOTE: By default, the name of the Query is Query1. To run the query again, double click on
the query name. To close the Query window, click on on close button on the top right corner
of the window.

Chapter 12: Forms and Reports

LibreOffice Base provides the Form feature for data entry purposes and report feature for
displaying the data.
Form
A form is a database object with a user-friendly interface that allows data entry and viewing
in a visually attractive and accessible method. It serves as the front end for entering and
editing data into any database.
There are two ways to create a form:
 1. Using a wizard
2. Using the Design View
Creating a Form Using a Wizard
Creating a Form Using a Wizard is the simplest way to create a form in LibreOffice base. To
create a form using wizard, follow the following steps.
 Step 1: Open Database
 Step 2: Click on Use Wizard to Create Form
 Step 3: Select the fields of your form
 Step 4: Decide if you want to set up a sub-form
 Step 5: Arrange the controls on your form
 Step 6: Select the data entry mode
 Step 7: Apply Style
 Step 8: Set the name of the form
 Step 9: Click on Finish
Forms Control Toolbar – This toolbar contains various controls that can be added to the
form.
Records Toolbar – The Records toolbar contains the navigation control buttons in the
extreme left.
Creating Reports using wizard
You can create report from the Report using a wizard. To create a report, follow the following
steps –
 Step 1: Open Database
 Step 2: Click on Use Wizard to Create Report
 Step 3: Select, Which fields do you want to have in your report
 Step 4: Enter, How do you want to label the field
 Step 5: Add grouping levels in the Report Wizard
 Step 6: Select sort option according to the data
 Step 7: How do you want your report to look
 Step 8: Describe how you want to processed
 Step 9: Click on Finish