Database Management System Class 10 Notes

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.

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. 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.
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.
 Table – Table is a collection of logically related records, It is organized as a set of
columns.
 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.
 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.
 Reports – The output of any query in the database may be displayed in the form of
reports.

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

Memo LONGVARCHAR to
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 allocated
Text VARCHAR
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

 Name Data type Signed Range

Decimal DECIMAL Yes Unlimited

Float FLOAT Yes

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

Double DOUBLE Yes

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 data type

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 data type

The binary datatype is used to store digital images,sounds, or videos.

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 method 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: set fields data type.
 Step 5: Set up the primary key
 Step 6: Click on Finish

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 this type of relationship, one specific record of a master table has one and only one
corresponding record in the transaction table. For example, the record for Admission_No in the
master table (Student_Detail) will have only one corresponding record of same value of
Admission No in the transaction table of Student_ Result.

One-to-Many relationship
This is one of the most common types of relationship between the tables in a database. As the
name says, 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.

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
shopkeeper may sell multiple products to multiple customers. So many-to-many relationship
exists between a product and a customer.
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.

Referential Integrity
According to the principle of referential integrity, no unmatched foreign key values should exist
in the database. That means if a record, say Admission No as 1001 is not present or deleted in
the master table (Student_Details) of Student database, then there should be no record with
Admission no as 1001 in the transaction table (Student_Result) as well.

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.

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.
A query can be created in three ways –
1. Using a Wizard
2. In Design View
3. In SQL view

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. It displays the data in a layout design by us and not just in a simple
row and column format. Primarily, a form contains field controls arranged in a presentable and
user friendly manner.
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 subform
 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.

Reports
A report is another useful feature of a database management system. We have seen that the
records that have been extracted using a query are displayed in a simple row and column
format. Instead, 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. 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.

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