DIWAKAR EDUCATION HUB

DATABASE MANAGEMENT
SYSTEMS UNIT – 4
AS PER UPDATED SYLLABUS
DIWAKAR EDUCATION HUB

THE LEARN WITH EXPERTIES

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Database Management System
Database Management System or DBMS in short refers to the technology of storing and
retrieving usersí data with utmost efficiency along with appropriate security measures.
Database is a collection of related data and data is a collection of facts and figures that can be
processed to produce information.
Mostly data represents recordable facts. Data aids in producing information, which is based on
facts. For example, if we have data about marks obtained by all students, we can then
conclude about toppers and average marks.
A database management system stores data in such a way that it becomes easier to retrieve,
manipulate, and produce information.
Database Management System or DBMS in short refers to the technology of storing and
retrieving usersí data with utmost efficiency along with appropriate security measures.
Characteristics
Traditionally, data was organized in file formats. DBMS was a new concept then, and all the
research was done to make it overcome the deficiencies in traditional style of data
management. A modern DBMS has the following characteristics −
 Real-world entity − A modern DBMS is more realistic and uses real-world entities to
design its architecture. It uses the behavior and attributes too. For example, a school
database may use students as an entity and their age as an attribute.
 Relation-based tables − DBMS allows entities and relations among them to form tables.
A user can understand the architecture of a database just by looking at the table names.
 Isolation of data and application − A database system is entirely different than its data.
A database is an active entity, whereas data is said to be passive, on which the database
works and organizes. DBMS also stores metadata, which is data about data, to ease its
own process.
 Less redundancy − DBMS follows the rules of normalization, which splits a relation when
any of its attributes is having redundancy in values. Normalization is a mathematically
rich and scientific process that reduces data redundancy.
 Consistency − Consistency is a state where every relation in a database remains
consistent. There exist methods and techniques, which can detect attempt of leaving
database in inconsistent state. A DBMS can provide greater consistency as compared to
earlier forms of data storing applications like file-processing systems.
 Query Language − DBMS is equipped with query language, which makes it more
efficient to retrieve and manipulate data. A user can apply as many and as different
filtering options as required to retrieve a set of data. Traditionally it was not possible
where file-processing system was used.
 ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation,
and Durability (normally shortened as ACID). These concepts are applied on
transactions, which manipulate data in a database. ACID properties help the database
stay healthy in multi-transactional environments and in case of failure.
 Multiuser and Concurrent Access − DBMS supports multi-user environment and allows
them to access and manipulate data in parallel. Though there are restrictions on
DIWAKAR EDUCATION HUB Page 2
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
transactions when users attempt to handle the same data item, but users are always
unaware of them.
 Multiple views − DBMS offers multiple views for different users. A user who is in the
Sales department will have a different view of database than a person working in the
Production department. This feature enables the users to have a concentrate view of
the database according to their requirements.
 Security − Features like multiple views offer security to some extent where users are
unable to access data of other users and departments. DBMS offers methods to impose
constraints while entering data into the database and retrieving the same at a later
stage. DBMS offers many different levels of security features, which enables multiple
users to have different views with different features. For example, a user in the Sales
department cannot see the data that belongs to the Purchase department. Additionally,
it can also be managed how much data of the Sales department should be displayed to
the user. Since a DBMS is not saved on the disk as traditional file systems, it is very hard
for miscreants to break the code.
Users
A typical DBMS has users with different rights and permissions who use it for different
purposes. Some users retrieve data and some back it up. The users of a DBMS can be broadly
categorized as follows −

 Administrators − Administrators maintain the DBMS and are responsible for

administrating the database. They are responsible to look after its usage and by whom it
should be used. They create access profiles for users and apply limitations to maintain
isolation and force security. Administrators also look after DBMS resources like system
license, required tools, and other software and hardware related maintenance.
 Designers − Designers are the group of people who actually work on the designing part
of the database. They keep a close watch on what data should be kept and in what
format. They identify and design the whole set of entities, relations, constraints, and
views.
 End Users − End users are those who actually reap the benefits of having a DBMS. End
users can range from simple viewers who pay attention to the logs or market rates to
sophisticated users such as business analysts.
Applications of DBMS
Database is a collection of related data and data is a collection of facts and figures that can be
processed to produce information.

DIWAKAR EDUCATION HUB Page 3

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Mostly data represents recordable facts. Data aids in producing information, which is based on
facts. For example, if we have data about marks obtained by all students, we can then
conclude about toppers and average marks.
A database management system stores data in such a way that it becomes easier to retrieve,
manipulate, and produce information. Following are the important characteristics and
applications of DBMS.
 ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation,
and Durability (normally shortened as ACID). These concepts are applied on
transactions, which manipulate data in a database. ACID properties help the database
stay healthy in multi-transactional environments and in case of failure.
 Multiuser and Concurrent Access − DBMS supports multi-user environment and allows
them to access and manipulate data in parallel. Though there are restrictions on
transactions when users attempt to handle the same data item, but users are always
unaware of them.
 Multiple views − DBMS offers multiple views for different users. A user who is in the
Sales department will have a different view of database than a person working in the
Production department. This feature enables the users to have a concentrate view of
the database according to their requirements.
 Security − Features like multiple views offer security to some extent where users are
unable to access data of other users and departments. DBMS offers methods to impose
constraints while entering data into the database and retrieving the same at a later
stage. DBMS offers many different levels of security features, which enables multiple
users to have different views with different features. For example, a user in the Sales
department cannot see the data that belongs to the Purchase department. Additionally,
it can also be managed how much data of the Sales department should be displayed to
the user. Since a DBMS is not saved on the disk as traditional file systems, it is very hard
for miscreants to break the code.
DBMS - Architecture
The design of a DBMS depends on its architecture. It can be centralized or decentralized or
hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-
tier architecture divides the whole system into related but independent n modules, which can
be independently modified, altered, changed, or replaced.
In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and
uses it. Any changes done here will directly be done on the DBMS itself. It does not provide
handy tools for end-users. Database designers and programmers normally prefer to use single-
tier architecture.
If the architecture of DBMS is 2-tier, then it must have an application through which the DBMS
can be accessed. Programmers use 2-tier architecture where they access the DBMS by means
of an application. Here the application tier is entirely independent of the database in terms of
operation, design, and programming.
3-tier Architecture

DIWAKAR EDUCATION HUB Page 4

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
A 3-tier architecture separates its tiers from each other based on the complexity of the users
and how they use the data present in the database. It is the most widely used architecture to
design a DBMS.

 Database (Data) Tier − At this tier, the database resides along with its query processing
languages. We also have the relations that define the data and their constraints at this
level.
 Application (Middle) Tier − At this tier reside the application server and the programs
that access the database. For a user, this application tier presents an abstracted view of
the database. End-users are unaware of any existence of the database beyond the
application. At the other end, the database tier is not aware of any other user beyond
the application tier. Hence, the application layer sits in the middle and acts as a
mediator between the end-user and the database.
 User (Presentation) Tier − End-users operate on this tier and they know nothing about
any existence of the database beyond this layer. At this layer, multiple views of the
database can be provided by the application. All views are generated by applications
that reside in the application tier.
Multiple-tier database architecture is highly modifiable, as almost all its components are
independent and can be changed independently.
Data Models
Data models define how the logical structure of a database is modeled. Data Models are
fundamental entities to introduce abstraction in a DBMS. Data models define how data is
connected to each other and how they are processed and stored inside the system.
The very first data model could be flat data-models, where all the data used are to be kept in
the same plane. Earlier data models were not so scientific, hence they were prone to
introduce lots of duplication and update anomalies.
Entity-Relationship Model
Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships
among them. While formulating real-world scenario into the database model, the ER Model
creates entity set, relationship set, general attributes and constraints.
ER Model is best used for the conceptual design of a database.
DIWAKAR EDUCATION HUB Page 5
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
ER Model is based on −
 Entities and their attributes.
 Relationships among entities.
These concepts are explained below.

 Entity − An entity in an ER Model is a real-world entity having properties

called attributes. Every attribute is defined by its set of values called domain. For
example, in a school database, a student is considered as an entity. Student has various
attributes like name, age, class, etc.
 Relationship − The logical association among entities is called relationship.
Relationships are mapped with entities in various ways. Mapping cardinalities define the
number of association between two entities.
Mapping cardinalities −
o one to one
o one to many
o many to one
o many to many
Relational Model
The most popular data model in DBMS is the Relational Model. It is more scientific a model
than others. This model is based on first-order predicate logic and defines a table as an n-ary
relation.

DIWAKAR EDUCATION HUB Page 6

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The main highlights of this model are −
 Data is stored in tables called relations.
 Relations can be normalized.
 In normalized relations, values saved are atomic values.
 Each row in a relation contains a unique value.
 Each column in a relation contains values from a same domain.
Data Schemas
A database schema is the skeleton structure that represents the logical view of the entire
database. It defines how the data is organized and how the relations among them are
associated. It formulates all the constraints that are to be applied on the data.
A database schema defines its entities and the relationship among them. It contains a
descriptive detail of the database, which can be depicted by means of schema diagrams. It’s
the database designers who design the schema to help programmers understand the database
and make it useful.

A database schema can be divided broadly into two categories −

 Physical Database Schema − This schema pertains to the actual storage of data and its
form of storage like files, indices, etc. It defines how the data will be stored in a
secondary storage.
 Logical Database Schema − This schema defines all the logical constraints that need to
be applied on the data stored. It defines tables, views, and integrity constraints.
Database Instance
It is important that we distinguish these two terms individually. Database schema is the
skeleton of database. It is designed when the database doesn't exist at all. Once the database
is operational, it is very difficult to make any changes to it. A database schema does not
contain any data or information.
A database instance is a state of operational database with data at any given time. It contains
a snapshot of the database. Database instances tend to change with time. A DBMS ensures

DIWAKAR EDUCATION HUB Page 7

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
that its every instance (state) is in a valid state, by diligently following all the validations,
constraints, and conditions that the database designers have imposed.
Three schema Architecture
 The three schema architecture is also called ANSI/SPARC architecture or three-level
architecture.
 This framework is used to describe the structure of a specific database system.
 The three schema architecture is also used to separate the user applications and
physical database.
 The three schema architecture contains three-levels. It breaks the database down
into three different categories.
The three-schema architecture is as follows:

In the above diagram:

o It shows the DBMS architecture.
o Mapping is used to transform the request and response between various database
levels of architecture.
o Mapping is not good for small DBMS because it takes more time.
o In External / Conceptual mapping, it is necessary to transform the request from external
level to conceptual schema.
o In Conceptual / Internal mapping, DBMS transform the request from the conceptual to
internal level.
1. Internal Level
o The internal level has an internal schema which describes the physical storage structure
of the database.

DIWAKAR EDUCATION HUB Page 8

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
o The internal schema is also known as a physical schema.
o It uses the physical data model. It is used to define that how the data will be stored in a
block.
o The physical level is used to describe complex low-level data structures in detail.
2. Conceptual Level
o The conceptual schema describes the design of a database at the conceptual level.
Conceptual level is also known as logical level.
o The conceptual schema describes the structure of the whole database.
o The conceptual level describes what data are to be stored in the database and also
describes what relationship exists among those data.
o In the conceptual level, internal details such as an implementation of the data structure
are hidden.
o Programmers and database administrators work at this level.
3. External Level
o At the external level, a database contains several schemas that sometimes called as
subschema. The subschema is used to describe the different view of the database.
o An external schema is also known as view schema.
o Each view schema describes the database part that a particular user group is interested
and hides the remaining database from that user group.
o The view schema describes the end user interaction with database systems.
Data Independence
If a database system is not multi-layered, then it becomes difficult to make any changes in the
database system. Database systems are designed in multi-layers as we learnt earlier.
Data Independence
A database system normally contains a lot of data in addition to users’ data. For example, it
stores data about data, known as metadata, to locate and retrieve data easily. It is rather
difficult to modify or update a set of metadata once it is stored in the database. But as a DBMS
expands, it needs to change over time to satisfy the requirements of the users. If the entire
data is dependent, it would become a tedious and highly complex job.

DIWAKAR EDUCATION HUB Page 9

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Metadata itself follows a layered architecture, so that when we change data at one layer, it
does not affect the data at another level. This data is independent but mapped to each other.
Logical Data Independence
Logical data is data about database, that is, it stores information about how data is managed
inside. For example, a table (relation) stored in the database and all its constraints, applied on
that relation.
Logical data independence is a kind of mechanism, which liberalizes itself from actual data
stored on the disk. If we do some changes on table format, it should not change the data
residing on the disk.
Physical Data Independence
All the schemas are logical, and the actual data is stored in bit format on the disk. Physical data
independence is the power to change the physical data without impacting the schema or
logical data.
For example, in case we want to change or upgrade the storage system itself − suppose we
want to replace hard-disks with SSD − it should not have any impact on the logical data or
schemas.
Database Language
 A DBMS has appropriate languages and interfaces to express database queries and
updates.
 Database languages can be used to read, store and update the data in the database.
Types of Database Language

1. Data Definition Language

 DDL stands for Data Definition Language. It is used to define database structure or
pattern.
 It is used to create schema, tables, indexes, constraints, etc. in the database.
 Using the DDL statements, you can create the skeleton of the database.
 Data definition language is used to store the information of metadata like the
number of tables and schemas, their names, indexes, columns in each table,
constraints, etc.
Some tasks that come under DDL:
DIWAKAR EDUCATION HUB Page 10
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Create: It is used to create objects in the database.
 Alter: It is used to alter the structure of the database.
 Drop: It is used to delete objects from the database.
 Truncate: It is used to remove all records from a table.
 Rename: It is used to rename an object.
 Comment: It is used to comment on the data dictionary.
These commands are used to update the database schema that's why they come under Data
definition language.
2. Data Manipulation Language
DML stands for Data Manipulation Language. It is used for accessing and manipulating data in
a database. It handles user requests.
Some tasks that come under DML:
 Select: It is used to retrieve data from a database.
 Insert: It is used to insert data into a table.
 Update: It is used to update existing data within a table.
 Delete: It is used to delete all records from a table.
 Merge: It performs UPSERT operation, i.e., insert or update operations.
 Call: It is used to call a structured query language or a Java subprogram.
 Explain Plan: It has the parameter of explaining data.
 Lock Table: It controls concurrency.
3. Data Control Language
 DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
 The DCL execution is transactional. It also has rollback parameters.
(But in Oracle database, the execution of data control language does not have the feature of
rolling back.)
Some tasks that come under DCL:
 Grant: It is used to give user access privileges to a database.
 Revoke: It is used to take back permissions from the user.
There are the following operations which have the authorization of Revoke:
CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.
4. Transaction Control Language
TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical
transaction.
Some tasks that come under TCL:
 Commit: It is used to save the transaction on the database.
 Rollback: It is used to restore the database to original since the last Commit.
DBMS Interface
A database management system (DBMS) interface is a user interface which allows for the
ability to input queries to a database without using the query language itself. A DBMS

DIWAKAR EDUCATION HUB Page 11

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
interface could be a web client, a local client that runs on a desktop computer, or even a
mobile app.
A database management system stores data and responds to queries using a query language,
such as SQL. A DBMS interface provides a way to query data without having to use the query
language, which can be complicated.
The typical way to do this is to create some kind of form that shows what kinds of queries
users can make. Web-based forms are increasingly common with the popularity of MySQL, but
the traditional way to do it has been local desktop apps. It is also possible to create mobile
applications. These interfaces provide a friendlier way of accessing data rather than just using
the command line.
User-friendly interfaces provide by DBMS may include the following:
1. Menu-Based Interfaces for Web Clients or Browsing –
These interfaces present the user with lists of options (called menus) that lead the user
through the formation of a request. Basic advantage of using menus is that they
removes the tension of remembering specific commands and syntax of any query
language, rather than query is basically composed step by step by collecting or picking
options from a menu that is basically shown by the system. Pull-down menus are a very
popular technique in Web based interfaces. They are also often used in browsing
interface which allow a user to look through the contents of a database in an
exploratory and unstructured manner.
2. Forms-Based Interfaces –
A forms-based interface displays a form to each user. Users can fill out all of the form
entries to insert a new data, or they can fill out only certain entries, in which case the
DBMS will redeem same type of data for other remaining entries. This type of forms are
usually designed or created and programmed for the users that have no expertise in
operating system. Many DBMSs have forms specification languages which are special
languages that help specify such forms.
Example: SQL* Forms is a form-based language that specifies queries using a form
designed in conjunction with the relational database schema.b>
3. Graphical User Interface –
A GUI typically displays a schema to the user in diagrammatic [Link] user then can
specify a query by manipulating the diagram. In many cases, GUI’s utilize both menus
and forms. Most GUIs use a pointing device such as mouse, to pick certain part of the
displayed schema diagram.
4. Natural language Interfaces –
These interfaces accept request written in English or some other language and attempt
to understand them. A Natural language interface has its own schema, which is similar
to the database conceptual schema as well as a dictionary of important words.
The natural language interface refers to the words in its schema as well as to the set of
standard words in a dictionary to interpret the [Link] the interpretation is successful,
the interface generates a high-level query corresponding to the natural language and
submits it to the DBMS for processing, otherwise a dialogue is started with the user to

DIWAKAR EDUCATION HUB Page 12

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
clarify any provided condition or request. The main disadvantage with this is that the
capabilities of this type of interfaces are not that much advance.
5. Speech Input and Output –
There is an limited use of speech say it for a query or an answer to a question or being a
result of a request it is becoming commonplace Applications with limited vocabularies
such as inquiries for telephone directory, flight arrival/departure, and bank account
information are allowed speech for input and output to enable ordinary folks to access
this information.
The Speech input is detected using a predefined words and used to set up the
parameters that are supplied to the queries. For output, a similar conversion from text
or numbers into speech take place.
6. Interfaces for DBA –
Most database system contains privileged commands that can be used only by the
DBA’s staff. These include commands for creating accounts, setting system parameters,
granting account authorization, changing a schema, reorganizing the storage structures
of a databases.
Centralized and Client-Server DBMS Architectures:
Centralized DBMS:
a) Merge everything into single system including- Hardware, DBMS software, application
programs, and user interface processing software.
b) User can still connect by a remote terminal – but all processing is done at centralized site.
Physical Centralized Architecture:

Architectures for DBMS have pursued trends similar to those generating computer system
architectures. Earlier architectures utilized mainframes computers to provide the main
processing for all system functions including user application programs as well as user
interface programs as well all DBMS functionality. The reason was that the majority of users

DIWAKAR EDUCATION HUB Page 13

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
accessed such systems via computer terminals that did not have processing power and only
provided display capabilities. Thus all processing was performed remotely on the computer
system and only display information and controls were sent from the computer to the display
terminals which were connected to central computer via a variety of types of communication
networks.
As prices of hardware refused most users replaced their terminals with PCs and workstations.
At first database systems utilized these computers similarly to how they have used is play
terminals so that DBMS itself was still a Centralized DBMS in which all the DBMS functionality
application program execution and user interface processing were carried out on one
Machine.
Basic 2-tier Client-Server Architectures:
 Specialized Servers with Specialized functions
 Print server
 File server
 DBMS server
 Web server
 Email server
 Clients are able to access the specialized servers as needed
Logical two-tier client server architecture:

Clients:
 Offer appropriate interfaces through a client software module to access as well as utilize
the various server resources.
 Clients perhaps diskless machines or PCs or Workstations with disks with only the client
software installed.
 Connected to the servers by means of some form of a network.
(LAN- local area network, wireless network and so on.)
DBMS Server:
 Provides database query as well as transaction services to the clients
 Relational DBMS servers are habitually called query servers, SQL servers, or transaction
servers
 Applications running on clients use an Application Program Interface (API) to access
server databases via standard interface such as:
ODBC- Open Database Connectivity standard
JDBC- for Java programming access
Client and server should install appropriate client module and server module software for
ODBC or JDBC

DIWAKAR EDUCATION HUB Page 14

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Two Tier Client-Server Architecture:
a) A client program may perhaps connect to several DBMSs sometimes called the data sources.
b) In general data sources are able to be files or other non-DBMS software that manages data.
Other variations of clients are likely- example in some object DBMSs more functionality is
transferred to clients including data dictionary functions, optimization as well as recovery
across multiple servers etc.
Three Tier Client-Server Architecture:
a) Common for Web applications.
b) Intermediate Layer entitled Application Server or Web Server.
c) Stores the web connectivity software as well as the business logic part of the application
used to access the corresponding data from the database server.
d) Acts like a conduit for sending moderately processed data between the database server and
the client.
e) Three-tier Architecture is able to Enhance Security:
 Database server merely accessible via middle tier.
 Clients can’t directly access database server.

Classification of DBMS's:
• Based on the data model used
• Traditional- Network, Relational, Hierarchical.
• Emerging- Object-oriented and Object-relational.
• Other classifications
• Single-user (typically utilized with personal computers) v/s multi-user (most DBMSs).
• Centralized (utilizes a single computer with one database) v/s distributed (uses multiple
computers
and multiple databases)
Variations of Distributed DBMSs (DDBMSs):
 Homogeneous DDBMS
 Heterogeneous DDBMS
 Federated or Multi-database Systems
 Distributed Database Systems have at the present come to be known as client-server
based database systems because

DIWAKAR EDUCATION HUB Page 15

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 They don’t support a totally distributed environment however rather a set of database
servers supporting a set of clients.
Cost considerations for DBMSs:
 Cost Range- from free open-source systems to configurations costing
millions of dollars
 Instances of free relational DBMSs- MySQL, PostgreSQL and others.

Data Modelling
Data modeling (data modelling) is the process of creating a data model for the data to be
stored in a Database. This data model is a conceptual representation of Data objects, the
associations between different data objects and the rules. Data modeling helps in the visual
representation of data and enforces business rules, regulatory compliances, and government
policies on the data. Data Models ensure consistency in naming conventions, default values,
semantics, security while ensuring quality of the data.
Data Model
Data model is defined as an abstract model that organizes data description, data semantics
and consistency constraints of data. Data model emphasizes on what data is needed and how
it should be organized instead of what operations will be performed on data. Data Model is
like architect's building plan which helps building conceptual models and set relationship
between data items.
The two types of Data Models techniques are
1. Entity Relationship (E-R) Model
2. UML (Unified Modelling Language)
Why use Data Model?
The primary goal of using data model are:
 Ensures that all data objects required by the database are accurately represented.
Omission of data will lead to creation of faulty reports and produce incorrect results.
 A data model helps design the database at the conceptual, physical and logical levels.
 Data Model structure helps to define the relational tables, primary and foreign keys and
stored procedures.
 It provides a clear picture of the base data and can be used by database developers to
create a physical database.
 It is also helpful to identify missing and redundant data.
 Though the initial creation of data model is labor and time consuming, in the long run, it
makes your IT infrastructure upgrade and maintenance cheaper and faster.
Types of Data Models
Types of Data Models : There are mainly three different types of data models: conceptual data
models, logical data models and physical data models and each one has a specific purpose. The
data models are used to represent the data and how it is stored in the database and to set the
relationship between data items.

DIWAKAR EDUCATION HUB Page 16

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
1. Conceptual Data Model: This Data Model defines WHAT the system contains. This
model is typically created by Business stakeholders and Data Architects. The purpose is
to organize, scope and define business concepts and rules.
2. Logical Data Model: Defines HOW the system should be implemented regardless of the
DBMS. This model is typically created by Data Architects and Business Analysts. The
purpose is to developed technical map of rules and data structures.
3. Physical Data Model: This Data Model describes HOW the system will be implemented
using a specific DBMS system. This model is typically created by DBA and developers.
The purpose is actual implementation of the database.

Types of Data Model

Conceptual Data Model
A Conceptual Data Model is an organized view of database concepts and their relationships.
The purpose of creating conceptual data model is to establish entities, their attributes and
relationships. In this data modeling level, there is hardly any detail available of the actual
database structure. Business stakeholders and data architects typically create a conceptual
data model.
The 3 basic tenants of Conceptual Data Model are
 Entity: A real-world thing
 Attribute: Characteristics or properties of an entity
 Relationship: Dependency or association between two entities
Data model example:
 Customer and Product are two entities. Customer number and name are attributes of
the Customer entity
 Product name and price are attributes of product entity
 Sale is the relationship between the customer and product

DIWAKAR EDUCATION HUB Page 17

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Conceptual Data Model

Characteristics of a conceptual data model
 Offers Organisation-wide coverage of the business concepts.
 This type of Data Models are designed and developed for a business audience.
 The conceptual model is developed independently of hardware specifications like data
storage capacity, location or software specifications like DBMS vendor and technology.
The focus is to represent data as a user will see it in the "real world."
Conceptual data models known as Domain models create a common vocabulary for all
stakeholders by establishing basic concepts and scope.
Logical Data Model
The Logical Data Model is used to define the structure of data elements and to set
relationships between them. Logical data model adds further information to the conceptual
data model elements. The advantage of using Logical data model is to provide a foundation to
form the base for the Physical model. However, the modeling structure remains generic.

Logical Data Model

At this Data Modeling level, no primary or secondary key is defined. At this Data modeling
level, you need to verify and adjust the connector details that were set earlier for
relationships.
Characteristics of a Logical data model
 Describes data needs for a single project but could integrate with other logical data
models based on the scope of the project.
 Designed and developed independently from the DBMS.
 Data attributes will have datatypes with exact precisions and length.
 Normalization processes to the model is applied typically till 3NF.
Physical Data Model
A Physical Data Model describes database specific implementation of the data model. It offers
database abstraction and helps generate schema. This is because of the richness of meta-data

DIWAKAR EDUCATION HUB Page 18

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
offered by a Physical Data Model. Physical data model also helps in visualizing database
structure by replicating database column keys, constraints, indexes, triggers and other RDBMS
features.

Physical Data Model

Characteristics of a physical data model:
 The physical data model describes data need for a single project or application though it
maybe integrated with other physical data models based on project scope.
 Data Model contains relationships between tables that which addresses cardinality and
nullability of the relationships.
 Developed for a specific version of a DBMS, location, data storage or technology to be
used in the project.
 Columns should have exact datatypes, lengths assigned and default values.
 Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are
defined.
Advantages and Disadvantages of Data Model:
Advantages of Data model:
 The main goal of a designing data model is to make certain that data objects offered by
the functional team are represented accurately.
 The data model should be detailed enough to be used for building the physical
database.
 The information in the data model can be used for defining the relationship between
tables, primary and foreign keys, and stored procedures.
 Data Model helps business to communicate the within and across organizations.
 Data model helps to documents data mappings in ETL process
 Help to recognize correct sources of data to populate the model
Disadvantages of Data model:
 To develop Data model one should know physical data stored characteristics.
 This is a navigational system produces complex application development, management.
Thus, it requires a knowledge of the biographical truth.
 Even smaller change made in structure require modification in the entire application.
 There is no set data manipulation language in DBMS.
Entity Relationship (E-R) Model

DIWAKAR EDUCATION HUB Page 19

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The ER model defines the conceptual view of a database. It works around real-world entities
and the associations among them. At view level, the ER model is considered a good option for
designing databases.
Component of ER Diagram

ER Diagram
ER Model is represented by means of an ER diagram. Any object, for example, entities,
attributes of an entity, relationship sets, and attributes of relationship sets, can be
represented with the help of an ER diagram.
Entity
An entity can be a real-world object, either animate or inanimate, that can be easily
identifiable. For example, in a school database, students, teachers, classes, and courses
offered can be considered as entities. All these entities have some attributes or properties that
give them their identity.
An entity set is a collection of similar types of entities. An entity set may contain entities with
attribute sharing similar values. For example, a Students set may contain all the students of a
school; likewise a Teachers set may contain all the teachers of a school from all faculties. Entity
sets need not be disjoint.
An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.
Consider an organization as an example- manager, product, employee, department etc. can be
taken as an entity.

DIWAKAR EDUCATION HUB Page 20

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
a. Weak Entity
An entity that depends on another entity called a weak entity. The weak entity doesn't contain
any key attribute of its own. The weak entity is represented by a double rectangle.

Attributes
Entities are represented by means of their properties, called attributes. All attributes have
values. For example, a student entity may have name, class, and age as attributes.
There exists a domain or range of values that can be assigned to attributes. For example, a
student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be
negative, etc.
Attributes are the properties of entities. Attributes are represented by means of ellipses. Every
ellipse represents one attribute and is directly connected to its entity (rectangle).

If the attributes are composite, they are further divided in a tree like structure. Every node is
then connected to its attribute. That is, composite attributes are represented by ellipses that
are connected with an ellipse.

Multivalued attributes are depicted by double ellipse.

DIWAKAR EDUCATION HUB Page 21

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Derived attributes are depicted by dashed ellipse.

Types of Attributes
 Simple attribute − Simple attributes are atomic values, which cannot be divided further.
For example, a student's phone number is an atomic value of 10 digits.
 Composite attribute − Composite attributes are made of more than one simple
attribute. For example, a student's complete name may have first_name and last_name.
 Derived attribute − Derived attributes are the attributes that do not exist in the physical
database, but their values are derived from other attributes present in the database. For
example, average_salary in a department should not be saved directly in the database,
instead it can be derived. For another example, age can be derived from data_of_birth.
 Single-value attribute − Single-value attributes contain single value. For example −
Social_Security_Number.
 Multi-value attribute − Multi-value attributes may contain more than one values. For
example, a person can have more than one phone number, email_address, etc.
These attribute types can come together in a way like −
 simple single-valued attributes
 simple multi-valued attributes
 composite single-valued attributes
 composite multi-valued attributes
Entity-Set and Keys
Key is an attribute or collection of attributes that uniquely identifies an entity among entity
set.
For example, the roll_number of a student makes him/her identifiable among students.
DIWAKAR EDUCATION HUB Page 22
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Super Key − A set of attributes (one or more) that collectively identifies an entity in an
entity set.
 Candidate Key − A minimal super key is called a candidate key. An entity set may have
more than one candidate key.
 Primary Key − A primary key is one of the candidate keys chosen by the database
designer to uniquely identify the entity set.
Relational Database Design (RDD) mean
Relational database design (RDD) models information and data into a set of tables with rows
and columns. Each row of a relation/table represents a record, and each column represents an
attribute of data. The Structured Query Language (SQL) is used to manipulate relational
databases. The design of a relational database is composed of four stages, where the data are
modeled into a set of related tables. The stages are:
 Define relations/attributes
 Define primary keys
 Define relationships
 Normalization
Relational Database Design (RDD)
Relational databases differ from other databases in their approach to organizing data and
performing transactions. In an RDD, the data are organized into tables and all types of data
access are carried out via controlled transactions. Relational database design satisfies the ACID
(atomicity, consistency, integrity and durability) properties required from a database design.
Relational database design mandates the use of a database server in applications for dealing
with data management problems.
The four stages of an RDD are as follows:
 Relations and attributes: The various tables and attributes related to each table are
identified. The tables represent entities, and the attributes represent the properties of
the respective entities.
 Primary keys: The attribute or set of attributes that help in uniquely identifying a record
is identified and assigned as the primary key
 Relationships: The relationships between the various tables are established with the
help of foreign keys. Foreign keys are attributes occurring in a table that are primary
keys of another table. The types of relationships that can exist between the relations
(tables) are:
o One to one
o One to many
o Many to many
An entity-relationship diagram can be used to depict the entities, their attributes and the
relationship between the entities in a diagrammatic way.
 Normalization: This is the process of optimizing the database structure. Normalization
simplifies the database design to avoid redundancy and confusion. The different normal
forms are as follows:

DIWAKAR EDUCATION HUB Page 23

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
o First normal form
o Second normal form
o Third normal form
o Boyce-Codd normal form
o Fifth normal form
By applying a set of rules, a table is normalized into the above normal forms in a linearly
progressive fashion. The efficiency of the design gets better with each higher degree of
normalization.
Relationship
The association among entities is called a relationship. For example, an employee works_at a
department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.
A relationship is used to describe the relation between entities. Diamond or rhombus is used
to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known as
one to one relationship.
For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an entity on
the right associates with the relationship then this is known as a one-to-many relationship.
For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.

DIWAKAR EDUCATION HUB Page 24

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
c. Many-to-one relationship
When more than one instance of the entity on the left, and only one instance of an entity on
the right associates with the relationship then it is known as a many-to-one relationship.
For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then it is known as a many-to-many
relationship.
For example, Employee can assign by many projects and project can have many employees.

Participation Constraints
 Total Participation − Each entity is involved in the relationship. Total participation is
represented by double lines.
 Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.

Relationship Set
A set of relationships of similar type is called a relationship set. Like entities, a relationship too
can have attributes. These attributes are called descriptive attributes.
Degree of Relationship
The number of participating entities in a relationship defines the degree of the relationship.
 Binary = degree 2
 Ternary = degree 3
 n-ary = degree
Mapping Cardinalities

DIWAKAR EDUCATION HUB Page 25

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Cardinality defines the number of entities in one entity set, which can be associated with the
number of entities of other set via relationship set.
 One-to-one − One entity from entity set A can be associated with at most one entity of
entity set B and vice versa.

 One-to-many − One entity from entity set A can be associated with more than one
entities of entity set B however an entity from entity set B, can be associated with at
most one entity.

 Many-to-one − More than one entities from entity set A can be associated with at most
one entity of entity set B, however an entity from entity set B can be associated with
more than one entity from entity set A.

DIWAKAR EDUCATION HUB Page 26

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Many-to-many − One entity from A can be associated with more than one entity from B
and vice versa.

Notation of ER diagram
Database can be represented using the notations. In ER diagram, many notations are used to
express the cardinality. These notations are as follows:

Fig: Notations of ER diagram

Relational Model concept
Relational model can represent as a table with columns and rows. Each row is known as a
tuple. Each table of the column has a name or attribute.

DIWAKAR EDUCATION HUB Page 27

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Domain: It contains a set of atomic values that an attribute can take.
Attribute: It contains the name of a column in a particular table. Each attribute Ai must have a
domain, dom(Ai)
Relational instance: In the relational database system, the relational instance is represented
by a finite set of tuples. Relation instances do not have duplicate tuples.
Relational schema: A relational schema contains the name of the relation and name of all
columns or attributes.
Relational key: In the relational key, each row has one or more attributes. It can identify the
row in the relation uniquely.
Example: STUDENT Relation

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24

Shyam 12839 9026288936 Delhi 35

Laxman 33289 8583287182 Gurugram 20

Mahesh 27857 7086819134 Ghaziabad 27

Ganesh 17282 9028 9i3988 Delhi 40

 In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the
attributes.
 The instance of schema STUDENT has 5 tuples.
 t3 = <Laxman, 33289, 8583287182, Gurugram, 20>
Properties of Relations
 Name of the relation is distinct from all other relations.
 Each relation cell contains exactly one atomic (single) value
 Each attribute contains a distinct name
 Attribute domain has no significance
 tuple has no duplicate value
 Order of tuple can have a different sequence
Constraints on Relational database model
On modeling the design of the relational database we can put some restrictions like what
values are allowed to be inserted in the relation, what kind of modifications and deletions are
allowed in the relation. These are the restrictions we impose on the relational database.
In models like ER models, we did not have such features.

DIWAKAR EDUCATION HUB Page 28

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Constraints in the databases can be categorized into 3 main categories:
1. Constraints that are applied in the data model is called Implicit constraints.
2. Constraints that are directly applied in the schemas of the data model, by specifying
them in the DDL(Data Definition Language). These are called as schema-based
constraints or Explicit constraints.
3. Constraints that cannot be directly applied in the schemas of the data model. We call
these Application based or semantic constraints.
So here we will deal with Implicit constraints.
Mainly Constraints on the relational database are of 4 types:
1. Domain constraints
2. Key constraints
3. Entity Integrity constraints
4. Referential integrity constraints
1. Domain constraints :
1. Every domain must contain atomic values(smallest indivisible units) it means composite
and multi-valued attributes are not allowed.
2. We perform datatype check here, which means when we assign a data type to a column
we limit the values that it can contain. Eg. If we assign the datatype of attribute age as
int, we cant give it values other then int datatype.

Explanation:
In the above relation, Name is a composite attribute and Phone is a multi-values attribute, so
it is violating domain constraint.
2. Key Constraints or Uniqueness Constraints :
1. These are called uniqueness constraints since it ensures that every tuple in the relation
should be unique.
2. A relation can have multiple keys or candidate keys(minimal superkey), out of which we
choose one of the keys as primary key, we don’t have any restriction on choosing the
primary key out of candidate keys, but it is suggested to go with the candidate key with
less number of attributes.
3. Null values are not allowed in the primary key, hence Not Null constraint is also a part of
key constraint.

DIWAKAR EDUCATION HUB Page 29

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Explanation:
In the above table, EID is the primary key, and first and the last tuple has the same value in EID
ie 01, so it is violating the key constraint.
3. Entity Integrity Constraints :
1. Entity Integrity constraints says that no primary key can take NULL value, since using
primary key we identify each tuple uniquely in a relation.

Explanation:
In the above relation, EID is made primary key, and the primary key cant take NULL values but
in the third tuple, the primary key is null, so it is a violating Entity Integrity constraints.
4. Referential Integrity Constraints :
1. The Referential integrity constraints is specified between two relations or tables and
used to maintain the consistency among the tuples in two relations.
2. This constraint is enforced through foreign key, when an attribute in the foreign key of
relation R1 have the same domain(s) as the primary key of relation R2, then the foreign
key of R1 is said to reference or refer to the primary key of relation R2.
3. The values of the foreign key in a tuple of relation R1 can either take the values of the
primary key for some tuple in relation R2, or can take NULL values, but can’t be empty.

DIWAKAR EDUCATION HUB Page 30

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Explanation:
In the above, DNO of the first relation is the foreign key, and DNO in the second relation is the
primary key. DNO = 22 in the foreign key of the first table is not allowed since DNO = 22
is not defined in the primary key of the second relation. Therefore Referential integrity
constraints is violated here
Relational Language
Relational language is a type of programming language in which the programming logic is
composed of relations and the output is computed based on the query applied. Relational
language works on relations among data and entities to compute a result. Relational language
includes features from and is similar to functional programming language.
Relational language is primarily based on the relational data model, which governs relational
database software and systems. In the relational model’s programming context, the
procedures are replaced by the relations among values. These relations are applied over the
processed arguments or values to construct an output. The resulting output is mainly in the
form of an argument or property. The side effects emerging from this programming logic are
also handled by the procedures or relations.
Relational language is primarily based on the relational data model, which governs relational
database software and systems. In the relational model’s programming context, the
procedures are replaced by the relations among values. These relations are applied over the
processed arguments or values to construct an output. The resulting output is mainly in the
form of an argument or property. The side effects emerging from this programming logic are
also handled by the procedures or relations.
Relational Databases and Schemas
A relational database schema is an arrangement of relation states in such a manner that every
relational database state fulfills the integrity constraints set on a relational database schema.
A relational schema outlines the database relationships and structure in a relational database
program. It can be displayed graphically or written in the Structured Query Language (SQL)
used to build tables in a relational database.
A relational schema contains the name of the relation and name of all columns or attributes.

DIWAKAR EDUCATION HUB Page 31

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
A relation schema represents name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for STUDENT. If a schema
has more than 1 relation, it is called Relational Schema.
a relational database schema is an arrangement of integrity constraints. Thus, in the context of
relational database schema following points deserve a particular consideration:
1. A specific characteristic, that bears the same real-world concept, may appear in more
than one relationship with the same or a different name. For example, in Employees
relation, Employee Id (EmpId) is represented in Vouchers as AuthBy and PrepBy.
2. The specific real-world concept that appears more than once in a relationship should be
represented by different names. For example, an employee is represented as
subordinate or junior by using EmpId and as a superior or senior by using SuperId, in the
employee’s relation.
3. The integrity constraints that are specified on database schema shall apply to every
database state of that schema.
Relational Algebra
Relational algebra is a procedural query language. It gives a step by step process to obtain the
result of the query. It uses operators to perform queries.
Types of Relational operation

1. Select Operation:
o The select operation selects tuples that satisfy a given predicate.
o It is denoted by sigma (σ).
1. Notation: σ p(r)
Where:
σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT.
These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation

DIWAKAR EDUCATION HUB Page 32

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:
1. σ BRANCH_NAME="perryride" (LOAN)
Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:
o This operation shows the list of those attributes that we wish to appear in the result.
Rest of the attributes are eliminated from the table.
o It is denoted by ∏.
1. Notation: ∏ A1, A2, An (r)
Where
A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

DIWAKAR EDUCATION HUB Page 33

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:
1. ∏ NAME, CITY (CUSTOMER)
Output:

NAME CITY

Jones Harrison

Smith Rye

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:
o Suppose there are two tuples R and S. The union operation contains all the tuples that
are either in R or S or both in R & S.
o It eliminates the duplicate tuples. It is denoted by ∪.
1. Notation: R ∪ S
A union operation must hold the following condition:
o R and S must have the attribute of the same number.
o Duplicate tuples are eliminated automatically.
Example:
DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

DIWAKAR EDUCATION HUB Page 34

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Mayes A-321

Turner A-176

Johnson A-273

Jones A-472

Lindsay A-284
BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11

Williams L-17

Input:
1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner

DIWAKAR EDUCATION HUB Page 35

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Jones

Lindsay

Jackson

Curry

Williams

Mayes

4. Set Intersection:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples
that are in both R & S.
o It is denoted by intersection ∩.
1. Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME

Smith

Jones

5. Set Difference:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples
that are in R but not in S.
o It is denoted by intersection minus (-).
1. Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

DIWAKAR EDUCATION HUB Page 36

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product
o The Cartesian product is used to combine each row in one table with each row in the
other table. It is also known as a cross product.
o It is denoted by X.
1. Notation: E X D
Example:
EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B

DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:
1. EMPLOYEE X DEPARTMENT

DIWAKAR EDUCATION HUB Page 37

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing

3 John B B Sales

3 John B C Legal

7. Rename Operation:
The rename operation is used to rename the output relation. It is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to STUDENT1.
1. ρ(STUDENT1, STUDENT)
Note: Apart from these common operations Relational algebra can be used in Join operations.
Relational Calculus
o Relational calculus is a non-procedural query language. In the non-procedural query
language, the user is concerned with the details of how to obtain the end results.
o The relational calculus tells what to do but never explains how to do.
Types of Relational calculus:

DIWAKAR EDUCATION HUB Page 38

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

1. Tuple Relational Calculus (TRC)

o The tuple relational calculus is specified to select the tuples in a relation. In TRC, filtering
variable uses the tuples of a relation.
o The result of the relation can have one or more tuples.
Notation:
1. {T | P (T)} or {T | Condition (T)}
Where
T is the resulting tuples
P(T) is the condition used to fetch T.
For example:
1. { [Link] | Author(T) AND [Link] = 'database' }
OUTPUT: This query selects the tuples from the AUTHOR relation. It returns a tuple with
'name' from Author who has written an article on 'database'.
TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and Universal
Quantifiers (∀).
For example:
1. { R| ∃T ∈ Authors([Link]='database' AND [Link]=[Link])}
Output: This query will yield the same result as the previous one.
2. Domain Relational Calculus (DRC)
o The second form of relation is known as Domain relational calculus. In domain relational
calculus, filtering variable uses the domain of attributes.
o Domain relational calculus uses the same operators as tuple calculus. It uses logical
connectives ∧ (and), ∨ (or) and ┓ (not).
o It uses Existential (∃) and Universal Quantifiers (∀) to bind the variable.
Notation:
1. { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
Where
a1, a2 are attributes
P stands for formula built by inner attributes
For example:

DIWAKAR EDUCATION HUB Page 39

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
1. {< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}
Output: This query will yield the article, page, and subject from the relational javatpoint,
where the subject is a database.
Codd Rules
Dr Edgar F. Codd, after his extensive research on the Relational Model of database systems,
came up with twelve rules of his own, which according to him, a database must obey in order
to be regarded as a true relational database.
These rules can be applied on any database system that manages stored data using only its
relational capabilities. This is a foundation rule, which acts as a base for all the other rules.
Rule 1: Information Rule
The data stored in a database, may it be user data or metadata, must be a value of some table
cell. Everything in a database must be stored in a table format.
Rule 2: Guaranteed Access Rule
Every single data element (value) is guaranteed to be accessible logically with a combination of
table-name, primary-key (row value), and attribute-name (column value). No other means,
such as pointers, can be used to access data.
Rule 3: Systematic Treatment of NULL Values
The NULL values in a database must be given a systematic and uniform treatment. This is a
very important rule because a NULL can be interpreted as one the following − data is missing,
data is not known, or data is not applicable.
Rule 4: Active Online Catalog
The structure description of the entire database must be stored in an online catalog, known
as data dictionary, which can be accessed by authorized users. Users can use the same query
language to access the catalog which they use to access the database itself.
Rule 5: Comprehensive Data Sub-Language Rule
A database can only be accessed using a language having linear syntax that supports data
definition, data manipulation, and transaction management operations. This language can be
used directly or by means of some application. If the database allows access to data without
any help of this language, then it is considered as a violation.
Rule 6: View Updating Rule
All the views of a database, which can theoretically be updated, must also be updatable by the
system.
Rule 7: High-Level Insert, Update, and Delete Rule
A database must support high-level insertion, updation, and deletion. This must not be limited
to a single row, that is, it must also support union, intersection and minus operations to yield
sets of data records.
Rule 8: Physical Data Independence
The data stored in a database must be independent of the applications that access the
database. Any change in the physical structure of a database must not have any impact on
how the data is being accessed by external applications.
Rule 9: Logical Data Independence
DIWAKAR EDUCATION HUB Page 40
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The logical data in a database must be independent of its user’s view (application). Any change
in logical data must not affect the applications using it. For example, if two tables are merged
or one is split into two different tables, there should be no impact or change on the user
application. This is one of the most difficult rule to apply.
Rule 10: Integrity Independence
A database must be independent of the application that uses it. All its integrity constraints can
be independently modified without the need of any change in the application. This rule makes
a database independent of the front-end application and its interface.
Rule 11: Distribution Independence
The end-user must not be able to see that the data is distributed over various locations. Users
should always get the impression that the data is located at one site only. This rule has been
regarded as the foundation of distributed database systems.
Rule 12: Non-Subversion Rule
If a system has an interface that provides access to low-level records, then the interface must
not be able to subvert the system and bypass security and integrity constraints.
SQL
SQL is a programming language for Relational Databases. It is designed over relational algebra
and tuple relational calculus. SQL comes as a package with all major distributions of RDBMS.
SQL comprises both data definition and data manipulation languages. Using the data definition
properties of SQL, one can design and modify database schema, whereas data manipulation
properties allows SQL to store and retrieve data from database.
 SQL stands for Structured Query Language. It is used for storing and managing data
in relational database management system (RDMS).
 It is a standard language for Relational Database System. It enables a user to create,
read, update and delete relational databases and tables.
 All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as
their standard database language.
 SQL allows users to query the database in a number of ways, using English-like
statements.
Rules:
SQL follows the following rules:
 Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
 Statements of SQL are dependent on text lines. We can use a single SQL statement
on one or multiple text line.
 Using the SQL statements, you can perform most of the actions in a database.
 SQL depends on tuple relational calculus and relational algebra.
SQL process:
 When an SQL command is executing for any RDBMS, then the system figure out the
best way to carry out the request and the SQL engine determines that how to
interpret the task.

DIWAKAR EDUCATION HUB Page 41

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 In the process, various components are included. These components can be
optimization Engine, Query engine, Query dispatcher, classic, etc.
 All the non-SQL queries are handled by the classic query engine, but SQL query
engine won't handle logical files.

Characteristics of SQL
 SQL is easy to learn.
 SQL is used to access data from relational database management systems.
 SQL can execute queries against the database.
 SQL is used to describe the data.
 SQL is used to define the data in the database and manipulate it when needed.
 SQL is used to create and drop the database and table.
 SQL is used to create a view, stored procedure, function in a database.
 SQL allows users to set permissions on tables, procedures, and views.
SQL Datatype
 SQL Datatype is used to define the values that a column can contain.
 Every column is required to have a name and data type in the database table.
Datatype of SQL:

DIWAKAR EDUCATION HUB Page 42

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

1. Binary Datatypes
There are Three types of binary Datatypes which are given below:

Data Description
Type

binary It has a maximum length of 8000 bytes. It contains fixed-length

binary data.

varbinary It has a maximum length of 8000 bytes. It contains variable-length

binary data.

image It has a maximum length of 2,147,483,647 bytes. It contains

variable-length binary data.

2. Approximate Numeric Datatype :

The subtypes are given below:

Data From To Description

type

float -1.79E + 1.79E + It is used to specify a floating-point value

308 308 e.g. 6.2, 2.9 etc.

real -3.40e + 3.40E + It specifies a single precision floating point

38 38 number

DIWAKAR EDUCATION HUB Page 43

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
3. Exact Numeric Datatype
The subtypes are given below:

Data Description
type

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal number.

numeric It is used to specify a numeric value.

4. Character String Datatype

The subtypes are given below:

Data Description
type

char It has a maximum length of 8000 characters. It contains Fixed-length

non-unicode characters.

varchar It has a maximum length of 8000 characters. It contains variable-

length non-unicode characters.

text It has a maximum length of 2,147,483,647 characters. It contains

variable-length non-unicode characters.

5. Date and time Datatypes

The subtypes are given below:

DIWAKAR EDUCATION HUB Page 44

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Datatype Description

date It is used to store the year, month, and days value.

time It is used to store the hour, minute, and second values.

timestamp It stores the year, month, day, hour, minute, and the second
value.

SQL INSERT Statement

The SQL INSERT statement is used to insert a single or multiple data in a table. In SQL, You can
insert the data in two ways:
1. Without specifying column name
2. By specifying column name
Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

1. Without specifying column name

If you want to specify all column values, you can specify or ignore the column values.
Syntax
1. INSERT INTO TABLE_NAME
2. VALUES (value1, value2, value 3, .... Value N);
DIWAKAR EDUCATION HUB Page 45
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Query
1. INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);
Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

2. By specifying column name

To insert partial column values, you must have to specify the column names.
Syntax
1. INSERT INTO TABLE_NAME
2. [(col1, col2, col3,.... col N)]
3. VALUES (value1, value2, value 3, .... Value N);
Query
1. INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);
Output: After executing this query, the table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

DIWAKAR EDUCATION HUB Page 46

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

7 Jack null null 40

Note: In SQL INSERT query, if you add values for all columns then there is no need to specify
the column name. But, you must be sure that you are entering the values in the same order as
the column exists.
SQL Update Statement
The SQL UPDATE statement is used to modify the data that is already in the database. The
condition in the WHERE clause decides that which row is to be updated.
Syntax
1. UPDATE table_name
2. SET column1 = value1, column2 = value2, ...
3. WHERE condition;
Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

DIWAKAR EDUCATION HUB Page 47

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Updating single record

Update the column EMP_NAME and set the value to 'Emma' in the row where SALARY is
500000.
Syntax
1. UPDATE table_name
2. SET column_name = value
3. WHERE condition;
Query
1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Emma'
3. WHERE SALARY = 500000;
Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Emma Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Updating multiple records

DIWAKAR EDUCATION HUB Page 48

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
If you want to update multiple columns, you should separate each field assigned with a
comma. In the EMPLOYEE table, update the column EMP_NAME to 'Kevin' and CITY to 'Boston'
where EMP_ID is 5.
Syntax
1. UPDATE table_name
2. SET column_name = value1, column_name2 = value2
3. WHERE condition;
Query
1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Kevin', City = 'Boston'
3. WHERE EMP_ID = 5;
Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Kevin Boston 200000 36

6 Marry Canada 600000 48

Without use of WHERE clause

If you want to update all row from a table, then you don't need to use the WHERE clause. In
the EMPLOYEE table, update the column EMP_NAME as 'Harry'.
Syntax
1. UPDATE table_name
2. SET column_name = value1;
Query
1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Harry';
Output
DIWAKAR EDUCATION HUB Page 49
 DATABASE MANAGEMENT SYSTEMS UNIT – 4

EMP_ID EMP_NAME CITY SALARY AGE

1 Harry Chicago 200000 30

2 Harry Austin 300000 26

3 Harry Denver 100000 42

4 Harry Washington 500000 29

5 Harry Los angels 200000 36

6 Harry Canada 600000 48

SQL DELETE Statement

The SQL DELETE statement is used to delete rows from a table. Generally, DELETE statement
removes one or more records form a table.
Syntax
1. DELETE FROM table_name WHERE some_condition;
Sample Table
EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

DIWAKAR EDUCATION HUB Page 50

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Deleting Single Record

Delete the row from the table EMPLOYEE where EMP_NAME = 'Kristen'. This will delete only
the fourth row.
Query
1. DELETE FROM EMPLOYEE
2. WHERE EMP_NAME = 'Kristen';
Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Deleting Multiple Record

Delete the row from the EMPLOYEE table where AGE is 30. This will delete two rows(first and
third row).
Query
1. DELETE FROM EMPLOYEE WHERE AGE= 30;
Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

2 Robert Austin 300000 26

DIWAKAR EDUCATION HUB Page 51

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Delete all of the records

Delete all the row from the EMPLOYEE table. After this, no records left to display. The
EMPLOYEE table will become empty.
Syntax
DELETE * FROM table_name;
or
DELETE FROM table_name;
Query
1. DELETE FROM EMPLOYEE;
Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

Note: Using the condition in the WHERE clause, we can delete single as well as multiple
records. If you want to delete all the records from the table, then you don't need to use the
WHERE clause.
Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and columns.
o To create the view, we can select the fields from one or more tables present in the
database.
o A view can either have specific rows based on certain condition or all the rows of a
table.
Sample table:
Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

DIWAKAR EDUCATION HUB Page 52

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19

2 Kathrin 86 21

3 David 74 18

4 Alina 90 20

5 John 96 18

1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a single
table or multiple tables.
Syntax:
1. CREATE VIEW view_name AS
2. SELECT column1, column2.....
3. FROM table_name
4. WHERE condition;
2. Creating View from a single table
In this example, we create a View named DetailsView from the table Student_Detail.
Query:
1. CREATE VIEW DetailsView AS
2. SELECT NAME, ADDRESS
3. FROM Student_Details
4. WHERE STU_ID < 4;

DIWAKAR EDUCATION HUB Page 53

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Just like table query, we can query the view to view the data.
1. SELECT * FROM DetailsView;
Output:

NAME ADDRESS

Stephan Delhi

Kathrin Noida

David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT
statement.
In the given example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.
Query:
1. CREATE VIEW MarksView AS
2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
3. FROM Student_Detail, Student_Mark
4. WHERE Student_Detail.NAME = Student_Marks.NAME;
To display data of View MarksView:
1. SELECT * FROM MarksView;

NAME ADDRESS MARKS

Stephan Delhi 97

Kathrin Noida 86

David Ghaziabad 74

Alina Gurugram 90

DIWAKAR EDUCATION HUB Page 54

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

4. Deleting View
A view can be deleted using the Drop View statement.
Syntax
1. DROP VIEW view_name;
Example:
If we want to delete the View MarksView, we can do this as:
1. DROP VIEW MarksView;
Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, written to be executed in response to any of the following events −
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
 A database definition (DDL) statement (CREATE, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is
associated.
Benefits of Triggers
Triggers can be written for the following purposes −
 Generating some derived column values automatically
 Enforcing referential integrity
 Event logging and storing information on table access
 Auditing
 Synchronous replication of tables
 Imposing security authorizations
 Preventing invalid transactions
Creating Triggers
The syntax for creating a trigger is −
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION

DIWAKAR EDUCATION HUB Page 55

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Exception-handling-statements
END;
Where,
 CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger
with the trigger_name.
 {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The
INSTEAD OF clause is used for creating trigger on a view.
 {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
 [OF col_name] − This specifies the column name that will be updated.
 [ON table_name] − This specifies the name of the table associated with the trigger.
 [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for
various DML statements, such as INSERT, UPDATE, and DELETE.
 [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for
each row being affected. Otherwise the trigger will execute just once when the SQL
statement is executed, which is called a table level trigger.
 WHEN (condition) − This provides a condition for rows for which the trigger would fire.
This clause is valid only for row-level triggers.
Example
To start with, we will be using the CUSTOMERS table we had created and used in the previous
chapters −
Select * from customers;

The following program creates a row-level trigger for the customers table that would fire for
INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will
display the salary difference between the old values and new values −
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN ([Link] > 0)
DECLARE
sal_diff number;
DIWAKAR EDUCATION HUB Page 56
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
BEGIN
sal_diff := :[Link] - :[Link];
dbms_output.put_line('Old salary: ' || :[Link]);
dbms_output.put_line('New salary: ' || :[Link]);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Trigger created.
The following points need to be considered here −
 OLD and NEW references are not available for table-level triggers, rather you can use
them for record-level triggers.
 If you want to query the table in the same trigger, then you should use the AFTER
keyword, because triggers can query the table or change it again only after the initial
changes are applied and the table is back in a consistent state.
 The above trigger has been written in such a way that it will fire before any DELETE or
INSERT or UPDATE operation on the table, but you can write your trigger on a single or
multiple operations, for example BEFORE DELETE, which will fire whenever a record will
be deleted using the DELETE operation on the table.
Triggering a Trigger
Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement,
which will create a new record in the table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
When a record is created in the CUSTOMERS table, the above create
trigger, display_salary_changes will be fired and it will display the following result −
Old salary:
New salary: 7500
Salary difference:
Because this is a new record, old salary is not available and the above result comes as null. Let
us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement
will update an existing record in the table −
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
When a record is updated in the CUSTOMERS table, the above create
trigger, display_salary_changes will be fired and it will display the following result −
Old salary: 1500
New salary: 2000

DIWAKAR EDUCATION HUB Page 57

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Salary difference: 500
SQL injection (SQLi)
SQL injection is a web security vulnerability that allows an attacker to interfere with the
queries that an application makes to its database. It generally allows an attacker to view data
that they are not normally able to retrieve. This might include data belonging to other users,
or any other data that the application itself is able to access. In many cases, an attacker can
modify or delete this data, causing persistent changes to the application's content or behavior.
In some situations, an attacker can escalate an SQL injection attack to compromise the
underlying server or other back-end infrastructure, or perform a denial-of-service attack.
Impact of a successful SQL injection attack
A successful SQL injection attack can result in unauthorized access to sensitive data, such as
passwords, credit card details, or personal user information. Many high-profile data breaches
in recent years have been the result of SQL injection attacks, leading to reputational damage
and regulatory fines. In some cases, an attacker can obtain a persistent backdoor into an
organization's systems, leading to a long-term compromise that can go unnoticed for an
extended period.
SQL injection examples
There are a wide variety of SQL injection vulnerabilities, attacks, and techniques, which arise in
different situations. Some common SQL injection examples include:
 Retrieving hidden data, where you can modify an SQL query to return additional results.
 Subverting application logic, where you can change a query to interfere with the
application's logic.
 UNION attacks, where you can retrieve data from different database tables.
 Examining the database, where you can extract information about the version and
structure of the database.
 Blind SQL injection, where the results of a query you control are not returned in the
application's responses.
How to detect SQL injection vulnerabilities
The majority of SQL injection vulnerabilities can be found quickly and reliably using Burp
Suite's web vulnerability scanner.
SQL injection can be detected manually by using a systematic set of tests against every entry
point in the application. This typically involves:
 Submitting the single quote character ' and looking for errors or other anomalies.
 Submitting some SQL-specific syntax that evaluates to the base (original) value of the
entry point, and to a different value, and looking for systematic differences in the
resulting application responses.
 Submitting Boolean conditions such as OR 1=1 and OR 1=2, and looking for differences
in the application's responses.
 Submitting payloads designed to trigger time delays when executed within an SQL
query, and looking for differences in the time taken to respond.

DIWAKAR EDUCATION HUB Page 58

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Submitting OAST payloads designed to trigger an out-of-band network interaction when
executed within an SQL query, and monitoring for any resulting interactions.
SQL injection in different parts of the query
Most SQL injection vulnerabilities arise within the WHERE clause of a SELECT query. This type
of SQL injection is generally well-understood by experienced testers.
But SQL injection vulnerabilities can in principle occur at any location within the query, and
within different query types. The most common other locations where SQL injection arises are:
 In UPDATE statements, within the updated values or the WHERE clause.
 In INSERT statements, within the inserted values.
 In SELECT statements, within the table or column name.
 In SELECT statements, within the ORDER BY clause.
Second-order SQL injection
First-order SQL injection arises where the application takes user input from an HTTP request
and, in the course of processing that request, incorporates the input into an SQL query in an
unsafe way.
In second-order SQL injection (also known as stored SQL injection), the application takes user
input from an HTTP request and stores it for future use. This is usually done by placing the
input into a database, but no vulnerability arises at the point where the data is stored. Later,
when handling a different HTTP request, the application retrieves the stored data and
incorporates it into an SQL query in an unsafe way.
Second-order SQL injection often arises in situations where developers are aware of SQL
injection vulnerabilities, and so safely handle the initial placement of the input into the
database. When the data is later processed, it is deemed to be safe, since it was previously
placed into the database safely. At this point, the data is handled in an unsafe way, because
the developer wrongly deems it to be trusted.
Database-specific factors
Some core features of the SQL language are implemented in the same way across popular
database platforms, and so many ways of detecting and exploiting SQL injection vulnerabilities
work identically on different types of database.
However, there are also many differences between common databases. These mean that
some techniques for detecting and exploiting SQL injection work differently on different
platforms. For example:
 Syntax for string concatenation.
 Comments.
 Batched (or stacked) queries.
 Platform-specific APIs.
 Error messages.
How to prevent SQL injection
Most instances of SQL injection can be prevented by using parameterized queries (also known
as prepared statements) instead of string concatenation within the query.
The following code is vulnerable to SQL injection because the user input is concatenated
directly into the query:
DIWAKAR EDUCATION HUB Page 59
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
String query = "SELECT * FROM products WHERE category = '"+ input + "'";
Statement statement = [Link]();
ResultSet resultSet = [Link](query);
This code can be easily rewritten in a way that prevents the user input from interfering with
the query structure:
PreparedStatement statement = [Link]("SELECT * FROM products
WHERE category = ?");
[Link](1, input);
ResultSet resultSet = [Link]();
Parameterized queries can be used for any situation where untrusted input appears as data
within the query, including the WHERE clause and values in an INSERT or UPDATE statement.
They can't be used to handle untrusted input in other parts of the query, such as table or
column names, or the ORDER BY clause. Application functionality that places untrusted data
into those parts of the query will need to take a different approach, such as white-listing
permitted input values, or using different logic to deliver the required behavior.
For a parameterized query to be effective in preventing SQL injection, the string that is used in
the query must always be a hard-coded constant, and must never contain any variable data
from any origin. Do not be tempted to decide case-by-case whether an item of data is trusted,
and continue using string concatenation within the query for cases that are considered safe. It
is all too easy to make mistakes about the possible origin of data, or for changes in other code
to violate assumptions about what data is tainted.
Functional Dependency
The functional dependency is a relationship that exists between two attributes. It typically
exists between the primary key and non-key attribute within a table.
X → Y
The left side of FD is known as a determinant, the right side of the production is known as a
dependent.
For example:
Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.
Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table
because if we know the Emp_Id, we can tell that employee name associated with it.
Functional dependency can be written as:
Emp_Id → Emp_Name
We can say that Emp_Name is functionally dependent on Emp_Id.
Types of Functional dependency

DIWAKAR EDUCATION HUB Page 60

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

1. Trivial functional dependency

 A → B has trivial functional dependency if B is a subset of A.
 The following dependencies are also trivial like: A → A, B → B
Example:
1. Consider a table with two columns Employee_Id and Employee_Name.
2. {Employee_id, Employee_Name} → Employee_Id is a trivial functional dependency as

3. Employee_Id is a subset of {Employee_Id, Employee_Name}.

4. Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are trivi
al dependencies too.
2. Non-trivial functional dependency
 A → B has a non-trivial functional dependency if B is not a subset of A.
 When A intersection B is NULL, then A → B is called as complete non-trivial.
Example:
1. ID → Name,
2. Name → DOB
Normalization
 Normalization is the process of organizing the data in the database.
 Normalization is used to minimize the redundancy from a relation or set of relations.
It is also used to eliminate the undesirable characteristics like Insertion, Update and
Deletion Anomalies.
 Normalization divides the larger table into the smaller table and links them using
relationship.
 The normal form is used to reduce redundancy from the database table.
Types of Normal Forms
There are the four types of normal forms:

DIWAKAR EDUCATION HUB Page 61

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Normal Description
Form

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non-key attributes

are fully functional dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition

dependency exists.

4NF A relation will be in 4NF if it is in Boyce Codd normal form and has
no multi-valued dependency.

5NF A relation is in 5NF if it is in 4NF and not contains any join

dependency and joining should be lossless.

Transaction
o The transaction is a set of logically related operation. It contains a group of tasks.
o A transaction is an action or series of actions. It is performed by a single user to perform
operations for accessing the contents of the database.
Example: Suppose an employee of bank transfers Rs 800 from X's account to Y's account. This
small transaction contains several low-level tasks:
X's Account
1. Open_Account(X)
DIWAKAR EDUCATION HUB Page 62
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
2. Old_Balance = [Link]
3. New_Balance = Old_Balance - 800
4. [Link] = New_Balance
5. Close_Account(X)
Y's Account
1. Open_Account(Y)
2. Old_Balance = [Link]
3. New_Balance = Old_Balance + 800
4. [Link] = New_Balance
5. Close_Account(Y)
Operations of Transaction:
Following are the main operations of transaction:
Read(X): Read operation is used to read the value of X from the database and stores it in a
buffer in main memory.
Write(X): Write operation is used to write the value back to the database from the buffer.
An example to debit transaction from an account which consists of following operations:
1. 1. R(X);
2. 2. X = X - 500;
3. 3. W(X);
Assume the value of X before starting of the transaction is 4000.
 The first operation reads X's value from database and stores it in a buffer.
 The second operation will decrease the value of X by 500. So buffer will contain
3500.
 The third operation will write the buffer's value to the database. So X's final value will
be 3500.
But it may be possible that because of the failure of hardware, software or power, etc. that
transaction may fail before finished all the operations in the set.
For example: If in the above transaction, the debit transaction fails after executing operation 2
then X's value will remain 4000 in the database which is not acceptable by the bank.
To solve this problem, we have two important operations:
Commit: It is used to save the work done permanently.
Rollback: It is used to undo the work done.
Transaction property
The transaction has the four properties. These are used to maintain consistency in a database,
before and after the transaction.
Property of Transaction
1. Atomicity
2. Consistency
3. Isolation
4. Durability

DIWAKAR EDUCATION HUB Page 63

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Atomicity
o It states that all operations of the transaction take place at once if not, the transaction is
aborted.
o There is no midway, i.e., the transaction cannot occur partially. Each transaction is
treated as one unit and either run to completion or is not executed at all.
Atomicity involves the following two operations:
Abort: If a transaction aborts then all the changes made are not visible.
Commit: If a transaction commits then all the changes made are visible.
Consistency
o The integrity constraints are maintained so that the database is consistent before and
after the transaction.
o The execution of a transaction will leave a database in either its prior stable state or a
new stable state.
o The consistent property of database states that every transaction sees a consistent
database instance.
o The transaction is used to transform the database from one consistent state to another
consistent state.
Isolation
 It shows that the data which is used at the time of execution of a transaction cannot
be used by the second transaction until the first one is completed.
 In isolation, if the transaction T1 is being executed and using the data item X, then
that data item can't be accessed by any other transaction T2 until the transaction T1
ends.

DIWAKAR EDUCATION HUB Page 64

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 The concurrency control subsystem of the DBMS enforced the isolation property.
Durability
 The durability property is used to indicate the performance of the database's
consistent state. It states that the transaction made the permanent changes.
 They cannot be lost by the erroneous operation of a faulty transaction or by the
system failure. When a transaction is completed, then the database reaches a state
known as the consistent state. That consistent state cannot be lost, even in the event
of a system's failure.
 The recovery subsystem of the DBMS has the responsibility of Durability property.
States of Transaction
In a database, the transaction can be in one of the following states -

Active state
o The active state is the first state of every transaction. In this state, the transaction is
being executed.
o For example: Insertion or deletion or updating a record is done here. But all the records
are still not saved to the database.
Partially committed
o In the partially committed state, a transaction executes its final operation, but the data
is still not saved to the database.
o In the total mark calculation example, a final display of the total marks step is executed
in this state.
Committed
A transaction is said to be in a committed state if it executes all its operations successfully. In
this state, all the effects are now permanently saved on the database system.
Failed state
 If any of the checks made by the database recovery system fails, then the transaction
is said to be in the failed state.
 In the example of total mark calculation, if the database is not able to fire a query to
fetch the marks, then the transaction will fail to execute.
Aborted
DIWAKAR EDUCATION HUB Page 65
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 If any of the checks fail and the transaction has reached a failed state then the
database recovery system will make sure that the database is in its previous
consistent state. If not then it will abort or roll back the transaction to bring the
database into a consistent state.
 If the transaction fails in the middle of the transaction then before executing the
transaction, all the executed transactions are rolled back to its consistent state.
 After aborting the transaction, the database recovery module will select one of the
two operations:
1. Re-start the transaction
2. Kill the transaction
Desirable Properties of Transactions
Any transaction must maintain the ACID properties, viz. Atomicity, Consistency, Isolation, and
Durability.
 Atomicity − This property states that a transaction is an atomic unit of processing, that
is, either it is performed in its entirety or not performed at all. No partial update should
exist.
 Consistency − A transaction should take the database from one consistent state to
another consistent state. It should not adversely affect any data item in the database.
 Isolation − A transaction should be executed as if it is the only one in the system. There
should not be any interference from the other concurrent transactions that are
simultaneously running.
 Durability − If a committed transaction brings about a change, that change should be
durable in the database and not lost in case of any failure.
Schedules and Conflicts
In a system with a number of simultaneous transactions, a schedule is the total order of
execution of operations. Given a schedule S comprising of n transactions, say T1, T2,
T3………..Tn; for any transaction Ti, the operations in Ti must execute as laid down in the
schedule S.
Types of Schedules
There are two types of schedules −
 Serial Schedules − In a serial schedule, at any point of time, only one transaction is
active, i.e. there is no overlapping of transactions. This is depicted in the following graph
−

DIWAKAR EDUCATION HUB Page 66

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Parallel Schedules − In parallel schedules, more than one transactions are active
simultaneously, i.e. the transactions contain operations that overlap at time. This is
depicted in the following graph −

Conflicts in Schedules
In a schedule comprising of multiple transactions, a conflict occurs when two active
transactions perform non-compatible operations. Two operations are said to be in conflict,
when all of the following three conditions exists simultaneously −
 The two operations are parts of different transactions.
 Both the operations access the same data item.
 At least one of the operations is a write_item() operation, i.e. it tries to modify the data
item.
Serializability
A serializable schedule of ‘n’ transactions is a parallel schedule which is equivalent to a serial
schedule comprising of the same ‘n’ transactions. A serializable schedule contains the
correctness of serial schedule while ascertaining better CPU utilization of parallel schedule.
Equivalence of Schedules
Equivalence of two schedules can be of the following types −
 Result equivalence − Two schedules producing identical results are said to be result
equivalent.
 View equivalence − Two schedules that perform similar action in a similar manner are
said to be view equivalent.
 Conflict equivalence − Two schedules are said to be conflict equivalent if both contain
the same set of transactions and has the same order of conflicting pairs of operations.
Concurrency Control
 In the concurrency control, the multiple transactions can be executed
simultaneously.
 It may affect the transaction result. It is highly important to maintain the order of
execution of those transactions.
Problems of concurrency control
Several problems can occur when concurrent transactions are executed in an uncontrolled
manner. Following are the three problems in concurrency control.
1. Lost updates
2. Dirty read
DIWAKAR EDUCATION HUB Page 67
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
3. Unrepeatable read
1. Lost update problem
o When two transactions that access the same database items contain their operations in
a way that makes the value of some database item incorrect, then the lost update
problem occurs.
o If two transactions T1 and T2 read a record and then update it, then the effect of
updating of the first record will be overwritten by the second update.
Example:

Here,
 At time t2, transaction-X reads A's value.
 At time t3, Transaction-Y reads A's value.
 At time t4, Transactions-X writes A's value on the basis of the value seen at time t2.
 At time t5, Transactions-Y writes A's value on the basis of the value seen at time t3.
 So at time T5, the update of Transaction-X is lost because Transaction y overwrites it
without looking at its current value.
 Such type of problem is known as Lost Update Problem as update made by one
transaction is lost here.
2. Dirty Read
o The dirty read occurs in the case when one transaction updates an item of the database,
and then the transaction fails for some reason. The updated database item is accessed
by another transaction before it is changed back to the original value.
o A transaction T1 updates a record which is read by T2. If T1 aborts then T2 now has
values which have never formed part of the stable database.
Example:

 At time t2, transaction-Y writes A's value.

 At time t3, Transaction-X reads A's value.

DIWAKAR EDUCATION HUB Page 68

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 At time t4, Transactions-Y rollbacks. So, it changes A's value back to that of prior to
t1.
 So, Transaction-X now contains a value which has never become part of the stable
database.
 Such type of problem is known as Dirty Read Problem, as one transaction reads a
dirty value which has not been committed.
3. Inconsistent Retrievals Problem
o Inconsistent Retrievals Problem is also known as unrepeatable read. When a transaction
calculates some summary function over a set of data while the other transactions are
updating the data, then the Inconsistent Retrievals Problem occurs.
o A transaction T1 reads a record and then does some other processing during which the
transaction T2 updates the record. Now when the transaction T1 reads the record, then
the new value will be inconsistent with the previous value.
Example:
Suppose two transactions operate on three accounts.

DIWAKAR EDUCATION HUB Page 69

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
o Transaction-X is doing the sum of all balance while transaction-Y is transferring an
amount 50 from Account-1 to Account-3.
o Here, transaction-X produces the result of 550 which is incorrect. If we write this
produced result in the database, the database will become an inconsistent state
because the actual sum is 600.
o Here, transaction-X has seen an inconsistent state of the database.
Concurrency Control Protocol
Concurrency control protocols ensure atomicity, isolation, and serializability of concurrent
transactions. The concurrency control protocol can be divided into three categories:
1. Lock based protocol
2. Time-stamp protocol
3. Validation based protocol
Query Processing in DBMS
Query Processing is the activity performed in extracting data from the database. In query
processing, it takes various steps for fetching the data from the database. The steps involved
are:
1. Parsing and translation
2. Optimization
3. Evaluation
The query processing works in the following way:
Parsing and Translation
As query processing includes certain activities for data retrieval. Initially, the given user queries
get translated in high-level database languages such as SQL. It gets translated into expressions
that can be further used at the physical level of the file system. After this, the actual
evaluation of the queries and a variety of query -optimizing transformations and takes place.
Thus before processing a query, a computer system needs to translate the query into a
human-readable and understandable language. Consequently, SQL or Structured Query
Language is the best suitable choice for humans. But, it is not perfectly suitable for the internal
representation of the query to the system. Relational algebra is well suited for the internal
representation of a query. The translation process in query processing is similar to the parser
of a query. When a user executes any query, for generating the internal form of the query, the
parser in the system checks the syntax of the query, verifies the name of the relation in the
database, the tuple, and finally the required attribute value. The parser creates a tree of the
query, known as 'parse-tree.' Further, translate it into the form of relational algebra. With this,
it evenly replaces all the use of the views when used in the query.
Thus, we can understand the working of a query processing in the below-described diagram:

DIWAKAR EDUCATION HUB Page 70

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Suppose a user executes a query. As we have learned that there are various methods of
extracting the data from the database. In SQL, a user wants to fetch the records of the
employees whose salary is greater than or equal to 10000. For doing this, the following query
is undertaken:
select emp_name from Employee where salary>10000;
Thus, to make the system understand the user query, it needs to be translated in the form of
relational algebra. We can bring this query in the relational algebra form as:
o σsalary>10000 (πsalary (Employee))
o πsalary (σsalary>10000 (Employee))
After translating the given query, we can execute each relational algebra operation by using
different algorithms. So, in this way, a query processing begins its working.
Evaluation
For this, with addition to the relational algebra translation, it is required to annotate the
translated relational algebra expression with the instructions used for specifying and
evaluating each operation. Thus, after translating the user query, the system executes a query
evaluation plan.
Query Evaluation Plan
 In order to fully evaluate a query, the system needs to construct a query evaluation
plan.
 The annotations in the evaluation plan may refer to the algorithms to be used for the
particular index or the specific operations.
 Such relational algebra with annotations is referred to as Evaluation Primitives. The
evaluation primitives carry the instructions needed for the evaluation of the
operation.

DIWAKAR EDUCATION HUB Page 71

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Thus, a query evaluation plan defines a sequence of primitive operations used for
evaluating a query. The query evaluation plan is also referred to as the query
execution plan.
 A query execution engine is responsible for generating the output of the given
query. It takes the query execution plan, executes it, and finally makes the output for
the user query.
Optimization
 The cost of the query evaluation can vary for different types of queries. Although the
system is responsible for constructing the evaluation plan, the user does need not to
write their query efficiently.
 Usually, a database system generates an efficient query evaluation plan, which
minimizes its cost. This type of task performed by the database system and is known
as Query Optimization.
 For optimizing a query, the query optimizer should have an estimated cost analysis of
each operation. It is because the overall operation cost depends on the memory
allocations to several operations, execution costs, and so on.
Finally, after selecting an evaluation plan, the system evaluates the query and produces the
output of the query.
Steps for Query Optimization
Query optimization involves three steps, namely query tree generation, plan generation, and
query plan code generation.
Step 1 − Query Tree Generation
A query tree is a tree data structure representing a relational algebra expression. The tables of
the query are represented as leaf nodes. The relational algebra operations are represented as
the internal nodes. The root represents the query as a whole.
During execution, an internal node is executed whenever its operand tables are available. The
node is then replaced by the result table. This process continues for all internal nodes until the
root node is executed and replaced by the result table.
For example, let us consider the following schemas −
EMPLOYEE

EmpID EName Salary DeptNo

DEPARTMENT

DNo DName Locatio

Example 1
Let us consider the query as the following.
$$\pi_{EmpID} (\sigma_{EName = \small "ArunKumar"} {(EMPLOYEE)})$$
The corresponding query tree will be −

DIWAKAR EDUCATION HUB Page 72

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Example 2
consider another query involving a join.
$\pi_{EName, Salary} (\sigma_{DName = \small "Marketing"} {(DEPARTMENT)})
\bowtie_{DNo=DeptNo}{(EMPLOYEE)}$
Following is the query tree for the above query.

Step 2 − Query Plan Generation

After the query tree is generated, a query plan is made. A query plan is an extended query tree
that includes access paths for all operations in the query tree. Access paths specify how the
relational operations in the tree should be performed. For example, a selection operation can
have an access path that gives details about the use of B+ tree index for selection.
Besides, a query plan also states how the intermediate tables should be passed from one
operator to the next, how temporary tables should be used and how operations should be
pipelined/combined.
Step 3− Code Generation
Code generation is the final step in query optimization. It is the executable form of the query,
whose form depends upon the type of the underlying operating system. Once the query code
is generated, the Execution Manager runs it and produces the results.
Approaches to Query Optimization
Among the approaches for query optimization, exhaustive search and heuristics-based
algorithms are mostly used.
Exhaustive Search Optimization
DIWAKAR EDUCATION HUB Page 73
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
In these techniques, for a query, all possible query plans are initially generated and then the
best plan is selected. Though these techniques provide the best solution, it has an exponential
time and space complexity owing to the large solution space. For example, dynamic
programming technique.
Heuristic Based Optimization
Heuristic based optimization uses rule-based optimization approaches for query optimization.
These algorithms have polynomial time and space complexity, which is lower than the
exponential complexity of exhaustive search-based algorithms. However, these algorithms do
not necessarily produce the best query plan.
Some of the common heuristic rules are −
 Perform select and project operations before join operations. This is done by moving the
select and project operations down the query tree. This reduces the number of tuples
available for join.
 Perform the most restrictive select/project operations at first before the other
operations.
 Avoid cross-product operation since they result in very large-sized intermediate tables.
Database Recovery Techniques
Crash Recovery
DBMS is a highly complex system with hundreds of transactions being executed every second.
The durability and robustness of a DBMS depends on its complex architecture and its
underlying hardware and system software. If it fails or crashes amid transactions, it is expected
that the system would follow some sort of algorithm or techniques to recover lost data.
Failure Classification
To see where the problem has occurred, we generalize a failure into various categories, as
follows −
Transaction failure
A transaction has to abort when it fails to execute or when it reaches a point from where it
can’t go any further. This is called transaction failure where only a few transactions or
processes are hurt.
Reasons for a transaction failure could be −
 Logical errors − Where a transaction cannot complete because it has some code error or
any internal error condition.
 System errors − Where the database system itself terminates an active transaction
because the DBMS is not able to execute it, or it has to stop because of some system
condition. For example, in case of deadlock or resource unavailability, the system aborts
an active transaction.
System Crash
There are problems − external to the system − that may cause the system to stop abruptly and
cause the system to crash. For example, interruptions in power supply may cause the failure of
underlying hardware or software failure.
Examples may include operating system errors.

DIWAKAR EDUCATION HUB Page 74

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Disk Failure
In early days of technology evolution, it was a common problem where hard-disk drives or
storage drives used to fail frequently.
Disk failures include formation of bad sectors, unreachability to the disk, disk head crash or
any other failure, which destroys all or a part of disk storage.
Storage Structure
We have already described the storage system. In brief, the storage structure can be divided
into two categories −
 Volatile storage − As the name suggests, a volatile storage cannot survive system
crashes. Volatile storage devices are placed very close to the CPU; normally they are
embedded onto the chipset itself. For example, main memory and cache memory are
examples of volatile storage. They are fast but can store only a small amount of
information.
 Non-volatile storage − These memories are made to survive system crashes. They are
huge in data storage capacity, but slower in accessibility. Examples may include hard-
disks, magnetic tapes, flash memory, and non-volatile (battery backed up) RAM.
Recovery and Atomicity
When a system crashes, it may have several transactions being executed and various files
opened for them to modify the data items. Transactions are made of various operations,
which are atomic in nature. But according to ACID properties of DBMS, atomicity of
transactions as a whole must be maintained, that is, either all the operations are executed or
none.
When a DBMS recovers from a crash, it should maintain the following −
 It should check the states of all the transactions, which were being executed.
 A transaction may be in the middle of some operation; the DBMS must ensure the
atomicity of the transaction in this case.
 It should check whether the transaction can be completed now or it needs to be rolled
back.
 No transactions would be allowed to leave the DBMS in an inconsistent state.
There are two types of techniques, which can help a DBMS in recovering as well as maintaining
the atomicity of a transaction −
 Maintaining the logs of each transaction, and writing them onto some stable storage
before actually modifying the database.
 Maintaining shadow paging, where the changes are done on a volatile memory, and
later, the actual database is updated.
Log-based Recovery
Log is a sequence of records, which maintains the records of actions performed by a
transaction. It is important that the logs are written prior to the actual modification and stored
on a stable storage media, which is failsafe.
Log-based recovery works as follows −
 The log file is kept on a stable storage media.

DIWAKAR EDUCATION HUB Page 75

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 When a transaction enters the system and starts execution, it writes a log about it.
<Tn, Start>
 When the transaction modifies an item X, it write logs as follows −
<Tn, X, V1, V2>
It reads Tn has changed the value of X, from V1 to V2.
 When the transaction finishes, it logs −
<Tn, commit>
The database can be modified using two approaches −
 Deferred database modification − All logs are written on to the stable storage and the
database is updated when a transaction commits.
 Immediate database modification − Each log follows an actual database modification.
That is, the database is modified immediately after every operation.
Recovery with Concurrent Transactions
When more than one transaction are being executed in parallel, the logs are interleaved. At
the time of recovery, it would become hard for the recovery system to backtrack all logs, and
then start recovering. To ease this situation, most modern DBMS use the concept of
'checkpoints'.
Checkpoint
Keeping and maintaining logs in real time and in real environment may fill out all the memory
space available in the system. As time passes, the log file may grow too big to be handled at
all. Checkpoint is a mechanism where all the previous logs are removed from the system and
stored permanently in a storage disk. Checkpoint declares a point before which the DBMS was
in consistent state, and all the transactions were committed.
Recovery
When a system with concurrent transactions crashes and recovers, it behaves in the following
manner −

 The recovery system reads the logs backwards from the end to the last checkpoint.
 It maintains two lists, an undo-list and a redo-list.
 If the recovery system sees a log with <Tn, Start> and <Tn, Commit> or just <Tn, Commit>,
it puts the transaction in the redo-list.

DIWAKAR EDUCATION HUB Page 76

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 If the recovery system sees a log with <Tn, Start> but no commit or abort log found, it
puts the transaction in undo-list.
All the transactions in the undo-list are then undone and their logs are removed. All the
transactions in the redo-list and their previous logs are removed and then redone before
saving their logs.
Object and Object-Relational Databases
Object-Relational Database (ORD)
An object-relational database (ORD) is a database management system (DBMS) that's
composed of both a relational database (RDBMS) and an object-oriented database (OODBMS).
ORD supports the basic components of any object-oriented database model in its schemas and
the query language used, such as objects, classes and inheritance.
An object-relational database may also be known as an object relational database
management systems (ORDBMS).
ORD is said to be the middleman between relational and object-oriented databases because it
contains aspects and characteristics from both models. In ORD, the basic approach is based on
RDB, since the data is stored in a traditional database and manipulated and accessed using
queries written in a query language like SQL. However, ORD also showcases an object-oriented
characteristic in that the database is considered an object store, usually for software that is
written in an object-oriented programming language. Here, APIs are used to store and access
the data as objects.

One of ORD’s aims is to bridge the gap between conceptual data modeling techniques for
relational and object-oriented databases like the entity-relationship diagram (ERD) and object-
relational mapping (ORM). It also aims to connect the divide between relational databases and
the object-oriented modeling techniques that are usually used in programming languages like
Java, C# and C++.

Traditional RDBMS products concentrate on the efficient organization of data that is derived
from a limited set of data-types. On the other hand, an ORDBMS has a feature that allows
developers to build and innovate their own data types and methods, which can be applied to
the DBMS. With this, ORDBMS intends to allow developers to increase the abstraction with
which they view the problem area.
Database Security
DB2 database and functions can be managed by two different modes of security controls:
1. Authentication
2. Authorization
Authentication
Authentication is the process of confirming that a user logs in only in accordance with the
rights to perform the activities he is authorized to perform. User authentication can be
performed at operating system level or database level itself. By using authentication tools for
biometrics such as retina and figure prints are in use to keep the database from hackers or
malicious users.

DIWAKAR EDUCATION HUB Page 77

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The database security can be managed from outside the db2 database system. Here are some
type of security authentication process:
 Based on Operating System authentications.
 Lightweight Directory Access Protocol (LDAP)
For DB2, the security service is a part of operating system as a separate product. For
Authentication, it requires two different credentials, those are userid or username, and
password.
Authorization
You can access the DB2 Database and its functionality within the DB2 database system, which
is managed by the DB2 Database manager. Authorization is a process managed by the DB2
Database manager. The manager obtains information about the current authenticated user,
that indicates which database operation the user can perform or access.
Here are different ways of permissions available for authorization:
Primary permission: Grants the authorization ID directly.
Secondary permission: Grants to the groups and roles if the user is a member
Public permission: Grants to all users publicly.
Context-sensitive permission: Grants to the trusted context role.
Authorization can be given to users based on the categories below:
 System-level authorization
 System administrator [SYSADM]
 System Control [SYSCTRL]
 System maintenance [SYSMAINT]
 System monitor [SYSMON]
Authorities provide of control over instance-level functionality. Authority provide to group
privileges, to control maintenance and authority operations. For instance, database and
database objects.
 Database-level authorization
 Security Administrator [SECADM]
 Database Administrator [DBADM]
 Access Control [ACCESSCTRL]
 Data access [DATAACCESS]
 SQL administrator. [SQLADM]
 Workload management administrator [WLMADM]
 Explain [EXPLAIN]
Authorities provide controls within the database. Other authorities for database include with
LDAD and CONNECT.
 Object-Level Authorization: Object-Level authorization involves verifying privileges
when an operation is performed on an object.
 Content-based Authorization: User can have read and write access to individual rows
and columns on a particular table using Label-based access Control [LBAC].

DIWAKAR EDUCATION HUB Page 78

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
DB2 tables and configuration files are used to record the permissions associated with
authorization names. When a user tries to access the data, the recorded permissions verify the
following permissions:
 Authorization name of the user
 Which group belongs to the user
 Which roles are granted directly to the user or indirectly to a group
 Permissions acquired through a trusted context.
While working with the SQL statements, the DB2 authorization model considers the
combination of the following permissions:
 Permissions granted to the primary authorization ID associated with the SQL
statements.
 Secondary authorization IDs associated with the SQL statements.
 Granted to PUBLIC
 Granted to the trusted context role.
Instance level authorities
Some instance related authorities.
System administration authority (SYSADM)
It is highest level administrative authority at the instance-level. Users with SYSADM authority
can execute some databases and database manager commands within the instance. Users
with SYSADM authority can perform the following operations:
 Upgrade a Database
 Restore a Database
 Update Database manager configuration file.
System control authority (SYSCTRL)
It is the highest level in System control authority. It provides to perform maintenance and
utility operations against the database manager instance and its databases. These operations
can affect system resources, but they do not allow direct access to data in the database.
Users with SYSCTRL authority can perform the following actions:
 Updating the database, Node, or Distributed Connect Service (DCS) directory
 Forcing users off the system-level
 Creating or Dropping a database-level
 Creating, altering, or dropping a table space
 Using any table space
 Restoring Database
System maintenance authority (SYSMAINT)
It is a second level of system control authority. It provides to perform maintenance and utility
operations against the database manager instance and its databases. These operations affect
the system resources without allowing direct access to data in the database. This authority is
designed for users to maintain databases within a database manager instance that contains
sensitive data.
Only Users with SYSMAINT or higher level system authorities can perform the following tasks:

DIWAKAR EDUCATION HUB Page 79

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Taking backup
 Restoring the backup
 Roll forward recovery
 Starting or stopping instance
 Restoring tablespaces
 Executing db2trc command
 Taking system monitor snapshots in case of an Instance level user or a database level
user.
A user with SYSMAINT can perform the following tasks:
 Query the state of a tablespace
 Updating log history files
 Reorganizing of tables
 Using RUNSTATS (Collection catalog statistics)
System monitor authority (SYSMON)
With this authority, the user can monitor or take snapshots of database manager instance or
its database. SYSMON authority enables the user to run the following tasks:
 GET DATABASE MANAGER MONITOR SWITCHES
 GET MONITOR SWITCHES
 GET SNAPSHOT
 LIST
 LIST ACTIVE DATABASES
 LIST APPLICATIONS
 LIST DATABASE PARTITION GROUPS
 LIST DCS APPLICATIONS
 LIST PACKAGES
 LIST TABLES
 LIST TABLESPACE CONTAINERS
 LIST TABLESPACES
 LIST UTITLITIES
 RESET MONITOR
 UPDATE MONITOR SWITCHES
Database authorities
Each database authority holds the authorization ID to perform some action on the database.
These database authorities are different from privileges. Here is the list of some database
authorities:
ACCESSCTRL: allows to grant and revoke all object privileges and database authorities.
BINDADD: Allows to create a new package in the database.
CONNECT: Allows to connect to the database.
CREATETAB: Allows to create new tables in the database.
CREATE_EXTERNAL_ROUTINE: Allows to create a procedure to be used by applications and
the users of the databases.
DATAACCESS: Allows to access data stored in the database tables.
DIWAKAR EDUCATION HUB Page 80
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
DBADM: Act as a database administrator. It gives all other database authorities except
ACCESSCTRL, DATAACCESS, and SECADM.
EXPLAIN: Allows to explain query plans without requiring them to hold the privileges to access
the data in the tables.
IMPLICIT_SCHEMA: Allows a user to create a schema implicitly by creating an object using a
CREATE statement.
LOAD: Allows to load data into table.
QUIESCE_CONNECT: Allows to access the database while it is quiesce (temporarily disabled).
SECADM: Allows to act as a security administrator for the database.
SQLADM: Allows to monitor and tune SQL statements.
WLMADM: Allows to act as a workload administrator
Privileges
SETSESSIONUSER
Authorization ID privileges involve actions on authorization IDs. There is only one privilege,
called the SETSESSIONUSER privilege. It can be granted to user or a group and it allows to
session user to switch identities to any of the authorization IDs on which the privileges are
granted. This privilege is granted by user SECADM authority.
Schema privileges
This privileges involve actions on schema in the database. The owner of the schema has all the
permissions to manipulate the schema objects like tables, views, indexes, packages, data
types, functions, triggers, procedures and aliases. A user, a group, a role, or PUBLIC can be
granted any user of the following privileges:
 CREATEIN: allows to create objects within the schema
 ALTERIN: allows to modify objects within the schema.
DROPIN
This allows to delete the objects within the schema.
Table space privileges
These privileges involve actions on the tablespaces in the database. User can be granted the
USE privilege for the tablespaces. The privileges then allow them to create tables within
tablespaces. The privilege owner can grant the USE privilege with the command WITH GRANT
OPTION on the tablespace when tablespace is created. And SECADM or ACCESSCTRL
authorities have the permissions to USE privileges on the tablespace.
Table and view privileges
The user must have CONNECT authority on the database to be able to use table and view
privileges. The privileges for tables and views are as given below:
CONTROL
It provides all the privileges for a table or a view including drop and grant, revoke individual
table privileges to the user.
ALTER
It allows user to modify a table.
DELETE
DIWAKAR EDUCATION HUB Page 81
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
It allows the user to delete rows from the table or view.
INDEX
It allows the user to insert a row into table or view. It can also run import utility.
REFERENCES
It allows the users to create and drop a foreign key.
SELECT
It allows the user to retrieve rows from a table or view.
UPDATE
It allows the user to change entries in a table, view.
Package privileges
User must have CONNECT authority to the database. Package is a database object that
contains the information of database manager to access data in the most efficient way for a
particular application.
CONTROL
It provides the user with privileges of rebinding, dropping or executing packages. A user with
this privileges is granted to BIND and EXECUTE privileges.
BIND
It allows the user to bind or rebind that package.
EXECUTE
Allows to execute a package.
Index privileges
This privilege automatically receives CONTROL privilege on the index.
Sequence privileges
Sequence automatically receives the USAGE and ALTER privileges on the sequence.
Routine privileges
It involves the action of routines such as functions, procedures, and methods within a
database.
The enhanced data model offers rich features, but breaks backward compatibility.
The classic model is simple, well-understood, and had been around for a long time. The
enhanced data model offers many new features for structuring data. Data producers must
choose which data model to use.
Reasons to use the classic model:
 Data using the classic model can be read by all existing netCDF software.
 Writing programs for classic model data is easier.
 Most or all existing netCDF conventions are targeted at the classic model.
 Many great features, like compression, parallel I/O, large data sizes, etc., are available
within the classic model.
Reasons to use the ehanced model:
 Complex data structures can be represented very easily in the data, leading to easier
programming.

DIWAKAR EDUCATION HUB Page 82

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 If exisiting HDF5 applications produce or use these data, and depend on user-defined
types, unsigned types, strings, or groups, then the enhanced model is required.
 In performance-critical applications, the enhanced model may provide significant
benefits.
Multimedia Databases
The multimedia databases are used to store multimedia data such as images, animation,
audio, video along with text. This data is stored in the form of multiple file types like .txt(text),
.jpg(images), .swf(videos), .mp3(audio) etc.

Contents of the Multimedia Database

The multimedia database stored the multimedia data and information related to it. This is
given in detail as follows −
Media data
This is the multimedia data that is stored in the database such as images, videos, audios,
animation etc.
Media format data
The Media format data contains the formatting information related to the media data such as
sampling rate, frame rate, encoding scheme etc.
Media keyword data
This contains the keyword data related to the media in the database. For an image the
keyword data can be date and time of the image, description of the image etc.
Media feature data
Th Media feature data describes the features of the media data. For an image, feature data
can be colours of the image, textures in the image etc.
Challenges of Multimedia Database
There are many challenges to implement a multimedia database. Some of these are:

DIWAKAR EDUCATION HUB Page 83

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Multimedia databases contains data in a large type of formats such as .txt(text),
.jpg(images), .swf(videos), .mp3(audio) etc. It is difficult to convert one type of data
format to another.
 The multimedia database requires a large size as the multimedia data is quite large and
needs to be stored successfully in the database.
 It takes a lot of time to process multimedia data so multimedia database is slow.
Mobile Databases
Mobile databases are separate from the main database and can easily be transported to
various places. Even though they are not connected to the main database, they can still
communicate with the database to share and exchange data.
The mobile database includes the following components −
 The main system database that stores all the data and is linked to the mobile database.
 The mobile database that allows users to view information even while on the move. It
shares information with the main database.
 The device that uses the mobile database to access data. This device can be a mobile
phone, laptop etc.
 A communication link that allows the transfer of data between the mobile database and
the main database.
Deductive Database
A deductive database is a database system that makes conclusions about its data based on a
set of well-defined rules and facts. This type of database was developed to combine logic
programming with relational database management systems. Usually, the language used to
define the rules and facts is the logical programming language Datalog.
A Deductive Database is a type of database that can make conclusions or we can say
deductions using a sets of well defined rules and fact that are stored in the database. In
today’s world as we deal with a large amount of data, this deductive database provides a lot of
advantages. It helps to combine the RDBMS with logic programming. To design a deductive
database a purely declarative programming language called Datalog is used.
The implementations of deductive databases can be seen in LDL (Logic Data Language), NAIL
(Not Another Implementation of Logic), CORAL, and VALIDITY.
The use of LDL and VALIDITY in a variety of business/industrial applications are as follows.
1. LDL Applications:
This system has been applied to the following application domains:
 Enterprise modeling:
Data related to an enterprise may result in an extended ER model containing hundreds
of entities and relationship and thousands of [Link] domain involves modeling
the structure, processes, and constraints within an enterprise.
 Hypothesis testing or data dredging:
This domain involves formulating a hypothesis, translating in into an LDL rule set and a
query, and then executing the query against given data to test the hypothesis. This has
been applied to genome data analysis in the field of microbiology, where data dredging

DIWAKAR EDUCATION HUB Page 84

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
consists of identifying the DNA sequences from low-level digitized auto radio graphs
from experiments performed on [Link] Bacteria.
 Software reuse:
A small fraction of the software for an application is rule-based and encoded in LDL (bulk
is developed in standard procedural code). The rules give rise to a knowledge base that
contains, A definition of each C module used in systemand A set of rules that defines
ways in which modules can export/import functions, constraints and so on. The
“Knowledge base” can be used to make decisions that pertain to the reuse of software
subsets. This is being experimented within banking software.
2. VALIDITY Applications:
Validity combines deductive capabilities with the ability to manipulate complex objects (OIDs,
inheritance, methods, etc). It provides a DOOD data model and language called DEL (Datalog
Extended Language), an engine working along a client-server model and a set of tools for
schema and rule editing, validation, and querying.
The following are some application areas of the VALIDITY system:
 Electronic commerce:
In electronic commerce, complex customers profiles have to be matched against target
descriptions. The matching process is also described by rules, and computed predicates
deal with numeric computations. The declarative nature of DEl makes the formulation of
the matching algorithm easy.
 Rules-governed processes:
In a rules-governed process, well defined rules define the actions to be performed. In
those process some classes are modeled as DEL classes. The main advantage of VALIDITY
is the ease with which new regulations are taken into account.
 Knowledge discovery:
The goal of knowledge discovery is to find new data relationships by analyzing existing
data. An application prototype developed by University of Illinois utilizes already
existing minority student data that has been enhanced with rules in DEL.
 Concurrent Engineering:
A concurrent engineering applications deals with large amounts of centralized data,
shared by several participants. An application prototype has been developed in the area
of civil engineering. The design data is modeled using the object-oriented power of the
DEL language. DEL is able to handle transformation of rules into constraints, and it can
also handle any closed formula as an integrity constraint.
XML - Databases
XML Database is used to store huge amount of information in the XML format. As the use of
XML is increasing in every field, it is required to have a secured place to store the XML
documents. The data stored in the database can be queried using XQuery, serialized, and
exported into a desired format.
XML Database Types
There are two major types of XML databases −
 XML- enabled

DIWAKAR EDUCATION HUB Page 85

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Native XML (NXD)
XML - Enabled Database
XML enabled database is nothing but the extension provided for the conversion of XML
document. This is a relational database, where data is stored in tables consisting of rows and
columns. The tables contain set of records, which in turn consist of fields.
Native XML Database
Native XML database is based on the container rather than table format. It can store large
amount of XML document and data. Native XML database is queried by the XPath-
expressions.
Native XML database has an advantage over the XML-enabled database. It is highly capable to
store, query and maintain the XML document than XML-enabled database.
Example
Following example demonstrates XML database −
<?xml version = "1.0"?>
<contact-info>
<contact1>
<name>Tanmay Patil</name>
<company>TutorialsPoint</company>
<phone>(011) 123-4567</phone>
</contact1>

<contact2>
<name>Manisha Patil</name>
<company>TutorialsPoint</company>
<phone>(011) 789-4567</phone>
</contact2>
</contact-info>
Here, a table of contacts is created that holds the records of contacts (contact1 and contact2),
which in turn consists of three entities − name, company and phone.
Internet Database Applications
Internet Database Applications are programs that are built to run on Internet browsers and
communicate with database servers. Internet Database Applications are usually developed
using very few graphics and are built using XHTML forms and Style Sheets.
Most companies are starting to migrate from the old fashioned desktop database applications
to web based Internet Database Applications in XHTML format.
Below are some of the benefits of Internet Database Applications:
 Powerful and Scalable - Internet Database Applications are more robust, agile and able
to expand and scale up more easily.
Database servers that are built to serve Internet applications are designed to handle
millions of concurrent connections and complex SQL queries.
DIWAKAR EDUCATION HUB Page 86
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
A good example is Facebook, which uses database servers that are able to handle
millions of inquiries and complex SQL queries.
Internet database applications use the same type of database server that is designed to
run Facebook. The database servers that are built to serve desktop applications usually
can handle only a limited number of connections and are not able to deal with complex
SQL queries.
 Web Based - Internet Database Applications are web based applications, therefore the
data can be accessed using a browser at any location.
 Security - Database servers have been fortified with preventive features and security
protocols have been implemented to combat today's cyber security threats and
vulnerabilities.
 Open Source, Better Licensing Terms and Cost Savings - There are many powerful
database servers that are open source. This means that there is no licensing cost. Many
large enterprise sites are using Open Source Database Servers, such as Facebook, Yahoo,
YouTube, Flickr, Wikipedia, etc. . Open Source also creates less dependence on vendors,
which is a big advantage because that provides more product quality control and lower
cost. Open source also offers easier customization and is experiencing a fast growing
adoption rate, especially by the large and influential enterprises.
 Abundant Features - There are many open source programming languages (such as
PHP, Python, Ruby) and hundreds of powerful open source libraries, tools and plug-ins
specifically built to interact with today's database servers.
Geographical information system (GIS)
Geographical information system (GIS) is basically defined as a systematic integration of
hardware and software for capturing, storing, displaying, updating manipulating and analyzing
spatial data. GIS can also be viewed as an interdisciplinary area that incorporates many distinct
fields of study such as:
1. Geodesy that is basically projection,
surveying, cartography and so on.
2. Remote Sensing
3. Photogrammetry
4. Environmental Science
5. City Planning
6. Cognitive Science
As a result GIS relies on progress made in fields such as computer science, databases,
statistics, and artificial intelligence. All the different problems and question that arises from
the integration of multiple disciplines make a more than a simple tool.
Requirements for GIS –
Geographic Information requires a means of integration between different sources of data at
different level of accuracy. System basically deals with the aspects of daily life, so it must be
updated daily to keep it current and reliable. Much of the Information Stored in GIS are for
practical use requires a special means of retrieval and manipulation.

DIWAKAR EDUCATION HUB Page 87

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
GIS system and application basically deals with information that can be viewed as data with
specific meaning and context rather than simple data.
Components of GIS system –
GIS system can be viewed as an integration of three components are hardware and software,
data, people. Lets discuss them one by one:
1. Hardware and software –
Hardware relates to device used by end users such as graphic devices or plotters and
scanners. Data storage and manipulation is done using a range of processor. With the
development of the Internet and Web based application, Web servers have become part
of many system’s architecture, hence most GIS’s follows 3-Tier architecture.
Software parts relates to the processes used to define, store and manipulate the data and
hence it is akin to DBMS. Different models are used to provide efficient means of storage
retrieval and manipulation of data.
2. Data –
Geographic data are basically divided into two main groups are vector and raster.
Vector data/layers in GIS refers to discrete objects represented by points, lines and polygons.
Lines are formed by connecting two or more points and polygons are closed set of Lines.
Layers represent geometries that share a common set of attributes. Objects within a layer
have mutual topology. Vector sources include digitized maps, features extracted from image
surveys and many more.
Raster data is a continuous grid of cells in two dimension or the equivalent of cubic cells in
three dimension. Raster data are divided conceptually into categorical and continuous. In a
categorical raster every cell value is linked to a category in a separate [Link] Soil type,
vegetation [Link] suitability, and so on. Continuous raster images usually describes
continuous phenomena in space such as Digital Elevation Model where each pixel is an
elevation value. Unlike categorical raster, a continuous raster doesn’t have an
attribute/category table attached. Typical Raster sources are aerial images, satellite images
and scanned map images.
3. People –
People are involved in all phases of development of a GIS system and in collecting data.
They include cartographers and surveyors who create the maps and survey the land and
the geographical features. They also include system users who collect the data, upload
the data to system, manipulate the system and analyze the results.
Genome Data Management
GENOME is a prototype database management system (DBMS)/user interface system
designed to manage complex biological data, allowing users to more fully analyze and
understand relationships in human genome data. The system is designed to allow the
establishment of a net- work of searchable data sources.
Characteristics of Biological Data (Genome Data Management)
There are many characteristics of biological data. All these characteristics make the
management of biological information a particularly challenging problem. Here mainly we will
focus on characteristics of biological information and multidisciplinary field called

DIWAKAR EDUCATION HUB Page 88

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
bioinformatics. Bioinformatics, now a days has emerged with graduate degree programs in
several universities.
Characteristics of Biological Information:
 There is a high amount and range of variability in data.
There should be a flexibility in biological systems so that it can handle data types and
values. Placing constraints on data types must be limited with such a wide range of
possible data values. There can be a loss of information when there is exclusion of such
values.
 There will be a difference in representation of the same data by different biologists.
This can be done even using the same system. There is a multiple ways to model any
given entity with the results often reflecting the particular focus of the scientist.
There should be a linking of data elements in a network of schemas.
 Defining the complex queries and also important to the biologists.
Complex queries must be supported by biological systems. Knowledge of the data
structure is needed for the average users because with the help of this knowledge
average user can construct a complex query across data sets on their own. For this
systems must provide some tools for building these queries.
 When compared with most other domains or applications, biological data becomes
highly complex.
Such data must ensure that no information is lost during biological data modelling and
such data must be able to represent a complex substructure of data as well as
relationships. An additional context is provided by the structure of the biological data
for interpretation of the information.
 There is a rapid change in schemas of biological databases.
There should be a support of schema evolution and data object migration so that there
can be an improved information flow between generations or releases of databases.
The relational database systems support the ability to extend the schema and a
frequent occurrence in the biological setting.
 Most biologists are not likely to have knowledge of internal structure of the database or
about schema design.
Users need an information which can be displayed in a manner such that it can be
applicable to the problem which they are trying to address. Also the data structure
should be reflected in an easy and understandable manner. An information regarding
the meaning of the schema is not provided to the user because of the failure by the
relational schemas. A present search interfaces is provided by the web interfaces, which
may limit access into the database.
 There is no need of the write access to the database by the users of biological data,
instead they only require read access.
There is limitation of write access to the privileged users called curators. There are only
small numbers of users which require write access but a wide variety of read access
patterns are generated by the users into the databases.

DIWAKAR EDUCATION HUB Page 89

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Access to “old” values of the data are required by the users of biological data most often
while verifying the previously reported results.
Hence system of archives must support the changes to the values of the data in the
database. Access to both the most recent version of data value and its previous version
are important in the biological domain.
 Added meaning is given by the context of data for its use in biological applications.
Whenever appropriate, context must be maintained and conveyed to the user. For the
maximization of the interpretation of a biological data value, it should be possible to
integrate as many contexts as possible.
Distributed databases
Distributed databases can be classified into homogeneous and heterogeneous databases
having further divisions.
Types of Distributed Databases
Distributed databases can be broadly classified into homogeneous and heterogeneous
distributed database environments, each with further sub-divisions, as shown in the following
illustration.

Homogeneous Distributed Databases

In a homogeneous distributed database, all the sites use identical DBMS and operating
systems. Its properties are −
 The sites use very similar software.
 The sites use identical DBMS or DBMS from the same vendor.
 Each site is aware of all other sites and cooperates with other sites to process user
requests.
 The database is accessed through a single interface as if it is a single database.
Types of Homogeneous Distributed Database
There are two types of homogeneous distributed database −
 Autonomous − Each database is independent that functions on its own. They are
integrated by a controlling application and use message passing to share data updates.

DIWAKAR EDUCATION HUB Page 90

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Non-autonomous − Data is distributed across the homogeneous nodes and a central or
master DBMS co-ordinates data updates across the sites.
Heterogeneous Distributed Databases
In a heterogeneous distributed database, different sites have different operating systems,
DBMS products and data models. Its properties are −
 Different sites use dissimilar schemas and software.
 The system may be composed of a variety of DBMSs like relational, network,
hierarchical or object oriented.
 Query processing is complex due to dissimilar schemas.
 Transaction processing is complex due to dissimilar software.
 A site may not be aware of other sites and so there is limited co-operation in processing
user requests.
Types of Heterogeneous Distributed Databases
 Federated − The heterogeneous database systems are independent in nature and
integrated together so that they function as a single database system.
 Un-federated − The database systems employ a central coordinating module through
which the databases are accessed.
Distributed DBMS Architectures
DDBMS architectures are generally developed depending on three parameters −
 Distribution − It states the physical distribution of data across the different sites.
 Autonomy − It indicates the distribution of control of the database system and the
degree to which each constituent DBMS can operate independently.
 Heterogeneity − It refers to the uniformity or dissimilarity of the data models, system
components and databases.
Architectural Models
Some of the common architectural models are −
 Client - Server Architecture for DDBMS
 Peer - to - Peer Architecture for DDBMS
 Multi - DBMS Architecture
Client - Server Architecture for DDBMS
This is a two-level architecture where the functionality is divided into servers and clients. The
server functions primarily encompass data management, query processing, optimization and
transaction management. Client functions include mainly user interface. However, they have
some functions like consistency checking and transaction management.
The two different client - server architecture are −
 Single Server Multiple Client
 Multiple Server Multiple Client (shown in the following diagram)

DIWAKAR EDUCATION HUB Page 91

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Peer- to-Peer Architecture for DDBMS

In these systems, each peer acts both as a client and a server for imparting database services.
The peers share their resource with other peers and co-ordinate their activities.
This architecture generally has four levels of schemas −
 Global Conceptual Schema − Depicts the global logical view of data.
 Local Conceptual Schema − Depicts logical data organization at each site.
 Local Internal Schema − Depicts physical data organization at each site.
 External Schema − Depicts user view of data.

DIWAKAR EDUCATION HUB Page 92

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Multi - DBMS Architectures
This is an integrated database system formed by a collection of two or more autonomous
database systems.
Multi-DBMS can be expressed through six levels of schemas −
 Multi-database View Level − Depicts multiple user views comprising of subsets of the
integrated distributed database.
 Multi-database Conceptual Level − Depicts integrated multi-database that comprises of
global logical multi-database structure definitions.
 Multi-database Internal Level − Depicts the data distribution across different sites and
multi-database to local data mapping.
 Local database View Level − Depicts public view of local data.
 Local database Conceptual Level − Depicts local data organization at each site.
 Local database Internal Level − Depicts physical data organization at each site.
There are two design alternatives for multi-DBMS −
 Model with multi-database conceptual level.
 Model without multi-database conceptual level.

DIWAKAR EDUCATION HUB Page 93

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Design Alternatives
The distribution design alternatives for the tables in a DDBMS are as follows −
 Non-replicated and non-fragmented
 Fully replicated
 Partially replicated
 Fragmented
 Mixed
Non-replicated & Non-fragmented
In this design alternative, different tables are placed at different sites. Data is placed so that it
is at a close proximity to the site where it is used most. It is most suitable for database systems
where the percentage of queries needed to join information in tables placed at different sites
is low. If an appropriate distribution strategy is adopted, then this design alternative helps to
reduce the communication cost during data processing.
Fully Replicated
In this design alternative, at each site, one copy of all the database tables is stored. Since, each
site has its own copy of the entire database, queries are very fast requiring negligible
communication cost. On the contrary, the massive redundancy in data requires huge cost
during update operations. Hence, this is suitable for systems where a large number of queries
is required to be handled whereas the number of database updates is low.
Partially Replicated
Copies of tables or portions of tables are stored at different sites. The distribution of the tables
is done in accordance to the frequency of access. This takes into consideration the fact that
the frequency of accessing the tables vary considerably from site to site. The number of copies
of the tables (or portions) depends on how frequently the access queries execute and the site
which generate the access queries.

DIWAKAR EDUCATION HUB Page 94

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Fragmented
In this design, a table is divided into two or more pieces referred to as fragments or partitions,
and each fragment can be stored at different sites. This considers the fact that it seldom
happens that all data stored in a table is required at a given site. Moreover, fragmentation
increases parallelism and provides better disaster recovery. Here, there is only one copy of
each fragment in the system, i.e. no redundant data.
The three fragmentation techniques are −
 Vertical fragmentation
 Horizontal fragmentation
 Hybrid fragmentation
Mixed Distribution
This is a combination of fragmentation and partial replications. Here, the tables are initially
fragmented in any form (horizontal or vertical), and then these fragments are partially
replicated across the different sites according to the frequency of accessing the fragments.
DBMS Architecture
In client server computing, the clients requests a resource and the server provides that
resource. A server may serve multiple clients at the same time while a client is in contact with
only one server.
 The DBMS design depends upon its architecture. The basic client/server architecture is
used to deal with a large number of PCs, web servers, database servers and other
components that are connected with networks.
 The client/server architecture consists of many PCs and a workstation which are
connected via the network.
 DBMS architecture depends upon how users are connected to the database to get their
request done.
Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database
architecture is of two types like: 2-tier architecture and 3-tier architecture.

DIWAKAR EDUCATION HUB Page 95

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The different structures for two tier and three tier are given as follows −
Two - Tier Client/Server Architecture
The two tier architecture primarily has two parts, a client tier and a server [Link] client tier
sends a request to the server tier and the server tier responds with the desired information.
An example of a two tier client/server structure is a web server. It returns the required web
pages to the clients that requested them.
An illustration of the two-tier client/server structure is as follows −

Advantages of Two - Tier Client/Server Architecture

Some of the advantages of the two-tier client/server structure are −
 This structure is quite easy to maintain and modify.
 The communication between the client and server in the form of request response
messages is quite fast.
Disadvantages of Two - Tier Client/Server Architecture
A major disadvantage of the two-tier client/server structure is −
 If the client nodes are increased beyond capacity in the structure, then the server is not
able to handle the request overflow and performance of the system degrades.
Three - Tier Client/Server Architecture
The three tier architecture has three layers namely client, application and data layer. The
client layer is the one that requests the information. In this case it could be the GUI, web
interface etc. The application layer acts as an interface between the client and data layer. It
helps in communication and also provides security. The data layer is the one that actually
contains the required data.
An illustration of the three-tier client/server structure is as follows −

DIWAKAR EDUCATION HUB Page 96

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Advantages of Three - Tier Client/Server Architecture

Some of the advantages of the three-tier client/server structure are −
 The three tier structure provides much better service and fast performance.
 The structure can be scaled according to requirements without any problem.
 Data security is much improved in the three tier structure.
Disadvantages of Three - Tier Client/Server Architecture
A major disadvantage of the three-tier client/server structure is −
 Three - tier client/server structure is quite complex due to advanced features.

Data Modeling Life Cycle

It is a straight forward process of transforming the business requirements to fulfill the goals for
storing, maintaining, and accessing the data within IT systems. The result is a logical and
physical data model for an enterprise data warehouse.
The objective of the data modeling life cycle is primarily the creation of a storage area for
business information. That area comes from the logical and physical data modeling stages, as
shown in Figure:

Conceptual Data Model

A conceptual data model recognizes the highest-level relationships between the different
entities.
DIWAKAR EDUCATION HUB Page 97
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Characteristics of the conceptual data model
 It contains the essential entities and the relationships among them.
 No attribute is specified.
 No primary key is specified.
We can see that the only data shown via the conceptual data model is the entities that define
the data and the relationships between those entities. No other data, as shown through the
conceptual data model.

Logical Data Model

A logical data model defines the information in as much structure as possible, without
observing how they will be physically achieved in the database. The primary objective of
logical data modeling is to document the business data structures, processes, rules, and
relationships by a single view - the logical data model.
Features of a logical data model
 It involves all entities and relationships among them.
 All attributes for each entity are specified.
 The primary key for each entity is stated.
 Referential Integrity is specified (FK Relation).
The phase for designing the logical data model which are as follows:
 Specify primary keys for all entities.
 List the relationships between different entities.
 List all attributes for each entity.
 Normalization.
 No data types are listed

DIWAKAR EDUCATION HUB Page 98

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Physical Data Model

Physical data model describes how the model will be presented in the database. A physical
database model demonstrates all table structures, column names, data types, constraints,
primary key, foreign key, and relationships between tables. The purpose of physical data
modeling is the mapping of the logical data model to the physical structures of the RDBMS
system hosting the data warehouse. This contains defining physical RDBMS structures, such as
tables and data types to use when storing the information. It may also include the definition of
new data structures for enhancing query performance.
Characteristics of a physical data model
o Specification all tables and columns.
o Foreign keys are used to recognize relationships between tables.
The steps for physical data model design which are as follows:
 Convert entities to tables.
 Convert relationships to foreign keys.
 Convert attributes to columns.

DIWAKAR EDUCATION HUB Page 99

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Types of Data Warehouse Models

Enterprise Warehouse

DIWAKAR EDUCATION HUB Page 100

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
An Enterprise warehouse collects all of the records about subjects spanning the entire
organization. It supports corporate-wide data integration, usually from one or more
operational systems or external data providers, and it's cross-functional in scope. It generally
contains detailed information as well as summarized information and can range in estimate
from a few gigabyte to hundreds of gigabytes, terabytes, or beyond.
An enterprise data warehouse may be accomplished on traditional mainframes, UNIX super
servers, or parallel architecture platforms. It required extensive business modeling and may
take years to develop and build.
Data Mart
A data mart includes a subset of corporate-wide data that is of value to a specific collection of
users. The scope is confined to particular selected subjects. For example, a marketing data
mart may restrict its subjects to the customer, items, and sales. The data contained in the data
marts tend to be summarized.
Data Marts is divided into two parts:
Independent Data Mart: Independent data mart is sourced from data captured from one or
more operational systems or external data providers, or data generally locally within a
different department or geographic area.
Dependent Data Mart: Dependent data marts are sourced exactly from enterprise data-
warehouses.
Virtual Warehouses
Virtual Data Warehouses is a set of perception over the operational database. For effective
query processing, only some of the possible summary vision may be materialized. A virtual
warehouse is simple to build but required excess capacity on operational database servers.
Concept Hierarchy
A concept hierarchy defines a sequence of mappings from a set of low-level concepts to
higher-level, more general concepts. Consider a concept hierarchy for the dimension location.
City values for location include Vancouver, Toronto, New York, and Chicago. Each city,
however, can be mapped to the province or state to which it belongs. For example, Vancouver
can be mapped to British Columbia, and Chicago to Illinois. The provinces and states can in
turn be mapped to the country (e.g., Canada or the United States) to which they belong. These
mappings form a concept hierarchy for the dimension location, mapping a set of low-level
concepts (i.e., cities) to higher-level, more general concepts (i.e., countries). This concept
hierarchy is illustrated in Figure 4.9.

DIWAKAR EDUCATION HUB Page 101

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Figure 4.9. A concept hierarchy for location. Due to space limitations, not all of the hierarchy
nodes are shown, indicated by ellipses between nodes.
Many concept hierarchies are implicit within the database schema. For example, suppose that
the dimension location is described by the attributes number, street, city, province_or_state,
zip_code, and country. These attributes are related by a total order, forming a concept
hierarchy such as “street < city < province_or_state < country.” This hierarchy is shown
in Figure 4.10(a). Alternatively, the attributes of a dimension may be organized in a partial
order, forming a lattice. An example of a partial order for the time dimension based on the
attributes day, week, month, quarter, and year is “day <{month < quarter; week} < year.”1 This
lattice structure is shown in Figure 4.10(b). A concept hierarchy that is a total or partial order
among attributes in a database schema is called a schema hierarchy. Concept hierarchies that
are common to many applications (e.g., for time) may be predefined in the data mining
system. Data mining systems should provide users with the flexibility to tailor predefined
hierarchies according to their particular needs. For example, users may want to define a fiscal
year starting on April 1 or an academic year starting on September 1.

DIWAKAR EDUCATION HUB Page 102

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Figure 4.10. Hierarchical and lattice structures of attributes in warehouse dimensions: (a) a
hierarchy for location and (b) a lattice for time.
Concept hierarchies may also be defined by discretizing or grouping values for a given
dimension or attribute, resulting in a set-grouping hierarchy. A total or partial order can be
defined among groups of values. An example of a set-grouping hierarchy is shown in Figure
4.11 for the dimension price, where an interval ($X…$Y] denotes the range from $X (exclusive)
to $Y (inclusive).

Figure 4.11. A concept hierarchy for price.

There may be more than one concept hierarchy for a given attribute or dimension, based on
different user viewpoints. For instance, a user may prefer to organize price by defining ranges
for inexpensive, moderately_priced, and expensive.
Concept hierarchies may be provided manually by system users, domain experts, or
knowledge engineers, or may be automatically generated based on statistical analysis of the
data distribution. The automatic generation of concept hierarchies is discussed in Chapter 3 as
a preprocessing step in preparation for data mining.
OLTP and OLAP: The two terms look similar but refer to different kinds of systems. Online
transaction processing (OLTP) captures, stores, and processes data from transactions in real
time. Online analytical processing (OLAP) uses complex queries to analyze aggregated
historical data from OLTP systems.
OLTP
An OLTP system captures and maintains transaction data in a database. Each transaction
involves individual database records made up of multiple fields or columns. Examples include
banking and credit card activity or retail checkout scanning.
In OLTP, the emphasis is on fast processing, because OLTP databases are read, written, and
updated frequently. If a transaction fails, built-in system logic ensures data integrity.
OLAP
OLAP applies complex queries to large amounts of historical data, aggregated from OLTP
databases and other sources, for data mining, analytics, and business intelligence projects. In
OLAP, the emphasis is on response time to these complex queries. Each query involves one or
more columns of data aggregated from many rows. Examples include year-over-year financial
DIWAKAR EDUCATION HUB Page 103
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
performance or marketing lead generation trends. OLAP databases and data warehouses give
analysts and decision-makers the ability to use custom reporting tools to turn data into
information. Query failure in OLAP does not interrupt or delay transaction processing for
customers, but it can delay or impact the accuracy of business intelligence insights.
OLTP vs. OLAP: side-by-side comparison
OLTP is operational, while OLAP is informational. A glance at the key features of both kinds of
processing illustrates their fundamental differences, and how they work together.
OLTP provides an immediate record of current business activity, while OLAP generates and
validates insights from that data as it’s compiled over time. That historical perspective
empowers accurate forecasting, but as with all business intelligence, the insights generated
with OLAP are only as good as the data pipeline from which they emanate.
Association rules
Association rules are if-then statements that help to show the probability of relationships
between data items within large data sets in various types of databases. Association rule
mining has a number of applications and is widely used to help discover sales correlations
in transactional data or in medical data sets.
Association rule mining finds interesting associations and relationships among large sets of
data items. This rule shows how frequently a itemset occurs in a transaction. A typical example
is Market Based Analysis.
Market Based Analysis is one of the key techniques used by large relations to show
associations between [Link] allows retailers to identify relationships between the items that
people buy together frequently.
Given a set of transactions, we can find rules that will predict the occurrence of an item based
on the occurrences of other items in the transaction.
TID ITEMS

1 Bread, Milk

2 Bread, Diaper, Beer, Eggs

3 Milk, Diaper, Beer, Coke

4 Bread, Milk, Diaper, Beer

5 Bread, Milk, Diaper, Coke

Before starting first see the basic definitions.
Support Count( ) – Frequency of occurrence of a itemset.
Here ({Milk, Bread, Diaper})=2
Frequent Itemset – An itemset whose support is greater than or equal to minsup threshold.
Association Rule – An implication expression of the form X -> Y, where X and Y are any 2
itemsets.
DIWAKAR EDUCATION HUB Page 104
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Example: {Milk, Diaper}->{Beer}
Rule Evaluation Metrics –
 Support(s) –
The number of transactions that include items in the {X} and {Y} parts of the rule as a
percentage of the total number of [Link] is a measure of how frequently the
collection of items occur together as a percentage of all transactions.
 Support = (X+Y) total –
It is interpreted as fraction of transactions that contain both X and Y.
 Confidence(c) –
It is the ratio of the no of transactions that includes all items in {B} as well as the no of
transactions that includes all items in {A} to the no of transactions that includes all items
in {A}.
 Conf(X=>Y) = Supp(X Y) Supp(X) –
It measures how often each item in Y appears in transactions that contains items in X
also.
 Lift(l) –
The lift of the rule X=>Y is the confidence of the rule divided by the expected
confidence, assuming that the itemsets X and Y are independent of each [Link]
expected confidence is the confidence divided by the frequency of {Y}.
 Lift(X=>Y) = Conf(X=>Y) Supp(Y) –
Lift value near 1 indicates X and Y almost often appear together as expected, greater
than 1 means they appear together more than expected and less than 1 means they
appear less than [Link] lift values indicate stronger association.
Example – From the above table, {Milk, Diaper}=>{Beer}
s= ({Milk, Diaper, Beer}) |T|
= 2/5
= 0.4
c= (Milk, Diaper, Beer) (Milk, Diaper)
= 2/3
= 0.67
l= Supp({Milk, Diaper, Beer}) Supp({Milk, Diaper})*Supp({Beer})
= 0.4/(0.6*0.6)
= 1.11
The Association rule is very useful in analyzing datasets. The data is collected using bar-code
scanners in supermarkets. Such databases consists of a large number of transaction records
which list all items bought by a customer on a single purchase. So the manager could know if
certain groups of items are consistently purchased together and use this data for adjusting
store layouts, cross-selling, promotions based on statistics.
Classification
Classification is a data mining function that assigns items in a collection to target categories or
classes. The goal of classification is to accurately predict the target class for each case in the
DIWAKAR EDUCATION HUB Page 105
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
data. For example, a classification model could be used to identify loan applicants as low,
medium, or high credit risks.
A classification task begins with a data set in which the class assignments are known. For
example, a classification model that predicts credit risk could be developed based on observed
data for many loan applicants over a period of time. In addition to the historical credit rating,
the data might track employment history, home ownership or rental, years of residence,
number and type of investments, and so on. Credit rating would be the target, the other
attributes would be the predictors, and the data for each customer would constitute a case.
Classifications are discrete and do not imply order. Continuous, floating-point values would
indicate a numerical, rather than a categorical, target. A predictive model with a numerical
target uses a regression algorithm, not a classification algorithm.
The simplest type of classification problem is binary classification. In binary classification, the
target attribute has only two possible values: for example, high credit rating or low credit
rating. Multiclass targets have more than two values: for example, low, medium, high, or
unknown credit rating.
In the model build (training) process, a classification algorithm finds relationships between the
values of the predictors and the values of the target. Different classification algorithms use
different techniques for finding relationships. These relationships are summarized in a model,
which can then be applied to a different data set in which the class assignments are unknown.
Classification models are tested by comparing the predicted values to known target values in a
set of test data. The historical data for a classification project is typically divided into two data
sets: one for building the model; the other for testing the model. See "Testing a Classification
Model".
Scoring a classification model results in class assignments and probabilities for each case. For
example, a model that classifies customers as low, medium, or high value would also predict
the probability of each classification for each customer.
Classification has many applications in customer segmentation, business modeling, marketing,
credit analysis, and biomedical and drug response modeling.
A Sample Classification Problem
Suppose we want to predict which of our customers are likely to increase spending if given an
affinity card. You could build a model using demographic data about customers who have used
an affinity card in the past. Since we want to predict either a positive or a negative response
(will or will not increase spending), we will build a binary classification model.
This example uses classification model, dt_sh_clas_sample, which is created by one of the
Oracle Data Mining sample programs (described in Oracle Data Mining Administrator's
Guide). Figure 5-1 shows six columns and ten rows from the case table used to build the
model. A target value of 1 has been assigned to customers who increased spending with an
affinity card; a value of 0 has been assigned to customers who did not increase spending.
Figure 5-1 Sample Build Data for Classification

DIWAKAR EDUCATION HUB Page 106

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

After undergoing testing (see "Testing a Classification Model"), the model can be applied to
the data set that you wish to mine.
Figure 5-2 shows some of the predictions generated when the model is applied to the
customer data set provided with the Oracle Data Mining sample programs. It displays several
of the predictors along with the prediction (1=will increase spending; 0=will not increase
spending) and the probability of the prediction for each customer.
Figure 5-2 Classification Results in Oracle Data Miner

Description of "Figure 5-2 Classification Results in Oracle Data Miner"

Note:
Oracle Data Miner displays the generalized case ID in the DMR$CASE_ID column of the apply
output table. A "1" is appended to the column name of each predictor that you choose to
include in the output. The predictions (affinity card usage in Figure 5-2) are displayed in
the PREDICTION column. The probability of each prediction is displayed in
the PROBABILITY column. For decision trees, the node is displayed in the NODE column.

DIWAKAR EDUCATION HUB Page 107

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Since this classification model uses the Decision Tree algorithm, rules are generated with the
predictions and probabilities. With the Oracle Data Miner Rule Viewer, you can see the rule
that produced a prediction for a given node in the tree. Figure 5-3 shows the rule for node 5.
The rule states that married customers who have a college degree (Associates, Bachelor,
Masters, Ph.D., or professional) are likely to increase spending with an affinity card.
Figure 5-3 Decision Tree Rules for Classification

Description of "Figure 5-3 Decision Tree Rules for Classification"

Testing a Classification Model
A classification model is tested by applying it to test data with known target values and
comparing the predicted values with the known values.
The test data must be compatible with the data used to build the model and must be prepared
in the same way that the build data was prepared. Typically the build data and test data come
from the same historical data set. A percentage of the records is used to build the model; the
remaining records are used to test the model.
Test metrics are used to assess how accurately the model predicts the known values. If the
model performs well and meets the business requirements, it can then be applied to new data
to predict the future.
Accuracy
Accuracy refers to the percentage of correct predictions made by the model when compared
with the actual classifications in the test data. Figure 5-4 shows the accuracy of a binary
classification model in Oracle Data Miner.
Figure 5-4 Accuracy of a Binary Classification Model

DIWAKAR EDUCATION HUB Page 108

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Description of "Figure 5-4 Accuracy of a Binary Classification Model"

Confusion Matrix
A confusion matrix displays the number of correct and incorrect predictions made by the
model compared with the actual classifications in the test data. The matrix is n-by-n,
where n is the number of classes.
Figure 5-5 shows a confusion matrix for a binary classification model. The rows present the
number of actual classifications in the test data. The columns present the number of predicted
classifications made by the model.
Figure 5-5 Confusion Matrix for a Binary Classification Model

Description of "Figure 5-5 Confusion Matrix for a Binary Classification Model"

In this example, the model correctly predicted the positive class for affinity_card 516 times
and incorrectly predicted it 25 times. The model correctly predicted the negative class
for affinity_card 725 times and incorrectly predicted it 10 times. The following can be
computed from this confusion matrix:
 The model made 1241 correct predictions (516 + 725).

DIWAKAR EDUCATION HUB Page 109

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 The model made 35 incorrect predictions (25 + 10).
 There are 1276 total scored cases (516 + 25 + 10 + 725).
 The error rate is 35/1276 = 0.0274.
 The overall accuracy rate is 1241/1276 = 0.9725.
Algorithm
A case is classified by a majority vote of its neighbors, with the case being assigned
to the class most common amongst its K nearest neighbors measured by a distance
function. If K = 1, then the case is simply assigned to the class of its nearest
neighbor.

It should also be noted that all three distance measures are only valid for continuous
variables. In the instance of categorical variables the Hamming distance must be
used. It also brings up the issue of standardization of the numerical variables
between 0 and 1 when there is a mixture of numerical and categorical variables in
the dataset.

DIWAKAR EDUCATION HUB Page 110

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Choosing the optimal value for K is best done by first inspecting the data. In general,
a large K value is more precise as it reduces the overall noise but there is no
guarantee. Cross-validation is another way to retrospectively determine a good K
value by using an independent dataset to validate the K value. Historically, the
optimal K for most datasets has been between 3-10. That produces much better
results than 1NN.
Example:
Consider the following data concerning credit default. Age and Loan are two
numerical variables (predictors) and Default is the target.

We can now use the training set to classify an unknown case (Age=48 and
Loan=$142,000) using Euclidean distance. If K=1 then the nearest neighbor is the last
case in the training set with Default=Y.

D = Sqrt[(48-33)^2 + (142000-150000)^2] = 8000.01 >> Default=Y

DIWAKAR EDUCATION HUB Page 111

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

With K=3, there are two Default=Y and one Default=N out of three closest
neighbors. The prediction for the unknown case is again Default=Y.

Standardized Distance
One major drawback in calculating distance measures directly from the training set
is in the case where variables have different measurement scales or there is a
mixture of numerical and categorical variables. For example, if one variable is based
on annual income in dollars, and the other is based on age in years then income will
have a much higher influence on the distance calculated. One solution is to
standardize the training set as shown below.

Hidden Markov Model (HMM)

DIWAKAR EDUCATION HUB Page 112

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
A hidden Markov model (HMM) is a kind of statistical model that is a variation on the Markov
chain. In a hidden Markov model, there are "hidden" states, or unobserved, in contrast to a
standard Markov chain where all states are visible to the observer. Hidden Markov models are
used for machine learning and data mining tasks including speech, handwriting and gesture
recognition.
Hidden Markov Model (HMM)
The hidden Markov model was developed by the mathematician L.E. Baum and his colleagues
in the 1960s. Like the popular Markov chain, the hidden Markov model attempts to predict the
future state of a variable using probabilities based on the current and past state. The key
difference between a Markov chain and the hidden Markov model is that the state in the
latter is not directly visible to an observer, even though the output is.
Hidden Markov models are used for machine learning and data mining tasks. Some of these
include speech recognition, handwriting recognition, part-of-speech tagging and
bioinformatics.
Dependency Modeling
Dependency Modeling consists of finding a model which describes significant dependencies
between variables.
Dependency models exist at two levels:
1. The structural level of the model specifies (often graphically) which variables are locally
dependent on each other, and
2. The quantitative level of the model specifies the strengths of the dependencies using
some numerical scale.
Link Analysis
Link analysis is a data analysis technique used in network theory that is used to evaluate the
relationships or connections between network nodes. These relationships can be between
various types of objects (nodes), including people, organizations and even transactions.
Link analysis is essentially a kind of knowledge discovery that can be used to visualize data to
allow for better analysis, especially in the context of links, whether Web links or relationship
links between people or between different entities. Link analysis is often used in search engine
optimization as well as in intelligence, in security analysis and in market and medical research.
Link Analysis
Link analysis is literally about analyzing the links between objects, whether they are physical,
digital or relational. This requires diligent data gathering. For example, in the case of a website
where all of the links and backlinks that are present must be analyzed, a tool has to sift
through all of the HTML codes and various scripts in the page and then follow all the links it
finds in order to determine what sort of links are present and whether they are active or dead.
This information can be very important for search engine optimization, as it allows the analyst
to determine whether the search engine is actually able to find and index the website.
In networking, link analysis may involve determining the integrity of the connection between
each network node by analyzing the data that passes through the physical or virtual links. With
the data, analysts can find bottlenecks and possible fault areas and are able to patch them up
more quickly or even help with network optimization.
DIWAKAR EDUCATION HUB Page 113
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Link analysis has three primary purposes:
 Find matches for known patterns of interests between linked objects.
 Find anomalies by detecting violated known patterns.
 Find new patterns of interest (for example, in social networking and marketing and
business intelligence).

Social Network Analysis (SNA)

SNA usually refers to varied information and knowledge entities, but most actual studies focus
on human (node) and relational (tie) analysis. The tie value is social capital.
SNA is often diagrammed with points (nodes) and lines (ties) to present the intricacies related
to social networking. Professional researchers perform analysis using software and unique
theories and methodologies.

SNA research is conducted in either of the following ways:

 Studying the complete social network, including all ties in a defined population.
 Studying egocentric components, including all ties and personal communities, which
involves studying relationship between the focal points in the network and the social
ties they make in their communities.
A snowball network forms when alters become egos and can create, or nominate, additional
alters. Conducting snowball studies is difficult, due to logistical limitations. The abstract SNA
concept is complicated further by studying hybrid networks, in which complete networks may
create unlisted alters available for ego observation. Hybrid networks are analogous to
employees affected by outside consultants, where data collection is not thoroughly defined.

Three analytical tendencies make SNA distinctive, as follows:

 Groups are not assumed to be societal building blocks.
 Studies focus on how ties affect individuals and other relationships, versus discrete
individuals, organizations or states.
 Studies focus on structure, the composition of ties and how they affect societal norms,
versus assuming that socialized norms determine behavior.
Sequence mining
Sequence mining has already proven to be quite beneficial in many domains such as marketing
analysis or Web click-stream analysis. A sequence s is defined as a set of ordered items
denoted by 〈s1,s2,⋯,sn〉. In activity recognition problems, the sequence is typically ordered
using timestamps. The goal of sequence mining is to discover interesting patterns in data with
respect to some subjective or objective measure of how interesting it is. Typically, this task
involves discovering frequent sequential patterns with respect to a frequency support
measure.
The task of discovering all the frequent sequences is not a trivial one. In fact, it can be quite
challenging due to the combinatorial and exponential search space [19]. Over the past decade,
a number of sequence mining methods have been proposed that handle the exponential

DIWAKAR EDUCATION HUB Page 114

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
search by using various heuristics. The first sequence mining algorithm was called GSP , which
was based on the a priori approach for mining frequent itemsets. GSP makes several passes
over the database to count the support of each sequence and to generate candidates. Then, it
prunes the sequences with a support count below the minimum support.
Many other algorithms have been proposed to extend the GSP algorithm. One example is the
PSP algorithm, which uses a prefix-based tree to represent candidate patterns [38].
FREESPAN [26] and PREFIXSPAN are among the first algorithms to consider a projection
method for mining sequential patterns, by recursively projecting sequence databases into
smaller projected databases. SPADE is another algorithm that needs only three passes over the
database to discover sequential patterns. SPAM was the first algorithm to use a vertical
bitmap representation of a database. Some other algorithms focus on discovering specific
types of frequent patterns. For example, BIDE is an efficient algorithm for mining frequent
closed sequences without candidate maintenance; there are also methods for constraint-
based sequential pattern mining
Big Data
According to Gartner, the definition of Big Data –
“Big data” is high-volume, velocity, and variety information assets that demand cost-effective,
innovative forms of information processing for enhanced insight and decision making.”
This definition clearly answers the “What is Big Data?” question – Big Data refers to complex
and large data sets that have to be processed and analyzed to uncover valuable information
that can benefit businesses and organizations.
However, there are certain basic tenets of Big Data that will make it even simpler to answer
what is Big Data:
 It refers to a massive amount of data that keeps on growing exponentially with time.
 It is so voluminous that it cannot be processed or analyzed using conventional data
processing techniques.
 It includes data mining, data storage, data analysis, data sharing, and data visualization.
 The term is an all-comprehensive one including data, data frameworks, along with the
tools and techniques used to process and analyze the data.
Types of Big Data
Now that we are on track with what is big data, let’s have a look at the types of big data:
Structured
Structured is one of the types of big data and By structured data, we mean data that can be
processed, stored, and retrieved in a fixed format. It refers to highly organized information
that can be readily and seamlessly stored and accessed from a database by simple search
engine algorithms. For instance, the employee table in a company database will be
structured as the employee details, their job positions, their salaries, etc., will be present in
an organized manner.
Unstructured
Unstructured data refers to the data that lacks any specific form or structure whatsoever. This
makes it very difficult and time-consuming to process and analyze unstructured data. Email is

DIWAKAR EDUCATION HUB Page 115

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
an example of unstructured data. Structured and unstructured are two important types of big
data.
Semi-structured
Semi structured is the third type of big data. Semi-structured data pertains to the data
containing both the formats mentioned above, that is, structured and unstructured data. To
be precise, it refers to the data that although has not been classified under a particular
repository (database), yet contains vital information or tags that segregate individual elements
within the data. Thus we come to the end of types of data. Lets discuss the characteristics of
data.
Characteristics of Big Data
Back in 2001, Gartner analyst Doug Laney listed the 3 ‘V’s of Big Data – Variety, Velocity, and
Volume. Let’s discuss the characteristics of big data.
These characteristics, isolatedly, are enough to know what is big data. Let’s look at them in
depth:
1) Variety
Variety of Big Data refers to structured, unstructured, and semistructured data that is
gathered from multiple sources. While in the past, data could only be collected from
spreadsheets and databases, today data comes in an array of forms such as emails, PDFs,
photos, videos, audios, SM posts, and so much more. Variety is one of the important
characteristics of big data.
2) Velocity
Velocity essentially refers to the speed at which data is being created in real-time. In a broader
prospect, it comprises the rate of change, linking of incoming data sets at varying speeds, and
activity bursts.
3) Volume
Volume is one of the characteristics of big data. We already know that Big Data indicates huge
‘volumes’ of data that is being generated on a daily basis from various sources like social
media platforms, business processes, machines, networks, human interactions, etc. Such a
large amount of data are stored in data warehouses. Thus comes to the end of characteristics
of big data.

Advantages of Big Data (Features)

 One of the biggest advantages of Big Data is predictive analysis. Big Data analytics tools
can predict outcomes accurately, thereby, allowing businesses and organizations to
make better decisions, while simultaneously optimizing their operational efficiencies
and reducing risks.
 By harnessing data from social media platforms using Big Data analytics tools,
businesses around the world are streamlining their digital marketing strategies to
enhance the overall consumer experience. Big Data provides insights into the customer
pain points and allows companies to improve upon their products and services.
 Being accurate, Big Data combines relevant data from multiple sources to produce
highly actionable insights. Almost 43% of companies lack the necessary tools to filter out
DIWAKAR EDUCATION HUB Page 116
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
irrelevant data, which eventually costs them millions of dollars to hash out useful data
from the bulk. Big Data tools can help reduce this, saving you both time and money.
 Big Data analytics could help companies generate more sales leads which would
naturally mean a boost in revenue. Businesses are using Big Data analytics tools to
understand how well their products/services are doing in the market and how the
customers are responding to them. Thus, the can understand better where to invest
their time and money.
 With Big Data insights, you can always stay a step ahead of your competitors. You can
screen the market to know what kind of promotions and offers your rivals are providing,
and then you can come up with better offers for your customers. Also, Big Data insights
allow you to learn customer behavior to understand the customer trends and provide a
highly ‘personalized’ experience to them.
Who is using Big Data? 5 Applications
The people who’re using Big Data know better that, what is Big Data. Let’s look at some such
industries:
1) Healthcare
Big Data has already started to create a huge difference in the healthcare sector. With the help
of predictive analytics, medical professionals and HCPs are now able to provide personalized
healthcare services to individual patients. Apart from that, fitness wearables, telemedicine,
remote monitoring – all powered by Big Data and AI – are helping change lives for the better.
2) Academia
Big Data is also helping enhance education today. Education is no more limited to the physical
bounds of the classroom – there are numerous online educational courses to learn from.
Academic institutions are investing in digital courses powered by Big Data technologies to aid
the all-round development of budding learners.
3) Banking
The banking sector relies on Big Data for fraud detection. Big Data tools can efficiently detect
fraudulent acts in real-time such as misuse of credit/debit cards, archival of inspection tracks,
faulty alteration in customer stats, etc.
4) Manufacturing
According to TCS Global Trend Study, the most significant benefit of Big Data in
manufacturing is improving the supply strategies and product quality. In the manufacturing
sector, Big data helps create a transparent infrastructure, thereby, predicting uncertainties
and incompetencies that can affect the business adversely.
5) IT
One of the largest users of Big Data, IT companies around the world are using Big Data to
optimize their functioning, enhance employee productivity, and minimize risks in business
operations. By combining Big Data technologies with ML and AI, the IT sector is continually
powering innovation to find solutions even for the most complex of problems.
6. Retail

DIWAKAR EDUCATION HUB Page 117

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Big Data has changed the way of working in traditional brick and mortar retail stores. Over the
years, retailers have collected vast amounts of data from local demographic surveys, POS
scanners, RFID, customer loyalty cards, store inventory, and so on. Now, they’ve started to
leverage this data to create personalized customer experiences, boost sales, increase revenue,
and deliver outstanding customer service.
Retailers are even using smart sensors and Wi-Fi to track the movement of customers, the
most frequented aisles, for how long customers linger in the aisles, among other things. They
also gather social media data to understand what customers are saying about their brand,
their services, and tweak their product design and marketing strategies accordingly.
7. Transportation
Big Data Analytics holds immense value for the transportation industry. In countries across the
world, both private and government-run transportation companies use Big Data technologies
to optimize route planning, control traffic, manage road congestion, and improve services.
Additionally, transportation services even use Big Data to revenue management, drive
technological innovation, enhance logistics, and of course, to gain the upper hand in the
market.
Big Data Case studies
1. Walmart
Walmart leverages Big Data and Data Mining to create personalized product
recommendations for its customers. With the help of these two emerging technologies,
Walmart can uncover valuable patterns showing the most frequently bought products, most
popular products, and even the most popular product bundles (products that complement
each other and are usually purchased together).
Based on these insights, Walmart creates attractive and customized recommendations for
individual users. By effectively implementing Data Mining techniques, the retail giant has
successfully increased the conversion rates and improved its customer service substantially.
Furthermore, Walmart uses Hadoop and NoSQL technologies to allow customers to access
real-time data accumulated from disparate sources.
2. American Express
The credit card giant leverages enormous volumes of customer data to identify indicators that
could depict user loyalty. It also uses Big Data to build advanced predictive models for
analyzing historical transactions along with 115 different variables to predict potential
customer churn. Thanks to Big Data solutions and tools, American Express can identify 24% of
the accounts that are highly likely to close in the upcoming four to five months.
3. General Electric
In the words of Jeff Immelt, Chairman of General Electric, in the past few years, GE has been
successful in bringing together the best of both worlds – “the physical and analytical worlds.”
GE thoroughly utilizes Big Data. Every machine operating under General Electric generates
data on how they work. The GE analytics team then crunches these colossal amounts of data
to extract relevant insights from it and redesign the machines and their operations
accordingly.

DIWAKAR EDUCATION HUB Page 118

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Today, the company has realized that even minor improvements, no matter how small, play a
crucial role in their company infrastructure. According to GE stats, Big Data has the potential
to boost productivity by 1.5% in the US, which compiled over a span of 20 years could increase
the average national income by a staggering 30%!
4. Uber
Uber is one of the major cab service providers in the world. It leverages customer data to
track and identify the most popular and most used services by the users. Once this data is
collected, Uber uses data analytics to analyze the usage patterns of customers and determine
which services should be given more emphasis and importance.
Apart from this, Uber uses Big Data in another unique way. Uber closely studies the demand
and supply of its services and changes the cab fares accordingly. It is the surge pricing
mechanism that works something like this – suppose when you are in a hurry, and you have to
book a cab from a crowded location, Uber will charge you double the normal amount!
5. Netflix
Netflix is one of the most popular on-demand online video content streaming platform used
by people around the world. Netflix is a major proponent of the recommendation engine. It
collects customer data to understand the specific needs, preferences, and taste patterns of
users. Then it uses this data to predict what individual users will like and create personalized
content recommendation lists for them.
Today, Netflix has become so vast that it is even creating unique content for users. Data is the
secret ingredient that fuels both its recommendation engines and new content decisions. The
most pivotal data points used by Netflix include titles that users watch, user ratings, genres
preferred, and how often users stop the playback, to name a few. Hadoop, Hive, and Pig are
the three core components of the data structure used by Netflix.
6. Procter & Gamble
Procter & Gamble has been around us for ages now. However, despite being an “old”
company, P&G is nowhere close to old in its ways. Recognizing the potential of Big Data, P&G
started implementing Big Data tools and technologies in each of its business units all over the
world. The company’s primary focus behind using Big Data was to utilize real-time insights to
drive smarter decision making.
To accomplish this goal, P&G started collecting vast amounts of structured and unstructured
data across R&D, supply chain, customer-facing operations, and customer interactions, both
from company repositories and online sources. The global brand has even developed Big Data
systems and processes to allow managers to access the latest industry data and analytics.
7. IRS
Yes, even government agencies are not shying away from using Big Data. The US Internal
Revenue Service actively uses Big Data to prevent identity theft, fraud, and untimely payments
(people who should pay taxes but don’t pay them in due time).
The IRS even harnesses the power of Big Data to ensure and enforce compliance with tax rules
and laws. As of now, the IRS has successfully averted fraud and scams involving billions of
dollars, especially in the case of identity theft. In the past three years, it has also recovered
over US$ 2 billion.

DIWAKAR EDUCATION HUB Page 119

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Introduction to MapReduce
MapReduce is a programming model for processing large data sets with a parallel ,
distributed algorithm on a cluster (source: Wikipedia). Map Reduce when coupled with HDFS
can be used to handle big data. The fundamentals of this HDFS-MapReduce system, which is
commonly referred to as Hadoop.
The basic unit of information, used in MapReduce is a (Key,value) pair. All types of structured
and unstructured data need to be translated to this basic unit, before feeding the data to
MapReduce model. As the name suggests, MapReduce model consist of two separate
routines, namely Map-function and Reduce-function. This article will help you understand the
step by step functionality of Map-Reduce [Link] computation on an input (i.e. on a set of
pairs) in MapReduce model occurs in three stages:

Step 1 : The map stage

Step 2 : The shuffle stage
Step 3 : The reduce stage.
Semantically, the map and shuffle phases distribute the data, and the reduce phase performs
the computation. In this article we will discuss about each of these stages in detail.
[stextbox id=”section”] The Map stage [/stextbox]
MapReduce logic, unlike other data frameworks, is not restricted to just structured datasets. It
has an extensive capability to handle unstructured data as well. Map stage is the critical step
which makes this possible. Mapper brings a structure to unstructured data. For instance, if I
want to count the number of photographs on my laptop by the location (city), where the
photo was taken, I need to analyze unstructured data. The mapper makes (key, value) pairs
from this data set. In this case, key will be the location and value will be the photograph. After
mapper is done with its task, we have a structure to the entire data-set.
In the map stage, the mapper takes a single (key, value) pair as input and produces any
number of (key, value) pairs as output . It is important to think of the map operation as
stateless, that is, its logic operates on a single pair at a time (even if in practice several input
pairs are delivered to the same mapper). To summarize, for the map phase, the user simply
designs a map function that maps an input (key, value) pair to any number (even none) of
output pairs. Most of the time, the map phase is simply used to specify the desired location of
the input value by changing its key.
DIWAKAR EDUCATION HUB Page 120
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
[stextbox id=”section”] The shuffle stage [/stextbox]
The shuffle stage is automatically handled by the MapReduce framework, i.e. the engineer has
nothing to do for this stage. The underlying system implementing MapReduce routes all of the
values that are associated with an individual key to the same reducer.
[stextbox id=”section”] The Reduce stage [/stextbox]
In the reduce stage, the reducer takes all of the values associated with a single key k and
outputs any number of (key, value) pairs. This highlights one of the sequential aspects of
MapReduce computation: all of the maps need to finish before the reduce stage can begin.
Since the reducer has access to all the values with the same key, it can perform sequential
computations on these values. In the reduce step, the parallelism is exploited by observing
that reducers operating on different keys can be executed simultaneously. To summarize, for
the reduce phase, the user designs a function that takes in input a list of values associated
with a single key and outputs any number of pairs. Often the output keys of a reducer equal
the input key (in fact, in the original MapReduce paper the output key must equal to the input
key, but Hadoop relaxed this constraint).
Overall, a program in the MapReduce paradigm can consist of many rounds (usually
called jobs) of different map and reduce functions, performed sequentially one after another.
[stextbox id=”section”] An example [/stextbox]
Let’s consider an example to understand Map-Reduce in depth. We have the following 3
sentences :
1. The quick brown fox
2. The fox ate the mouse
3. How now brown cow
Our objective is to count the frequency of each word in all the sentences. Imagine that each of
these sentences acquire huge memory and hence are allotted to different data nodes.
Mapper takes over this unstructured data and creates key value pairs. In this case key is the
word and value is the count of this word in the text available at this data node. For instance,
the 1st Map node generates 4 key-value pairs : (the,1), (brown,1),(fox,1), (quick,1). The first 3
key-value pairs go to the first Reducer and the last key-value go to the second Reducer.

DIWAKAR EDUCATION HUB Page 121

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Similarly, the 2nd and 3rd map functions do the mapping for the other two
sentences. Through shuffling, all the similar words come to the same end. Once, the key value
pairs are sorted, the reducer function operates on this structured data to come up with a
summary.
[stextbox id=”section”] End Notes : [/stextbox]
Let’s take some example of Map-Reduce function usage in the industry :
• At Google:
– Index building for Google Search
– Article clustering for Google News
– Statistical machine translation
• At Yahoo!:
– Index building for Yahoo! Search
– Spam detection for Yahoo! Mail
• At Facebook:
– Data mining
– Ad optimization
– Spam detection Example
• At Amazon:
– Product clustering
– Statistical machine translation
The constraint of using Map-reduce function is that user has to follow a logic format. This
logic is to generate key-value pairs using Map function and then summarize using Reduce
function. But luckily most of the data manipulation operations can be tricked into this format.

DIWAKAR EDUCATION HUB Page 122

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
In the next article we will take some example like how to do data-set merging, matrix
multiplication, matrix transpose, etc. using Map-Reduce.
Introduction to Hadoop
Hadoop is a complete eco-system of open source projects that provide us the framework to
deal with big data. Let’s start by brainstorming the possible challenges of dealing with big data
(on traditional systems) and then look at the capability of Hadoop solution.
Following are the challenges I can think of in dealing with big data :
1. High capital investment in procuring a server with high processing capacity.
2. Enormous time taken
3. In case of long query, imagine an error happens on the last step. You will waste so much
time making these iterations.
4. Difficulty in program query building
Here is how Hadoop solves all of these issues :
1. High capital investment in procuring a server with high processing capacity: Hadoop
clusters work on normal commodity hardware and keep multiple copies to ensure reliability of
data. A maximum of 4500 machines can be connected together using Hadoop.
2. Enormous time taken : The process is broken down into pieces and executed in parallel,
hence saving time. A maximum of 25 Petabyte (1 PB = 1000 TB) data can be processed using
Hadoop.
3. In case of long query, imagine an error happens on the last step. You will waste so much
time making these iterations : Hadoop builds back up data-sets at every level. It also executes
query on duplicate datasets to avoid process loss in case of individual failure. These steps
makes Hadoop processing more precise and accurate.
4. Difficulty in program query building : Queries in Hadoop are as simple as coding in any
language. You just need to change the way of thinking around building a query to enable
parallel processing.
Background of Hadoop
With an increase in the penetration of internet and the usage of the internet, the data
captured by Google increased exponentially year on year. Just to give you an estimate of this
number, in 2007 Google collected on an average 270 PB of data every month. The same
number increased to 20000 PB everyday in 2009. Obviously, Google needed a better platform
to process such an enormous data. Google implemented a programming model called
MapReduce, which could process this 20000 PB per day. Google ran these MapReduce
operations on a special file system called Google File System (GFS). Sadly, GFS is not an open
source.
Doug cutting and Yahoo! reverse engineered the model GFS and built a parallel Hadoop
Distributed File System (HDFS). The software or framework that supports HDFS and
MapReduce is known as Hadoop. Hadoop is an open source and distributed by Apache.
Framework of Hadoop Processing
Let’s draw an analogy from our daily life to understand the working of Hadoop. The bottom of
the pyramid of any firm are the people who are individual contributors. They can be analyst,

DIWAKAR EDUCATION HUB Page 123

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
programmers, manual labors, chefs, etc. Managing their work is the project manager. The
project manager is responsible for a successful completion of the task. He needs to distribute
labor, smoothen the coordination among them etc. Also, most of these firms have a people
manager, who is more concerned about retaining the head count.

Hadoop works in a similar format. On the bottom we have machines arranged in parallel.
These machines are analogous to individual contributor in our analogy. Every machine has a
data node and a task tracker. Data node is also known as HDFS (Hadoop Distributed File
System) and Task tracker is also known as map-reducers.
Data node contains the entire set of data and Task tracker does all the operations. You can
imagine task tracker as your arms and leg, which enables you to do a task and data node as
your brain, which contains all the information which you want to process. These machines are
working in silos and it is very essential to coordinate them. The Task trackers (Project manager
in our analogy) in different machines are coordinated by a Job Tracker. Job Tracker makes sure
that each operation is completed and if there is a process failure at any node, it needs to
assign a duplicate task to some task tracker. Job tracker also distributes the entire task to all
the machines.
A name node on the other hand coordinates all the data nodes. It governs the distribution of
data going to each machine. It also checks for any kind of purging which have happened on
any machine. If such purging happens, it finds the duplicate data which was sent to other data
node and duplicates it again. You can think of this name node as the people manager in our
analogy which is concerned more about the retention of the entire dataset.

DIWAKAR EDUCATION HUB Page 124

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

When not to use Hadoop ?

Till now, we have seen how Hadoop has made handling big data possible. But in some
scenarios Hadoop implementation is not recommended. Following are some of those
scenarios :
1. Low Latency data access : Quick access to small parts of data
2. Multiple data modification : Hadoop is a better fit only if we are primarily concerned
about reading data and not writing data.
3. Lots of small files : Hadoop is a better fit in scenarios, where we have few but large files.

Distributed File System (DFS)

A distributed file system (DFS) is a file system with data stored on a server. The data is
accessed and processed as if it was stored on the local client machine. The DFS makes it
convenient to share information and files among users on a network in a controlled and
authorized way. The server allows the client users to share files and store data just like they
are storing the information locally. However, the servers have full control over the data and
give access control to the clients.
Distributed File System (DFS)
There has been exceptional growth in network-based computing recently and client/server-
based applications have brought revolutions in this area. Sharing storage resources and
information on the network is one of the key elements in both local area networks (LANs) and
wide area networks (WANs). Different technologies have been developed to bring

DIWAKAR EDUCATION HUB Page 125

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
convenience to sharing resources and files on a network; a distributed file system is one of the
processes used regularly.

One process involved in implementing the DFS is giving access control and storage
management controls to the client system in a centralized way, managed by the servers.
Transparency is one of the core processes in DFS, so files are accessed, stored, and managed
on the local client machines while the process itself is actually held on the servers. This
transparency brings convenience to the end user on a client machine because the network file
system efficiently manages all the processes. Generally, a DFS is used in a LAN, but it can be
used in a WAN or over the Internet.

A DFS allows efficient and well-managed data and storage sharing options on a network
compared to other options. Another option for users in network-based computing is a shared
disk file system. A shared disk file system puts the access control on the client’s systems so the
data is inaccessible when the client system goes offline. DFS is fault-tolerant and the data is
accessible even if some of the network nodes are offline.

A DFS makes it possible to restrict access to the file system depending on access lists or
capabilities on both the servers and the clients, depending on how the protocol is designed.
HDFS
Hadoop File System was developed using distributed file system design. It is run on commodity
hardware. Unlike other distributed systems, HDFS is highly faulttolerant and designed using
low-cost hardware.
HDFS holds very large amount of data and provides easier access. To store such huge data, the
files are stored across multiple machines. These files are stored in redundant fashion to rescue
the system from possible data losses in case of failure. HDFS also makes applications available
to parallel processing.
Features of HDFS
 It is suitable for the distributed storage and processing.
 Hadoop provides a command interface to interact with HDFS.
 The built-in servers of namenode and datanode help users to easily check the status of
cluster.
 Streaming access to file system data.
 HDFS provides file permissions and authentication.
HDFS Architecture
Given below is the architecture of a Hadoop File System.

DIWAKAR EDUCATION HUB Page 126

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

HDFS follows the master-slave architecture and it has the following elements.
Namenode
The namenode is the commodity hardware that contains the GNU/Linux operating system and
the namenode software. It is a software that can be run on commodity hardware. The system
having the namenode acts as the master server and it does the following tasks −
 Manages the file system namespace.
 Regulates client’s access to files.
 It also executes file system operations such as renaming, closing, and opening files and
directories.
Datanode
The datanode is a commodity hardware having the GNU/Linux operating system and datanode
software. For every node (Commodity hardware/System) in a cluster, there will be a datanode.
These nodes manage the data storage of their system.
 Datanodes perform read-write operations on the file systems, as per client request.
 They also perform operations such as block creation, deletion, and replication according
to the instructions of the namenode.
Block
Generally the user data is stored in the files of HDFS. The file in a file system will be divided
into one or more segments and/or stored in individual data nodes. These file segments are
called as blocks. In other words, the minimum amount of data that HDFS can read or write is
called a Block. The default block size is 64MB, but it can be increased as per the need to
change in HDFS configuration.
Goals of HDFS
Fault detection and recovery − Since HDFS includes a large number of commodity hardware,
failure of components is frequent. Therefore HDFS should have mechanisms for quick and
automatic fault detection and recovery.
DIWAKAR EDUCATION HUB Page 127
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Huge datasets − HDFS should have hundreds of nodes per cluster to manage the applications
having huge datasets.
Hardware at data − A requested task can be done efficiently, when the computation takes
place near the data. Especially where huge datasets are involved, it reduces the network traffic
and increases the throughput.
NoSQL
NoSQL databases (aka "not only SQL") are non tabular, and store data differently than
relational tables. NoSQL databases come in a variety of types based on their data model. The
main types are document, key-value, wide-column, and graph. They provide flexible schemas
and scale easily with large amounts of data and high user loads.
What is NoSQL?
When people use the term “NoSQL database”, they typically use it to refer to any non-
relational database. Some say the term “NoSQL” stands for “non SQL” while others say it
stands for “not only SQL.” Either way, most agree that NoSQL databases are databases that
store data in a format other than relational tables.
A common misconception is that NoSQL databases or non-relational databases don’t store
relationship data well. NoSQL databases can store relationship data—they just store it
differently than relational databases do. In fact, when compared with SQL databases, many
find modeling relationship data in NoSQL databases to be easier than in SQL databases,
because related data doesn’t have to be split between tables.
NoSQL data models allow related data to be nested within a single data structure.
NoSQL databases emerged in the late 2000s as the cost of storage dramatically decreased.
Gone were the days of needing to create a complex, difficult-to-manage data model simply for
the purposes of reducing data duplication. Developers (rather than storage) were becoming
the primary cost of software development, so NoSQL databases optimized for developer
productivity.
The benefits of NoSQL database
Data Models
NoSQL databases often leverage data models more tailored to specific use cases, making them
better at supporting those workloads than relational databases. For example, key-value
databases support simple queries very efficiently while graph databases are the best for
queries that involve identifying complex relationships between separate pieces of data.
Performance
NoSQL databases can often perform better than SQL/relational databases for your use case.
For example, if you’re using a document database and are storing all the information about an
object in the same document (so that it matches the objects in your code), the database only
needs to go to one place for those queries. In a SQL database, the same query would likely
involve joining multiple tables and records, which can dramatically impact performance while
also slowing down how quickly developers write code.
Scalability
SQL/relational databases were originally designed to scale up and although there are ways to
get them to scale out, those solutions are often bolt-ons, complicated, expensive to manage,
DIWAKAR EDUCATION HUB Page 128
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
and hard to evolve. Some core SQL functionality also only really works well when everything is
on one server. In contrast, NoSQL databases are designed from the ground up to scale-out
horizontally, making it much easier to maintain performance as your workload grows beyond
the limits of a single server.
Data Distribution
Because NoSQL databases are designed from the ground up as distributed systems, they can
more easily support a variety of business requirements. For example, suppose the business
needs a globally distributed application that provides excellent performance to users all
around the world. NoSQL databases can allow you to deploy a single distributed cluster to
support that application and ensure low latency access to data from anywhere. This approach
also makes it much easier to comply with data sovereignty mandates required by modern
privacy regulations.
Reliability
NoSQL databases ensure high availability and uptime with native replication and built-in
failover for self-healing, resilient database clusters. Similar failover systems can be set up for
SQL databases but since the functionality is not native to the underlying database, this often
means more resources to deploy and maintain a separate clustering layer that then takes
longer to identify and recover from underlying systems failures.
Flexibility
NoSQL databases are better at allowing users to test new ideas and update data structures.
For example, MongoDB, the leading document database, stores data in flexible, JSON-like
documents, meaning fields can vary from document to document and the data structures can
be easily changed over time, as application requirements evolve. This is a better fit for modern
microservices architectures where developers are continuously integrating and deploying new
application functionality.
Queries Optimization
Queries can be executed in many different ways. All paths lead to the same query result. The
Query optimizer evaluates the possibilities and selects the efficient plan. Efficiency is
measured in latency and throughput, depending on the workload. The cost of Memory, CPU,
disk usage is added to the cost of a plan in a cost-based optimizer.
Now, most NoSQL databases have SQL-like query language support. So, a good optimizer is
mandatory. When you don't have a good optimizer, developers have to live with feature
restrictions and DBAs have to live with performance issues.
Database Optimizer
A query optimizer chooses an optimal index and access paths to execute the query. At a very
high level, SQL optimizers decide the following before creating the execution tree:
1. Query rewrite based on heuristics, cost or both.
2. Index selection.
o Selecting the optimal index(es) for each of the table (keyspaces in Couchbase
N1QL, collection in case of MongoDB)
o Depending on the index selected, choose the predicates to push down, see the
query is covered or not, decide on sort and pagination strategy.
DIWAKAR EDUCATION HUB Page 129
 DATABASE MANAGEMENT SYSTEMS UNIT – 4
3. Join reordering
4. Join type
Queries Optimization
Query optimization is the science and the art of applying equivalence rules to rewrite the tree
of operators evoked in a query and produce an optimal plan. A plan is optimal if it returns the
answer in the least time or using the least space. There are well known syntactic, logical, and
semantic equivalence rules used during optimization. These rules can be used to select an
optimal plan among semantically equivalent plans by associating a cost with each plan and
selecting the lowest overall cost. The cost associated with each plan is generated using
accurate metrics such as the cardinality or the number of result tuples in the output of each
operator, the cost of accessing a source and obtaining results from that source, and so on. One
must also have a cost formula that can calculate the processing cost for each implementation
of each operator. The overall cost is typically defined as the total time needed to evaluate the
query and obtain all of the answers.
The characterization of an optimal, low-cost plan is a difficult task. The complexity of
producing an optimal, low-cost plan for a relational query is NP-complete. However, many
efforts have produced reasonable heuristics to solve this problem. Both dynamic programming
and randomized optimization based on simulated annealing provide good solutions.
A BIS could be improved significantly by exploiting the traditional database technology for
optimization extended to capture the complex metrics presented in Section 4.4.1. Many of the
systems presented in this book address optimization at different levels. K2 uses rewriting
rules and a cost model. P/FDM combines traditional optimization strategies, such as query
rewriting and selection of the best execution plan, with a query-shipping approach.
DiscoveryLink performs two types of optimization: query rewriting followed by a cost-based
optimization plan. KIND is addressing the use of domain knowledge into executable meta-
data. The knowledge of biological resources can be used to identify the best plan with query
(Q) defined in Section 4.4.2 as illustrated in the following.
The two possible plans illustrated in Figures 4.1 and 4.2 do not have the same cost. Evaluation
costs depend on factors including the number of accesses to each data source, the size
(cardinality) of each relation or data source involved in the query, the number of results
returned or the selectivity of the query, the number of queries that are submitted to the
sources, and the order of accessing sources.
Each access to a data source retrieves many documents that need to be parsed. Each object
returned may generate further accesses to (other) sources. Web accesses are costly and
should be as limited as possible. A plan that limits the number of accesses is likely to have a
lower cost. Early selection is likely to limit the number of accesses. For example, the call to
PubMed in the plan illustrated in Figure 4.1 retrieves 81,840 citations, whereas the call to
GenBank in the plan in Figure 4.2 retrieves 1616 sequences. (Note that the statistics and
results cited in this paper were gathered between April 2001 and April 2002 and may no
longer be up to date.) If each of the retrieved documents (from PubMed or GenBank)
generated an additional access to the second source, clearly the second plan has the potential
to be much less expensive when compared to the first plan.

DIWAKAR EDUCATION HUB Page 130

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The size of the data sources involved in the query may also affect the cost of the evaluation
plan. As of May 4, 2001, Swiss-Prot contained 95,674 entries whereas PubMed contained
more than 11 million citations; these are the values of cardinality for the corresponding
relations. A query submitted to PubMed (as used in the first plan) retrieves 727,545 references
that mention brain, whereas it retrieves 206,317 references that mention brain and were
published since 1995. This is the selectivity of the query. In contrast, the query submitted to
Swiss-Prot in the second plan returns 126 proteins annotated with calcium channel.
In addition to the previously mentioned characteristics of the resources, the order of accessing
sources and the use of different capabilities of sources also affects the total cost of the plan.
The first plan accesses PubMed and extracts values for identifiers of records in Swiss-Prot from
the results. It then passes these values to the query on Swiss-Prot via the join operator. To
pass each value, the plan may have to send multiple calls to the Swiss-Prot source, one for
each value, and this can be expensive. However, by passing these values of identifiers to Swiss-
Prot, the Swiss-Prot source has the potential to constrain the query, and this could reduce the
number of results returned from Swiss-Prot. On the other hand, the second plan submits
queries in parallel to both PubMed and Swiss-Prot. It does not pass values of identifiers of
Swiss-Prot records to Swiss-Prot; consequently, more results may be returned from Swiss-Prot.
The results from both PubMed and Swiss-Prot have to be processed (joined) locally, and this
could be computationally expensive. Recall that for this plan, 206,317 PubMed references and
126 proteins from Swiss-Prot are processed locally. However, the advantage is that a single
query has been submitted to Swiss-Prot in the second plan. Also, both sources are accessed in
parallel.
Although it has not been described previously, there is a third plan that should be considered
for this query. This plan would first retrieve those proteins annotated with calcium
channel from Swiss-Prot and extract MEDLINE identifiers from these records. It would then
pass these identifiers to PubMed and restrict the results to those matching the
keyword brain. In this particular case, this third plan has the potential to be the least costly. It
submits one sub-query to Swiss-Prot, and it will not download 206,317 PubMed references.
Finally, it will not join 206,317 PubMed references and 126 proteins from Swiss-Prot locally.
Optimization has an immediate impact in the overall performance of the system. The
consequences of the inefficiency of a system to execute users’ queries may affect the
satisfaction of users as well as the capabilities of the system to return any output to the user.
NoSQL Database
Databases can be divided in 3 types:
1. RDBMS (Relational Database Management System)
2. OLAP (Online Analytical Processing)
3. NoSQL (recently developed database)
NoSQL Database
NoSQL Database is used to refer a non-SQL or non relational database.
It provides a mechanism for storage and retrieval of data other than tabular relations model
used in relational databases. NoSQL database doesn't use tables for storing data. It is generally
used to store big data and real-time web applications.

DIWAKAR EDUCATION HUB Page 131

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
History behind the creation of NoSQL Databases
In the early 1970, Flat File Systems are used. Data were stored in flat files and the biggest
problems with flat files are each company implement their own flat files and there are no
standards. It is very difficult to store data in the files, retrieve data from files because there is
no standard way to store data.
Then the relational database was created by E.F. Codd and these databases answered the
question of having no standard way to store data. But later relational database also get a
problem that it could not handle big data, due to this problem there was a need of database
which can handle every types of problems then NoSQL database was developed.
Advantages of NoSQL
o It supports query language.
o It provides fast performance.
o It provides horizontal scalability.
Indexing data sets
Indexing is a way to optimize the performance of a database by minimizing the number of disk
accesses required when a query is processed. It is a data structure technique which is used to
quickly locate and access the data in a database.
Indexes are created using a few database columns.
 The first column is the Search key that contains a copy of the primary key or candidate
key of the table. These values are stored in sorted order so that the corresponding data
can be accessed quickly.
Note: The data may or may not be stored in sorted order.
 The second column is the Data Reference or Pointer which contains a set of pointers
holding the address of the disk block where that particular key value can be found.

The indexing has various attributes:

 Access Types: This refers to the type of access such as value based search, range access,
etc.
 Access Time: It refers to the time needed to find particular data element or set of
elements.
 Insertion Time: It refers to the time taken to find the appropriate space and insert a
new data.

DIWAKAR EDUCATION HUB Page 132

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
 Deletion Time: Time taken to find an item and delete it as well as update the index
structure.
 Space Overhead: It refers to the additional space required by the index.
In general, there are two types of file organization mechanism which are followed by the
indexing methods to store the data:
1. Sequential File Organization or Ordered Index File: In this, the indices are based on a
sorted ordering of the values. These are generally fast and a more traditional type of
storing mechanism. These Ordered or Sequential file organization might store the data
in a dense or sparse format:
o Dense Index:
 For every search key value in the data file, there is an index record.
 This record contains the search key and also a reference to the first data
record with that search key value.

o Sparse Index:
 The index record appears only for a few items in the data file. Each item
points to a block as shown.
 To locate a record, we find the index record with the largest search key
value less than or equal to the search key value we are looking for.
 We start at that record pointed to by the index record, and proceed along
with the pointers in the file (that is, sequentially) until we find the desired
record.

DIWAKAR EDUCATION HUB Page 133

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

2. Hash File organization: Indices are based on the values being distributed uniformly
across a range of buckets. The buckets to which a value is assigned is determined by a
function called a hash function.
There are primarily three methods of indexing:
 Clustered Indexing
 Non-Clustered or Secondary Indexing
 Multilevel Indexing
1. Clustered Indexing
When more than two records are stored in the same file these types of storing known as
cluster indexing. By using the cluster indexing we can reduce the cost of searching
reason being multiple records related to the same thing are stored at one place and it
also gives the frequent joing of more than two tables(records).
Clustering index is defined on an ordered data file. The data file is ordered on a non-key
field. In some cases, the index is created on non-primary key columns which may not be
unique for each record. In such cases, in order to identify the records faster, we will
group two or more columns together to get the unique values and create index out of
them. This method is known as the clustering index. Basically, records with similar
characteristics are grouped together and indexes are created for these groups.
For example, students studying in each semester are grouped together. i.e. 1 st Semester
students, 2nd semester students, 3rd semester students etc are grouped.

DIWAKAR EDUCATION HUB Page 134

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Clustered index sorted according to first name (Search key)

Primary Indexing:
This is a type of Clustered Indexing wherein the data is sorted according to the search key and
the primary key of the database table is used to create the index. It is a default format of
indexing where it induces sequential file organization. As primary keys are unique and are
stored in a sorted manner, the performance of the searching operation is quite efficient.
2. Non-clustered or Secondary Indexing
A non clustered index just tells us where the data lies, i.e. it gives us a list of virtual
pointers or references to the location where the data is actually stored. Data is not
physically stored in the order of the index. Instead, data is present in leaf nodes. For eg.
the contents page of a book. Each entry gives us the page number or location of the
information stored. The actual data here(information on each page of the book) is not
organized but we have an ordered reference(contents page) to where the data points
actually lie. We can have only dense ordering in the non-clustered index as sparse
ordering is not possible because data is not physically organized accordingly.
It requires more time as compared to the clustered index because some amount of
extra work is done in order to extract the data by further following the pointer. In the
case of a clustered index, data is directly present in front of the index.

DIWAKAR EDUCATION HUB Page 135

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

3. Multilevel Indexing
With the growth of the size of the database, indices also grow. As the index is stored in
the main memory, a single-level index might become too large a size to store with
multiple disk accesses. The multilevel indexing segregates the main block into various
smaller blocks so that the same can stored in a single block. The outer blocks are divided
into inner blocks which in turn are pointed to the data blocks. This can be easily stored

DIWAKAR EDUCATION HUB Page 136

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
in the main memory with fewer overheads.

NOSQL in Cloud
With the current move to cloud computing, the need to scale applications presents itself as a
challenge for storing data. If you are using a traditional relational database you may find
yourself working on a complex policy for distributing your database load across multiple
database instances. This solution will often present a lot of problems and probably won’t be
great at elastically scaling.
As an alternative you could consider a cloud-based NoSQL database. Over the past few weeks I
have been analysing a few such offerings, each of which promises to scale as your application
grows, without requiring you to think about how you might distribute the data and load.
Specifically I have been looking at Amazon’s DynamoDB, Google’s Cloud Datastore and Cloud
BigTable. I chose to take a look into these 3 databases because we have existing applications
running in Google and Amazon’s clouds and I can see the advantage these databases can offer.
In this post I’ll report on what I’ve learnt.
Consistency, Availability & Partition Tolerance

DIWAKAR EDUCATION HUB Page 137

 DATABASE MANAGEMENT SYSTEMS UNIT – 4

Firstly — and most importantly — it’s necessary to understand that distributed NoSQL
databases achieve high scalability in comparison to a traditional RDBMS by making some
important tradeoffs.
A good starting-place for thinking about this is the CAP Theorem, which states that a
distributed database can — at most — provide two of the following: Consistency, Availability
and Partition Tolerance. We define each of these as follows:
 Consistency: All nodes contain the same data
 Availability: Every request should receive a response
 Partition Tolerance: Losing a node should not affect the system
Eventually Consistent Operations

All three NoSQL databases I looked at provide Availability and Partition Tolerance for
eventually-consistent operations. In most cases these two properties will suffice.

DIWAKAR EDUCATION HUB Page 138

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
For example, if a user posts to a social media website and it takes a second or two for
everyone’s request to pick up the change, then it’s not usually an issue.
This happens due to write operations writing to multiple nodes before the data is eventually
replicated across all of the nodes, which usually occurs within one second. Read operations are
then read from only one node.
Strongly Consistent Operations

All three databases also provide strongly consistent operations which guarantee that the latest
version of the data will always be returned.
DynamoDB achieves this by ensuring that writes are written out to the majority of nodes
before a success result is returned. Reads are also done in a similar way — results will not
return until the record is read from more then half of the nodes. This is to ensure that the
result will be the latest copy of the record.
All this occurs at the expense of availability, where a node being inaccessible can prevent the
verification of the data’s consistency if it occurs a short time after the write operation. Google
achieves this behaviour in a slightly different way by using a locking mechanism where a read
can’t be completed on a node until it has the latest copy of the data. This model is required
when you need to guarantee the consistency of your data. For example, you would not want a
financial transaction being calculated on an old version of the data.
OK, now that we’ve got the hard stuff out of the way, let’s move onto some of the more
practical questions that might come up when using a cloud-based database.
Local Development

DIWAKAR EDUCATION HUB Page 139

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Having a database in the cloud is cool, but how does it work if you’ve got a team of
developers, each of whom needs to run their own copy of the database locally? Fortunately,
DynamoDB, BigTable and Cloud Datastore all have the option of downloading and running a
local development server. All three local development environments are really easy to
download and get started with. They are designed to provide you with an interface that
matches the production environment.
Java Object Mapping
If you are going to be using Java to develop your application, you might be used to using
frameworks like Hibernate or JPA to automatically map RDBMS rows to objects. How does this
work with NoSQL databases?
DynamoDB provides an intuitive way of mapping Java classes to objects in DynamoDB Tables.
You simply annotate the Java object as a DynamoDB Table and then annotate your instance
variable getters with the appropriate annotations.
@DynamoDBTable(tableName="users")
public class User {

@DynamoDBHashKey(attributeName="username")
public String getUsername(){
return username;
}
public void setUsername(String username){
[Link] = username;
}

@DynamoDBAttribute(attributeName = "email")
public String getEmail(){
return email;
}
public void setEmail(String email){
[Link] = email;
}
Querying
An important thing to understand about all of these NoSQL databases is that they don’t
provide a full-blown query language.
Instead, you need to use their APIs and SDKs to access the database. By using simple query
and scan operations you can retrieve zero or more records from a given table. Since each of
the three databases I looked at provide a slightly different way of indexing the tables, the
range of features in this space varies.
DynamoDB for example provides multiple secondary indexes, meaning there is the ability to
efficiently scan any indexed column. This is not a feature in either of Google’s NoSQL offerings.

DIWAKAR EDUCATION HUB Page 140

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
Furthermore, unlike SQL databases, none of these NoSQL databases give you a means of doing
table joins, or even having foreign keys. Instead, this is something that your application has to
manage itself.
That’s said, one of the main advantages in my opinion of NoSQL is that there is no fixed
schema. As your needs change you can dynamically add new attributes to records in your
table.
For example, using Java and DynamoDB, you can do the following, which will return a list of
users that have the same username as a given user:
User user = new User(username);
DynamoDBQueryExpression<User> queryExpression =
new DynamoDBQueryExpression<User>().withHashKeyValues(user);
List<User> itemList =
[Link]().query([Link], queryExpression);
Distributed Database Design
The main benefit of NoSQL databases is their ability to scale, and to do so in an almost
seamless way. But, just like a SQL database, a poorly-designed NoSQL database can give you
slow query response times. This is why you need to consider your database design carefully.
In order to spread the load across multiple nodes, distributed databases need to spread the
stored data across multiple nodes. This is done in order for the load to be balanced. The flip-
side of this is that if frequently-accessed data is on a small subset of nodes, you will not be
making full use of the available capacity.
Consequently, you need to be careful of which columns you select as indexes. Ideally you want
to spread your load across the whole table as opposed to accessing only a portion of your
data.
A good design can be achieved by picking a hash key that is likely to be randomly accessed. For
example if you have a users table and choose the username as the hash key it will be likely
that load will distributed across all of the nodes. This is due to the likeliness that users will be
randomly accessed.
In contrast to this, it would, for example, be a poor design to use the date as the hash key for a
table that contains forum posts. This is due to the likeliness that most of the requests will be
for the records on the current day so the node or nodes containing these records will likely be
a small subset of all the nodes. This scenario can cause your requests to be throttled or hang.
Pricing
DynamoDB is priced on storage and provisioned read/write capacity. In the Oregon region
storage is charged at $0.25 per GB/Month and at $0.0065 per hour for every 10 units of Write
Capacity and the same price for every 50 units of read capacity.
Google Cloud Datastore has a similar pricing model. With storage priced at $0.18 per GB of
data per month and $0.06 per 100,000 read operations. Write operations are charged at the
same rate. Datastore also have a Free quota of 50,000 read and 50,000 write operations per
day. Since Datastore is a Beta product it currently has a limit of 100 million operations per day,
however you can request the limit to be increased.

DIWAKAR EDUCATION HUB Page 141

 DATABASE MANAGEMENT SYSTEMS UNIT – 4
The pricing model for Google Bigtable is significantly different. With Bigtable you are charged
at a rate of $0.65 per instance/hour. With a minimum of 3 instances required, some basic
arithmetic gives us a starting price for Bigtable of $142.35 per month. You are then charged at
$0.17 per GB/Month for SSD-backed storage. A cheaper HDD-backed option priced at $0.026
per GB/Month is yet to be released.
Finally you are charged for external network usage. This ranges between 8 and 23 cents per
GB of traffic depending on the location and amount of data transferred. Traffic to other
Google Cloud Platform services in the same region/zone is free.

DIWAKAR EDUCATION HUB Page 142

DIWAKAR EDUCATION HUB

DATABASE MANAGEMENT
SYSTEMS UNIT – 4 MCQs
AS PER UPDATED SYLLABUS
DIWAKAR EDUCATION HUB

THE LEARN WITH EXPERTIES

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
1. A relational database consists of a b) Relation
collection of c) Set
a) Tables d) Schema
b) Fields Answer: a
c) Records Explanation: The values of the attribute
d) Keys should be present in the domain. Domain is a
Answer: a set of values permitted.
Explanation: Fields are the column of the 6. Database __________ which is the logical
relation or tables. Records are each row in a design of the database, and the database
relation. Keys are the constraints in a _______ which is a snapshot of the data in
relation. the database at a given instant in time.
2. A ________ in a table represents a a) Instance, Schema
relationship among a set of values. b) Relation, Schema
a) Column c) Relation, Domain
b) Key d) Schema, Instance
c) Row Answer: d
d) Entry Explanation: Instance is an instance of time
Answer: c and schema is a representation.
Explanation: Column has only one set of 7. Course(course_id,sec_id,semester)
values. Keys are constraints and row is one Here the course_id,sec_id and semester are
whole set of attributes. Entry is just a piece of __________ and course is a _________
data. a) Relations, Attribute
3. The term _______ is used to refer to a row. b) Attributes, Relation
a) Attribute c) Tuple, Relation
b) Tuple d) Tuple, Attributes
c) Field Answer: b
d) Instance Explanation: The relation course has a set of
Answer: b attributes course_id,sec_id,semester .
Explanation: Tuple is one entry of the 8. Department (dept name, building, budget)
relation with several attributes which are and Employee (employee_id, name, dept
fields. name, salary)
4. The term attribute refers to a ___________ Here the dept_name attribute appears in
of a table. both the relations. Here using common
a) Record attributes in relation schema is one way of
b) Column relating ___________ relations.
c) Tuple a) Attributes of common
d) Key b) Tuple of common
Answer: b c) Tuple of distinct
Explanation: Attribute is a specific domain in d) Attributes of distinct
the relation which has entries of all tuples. Answer: c
5. For each attribute of a relation, there is a Explanation: Here the relations are
set of permitted values, called the ________ connected by the common attributes.
of that attribute. 9. A domain is atomic if elements of the
a) Domain domain are considered to be ____________

DIWAKAR EDUCATION HUB Page 2

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
units. the same set. Candidate key is a set from a
a) Different super key which cannot be the whole of the
b) Indivisbile super set.
c) Constant 14. A _____ is a property of the entire
d) Divisible relation, rather than of the individual tuples
Answer: b in which each tuple is unique.
10. The tuples of the relations can be of a) Rows
________ order. b) Key
a) Any c) Attribute
b) Same d) Fields
c) Sorted Answer: b
d) Constant Explanation: Key is the constraint which
Answer: a specifies uniqueness.
Explanation: The values only count. The 15. Which one of the following attribute can
order of the tuples does not matter. be taken as a primary key?
11. Which one of the following is a set of one a) Name
or more attributes taken collectively to b) Street
uniquely identify a record? c) Id
a) Candidate key d) Department
b) Sub key Answer: c
c) Super key Explanation: The attributes name, street and
d) Foreign key department can repeat for some tuples. But
Answer: c the id attribute has to be unique. So it forms
Explanation: Super key is the superset of all a primary key.
the keys in a relation. 16. Which one of the following cannot be
12. Consider attributes ID, CITY and NAME. taken as a primary key?
Which one of this can be considered as a a) Id
super key? b) Register number
a) NAME c) Dept_id
b) ID d) Street
c) CITY Answer: d
d) CITY, ID Explanation: Street is the only attribute
Answer: b which can occur more than once.
Explanation: Here the id is the only attribute 17. An attribute in a relation is a foreign key if
which can be taken as a key. Other attributes the _______ key from one relation is used as
are not uniquely identified. an attribute in that relation.
13. The subset of a super key is a candidate a) Candidate
key under what condition? b) Primary
a) No proper subset is a super key c) Super
b) All subsets are super keys d) Sub
c) Subset is a super key Answer: b
d) Each subset is a super key Explanation: The primary key has to be
Answer: a referred in the other relation to form a
Explanation: The subset of a set cannot be foreign key in that relation.

DIWAKAR EDUCATION HUB Page 3

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
18. The relation with the attribute which is through which the database entries can be
the primary key is referenced in another accessed.
relation. The relation which has the attribute 22. Student(ID, name, dept name, tot_cred)
as a primary key is called In this query which attributes form the
a) Referential relation primary key?
b) Referencing relation a) Name
c) Referenced relation b) Dept
d) Referred relation c) Tot_cred
Answer: b d) ID
19. The ______ is the one in which the Answer: d
primary key of one relation is used as a Explanation: The attributes name, dept and
normal attribute in another relation. tot_cred can have same values unlike ID.
a) Referential relation 23. Which one of the following is a
b) Referencing relation procedural language?
c) Referenced relation a) Domain relational calculus
d) Referred relation b) Tuple relational calculus
Answer: c c) Relational algebra
20. A _________ integrity constraint requires d) Query language
that the values appearing in specified Answer: c
attributes of any tuple in the referencing Explanation: Domain and Tuple relational
relation also appear in specified attributes of calculus are non-procedural language. Query
at least one tuple in the referenced relation. language is a method through which
a) Referential database entries can be accessed.
b) Referencing 24. The_____ operation allows the combining
c) Specific of two relations by merging pairs of tuples,
d) Primary one from each relation, into a single tuple.
Answer: a a) Select
Explanation: A relation, say r1, may include b) Join
among its attributes the primary key of c) Union
another relation, say r2. This attribute is d) Intersection
called a foreign key from r1, referencing r2. Answer: b
The relation r1 is also called the referencing Explanation: Join finds the common tuple in
relation of the foreign key dependency, and the relations and combines it.
r2 is called the referenced relation of the 25. The result which operation contains all
foreign key. pairs of tuples from the two relations,
21. Using which language can a user request regardless of whether their attribute values
information from a database? match.
a) Query a) Join
b) Relational b) Cartesian product
c) Structural c) Intersection
d) Compiler d) Set difference
Answer: a Answer: b
Explanation: Query language is a method Explanation: Cartesian product is the

DIWAKAR EDUCATION HUB Page 4

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
multiplication of all the values in the Answer: a
attributes. Explanation: Select operation just shows the
26. Which one of the following is used to required fields of the relation. So it forms a
define the structure of the relation, deleting DML.
relations and relating schemas? 30. The basic data type char(n) is a _____
a) DML(Data Manipulation Langauge) length character string and varchar(n) is
b) DDL(Data Definition Langauge) _____ length character.
c) Query a) Fixed, equal
d) Relational Schema b) Equal, variable
Answer: b c) Fixed, variable
Explanation: Data Definition language is the d) Variable, equal
language which performs all the operation in Answer: c
defining structure of relation. Explanation: Varchar changes its length
27. Which one of the following provides the accordingly whereas char has a specific
ability to query information from the length which has to be filled by either letters
database and to insert tuples into, delete or spaces.
tuples from, and modify tuples in the 31. An attribute A of datatype varchar(20)
database? has the value “Avi”. The attribute B of
a) DML(Data Manipulation Langauge) datatype char(20) has value ”Reed”. Here
b) DDL(Data Definition Langauge) attribute A has ____ spaces and attribute B
c) Query has ____ spaces.
d) Relational Schema a) 3, 20
Answer: a b) 20, 4
Explanation: DML performs the change in the c) 20, 20
values of the relation. d) 3, 4
28. Answer: a
CREATE TABLE employee (name VARCHAR, id Explanation: Varchar changes its length
INTEGER) accordingly whereas char has a specific
What type of statement is this? length which has to be filled by either letters
a) DML or spaces.
b) DDL 32. To remove a relation from an SQL
c) View database, we use the ______ command.
d) Integrity constraint a) Delete
Answer: b b) Purge
Explanation: Data Definition language is the c) Remove
language which performs all the operation in d) Drop table
defining structure of relation. Answer: d
29. Explanation: Drop table deletes the whole
structure of the relation .purge removes the
SELECT * FROM employee
table which cannot be obtained again.
What type of statement is this?
33.
a) DML
b) DDL DELETE FROM r; //r - relation
c) View This command performs which of the
d) Integrity constraint following action?
DIWAKAR EDUCATION HUB Page 5
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
a) Remove relation 37. Here which of the following displays the
b) Clear relation entries unique values of the column?
c) Delete fields SELECT ________ dept_name
d) Delete rows FROM instructor;
Answer: b a) All
Explanation: Delete command removes the b) From
entries in the table. c) Distinct
34. d) Name
INSERT INTO instructor VALUES (10211, Answer: c
’Smith’, ’Biology’, 66000); Explanation: Distinct keyword selects only
What type of statement is this? the entries that are unique.
a) Query 38. The ______ clause allows us to select only
b) DML those rows in the result relation of the ____
c) Relational clause that satisfy a specified predicate.
d) DDL a) Where, from
Answer: b b) From, select
Explanation: The values are manipulated. So c) Select, from
it is a DML. d) From, where
35. Updates that violate __________ are Answer: a
disallowed. Explanation: Where selects the rows on a
a) Integrity constraints particular condition. From gives the relation
b) Transaction control which involves the operation.
c) Authorization 39. The query given below will not give an
d) DDL constraints error. Which one of the following has to be
Answer: a replaced to get the desired output?
Explanation: Integrity constraint has to be SELECT ID, name, dept name, salary * 1.1
maintained in the entries of the relation. WHERE instructor;
36. a) Salary*1.1
Name b) ID
Annie c) Where
Bob d) Instructor
Answer: c
Callie
Explanation: Where selects the rows on a
Derek particular condition. From gives the relation
Which of these query will display the the which involves the operation. Since Instructor
table given above ? is a relation it has to have from clause.
a) Select employee from name 40. The ________ clause is used to list the
b) Select name attributes desired in the result of a query.
c) Select name from employee a) Where
d) Select employee b) Select
Answer: c c) From
Explanation: The field to be displayed is d) Distinct
included in select and the table is included in
the from clause.
DIWAKAR EDUCATION HUB Page 6
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: b c) 1001
Explanation: None d) 1018
41. This Query can be replaced by which one Answer: d
of the following? Explanation: Greater than symbol does not
SELECT name, course_id include the given value unlike >=.
FROM instructor, teaches 44.
WHERE instructor_ID= teaches_ID; SELECT name ____ instructor name, course id
a) Select name,course_id from FROM instructor, teaches
teaches,instructor where WHERE [Link]= [Link];
instructor_id=course_id; Which keyword must be used here to rename
b) Select name, course_id from instructor the field name?
natural join teaches; a) From
c) Select name, course_id from instructor; b) Rename
d) Select course_id from instructor join c) As
teaches; d) Join
Answer: b Answer: c
Explanation: Join clause joins two tables by Explanation: As keyword is used to rename.
matching the common column. 45.
42. SELECT * FROM employee WHERE
SELECT * FROM employee WHERE dept_name="Comp Sci";
salary>10000 AND dept_id=101; In the SQL given above there is an error .
Which of the following fields are displayed as Identify the error.
output? a) Dept_name
a) Salary, dept_id b) Employee
b) Employee c) “Comp Sci”
c) Salary d) From
d) All the field of employee relation Answer: c
Answer: d Explanation: For any string operations single
Explanation: Here * is used to select all the quoted(‘) must be used to enclose.
fields of the relation. 46.
43. SELECT emp_name
Employee_id Name Salary FROM department
1001 Annie 6000 WHERE dept_name LIKE ’ _____ Computer
1009 Ross 4500 Science’;
1018 Zeith 7000 Which one of the following has to be added
into the blank to select the dept_name which
This is Employee table.
has Computer Science as its ending string?
Which of the following employee_id will be
a) %
displayed for the given query?
b) _
SELECT * FROM employee WHERE
c) ||
employee_id>1009;
d) $
a) 1009, 1001, 1018
b) 1009, 1018

DIWAKAR EDUCATION HUB Page 7

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: a Answer: b
Explanation: The % character matches any Explanation: Union operator combines the
substring. relations.
47. ’_ _ _ ’ matches any string of ______ 51. The intersection operator is used to get
three characters. ’_ _ _ %’ matches any string the _____ tuples.
of at ______ three characters. a) Different
a) Atleast, Exactly b) Common
b) Exactly, Atleast c) All
c) Atleast, All d) Repeating
d) All, Exactly Answer: b
Answer: b Explanation: Intersection operator ignores
48. unique tuples and takes only common ones.
SELECT name 52. The union operation automatically
FROM instructor __________ unlike the select clause.
WHERE dept name = ’Physics’ a) Adds tuples
b) Eliminates unique tuples
ORDER BY name;
c) Adds common tuples
By default, the order by clause lists items in d) Eliminates duplicate
______ order.
Answer: d
a) Descending
b) Any 53. If we want to retain all duplicates, we
c) Same must write ________ in place of union.
d) Ascending a) Union all
b) Union some
Answer: d
c) Intersect all
Explanation: Specification of descending
d) Intersect some
order is essential but it not for ascending.
Answer: a
49.
Explanation: Union all will combine all the
SELECT * tuples including duplicates.
FROM instructor 54.
ORDER BY salary ____, name ___; (SELECT course id
To display the salary from greater to smaller FROM SECTION
and name in ascending order which of the
WHERE semester = ’Fall’ AND YEAR= 2009)
following options should be used?
a) Ascending, Descending EXCEPT
b) Asc, Desc (SELECT course id
c) Desc, Asc FROM SECTION
d) Descending, Ascending WHERE semester = ’Spring’ AND YEAR=
Answer: c 2010);
50. The union operation is represented by This query displays
a) ∩ a) Only tuples from second part
b) U b) Only tuples from the first part which has
c) – the tuples from second part
d) * c) Tuples from both the parts

DIWAKAR EDUCATION HUB Page 8

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
d) Tuples from first part which do not have b) Null Predicate
second part c) In Predicate
Answer: d d) Out Predicate
Explanation: Except keyword is used to Answer: a
ignore the values. Explanation: Like predicate matches the
55. For like predicate which of the following string in the given pattern.
is true. 60. Aggregate functions are functions that
i) % matches zero OF more characters. take a ___________ as input and return a
ii) _ matches exactly one CHARACTER. single value.
a) i-only a) Collection of values
b) ii-only b) Single value
c) i & ii c) Aggregate value
d) None of the mentioned d) Both Collection of values & Single value
Answer: a Answer: a
Explanation:% is used with like and _ is used 61.
to fill in the character. SELECT __________
56. The number of attributes in relation is FROM instructor
called as its WHERE dept name= ’Comp. Sci.’;
a) Cardinality Which of the following should be used to find
b) Degree the mean of the salary ?
c) Tuples a) Mean(salary)
d) Entity b) Avg(salary)
Answer: b c) Sum(salary)
57. _____ clause is an additional filter that is d) Count(salary)
applied to the result. Answer: b
a) Select Explanation: Avg() is used to find the mean of
b) Group-by the values.
c) Having 62.
d) Order by SELECT COUNT (____ ID)
Answer: c FROM teaches
Explanation: Having is used to provide WHERE semester = ’Spring’ AND YEAR =
additional aggregate filtration to the query. 2010;
58. _________ joins are SQL server default If we do want to eliminate duplicates, we use
a) Outer the keyword ______in the aggregate
b) Inner expression.
c) Equi a) Distinct
d) None of the mentioned b) Count
Answer: b c) Avg
Explanation: It is optional to give the inner d) Primary key
keyword with the join as it is default. Answer: a
59. The _____________ is essentially used to Explanation: Distinct keyword is used to
search for patterns in target string. select only unique items from the relation.
a) Like Predicate

DIWAKAR EDUCATION HUB Page 9

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
63. All aggregate functions except _____ SELECT course id
ignore null values in their input collection. FROM SECTION AS S
a) Count(attribute) WHERE semester = ’Fall’ AND YEAR= 2009
b) Count(*) AND
c) Avg EXISTS (SELECT *
d) Sum
FROM SECTION AS T
Answer: b
WHERE semester = ’Spring’ AND YEAR= 2010
Explanation: * is used to select all values
AND
including null.
[Link] id= [Link] id);
64. A Boolean data type that can take values
true, false, and________ b)
a) 1 SELECT name
b) 0 FROM instructor
c) Null WHERE salary > SOME (SELECT salary
d) Unknown FROM instructor
Answer: d WHERE dept name = ’Biology’);
Explanation: Unknown values do not take c)
null value but it is not known. SELECT COUNT (DISTINCT ID)
65. The ____ connective tests for set FROM takes
membership, where the set is a collection of
WHERE (course id, sec id, semester, YEAR) IN
values produced by a select clause. The ____
(SELECT course id, sec id, semester, YEAR
connective tests for the absence of set
FROM teaches
membership.
a) Or, in WHERE [Link]= 10101);
b) Not in, in d)
c) In, not in (SELECT course id
d) In, or FROM SECTION
Answer: c WHERE semester = ’Spring’ AND YEAR= 2010)
Explanation: In checks, if the query has the Answer: a
value but not in checks if it does not have the 68. We can test for the nonexistence of
value. tuples in a subquery by using the _____
66. The phrase “greater than at least one” is construct.
represented in SQL by _____ a) Not exist
a) < all b) Not exists
b) < some c) Exists
c) > all d) Exist
d) > some Answer: b
Answer: d Explanation: Exists is used to check for the
Explanation: >some takes atlest one value existence of tuples.
above it 69.
67. Which of the following is used to find all SELECT dept_name, ID, avg (salary)
courses taught in both the Fall 2009 semester
FROM instructor
and in the Spring 2010 semester .
GROUP BY dept_name;
a)

DIWAKAR EDUCATION HUB Page 10

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
This statement IS erroneous because Eg : SELECT name, salary, avg salary
a) Avg(salary) should not be selected FROM instructor I1, lateral (SELECT
b) Dept_id should not be used in group by avg(salary) AS avg salary
clause FROM instructor I2
c) Misplaced group by clause WHERE [Link] name= [Link] name);
d) Group by clause is not valid in this query Without the lateral clause, the subquery
Answer: b cannot access the correlation variable
Explanation: Any attribute that is not present I1 from the outer query.
in the group by clause must appear only 73. Which of the following creates a
inside an aggregate function if it appears in temporary relation for the query on which it
the select clause, otherwise the query is is defined?
treated as erroneous. a) With
70. SQL applies predicates in the _______ b) From
clause after groups have been formed, so c) Where
aggregate functions may be used. d) Select
a) Group by Answer: a
b) With Explanation: The with clause provides a way
c) Where of defining a temporary relation whose
d) Having definition is available only to the query in
Answer: b which the with clause occurs.
Explanation: The with clause provides away 74. A Delete command operates on ______
of defining a temporary relation whose relation.
definition is available only to the query in a) One
which the with clause occurs. b) Two
71. Aggregate functions can be used in the c) Several
select list or the_______clause of a select d) Null
statement or subquery. They cannot be used Answer: a
in a ______ clause. Explanation: Delete can delete from only one
a) Where, having table at a time.
b) Having, where
75.
c) Group by, having
d) Group by, where Delete from r where P;
Answer: b The above command
Explanation: To include aggregate functions a) Deletes a particular tuple from the relation
b) Deletes the relation
having clause must be included after where.
c) Clears all entries from the relation
72. The ________ keyword is used to access
d) All of the mentioned
attributes of preceding tables or subqueries
in the from clause. Answer: a
a) In Explanation: Here P gives the condition for
b) Lateral deleting specific rows.
c) Having 76. Which one of the following deletes all the
d) With entries but keeps the structure of the
Answer: b relation.
Explanation: a) Delete from r where P;

DIWAKAR EDUCATION HUB Page 11

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
b) Delete from instructor where dept name= query expression. The view name is
’Finance’; represented by v.
c) Delete from instructor where salary 81.
between 13000 and 15000; SELECT course_id
d) Delete from instructor; FROM physics_fall_2009
Answer: d WHERE building= ’Watson’;
Explanation: Absence of condition deletes all
Here the tuples are selected from the
rows.
[Link] one denotes the view.
77. _________ are useful in SQL update a) Course_id
statements, where they can be used in the b) Watson
set clause. c) Building
a) Multiple queries d) physics_fall_2009
b) Sub queries
Answer: c
c) Update
Explanation: View names may appear in a
d) Scalar subqueries
query any place where a relation name may
Answer: d appear.
78. The problem of ordering the update in 82. Materialised views make sure that
multiple updates is avoided using a) View definition is kept stable
a) Set b) View definition is kept up-to-date
b) Where c) View definition is verified for error
c) Case d) View is deleted after specified time
d) When
Answer: b
Answer: c
83. Updating the value of the view
Explanation: The case statements can add
a) Will affect the relation from which it is
the order of updating tuples.
defined
79. Which of the following creates a virtual b) Will not change the view definition
relation for storing the query? c) Will not affect the relation from which it is
a) Function defined
b) View d) Cannot determine
c) Procedure
Answer: a
d) None of the mentioned
84. SQL view is said to be updatable (that is,
Answer: b
inserts, updates or deletes can be applied on
Explanation: Any such relation that is not
the view) if which of the following conditions
part of the logical model, but is made visible
are satisfied by the query defining the view?
to a user as a virtual relation, is called a view.
a) The from clause has only one database
80. Which of the following is the syntax for relation
views where v is view name? b) The query does not have a group by or
a) Create view v as “query name”; having clause
b) Create “query expression” as view; c) The select clause contains only attribute
c) Create view v as “query expression”; names of the relation and does not have any
d) Create view “query expression”; expressions, aggregates, or distinct
Answer: c specification
Explanation: <query expression> is any legal d) All of the mentioned

DIWAKAR EDUCATION HUB Page 12

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: d b) Select
Explanation: All of the conditions must be c) View …as
satisfied to update the view in sql. d) None of the mentioned
85. Which of the following is used at the end Answer: d
of the view to reject the tuples which do not Explanation: Syntax is – create view v as
satisfy the condition in where clause? <query expression>;.
a) With 88. A _________ consists of a sequence of
b) Check query and/or update statements.
c) With check a) Transaction
d) All of the mentioned b) Commit
Answer: c c) Rollback
Explanation: Views can be defined with a d) Flashback
with check option clause at the end of the Answer: a
view definition; then, if a tuple inserted into Explanation: Transaction is a set of operation
the view does not satisfy the view’s where until commit.
clause condition, the insertion is rejected by 89. Which of the following makes the
the database system. transaction permanent in the database?
86. For the view Create view instructor_info a) View
as b) Commit
SELECT ID, name, building c) Rollback
FROM instructor, department d) Flashback
WHERE [Link] name= Answer: b
[Link] name; Explanation: Commit work commits the
If we insert tuple into the view as insert into current transaction.
instructor info values (’69987’, ’White’, 90. In order to undo the work of transaction
’Taylor’); after last commit which one should be used?
What will be the values of the other a) View
attributes in instructor and department b) Commit
relations? c) Rollback
a) Default value d) Flashback
b) Null Answer: c
c) Error statement Explanation: Rollback work causes the
d) 0 current transaction to be rolled back; that is,
Answer: b it undoes all the updates performed by the
Explanation: The values take null if there is SQL statements in the transaction.
no constraint in the attribute else it is an 91. Consider the following action:
Erroneous statement. TRANSACTION.....
87. Commit;
CREATE VIEW faculty AS ROLLBACK;
SELECT ID, name, dept name What does Rollback do?
FROM instructor; a) Undoes the transactions before commit
Find the error in this query. b) Clears all transactions
a) Instructor c) Redoes the transactions before commit
d) No action
DIWAKAR EDUCATION HUB Page 13
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: d 96. Which of the following is used to get back
Explanation: Once a transaction has executed all the transactions back after rollback?
commit work, its effects can no longer be a) Commit
undone by rollback work. b) Rollback
92. In case of any shut down during c) Flashback
transaction before commit which of the d) Redo
following statement is done automatically? Answer: c
a) View 97. ______ will undo all statements up to
b) Commit commit?
c) Rollback a) Transaction
d) Flashback b) Flashback
Answer: c c) Rollback
Explanation: Once a transaction has executed d) Abort
commit work, its effects can no longer be Answer: c
undone by rollback work. Explanation: Flashback will undo all the
93. In order to maintain the consistency statements and Abort will terminate the
during transactions, database provides operation.
a) Commit 98. To include integrity constraint in an
b) Atomic existing relation use :
c) Flashback a) Create table
d) Retain b) Modify table
Answer: b c) Alter table
Explanation: By atomic, either all the effects d) Drop table
of the transaction are reflected in the Answer: c
database, or none are (after rollback). Explanation: SYNTAX – alter table table-name
94. Transaction processing is associated with add constraint, where constraint can be any
everything below except constraint on the relation.
a) Conforming an action or triggering a 99. Which of the following is not an integrity
response constraint?
b) Producing detail summary or exception a) Not null
report b) Positive
c) Recording a business activity c) Unique
d) Maintaining a data d) Check ‘predicate’
Answer: a Answer: b
95. A transaction completes its execution is Explanation: Positive is a value and not a
said to be constraint.
a) Committed 100.
b) Aborted CREATE TABLE Employee(Emp_id NUMERIC
c) Rolled back NOT NULL, Name VARCHAR(20) , dept_name
d) Failed VARCHAR(20), Salary NUMERIC
Answer: a UNIQUE(Emp_id,Name));
Explanation: A complete transaction always INSERT INTO Employee VALUES(1002, Ross,
commits. CSE, 10000)

DIWAKAR EDUCATION HUB Page 14

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
INSERT INTO Employee department name specified in the tuple must
VALUES(1006,Ted,Finance, ); exist in the department relation.
INSERT INTO Employee 103.
VALUES(1002,Rita,Sales,20000); CREATE TABLE course
What will be the result of the query? (...
a) All statements executed FOREIGN KEY (dept name) REFERENCES
b) Error in create statement department
c) Error in insert into Employee . . . );
values(1006,Ted,Finance, );
Which of the following is used to delete the
d) Error in insert into Employee
entries in the referenced table when the
values(1008,Ross,Sales,20000);
tuple is deleted in course table?
Answer: d a) Delete
Explanation: The not null specification b) Delete cascade
prohibits the insertion of a null value for the c) Set null
attribute. d) All of the mentioned
The unique specification says that no two
Answer: b
tuples in the relation can be equal on all the
Explanation: The delete “cascades” to the
listed attributes.
course relation, deletes the tuple that refers
101. to the department that was deleted.
CREATE TABLE Manager(ID NUMERIC,Name 104. Domain constraints, functional
VARCHAR(20),budget NUMERIC,Details dependency and referential integrity are
VARCHAR(30)); special forms of _________
Inorder to ensure that the value of budget is a) Foreign key
non-negative which of the following should b) Primary key
be used? c) Assertion
a) Check(budget>0) d) Referential constraint
b) Check(budget<0) Answer: c
c) Alter(budget>0) Explanation: An assertion is a predicate
d) Alter(budget<0) expressing a condition we wish the database
Answer: a to always satisfy.
Explanation: A common use of the check 105. Which of the following is the right syntax
clause is to ensure that attribute values for the assertion?
satisfy specified conditions, in effect creating a) Create assertion ‘assertion-name’ check
a powerful type system. ‘predicate’;
102. Foreign key is the one in which the b) Create assertion check ‘predicate’
________ of one relation is referenced in ‘assertion-name’;
another relation. c) Create assertions ‘predicates’;
a) Foreign key d) All of the mentioned
b) Primary key Answer: a
c) References
106. Data integrity constraints are used to:
d) Check constraint
a) Control who is allowed access to the data
Answer: b b) Ensure that duplicate records are not
Explanation: The foreign-key declaration entered into the table
specifies that for each course tuple, the c) Improve the quality of data entered for a
DIWAKAR EDUCATION HUB Page 15
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
specific property (i.e., table column) Here which one denotes the relation for
d) Prevent users from changing the values which index is created?
stored in the table a) StudentID_index
Answer: c b) ID
107. Which of the following can be addressed c) StudentID
by enforcing a referential integrity d) Student
constraint? Answer: d
a) All phone numbers must include the area Explanation: The statement creates an index
code named studentID index on the attribute ID of
b) Certain fields are required (such as the the relation student.
email address, or phone number) before the 111. Which of the following is used to store
record is accepted movie and image files?
c) Information on the customer must be a) Clob
known before anything can be sold to that b) Blob
customer c) Binary
d) When entering an order quantity, the user d) Image
must input a number and not some text (i.e., Answer: b
12 rather than ‘a dozen’) Explanation: SQL therefore provides large-
Answer: c object data types for character data (clob)
Explanation: The information can be referred and binary data (blob). The letters “lob” in
to and obtained. these data types stand for “Large OBject”.
108. Dates must be specified in the format 112. The user defined data type can be
a) mm/dd/yy created using
b) yyyy/mm/dd a) Create datatype
c) dd/mm/yy b) Create data
d) yy/dd/mm c) Create definetype
Answer: b d) Create type
Explanation: yyyy/mm/dd is the default Answer: d
format in sql. Explanation: The create type clause can be
109. A ________ on an attribute of a relation used to define new [Link] : create type
is a data structure that allows the database Dollars as numeric(12,2) final; .
system to find those tuples in the relation 113. Values of one type can be converted to
that have a specified value for that attribute another domain using which of the following?
efficiently, without scanning through all the a) Cast
tuples of the relation. b) Drop type
a) Index c) Alter type
b) Reference d) Convert
c) Assertion Answer: a
d) Timestamp Explanation: Example of cast :cast
Answer: a ([Link] to numeric(12,2)). SQL
Explanation: Index is the reference to the provides drop type and alter type clauses to
tuples in a relation. drop or modify types that have been created
110. earlier.
Create index studentID_index on student(ID); 114.

DIWAKAR EDUCATION HUB Page 16

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
CREATE DOMAIN YearlySalary NUMERIC(8,2) 118. The database administrator who
CONSTRAINT salary VALUE test __________; authorizes all the new users, modifies the
In order to ensure that an instructor’s salary database and takes grants privilege is
domain allows only values greater than a a) Super user
specified value use: b) Administrator
a) Value>=30000.00 c) Operator of operating system
b) Not null; d) All of the mentioned
c) Check(value >= 29000.00); Answer: d
d) Check(value) Explanation: The authorizations provided by
Answer: c the administrator to the user is a privilege.
Explanation: Check(value ‘condition’) is the 119. Which of the following is a basic form of
syntax. grant statement?
115. Which of the following closely resembles a)
Create view? GRANT 'privilege list'
a) Create table . . .like ON 'relation name or view name'
b) Create table . . . as TO 'user/role list';
c) With data b)
d) Create view as GRANT 'privilege list'
Answer: b ON 'user/role list'
Explanation: The ‘create table . . . as’
TO 'relation name or view name';
statement closely resembles the create view
statement and both are defined by using c)
queries. The main difference is that the GRANT 'privilege list'
contents of the table are set when the table TO 'user/role list'
is created, whereas the contents of a view d)
always reflect the current query result. GRANT 'privilege list'
116. In contemporary databases, the top ON 'relation name or view name'
level of the hierarchy consists of ______ each ON 'user/role list';
of which can contain _____ Answer: a
a) Catalogs, schemas Explanation: The privilege list allows the
b) Schemas, catalogs granting of several privileges in one
c) Environment, schemas command .
d) Schemas, Environment
120. Which of the following is used to provide
Answer: a privilege to only a particular attribute?
117. Which of the following statements a) Grant select on employee to Amit
creates a new table temp instructor that has b) Grant update(budget) on department to
the same schema as an instructor. Raj
a) create table temp_instructor; c) Grant update(budget,salary,Rate) on
b) Create table temp_instructor like department to Raj
instructor; d) Grant delete to Amit
c) Create Table as temp_instructor; Answer: b
d) Create table like temp_instructor; Explanation: This grant statement gives user
Answer: b Raj update authorization on the budget
attribute of the department relation.
DIWAKAR EDUCATION HUB Page 17
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
121. Which of the following statement is used not necessarily receive all privileges on that
to remove the privilege from the user Amir? view.
a) Remove update on department from Amir 124. If we wish to grant a privilege and to
b) Revoke update on employee from Amir allow the recipient to pass the privilege on to
c) Delete select on department from Raj other users, we append the __________
d) Grant update on employee from Amir clause to the appropriate grant command.
Answer: b a) With grant
Explanation: revoke on from ; b) Grant user
122. Which of the following is used to provide c) Grant pass privelege
delete authorization to instructor? d) With grant option
a) Answer: d
CREATE ROLE instructor ; 125. In authorization graph, if DBA provides
GRANT DELETE TO instructor; authorization to u1 which inturn gives to u2
b) which of the following is correct?
CREATE ROLE instructor; a) If DBA revokes authorization from u1 then
u2 authorization is also revoked
GRANT SELECT ON takes
b) If u1 revokes authorization from u2 then
TO instructor; u2 authorization is revoked
c) c) If DBA & u1 revokes authorization from u1
CREATE ROLE instructor; then u2 authorization is also revoked
GRANT DELETE ON takes d) If u2 revokes authorization then u1
TO instructor; authorization is revoked
d) All of the mentioned Answer: c
Answer: c Explanation: A user has an authorization if
Explanation: The role is first created and the and only if there is a path from the root of
authorization is given on relation takes to the the authorization graph down to the node
role. representing the user.
123. Which of the following is true regarding 126. Which of the following is used to avoid
views? cascading of authorizations from the user?
a) The user who creates a view cannot be a) Granted by current role
given update authorization on a view without b) Revoke select on department from Amit,
having update authorization on the relations Satoshi restrict;
used to define the view c) Revoke grant option for select on
b) The user who creates a view cannot be department from Amit;
given update authorization on a view without d) Revoke select on department from Amit,
having update authorization on the relations Satoshi cascade;
used to define the view Answer: b
c) If a user creates a view on which no Explanation: The revoke statement may
authorization can be granted, the system will specify restrict in order to prevent cascading
allow the view creation request revocation. The keyword cascade can be used
d) A user who creates a view receives all instead of restrict to indicate that revocation
privileges on that view should cascade.
Answer: c 127. The granting and revoking of roles by
Explanation: A user who creates a view does the user may cause some confusions when

DIWAKAR EDUCATION HUB Page 18

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
that user role is revoked. To overcome the Answer: b
above situation Explanation: The triggers run after an insert,
a) The privilege must be granted only by roles update or delete on a table. They are not
b) The privilege is granted by roles and users supported for views.
c) The user role cannot be removed once 131. What are the after triggers?
given a) Triggers generated after a particular
d) By restricting the user access to the roles operation
Answer: a b) These triggers run after an insert, update
Explanation: The current role associated with or delete on a table
a session can be set by executing set role c) These triggers run after an insert, views,
name. The specified role must have been update or delete on a table
granted to the user, else the set role d) All of the mentioned
statement fails. Answer: b
128. A __________ is a special kind of a store Explanation: AFTER TRIGGERS can be
procedure that executes in response to classified further into three types as: AFTER
certain action on the table like insertion, INSERT Trigger, AFTER UPDATE Trigger, AFTER
deletion or updation of data. DELETE Trigger.
a) Procedures 132. The variables in the triggers are declared
b) Triggers using
c) Functions a) –
d) None of the mentioned b) @
Answer: b c) /
Explanation: Triggers are automatically d) /@
generated when a particular operation takes Answer: b
place. Explanation: Example : declare @empid int;
129. Triggers are supported in where empid is the variable.
a) Delete 133. The default extension for an Oracle
b) Update SQL*Plus file is:
c) Views a) .txt
d) All of the mentioned b) .pls
Answer: c c) .ora
Explanation: The triggers run after an insert, d) .sql
update or delete on a table. They are not Answer: d
supported for views. Explanation: Example :None.
130. The CREATE TRIGGER statement is used 134. Which of the following is NOT an Oracle-
to create the trigger. THE _____ clause supported trigger?
specifies the table name on which the trigger a) BEFORE
is to be attached. The ______ specifies that b) DURING
this is an AFTER INSERT trigger. c) AFTER
a) for insert, on d) INSTEAD OF
b) On, for insert Answer: b
c) For, insert Explanation: Example: During trigger is not
d) None of the mentioned possible in any database.

DIWAKAR EDUCATION HUB Page 19

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
135. What are the different in triggers? summaries of measure attributes, are
a) Define, Create viewed.
b) Drop, Comment 140. The generalization of cross-tab which is
c) Insert, Update, Delete represented visually is ____________ which
d) All of the mentioned is also called as data cube.
Answer: c a) Two dimensional cube
Explanation: Triggers are not possible for b) Multidimensional cube
create, drop. c) N-dimensional cube
136. Triggers ________ enabled or disabled d) Cuboid
a) Can be Answer: a
b) Cannot be Explanation: Each cell in the cube is
c) Ought to be identified for the values for the three
d) Always dimensional attributes.
Answer: a 141. The process of viewing the cross-tab
Explanation: Triggers can be manipulated. (Single dimensional) with a fixed value of one
137. Which prefixes are available to Oracle attribute is
triggers? a) Slicing
a) : new only b) Dicing
b) : old only c) Pivoting
c) Both :new and : old d) Both Slicing and Dicing
d) Neither :new nor : old Answer: a
Answer: c Explanation: The slice operation selects one
138. OLAP stands for particular dimension from a given cube and
a) Online analytical processing provides a new sub-cube. Dice selects two or
b) Online analysis processing more dimensions from a given cube and
c) Online transaction processing provides a new sub-cube.
d) Online aggregate processing 142. The operation of moving from finer-
Answer: a granularity data to a coarser granularity (by
Explanation: OLAP is the manipulation of means of aggregation) is called a ________
information to support decision making. a) Rollup
139. Data that can be modeled as dimension b) Drill down
attributes and measure attributes are called c) Dicing
_______ data. d) Pivoting
a) Multidimensional Answer: a
b) Singledimensional Explanation: The opposite operation—that of
c) Measured moving fromcoarser-granularity data to finer-
d) Dimensional granularity data—is called a drill down.
Answer: a 143. In SQL the cross-tabs are created using
Explanation: Given a relation used for data a) Slice
analysis, we can identify some of its b) Dice
attributes as measure attributes, since they c) Pivot
measure some value, and can be aggregated d) All of the mentioned
[Link] attribute define the
dimensions on which measure attributes, and
DIWAKAR EDUCATION HUB Page 20
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: a default], expression)
Explanation: Pivot (sum(quantity) for color in d) DECODE (expression, search, result [,
(’dark’,’pastel’,’white’)). search, result]… [, default])
144. Answer: d
{ (item name, color, clothes size), (item name, 148. Relational Algebra is a __________
color), (item name, clothes size), (color, query language that takes two relations as
clothes size), (item name), (color), (clothes input and produces another relation as an
size), () } output of the query.
This can be achieved by using which of the a) Relational
following ? b) Structural
a) group by rollup c) Procedural
b) group by cubic d) Fundamental
c) group by Answer: c
d) none of the mentioned Explanation: This language has fundamental
Answer: d and other operations which are used on
Explanation: ‘Group by cube’ is used . relations.
145. What do data warehouses support? 149. Which of the following is a fundamental
a) OLAP operation in relational algebra?
b) OLTP a) Set intersection
c) OLAP and OLTP b) Natural join
d) Operational databases c) Assignment
Answer: a d) None of the mentioned
146. Answer: d
SELECT item name, color, clothes SIZE, Explanation: The fundamental operations are
SUM(quantity) select, project, union, set difference,
Cartesian product, and rename.
FROM sales
150. Which of the following is used to denote
GROUP BY rollup(item name, color, clothes
the selection operation in relational algebra?
SIZE);
a) Pi (Greek)
How many grouping is possible in this rollup? b) Sigma (Greek)
a) 8 c) Lambda (Greek)
b) 4 d) Omega (Greek)
c) 2
Answer: b
d) 1
Explanation: The select operation selects
Answer: b tuples that satisfy a given predicate.
Explanation: { (item name, color, clothes
151. For select operation the ________
size), (item name, color), (item name), () }.
appear in the subscript and the ___________
147. Which one of the following is the right argument appears in the paranthesis after
syntax for DECODE? the sigma.
a) DECODE (search, expression, result [, a) Predicates, relation
search, result]… [, default]) b) Relation, Predicates
b) DECODE (expression, result [, search, c) Operation, Predicates
result]… [, default], search)
d) Relation, Operation
c) DECODE (search, result [, search, result]… [,
Answer: a
DIWAKAR EDUCATION HUB Page 21
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
152. The ___________ operation, denoted by combines the result of both LEFT and RIGHT
−, allows us to find tuples that are in one joins.
relation but are not in another. 157. The assignment operator is denoted by
a) Union a) ->
b) Set-difference b) <-
c) Difference c) =
d) Intersection d) ==
Answer: b Answer: b
Explanation: The expression r − s produces a Explanation: The result of the expression to
relation containing those tuples in r but not in the right of the ← is assigned to the relation
s. variable on the left of the ←.
153. Which is a unary operation: 158. Find the ID, name, dept name, salary for
a) Selection operation instructors whose salary is greater than
b) Primitive operation $80,000 .
c) Projection operation a) {t | t ε instructor ∧ t[salary] > 80000}
d) Generalized selection b) Э t ∈ r (Q(t))
Answer: d c) {t | Э s ε instructor (t[ID] = s[ID]∧ s[salary] >
Explanation: Generalization Selection takes 80000)}
only one argument for operation. d) None of the mentioned
154. Which is a join condition contains an Answer: a
equality operator: Explanation: This expression is in tuple
a) Equijoins relational format.
b) Cartesian 159. A query in the tuple relational calculus is
c) Natural expressed as:
d) Left a) {t | P() | t}
Answer: a b) {P(t) | t }
155. In precedence of set operators, the c) {t | P(t)}
expression is evaluated from d) All of the mentioned
a) Left to left Answer: c
b) Left to right Explanation: The tuple relational calculus, is a
c) Right to left nonprocedural query language. It describes
d) From user specification the desired information without giving a
Answer: b specific procedure for obtaining that
Explanation: The expression is evaluated information.
from left to right according to the 160.
precedence. {t | Э s ε instructor (t[name] = s[name]
156. Which of the following is not outer join? ∧ Э u ε department (u[dept name] = s[dept
a) Left outer join name]
b) Right outer join ∧ u[building] = “Watson”))}
c) Full outer join Which of the following best describes the
d) All of the mentioned query?
Answer: d a) Finds the names of all instructors whose
Explanation: The FULL OUTER JOIN keyword department is in the Watson building
b) Finds the names of all department is in the
DIWAKAR EDUCATION HUB Page 22
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Watson building Answer: c
c) Finds the name of the dapartment whose Explanation: For example, each person in a
instructor and building is Watson university is an entity.
d) Returns the building name of all the 165. The descriptive property possessed by
departments each entity set is _________
Answer: a a) Entity
Explanation: This query has two “there b) Attribute
exists” clauses in our tuple-relational-calculus c) Relation
expression, connected by and (∧). d) Model
161. Which of the following symbol is used in Answer: b
the place of except? Explanation: Possible attributes of the
a) ^ instructor entity set are ID, name, dept name,
b) V and salary.
c) ¬ 166. The function that an entity plays in a
d) ~ relationship is called that entity’s
Answer: c _____________
Explanation: The query ¬P negates the value a) Participation
of P. b) Position
162. “Find all students who have taken all c) Role
courses offered in the Biology department.” d) Instance
The expressions that matches this sentence is Answer: c
: Explanation: A relationship is an association
a) Э t ε r (Q(t)) among several entities.
b) ∀ t ε r (Q(t)) 167. The attribute name could be structured
c) ¬ t ε r (Q(t)) as an attribute consisting of first name,
d) ~ t ε r (Q(t)) middle initial, and last name. This type of
Answer: b attribute is called
Explanation: ∀ is used denote “for all” in SQL. a) Simple attribute
163. An ________ is a set of entities of the b) Composite attribute
same type that share the same properties, or c) Multivalued attribute
attributes. d) Derived attribute
a) Entity set Answer: b
b) Attribute set Explanation: Composite attributes can be
c) Relation set divided into subparts (that is, other
d) Entity model attributes).
Answer: a 168. The attribute AGE is calculated from
Explanation: An entity is a “thing” or “object” DATE_OF_BIRTH. The attribute AGE is
in the real world that is distinguishable from a) Single valued
all other objects. b) Multi valued
164. Entity is a _________ c) Composite
a) Object of relation d) Derived
b) Present working model Answer: d
c) Thing in real world Explanation: The value for this type of
d) Model of relation

DIWAKAR EDUCATION HUB Page 23

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
attribute can be derived from the values of a) Mapping Cardinality
other related attributes or entities. b) Relational Cardinality
169. Not applicable condition can be c) Participation Constraints
represented in relation entry as d) None of the mentioned
a) NA Answer: a
b) 0 Explanation: Mapping cardinality is also
c) NULL called as cardinality ratio.
d) Blank Space 173. An entity in A is associated with at most
Answer: c one entity in B, and an entity in B is
Explanation: NULL always represents that the associated with at most one entity in [Link] is
value is not present. called as
170. Which of the following can be a a) One-to-many
multivalued attribute? b) One-to-one
a) Phone_number c) Many-to-many
b) Name d) Many-to-one
c) Date_of_birth Answer: b
d) All of the mentioned Explanation: Here one entity in one set is
Answer: a related to one one entity in other set.
Explanation: Name and Date_of_birth cannot 174. An entity in A is associated with at most
hold more than 1 value. one entity in B. An entity in B, however, can
171. Which of the following is a single valued be associated with any number (zero or
attribute more) of entities in A.
a) Register_number a) One-to-many
b) Address b) One-to-one
c) SUBJECT_TAKEN c) Many-to-many
d) Reference d) Many-to-one
Answer: a Answer: d
172. In a relation between the entities the Explanation: Here more than one entity in
type and condition of the relation should be one set is related to one one entity in other
specified. That is called as______attribute. set.
a) Desciptive 175. Data integrity constraints are used to:
b) Derived a) Control who is allowed access to the data
c) Recursive b) Ensure that duplicate records are not
d) Relative entered into the table
Answer: a c) Improve the quality of data entered for a
Explanation: Consider the entity sets student specific property
and section, which participate in a d) Prevent users from changing the values
relationship set takes. We may wish to store stored in the table
a descriptive attribute grade with the Answer: c
relationship to record the grade that a Explanation: The data entered will be in a
student got in the class. particular cell (i.e., table column).
172. _____________ express the number of 176. Establishing limits on allowable property
entities to which another entity can be values, and specifying a set of acceptable,
associated via a relationship set. predefined options that can be assigned to a

DIWAKAR EDUCATION HUB Page 24

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
property are examples of: c) Composite Key
a) Attributes d) Foreign Key
b) Data integrity constraints Answer: d
c) Method constraints Explanation: Foreign key is used when
d) Referential integrity constraints primary key of one relation is used in another
Answer: b relation.
Explanation: Only particular value satisfying 181. ____________ is preferred method for
the constraints are entered in the column. enforcing data integrity
177. Which of the following can be addressed a) Constraints
by enforcing a referential integrity b) Stored Procedure
constraint? c) Triggers
a) All phone numbers must include the area d) Cursors
code Answer: a
b) Certain fields are required (such as the Explanation: Constraints are specified to
email address, or phone number) before the restrict entries in the relation.
record is accepted 182. Which of the following gives a logical
c) Information on the customer must be structure of the database graphically?
known before anything can be sold to that a) Entity-relationship diagram
customer b) Entity diagram
d) Then entering an order quantity, the user c) Database diagram
must input a number and not some text (i.e., d) Architectural representation
12 rather than ‘a dozen’) Answer: a
Answer: c Explanation: E-R diagrams are simple and
178. ______ is a special type of integrity clear—qualities that may well account in
constraint that relates two relations & large part for the widespread use of the E-R
maintains consistency across the relations. model.
a) Entity Integrity Constraints 183. The entity relationship set is
b) Referential Integrity Constraints represented in E-R diagram as
c) Domain Integrity Constraints a) Double diamonds
d) Domain Constraints b) Undivided rectangles
Answer: b c) Dashed lines
179. Which one of the following uniquely d) Diamond
identifies the elements in the relation? Answer: d
a) Secondary Key Explanation: Dashed lines link attributes of a
b) Primary key relationship set to the relationship set.
c) Foreign key 184. The Rectangles divided into two parts
d) Composite key represents
Answer: b a) Entity set
Explanation: Primary key checks for not null b) Relationship set
and uniqueness constraint. c) Attributes of a relationship set
180. Drop Table cannot be used to drop a d) Primary key
table referenced by a _________ constraint. Answer: a
a) Local Key Explanation: The first part of the rectangle,
b) Primary Key contains the name of the entity set. The
DIWAKAR EDUCATION HUB Page 25
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
second part contains the names of all the associated with an identifying entity; that is,
attributes of the entity set. the weak entity set is said to be existence
185. Consider a directed line(->) from the dependent on the identifying entity set. The
relationship set advisor to both entity sets identifying entity set is said to own the weak
instructor and student. This indicates entity set that it identifies. It is also called as
_________ cardinality owner entity set.
a) One to many 189. Weak entity set is represented as
b) One to one a) Underline
c) Many to many b) Double line
d) Many to one c) Double diamond
Answer: b d) Double rectangle
Explanation: This indicates that an instructor Answer: c
may advise at most one student, and a Explanation: An entity set that has a primary
student may have at most one advisor. key is termed a strong entity set.
186. We indicate roles in E-R diagrams by 190. If you were collecting and storing
labeling the lines that connect ___________ information about your music collection, an
to __________ album would be considered a(n) _____
a) Diamond , diamond a) Relation
b) Rectangle, diamond b) Entity
c) Rectangle, rectangle c) Instance
d) Diamond, rectangle d) Attribute
Answer: d Answer: b
Explanation: Diamond represents a Explanation: An entity set is a logical
relationship set and rectangle represents a container for instances of an entity type and
entity set. instances of any type derived from that entity
187. An entity set that does not have type.
sufficient attributes to form a primary key is 191. What term is used to refer to a specific
termed a __________ record in your music database; for instance;
a) Strong entity set information stored about a specific album?
b) Variant set a) Relation
c) Weak entity set b) Instance
d) Variable set c) Table
Answer: c d) Column
Explanation: An entity set that has a primary Answer: b
key is termed a strong entity set. Explanation: The environment of database is
188. For a weak entity set to be meaningful, it said to be an instance. A database instance or
must be associated with another entity set, an ‘instance’ is made up of the background
called the processes needed by the database.
a) Identifying set 192. The total participation by entities is
b) Owner set represented in E-R diagram as
c) Neighbour set a) Dashed line
d) Strong entity set b) Double line
Answer: a c) Double rectangle
Explanation: Every weak entity must be d) Circle

DIWAKAR EDUCATION HUB Page 26

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: b c) Inheritance
Explanation: It is used to represent the d) Constraint generalization
relation between several attributes. Answer: b
193. Given the basic ER and relational Explanation: The process of designating
models, which of the following is subgroupings within an entity set is called
INCORRECT? specialization.
a) An attribute of an entity can have more 197. Which relationship is used to represent a
than one value specialization entity?
b) An attribute of an entity can be composite a) ISA
c) In a row of a relational table, an attribute b) AIS
can have more than one value c) ONIS
d) In a row of a relational table, an attribute d) WHOIS
can have exactly one value or a NULL value Answer: a
Answer: c Explanation: In terms of an E-R diagram,
Explanation: It is possible to have several specialization is depicted by a hollow arrow-
values for a single attribute provide it is a head pointing from the specialized entity to
multi-valued attribute. the other entity.
194. Which of the following indicates the 198. The refinement from an initial entity set
maximum number of entities that can be into successive levels of entity subgroupings
involved in a relationship? represents a ________ design process in
a) Minimum cardinality which distinctions are made explicit.
b) Maximum cardinality a) Hierarchy
c) ERD b) Bottom-up
d) Greater Entity Count c) Top-down
Answer: b d) Radical
Explanation: In SQL (Structured Query Answer: c
Language), the term cardinality refers to the Explanation: The design process may also
uniqueness of data values contained in a proceed in a bottom-up manner, in which
particular column (attribute) of a database multiple entity sets are synthesized into a
table. higher-level entity set on the basis of
195. In E-R diagram generalization is common features.
represented by 199. There are similarities between the
a) Ellipse instructor entity set and the secretary entity
b) Dashed ellipse set in the sense that they have several
c) Rectangle attributes that are conceptually the same
d) Triangle across the two entity sets: namely, the
Answer: d identifier, name, and salary attributes. This
Explanation: Ellipse represents attributes, process is called
rectangle represents entity. a) Commonality
196. The entity set person is classified as b) Specialization
student and employee. This process is called c) Generalization
_________ d) Similarity
a) Generalization Answer: c
b) Specialization Explanation: Generalization is used to

DIWAKAR EDUCATION HUB Page 27

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
emphasize the similarities among lower-level attributes.
entity sets and to hide the differences. a) First
200. If an entity set is a lower-level entity set b) Second
in more than one ISA relationship, then the c) Third
entity set has d) Fourth
a) Hierarchy Answer: a
b) Multilevel inheritance Explanation: The first normal form is used to
c) Single inheritance eliminate the duplicate information.
d) Multiple inheritance 204. A table on the many side of a one to
Answer: d many or many to many relationship must:
Explanation: The attributes of the higher- a) Be in Second Normal Form (2NF)
level entity sets are said to be inherited by b) Be in Third Normal Form (3NF)
the lower-level entity sets. c) Have a single attribute key
201. A _____________ constraint requires d) Have a composite key
that an entity belong to no more than one Answer: d
lower-level entity set. Explanation: The relation in second normal
a) Disjointness form is also in first normal form and no
b) Uniqueness partial dependencies on any column in
c) Special primary key.
d) Relational 205. Tables in second normal form (2NF):
Answer: a a) Eliminate all hidden dependencies
Explanation: For example, student entity can b) Eliminate the possibility of a insertion
satisfy only one condition for the student anomalies
type attribute; an entity can be either a c) Have a composite key
graduate student or an undergraduate d) Have all non key fields depend on the
student, but cannot be both. whole primary key
202. Consider the employee work-team Answer: a
example, and assume that certain employees Explanation: The relation in second normal
participate in more than one work team. A form is also in first normal form and no
given employee may therefore appear in partial dependencies on any column in
more than one of the team entity sets that primary key.
are lower level entity sets of employee. Thus, 206. Which-one ofthe following statements
the generalization is _____________ about normal forms is FALSE?
a) Overlapping a) BCNF is stricter than 3 NF
b) Disjointness b) Lossless, dependency -preserving
c) Uniqueness decomposition into 3 NF is always possible
d) Relational c) Loss less, dependency – preserving
Answer: a decomposition into BCNF is always possible
Explanation: In overlapping generalizations, d) Any relation with two attributes is BCNF
the same entity may belong to more than one Answer: c
lower-level entity set within a single Explanation: We say that the decomposition
generalization. is a lossless decomposition if there is no loss
203. In the __________ normal form, a of information by replacing r (R) with two
composite attribute is converted to individual relation schemas r1(R1) andr2(R2).

DIWAKAR EDUCATION HUB Page 28

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
207. Functional Dependencies are the types b) 2NF
of constraints that are based on______ c) 3NF
a) Key d) 4NF
b) Key revisited Answer: c
c) Superset key Explanation: The table is in 3NF if every non-
d) None of the mentioned prime attribute of R is non-transitively
Answer: a dependent (i.e. directly dependent) on every
Explanation: Key is the basic element needed superkey of R.
for the constraints. 212.
208. Which is a bottom-up approach to Empdt1(empcode, name, street, city, state,
database design that design by examining the pincode).
relationship between attributes: For any pincode, there is only one city and
a) Functional dependency state. Also, for given street, city and state,
b) Database modeling there is just one pincode. In normalization
c) Normalization terms, empdt1 is a relation in
d) Decomposition a) 1 NF only
Answer: c b) 2 NF and hence also in 1 NF
Explanation: Normalisation is the process of c) 3NF and hence also in 2NF and 1NF
removing redundancy and unwanted data. d) BCNF and hence also in 3NF, 2NF and 1NF
209. Which forms simplifies and ensures that Answer: b
there are minimal data aggregates and Explanation: The relation in second normal
repetitive groups: form is also in first normal form and no
a) 1NF partial dependencies on any column in
b) 2NF primary key.
c) 3NF 213. We can use the following three rules to
d) All of the mentioned find logically implied functional
Answer: c dependencies. This collection of rules is
Explanation: The first normal form is used to called
eliminate the duplicate information. a) Axioms
210. Which forms has a relation that b) Armstrong’s axioms
possesses data about an individual entity: c) Armstrong
a) 2NF d) Closure
b) 3NF Answer: b
c) 4NF Explanation: By applying these rules
d) 5NF repeatedly, we can find all of F+, given F.
Answer: c 214. An approach to website design with the
Explanation: A Table is in 4NF if and only if, emphasis on converting visitors to outcomes
for every one of its non-trivial multivalued required by the owner is referred to as:
dependencies X \twoheadrightarrow Y, X is a a) Web usability
superkey—that is, X is either a candidate key b) Persuasion
or a superset thereof. c) Web accessibility
211. Which forms are based on the concept d) None of the mentioned
of functional dependency: Answer: b
a) 1NF Explanation: In computing, graphical user
DIWAKAR EDUCATION HUB Page 29
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
interface is a type of user interface that interface specifies how some software
allows users to interact with electronic components should interact with each other.
devices. 219. Blueprints are intended to:
125. A method of modelling and describing a) Prototype of the screen layout showing
user tasks for an interactive application is navigation and main design elements
referred to as: b) Show the grouping of pages and user
a) Customer journey journeys
b) Primary persona c) Indicate the structure of a site during site
c) Use case design and as a user feature
d) Web design persona d) Prototype typical customer journeys or
Answer: c clickstreams through a website
Explanation: The actions in GUI are usually Answer: c
performed through direct manipulation of Explanation: A blueprint is a reproduction of
the graphical elements. a technical drawing, documenting an
216. Information architecture influences: architecture or an engineering design, using a
a) Answer choice contact print process.
b) Site structure 220. Storyboards are intended to:
c) Labeling a) Indicate the structure of a site during site
d) Navigation design design and as a user feature
Answer: b b) Prototype of the screen layout showing
Explanation: The actions in GUI are usually navigation and main design elements
performed through direct manipulation of c) Integrate consistently available
the graphical elements. components on the webpage (e.g. navigation,
217. Also known as schematics, a way of search boxes)
illustrating the layout of an individual d) Prototype typical customer journeys or
webpage is a: click streams through a website
a) Wireframe Answer: d
b) Sitemap Explanation: An application programming
c) Card sorting interface specifies how some software
d) Blueprint components should interact with each other.
Answer: a 221. Which of the following occupies boot
Explanation: An application programming record of hard and floppy disks and activated
interface specifies how some software during computer startup?
components should interact with each other. a) Worm
218. A graphical or text depiction of the b) Boot sector virus
relationship between different groups of c) Macro virus
content on a website is referred to as a: d) Virus
a) Wireframe Answer: b
b) Blueprint Explanation: A blueprint is a reproduction of
c) Sitemap a technical drawing, documenting an
d) Card sorting architecture or an engineering design, using a
Answer: c contact print process.
Explanation: An application programming 222. In ordered indices the file containing the
records is sequentially ordered, a

DIWAKAR EDUCATION HUB Page 30

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
___________ is an index whose search key Answer: a
also defines the sequential order of the file. Explanation: In a dense nonclustering index,
a) Clustered index the index must store a list of pointers to all
b) Structured index records with the same search-key value.
c) Unstructured index 226. In a __________ index, an index entry
d) Nonclustered index appears for only some of the search-key
Answer: a values.
Explanation: Clustering index are also called a) Dense
primary indices; the term primary index may b) Sparse
appear to denote an index on a primary key, c) Straight
but such indices can in fact be built on any d) Continuous
search key. Answer: a
223. Indices whose search key specifies an Explanation: Sparse indices can be used only
order different from the sequential order of if the relation is stored in sorted order of the
the file are called ___________ indices. search key, that is if the index is a clustering
a) Nonclustered index.
b) Secondary 227. Incase the indices values are larger,
c) All of the mentioned index is created for these values of the index.
d) None of the mentioned This is called
Answer: c a) Pointed index
Explanation: Nonclustering index is also b) Sequential index
called secondary indices. c) Multilevel index
224. An ____________ consists of a search- d) Multiple index
key value and pointers to one or more Answer: c
records with that value as their search-key Explanation: Indices with two or more levels
value. are called multilevel indices.
a) Index entry 228. A search key containing more than one
b) Index hash attribute is referred to as a _________ search
c) Index cluster key.
d) Index map a) Simple
Answer: a b) Composite
Explanation: The pointer to a record consists c) Compound
of the identifier of a disk block and an offset d) Secondary
within the disk block to identify the record Answer: b
within the block. Explanation: The structure of the index is the
225. In a _______ clustering index, the index same as that of any other index, the only
record contains the search-key value and a difference being that the search key is not a
pointer to the first data record with that single attribute, but rather is a list of
search-key value and the rest of the records attributes.
will be in the sequential pointers. 229. In B+ tree the node which points to
a) Dense another node is called
b) Sparse a) Leaf node
c) Straight b) External node
d) Continuous

DIWAKAR EDUCATION HUB Page 31

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
c) Final node b) Three
d) Internal node c) Four
Answer: d d) None of the mentioned
Explanation: Nonleaf nodes are also referred Answer: b
to as internal nodes. Explanation: Only if the criteria is fulfilled the
230. Insertion of a large number of entries at values are hashed.
a time into an index is referred to as 234. What is the main limitation of
__________ of the index. Hierarchical Databases?
a) Loading a) Limited capacity (unable to hold much
b) Bulk insertion data)
c) Bulk loading b) Limited flexibility in accessing data
d) Increase insertion c) Overhead associated with maintaining
Answer: c indexes
Explanation: Bulk loading is used to improve d) The performance of the database is poor
efficiency and scalability. Answer: b
231. While inserting the record into the Explanation: In this, the data items are
index, if the search-key value does not placed in a tree like hierarchical structure.
appear in the index. 235. The property (or set of properties) that
a) The system adds a pointer to the new uniquely defines each row in a table is called
record in the index entry the:
b) The system places the record being a) Identifier
inserted after the other records with the b) Index
same search-key values c) Primary key
c) The system inserts an index entry with the d) Symmetric key
search-key value in the index at the Answer: c
appropriate position Explanation: Primary is used to uniquely
d) None of the mentioned identify the tuples.
Answer: c 236. The separation of the data definition
Explanation: If the index entry stores from the program is known as:
pointers to all records with the same search a) Data dictionary
key value, the system adds a pointer to the b) Data independence
new record in the index entry. c) Data integrity
232. A(n) _________ can be used to preserve d) Referential integrity
the integrity of a document or a message. Answer: b
a) Message digest Explanation: Data dictionary is the place
b) Message summary where the meaning of the data are organized.
c) Encrypted message 237. Bitmap indices are a specialized type of
d) None of the mentioned index designed for easy querying on
Answer: c ___________
Explanation: Encryption algorithms are used a) Bit values
to keep the contents safe. b) Binary digits
233. A hash function must meet ________ c) Multiple keys
criteria. d) Single keys
a) Two

DIWAKAR EDUCATION HUB Page 32

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: c c) To perform fast searches
Explanation: Each bitmap index is built on a d) All of the mentioned
single key. Answer: d
238. A _______ on the attribute A of relation Explanation: A database index is a data
r consists of one bitmap for each value that A structure that improves the speed of data
can take. retrieval operations on a database table at
a) Bitmap index the cost of additional writes.
b) Bitmap 242. How many types of indexes are there in
c) Index sql server?
d) Array a) 1
Answer: a b) 2
Explanation: A bitmap is simply an array of c) 3
bits. d) 4
239. Answer: b
SELECT * Explanation: They are clustered index and
FROM r non clustered index.
WHERE gender = ’f’ AND income level = ’L2’; 243. How non clustered index point to the
In this selection, we fetch the bitmaps for data?
gender value f and the bitmap for income a) It never points to anything
level value L2, and perform an ________ of b) It points to a data row
the two bitmaps. c) It is used for pointing data rows containing
a) Union key values
b) Addition d) None of the mentioned
c) Combination Answer: c
d) Intersection Explanation: Nonclustered indexes have a
Answer: d structure separate from the data rows. A
Explanation: We compute a new bitmap nonclustered index contains the nonclustered
where bit i has value 1 if the ith bit of the two index key values and each key value entry has
bitmaps are both 1, and has a value 0 a pointer to the data row that contains the
otherwise. key value.
240. To identify the deleted records we use 244. Which one is true about clustered index?
the ______________ a) Clustered index is not associated with table
a) Existence bitmap b) Clustered index is built by default on
b) Current bitmap unique key columns
c) Final bitmap c) Clustered index is not built on unique key
d) Deleted bitmap columns
d) None of the mentioned
Answer: a
Explanation: The bitmaps which are deleted Answer: b
are denoted by 0. Explanation: Nonclustered indexes have a
structure separate from the data rows. A
241. What is the purpose of the index in sql
nonclustered index contains the nonclustered
server?
index key values and each key value entry has
a) To enhance the query performance
a pointer to the data row that contains the
b) To provide an index to a record
key value.

DIWAKAR EDUCATION HUB Page 33

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
245. What is true about indexes? 249. Which of the following are the process
a) Indexes enhance the performance even if of selecting the data storage and data access
the table is updated frequently characteristics of the database?
b) It makes harder for sql server engines to a) Logical database design
work to work on index which have large keys b) Physical database design
c) It doesn’t make harder for sql server c) Testing and performance tuning
engines to work to work on index which have d) Evaluation and selecting
large keys Answer: b
d) None of the mentioned Explanation: The physical design of the
Answer: b database optimizes performance while
Explanation: Indexes tend to improve the ensuring data integrity by avoiding
performance. unnecessary data redundancies.
246. A collection of data designed to be used 250. Which of the following terms does refer
by different people is called a/an to the correctness and completeness of the
a) Organization data in a database?
b) Database a) Data security
c) Relationship b) Data constraint
d) Schema c) Data independence
Answer: b d) Data integrity
Explanation: Database is a collection of Answer: d
related tables. Explanation: ACID property is satisfied by
247. Which of the following is the oldest transaction in database.
database model? 251. The relationship between DEPARTMENT
a) Relational and EMPLOYEE is a
b) Deductive a) One-to-one relationship
c) Physical b) One-to-many relationship
d) Network c) Many-to-many relationship
Answer: d d) Many-to-one relationship
Explanation: The network model is a Answer: b
database model conceived as a flexible way Explanation: One entity department is
of representing objects and their related to several employees.
relationships. 252. A table can be logically connected to
248. Which of the following schemas does another table by defining a
define a view or views of the database for a) Super key
particular users? b) Candidate key
a) Internal schema c) Primary key
b) Conceptual schema d) Unique key
c) Physical schema Answer: c
d) External schema Explanation: A superkey is a combination of
Answer: d attributes that can be uniquely used to
Explanation: An externally-defined schema identify a database record.
can provide access to tables that are 253. If the state of the database no longer
managed on any PostgreSQL, Microsoft SQL reflects a real state of the world that the
Server, SAS, Oracle, or MySQL database. database is supposed to capture, then such a

DIWAKAR EDUCATION HUB Page 34

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
state is called that was designed specifically for the
a) Consistent state seamless processing of SQL commands.
b) Parallel state 257. _______________ has made PL/SQL
c) Durable state code run faster without requiring any
d) Inconsistent state additional work on the part of the
Answer: d programmer.
Explanation: SQL data consistency is that a) SQL Server
whenever a transaction is performed, it sees b) My SQL
a consistent database. c) Oracle
254. Ensuring isolation property is the d) SQL Lite
responsibility of the Answer: c
a) Recovery-management component of the Explanation: An Oracle database is a
DBMS collection of data treated as a unit. The
b) Concurrency-control component of the purpose of a database is to store and retrieve
DBMS related information.
c) Transaction-management component of 258. A line of PL/SQL text contains groups of
the DBMS characters known as
d) Buffer management component in DBMS a) Lexical Units
Answer: b b) Literals
Explanation: Concurrency control ensures c) Textual Units
that correct results for concurrent operations d) Identifiers
are generated while getting those results as Answer: a
quickly as possible. Explanation: Lexical items can be generally
255. _______________ is a procedural understood to convey a single meaning,
extension of Oracle – SQL that offers much as a lexeme, but are not limited to
language constructs similar to those in single words.
imperative programming languages. 259. We use ______________ name PL/SQL
a) SQL program objects and units.
b) PL/SQL a) Lexical Units
c) Advanced SQL b) Literals
d) PQL c) Delimiters
Answer: b d) Identifiers
Explanation: PL/SQL is an imperative 3GL Answer: d
that was designed specifically for the Explanation: The database object name is
seamless processing of SQL commands. referred to as its identifier.
256. ___________ combines the data 260. Consider money is transferred from
manipulating power of SQL with the data (1)account-A to account-B and (2) account-B
processing power of Procedural languages. to account-A. Which of the following form a
a) PL/SQL transaction?
b) SQL a) Only 1
c) Advanced SQL b) Only 2
d) PQL c) Both 1 and 2 individually
Answer: a d) Either 1 or 2
Explanation: PL/SQL is an imperative 3GL

DIWAKAR EDUCATION HUB Page 35

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: c Answer: c
Explanation: The term transaction refers to a Explanation: Even though multiple
collection of operations that form a single transactions may execute concurrently, the
logical unit of work. system guarantees that, for every pair of
261. A transaction is delimited by statements transactions Ti and Tj, it appears to Ti that
(or function calls) of the form __________ either Tj finished execution before Ti started
a) Begin transaction and end transaction or Tj started execution after Ti finished.
b) Start transaction and stop transaction 265. The property of a transaction that
c) Get transaction and post transaction persists all the crashes is
d) Read transaction and write transaction a) Atomicity
Answer: a b) Durability
Explanation: The transaction consists of all c) Isolation
operations executed between the begin d) All of the mentioned
transaction and end transaction. Answer: b
262. Identify the characteristics of Explanation: After a transaction completes
transactions successfully, the changes it has made to the
a) Atomicity database persist, even if there are system
b) Durability failures.
c) Isolation 266. __________ states that only valid data
d) All of the mentioned will be written to the database.
Answer: d a) Consistency
Explanation: Because of the above three b) Atomicity
properties, transactions are an ideal way of c) Durability
structuring interaction with a database. d) Isolation
263. Which of the following has “all-or-none” Answer: a
property? Explanation: If for some reason, a transaction
a) Atomicity is executed that violates the database’s
b) Durability consistency rules, the entire transaction will
c) Isolation be rolled back and the database will be
d) All of the mentioned restored to a state consistent with those
Answer: a rules.
Explanation: Either all operations of the 267. Transaction processing is associated
transaction are reflected properly in the with everything below except
database, or none are. a) Producing detail summary or exception
264. The database system must take special reports
actions to ensure that transactions operate b) Recording a business activity
properly without interference from c) Confirming an action or triggering a
concurrently executing database statements. response
This property is referred to as d) Maintaining a data
a) Atomicity Answer: c
b) Durability Explanation: Collections of operations that
c) Isolation form a single logical unit of work are called
d) All of the mentioned transactions.

DIWAKAR EDUCATION HUB Page 36

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
268. The Oracle RDBMS uses the ____ b) To create a database trigger
statement to declare a new transaction start c) To add/rename data files, to change
and its properties. storage
a) BEGIN d) All of the mentioned
b) SET TRANSACTION Answer: a
c) BEGIN TRANSACTION Explanation: Triggers are used to initialize the
d) COMMIT actions for an activity.
Answer: b 272. Which character function can be used to
Explanation: Commit is used to store all the return a specified portion of a character
transactions. string?
269. ____ means that the data used during a) INSTR
the execution of a transaction cannot be used b) SUBSTRING
by a second transaction until the first one is c) SUBSTR
completed. d) POS
a) Consistency Answer: c
b) Atomicity Explanation: SUBSTR are used to match the
c) Durability particular characters in a string.
d) Isolation 273. Which of the following is TRUE for the
Answer: d System Variable $date$?
Explanation: Even though multiple a) Can be assigned to a global variable
transactions may execute concurrently, the b) Can be assigned to any field only during
system guarantees that, for every pair of design time
transactions Ti and Tj, it appears to Ti that c) Can be assigned to any variable or field
either Tj finished execution before Ti started during run time
or Tj started execution after Ti finished. d) Can be assigned to a local variable
270. In SQL, which command is used to issue Answer: b
multiple CREATE TABLE, CREATE VIEW and Explanation: A database schema of a
GRANT statements in a single transaction? database system is its structure described in a
a) CREATE PACKAGE formal language supported by the database
b) CREATE SCHEMA management system and refers to the
c) CREATE CLUSTER organization of data as a blueprint of how a
d) All of the mentioned database is constructed.
Answer: b 275. What are the different events in
Explanation: A database schema of a Triggers?
database system is its structure described in a a) Define, Create
formal language supported by the database b) Drop, Comment
management system and refers to the c) Insert, Update, Delete
organization of data as a blueprint of how a d) Select, Commit
database is constructed. Answer: c
271. In SQL, the CREATE TABLESPACE is used Explanation: A database trigger is a
a) To create a place in the database for procedural code that is automatically
storage of scheme objects, rollback executed in response to certain events on a
segments, and naming the data files to particular table or view in a database.
comprise the tablespace

DIWAKAR EDUCATION HUB Page 37

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
276. Which of the following is not a property c) Locks
of transactions? d) Cursors
a) Atomicity Answer: c
b) Concurrency Explanation: Locks are used to maintain
c) Isolation database consistency.
d) Durability 281. A lock that allows concurrent
Answer: d transactions to access different rows of the
Explanation: ACID properties are the same table is known as a
properties of transactions. a) Database-level lock
277. SNAPSHOT is used for (DBA) b) Table-level lock
a) Synonym c) Page-level lock
b) Tablespace d) Row-level lock
c) System server Answer: d
d) Dynamic data replication Explanation: Locks are used to maintain
Answer: d database consistency.
Explanation: Snapshot gets the instance of 282. Which of the following are introduced to
the database at that time. reduce the overheads caused by the log-
278. Isolation of the transactions is ensured based recovery?
by a) Checkpoints
a) Transaction management b) Indices
b) Application programmer c) Deadlocks
c) Concurrency control d) Locks
d) Recovery management Answer: a
Explanation: Checkpoints are introduced to
Answer: c reduce overheads caused by the log-based
Explanation: ACID properties are the recovery.
properties of transactions. 283. Which of the following protocols
279. Constraint checking can be disabled in ensures conflict serializability and safety from
existing _______________ and deadlocks?
_____________ constraints so that any data a) Two-phase locking protocol
you modify or add to the table is not checked b) Time-stamp ordering protocol
against the constraint. c) Graph based protocol
a) CHECK, FOREIGN KEY d) None of the mentioned
b) DELETE, FOREIGN KEY Answer: b
c) CHECK, PRIMARY KEY Explanation: Time-stamp ordering protocol
d) PRIMARY KEY, FOREIGN KEY ensures conflict serializability and safety from
Answer: a deadlocks.
Explanation: Check and foreign constraints 284. A system is in a ______ state if there
are used to constraint the table data. exists a set of transactions such that every
280. In order to maintain transactional transaction in the set is waiting for another
integrity and database consistency, what transaction in the set.
technology does a DBMS deploy? a) Idle
a) Triggers b) Waiting
b) Pointers

DIWAKAR EDUCATION HUB Page 38

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
c) Deadlock a) BETWEEN
d) Ready b) IS NULL
Answer: c c) LIKE
Explanation: When one data item is waiting d) IN
for another data item in a transaction then Answer: b
system is in deadlock. Explanation: Exists is used to check whether
285. The deadlock state can be changed back an attribute value is null or not in conjunction
to stable state by using _____________ with the where clause.
statement. 289. The method of access that uses key
a) Commit transformation is called as
b) Rollback a) Direct
c) Savepoint b) Hash
d) Deadlock c) Random
Answer: b d) Sequential
Explanation: Rollback is used to rollback to Answer: b
the point before lock is obtained. Explanation: Hash technique uses particular
286. What are the ways of dealing with hash key value.
deadlock? 290. Why do we need concurrency control on
a) Deadlock prevention B+ trees ?
b) Deadlock recovery a) To remove the unwanted data
c) Deadlock detection b) To easily add the index elements
d) All of the mentioned c) To maintain accuracy of index
Answer: d d) All of the mentioned
Explanation: Deadlock prevention is also Answer: c
called as deadlock recovery. Prevention is Explanation: Indices do not have to be
commonly used if the probability that the treated like other database structures.
system would enter a deadlock state is 291. How many techniques are available to
relatively high; otherwise, detection and control concurrency on B+ trees?
recovery are more efficient. a) One
287. The most recent version of standard SQL b) Three
prescribed by the American National c) Four
Standards Institute is d) None of the mentioned
a) SQL 2016 Answer: d
b) SQL 2002 Explanation: Two techniques are present.
c) SQL – 4 292. In crabbing protocol locking
d) SQL2 a) Goes down the tree and back up
Answer: a b) Goes up the tree and back down
Explanation: SQL-2016 is the most recent c) Goes down the tree and releases
version of standard SQL prescribed by the d) Goes up the tree and releases
ANSI. Answer: a
288. ANSI-standard SQL allows the use of Explanation: It moves in a crab like manner.
special operators in conjunction with the 293. The deadlock can be handled by
WHERE clause. A special operator used to a) Removing the nodes that are deadlocked
check whether an attribute value is null is b) Restarting the search after releasing the
DIWAKAR EDUCATION HUB Page 39
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
lock Answer: c
c) Restarting the search without releasing the Explanation: The transaction, can be re-
lock executed at a later time.
d) Resuming the search 298. The transaction can no longer continue
Answer: b with its normal execution because of some
Explanation: Crabbing protocol moves in a internal condition, such as bad input, data
crab like manner. not found, overflow, or resource limit
294. The recovery scheme must also provide exceeded. This is
a) High availability a) Read error
b) Low availability b) Boot error
c) High reliability c) Logical error
d) High durability d) System error
Answer: a Answer: c
Explanation: It must minimize the time for Explanation: The transaction, can be re-
which the database is not usable after a executed at a later time.
failure. 299. The assumption that hardware errors
295. Which one of the following is a failure to and bugs in the software bring the system to
a system a halt, but do not corrupt the nonvolatile
a) Boot crash storage contents, is known as the
b) Read failure a) Stop assumption
c) Transaction failure b) Fail assumption
d) All of the mentioned c) Halt assumption
Answer: c d) Fail-stop assumption
Explanation: Types of system failure are Answer: d
transaction failure, system crash and disk Explanation: Well-designed systems have
failure. numerous internal checks, at the hardware
296. Which of the following belongs to and the software level, that bring the system
transaction failure to a halt when there is an error. Hence, the
a) Read error fail-stop assumption is a reasonable one.
b) Boot error 300. Which kind of failure loses its data in
c) Logical error head crash or failure during a transfer
d) All of the mentioned operation.
Answer: c a) Transaction failure
Explanation: Types of system transaction b) System crash
failure are logical and system error. c) Disk failure
297. The system has entered an undesirable d) All of the mentioned
state (for example, deadlock), as a result of Answer: c
which a transaction cannot continue with its Explanation: Copies of the data on other
normal execution. This is disks, or archival backups on tertiary media,
a) Read error such as DVD or tapes, are used to recover
b) Boot error from the failure.
c) Logical error 301. The log is a sequence of _________
d) System error recording all the update activities in the
database.

DIWAKAR EDUCATION HUB Page 40

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
a) Log records new page table just stores a pointer to the
b) Records original page.
c) Entries 305. If a transaction does not modify the
d) Redo database until it has committed, it is said to
Answer: a use the ___________ technique.
Explanation: The most widely used structure a) Deferred-modification
for recording database modifications is the b) Late-modification
log. c) Immediate-modification
302. In the ___________ scheme, a d) Undo
transaction that wants to update the Answer: a
database first creates a complete copy of the Explanation: Deferred modification has the
database. overhead that transactions need to make
a) Shadow copy local copies of all updated data items;
b) Shadow Paging further, if a transaction reads a data item that
c) Update log records it has updated, it must read the value from its
d) All of the mentioned local copy.
Answer: a 306. If database modifications occur while
Explanation: If at any point the transaction the transaction is still active, the transaction
has to be aborted, the system merely deletes is said to use the ___________technique.
the new copy. The old copy of the database a) Deferred-modification
has not been affected. b) Late-modification
303. The ____________ scheme uses a page c) Immediate-modification
table containing pointers to all pages; the d) Undo
page table itself and all updated pages are Answer: c
copied to a new location. Explanation: We say a transaction modifies
a) Shadow copy the database if it performs an update on a
b) Shadow Paging disk buffer, or on the disk itself; updates to
c) Update log records the private part of main memory do not
d) All of the mentioned count as database modifications.
Answer: b 307. ____________ using a log record sets
Explanation: Any page which is not updated the data item specified in the log record to
by a transaction is not copied, but instead the the old value.
new page table just stores a pointer to the a) Deferred-modification
original page. b) Late-modification
304. The current copy of the database is c) Immediate-modification
identified by a pointer, called ____________ d) Undo
which is stored on disk. Answer: d
a) Db-pointer Explanation: Undo brings the previous
b) Update log contents.
c) Update log records 308. In the __________ phase, the system
d) All of the mentioned replays updates of all transactions by
Answer: a scanning the log forward from the last
Explanation: Any page which is not updated checkpoint.
by a transaction is not copied, but instead the a) Repeating

DIWAKAR EDUCATION HUB Page 41

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
b) Redo b) Backup
c) Replay c) Redo memory
d) Undo d) Disk
Answer: b Answer: d
Explanation: Undo brings the previous Explanation: If there are insufficient log
contents. records to fill the block, all log records in
309. In order to reduce the overhead in main memory are combined into a partially
retrieving the records from the storage space full block and are output to stable storage.
we use 313. The silicon chips used for data
a) Logs processing are called
b) Log buffer a) RAM chips
c) Medieval space b) ROM chips
d) Lower records c) Micro processors
Answer: b d) PROM chips
Explanation: The output to stable storage is Answer: d
in units of blocks. Explanation: PROM is Programmable Read
310. The order of log records in the stable Only Memory.
storage ____________ as the order in which 314. Which of the following is used for
they were written to the log buffer. manufacturing chips?
a) Must be exactly the same a) Control bus
b) Can be different b) Control unit
c) Is opposite c) Parity unit
d) Can be partially same d) Semiconductor
Answer: a Answer: d
Explanation: As a result of log buffering, a log Explanation: A semiconductor is a material
record may reside in only main memory which has electrical conductivity between
(volatile storage) for a considerable time that of a conductor such as copper and that
before it is output to stable storage. of an insulator such as glass.
311. Before a block of data in main memory 315. What was the name of the first
can be output to the database, all log records commercially available microprocessor chip?
pertaining to data in that block must have a) Intel 308
been output to stable storage. This is b) Intel 33
a) Read-write logging c) Intel 4004
b) Read-ahead logging d) Motorola 639
c) Write-ahead logging Answer: c
d) None of the mentioned Explanation: The Intel 4004 is a 4-bit central
Answer: c processing unit (CPU) released by Intel
Explanation: The WAL rule requires only that Corporation in 1971
the undo information in the log has been 316. Which lock should be obtained to
output to stable storage, and it permits the prevent a concurrent transaction from
redo information to be written later. executing a conflicting read, insert or delete
312. Writing the buffered log to __________ operation on the same key value.
is sometimes referred to as a log force. a) Higher-level lock
a) Memory b) Lower-level lock

DIWAKAR EDUCATION HUB Page 42

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
c) Read only lock Answer: a
d) Read write Explanation: We can achieve high availability
Answer: a by performing transaction processing at one
Explanation: Operations acquire lower-level site, called the primary site, and having a
locks while they execute, but release them remote backup site where all the data from
when they complete; the corresponding the primary site are replicated.
transaction must however retain a higher- 320. The backup is taken by
level lock in a two-phase manner to prevent a) Erasing all previous records
concurrent transactions from executing b) Entering the new records
conflicting actions. c) Sending all log records from primary site to
317. Once the lower-level lock is released, the remote backup site
the operation cannot be undone by using the d) Sending selected records from primary site
old values of updated data items, and must to the remote backup site
instead be undone by executing a Answer: c
compensating operation; such an operation is Explanation: We can achieve high availability
called by performing transaction processing at one
a) Logical operation site, called the primary site, and having a
b) Redo operation remote backup site where all the data from
c) Logical undo operation the primary site are replicated.
d) Undo operation 321. When the __________ the backup site
Answer: a takes over processing and becomes the
Explanation: It is important that the lower- primary.
level locks acquired during an operation are a) Secondary fails
sufficient to perform a subsequent logical b) Backup recovers
undo of the operation. c) Primary fails
318. The remote backup site is sometimes d) None of the mentioned
also called the Answer: c
a) Primary Site Explanation: When the original primary site
b) Secondary Site recovers, it can either play the role of remote
c) Tertiary Site backup, or take over the role of primary site
d) None of the mentioned again.
Answer: b 322. The simplest way of transferring control
Explanation: We can achieve high availability is for the old primary to receive __________
by performing transaction processing at one from the old backup site.
site, called the primary site, and having a a) Undo logs
remote backup site where all the data from b) Redo Logs
the primary site are replicated. c) Primary Logs
319. Remote backup system must be d) All of the mentioned
_________ with the primary site. Answer: c
a) Synchronised Explanation: If control must be transferred
b) Separated back, the old backup site can pretend to have
c) Connected failed, resulting in the old primary taking
d) Detached but related over.

DIWAKAR EDUCATION HUB Page 43

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
323. In the __________ phase, the system specialization. Specialization allows us to
replays updates of all transactions by distinguish among entities.
scanning the log forward from the last 327. State true or false: Specialization can be
checkpoint. applied only once
a) Repeating a) True
b) Redo b) False
c) Replay
d) Undo Answer: a
Answer: b Explanation: We can apply specialization
Explanation: Undo brings the previous multiple times to refine a design. An entity
contents. set may also be specialized by more than one
324. The actions which are played in the distinguishing feature.
order while recording it is called 328. Which of the following is the
______________ history. specialization that permits multiple sets
a) Repeating a) Superclass specialization
b) Redo b) Disjoint specialization
c) Replay c) Overlapping specialization
d) Undo d) None of the mentioned
Answer: a Answer: c
Explanation: Undo brings the previous Explanation: Overlapping specialization is the
contents. type of specialization that permits multiple
325. A special redo-only log record < Ti, Xj, sets. But disjoint specialization does not
V1> is written to the log, where V1 is the permit multiple sets. Disjoint specialization
value being restored to data item Xj during permits at most one set.
the rollback. These log records are sometimes 329. The similarities between the entity set
called can be expressed by which of the following
a) Log records features?
b) Records a) Specialization
c) Compensation log records b) Generalization
d) Compensation redo records c) Uniquation
Answer: c d) Inheritance
Explanation: Such records do not need undo Answer: b
information since we never need to undo Explanation: The similarities between the
such an undo operation. entity set can be expressed by the
326. The process of designating sub generalization feature. It is a containment o
groupings within the entity set is called as the relationship that exists between a higher
_______ level entity set and one or more lower level
a) Specialization entity sets.
b) Division 330. Higher level entity sets are designated
c) Aggregation by the term _________
d) Finalization a) Sub class
Answer: a b) Super class
Explanation: The process of designating sub- c) Parent class
groupings within the entity set is called as d) Root class

DIWAKAR EDUCATION HUB Page 44

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
Answer: b attributes makes it very difficult for data
Explanation: Higher level entity sets can also extraction. So, It is not a good relational
be designated by the term super class. In the design feature.
similar manner lower level entity sets can 335. The dependency rules specified by the
also be designated by the term sub class. database designer are known as _______
331. State true or false: The attributes of the a) Designer dependencies
higher level entity sets are inherited by the b) Database rules
attributes of the lower level entity sets c) Functional dependencies
a) True d) None of the mentioned
b) False Answer: c
Answer: a Explanation: The dependency rules specified
Explanation: The attributes of the higher by the database designer are known as
level entity sets are inherited by the functional dependencies. The normal forms
attributes of the lower level entity sets. But are based on functional dependencies.
the inverse is not true in this case. 336. If the decomposition is unable to
332. Which of the following is not a represent certain important facts about the
generalization constraint? relation, then such a decomposition is called
a) Condition-defined as?
b) User defined a) Lossless decomposition
c) Disjoint b) Lossy decomposition
d) Machine defined c) Insecure decomposition
Answer: d d) Secure decomposition
Explanation: Machine definition is not a Answer: b
generalization constraint. Condition defined, Explanation: If the decomposition is unable
user defined, disjoint and overlapping are 4 to represent certain important facts about
generalization constraints. the relation, then such a decomposition is
333. Condition defined generalization called as lossy decomposition. Lossy
constraint is also said to be ________ decompositions should be avoided as they
a) Attribute defined result in the loss of data.
b) Constraint defined 337. An instance of a relation that satisfies all
c) Value defined real world constraints is known as?
d) Undefined a) Proper relation
Answer: a b) Ideal relation
Explanation: Condition defined c) Perfect relation
generalization constraint is also said to be d) Legal relation
attribute defined. Answer: d
334. Which of the following is not a feature of Explanation: A relation that satisfies all the
a good relational design? real world constraints is called as a legal
a) Specifying primary keys relation. An instance of a legal relation is
b) Specifying foreign keys called as a legal instance.
c) Preserving integrity constraints 338. If K → R then K is said to be the _______
d) Allowing redundancy of attributes of R
Answer: d a) Candidate key
Explanation: Allowing redundancy of b) Foreign key

DIWAKAR EDUCATION HUB Page 45

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
c) Super key a) Primary keys
d) Domain b) Functional dependencies
Answer: c c) Check clause
Explanation: If K → R then k is said to be the d) All of the mentioned
superkey of R i.e. K uniquely identifies every Answer: d
tuple in the relation R. Explanation: Primary keys, Functional
339. X → Y holds on a schema k(K) if? dependencies, Check clause are all used to
a) At least one legal instance satisfies the express database consistency.
functional dependency 343. _________ introduces the Management
b) No legal instance satisfies the functional Data Warehouse (MDW) to SQL Server
dependency Management Studio for streamlined
c) Each and every legal instance satisfies the performance troubleshooting.
functional dependency a) SQL Server 2005
d) None of the mentioned b) SQL Server 2008
Answer: c c) SQL Server 2012
Explanation: X → Y holds on a schema k(K) if d) SQL Server 2014
each and every legal instance satisfies the Answer: b
functional dependency. Even if one instance Explanation: MDW is a set of components
does not satisfy the functional dependency that enable a database developer or
X→ Y does not hold on a schema. administrator to quickly track down problems
340. X→ Y is trivial if? that could be causing performance
a) X ⊂ Y degradation.
b) Y ⊂ X 344. Point out the correct statement.
c) X ⊇ Y a) MDW consist of three components
d) None of the mentioned b) SQL Server Express instances can be
Answer: a targets
Explanation: X→ Y is said to be trivial if X is a c) Setting up the MDW is a one-step process
subset of Y. Thus X ⊂ Y implies X→Y is trivial. d) All of the mentioned
341. Which of the following is not a condition Answer: a
for X→ Y in Boyce codd normal form? Explanation: MDW consists of three
a) X → Y is trivial components: Data Collector, MDW database
b) X is the superkey for the relational schema and MDW reports.
R 345. Which of the following mode allows for
c) Y is the superkey for the relational schema the collection and uploading of data to occur
R on demand?
d) All of the mentioned a) Non-cached mode
Answer: c b) Cached mode
Explanation: Y does not need to be a c) Mixed mode
superkey of the relation for the given d) All of the mentioned
functional dependency to satisfy BCNF. X→ Y Answer: a
must be trivial and X must be the superkey of Explanation: In non-cached mode, collection
the relation R. and upload are on the same schedule.
342. Which of the following is used to express 346. Which of the following scenario favours
database consistency? cached mode?

DIWAKAR EDUCATION HUB Page 46

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
a) Continuous collection of data b) core.supported_collector_types_internal
b) Less frequent uploads c) core.wait_categories
c) Data collection and uploading of jobs on d)
different schedules core.performance_counter_report_group_ite
d) All of the mentioned ms
Answer: d Answer: d
Explanation: Cached mode uses separate Explanation: core.wait_categories contains
schedules for collection and upload. the categories used to group wait types
347. Point out the wrong statement. according to wait_type characteristic.
a) The Data Collection is performed primarily 350. Which of the following table is used in
through SSIS packages that control the the management data warehouse schema
collection frequency on the target that is required for the Server Activity?
b) You should change the database name a) snapshots.query_stat
after creation b) snapshots.os_latch_stats
c) Do not change any of the job specifications c) snapshots.active_sessions
for the data collection and upload jobs d) all of the mentioned
d) None of the mentioned Answer: b
Answer: b Explanation: snapshots.os_latch_stats is a
Explanation: You should not change the System level resource table.
database name after creation, because all of 351. Which of the following is syntax for
the jobs created to manage the database sp_add_collector_type procedure?
collection refer to the database by the a) core.sp_add_collector [
original name and will generate errors if the @collector_type_uid = ] ‘collector_type_uid’
name is changed. b) core.sp_add_collector_type [
348. Which of the following is the best @collector_type_uid = ].
Practice and Caveat for Management Data c) core.sp_add_collector_type [
Warehouse? @collector_type_uid = ] ‘collector_type_uid’
a) Use a centralized server for the MDW d) none of the mentioned
database Answer: c
b) The XML parameters for a single T-SQL Explanation: core.sp_add_collector_type
collection item can have multiple <Query> adds a new entry to the
elements core.supported_collector_types view in the
c) Use a distributed server for the MDW management data warehouse database.
database 352. What does collector_type_id stands for
d) All of the mentioned in the following code snippet?
Answer: a core.sp_remove_collector_type [
Explanation: Centralized server allows you to @collector_type_uid = ] ‘collector_type_uid’
use a single point for viewing reports for a) uniqueidentifier
multiple instances. b) membership role
349. ____________ stores information about c) directory
how the management data warehouse d) none of the mentioned
reports should group and aggregate Answer: a
performance counters. Explanation: collector_type_uid is the GUID
a) core.snapshots_internal for the collector type.

DIWAKAR EDUCATION HUB Page 47

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
353. Which of the following clustering type Answer: d
has characteristic shown in the below figure? Explanation: K-means clustering follows
partitioning approach.
357. Point out the wrong statement.
a) k-means clustering is a method of vector
quantization
b) k-means clustering aims to partition n
observations into k clusters
c) k-nearest neighbor is same as k-means
a) Partitional d) none of the mentioned
b) Hierarchical Answer: c
c) Naive bayes Explanation: k-nearest neighbor has nothing
d) None of the mentioned to do with k-means.
Answer: b 358. Which of the following combination is
Explanation: Hierarchical clustering groups incorrect?
data over a variety of scales by creating a a) Continuous – euclidean distance
cluster tree or dendrogram. b) Continuous – correlation similarity
354. Point out the correct statement. c) Binary – manhattan distance
a) The choice of an appropriate metric will d) None of the mentioned
influence the shape of the clusters Answer: d
b) Hierarchical clustering is also called HCA Explanation: You should choose a
c) In general, the merges and splits are distance/similarity that makes sense for your
determined in a greedy manner problem.
d) All of the mentioned 359. Hierarchical clustering should be
Answer: d primarily used for exploration.
Explanation: Some elements may be close to a) True
one another according to one distance and b) False
farther away according to another. Answer: a
355. Which of the following is finally Explanation: Hierarchical clustering is
produced by Hierarchical Clustering? deterministic.
a) final estimate of cluster centroids 360. Which of the following function is used
b) tree showing how close things are to each for k-means clustering?
other a) k-means
c) assignment of each point to clusters b) k-mean
d) all of the mentioned c) heatmap
Answer: b d) none of the mentioned
Explanation: Hierarchical clustering is an Answer: a
agglomerative approach. Explanation: K-means requires a number of
356. Which of the following is required by K- clusters.
means clustering? 361. Which of the following clustering
a) defined distance metric requires merging approach?
b) number of clusters a) Partitional
c) initial guess as to cluster centroids b) Hierarchical
d) all of the mentioned

DIWAKAR EDUCATION HUB Page 48

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
c) Naive Bayes b) Variety
d) None of the mentioned c) Volume
Answer: b d) None of the mentioned
Explanation: Hierarchical clustering requires Answer: b
a defined distance as well. Explanation: Big data enables organizations
362. K-means is not deterministic and it also to store, manage, and manipulate vast
consists of number of iterations. amounts of disparate data at the right speed
a) True and at the right time.
b) False 366. Which of the following analytical
Answer: a capabilities are provided by information
Explanation: K-means clustering produces management company?
the final estimate of cluster centroids. a) Stream Computing
363. Which of the following term is b) Content Management
appropriate to the below figure? c) Information Integration
d) All of the mentioned
Answer: d
Explanation: With stream computing, store
a) Large Data less, analyze more and make better decisions
b) Big Data faster.
c) Dark Data 367. Point out the wrong statement.
d) None of the mentioned a) The big volume indeed represents Big Data
Answer: b b) The data growth and social media
Explanation: Big data is a broad term for data explosion have changed how we look at the
sets so large or complex that traditional data data
processing applications are inadequate. c) Big Data is just about lots of data
364. Point out the correct statement. d) All of the mentioned
a) Machine learning focuses on prediction, Answer: c
based on known properties learned from the Explanation: Big Data is actually a concept
training data providing an opportunity to find new insight
b) Data Cleaning focuses on prediction, based into your existing data as well guidelines to
on known properties learned from the capture and analysis your future data.
training data 368. Which of the following step is performed
c) Representing data in a form which both by data scientist after acquiring the data?
mere mortals can understand and get a) Data Cleansing
valuable insights is as much a science as b) Data Integration
much as it is art c) Data Replication
d) None of the mentioned d) All of the mentioned
Answer: d Answer: a
Explanation: Visualization is becoming a very Explanation: Data cleansing, data cleaning or
important aspect. data scrubbing is the process of detecting and
365. Which of the following characteristic of correcting (or removing) corrupt or
big data is relatively more concerned to data inaccurate records from a record set, table,
science? or database.
a) Velocity

DIWAKAR EDUCATION HUB Page 49

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
369. 3V’s are not sufficient to describe big c) The additional acquaintance used by a
data. learning algorithm to facilitate the learning
a) True process
b) False d) None of these
Answer: a Answer:C
Explanation: IBM data scientists break big 375: Which of the following is true for
data into four dimensions: volume, variety, Classification?
velocity and veracity. a) A subdivision of a set
370: Which of the following applied on b) A measure of the accuracy
warehouse?
c) The task of assigning a classification
a) write only
d) All of these
b) read only
Answer:A
c) both a & b
376: Data mining is?
d) none of these
a) time variant non-volatile collection of data
Answer:B
b) The actual discovery phase of a knowledge
371: Data can be store , retrive and updated
c) The stage of selecting the right data
in …
d) None of these
a) SMTOP
Answer -:B
b) OLTP
377: ——- is not a data mining functionality?
c) FTP
A) Clustering and Analysis
d) OLAP
B) Selection and interpretation
Answer:B
C) Classification and regression
372: Which of the following is a good
alternative to the star schema? D) Characterization and Discrimination
a) snow flake schema Answer -:B
b) star schema 378: Which of the following can also applied
to other forms?
c) star snow flake schema
a) Data streams & Sequence data
d) fact constellation
b) Networked data
Answer :D
c) Text & Spatial data
373: Patterns that can be discovered from a
given database are which type… d) All of these
a) More than one type Answer -:D
379:Which of the following is general
b) Multiple type always
characteristics or features of a target class of
c) One type only
data?
d) No specific type
a) Data selection
Answer :A
b) Data discrimination
374:Background knowledge is…
c) Data Classification
a) It is a form of automatic learning.
c) Data Characterization
b) A neural network that makes use of a
Answer -:D
hidden layer
380: ——– is the out put of KDD…
a) Query
DIWAKAR EDUCATION HUB Page 50
 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
b) Useful Information 385. Classification and regression are the
c) Data properties of…
d) information a) data analysis
Answer -:B b) data manipulation’
381: What is noise? c) data mining
a) component of a network d) none of these
b) context of KDD and data mining Answer -:C
c) aspects of a data warehouse 386. A class of learning algorithm that tries to
d) None of these find an optimum classification of a set of
Answer -:B examples using the probabilistic theory is
named as …
[Link] that are engaged in sentiment
mining are analyzing data collected from? a) Bayesian classifiers
A. social media sites. b) Dijkstra classifiers
B. in-depth interviews. c) doppler classifiers
C. focus groups. d) all of these
D. experiments. Answer -:A
E. observations. 387. Which of the following can be used for
Answer -:A. social media sites. finding deep knowledge?
Which of the following forms of data mining a) stacks
assigns records to one of a predefined set of b) algorithms
classes? c) clues
(A). Classification d) none of these
(B). Clustering Answer -:C
(C). Both A and B 388. We define a ______ as a subdivison of a
(D). None set of examples into a number of classes.
Answer -:(B). Clustering a) kingdom
383. What is the adaptive system b) tree
management? c) classification
a) machine language techniques d) array
b) machine learning techniques Answer -:C
c) machine procedures techniques 389. Group of similar objects that differ
d) none of these significantly from other objects is named as …
Answer -:B a) classification
384. An essential process used for applying b) cluster
intelligent methods to extract the data c) community
patterns is named as … d) none of these
a) data mining Answer -:B
b) data analysis 390. Combining different type of methods or
c) data implementation information is ….
d) data computation a) analysis
Answer -:A b) computation

DIWAKAR EDUCATION HUB Page 51

 DATABASE MANAGEMENT SYSTEMS UNIT – 4 MCQs
c) stack a) SMTOP
d) hybrid b) OLTP
Answer -:D c) FTP
391. What i sthe name of database having a d) OLAP
set of databases from different vendors, Answer -:B
possibly using different database paradigms? 397: Which of the following is a good
a) homogeneous database alternative to the star schema?
b) heterogeneous database a) snow flake schema
c) hybrid database b) star schema
d) none of these c) star snow flake schema
Answer -:B d) fact constellation
392. What is the strategic value of data Answer -:D
mining? 398: Patterns that can be discovered from a
a) design sensitive given database are which type…
b) cost sensitive a) More than one type
c) technical sensitive b) Multiple type always
d) time sensitive c) One type only
Answer -:D d) No specific type
393. The amount of information with in data Answer -:A
as opposed to the amount of redundancy or 399:Background knowledge is…
noise is known as … a) It is a form of automatic learning.
a) paragraph content b) A neural network that makes use of a
b) text content hidden layer
c) information content c) The additional acquaintance used by a
d) none of these learning algorithm to facilitate the learning
Answer -:C process
394. What is inductive learning? d) None of these
a) learning by hypothesis Answer -:C
b) learning by analyzing 400: Which of the following is true for
c) learning by generalizing Classification?
d) none of these a) A subdivision of a set
Answer -:C b) A measure of the accuracy
395: Which of the following applied on c) The task of assigning a classification
warehouse? d) All of these
a) write only Answer -:A
b) read only
c) both a & b
d) none of these
Answer -:B
396: Data can be store , retrive and updated
in …

DIWAKAR EDUCATION HUB Page 52