DBMS Notes Class 10 IT Code 402 [Exam Focused]

Get the ultimate study guide for Unit-3 Database Management System using Libre Office Base!
This comprehensive Database Management System Notes of Class 10 IT is designed for students who want to score a perfect 50/50 in theory. This DBMS Notes Class 10 offers clear definitions, practical steps, and easy explanations to help students score better and gain a competitive edge in exams.

Introduction to Database Management System

Data and Information

Data refers to raw facts and figures. These facts can be related to any person, place, activity, or thing. Data can exist in different forms such as text, images, audio, or video. Before it can be useful, it must be properly processed using a computer or any other system.

Information is the meaningful and organized form of data. When data is processed correctly, it becomes useful information. If the input data is incorrect, the resulting information will also be inaccurate.

Difference between Data and Information

Data	Information
Data refers to raw facts and figures.	Information refers to processed and meaningful data.
Data is unorganized and has no clear meaning on its own.	Information is organized and has clear meaning.
Data is not processed.	Information is obtained after processing data.
Examples: marks of students, names, numbers.	Examples: report card, pay slip, charts, invoices.

Database and DBMS

What is Database?

A database is a collection of logically related data that is stored in an organized manner. It allows users to store information in such a way that it can be easily accessed and managed. In a database, data can be added, modified, deleted, or viewed according to the user’s requirements.

What is Database Management System?

• It is application software that can be used to create and manage database efficiently.
• it enables 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.

Advantages of Database

• Organised Storage – Data is stored in an organised manner, which makes retrieval fast and accurate.
• Data Analysis – Helps in analysing data easily, such as finding maximum, minimum, average, or mean values.
• Data Sharing – The same database can be used by multiple applications, allowing efficient data sharing.
• Minimal Data Redundancy – Reduces duplication of data by avoiding repeated storage of the same data in multiple tables.
• Data Consistency – Ensures data remains uniform across all tables, reducing the chances of conflicting or incorrect data.
• Data Integrity – Ensures that data is valid, accurate, and consistent by using various constraints in the database.
• Data Privacy – Security rules ensure that only authorised users can access the data at different levels.
• Backup and Recovery – Provides automatic backup and recovery features to restore data in case of system failure or crash.
• Data Security – Unauthorized access to data is prevented by assigning passwords to users. Data can also be encrypted to make it unreadable for unauthorized users

Data Models

• This structure of a database is known as a data model, which describes how data is stored and retrieved.
• A data model defines the structure of data, the relationships among data, and the rules or constraints applied to it.
• Common data models include:
  • Hierarchical data model
  • Network data model
  • Relational data model

Hierarchical Data Model

In this model, data is organized in a tree-like structure and stored in the form of records. A record is a collection of fields and their values. All records are connected at different levels, forming a hierarchy. For example, a company’s data can be represented using a hierarchical structure where different types of information are arranged in levels.

Network Data Model

In this model, multiple records can be linked to a single master record. It is like an inverted tree where the master is at the bottom and related information is connected above it in branches. This model allows more flexible relationships between data compared to the hierarchical model.

Relational Data Model

This data model is based on establishing relationships between two or more tables in a database. It is the most widely used database model. In this model, data is stored in tables made up of rows and columns, and relationships are formed using common fields between tables.

Relational Database Model

The relational database model was proposed by E. F. Codd in 1970. It is the most commonly used database model. Data is stored in different tables consisting of rows and columns, and relationships between tables are created using common fields. That is why it is called a relational database model.

Relational Database Terminology

• Entity – A real-world object about which data is stored in a database.
• Table – A collection of related records arranged in rows and columns.
• Field (Column/Attribute) – The smallest unit of data in a database; represents a single type of information.
• Data Values – Raw data in numeric, character, or alphanumeric form.
• Record (Row) – A collection of data values related to one entity.
• Primary Key – A field that uniquely identifies each record in a table.
• Foreign Key – A field in one table that links it to the primary key of another table.
• Candidate Key – All possible fields that can become a primary key.
• Alternate Key – Candidate keys that are not selected as the primary key.

Objects of an RDBMS

• Table – The basic unit of a DBMS where data is stored in row and column format. Columns represent fields (attributes) and rows represent records.
• Forms – Used to enter data into tables in a simple and user-friendly way. They include elements like text boxes, labels, radio buttons, list boxes, and check boxes.
• Queries – Used to retrieve specific information from a database. It works like asking a question from the database based on certain conditions.
• Reports – Used to display the output of queries in a well-formatted and printable layout for better presentation.

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 cannot 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

Starting with LibreOffice Base

Create Database using Libre Office Base

• Step 1: Start LibreOffice Base from Windows or Linux (Start menu or application icon).
• Step 2: The Database Wizard will open, showing options to create or open a database.
• Step 3: Select “Create a new database” and click Next.
• Step 4: Choose “No, do not register the database” and proceed.
• Step 5: Select “Open the database for editing” (default option).
• Step 6: Click Finish to complete the database creation process.
• Step 7: A Save As dialog box will appear.
• Step 8: Choose the location (drive/folder) to save the database.
• Step 9: Enter a file name (e.g., Sports Day).
• Step 10: Click Save; the database will be saved with .odb extension.

Components of LibreOffice Base Interface

• Title Bar – Shows the name of the database and application. It also has minimize, maximize, and close buttons.
• Menu Bar – Located below the title bar. It contains menus like File, Edit, View, Insert, Tools, Window, and Help for different operations.
• Standard Toolbar – Placed below the menu bar. It provides quick access to commonly used tools.
• Status Bar – Located at the bottom of the window. It shows information about the current view of the database.
• Database Pane – Located on the left side. It contains objects like Tables, Forms, Queries, and Reports.

Creating a Table

We can create table in two ways

• Create table using Wizard
• Create table in Design View

Create Table using Table Wizard

• Step 1: Open your database.
• Step 2: Click on Tables → select Use Wizard to create table.
• Step 3: In the dialog box, select fields by choosing a category and then selecting a table from the Sample Tables drop-down list.
• Step 4: Select the required fields and click the arrow button (→) to add predefined columns.
• Step 5: Click Next.
• Step 6: Select a field from the Selected Fields list and set its data type using the Field Type drop-down menu.
• Step 7: Repeat Step 6 for the remaining fields.
• Step 8: Click Next.
• Step 9: Select Create Primary Key.
• Step 10: Choose Use an existing field as primary key and select the field name from the drop-down list.
• Step 11: Click Next.
• Step 12: Click Finish (ensure that Insert data immediately is selected).
• Step 13: The datasheet window will appear where you can enter records or close it.

Create table using Design View

• Step 1: Open your database.
• Step 2: Click on Tables → select Create Table in Design View.
• Step 3: In the Table Design window, enter the field name in the Field Name column and select its data type from the Data Type column.
• Step 4: Repeat Step 3 to add other fields.
• Step 5: After entering all field names, select a field and right-click on the left side row selector.
• Step 6: Select Primary Key from the pop-up menu.
• Step 7: Save the table by clicking File → Save.
• Step 8: Enter the 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

Category	Data type	Description
Numeric	Yes/No	Values as 0 or 1
	Integer	Ranges between -231 to 231 -1
	Big Integer	Ranges -263 to 263 -1
	Number	Maximum precision e (+/-)231
	Decimal	Maximum precision e (+/-)231
	Float	Ranges from 2-1074 to (2-22-52)*21023
Alphanumeric	Memo	Store up to max length indicated by user
	Char	Store fixed length string
	Varchar	Store variable length string
Date Time	Date	Store date only
	Time	Store time only
	Timestamp	Store date and time both
Others	Object	Store serialize object

Setting Primary Key

• Select the field (e.g. Account_No) in the table to set it as the primary key.
• Right-click and choose Primary Key from the menu.
• A key icon appears next to the field, indicating that it is set as the primary key.

Entering Data in a Table (Table Data View)

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

Inserting, Editing and deleting data in the table

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

Navigating through Table

• The black arrow (record pointer) shows the active record in the table.
• Navigation box at the bottom is used to move through records.
• Record Selector Box shows the current record number and allows direct entry of a record number.
• Navigation buttons help to move:
  • First record
  • Previous and Next records
  • Last record

Editing Data

• Click on the field value that needs to be edited.
• Enter the new value directly.
• An edit icon appears while changes are being made.
• Press Esc to cancel changes and restore original data.

Deleting Records

• Select the record to be deleted.
• Press Delete key or use Edit → Delete Record option.
• Alternatively, right-click and choose Delete Rows.
• A confirmation box appears; click Yes to delete or No to cancel.

Sorting Data

• Open the table and select the field to sort.
• Click Sort Ascending or Sort Descending icon on the toolbar.
• Data is arranged in the selected order.
• For multiple fields, use the Sort dialog box, set order, and click OK.

Closing LibreOffice Base

• Click File → Close or
• Click the (X) button on the window to exit the application.

Working with Multiple Tables

Editing and Deleting Table

Editing a Table

• Right-click on the table and select Edit option.
• The table opens in Design View.
• You can add or remove fields or change field properties.
• Make the required changes and save the table.
• Existing records in the table are not affected by these changes.

Deleting a Table

• Right-click on the table (e.g., Customer table).
• Select the Delete option.
• A confirmation box will appear.
• Click Yes to delete the table permanently.

Renaming a Table

• Right-click on the table name.
• Select Rename option.
• Enter the new name.
• Press Enter to save the new name.

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

One-to-One Relationship

• In this relationship, one record in the master table is linked to only one record in the transaction table.
• Each value in the master table has a single and unique corresponding value in the related table.
• No two records share the same matching value between the two tables.
• It is used when each entry has a unique relationship with only one other entry in another table.

One-to-Many Relationship (Pointers)

• In this relationship, one record in the master table is linked to multiple records in the transaction table.
• A single entry in the main table can have many related entries in the second table.
• It is one of the most commonly used relationships in databases.
• It is used when one entity is associated with many other entities

Many-to-Many Relationship (Pointers)

• In this relationship, multiple records in the master table are related to multiple records in the transaction table.
• One record in the first table can be linked with many records in the second table, and vice versa.
• It is used when both tables have multiple connections with each other.
• This relationship is common when data needs to be stored in both tables for multiple entries.

Advantages of Relating Tables in a Database

• A relationship can help prevent data redundancy
• It helps prevent missing data by keeping deleted data from getting out of synch. This is called referential integrity
• It restricts the user from entering invalid data in the referenced fields
• Any Update in the master table is automatically reflected in the transaction tables

Creating Relationship between tables

• Step 1: Open your database.
• Step 2: Select the Relationships option from the Tools menu.
• Step 3: Select the required tables and click the Add button to add the tables between which you want to create a relationship.
• Step 4: Close the Show Table dialog box.
• Step 5: Place the mouse pointer on the common field (primary key) in one table.
• Step 6: Hold the mouse button and drag it to the same common field in the other table.
• Step 7: Release the mouse button. A line will appear between the linked tables, showing the relationship.

Referential Integrity

• Referential integrity ensures that no unmatched foreign key values exist in the database.
• A record in the transaction table must have a matching record in the master table.
• If a record is deleted or not present in the master table, related records should not exist in the transaction table.
• It maintains consistency between related tables.
• DBMS allows only those values in the transaction table that already exist in the master table.

---

Options to Maintain Referential Integrity (LibreOffice Base)

• No Action – Prevents updating or deleting a master record if related records exist in the transaction table.
• Update Cascade – Changes made in the master table are automatically updated in related records of the transaction table.
• Set NULL – Sets related foreign key values to NULL when the master record is updated or deleted.
• Set Default – Assigns a default value to related fields when the master record is updated or deleted.

Setting Relationship Properties

• Double-click the relationship line between the two tables.
• The Relations dialog box will open.
• Select the required referential integrity option.
• Click OK to apply the settings.

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

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:

1. Create query using Wizard
2. Create query in design view
3. Create query using SQL

Create query using Wizard

• Step 1: Open the database.
• Step 2: Click on Queries in the Database Pane.
• Step 3: Select Use Wizard to Create Query from the Tasks Area.
• Step 4: Choose the required table and select the necessary fields.
• Step 5: Move the selected fields to the query list.
• Step 6: Click Next and set sorting order if required.
• Step 7: Define the required search conditions (criteria) for filtering records.
• Step 8: Skip numerical calculation steps if not needed and proceed.
• Step 9: Assign alias names to fields if required.
• Step 10: Review the query summary and provide a query name.
• Step 11: Click Finish to create and display the query results.

Create Query using Design View

• Step 1: Click on the Queries icon in the Database Window.
• Step 2: Select Create Query in Design View from the Tasks Pane.
• Step 3: In the Add Table or Query dialog box, select the required table and click Add.
• Step 4: Add all required tables in the same way.
• Step 5: Click Close to close the Add Table or Query dialog box.
• Step 6: Select required fields from the tables and add them to the design grid.
• Step 7: Set Alias names if required in the Alias row.
• Step 8: Set sorting order (ascending or descending) in the Sort row if needed.
• Step 9: Click Run Query or press F5 to execute the query.
• Step 10: Click Save to save the query.
• Step 11: Enter a query name in the Save As dialog box and click OK.
• Step 12: To view results again, double-click the query name.
• Step 13: Close the Query window using the close button

Editing a Query (Pointers)

• Click on the Query icon to view the list of created queries.
• Right-click on the query name in the Objects Area.
• Select the Edit option from the menu.
• The Query Design window will open.
• Apply conditions in the Criterion row (e.g., filtering specific categories or values).
• Use relational operators like <, >, <=, >=, !=, = to set conditions.
• Save and run the query to view the updated results.

Working with Numerical Data in Query

• Open Query Design View and add required tables.
• Add fields such as Category Name and Points.
• Use the Function row for calculations.
• Select Group to group data by a field.
• Select functions like Average, Sum, Count, Min, Max for calculations.
• Press F5 to run the query and view results.
• Conditions can also be added in the Criterion row to filter grouped data.

Forms and Reports

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
• A form is a database object that provides a user-friendly interface for entering and viewing data.
• It acts as the front end for data entry and data modification.
• Data in a form is displayed in a well-designed format instead of rows and columns.
• A form contains field controls arranged in a structured layout.
• Each field control includes a label and a text box for entering values.
• Labels describe the type of data to be entered in each field.
• Forms may also include titles, headings, images, list boxes, and radio buttons.
• Forms can be created using a Wizard or Design View.

Creating Form using Wizard

• Step 1: Open the database and click on the Forms icon in the Database Pane.
• Step 2: Select Use Wizard to Create Form from the Tasks Pane.
• Step 3: Choose the required table from the list.
• Step 4: Select the required fields from Available Fields and move them to the form.
• Step 5: Click Next and decide whether to add a subform (skip if not required).
• Step 6: Select the layout style for arranging fields in the form.
• Step 7: Choose whether the form will be used for data display, data entry, or both.
• Step 8: Apply required styles like background color and border type.
• Step 9: Enter a name for the form.
• Step 10: Choose whether to open the form for use or modify it later.
• Step 11: Click Finish to create the form and display it.

Forms Control Toolbar

• The Forms Control Toolbar contains tools used to add and edit different controls in a form.
• It helps in designing the form by adding elements like labels, text boxes, buttons, etc.

Record Toolbar

• The Records Toolbar provides navigation buttons to move through records in a table.
• It allows viewing different records and shows the current record number.
• It also provides options to add a new record, save a record, and delete a record.
• As we move between records, the record number changes automatically.

Modifying a Form

• A form can be modified after creation by changing its design or properties.
• Modifications include changing background color, font style, and control positions.

Changing Background Color

• Open the form in Design View.
• Right-click on the form and select Page Style.
• Choose the desired color from the palette.
• Click OK to apply changes.

Editing Labels

• Select the label using Ctrl + Click.
• Right-click and open Control Properties.
• Change the label text or formatting properties.
• Close the dialog box to apply changes.

Adding Text (Labels) in Form

• Select the Label tool from Forms Control Toolbar.
• Draw a label on the form.
• Enter the required text in properties.
• Set font style, size, and formatting.

Adding a New Record using Form

• Switch to Form View from Design Mode.
• Click New Record button on Records Toolbar.
• Enter data in the blank form.
• Click Save Record to store it.

Deleting a Record

• Navigate to the required record using navigation buttons.
• Click Delete Record button.
• Confirm deletion if prompted.

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.

CREATING Report using Wizard

• Step 1: Open your database.
• Step 2: Select Report → Create report using Wizard.
• Step 3: Select the table or query name from the “Table or Query” drop-down list.
• Step 4: Add fields from “Available Fields” to “Fields in Report” by clicking the “>” button. To select all fields at once, click the “>>” button.
• Step 5: Click Next.
• Step 6: Click Next again.
• Step 7: If you want to group records, select the field from the “Fields” list based on which the data will be grouped; otherwise, click Next directly.
• Step 8: If you want to sort the data in the report, select the field name from the “Sort By” drop-down list, then click Next.
• Step 9: Select the layout of the report from the “Layout of Data” list.
• Step 10: Select an option from the “Layout of Header and Footer” section as per your requirement.
• Step 11: Select the orientation if required.
• Step 12: Click Next and enter a title for the report.
• Step 13: Click Finish.

Inserting Other Controls in Report (Steps)

• Step 1: Open the report and right-click on the report name, then select Edit.
• Step 2: The Report Builder window will open. Use the Report Controls toolbar to add different elements.

Inserting Title and Heading

• Step 1: Select the Label tool from the Report Controls toolbar.
• Step 2: Click and drag on the report area to create a label box.
• Step 3: Double-click the label to open the Properties dialog box.
• Step 4: Enter the title in the Label property (for example, report title) and set font style and size as required.
• Step 5: Close the Properties dialog box. The formatted title will appear on the report

Inserting Date and Time

• Step 1: Click inside the Page Header area to activate it.
• Step 2: Select Insert → Date and Time option.
• Step 3: Choose the required format for date and time and click OK.
• Step 4: The date will appear in the header; you can drag and adjust its position if needed.