Unit 3(Part B): 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.
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.
• 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.
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 64000
Memo LONGVARCHAR
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
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 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: Click on Finish
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 – Foreign key is used to link one table to another
table using referencing where the first table will be primary key,
and the second table will be foreign key. It is used to make
relationship between two tables.
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.
Queries in Base
In the database data is stored in an organized manner which helps to retrieve
data quickly and accurately. To search and retrieve the desired data from the
database, we have to give its specifications to DBMS. Such specifications are
given to the database in the form of queries.
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
Database Languages
Database Languages having two type:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
DDL Statements:
• Create: Using this statement, a database or set of tables can be
created.
• Alternate: This statement is used to change the table’s structure.
• Drop: This statement is used to remove database objects from
the system.
DML statements:
• SELECT: The statement “SELECT” is used to get data from the
database.
• INSERT: The statement “INSERT” is used to add a new record to
the database.
• DELETE: The database can be cleaned out by using the
statement DELETE.
• UPDATE: This statement is used to modify the database’s
information.
