Session 1: APPRECIATE THE CONCEPT OF DATABASE MANAGEMENT SYSTEM.

Database: A Database is an organized collection of data, it can be visualized as a container

of information.
Database Management System: DBMS is a software package with computer programs
that control the creation, maintenance, and use of a database. A database is a collection
of data and DBMS allows different user application programs to concurrently access the
same database. Examples of DBMS – Oracle, MySQL, FoxPro, IBM DB2, Microsoft SQL
Server, OpenOffice.org Base, and Microsoft Access.
Organization of Data: Data can be organized in two ways –
1. Flat File: Data is stored in a single table. This is usually suitable for less amount of
data.
2. Relational: Data is stored in multiples tables which are linked by a common field.
This is suitable for medium to large amount of data.
Database Servers: These are dedicated computer systems that hold the databases and
run only the DBMS and related software. Databases are available on database servers and
are usually accessed through a command line or Graphic User Interface tools [called
Frontend].
Advantages of Database:
1) Reduces Data Redundancy: Database reduces data redundancy. Database reduces
Duplication of data, in fact, there is no chance to encounter duplicate data in a database.
2) Sharing of Data: The users of the database can share the data among themselves.
3) Data Integrity: Data integrity means that the data in the database is accurate and
consistent.
4) Data Security: Database provides data security as only authorized users are allowed to
access the database and their identities are authenticated by using a username and
password.
5) Privacy: Only authorized users can access a database according to the database privacy
constraints.
6) Backup and Recovery: DBMS automatically takes care of backup and recovery. In case
of a crash or system failure, it gets restored to its previous condition.
Features of Database:
 • The database has one or more tables.
• Each table has information about one type of item.
• Every table in a database has a key field that makes sure that there are unique
values throughout the database.
Relational Database Terminology
• Entity – It is a real world object about which information is to be stored in a database.
For example, if we want to store information about an entity Student in a school, then we
need to have his admission number, roll number, name, father’s name, date of birth, etc.
These details associated with the entity are called attributes. Each entity is a collection of
these attributes associated with it. So roll number, name, admission number, etc., are
attributes associated with the entity student. These attributes are represented in the
form of columns.
• Table – A table is a collection of logically related records. It is organised as a set of
columns, and can have any number of rows. For example, the Employee table can have
columns, namely name, designation, department and have records or rows having data of
100 employees.
• Field or Columns or attributes – A field is the smallest entity in the database. A
collection of fields make a record, a collection of records make a table and a collection of
tables make a database. Fields are individual record characteristics and are presented as
columns within a table. Data values are stored in a database as fields. A field holds the
data values of one type of data for several persons. For example, in the Employee table
the field “Emp Name” will hold the names of employees of an organisation.
• Data Values – Data values are the raw data represented in numeric, character or Keys in
a Database: alphanumeric form. Examples of data values are ‘Abhinav Bindra’, ‘26’
‘shooting’, “Chandigarh”, “10-12-2018”, etc
1) Primary Key: A primary key is a unique value that identifies a row in a table. Primary
Key helps the database to quickly search for a record.
2) Composite Primary Key: When a primary key constraint is applied on one or more
columns then it is known as Composite Primary Key.
3) Foreign key: The foreign key identifies a column or set of columns in one (referencing)
table that refers to a column or set of columns in another (referenced) table.
• Candidate Key – All the field values that are eligible to be the primary key are the
candidate keys for that table. Such fields can neither be left blank nor can have duplicate
values.
• Alternate Key – Out of the candidate keys, one or two are made as primary keys. The
others are the alternate keys. Hence, if Roll Number is made as the primary key,
Admission Number is the Alternate key.
SESSION-2 : CREATE AND EDIT TABLES USING WIZARD & SQL COMMANDS
Database Objects:
1) Table: It is a collection of data elements (values). It consists of vertical columns and
horizontal rows where we put the required information.
2) Columns or Fields or Attributes: It is a set of data values of a particularly simple type,
one for each row of the table.
3) Rows or Records or Tuples: It represents a single data item in a table. Every row in the
table has the same structure.
Data Types: These are used to identify which type of data we are going to store in the
database. Data types in the OpenOffice base are broadly classified into five categories.
1. Numeric Types
2. Alphanumeric Types
3. Binary Types
4. Date time
5. Other Variable types
Numeric Types: Used for storing information such as mobile number, roll number, door
number, etc.
Alphanumeric Types: Used to store information that has alphabets as well as numbers for
example address, book summary field, etc.
Binary Types: Used for storing data in binary formats. This can be used for storing photos,
music files, etc.
DATE TIME: Used for storing information such as date of birth, date of admission, date of
product sale, etc.
SESSION 3: PERFORM OPERATIONS ON TABLE
In the OpenOffice Base, we store data in tables that can be inserted, modified, and
removed using suitable options.
Inserting Data In The Table:
Steps to insert data in a table –
1) Select the table, Double click on it.
2) The table will open in Datasheet View.
3) Insert the required number of records in Datasheet View.
Editing Records In The Table:
Steps to Edit Records in the table –
1) Select the table, Double click on it.
2) The table will open in Datasheet View.
3) Edit the required record in Datasheet View.
Deleting Records From The Table
Steps to remove data from the table –
1) Select the table, Double click on it.
2) The table will open in Datasheet View.
3) Select the data > right click on selected data > select the Delete option
Field Properties :
Steps to set field properties –
1. Select the table > Right-click > Select the option Edit > the table will open in Design
View.
There are various properties of fields according to the data type set for each field in the
design view.
The properties of numeric type data are given below :
1) AutoValue – if set to yes then the field will get the auto numeric values.
2) AutoValue – if set to no then the field will not get the auto numeric values.
3) Length – By default length of the field is 10 but the size of the field can be set to
maximum length.
4) Default Value – A default value can be set for a field if the user doesn’t provide any
value while entering the values in the table.
5) Format – This property helps to set the format of the data entered in the field such as
91-222-333.
Database Management System Class 10 Notes 2023-24.
The properties of character type data are shown below :
1) Entry Required – if set to yes then it will be a must to insert the value in the field.
2) Length – By default length of the field is 10 but the size of the field can be set to
maximum length.
3) Default Value – A default value can be set for a field if the user doesn’t provide any
value while entering the values in the table.
4) Format – This property helps to set the format of the data entered in the field such as
91-222-333.
Sorting Data :
Sorting means arranging the data in either ascending order or descending order.
Referential Integrity :
Referential integrity is used to maintain the accuracy and consistency of data in a
relationship. In OpenOffice Base, data can be linked between two or more tables with the
help of primary key and foreign key
Referential integrity helps to avoid:
1) Adding records to a related table if there is no associated record available in the
primary key table.
2) Changing values in a primary if any dependent records are present in the associated
table(s).
3) Deleting records from a primary key table if there are any matching related records
available in the associated table(s).
LibreOffice Base gives us following four options to choose from to maintain referential
integrity in such cases.
• No action – This is the default option. This option states that a user should not be
allowed to update or delete any record in the master table if any related record exists in
the transaction table.
• Update cascade – This option allows the user to delete or update the referenced field
but along with it all the related records in any of the transaction tables will also be
deleted or updated.
• Set NULL – This option assigns NULL value to all the related fields if the master record is
deleted or updated.
• Set default – This option assigns any fixed default value to all the related fields if the
master record is deleted or updated.
Creating and Editing Relationships between Tables :
A relationship refers to an association or connection between two or more tables.
Relationships between tables help to:
1. Save time as there is no need to enter the same data in separate tables.
2. Reduce data-entry errors.
3. Summarize data from related tables.
You can create a relationship between any two tables by selecting the Relationships
option from the Tools menu.
There are three types of relationships that can be created in tables:
1) ONE to ONE Relationship: In this relationship, both the tables must have primary key
columns.
2) One to Many Relationship: In this relationship, one of the tables must have a primary
key column.
3) Many to Many Relationship: In this relationship, no tables have the primary key
column.
Remove the Relationships
The relationships applied on the tables can be removed also with the help of the Delete
option. Right, Click on the relationship thread and select the Delete option.
SESSION 4: RETRIEVE DATA USING QUERY
A query is one of the most important feature of any DBMS. Using a query, we can retrieve
and display data from one or more tables in a database. This is done by giving specific
search criteria to the DBMS so that we are able to view the exact information that we
want. LibreOffice Base allows us to create a query and even save it as an object in a
database. This helps us to run the query multiple times as and when required.
Queries are commands that are used to define the data structure and also to manipulate
the data in the database. A query helps us join information from different tables and filter
that information
There are two types of languages:-
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
Common DDL Statements are:-
1. Create :- This satement is used to create database or tables.
2. Alter :- This satement is used to modify structure of table.
3. Drop :- This satement is used to delete database objects.
Common DML Statements are:-
1. SELECT :- This satement is used to retrieves information from the database.
2. INSERT :- This satement is used to insert new record into the database.
3. DELETE :- This satement is used for deletion of information in the database.
4. UPDATE :- This satement is used for modification of information in the database
Select Statement :
A SELECT statement retrieves zero or more rows from one or more tables. SELECT is the
most commonly used Data Manipulation Language(DML) command. To retrieve all the
columns in a table the syntax is SELECT * FROM <Table Name>;
For example, if you want to display all the data from table emp (short form of employee),
the command is
Select * from emp;
The SELECT statement has many optional clauses:
1. WHERE specifies which rows to retrieve.
 2. ORDER BY specifies an order in which to return the rows.
for example Select * from emp where name = “Neha”;
The above query will show the result of a particular employee named “Neha”.
Select * from emp order by Salary;
The above query will show all the records of table emp according to ascending order of
column Salary.
SESSION 5: CREATE FORMS AND REPORTS USING WIZARD
FORM: 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.
There are two ways to create a form: • Using a wizard • Using the Design View
Creating a Form Using a Wizard is the simplest way to create a form. To explain how to
create a form using a wizard, we will get back to the Sports Day database created in the
previous chapters. To create a form using wizard, follow the following steps.
Creating Form Using Wizard: Steps To Create Form Using Wizard are :
1) Click Use Wizard to Create Form option under Tasks group. The Form Wizard dialog box
appears.
2) Select selective fields to be sent onto the form by selecting the field name and clicking
the > button and clicking Next.
3) Select the option Add Subform if you need to insert the contents in the table in a
separate form and click Next.
4) Arrange selected fields in a form and click Next.
5) Select the data entry mode and click Next.
6) Specify the styles to be used in the form and click Next.
7) Specify the name of the form. Click Finish.
REPORT: 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. A report
helps to display the data in a summarized manner. It is used to generate the overall work
outcome in a clear format.
Creating Reports using the wizard: Steps To Create Report Using Wizard are :
1) Click on Use Wizard to Create Report… option available under Tasks.
2) Select all the table fields by selecting the >> button.
3) Redefine the label of the fields in the reports or else you can set the default name and
click Next.
4) Define grouping for the fields of the table if required and click Next
5) Sort the field in the report by selecting the appropriate field name and sorting
method(if required) and click Next.
6) Select the layout of the report and click Next.
7) Define a name for the report and click Finish.