BHARAT SINGH

Assignment: -2
Database Management System

Unit :- 01
Q1) What do you mean by DBMS? Explain its advantage and disadvantage.
Ans :- DBMS is a program or group of programs that work in conjunction with the operating system to
create, process, store, retrieve, control and manage the data. It acts as an interface between the
application program and the data stored in the database.

Advantages of DBMS are as: -

1. Controlled redundancy: - In a traditional file system, each application program has its own data,
which causes duplication of common data items in more than one file. This duplication/redundancy
requires multiple updating for a single transaction and wastes a lot of storage space. We cannot
eliminate all redundancy due to technical reasons. But in a database, this duplication can be
carefully controlled, that means the database system is aware of the redundancy and it assumes the
responsibility for propagating updates.
2. Data consistency: The problem of updating multiple files in traditional file system leads to
inaccurate data as different files may contain different information of the same data item at a given
point of time. This causes incorrect or contradictory information to its users. In database systems,
this problem of inconsistent data is automatically solved by controlling the redundancy.

3. Program data independence: The traditional file systems are generally data dependent, which implies
that the data organization and access strategies are dictated by the needs of the specific application and
the application programs are developed accordingly. However, the database systems provide an
independence between the file system and application program, that allows for changes at one level of
the data without affecting others. This property of database systems allow to change data without
changing the application programs that process the data.

4. Sharing of data: In database systems, the data is centrally controlled and can be shared by all
authorized users. The sharing of data means not only the existing applications programs can also share
the data in the database but new application programs can be developed to operate on the existing
data. Furthermore, the requirements of the new application programs may be satisfied without creating
any new file.

5. Enforcement of standards: In database systems, data being stored at one central place, standards can
easily be enforced by the DBA. This ensures standardised data formats to facilitate data transfers
between systems. Applicable standards might include any Or all of the following—departmental,
installation, organizational, industry, corporate, National or international.

6. Improved data integrity: Data integrity means that the data contained in the Database is both
accurate and consistent. The centralized control property allow Adequate checks can be incorporated to
BHARAT SINGH

provide data integrity. One integrity check That should be incorporated in the database is to ensure that
if there is a reference To certain object, that object must exist.

7. Improved security: Database security means protecting the data contained in the Database from
unauthorised users. The DBA ensures that proper access procedures Are followed, including proper
authentical schemes for access to the DBMS and Additional checks before permitting access to sensitive
data. The level of security Could be different for various types of data and operations.

8. Data access is efficient: The database system utilizes different sophisticated techniques To access the
stored data very efficiently.

9. Conflicting requirements can be balanced: The DBA resolves the conflicting Requirements of various
users and applications by knowing the overall requirements Of the organization. The DBA can structure
the system to provide an overall service That is best for the organization.

10. Improved backup and recovery facility: Through its backup and recovery subsystem, The database
system provides the facilities for recovering from hardware or software Failures. The recovery
subsystem of the database system ensures that the database Is restored to the state it was in before the
program started executing, in case of System crash.

11. Minimal program maintenance: In a traditional file system, the application programs with the
description of data and the logic for accessing the data are built individually. Thus, changes to the data
formats or access methods results in the need to modify the application programs. Therefore, high
maintenance effort are required. These are Reduced to minimal in database systems due to
independence of data and application Programs.

12. Data quality is high: The quality of data in database systems are very high as Compared to
traditional file systems. This is possible due to the presence of tools And processes in the database
system.

13. Good data accessibility and responsiveness: The database systems provide query Languages or
report writers that allow the users to ask ad hoc queries to obtain The needed information immediately,
without the requirement to write application Programs (as in case of file system), that access the
information from the database. This is possible due to integration in database systems.

14. Concurrency control: The database systems are designed to manage simultaneous (concurrent)
access of the database by many users. They also prevent any loss of Information or loss of integrity due
to these concurrent accesses.

15. Economical to scale: In database systems, the operational data of an organization is Stored in a
central database. The application programs that work on this data can be built with very less cost as
compared to traditional file system. This reduces overall costs of operation and management of the
database that leads to an economical scaling.

Disadvantages of DBMS are as: -

1. Complexity increases: The data structure may become more complex because of the Centralised
database supporting many applications in an organization. This may lead to difficulties in its
management and may require professionals for management.
BHARAT SINGH

2. Requirement of more disk space: The wide functionality and more complexity Increase the size of
DBMS. Thus, it requires much more space to store and run than the traditional file system.
3. Additional cost of hardware: The cost of database system’s installation is much more. It depends on
environment and functionality, size of the hardware and maintenance Costs of hardware.
4. Cost of conversion: The cost of conversion from old file-system to new database System is very high.
In some cases, the cost of conversion is so high that the cost of DBMS and extra hardware becomes
insignificant. It also includes the cost of training Manpower and hiring the specialized manpower to
convert and run the system.
5. Need of additional and specialized manpower : Any organization having database systems, need to
be hire and train its manpower on regular basis to design and implement databases and to provide
database administration services.

6. Need for backup and recovery: For a database system to be accurate and available all times, a
procedure is required to be developed and used for providing backup copies to all its users when
damage occurs.

7. Organizational conflict: A centralised and shared database system requires a consensus on data
definitions and ownership as well as responsibilities for accurate data maintenance.

8. More installation and management cost : The big and complete database systems are more costly.
They require trained manpower to operate the system and has additional annual maintenance and
support costs.

Q2) What do you mean by Database Administrator ?And What are the responsibility and
function of DBA ?
Ans:- Database Administrator (DBA) is a person who have complete control over database of
any enterprise. DBA is responsible for overall performance of database. He is free to take
decisions for database and provides technical support. He is concerned with the Back-End of
any project.
The main responsibilities of DBA are as follows:

1. Deciding the conceptual schema or contents of database: DBA decides the data, Fields, tables,
queries, data types, attributes, relations, entities or you can say that he Is responsible for overall
logical design of database.
2. Deciding the internal schema of structure of physical storage: DBA decides how the data is
actually stored at physical storage, how data is represented at physical Storage.
3. Deciding users: DBA gives permission to users to use database. Without having Proper
permission, no one can access data from database.
4. Deciding user view: DBA decides different views for different users.
5. Granting of authorities: DBA decides which user can use which portion of database. DBA gives
authorities or rights to data access. User can use only that data on which Access right is granted
to him.
BHARAT SINGH

6. Deciding constraints: DBA decides various constraints over database for maintaining
Consistency and validity in database.
7. Security: Security is the major concern in database. DBA takes various steps to Make data more
secure against various disasters and unauthorized access of data.
8. Monitoring the performance: DBA is responsible for overall performance of database. DBA
regularly monitors the database to maintain its performance and try to improve it.
9. Backup: DBA takes regular backup of database, so that it can be used during system Failure.
Backup is also used for checking data for consistency.
10. Removal of dump and maintain free space: DBA is responsible for removing Unnecessary data
from storage and maintain enough free space for daily operations. He can also increase storage
capacity when necessary.
11. Checks: DBA also decides various security and validation checks over database to Ensure
consistency.
12. Liaisioning with users: Another task of the DBA is to liaisioning with users and ensure the
availability of the data they require and write the necessary external schemas.

The functions of a database administrator are: -

1. Selection of hardware and software

-Keep up with current technological trends
-Predict future changes
- Emphasis on established off the shelf products
2. Managing data security and privacy
-Protection of data against accidental or intentional loss, destruction, or misuse
-Firewalls
-Establishment of user privileges
-Complicated by use of distributed systems such as internet access and client/ server
technology.
Unit :- 02
Q3) Explain various data models and show comparison between them.

Ans: - A data model provides mechanism to structure the data for the entities being modelled, allow a
set of manipulative operators to be defined on them, and enforce set of constraints to ensure
the accuracy of data.
The various data models are given below: -
1. Hierarchical Model.
2. Network model.
3. Relational model.

1. Hierarchical Model: -
Hierarchical model is based on tree structure. A hierarchical database consists of collection of
records, that are connected to each other by links.
BHARAT SINGH

Record: A record is a collection of attributes; each contains only one data value.
Link: A link is an association between two records.

The tree structure used in hierarchical model is known as rooted tree. The Root node of
that tree is dummy node or an empty node. So, hierarchical model is a collection of rooted
trees. Collection of rooted trees make forest. A rooted tree is also known as database tree.
known as database tree.

The main advantages of hierarchical data model are: -

1. Simplicity: In this model, records are related in form of parent/child relationship. So Performing
various operations (i.e., insertion, deletion etc.) in this tree like structure Is easy and simple to
perform. This results in the simple design of the database Resulting from this model.

2. Integrity of Data: The parent/child relationship between the various records in the Hierarchical
model is represented by a relationship or link. Each child segment can Be linked to only one
parent and a child can only be reached through it parent, so This model promotes data integrity.

4. Data Security: Each child segment can be linked to only one parent and a child can Only be
reached through its parent in this model. So, for deleting the child segment Proper information
of parent segment is needed. Thus, it provides data security which Is enforced by the DBMS.

5. Efficiency: The hierarchical model contains one to many relationships between parent and child.
When the database contains many 1: N relationships between various Records then this model
handles it very efficiently.

6. It is very efficient to handle large number of transactions using this model. This is Mainly
because the links (or relationship) established by the pointer in the various Records are
permanent and cannot be modified.

Disadvantages of Hierarchical Model: -

1. Knowledge of physical level of data storage is required : The requirement for a One-to-many
between parent and child can result in redundancy of data.

2. Complexity: The physical links make it very difficult to expand or modify the Database, changes
typically require substantial rewriting efforts

3. Inflexibility: The basic problem occurs with this model is that they are not flexible Enough to
establish all the relationships (many-to-many etc.) which occur in the real World. Usually there
are one to many relationships between the records, established by pointers which are
BHARAT SINGH

permanent and cannot be modified in case of other cases Where relationships (like many to
many etc.) exist.

4. Lack of querying facilities: The lack of declarative querying facilities and need for Navigation
of pointers to access needed information make querying rather complex. It does not provide
the adhoc query capability easily.

5. Database management problems: In this model, the modifications to the data Structure
leads to significant modifications to application programs that access the Database. Also
new relations or nodes result in complex system management tasks.

6. Problems with data manipulation operations: Various problems are encountered While
performing various operations like insertion, deletion and updation. Moreover, the data
retrieval is also very complex and asymmetric. Therefore, a better model is Needed to solve
these problems.

7. Lack of standards: This model does not have any specific or precise standard for Database
design and modelling.

2. Network Model: -

Network model is based on graph structure. A network database consists of collection of

records, which are connected to each other by links.
Record: A record is a collection of attributes; each contain only one data value.
Link: A link is an association between two records.
So, network model is a collection of graphs.

Advantages of Network Model: -

1. Eliminate Data Redundancy: In network model, we have only one occurrence for A particular
record in the database which can refer to other records using links or pointers. Since there is no
multiple occurrences of records so it eliminates data redundancy
2. Lesser Storage Requirements : Since in this model, a record occurs only once without repetition
so lesser storage requirements are there for storing the records in the database.
3. Better Performance : In this model the relationships are defined directly which leads to better
performance.
4. Handle Many Types of Relationships : As we know that a large number of different type or
relationships like one to one (1 : 1), one to many (1 : N) and many to many (N : N) etc., exist in
the real world situations. These relationships can easily defined in the database definition using
network model.
5. Easy Access of Data : Since records in the network model are linked together through use of
pointers so it is very easy to move from one owner record to another. Also an application can
access an owner record and all the member records in the set.
BHARAT SINGH

6. Promotes Data Integrity : The network model promotes data integrity because of the required
owner-member relationships i.e., user must first design the owner record and then the member
record.
7. Enforce Standards : The network model was standardized as Codasyl DBTG Model so all the
network database management systems confirm to these standards. The standards include Data
Definition Language (DDL) and Data Manipulation Language (DML) which improves the database
administration and portability.
8. Data Independence : In network model the structure of the data can be changed without
modifying application programs which leads to independence of data but this data
independence is partially not fully unlike the hierarchical model where data independence is
very low.

Disadvantages of Network Model

1. Complexity : Although the conceptual designing of the network model is simple but The design
at the hardware level is very complex because a large number of pointers Are required to show
the relationship between the owner records and the member Records. This makes this model
obsolete for all the practical purposes.
2. Difficulty in Querying Data : For querying data in network model the programmer Is forced to
think in terms of links and how to traverse them to get the needed Information. So proper
technical skills are required on part of a programmer.
3. Lack of Structural Independence : Although the network model achieves data Independence but
it fails to achieve structural independence. Since the various records Are linked through
pointers, which forms a navigational chain. So making structural Changes to the databases is
very difficult. If changes are to be made to the database Structure then all the application
programs using it also need to be modified before Accessing the data.

3. Relational Model :-

The relational model is an abstract Theory of data that is based on the mathematical concept of
relations. In relational database, Information is stored in tabular form or in tables. So, it consists
of collection of tables. We Use mathematical terms in relational model. The relational model is
concerned with three Aspects of data: data structure, data integrity, and data manipulation.

Advantages of Relational Model :-

(i) Simplicity : The Relational database model is very easy and simple to design and
Implement at the logical level. The different tables in the database can be designed
Using appropriate attributes and data values very easily. All the relations are
designed In a tabular manner, which helps the user to concentrate on the logical
view of the Database rather than complex internal details of how data is stored.
(ii) Flexible : The Relational database provide flexibility that allows changes to database
Structure to be easily accommodated.
BHARAT SINGH

(iii) Data Independence : Because data resides in tables, the structure of database can
Be changed without having to change any applications that were based on the
Structure. If you are using non relational database you probably have to modify the
Application that will access this information by including pointers to the new data.
But with relational database the information is immediately accessible because it is
Automatically related to other data by virtue of its position in the table.
(iv) Structural Independence : Relational database is only concerned with data and not
With the structures, which improves performance. Hence processing time and
storage Space is comparatively large in relational database but the user is not
required to Know the details of the structure design. The structural flexibility of a
relational Database allows combinations of data to be retrieved that were never
anticipated at The time the database was initially designed.
(v) Query Capability : It makes possible a high level query language i.e., SQL (Structure
Query Language) which avoids complex database navigation. In this model the
queries Are based on logical relationships and processing those queries does not
require Predefined access paths among the data i.e., pointers.
(vi) Matured Technology : Relational model is useful for representing most of the real
World objects and relationship between them. Relationship implementation is very
Easy through the use of a key.
(vii) Ability to easily take advantages of new hardware technology which make things
Easy for the users.

Disadvantages of Relational Model :-

(i) The relational database use a simple mapping of logical tables to physical structures.
Indexing and hashing techniques are used for access to table data and for certain
Constraint processing. This severally limits the performance.
(ii) The most significant limitation of relational model is its limited ability to deal with
Binary large objects such as images, spreadsheets, e-mail messages, documents etc.
(iii) Since this model has the ability to easily take advantage of new hardware
technology To run smoothly, so large hardware overheads are incurred. This make it
a costly Affair.
(iv) Mapping objects to relational database can be a difficult skill to learn.
(v) Data Integrity is difficult to ensure with relational databases because no single
Application has control over the data so it is very difficult to ensure that all
applications Are operating under business principles. The individual database will
also create Problems like data duplication, data inconsistency and so on.

Q4) Explain database system architecture.

Ans:- The architecture is a framework for describing database concepts and specifying the structure
Of database system.

Data base system Architecture is divided into three parts: -

BHARAT SINGH

1. Internal level.
2. External level.
3. Conceptual Level.

1.Internal level: -

Internal level describes the actual physical storage of data or the way In which the data is actually
stored in memory. This level is not relational because data is Stored according to various coding
schemes instead of tabular form (in tables). This is the Low level representation of entire database. The
internal view is described by means of an Internal schema.

The internal level is concerned with the following aspects:

 Storage space allocation

 Access paths
 Data compression and encryption techniques
 Record placement etc.

The internal level provides coverage to the data structures and file organizations used to Store data on
storage devices.

2.External level: -

The external level is concerned with individual users. This level Describes the actual view of data seen by
individual users. The external schema is defined By the DBA for every user. The remaining part of
database is hidden from that user. This Means user can only access data of its own interest. In other
words, user can access only That part of database for which he is authorized by DBA. This level is also
relational or Very close to it.

3.Conceptual level: -

The conceptual level is also known as logical level which Describes the overall logical structure of whole
database for a community of users. This level Is relational because data visible at this level will be
relational tables and operators will be Relational operators. This level represents entire contents of the
database in an abstract form In comparison with physical level. Here conceptual schema is defined
which hides the actual Physical storage and concentrate on relational model of database.

Unit: - 03
Q5) What do you mean by database security? What are the various Technique used for it?
Ans:- Database security in an important issue in Database Management System (DBMS) because
Database security in an important issue in Database Management System (DBMS) because of the
importance and sensitivity of data and information of an enterprise. Therefore, the data in the database
system need to be protected from unauthorized access and corruption. Database security allows or
disallows users from performing actions on the objects contained within the database. Almost all
DBMS’s provide discretionary access control to regulate all user access to the objects through privileges.
A privilege is permission to access the objects in a well-defined man of the importance and sensitivity of
data and information of an enterprise. Therefore, the data in the database system need to be protected
BHARAT SINGH

from unauthorized access and corruption. Database security allows or disallows users from performing
actions on the objects contained within the database. Almost all DBMS’s provide discretionary access
control to regulate all user access to the objects through privileges. A privilege is permission to access
the objects in a well-defined manner.

To protect the database from these unauthorized access or security violations, the security Measures
at following levels must be taken:

 Database system: Different access rights to different users can be given so that they Can use
data which they really want. Now, the database is responsible to maintain These restrictions and
security.
 Operating system: Operating System must be secured to unauthorized access. Only A secured
database system cannot maintain security.
 Network: Systems or Computers are connected through LAN’s, internet etc. and data Can be
shared through these networks. So, security within the network software is Important.
 Human factors: Authorization must be given carefully to reduce human errors.
 Physical security: Servers and computer systems must be secured physically from Looting, fire,
failure of systems like disasters.
Security measures at these different levels must be taken to make database secure.

Unit: -04
Q8) What do you mean by data mining? Explain advantage and disadvantage with their
applications.
Ans: - Data mining is a collection of techniques, which is used to find undiscovered patterns by
manipulating large volumes of data. It is a process of mining or discovering of new information. It is used
in conjunction of data warehousing to help in certain types of decisions. It is applied to operational
database with individual transactions.

Data mining involves the use of sophisticated data analysis tools to discover previously Unknown, valid
patterns and actionable information from vast amount of data and using it to make crucial business
decisions

Following are the advantages of data mining:

1. Marketing/Retailing: Data mining provides marketers and retailers with useful and Accurate
trends about their customers purchasing behaviour.
2. Banking/Finance: Data mining can assist financial institutions in areas such as credit Reporting
and loan information.
3. Law enforcement: Data mining helps law enforcers in identifying criminal suspects as well as
capturing them by examining trends in location, crime type etc.
4. Researches: It helps researches by speeding up their data analysing process, which Helps them
to do more work within some time limits.

Disadvantages of Data Mining

BHARAT SINGH

1. Privacy issues: With the widespread use of technology, personal privacy has always Been a
major concern of any country. It is possible that any fraud company can sold Their customers
information.
2. Security issues: Data mining gives access directly to database to different users, which may
cause leakage of secured data.
3. Inaccurate information: Data mining is not 100% accurate. It may contain inaccurate Information
that leads to inconsistency.

Data Mining Applications:-

We can apply data mining technologies on different domains, some of them are as follows:

1. Retail/Marketing:
 Identify buying patterns of customers.
 Market basket analysis.
 Predict response of mailing campaigns.
 Find association among customer demographic characteristics.
 Design of catalogs, store layouts and advertising campaigns.
2. Banking/Finance:
 Detect patterns of fraudulent credit card use.
 Predict customers likely to change their credit card affiliation.
 Performance analysis of finance investments.
 Identify hidden correlations between different financial indicators.
 Identify stock trading rules from historical market data.
3. Insurance:
 Claims analysis i.e., which medical procedures are claimed together.
 Identify behaviour patterns of risky customers.
 Identify fraudulent behaviours.
 Identify customers which will buy new insurance policies.
4. Health care:
 Identify the side effects of drugs.
 Identify the effectiveness of a particular medical treatment.
 Identify the experience of doctor required to handle patients.
 Optimization of processes within a hospital.
 Discovering patterns in radiological images.
5. Transportation:
 Analyse loading patterns.
 Determine the distribution schedules among outlets.
 Identify shortest routes.
6. Manufacturing:
 Optimization of resources like machines, manpower, energy, space, time etc. Identify
the methods to reduce cost of manufacturing and reduce wastage. Identify the cause of
production problems like machine failure.
 Optimal design of manufacturing processes.
BHARAT SINGH