Module 3

Database Management System

Introduction to Data and Information
Data:
Data is derived from the Latin word “datum” which elaborates “something given”, so data can
be defined as crude and unordered facts that need to be processed to make it organized. Although
data can be present everywhere in our daily life, it is our responsibility how to collect the data
from the different corners and then process the data to get some useful insight from it which can
be useful for our particular use. Data can be anything it can be in the form of symbols, characters,
numbers, images, etc. Data is always interpreted by humans or machines to make it organized in
particular order to derive its meaning.
Information:
The raw data is collected, after processing this raw data the outcome is information. This
information can be defined as when the data is processed, organized, and presented in a specific
context to serve its use is called information. The information doesn’t have any existence without
data, mostly information have measuring unit like quantity, time, etc. There are also a lot of
differences between data and information. For information to be useful, the process data has the
following characteristics which are:
Time – Information should be available at any point in time whenever it is required.
Accuracy – Information should be actual and organized only then it can serve its purpose.
Completeness – Information should be finite and consistent.
Some examples of information:
Information about transportation systems such as train schedules.
Geographical information such as direction.
Pay slips.
Bank passbook.
Printed documents.
A database is an organized collection of structured information, or data, typically stored
electronically in a computer system. A database is usually controlled by a database management
system (DBMS). Together, the data and the DBMS, along with the applications that are associated
with them, are referred to as a database system, often shortened to just database.
Data within the most common types of databases in operation today is typically modeled in rows
and columns in a series of tables to make processing and data querying efficient. The data can then
be easily accessed, managed, modified, updated, controlled, and organized. Most databases use
structured query language (SQL) for writing and querying data.
What is a Database Model?
A Database Model is a type of data model that defines a Database’s logical structure. It
determines how data can be stored, organized, and manipulated in the first place. The Relational
Model, which uses a table-based format, is the most common Database Model. It demonstrates
how data is organized and the various types of relationships that exist between them.
Types of Database Models
1. Hierarchical Databases
It’s one of IBM’s first types of Database Models for information management. The data is
organized in a tree-like structure in a Hierarchical Database Model.
Nowadays, these types of Database models are uncommon. It has nodes for records and branches
for fields. A hierarchical Database is exemplified by the Windows registry in Windows XP whose
configuration options are saved as node-based tree structures.
The diagram below depicts a generalized Hierarchical Database Model (data represented or stored
in the root node, parent node, and child node).

Hierarchical Database Example

Do note how Departments and Administration are entirely unlike each other and yet fall under
the domain of a university. They are elements that form this hierarchy.
Another perspective advises visualizing the data being organized in a parent-child relationship,
which upon addition of multiple data elements would resemble a tree. The child records are
linked to the parent record using a field, and so the parent record is allowed multiple child
records. However, vice versa is not possible.
Notice that due to such a structure, hierarchical databases are not easily salable; the addition of
data elements requires a lengthy traversal through the database.
 2. Network Databases
In Layman’s terms, a network database is a hierarchical database, but with a major tweak. The
child records are given the freedom to associate with multiple parent records. As a result, a
network or net of database files linked with multiple threads is observed. Notice how the Student,
Faculty, and Resources elements each have two-parent records, which are Departments and
Clubs.
Entity Relationship Database Models
Entity Relationship Database Model is one of the types of Database models that is similar to the
network model, it captures relationships between real-world entities, but it isn’t as closely linked
to the Database’s physical structure. It’s more commonly used to conceptually design a Database.
The people, places, and things about which data points are stored are referred to as entities, and
each of them has specific attributes that make up their domain. The cardinality of entities, or the
relationships between them, is also mapped.

Network Database Example

Certainly, a complex framework, network databases are more capable of representing two-
directional relationships. Also, conceptual simplicity favors the utilization of a simpler database
management language.
The disadvantage lies in the inability to alter the structure due to its complexity and also in it
being highly structurally dependent.

3. Relational Databases
A Relational Database management system refers to the various software systems used to
maintain Relational Databases (RDBMS). The data in this type of Database Model is organized in
two-dimensional tables with rows and columns, and the relationship is maintained by storing a
common field. There are three main parts to it.
Three key terms, relations, attributes, and domains, are frequently used in Relational Models. A
table with rows and columns is what a Relation is. In relational databases, Attributes are the
defining characteristics or properties that define all items belonging to a particular category and
are applied to all cells in a column. The Domain is nothing more than the set of values that the
attributes can take. The relational Database Model is depicted in the following diagram.
Parameters in Relational Model
Tuple: A tuple is a single row in a table.
Cardinality Of a Relation: The cardinality of a relationship is determined by the number of tuples
in it. The relation has a cardinality of 4 in this case.
Degree Of a Relation: Each tuple column is referred to as an attribute. The degree of a relationship
is determined by the number of attributes in it.

4. Entity Relationship Database Models

Entity Relationship Database Model is one of the types of Database models that is similar to the
network model, it captures relationships between real-world entities, but it isn’t as closely linked
to the Database’s physical structure. It’s more commonly used to conceptually design a Database.
The people, places, and things about which data points are stored are referred to as entities, and
each of them has specific attributes that make up their domain. The cardinality of entities, or the
relationships between them, is also mapped.
Introduction of DBMS (Database Management System)

A Database Management System (DBMS) is a software system that is designed to manage and
organize data in a structured manner. It allows users to create, modify, and query a database, as
well as manage the security and access controls for that database.
The DBMS serves as the intermediary between the user and the database. The database structure
itself is stored as a collection of files, So, we can access the data in those files through the DBMS.
The DBMS receives all application requests and translates them into the complex operations
required to fulfill those requests. The DBMS hides much of the database’s internal complexity
from the application programs and users.
Advantages of DBMS:

Improved Data Sharing: DBMS enables data sharing among multiple users and applications.
Multiple users can access the same data simultaneously, without interfering with each other's
work. This improves collaboration and productivity, as well as reduces the risk of data
duplication.

Data Integration: DBMS allows the integration of data from multiple sources. This means that
data can be collected from various systems and combined into a single database, which makes it
easier to access and analyze.

Data Security: DBMS provides various security features such as access control, authentication,
and encryption to safeguard data from unauthorized access and prevent data loss or corruption.

Data Consistency: DBMS enforces consistency in data by ensuring that data is accurate,
complete, and up-to-date. This helps to avoid data inconsistencies and errors that can result in
significant consequences for an organization.

Data Integrity: DBMS ensures that data is stored and retrieved without any loss or corruption. It
provides mechanisms such as transaction management and recovery, which guarantees data
integrity even in case of system failures.
Disadvantages of DBMS:

High Cost: Implementing a DBMS can be expensive due to the cost of licensing, hardware, and
maintenance. This cost can be particularly significant for smaller organizations.

Complexity: DBMS is a complex software that requires a significant amount of technical

expertise to install, configure, and maintain. This complexity can lead to increased maintenance
costs and technical difficulties.

System Overhead: DBMS requires system resources such as memory, CPU, and disk space,
which can lead to system overhead and reduced system performance.

Data Dependency: DBMS stores data in a particular format, which can create data dependency
issues. If the format changes, it can affect the application that uses the data, leading to additional
maintenance and development costs.

Single Point of Failure: DBMS represents a single point of failure for an organization's data. If
the DBMS fails, it can lead to significant data loss and downtime, which can have a severe
impact on the business.

What is File Management System?

A file management system handles how to read and write data to the hard disk. When installing
the operating system, the file system also installs to the computer. For examples, OS such as Linux
and Windows provide files systems. It stores data to the hard disk and storing and retrieving data
occurs through this file management system.

Difference Between DBMS and File Management System

DBMS vs File Management System

DBMS is a system software for creating and A file management system is a
managing databases that provides a systematic software that manages data files in
way to create, retrieve, update and manage data. a computer system.

Data Redundancy

In a file management system, data

Data redundancy is low in a DBMS.
redundancy is high.
 Consistency

Data consistency is low in the file

In DBMS, the data consistency is high.
management system.

Data Sharing

Sharing data is harder in file

Sharing data is easier in DBMS.
management system.

Integrity

In file management system, the

Data integrity is high in DBMS.
data integrity is low.

Operations

Updating, searching, retrieving

Updating, searching, retrieving data is easier in
data is harder in a file
DBMS because of queries.
management system.

Security

Data is not very secure in file

In DBMS, the data is more secure.
management system.

Backup and Recovery Process

Backup and recovery process is complex in a Backup and recovery process is

DBMS. simple in a file system.

Number of Users

File management system is

DBMS is suitable for large organizations to
suitable for small organizations or
support multiple users.
single users.

What is Data Mining?

In the process of data mining, data is carefully extracted and analysed to fetch nothing other than
useful information. Here, all the hidden patterns are researched from the dataset, thereby
predicting future behaviour. Besides, it can seamlessly indicate and discover unique relationships
through the data.
On the other hand, data mining makes the best use of artificial intelligence, statistics, machine
learning systems, databases, etc. It is used for figuring out the hidden patterns within the data.
Additionally, it also supports all business-related queries that take loads of time to resolve.
Features of Data Mining

Some of the unique features of data mining are:

 It is capable of predicting future results

 It can efficiently handle large datasets and databases
 It can seamlessly utilise the automated discovery of patterns
 It has the potential to create actionable insights, etc

What is Data Warehousing?

A data warehouse is nothing but an electronic storage that stores gigantic amounts of business
information. It is exquisitely designed for both query and analysis rather than processing
transactions.
Data warehousing is a unique technique that helps collect and manage data from various sources.
Furthermore, these data provide meaningful and valuable business insights. It is basically a blend
of components and technologies that enables strategic usage of data.
In a nutshell, data warehousing is a process of transforming data into information, thereby
making it widely available for analysis. Now, let us discuss the important differences between
data mining and data warehousing in detail.

Features of Data Warehouses

Here is a comprehensive listing of the most common features of data warehouses:

1. Unified
Numerous heterogeneous sources are compiled together to formulate a data warehouse
like social databases, level documents, etc.
2. Subject Oriented
It offers the most important data regarding particular subjects like products, suppliers,
customers, promotions, etc. It handles data analysis and modelling while assisting
organisations in making data-driven decisions
3. Nonvolatile
The previous data remains intact whenever new data is added to the data warehouse.
Since the operational database and data warehouse are kept separately, changes within
the operational database aren’t shown.
 4. Time-Variant
Data collected within the data warehouse is seamlessly identified within a particular
period.

Difference Between Data Mining and Data Warehousing

Data Mining Data Warehousing

This procedure involves analysing data
It is exquisitely designed for analytical analysis
patterns
Regular data analysis Periodical data storage
Uses pattern recognition logic to identify
Extracts and stores data to enable easy reporting
patterns
It is carried out by business users with the
It is carried out by engineers
help of engineers
It helps in extracting data from large data sets It pools all the relevant data together
Statistics, AI, Machine Learning, and
Integrated, subject-oriented, non-volatile, and
Databases are used in data mining
time-varying constitute data warehouses
technologies
Pattern recognition logic is used for It involves extracting and storing data in perfect
determining patterns order to make efficient reporting
Employs pattern recognition tools to help Extracts are stores data in an orderly format,
identify the access patterns thereby making reporting faster and easier
It helps in creating suggestive patterns of key When connected with operational business
parameters systems like CRM, it deliberately adds value to it

Application of DBMS
There are different fields where a database management system is utilized. Following are a few
applications which utilize the information base administration framework –

1. Railway Reservation System –

In the rail route reservation framework, the information base is needed to store the
record or information of ticket appointments, status about train’s appearance, and
flight. Additionally, if trains get late, individuals become acquainted with it through
the information base update.

2. Library Management System –

There are lots of books in the library so; it is difficult to store the record of the
relative multitude of books in a register or duplicate. Along these lines, the data set
 administration framework (DBMS) is utilized to keep up all the data identified with
the name of the book, issue date, accessibility of the book, and its writer.

3. Banking –
Database the executive’s framework is utilized to store the exchange data of the
client in the information base.

4. Education Sector –
Presently, assessments are led online by numerous schools and colleges. They deal
with all assessment information through the data set administration framework
(DBMS). In spite of that understudy’s enlistments subtleties, grades, courses,
expense, participation, results, and so forth all the data is put away in the
information base.

5. Credit card exchanges –

The database Management framework is utilized for buying on charge cards and age
of month-to-month proclamations.

6. Social Media Sites –

We all utilization of online media sites to associate with companions and to impart
our perspectives to the world. Every day, many people group pursue these online
media accounts like Pinterest, Facebook, Twitter, and Google in addition to. By the
utilization of the data set administration framework, all the data of clients are put
away in the information base and, we become ready to interface with others.

7. Broadcast communications –
Without DBMS any media transmission organization can’t think. The Database the
executive’s framework is fundamental for these organizations to store the call
subtleties and month to month postpaid bills in the information base.
8. Account –
The information base administration framework is utilized for putting away data
about deals, holding and acquisition of monetary instruments, for example, stocks
and bonds in a data set.

9. Online Shopping –
These days, web-based shopping has become a major pattern. Nobody needs to visit
the shop and burn through their time. Everybody needs to shop through web based
shopping sites, (for example, Amazon, Flipkart, Snapdeal) from home. So all the
items are sold and added uniquely with the assistance of the information base
administration framework (DBMS). Receipt charges, installments, buy data these
are finished with the assistance of DBMS.

10. Human Resource Management –

Big firms or organizations have numerous specialists or representatives working
under them. They store data about worker’s compensation, assessment, and work
with the assistance of an information base administration framework (DBMS).

11. Manufacturing –
Manufacturing organizations make various kinds of items and deal them
consistently. To keep the data about their items like bills, acquisition of the item,
amount, inventory network the executives, information base administration
framework (DBMS) is utilized.

12. Airline Reservation System –

This framework is equivalent to the railroad reservation framework. This
framework additionally utilizes an information base administration framework to
store the records of flight takeoff, appearance, and defer status.
13. Healthcare: DBMS is used in healthcare to manage patient data, medical records,
and billing information.
 14. Data retrieval: DBMS provides a way to retrieve data quickly and easily using
search queries.
15. Data manipulation: DBMS provides tools to manipulate data, such as sorting,
filtering, and aggregating data.
16. Security: DBMS provides security features to ensure that only authorized users
have access to the data.
17. Data backup and recovery: DBMS provides tools to back up data and recover it in
case of system failures or data loss.
18. Multi-user access: DBMS allows multiple users to access and modify data
simultaneously.
19. Reporting and analysis: DBMS provides tools to generate reports and analyze data
to gain insights and make informed decisions.
What Is Microsoft Access?
Microsoft Access is a well-known database management system produced by Microsoft and is
part of the Microsoft 365 office suite. Microsoft Access combines Microsoft’s relational Jet
Database Engine with software development tools and a graphic user interface (GUI). It was first
released in November 1992, so it’s been around for a while. In the rapidly changing, fast-paced
IT world, we can best describe a 30-year-old program as "venerable."

Microsoft Access enables business and enterprise users to manage data and analyze vast amounts
of information efficiently. The program provides a blend of database functionality
and programming capabilities for creating easy-to-navigate forms.

Microsoft Access is like Microsoft Excel in that you can store, edit, and view data. However,
Access has much more to offer, as we are about to see.

MS Access Features

Some of the key features of MS Access are:

User-Friendly Interface: MS Access provides a user-friendly interface that allows you to create
and manage databases using a drag-and-drop interface. This makes it easy to create tables, forms,
queries, and reports without requiring extensive technical knowledge.
Customizable Templates: MS Access includes a range of customizable templates that you can
use as a starting point for your database. This includes templates for inventory management,
customer management, project tracking, and more.

Powerful Query Designer: MS Access includes a powerful query designer that allows you to
extract specific data from tables based on user-defined criteria. This includes the ability to sort,
filter, and aggregate data from multiple tables.

Integrated Reporting Tools: MS Access includes integrated reporting tools that allow you to
create professional-looking reports based on data from one or more tables. This includes the
ability to create charts, graphs, and summaries.

Customizable Forms: MS Access allows you to create custom forms that provide a user-
friendly interface for entering and viewing data in your tables. This includes the ability to add
controls, such as text boxes, drop-down lists, and buttons.

Macros For Automation: MS Access includes macros that allow you to automate common tasks
in your database, such as opening forms and running queries. This can save time and increase
efficiency.

Customizable Code: MS Access allows you to write custom code using the Visual Basic for
Applications (VBA) programming language. This allows you to create custom functions and
automate complex tasks.

Collaboration Features: MS Access allows you to share your database with other users and
control their access to the data. This includes the ability to assign specific permissions to
individual users or groups.

Integration With Other Applications: MS Access integrates with other applications in the
Microsoft Office suite, such as Excel and Word. This allows you to import and export data, as
well as generate reports and charts using data from multiple sources.
MS Access - Create Database

In this chapter, we will be covering the basic process of starting Access and creating a database.
This chapter will also explain how to create a desktop database by using a template and how to
build a database from scratch.

To create a database from a template, we first need to open MS Access and you will see the
following screen in which different Access database templates are displayed.

To view the all the possible databases, you can scroll down or you can also use the search box.

Let us enter project in the search box and press Enter. You will see the database templates related
to project management.
Select the first template. You will see more information related to this template.

After selecting a template related to your requirements, enter a name in the File name field and
you can also specify another location for your file if you want.
Now, press the Create option. Access will download that database template and open a new
blank database as shown in the following screenshot.

Now, click the Navigation pane on the left side and you will see all the other objects that come
with this database.
Click the Projects Navigation and select the Object Type in the menu.

You will now see all the objects types — tables, queries, etc.
Create Blank Database

Sometimes database requirements can be so specific that using and modifying the existing
templates requires more work than just creating a database from scratch. In such case, we make
use of blank database.

Step 1 − Let us now start by opening MS Access.

Step 2 − Select Blank desktop database. Enter the name and click the Create button.

Step 3 − Access will create a new blank database and will open up the table which is also
completely blank.
Creating Microsoft Access Tables

Tables are the foundation of an Access database. Access stores data in tables. This lesson teaches
you how to create a table, add fields to a table, assign data types to fields, and set field
properties.
Understanding Tables
A table is a set of columns and rows. Each column is called a field. Within a table, each field
must be given a name and no two fields can have the same name. Each value in a field represents
a single category of data. For example, a table might have three fields: Last Name, First Name,
and Phone Number. The table consists of three columns: one for last name, one for first name,
and one for phone number. In every row of the table, the Last Name field contains the last name,
the First Name field contains the first name, and the Phone Number field contains the phone
number. Each row in a table is called a record.
All of the data in a table should refer to the same subject. For example, all of the data in the
Employees table should refer to employees, all of the data in the students table should refer to
students, and all of the data in the Courses table should refer to courses.

You can view an Access database as a collection of related tables. For example, in a database that
contains tables for Employees, Students, and Courses, the Employees table lists the employees,
the Students table lists students, and the Courses table lists the courses students can take.

After Access creates a blank database, it opens in Datasheet view and makes available the tools
you need to create a table. Datasheet view displays a table as a set of columns and rows. When
you view a blank database for the first time in Datasheet view, you see a column named ID. This
column is by default the primary key field.

A primary key is a field or combination of fields that uniquely identify each record in a table. No
two records in a table should have the same values in every field. For example, the following
should not occur in a table.

Last Name First Name City

Smith John Jonestown

Smith John Jonestown

In the real world, it is possible to have two people from the same city with the same first and last
name. In cases like this, you can use the ID field as the primary key field and use it to make each
record unique. The ID field has a data type of AutoNumber; as a result, Access automatically
creates a unique number for each record in the database. The resulting table will look like the one
shown here.

ID Last Name First Name City

1 Smith John Jonestown

2 Smith John Jonestown

Access provides several methods for creating a table. One method is to use the Rename option
with the Add New Field column label to give each column the field name you want it to have and
then to type or paste your data into the table. Field names can include letters, numbers, and spaces
and can be up to 64 characters long. When choosing a field name, try to keep it short.
When you save your table for the first time, Access gives you the opportunity to name your table.
Each table name must be unique; hence, two tables in the same database cannot have the same
name. The table name should describe the data in the table; can consist of letters, numbers, and
spaces; and can be up to 64 characters long. When choosing a table name, try to keep it short.

You can save a table by clicking the Save button on the Quick Access toolbar or by right-clicking
the Tables tab and then choosing Save from the menu that appears.

To add fields to a table:

1. Click the Add New Field column label.

2. Activate the Datasheet tab.
3. Click Rename in the Fields & Columns group.
4. Type the field name.
5. Press Enter. Access creates the field.
 6. Type the next field name. Access creates the field. Continue until you have created all of the
fields in your table.
7. Press Enter without entering a field name to end your entries.
Or
1. Right-click the Add New Field column label. A menu appears.
2. Click Rename Column.
3. Type the field name.
4. Press Enter. Access creates the field.
5. Type the next field name. Access creates the field. Continue until you have created all of the
fields in your table.

Name and Save a Table

After you create a table, you must name and save it.

To name and save a table:

1. Click the Save button on the Quick Access toolbar. The Save As dialog box appears.
2. Type the name you want to give your table.
3. Click OK. Access names your table.

Tip: You can use the Rename option at any time to rename any column. For example,
you can rename the ID column Employee ID.

MS Access - Adding Data

Let us add some data into your tables by opening the Access database we have
created.
Select the Views → Datasheet View option in the ribbon and add some data as shown
in the following screenshot.

Similarly, add some data in the second table as well as shown in the following
screenshot.
You can now see that inserting a new data and updating the existing data is very
simple in Datasheet View as working in spreadsheet. But if you want to delete any
data you need to select the entire row first as shown in the following screenshot.

Now press the delete button. This will display the confirmation message.
Click Yes and you will see that the selected record is deleted now.

FormS in Access

Forms in Access are like display cases in stores that make it easier to view or get the items that you want.
Since forms are objects through which you or other users can add, edit, or display the data stored in your
Access desktop database, the design of your form is an important aspect. If your Access desktop database
is going to be used by multiple users, well-designed forms is essential for efficiency and data entry
accuracy.

Create a blank form in Access

 1. To create a form with no controls or preformatted
elements: On the Create tab, click Blank Form.
Access opens a blank form in Layout view, and
displays the Field List pane.
2. In the Field List pane, click the plus sign (+) next to
the table or tables that contain the fields that you
want to see on the form.
3. To add a field to the form, double-click it or drag it
onto the form. To add several fields at once, hold
down CTRL and click several fields, and then drag
them onto the form at the same time.
Note: The order of the tables in the Field List pane can change, depending on which part of the
form is currently selected. If you are not able to add a field to the form, try selecting a different
part of the form and then try adding the field again.

4. Use the tools in the Controls group on the Form

Layout Tools tab to add a logo, title, page numbers,
or the date and time to the form.
5. If you want to add a wider variety of controls to the
form, click Design and use the tools in
the Controls group.

Create a Report
How to Create a Report in Access
You can create a report either by using the Report Wizard, if you want to use more than
one table, or the Report button, if you already have a table or query open.

1. Select the table or query you want to base the report on.

2. Click the Create tab on the ribbon.

3. Click the Report button.
 You can also select either the Blank Report button (to create and display
the report in Layout View) or the Report Design button (to create and
display a blank report in Design View).

A report is instantly created based on the active table or query, and appears
in Layout View.

4. Click the Save button.

5. Give the report a name and click OK.
The report is saved and appears in the Navigation Pane.

There are several ways to create reports in Access, they're listed in this table.

Report Buttons on the Home tab

Creates a basic report of the data in the current table or query. You
Report
can then add features, such as groups and totals.

Report Creates a new blank report in Design view, where you can make
Design more advanced changes to the report.

Blank Report Creates a blank report with no fields or formatting.

Report Shows the Report Wizard that helps you create simple, customizable
Wizard reports.

Labels Shows the Label Wizard to create standard or custom labels.