Syllabus:

Database Concepts:- The concept of data base management system; Data field, records, and
files, Sorting and indexing data; Searching records. Designing queries, and reports(MS-
Access)

Concept and Features of DBMS:

➢ Data is defined as the collection of information and a database is referred to as

collection of related data. Databases management systems are designed so that they
contain related data, which can be used whenever there is a requirement for such data.
➢ Database Management System can be defined as a software that is used to enter, store,
manipulate and retrieve data that is organised in the form of information in the
databases.
➢ Database Management System, also known as DBMS, is a software that helps in
creation and maintenance of databases.
➢ The overall design of the database is known as the database schema. There are two
types of schema in a database namely, physical and logical. The physical schema is
concerned with the database design at the physical level and the logical schema is
about the database design on a logical level.

Features of a Database Management System :

Following are the features of a database management system:

1. Minimum repetition and redundancy: In a database there are high chances of data
duplication due to the presence of many users. DBMS resolves this issue by building a
repository where all the data are stored and can be accessed by multiple users.

2. Databases of big corporations or companies can only be managed by a database

management system. Database management system offers data protection and features like
recovery and backup for easy recording and retrieval of data.

3. Enhanced security: Database management systems offer enhanced security by restricting

public access. It can only be accessed by a database administrator or the concerned head of
the department.

4. Multiple user access: Database is designed in such a way that it can be accessed by
multiple users who can work on different sections of the databases at a time without
interrupting the flow of work.

5. Provides multiple views of the data: Database management system allows the users to
access multiple views of the data present in the database. For e.g: an HR personnel can check
the database for processing the data of the employee, while at the same time an employee can
use the database to access resources related to payslip, attendance, etc.
6. Permanent storage of data: Data stored in a database will remain stored permanently
until and unless it is deleted from the system manually. Even in cases of accidental deletion
of data, the database system has a robust recovery system which allows recovery of deleted
data.
Nature of Database Management System

The following lines describe the nature of the database management system:

1. Data is combined in the database to form operational units, which results in reduction of
duplication of data and improves access to all the data in the database.

2. As the database becomes more advanced, it enables addition of data and programs to the
system.

Objectives of Database Management System

Database management system has the following objectives:

1. Elimination of redundant data

2. Enabling data access easy for the user
3. Providing a source of mass storage of data
4. Allowing multiple users to access the database at the same time
5. Providing appropriate and quick response to user queries

Functions of Database Management System

Following are the functions of the database management system:

1. Provide a mechanism for storing, organising and retrieving data
2. Reducing redundancy of data
3. Providing facility for preventing unrestricted access to database by means of
passwords
4. Creating data and program independence such that one can be changed without
changing the other

Types of DBMS

There are four different types of DBMS which are mentioned below

1. Hierarchical database
2. Network Database
3. Relational Database
4. Object Oriented Database
Examples of Database Management System

Following are some of the popular database management systems in use:

1. MySQL
2. Oracle
3. MS-Access
4. SQLite

Fields, Records and Files :

Data is usually organized as collection of Fields, Records and Files. What are these terms?

Field :-

It is defined as a unit of meaningful information about an entity like date of flight, name of
passenger, address etc.

Record :-
It is a collection of units of information about a particular entity. Passenger of an airplane, an
employee of an organization, or an article sold from a store.
File :-

A collection of records involving a set of entities with certain aspects in common and
organized for some particular purpose is called a file. For example collection of records of all
passengers.
There are three file organizations to understand the relationship between Fields, Records and
Files-

• Sequential,
• Indexed Sequential and
• Relative.

Sequential File

➢ In a sequential file, records are stored one after another on a storage device such that
accessing any record can be done only after accessing all records stored before it.
➢ A sequential file is physically placed on a storage device by storing the sequence of
records in adjacent locations on a track. If the file is stored in a direct access storage
device like drum and the file is larger than the amount of space on a track, then the
records are placed on adjacent tracks. The basic operations to be performed on a
sequential file are read, write and update.
Indexed Sequential File
➢ A file organization where the records can be accessed directly as well as sequentially is
called indexed sequential file. The capability of directly accessing a record based on a key
can only be achieved if the external storage device supports this type of access i.e.
magnetic drums and disks.
➢ Index is a sequential file which contains index as its record. Index files contain two fields
for each file i.e. a Key field and a pointer pointing to some record in the main file. To find
a specific field in main file, index is searched for the key value required. The pointer
related to key field searches the record at the location it points to.

Relative Files

➢ A file organization where the records are ordered by their relative key is called relative
file. The relative key is record number that represents the record location relative to where
the file starts.
➢ If the first record in the file has a relative record number of 6, the hundrendth record has a
relative record number of 106. The relative files have the advantage of having records that
can be of fixed length or variable length.
➢ The relative files can have the storage of records sequentially, randomly, or dynamically.
If a relative files is accessed or stored sequentially, the ordering is meant to organize the
relative record numbers.
Sorting :

The arrangement of data in a preferred order is called sorting in the data structure. By sorting
data, it is easier to search through it quickly and easily. The simplest example of sorting is a
dictionary.

What is a Sorting Algorithm?

A sorting algorithm is just a series of orders or instructions. In this, an array is an input, on

which the sorting algorithm performs operations to give out a sorted array.

Example of sorting :

Let’s suppose you have an array of strings: [h,j,k,i,n,m,o,l]

Now, sorting would yield an output array in alphabetical order.

Output: [h,i,j,k,l,m,n,o]

Sorting Categories :

There are two different categories in sorting:

Internal sorting : If the input data is such that it can be adjusted in the main memory at once,
it is called internal sorting.

External sorting : If the input data is such that it cannot be adjusted in the memory entirely
at once, it needs to be stored in a hard disk, floppy disk, or any other storage device. This is
called external sorting.
Types of Sorting
Here are a few of the most common types of sorting algorithms.
1. Merge Sort

This algorithm works on splitting an array into two halves of comparable sizes. Each half is
then sorted and merged back together by using the merge () function.

Here’s how the algorithm works:

MergeSort(arr[], l, r)

If r > l

1. Divide the array into two equal halves by locating the middle point:
middle m = (l+r)/2

2. Use the mergeSort function to call for the first half:

Call mergeSort(arr, l, m)

3. Call mergeSort for the second half:

Call mergeSort(arr, m+1, r)

4. Use the merge () function to merge the two halves sorted in step 2 and 3:

Call merge(arr, l, m, r)
Check out the image below to get a clear picture of how this works.
2. Selection Sort

In this, at first, the smallest element is sent to the first position. Then, the next smallest
element is searched in the remaining array and is placed at the second position. This goes on
until the algorithm reaches the final element and places it in the right position.

3. Bubble Sort

It is the easiest and simplest of all the sorting algorithms. It works on the principle of
repeatedly swapping adjacent elements in case they are not in the right order. In simpler
terms, if the input is to be sorted in ascending order, the bubble sort will first compare the
first two elements in the array. In case the second one is smaller than the first, it will swap the
two, and move on to the next element, and so on.
4. Insertion Sort-

Insertion sort falls under one of the most popular sorting types in data structure. It is basically
an algorithm that helps to place an unsorted element at its suitable position in each iteration.
It’s similar to the way you sort your cards during a card game. The first card is usually
considered to be already sorted, and the next card that you pick up is then compared against
the first one. Based on the first card, you wither place the unsorted second card on the right or
left side of the former one. The insertion sort follows the same approach.

5. Quick Sort-

Also known as partition exchange sorting, quick sort is yet another very popular sorting types
in data structure that is based on partition. Using this particular algorithm, you pick on an
element, which is known as the pivot element, and then rearrange the rest of the elements
around the pivot element. It then further divides the array into two specific sub-arrays. Once
you have fixed the pivot element, then it automatically disintegrates the rest of the elements.
For example, elements that are lesser are placed on the left side of the pivot element, and
elements on the right side are usually the ones that are greater. This whole process continues
until only one element is left in the sub-array.

What is Indexing in DBMS?

Indexing is a technique for improving database performance by reducing the number of disk
accesses necessary when a query is run. An index is a form of data structure. It’s used to
swiftly identify and access data and information present in a database table.

Structure of Index

We can create indices using some columns of the database.

• The search key is the database’s first column, and it contains a duplicate or copy of
the table’s candidate key or primary key. The primary key values are saved in sorted
order so that the related data can be quickly accessible.
• The data reference is the database’s second column. It contains a group of pointers
that point to the disk block where the value of a specific key can be found.
Ordered Indices

To make searching easier and faster, the indices are frequently arranged/sorted. Ordered
indices are indices that have been sorted.

Example
Let’s say we have a table of employees with thousands of records, each of which is ten bytes
large. If their IDs begin with 1, 2, 3,…, etc., and we are looking for the student with ID-543:

• We must search the disk block from the beginning till it reaches 543 in the case of a
DB without an index. After reading 543*10=5430 bytes, the DBMS will read the
record.

• We will perform the search using indices in the case of an index, and the DBMS
would read the record after it reads 542*2 = 1084 bytes, which is significantly less
than the prior example.

Primary Index

• Primary indexing refers to the process of creating an index based on the table’s
primary key. These primary keys are specific to each record and establish a 1:1
relationship between them.

• The searching operation is fairly efficient because primary keys are stored in sorted
order.
• There are two types of primary indexes: dense indexes and sparse indexes.

Dense Index

Every search key value in the data file has an index record in the dense index. It speeds up the
search process. The total number of records present in the index table and the main table are
the same in this case. It requires extra space to hold the index record. A pointer to the actual
record on the disk and the search key are both included in the index records.
Sparse Index

Only a few items in the data file have index records. Each and every item points to a certain
block. Rather than pointing to each item in the main database, the index, in this case, points
to the records that are present in the main table that is in a gap.

Clustering Index

• An ordered data file can be defined as a clustered index. Non-primary key columns,
which may or may not be unique for each record, are sometimes used to build indices.

• In this situation, we’ll join two or more columns to acquire the unique value and
generate an index out of them to make it easier to find the record. A clustering index is
a name for this method.
• Records with comparable properties are grouped together, and indices for these
groups are constructed.

Example

Assume that each department in a corporation has numerous employees. Assume we utilise a
clustering index, in which all employees with the same Dept_ID are grouped together into a
single cluster, and index pointers refer to the cluster as a whole. Dept_Id is a non-unique key
in this case.
Secondary Index

• When using sparse indexing, the size of the mapping grows in sync with the size of
the table. These mappings are frequently stored in primary memory to speed up
address fetching. The secondary memory then searches the actual data using the
address obtained through mapping.
• Fetching the address becomes slower as the mapping size increases. The sparse index
will be ineffective in this scenario, so secondary indexing is used to solve this
problem.
• Another level of indexing is introduced in secondary indexing to reduce the size of the
mapping. The massive range for the columns is chosen first in this method, resulting
in a small mapping size at the first level.
• Each range is then subdivided into smaller groups. Because the first level’s mapping
is kept in primary memory, fetching the addresses is faster. The second-level mapping,
as well as the actual data, are kept in secondary memory (or hard disk).
Searching MS Access for a specific record

The simplest type of search looks for a specific record. To search for a record, you need to
know the data stored in at least one of its fields, such as a phone number or an email address.

The more information you already know, the more likely Access will find the one record you
want. If you search for all records that contain the first name Bill, Access could find dozens
of records. If you just search for all records that contain the first name Bill, the last
name Johnson, and a state address of Alaska, Access will likely find just the record you want.
To search for a specific record in an Access database table, follow these steps:

1. In the All Access Objects pane on the left of the screen, double-click the name of
the database table you want to search.

Access displays the Datasheet view of your database.

2. Click the Home tab.

3. In the Find group, click the Find icon.

The Find and Replace dialog box appears.

Search for a specific record in a database table.

4. Click in the Find What text box and type the data you know is stored in the record
you want to find.

For example, if you want to find the phone number of a person but you know only the
person’s last name, type the last name in the Find What text box.

1. Click the Look In list box and choose Current field or Current document (searches
in all fields).
2. (Optional) Click in the Match list box and choose one of the following:

o Any Part of Field: The Find What text can appear in any part of a field.

o Whole Field: The Find What text is the only text stored in a field.

o Start of Field: The Find What text can be only at the beginning of a field.

1. (Optional) Click in the Search list box and choose one of the following:

o Up: Searches from the record where the cursor appears, up to the beginning of
the database table

o Down: Searches from the record where the cursor appears, down to the end of
the database table

o All: Searches the entire database table

2. Click Find Next.

Access highlights the field where it finds the text you typed in Step 4.

3. Repeat Step 8 to search for more records that may contain the text you typed in Step 4

4. Click Cancel or the Close button.

Designing Queries (MS-Access) :

A query is a request for data results, and for action on data. You can use a query to answer a
simple question, to perform calculations, to combine data from different tables, or even to
add, change, or delete table data.

Create Select Query

• If you want to review data from only certain fields in a table, or review data from
multiple tables simultaneously or maybe just see the databased on certain criteria, you
can use the Select query.
• Let us now look into a simple example in which we will create a simple query which
will retrieve information from tblEmployees table.
Open the database and click on the Create tab.

Click Query Design.

In the Tables tab, on the Show Table dialog, double-click the tblEmployees table and
then Close the dialog box.
In the tblEmployees table, double-click all those fields which you want to see as result of the
query. Add these fields to the query design grid as shown in the following screenshot.

Now click Run on the Design tab, then click Run.

The query runs, and displays only data in those field which is specified in the query.

Designing Reports (MS-Access) :

• Reports offer a way to view, format, and summarize the information in your Microsoft
Access database. For example, you can create a simple report of phone numbers for all
your contacts.
• A report consists of information that is pulled from tables or queries, as well as
information that is stored with the report design, such as labels, headings, and graphics.
Example for creating a very simple report. For this, we need to go to the Create tab.
Before clicking on the Report button to create a basic report, make sure the proper query is
selected. In this case, qryCurrentProjects is selected in your navigation pane. Now click on
the Report button, which will generate a report based on that query.

You will see that the report is open in Layout view. This provides a quick way to adjust the
size or width of any of your fields that you see on the report. Let us now adjust the column
widths to make everything fit in a better way.
Scroll down and adjust the page control at the bottom.

This was a very quick way to create a very simple report. You could also make minor changes
and adjustments from the report design view.
Let us now change the Title of the report and give it another name.

Click on the save icon to save your report.

You will get the above dialog box.

Enter a name for your report and click Ok. If you want to view what this report will actually
look like, in Print Preview, you can go back to the View button and click on Print Preview to
see what this report would look like when printed either on paper or as a PDF.

Using the tools on the lower right-hand corner, you can zoom in or zoom out. You also have
some buttons on the Print Preview tab that appear automatically when you switch to Print
Preview. In the zoom section, you've got a view for one page, two pages; or if you have a
longer report, you can view four pages at once, eight pages or twelve pages.