SESSION 1: APPRECIATE THE CONCEPT

OF DATABASE MANAGEMENT SYSTEM

• A database is an organized collection of data. It is
considered as a container of information.
• In the manual system, you would maintain several files with
different bits of information while in the computerized
system you would use database programs.
• The software that is used to create, update and retrieve data is known
as database management system (DBMS). It facilitates planning and
maintenance of the database for the user. Some of the common
examples of DBMS are MS Access, Open Office or LibreOffice Base,
Oracle, Ingress, MySQL
Data Models
• A database can be designed in different ways depending on the data
being stored. This structure of database is known as data model that
describes the manner in which data will be stored and retrieved.
• There are different data models such as hierarchical data model,
network data model and relational data model.
Hierarchical Data Model
In this model the data is organized into a tree like
structure. The data is stored in the form of
records. A record is a collection of fields and its
data values. All these records are linked to each
other at various levels, thereby forming a
hierarchy
Relational Data Model
This data model is based on the principle of
setting relationships between two or more tables
of the same database. It is the most commonly
used database model.
Relational Database
Terminology
• Important Terms :
• 1) Primary Key : A primary key is a unique value that
identifies a row in a table. It helps the database to search
for a record.
• 2) Composite Primary Key : When primary key constraint is
applied on one or more columns then it is known as
Composite)
• 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.te
Primary Key.
RDBMS A relational database is a type of database. It uses a structure
that allows us to identify and access data in relation to another piece of
data in the database. Often, data in a relational database is organized into
tables.
• This foreign key can be used to set a relation between two tables. •
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. So in the table Student
Marks, Enrollment Number and Roll Number both are candidate keys.
• 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.
User Interface of Libre Office Base
Creating a Table
Once the database is created, we can start
working with objects of the database. First and
foremost is the creation of the table and then
entering data in the table. A table in LibreOffice
Base can be created using a wizard or using the
Design view.
Creating the table using wizard restricts us to use the same fields in the per-
designed tables. It may not solve the purpose in real scenario, as we may
require to create a table with the different fields for our purpose.
Creating tables using Design View gives us more flexibility to do our work.

To create table using Design View, in main Database window, click on the
option Create Table in Design View in the Tasks Pane and Table Design Window
will be opened.
The screen is broadly divided into two sections or horizontal panes. The upper
half consists of a grid structure with three columns Field Name, Field Type and
Description.
• Field Name – It is the name of the field assigned at the time of creation of table.
• Field Type – It allows to assign a data type to the field.
• Description – It allows to describe the purpose of the field. It is not the part of database table,
but it is meant for the user to understand the purpose of the field. We may or may not enter
field description.
 Setting the Primary Key
• every table must have a primary key that uniquely identifies a record
in the table. To make a particular field as the primary key, place the
mouse pointer before the field name, say Event Id in our above
example and right click. A pop up menu appears. 2023-24 166
Domestic Data entry operator – class X Select the Primary Key option
from pop up menu as shown in Fig. 9.16. A key icon appears before
the field name indicating that it is a primary key
 Saving a Table
• After creating the table you need to save it on the disk. To save the
table click on the save button or follow menu option File > Save As. A
Save As dialog box is displayed as shown in Fig. 9.17. Enter the name
of table and click on OK button

Tip: Press Ctrl + S to save the table or click Save icon from
the toolbar. If the table is being saved after making some
changes, simply select File-> Save option.
• Sorting Data in the Table Data in a table can be arranged in ascending
or descending order. This process of arranging the records in
particular order on any filed is called as sorting.
• Closing LibreOffice Base To close the application window of
LibreOffice Base, click on the File > Close or click on the cross (x)
button of the LibreOffice Base window.
Working with Multiple Tables
Editing and Deleting Tables
It is possible to copy, rename, edit and delete the table of database by right clicking
on the table name and using the appropriate option from the pop up menu.
Editing a table involves the task such as adding a new field or removing any field in
a table or to alter any of the field properties. To edit a table, open the Database
User Interface window. Selecting the Table object in Database Pane, the list of
tables will be displayed in the Table Area. Right click on the table name and select
Edit option from the pop menu.
Similarly to delete a table, right click on the table to be deleted. A confirmation box
to confirm for deletion of the table will be displayed. Click on Yes button to finally
delete the table.
To rename a table, right click on the table name in the Table Area and select
Rename.. option from the pop up menu. A cursor will appear. Type the new name
and press the Enter key.
 Relationships between Tables
• The most important prerequisite for setting a relationship is that
there must be a common field(s) between the two tables to create a
relationship.

The record pertaining to Admission

No in Student_Details table is
considered as the master record
while the corresponding record in
the related table (Student_Result) is
the transaction record. Therefore
Student_Details is called the master
table and Student_ Result is called
the transaction table.
Types of Relationships
• The type of relationship between any two tables in a database is
based on the number of records that are present in the transaction
table corresponding to the master table.
• Primarily three types of relationships can be set up between two
tables in a relational database These are: (i) One-to-One (ii) One-to-
many (iii) 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. This is because no two students will be given
same admission number. Similarly one person can have only one ticket to get
entry into a stadium to view the match. So relationship between Student and
Admission number and a person and his ticket number will be one-to-one
relationship (Fig. 10.5).
 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. So we can say that
there is a one to may relationship between
a teacher and class or teacher and student
or seller and products (Fig. 10.6).
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 teacher in a school may hold multiple
responsibilities such as class teacher, an activity
incharge or examination in-charge. For each
responsibility the teacher might be attached with
multiple students. So this type of relationship will be
many to many relationship. Similarly a shopkeeper may
sell multiple products to multiple customers. So many-
to-many relationship exists between a product and a
customer. Fig. 10.8 shows the corresponding records in
master table and transaction table are in same color.
Advantages of Relating Tables in a Database
There are various 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. We will study in detail
about referential integrity later in the chapter.
• Creating relationships between tables restricts the user from entering
invalid data in the referenced fields.
• Any updation in the master table is automatically reflected in the
transaction tables.
• table as primary key -referenced field.
• the values of CategoryID might be repeated. Here it is known as
referencing field.
LibreOffice Base gives us following four options to choose from to maintain
referential integrity.
• 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.
• Queries in Base
There are two ways to create a form:
• Using a wizard • Using the Design View
On the left of the Form Design window is the Forms Control toolbar
and at the bottom is the Records toolbar.
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. With the help of these buttons, we can traverse and view
the records in the file. As we move from one record to another, the
record number in the record text boxes changes.
Modifying a Form
It is possible to modify the form in any manner once it is created. The
modification can be to change the background color, font size and color
of the text or even positioning of various controls in the form.
Editing the labels
To edit the labels, either by changing the text or by changing the
formatting effects
Adding a Tool tip
A tool-tip is a small piece of text that is displayed when the mouse
pointer is placed on a particular control.