Chapter - 7
[Link] Integrity:
The overall precision, completeness, and continuity of data are known as
data integrity. Data integrity also applies to the data's protection and
security.
Data integrity also ensures that the information is protected from outside
influences.
Different Kinds of data integrity
Physical and logical data integrity are the two forms of data integrity. Both
are a collection of procedures and methods for maintaining data integrity in
hierarchical and relational databases.
Physical Integrity
Physical integrity refers to the safeguarding of data's completeness and
precision during storage and retrieval. Physical integrity is risked when
natural disasters occur, electricity goes out, or hackers interrupt database
functions.
Logical integrity
In a relational database, logical consistency ensures the data remains intact
as it is used in various ways. Logical integrity, like physical integrity,
defends data from human error and hackers, but in a different way. There
are four different forms of logical consistency.
o Entity Integrity: Entity integrity ensures that each row in a table
has a unique and not null attribute called primary key which defines
the particular row. Eg: In employee table the employee number is
unique and not null is said to be entity integrity.
o Referential Integrity: A foreign key table in a database is a second
table that may refer to a database's primary key table. Foreign keys
are used to connect data that is either shared or null. For example, in
employee primary table empid act as foreign key in table department.
o Domain Integrity: A domain is the named set of scalar values all are
of same type. Eg: If we have a column of age the value will be between
1to120. It makes some sense. Value like 500, 300 etc have no
meaning.
o User-Defined Integrity: Outside of object, referential, and domain
integrity, there are sets of data generated by users. This data would be
labeled as "user-defined" if an employer created a column to input
employee corrective action.
2. Database Security
Database Security refers to the array of controls, tools, and procedures
designed to ensure and safeguard confidentiality, integrity, and accessibility.
Security for databases must cover and safeguard the following aspects:
o The database containing data.
o Database management systems (DBMS)
o Any applications that is associated with it.
o Physical database servers or the database server virtual, and the
hardware that runs it.
o The infrastructure for computing or network that is used to connect to
the database.
Why Database Security is Important?
According to the definition, a data breach refers to a breach of data integrity
in databases. The amount of damage an incident like a data breach can
cause our business is contingent on various consequences or elements.
o Intellectual property that is compromised: Our intellectual
property--trade secrets, inventions, or proprietary methods -- could be
vital for our ability to maintain an advantage in our industry. If our
intellectual property has been stolen or disclosed and our competitive
advantage is lost, it could be difficult to keep or recover.
o The damage to our brand's reputation: Customers or partners may
not want to purchase goods or services from us (or deal with our
business) If they do not feel they can trust our company to protect
their data or their own.
o The concept of business continuity (or lack of it): Some businesses
cannot continue to function until a breach has been resolved.
o Costs for repairing breaches and notifying consumers about
them: Alongside notifying customers of a breach, the company that
has been breached is required to cover the investigation and forensic
services such as crisis management, triage repairs to the affected
systems, and much more.
Common Threats and Challenges
Here are some of the most prevalent kinds of reasons for security attacks
and the reasons.
Insider Dangers
An insider threat can be an attack on security from any three sources
having an access privilege to the database.
o A malicious insider who wants to cause harm.
o An insider who is negligent and makes mistakes that expose the
database to attack. vulnerable to attacks.
o An infiltrator is an outsider who acquires credentials by using a
method like phishing or accessing the database of credential
information in the database itself.
Insider dangers are among the most frequent sources of security breaches to
databases. They often occur as a consequence of the inability of employees
to have access to privileged user credentials.
Human Error
The unintentional mistakes, weak passwords or sharing passwords, and
other negligent or uninformed behaviours of users remain the root causes of
almost half (49 percent) of all data security breaches.
Database Software Vulnerabilities can be exploited
Hackers earn their money by identifying and exploiting vulnerabilities in
software such as databases management software. The major database
software companies and open-source databases management platforms
release regular security patches to fix these weaknesses.
SQL/NoSQL Injection Attacks
A specific threat to databases is the infusing of untrue SQL as well as other
non-SQL string attacks in queries for databases delivered by web-based
apps and HTTP headers. Companies that do not follow the safe coding
practices for web applications and conduct regular vulnerability tests are
susceptible to attacks using these.
Buffer Overflow is a way to Exploit Buffers
Buffer overflow happens when a program seeks to copy more data into the
memory block with a certain length than it can accommodate. The attackers
may make use of the extra data, which is stored in adjacent memory
addresses, to establish a basis for they can begin attacks.
Malware
Malware is software designed to exploit vulnerabilities or cause harm to
databases. Malware can be accessed via any device that connects to the
databases network.
Attacks on Backups
Companies that do not protect backup data using the same rigorous
controls employed to protect databases themselves are at risk of
cyberattacks on backups.
The following factors amplify the threats:
o Data volumes are growing: Data capture, storage, and processing
continue to increase exponentially in almost all organizations. Any
tools or methods must be highly flexible to meet current as well as far-
off needs.
o The infrastructure is sprawling: Network environments are
becoming more complicated, especially as companies shift their
workloads into multiple clouds and hybrid cloud architectures and
make the selection of deployment, management, and administration of
security solutions more difficult.
Best use of Database Security/ Technique to protect data/ Technique
for data security:
As databases are almost always accessible via the network, any security risk
to any component or part of the infrastructure can threaten the database.
Likewise, any security attack that impacts a device or workstation could
endanger the database. Therefore, security for databases must go beyond
the limits of the database.
In evaluating the security of databases in our workplace to determine our
organization's top priorities, look at each of these areas.
o Security for physical security: If the database servers are on-
premises or the cloud data centre, they should be placed in a secure,
controlled climate. (If our server for database is located in a cloud-
based data centre, the cloud provider will handle the security on our
behalf.)
o Access to the network and administrative restrictions: The
practical minimum number of users granted access to the database
and their access rights should be restricted to the minimum level
required to fulfil their tasks. Additionally, access to the network is
limited to the minimum permissions needed.
o End security of the user account or device: Any device that
connects to the network hosting the database must be physically
secured and the security checks throughout the day.
o Security: ALL data--including data stored in databases, as well as
credential information should be secured using the highest-quality
encryption when in storage and while in transport. All encryption keys
must be used in accordance with the best practices guidelines.
o Security of databases using software: Always use the most current
version of our software to manage databases and apply any patches
immediately after they're released.
o Security for web server applications and websites: Any application
or web server that connects to the database could be a target and
should be subjected to periodic security testing and best practices
management.
o Security of backups: All backups, images, or copies of the database
should have the identical (or equally rigorous) security procedures as
the database itself.
o Auditing: Audits of security standards for databases should be
conducted every few months. Record all the logins on the server as
well as the operating system. Also, record any operations that are
made on sensitive data, too.
3. Client-server Database Architecture in DBMS
In client-server architecture many clients connected with one server. The
server is [Link] provides services to all clients. All clients request to
the server for different Service. The server displays the results according to
the client’s request.
In this type of architecture has one or more client computers attached to a
central server over a network.
Client/server architecture is also called as a networking computing model
and client-server network because all the requests and demands are sent
over a network.
Working of Client-server Database Architecture in DBMS
Basically client-server model defines how the server provides services to
clients.
Server is a centralized computer that provides services to all attach clients.
For example file server, web server, etc. each the basic work of server to
provide services to each client.
The client can be a laptop computer, tablets, and smartphones, etc. The
server has many types of relationship with clients. Many servers have one
too many relationships with [Link] one too many relationships many
clients connected with one server.
When one client wants to communicate with the server. The server may be
accepted or rejects the request of clients.
When the server computer accepts the request of clients than server
maintains a connection according to a defined protocol. The protocol rules
over the network.
That must be followed for any network connection. If the one client wants
to send an email over the network. It requests the server, the SMTP (the
protocol that is SMPT is standing for simple mail transfer protocol that used
to transfer a mail over the network. SMTP is a set of commands or
commands that check authentication and the transfer of email.
Structure of Client-server Database Architecture in DBMS
By using this architecture structure this software is divided into three
different tiers:
1. Presentation tier
2. Logic tier
3. Data-tier
Each tier type builds and maintains independently.
1-Presentation tier
This is the first and topmost level of the application. The basic work of this
layer provides user interface and that interface is a graphical user interface.
The graphical user interface is an interface that consists of menus, buttons,
and icons, etc. The presentation tier presents information related to such
work as browsing, sales purchasing, and shopping cart contents. It attaches
with other tiers by computing results to the browser/client tier and all other
tiers in the network.
2-Logic tier
The logical tier is also known as data access tier and middle tier. It lies
between the presentation tier and the data [Link] basically controls the
application’s functions by performing processing. The components that build
this layer exist on the server, assist the resources sharing these components
also define the business rules like different government legal rules, data
rules, and different business algorithm .which are designed to keep data
structure consistent.
3-Data tier
This is basically the DBMS (database management system) layer. This layer
consist of [Link] can be used through the business services layer.
In this layer, data is stored and retrieved and the responsibility of this layer
to keep data consistent and independent.
Providing data its own tier also improves scalability and performance. This
layer consists of data access components.
Advantages of Client-server Database Architecture in DBMS
1. All the data and resources are controlled by server .In this way all
data and resources are very consistent.
2. You can easily increase the number of client in this architecture at
any time. This all increases the scalability of the network.
3. This is very easy to maintain you can easily repair, replace or add
clients in this network. The independence of the changes also known
as encapsulation.
4. This network is very easy to use and it is not complicated.
Disadvantages of Client-server Database Architecture in DBMS
1. Traffic is a big problem in this network.
2. When you add large numbers of the client with server this network
will be more complicated.
3. The hardware and software are very expensive.
4. The client does not have resources for each resource they need to
request the server. Because of all resources exit on server.
4. Distributed Database Management System:
It is a set of interconnected database that is distributed over the computer
network or internet.
It manages the distributed database and provides mechanism so as to
make the database transparent to the users.
Concepts:
Definition:
Distributed database:
It is interrelated collection of shared data physically distributed
over a computer network.
Distributed DBMS:
System that permits the management of the distributed
database.
A DDBMS consist of single logical database that is split into
number of fragments. Each fragment is stored on one or more computers
under the control of separate DBMS with the computers connected by a
communication network.
Each site is capable of independently processing user request
that requires to access local data and is also capable of processing data.
Fetching data from other sites is said to be global application, within site is
said to be local application.
Diagrammatic Representation of DDBMS:
Site 1 DB
Computer Site 2 DB
DB Site 4
Network
Site 3
In this technique, network linking the computers will enable the branches
to communicate with each other, it also enable to access data stored at
another branch office.
Classification of DDBMS:
It is classified into two categories:
[Link]
[Link]
Homogeneous:
All sites are using the same DBMS product. It must easier to design and
manage. It provides incremental growth, making the addition of new site to
the DDBMS easy and allows increased performance.
Heterogeneous:
In this, sites may run different DBMS products. It is usually result when
individual sites have implemented their own database. It need translation to
allow communication between different DBMS.
Architecture of DDBMS:
Reference Architecture
Component Architecture
Reference architecture:
It follows three level architecture for a centralized DBMS. It consist of
set of global external schema, global conceptual schema, fragmentation and
allocation schema and set of internal schema with three level.
Global external Global external Global external
schema Schema Schema
Global Conceptual
Schema
Fragmentation
Schema
Allocation Schema
Local mapping Local mapping Local mapping
Schema Schema Schema
Local Conceptual Local Conceptual Local Conceptual
Schema Schema Schema
Local internal
Local internal Local internal
Schema
Schema Schema
Global external schema provides logical data.
Global Conceptual Schema:
It is a logical description of whole database, as if it were not
distributed. It contains definitions of entities, constraints, relationships,
security and integrity information. It provides physical data independence
from the distributed environment.
Fragmentation Schema:
It describes how the data is logically partitioned.
Allocation Schema:
It describes where the data is to be located, taking account of any
replication.
Local Schemas:
Each local DBMS has its own set of schemas. Local conceptual
and local internal schema correspond to equivalent levels of ANSI – SPARC
architecture. Local mapping schema maps fragment in the allocation
schema into external objects in local database.
Component Architecture:
It consist of four major components (i) Local DBMS (ii) Data
Communication component (iii) Distributed DBMS (iv) Global System
Catalog.
DATA
LOCAL DBMS COMMUNICATIO
N DISTRIBUTED DBMS
GLOBAL SYSTEM
CATALOG
Local DBMS Component:
It is a standard DBMS, responsible for controlling the local data at each
site that has a database.
It has its own system catalog that stores information about the data held at
that site called Local System Catalog. In homogeneous system, the LDBMS
component contains the same DBMS product whereas heterogeneous
contain different DBMS product.
Data Communication Component:
It is a software that enables all sites to communicate with each other,
contains information about sites and the links.
Global System Catalog:
It has same functionality as the system catalog of centralized system. It
holds information to the distributed nature of the system such as
fragmentation, replication and allocation.
Distributed DBMS Component:
It is the controlling unit of entire system.
Distributed Database Design:
Fragmentation
Replication
Allocation. This are the three important design.
Fragmentation:
A relation is divided into various sub relations called fragments, which
are distributed.
It is of three main types:
Horizontal fragmentation
Vertical fragmentation
Mixed fragmentation.
Horizontal fragmentation:
It divide the relation into fragments of rows. Each horizontal fragment
have different number of rows.
Eg:
Empid Name Dept Designation Salary
101 AAA `IS Asst Prof 7000
102 BBB IS Lect 6500
103 CCC CS Asso Prof 7500
104 DDD IT Asst Prof 7000
Employee
Empid Name Dept Designation Salary
101 AAA `IS Asst Prof 7000
102 BBB IS Lect 6500
σ (dept=’IS’(employee))
Vertical fragmentation:
It refers to the division of relation into fragments that comprise a
collection of attribute.
Empid Name Dept
101 AAA `IS
102 BBB IS
103 CCC CS
104 DDD IT
Π (empid,name,dept (employee))
Mixed fragmentation:
It is a two step process, it have row major and column major. In row
major horizontal fragmentation will perform first than vertical fragmentation
whereas in column major vertical fragmentation and horizontal
fragmentation.
Empid Name Dept
101 AAA `IS
102 BBB IS
σ (dept=’IS’(Π (empid,name,dept (employee)))
Π (empid,name,dept (σ (dept=’IS’(employee))))
Replication:
It is the storage of data copies at multiple sites on the network. Fragment
copies can be stored on several site, enhanced data availability and response
time.
If data is replicated over multiple sites, the DDBMS must decide which copy
to access. Database can be either fully replicated, partial replicated and no
replication.
Fully replication stores multiple copies of each fragment at multiple sites. It
is impractical.
Partial replication stores multiple copies of some database fragments at
multiple sites.
No replication stores each database fragments at single site. No duplication
occurs.
Replication is a possibility of restoring lost data at a particular site.
Allocation:
It is a process of deciding where to store a data. It also involves a decision as
to which data is stored in which location. It can be centralized, replicated or
partitioned.
Centralized:
Entire database is stored at one site.
Partitioned:
Database is divided into several number of fragments that are stored
at several sites.
Replicated:
Copies of one or more fragments are stored at several sites.
5. Data Mining and Data Warehousing
Data Warehousing:
Data warehouse is a central repository of information collected from
multiple sources, stored under a unified name residing on single site.
It is modeled by multidimensional structure or data cubes. Each
dimension is an attribute and each cell represent the aggregate measure.
Data warehouse collects information about subjects that span on
entire organization.
A data warehouse is a subject-oriented, integrated, time-variant, and non-
volatile collection of data in support of management’s decision making
process”.
The four keywords—subject-oriented, integrated, time-variant, and
nonvolatile— distinguish data warehouses from other data repository
systems, such as relational database systems, transaction processing
systems, and file systems.
Subject-oriented: A data warehouse is organized around major subjects
such as customer, supplier, product, and sales. Rather than concentrating
on the day-to-day operations and transaction processing of an organization,
a data warehouse focuses on the modeling and analysis of data for decision
makers. Hence, data warehouses typically provide a simple and concise view
of particular subject issues by excluding data that are not useful in the
decision support process.
Integrated: A data warehouse is usually constructed by integrating multiple
heterogeneous sources, such as relational databases, flat files, and online
transaction records. Data cleaning and data integration techniques are
applied to ensure consistency in naming conventions, encoding structures,
attribute measures, and so on.
Time-variant: Data are stored to provide information from an historic
perspective ( e.g., the past 2.7–10 years). Every key structure in the data
warehouse contains, either implicitly or explicitly, a time element.
Nonvolatile: A data warehouse is always a physically separate store of data
transformed from the application data found in the operational environment.
Data stored is permanent in nature.
Data warehousing is the process of constructing and using data
warehouses. The construction of a data warehouse requires data cleaning,
data integration, and data consolidation.
Benefits of Data Warehouse:
Informed decision making.
Consolidated data from many sources.
Historical data analysis.
Data quality, consistency and accuracy.
Separation of analytical processing from transactional
database, which improves the performance of both
system.
Data Warehouse Architecture:
Data warehouses often adopt three-tier architecture, as presented in the
following
1. The bottom tier is a warehouse database server that is almost always a
relational database system. Back-end tools and utilities are used to feed
data into the bottom tier from operational databases or other external
sources (such as customer profile information provided by external
consultants). These tools and utilities perform data extraction, cleaning,
and transformation (e.g., to merge similar data from different sources into
a unified format), as well as load and refresh functions to update the data
warehouse.
2. The middle tier is an OLAP server that is typically implemented using
either (1) a relational OLAP (ROLAP) model, that is, an extended relational
DBMS that maps operations on multidimensional data to standard
relational operations; or (2) a multidimensional OLAP (MOLAP) model,
that is, a special-purpose server that directly implements
multidimensional data and operations. OLAP servers
3. The top tier is a front-end client layer, which contains query and
reporting tools, analysis tools, and/or data mining tools (e.g., trend
analysis, prediction, and so on).
Data mining:
It refers to extracting or mining knowledge from large database.
Data mining and knowledge discovery in the databases is a new
interdisciplinary field, merging ideas from statistics, machine learning
database, parallel computing etc.
Definition of data mining:
It is the search for the relationships and global patterns that exist
in large databases but are hidden among vast amount of data.
The KDD/DM Process Model
Data mining is the core of KDD process model.
KDD stands for Knowledge Discovery in Database.
The Knowledge Discovery in Databases process comprises of a few
steps leading from raw data collections to some form of new knowledge.
The iterative process consists of the following steps:
• Data cleaning: also known as data cleansing, it is a phase in which
noise data and irrelevant data are removed from the collection.
• Data integration: at this stage, multiple data sources, often
heterogeneous, may be combined in a common source.
• Data selection: at this step, the data relevant to the analysis is
decided on and retrieved from the data collection.
• Data transformation: also known as data consolidation, it is a phase
in which the selected data is transformed into forms appropriate for
the mining procedure.
• Data mining: it is the crucial step in which clever techniques are
applied to extract patterns potentially useful.
• Pattern evaluation: in this step, strictly interesting patterns
representing knowledge are identified based on given measures.
Pattern represents knowledge if it is easily understood by humans;
valid on test data with some degree of certainty; and potentially
useful, novel, or validates a hunch about which the used was curious.
Measures of pattern interestingness, either objective or subjective, can
be used to guide the discovery process.
• Knowledge representation: is the final phase in which the discovered
knowledge is visually represented to the user. This essential step uses
visualization techniques to help users understand and interpret the
data mining results.
Example for data mining technique apriori algorithm:
There are four transactions in this database |D|=4 and minimum
support count is taken as ‘2’. Use the apriori algorithm for finding frequent
item set in ‘D’.
TID Items
100 134
200 235
300 1235
400 25
Solution:
D
TID Items
100 134
200 235
300 1235
400 25
------Scan D for count each candidate----
Iteration 1:
Item set Sup Count Item set Sup Count
1 2 1 2
2 3 --compare-> 2 3
3 3 3 3
4 1 5 3
5 3
Iteration 2:
Item set Sup Count Item set Sup Count
1,2 1 1,3 2
1,3 2 2,3 2
1,5 1 --compare-> 2,5 3
2,3 2 3,5 2
2,5 3
3,5 2
Iteration 3:
Item set Sup Count Item set Sup Count
--compare->
1,2,3 1 2,3,5 2
1,3,5 1
2,3,5 2
Since we have only one item set, the algorithm terminates having found all
of the frequent item sets.