RDBMS Class 10 Notes | Relational Database Management System

Get Latest and well organized RDBMS Class 10 Notes covering all chapters Database Concepts, SQL, Table, Table Operation, Query, Forms and Reports used in Open Office Base. All the topics in RDBMS Class 10 Notes are written in concise manner so that student can learn, understand and remember easily.

Contents

1 SESSION 1: Appreciate the Concept of Database Management System
2 SESSION 2: Create and Edit Table using Wizard & SQL Commands
3 SESSION 3: Perform Operations on Table
4 SESSION 4: Retrieving data using Query
5 SESSION 5: Creating Forms and Reports Using Wizard

SESSION 1: Appreciate the Concept of Database Management System

What is Database Management System?

It is application software that can be used to create and manage database efficiently.
it enable user or application to create, store, update, delete and retrieve data from database itself.
Examples of DBMS are MySQL, Oracle, MongoDB, MS Access, MS SQL Server, FoxPro, SQLite etc.

What is database?

Database is an organized collection of interrelated data that has been recorded, organized and made available for searching is called Database.

Advantages of Database

Reduced Data Redundancy: As different type of data are stored in different tables within a single database, chances of repetition of data reduced to zero.

Data Integrity: Data integrity ensures that data stored in database is valid, accurate and consistent in database. There are many data constraints present in database that implements data integrity.

Data Consistency: it refers to same appearance of data across the database and for all the users viewing the database. Moreover, any changes made to data are instantly reflected to all its associated tables within a database and there is no data inconsistency.

Data Privacy: there are data security rules that when applied can ensure the accessibility scope of data at different levels to authentic users only.

Backup and Recovery: there is provision of automatic data backup that helps to restore database in case of system failure or crash and keep data safe and available.

Features of Database

A database can have more than one table.
Each table in a database contains information about one type of item.
Every record must be unique in a table.
Every table should have one or more fields designated as key.
Sequence of Columns (fields) of table can be in any order

Keys in Database

Primary key

It ensures each record is unique in a table.
It is also indexed in database, making it faster to search for records
A table can have only primary key
Primary key can not have NULL values

Composite Primary Key

When Primary key constraint is applied on or more columns then it is known as Composite primary key.

Foreign Key

The field which is Primary key in another related table.
Foreign key is used to establish relation between two tables.
A table can have more than Foreign key

What is RDBMS?

RDBMS stands for Relational Database Management System. It was developed by E.F. Codd at IBM 1970. It is used to organize collection of data as a relation where relation corresponds to a table of values. Often, data in a relational database is organized into tables.

SESSION 2: Create and Edit Table using Wizard & SQL Commands

Database Objects

Tables:

A table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows.
A table has a defined number of columns, but can have any number of rows.
Each row is identified by the values appearing in a particular column identified as a unique key index or the key field

Columns or Fields or Attributes

A column is a set of data values of a particular type, one for each row of the table.
The columns provide the structure according to which the rows are composed.
For example, Itemcode, Itemname are fields in a row

Rows or Records or Tuples

A row also called a Record or Tuple represents a single, data item in a table.

Creating database in Open Office

Start Open Office
Select the option Database
In database Wizard dialog, select ‘Create a new database’ and click Next and than click on Finish (with no change)
In Save As dialog, specify name for the database and click save
Database Window will be opened

What is Table?

Tables are the basic building blocks of a database which is used to store data in the database.

How many ways we can create table in Open Office Base?

We can create table in two ways

Create table using Wizard
Create table in Design View

Create Table using Table Wizard

Open your database
Click Tables → select Use Wizard to create table
From the dialog, click select Fields → Choose Category → Select the table from ‘Sample Tables’ drop down
Select the fields as per your choice and select arrow button —– to add predefined columns
Click on Next
Now select the filed form ‘selected field’ list and set its data type by selecting it from ‘Field Type’ drop down
Repeat step 6 for other fields
Click Next
Check ‘create Primary key’
Click on ‘Use an existing field as primary key and then select field name from from down
Click Next
Click Finish (make sure ‘Insert data immediately’ is selected )
Now datasheet window appears where you can enter records or close it.

Create table using Design View

Open your database
Click Tables → select ‘Create Table in Design View’
In table design window, type field name in ‘field name’ column and select its data type from ‘Field Type’ data type
Repeat step 3 for entering other fields
After entering all fieldname, select a field name and right click at left most side
Select ‘Primary Key’ from pop up
After specifying all, save the table by clicking File → Save
Specify table name and click OK

Data Types

Data types are used to determine which type of data we are going to store in the database.

Following tables shows different data types used in Open Office Base

Table Data View dialog box

We can enter records in a table using table data view.

SESSION 3: Perform Operations on Table

Inserting, Editing and deleting data in the table

Select the table → double click on it
Now table datasheet widow opens, here you can enter records, modify it or delete it.

Field Properties

Auto Value: it set to yes, field will get the auto numeric values
Length – By default length of the field is 10 but the size of the field can be set to maximum length.
Default Value – A default value can be set for a field if user don’t provide any value while entering the values in the table.
Format example – This property helps to set the format of the data entered in the field such as 91-222-333.
Entry Required – if set to yes then it will be must to insert the value in the field which means that field cannot be left blank.

Referential Integrity

Referential integrity is used to maintain accuracy and consistency of data in a relationship.

Referential integrity helps to avoid:

Adding records to a related table if there is no associated record available in the primary key table.
Changing values in a primary if any dependent records are present in associated table(s).
Deleting records from a primary key table if there are any matching related records available in associated table(s).

Relationships between Tables

A relationship refers to an association or connection between two or more tables. When you relate two tables, you don’t need to enter the same data in separate tables.

Relationships between tables help to:

Save time as there is no need to enter the same data in separate tables.
Reduce data-entry errors.
Summarize data from related tables.

Types of relationships

ONE to ONE: In this relationship, both the tables must have primary key columns.
ONE to MANY or MANY to ONE: In this relationship, one of the table must have primary key column
MANY to MANY: In this relationship, no table has the primary key column

Creating Relationship between tables

Open your database
Select Relationship option from Tools Menu
Select the table and click on add button to Add the tables in amongst which you want to create relationship
Close show table box
Now place the mouse over the common field which is primary in a table
Hold down the mouse button and drag it over same common field of another field
Release the mouse button. You will see a line appears between linked tables now.

How to remove relationship between tables

The relationships applied on the tables can be removed also with the help of Delete option. Right Click on the relationship thread and select Delete option

SESSION 4: Retrieving data using Query

What is query?

Query is an object in Open Office base which is used to search specific records from one or more table.
Query helps use to join records from different tables and filter that information

How many ways we can create query in Open Office Base?

We can create query in 3 ways in Open Office Base as given below:

Create query using Wizard
Create query in design view
Create query using SQL

Create query using Wizard

Open your database
Select query → create ‘Create Query using Wizard’
Select table from ‘tables’ drop down
Add fields from ‘Available fields’ list by clicking ‘>’ to ‘Fields in the Query’ list
Click on finish

Create Query using Design View

Open your database
Select Query → create query in design view
Select the table from show table box and add it in query
Now double click all the fields you want to display in query result
Set the condition at ‘criteria’ row for the field to be searched
Save and run query

What is SQL?

SQL is Structured Query language (pronounced as ‘sequel’) is computer language used to create manage database. We can create queries SQL to view or retrieve specific records from database.

SELECT statement

SELECT statement in SQL is used to retrieve records from one or more tables of a database. It is one of the most commonly used DML (data manipulation Language) command.

How to use Select statement to retrieve data from table

To view all fields:

SELECT * FROM <table name>;

To view specific fields:

SELECT <field(s)> FROM <table name>;

WHERE clause

We can use WHERE clause with SELECT statement to create query. WHERE clause is used to set criteria for the data to be retrieved from table.

Syntax:

SELECT <field name(s)> FROM <table name>

WHERE <criteria>;

ORDER by clause

It is used to arrange or sort data in ascending or descending order in resultset. It can be used with SELECT statement as given below:

SELECT <field(s)> FROM <tablename>

WHERE <criteria>

ORDER BY <field name> ASC/DESC;

Create Query using SQL

Open your database
Select Query → Create Query in SQL view
Now SQL editor opens, here you can type SQL Query using SELECT Command
Save and Run Query

Examples of SQL Query

Display iname and its price from item table.

SELECT iname, price FROM item;

Display items which rate is less than 100rs

SELECT * FROM item WHERE rate <100;

Display name, department and salary of all employees after incrementing salary by 1000.

SELECT ename, dept, salary + 1000 FROM employee;

Display records of those salesman who lives in Delhi and Mumbai from salesman table.

SELECT * FROM salesman WHERE city IN (‘Delhi’, Mumbai’)

Display students details from student table in ascending order of rollno.

SELECT * FROM student ORDER BY rollno;

Display all item details of patanjali brand which rate is 100.

SELECT * FROM item WHERE brand = ‘patanjali’ AND rate = 100;

UPDATE statement

Update statement is used for modifying records in a database.

The general syntax of the update statement is as follows:

UPDATE <table name>

SET = expression

WHERE <criteria>;

For example

Update SDetails set Location = ‘Bhubaneswar’ where Rollno = 14;

Will update location to Bhubneshwar of student havng rollno 14.

SESSION 5: Creating Forms and Reports Using Wizard

What is Form in Open Office Base?

A form provides the user a systematic way of storing information into the database.
It is an interface in a user specified layout that lets users to view, enter, and change data directly in database objects such as tables

CREATING Form using Wizard

Open your database
Select Form →Create form using Wizard
Select table or query name from ‘table or query’ drop down list
Add fields from ‘available fields’ to ‘fields in the query’ by clicking ‘>’. (To use all fields at once click ‘ >>’)
Click Next
Click Next again
Select a Controls Arrangement layout for your Form and click Next
Select the data entry model (mostly we use ‘the form is to be used for entering new data entry’)
Click Next and Select a Style for your form
Click Next and set the name of the form
Click Finish

What is Report in Open Office Base?

A report is an object of Open Office Base used for generating printout of records in organized manner.
Report can group and generate summary of available data in clear format.