RSR RUNGTA COLLEGE OF ENGINEERING & TECHNOLOGY, BHILAI

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

SUBJECT NAME – DATABASE MANAGEMENT SYSTEM
Course & Semester – PGDCA 2ND

1. Explain database, classify type of database user in DBMS.

ANSWER:-

Database

A collection of organized data stored electronically, allowing efficient retrieval and

manipulation.

DBMS (Database Management System)

Software that manages databases (e.g., MySQL, Oracle), providing data security, backup, and
query processing.

DAIGRAM:-
Types of Database Users:

1. End Users: Non-technical users accessing data via apps (e.g., customers, managers).

2. Application Programmers: Developers writing apps to interact with the DB.

3. Database Administrators (DBAs): Manage DB setup, security, performance, and backups.

4. System Analysts: Analyze data needs and requirements.

5. Database Designers: Design the DB structure (schema, tables).

Advantages:-

-> Data Security: Controlled access

-> Data Sharing: Multiple users

-> Backup & Recovery: Prevents data loss

-> Efficient Data Retrieval: Fast queries

Disadvantages:-

-> Cost: DBMS can be expensive

-> Complexity: Requires expertise

-> Performance Issues: Large DBs can slow down

2. Explain the different type of database languages with example.

ANSWER:

Database languages

1. Data Definition Language(DDL) :-

The DDL stands for Data Definition Language, Which is used to define the database's
internal structure and Pattern of the Database. It is used to define and modify the structure
of the database itself, including the tables, views, indexes and other schema-related objects.
It deals with the creation and modification of database schema, but it doesn't deal with the
data itself.

Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);

2. Data Query Language (DQL) :-

Data Query Language DQL is used to fetch data from the database. The main command is
SELECT, which retrieves records based on the query. The output is returned as a result set (a
temporary table) that can be viewed or used in applications.

Example:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;

3. Data Manipulation Language (DML) :-

Data Manipulation Language DML commands are used to manipulate the data stored in
database tables. With DML, you can insert new records, update existing ones, delete
unwanted data or retrieve information.
Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');

4. Data Control Language (DCL) :-

Data Control Language includes commands such as GRANT and REVOKE which mainly deal
with the rights, permissions and other controls of the database system. These commands are
used to control access to data in the database by granting or revoking permissions.

Example:
GRANT SELECT, UPDATE ON employees TO user_name;

5. Transaction Control Language (TCL) :-

Transaction Control Language Transactions group a set of tasks into a single execution unit.
Each transaction begins with a specific task and ends when all the tasks in the group are
successfully completed. If any of the tasks fail, transaction fails. Therefore, a transaction has
only two results: success or failure.

Example:
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;

3. what is data independence explain with proper example.

ANSWER:
Data independence in a Database Management System (DBMS) is the ability to modify the
schema at one level without affecting the schema at the next higher level. This ensures that
changes in data storage or structure do not disrupt applications or user views.
There are two key types of data independence:
1. Physical Data Independence
2. Logical Data Independence

Physical Data Independence

Physical Data Independence is defined as the ability to make changes in the structure of the
lowest level of the DBMS without affecting the higher-level schemas. Hence, modification in
the Physical level should not result in any changes in the Logical or View levels.
Key Features
 • Changes in physical storage (e.g., creating new files, adding indexes, storing data in a
different location) should not impact the higher-level schemas.
• It ensures optimization of database performance without affecting the overall
structure or user applications.
Example: Suppose we want to improve database performance by creating a new index for
faster retrieval of data or adding a new file system for storage. These modifications should not
affect the logical view of the data, such as how the data is represented in the database tables
or how it is accessed by the us.
Logical Data Independence
Logical Data Independence is the ability to change the logical schema (the structure and
relationships of the data) without affecting the view schema or application programs. This
means that changes at the logical level should not require modifications at the user interface
or application programs that interact with the database.
Key Features
• Changes in logical schema (e.g., adding or deleting attributes, creating new
relationships) should not require changes at the higher-level view schema or
application programs.
• It allows the database structure to evolve over time without affecting how users
interact with the data.
Example:
Logical Changes: Consider adding a new column to a database table (e.g., adding an "email"
column to the "employees" table). If the modification does not require any changes to the
way users interact with the data, this is an example of logical data independence.
Impact on Application Programs: Ideally, application programs or views accessing this table
should not need to change, even if the database schema has been modified.

4. Explain the database architecture with proper example.

ANSWER:
DBMS ARCHITECTURE :-
The DBMS architecture refers to the structural design and interconnected components that
manage and maintain databases efficiently. One of the most popular approaches to
structuring all types of systems, is a client-server architecture where client and server
components are decoupled and work independently to manage data, application logic and
user interaction.
1. Single Tier Architecture
In DBMS In a single-tier architecture in DBMS, the user has direct access to the database. The
user interacts with DBMS architecture directly, making changes that immediately reflect on
the database. This architecture does not provide user friendly equipment for end users and is
perfect for developing local applications where programmers need fast and direct access to
the database.

This type of architecture of a database system is best suited for scenarios where:
• Data is rarely modified.
• Only a single user is accessing the database.
• A seamless way to interact with or modify the database is required.
Example:
When learning SQL, developers often set up a database on their own computer. This lets them
run queries directly without a network, which is a simple 1-tier DBMS setup.
2. Two-Tier Architecture in DBMS
In a 2-tier paradigm, the application is located on the client-side machine and uses query
language statements to access database system capabilities on the server machine. For
establishing communication between the client and server machines, application program
interface standards like ODBC and JDBC are engaged. The structure of the 2-tier database is
shown in Fig.

Example:
Imagine withdrawing cash at a bank. The banker enters your account details and withdrawal
amount into the system. The client application (banker’s interface) sends a request to the
server-side database to check your balance and process the transaction. This setup is a classic
example of two-tier DBMS architecture.
3. Three-Tier Architecture in DBMS :
The client machine serves as the front end and does not directly interface with the database
in a three-tier architecture. Instead, to access data, the client end interacts with an application
server, which it subsequently does with a database system. The application server contains
the business logic (which actions to take and under what circumstances) for the application.
A three-tier architecture in general is shown in Figure. It fits web applications that operate on
the World Wide Web (WWW).

Example:
Consider an online shopping platform like Amazon. When a user places an order, the client-
side interface (website or app) sends the request to an application server. The application
server processes the order, verifies stock availability, and updates the database accordingly.
The client never communicates directly with the database; the application server handles all
interactions.

5. what do you understand by data model ? Specify data model types with suitable diagram.
ANSWER :
Data Model
Data Model gives us an idea that how the final system will look like after its complete
implementation. It defines the data elements and the relationships between the data
elements. Data Models are used to show how data is stored, connected, accessed and
updated in the database management system. Here, we use a set of symbols and text to
represent the information so that members of the organisation can communicate and
understand it. Though there are many data models being used nowadays but the Relational
model is the most widely used model. Apart from the Relational model, there are many other
types of data models . Some of the Data Models in DBMS are:
 1. Hierarchical Model
2. Network Model
3. Entity-Relationship Model
4. Relational Model
Hierarchical Model
Hierarchical Model was the first DBMS model. This model organises the data in the
hierarchical tree structure. The hierarchy starts from the root which has root data and then it
expands in the form of a tree adding child node to the parent node. This model easily
represents some of the real-world relationships like food recipes, sitemap of a website etc.
Example: We can represent the relationship between the shoes present on a shopping website
in the following way:

Features of a Hierarchical Model

1. One-to-many relationship: The data here is organised in a tree-like structure where
the one-to-many relationship is between the datatypes. Also, there can be only one
path from parent to any node. Example: In the above example, if we want to go to the
node sneakers we only have one path to reach there i.e through men's shoes node.
2. Parent-Child Relationship: Each child node has a parent node but a parent node can
have more than one child node. Multiple parents are not allowed.
3. Deletion Problem: If a parent node is deleted then the child node is automatically
deleted.
4. Pointers: Pointers are used to link the parent node with the child node and are used
to navigate between the stored data. Example: In the above example the 'shoes' node
points to the two other nodes 'women shoes' node and 'men's shoes' node.
Network Model
This model is an extension of the hierarchical model. It was the most popular model before
the relational model. This model is the same as the hierarchical model, the only difference is
that a record can have more than one parent. It replaces the hierarchical tree with a graph.
Example: In the example below we can see that node student has two parents i.e. CSE
Department and Library. This was earlier not possible in the hierarchical model.

Features of a Network Model

1. Ability to Merge more Relationships: In this model, as there are more relationships so
data is more related. This model has the ability to manage one-to-one relationships as
well as many-to-many relationships.
2. Many paths: As there are more relationships so there can be more than one path to
the same record. This makes data access fast and simple.
3. Circular Linked List: The operations on the network model are done with the help of
the circular linked list. The current position is maintained with the help of a program
and this position navigates through the records according to the relationship.
Entity-Relationship Model
Entity-Relationship Model or simply ER Model is a high-level data model diagram. In this
model, we represent the real-world problem in the pictorial form to make it easy for the
stakeholders to understand. It is also very easy for the developers to understand the system
by just looking at the ER diagram. We use the ER diagram as a visual tool to represent an ER
Model. ER diagram has the following three components:
• Entities: Entity is a real-world thing. It can be a person, place, or even a concept.
Example: Teachers, Students, Course, Building, Department, etc are some of the
entities of a School Management System.
• Attributes: An entity contains a real-world property called attribute. This is the
characteristics of that attribute. Example: The entity teacher has the property like
teacher id, salary, age, etc.
• Relationship: Relationship tells how two attributes are related. Example: Teacher
works for a department.
Features of ER Model
• Graphical Representation for Better Understanding: It is very easy and simple to
understand so it can be used by the developers to communicate with the stakeholders.
• ER Diagram: ER diagram is used as a visual tool for representing the model.
• Database Design: This model helps the database designers to build the database and
is widely used in database design.
Relational Model
Relational Model is the most widely used model. In this model, the data is maintained in the
form of a two-dimensional table. All the information is stored in the form of row and columns.
The basic structure of a relational model is tables. So, the tables are also called relations in the
relational model. Example: In this example, we have an Employee table.

Features of Relational Model

• Tuples: Each row in the table is called tuple. A row contains all the information about
any instance of the object. In the above example, each row has all the information
about any specific individual like the first row has information about John.
 • Attribute or field: Attributes are the property which defines the table or relation. The
values of the attribute should be from the same domain. In the above example, we
have different attributes of the employee like Salary, Mobile_no, etc.

6. Differentiate between Data,information and knowledge with suitable examples.

ANSWER :
DATA:-
Data is raw, unprocessed facts and figures without context. This information, in its raw form,
can be difficult to understand or apply without extra processing.
Example:
“23, 30, 28, 35” – these numbers alone are just data.
Key Characteristics:
• Unorganized
• No meaning on its own
• Collected from sensors, logs, user input
INFORMATION:-
Information is data that has been processed, organized, or structured to convey meaning and
significance.
Unlike raw data, information is more comprehensible and provides context that aids in
understanding the data.
The transformation from data to information generally involves several key steps:
1. Data Collection: Gathering raw data from various sources, such as weather stations,
satellites, or sensors.
2. Data Cleaning: Ensuring the data is accurate, consistent, and free from errors or outliers.
3. Data Analysis: Applying statistical methods and computational algorithms to identify
patterns, correlations, and trends within the data.
4. Data Interpretation: Making sense of the analyzed data by providing context and explaining
what the data signifies.
5. Data Presentation: Organizing the data in a coherent and visually appealing manner, such
as charts, graphs, or reports, to effectively communicate the information.
Example:
“Average temperature last week was 29°C.”
Key Characteristics:
• Structured
 • Contextual
• Used for reports and summaries
KNOWLEDGE:
Knowledge is information that has undergone further analysis, synthesis, and refinement,
resulting in a deeper understanding and more profound insights.
Knowledge builds on information by adding experience, context, interpretation, and
judgment, allowing it to be applied to solve problems, develop new products, or create
innovative solutions.
It is the culmination of a continuous learning process, where raw data is transformed into
information and subsequently into knowledge, empowering you to make informed decisions
and take effective actions.
The process of transforming information into knowledge involves several key steps:
1. Critical Analysis: Evaluating and interpreting information to understand its implications and
relevance.
2. Synthesis: Combining different pieces of information to form a comprehensive
understanding or new concepts.
3. Refinement: Continuously updating and improving knowledge based on new data, insights,
and experiences.
4. Application: Using knowledge to address real-world problems, innovate, and create value
Example:
“In a city prone to earthquakes, floods, and hurricanes, raw data like temperature readings,
past disaster records, and geographical details are collected.
When processed, this data becomes information about climate conditions and disaster
patterns.
Further analysis turns this information into knowledge that explains how these factors interact
and influence each other.”
Key Characteristics:
• Actionable
• Context + meaning
• Used for decisions

Difference Between Data vs Information vs Knowledge

Aspect Data Information Knowledge

 Processed, organized Application of experience,
Definition Raw facts and figures and contextualized context, and insights to
data information

Numbers, text, Summarized, analyzed, Awareness gained from

Form
images, videos, etc. and structured data information processing

Understanding of a process or
Meaningful and system and applying that to
Meaning Little or no meaning relevant for decision- solve problems, create new
making things, or make better
decisions

Individual data
Structured and Synthesized understanding and
points, lacks
Focus interconnected data for expertise for effective
meaningful
informed analysis application
interpretation

Data can guide Information enables Knowledge drives sound

Decision-
decisions but requires informed decision- decision-making based on
making
interpretation making experience

Transformed data for

Requires structuring Application of insights through
meaningful
Transformation and processing for experience and underpinning
interpretation and
meaningful analysis principles
decision-making

Secondary and created

Source Primary Experience and education
from data

Graph showing Insight gained from the graph

Temperature readings
Example temperature trends that temperature increases in
recorded
over time the summer

7. Describe the role of data dictionary in DBMS and list its typically contents.
ANSWER:
A data dictionary in a Database Management System (DBMS) serves as a centralized
repository of metadata, providing detailed information about the structure, organization, and
relationships of data within a database. It acts as a reference guide for database
administrators, developers, and users, ensuring consistency, accuracy, and clarity in data
usage across systems and teams. It is essential for managing complex databases, supporting
data governance, and maintaining data integrity.
Typical Contents of a Data Dictionary:
• Data Elements: Names, data types (e.g., VARCHAR, INTEGER), storage formats, length,
validation rules, and default values.
• Tables: Table names, owners, creation and last access dates, number of rows and
columns, and associated constraints (e.g., primary keys, foreign keys).
• Indexes: Index names, associated attributes, location, characteristics, and creation
dates.
• Relationships: Cardinality, connectivity, and whether relationships are mandatory or
optional (e.g., one-to-many, many-to-many).
• Constraints and Rules: Integrity constraints, triggers, default values, and business
rules.
• Programs and Views: SQL queries, reports, application formats, and database views
used to access data.
• User and Security Information: User accounts, roles, privileges, and access
permissions.
• Auditing and Usage: Information on who accessed or modified data, and usage
patterns.
• Database Schema: Definitions of all schema objects (tables, views, sequences,
procedures, etc.) and their interconnections.

8. Describe the different of data models :- 1 . Hierarchical 2 . Network 3 . Relational

ANSWER:
Hierarchical Data Model:
The hierarchical data model is the oldest type of the data model. It was developed by IBM in
1968. It organizes data in a tree-like structure. Hierarchical model consists of the following :
• It contains nodes which are connected by branches.
• The topmost node is called the root node.
• If there are multiple nodes appear at the top level, then these can be called root
segments.
• Each node has exactly one parent.
 • One parent may have many children.
Network Data Model:
It is the advance version of the hierarchical data model. To organize data it uses directed
graphs instead of the tree-structure. In this child can have more than one parent. It uses the
concept of the two data structures i.e. Records and Sets.

In the above figure, Project is the root node which has two children i.e. Project 1 and Project
2. Project 1 has 3 children and Project 2 has 2 children. Total there are 5 children i.e
Department A, Department B and Department C, they are network related children as we said
that this model can have more than one parent. So, for the Department B and Department C
have two parents i.e. Project 1 and Project 2.

Relational Data Model:

The relational data model was developed by E.F. Codd in 1970. There are no physical links as
they are in the hierarchical data model. Following are the properties of the relational data
model :
• Data is represented in the form of table only.
• It deals only with the data not with the physical structure.
• It provides information regarding metadata.
• At the intersection of row and column there will be only one value for the tuple.
• It provides a way to handle the queries with ease.
Difference Between Hierarchical, Network and Relational Data Model
 Hierarchical Data Model Network Data Model Relational Data Model

In this model, to store It organizes records in the

data hierarchy method is It organizes records to one form of table and
used. It is the oldest another through links or relationship between
method and not in use pointers. tables are set using
today. common fields.

To organize records, it It organizes records in the It organizes records in the

uses tree structure. form of directed graphs. form of tables.

In addition to 1:1 and 1:n it In addition to 1:1 and 1:n

It implements 1:1 and 1:n
also implements many to it also implements many
relations.
many relationships. to many relationships.

The logical representation

Pointers are used to A linked list is used to is used with rows and
establish relationships establish a relationship columns to depict
among records physically. among records physically. relationship among
records.

Insertion anomaly exits in

this model i.e. child node There is no insertion There is no insertion
cannot be inserted anomaly. anomaly.
without the parent node.

Deletion anomaly exists in

There is no deletion
this model i.e. it is difficult There is no deletion anomaly.
anomaly.
to delete the parent node.

Update leads to Updating a record is easy

inconsistency problems and simple with the
No such problem as only one
because of the existence process of normalization,
instance of records exist.
of multiple instances of a the redundant data gets
child record. removed.
 Hierarchical Data Model Network Data Model Relational Data Model

This model lacks data There is partial data This model provides data
independence. independence in this model. independence.

No such facility for

No such facility for querying SQL-based declarative
querying database is
database is supported. querying is supported.
supported.

It is used to access the It is used to access the data It is used to access the
data which is complex and which is complex and data which is complex and
asymmetric. symmetric. symmetric.

Difficult to design a database

It is easy to comprehend
Difficult to design a and manipulate a database
due to concealed physical
database because of its because of its complexity.
level details from end-
complexity. Hence, it imposes a burden
users.
on the programmer.

It is flexible as compared to It is flexible as compared

It is less flexible.
the hierarchical model. to the hierarchical model.

VAX-DBMS, DMS-1100 of It is mostly used in real

&XML and XAML use this
UNIVAC and SUPRADBMS's world applications. Oracle,
model.
use this model. SQL.

9. What is DBMS? Explain the advantages of DBMS over traditional file systems
ANSWER:-
A Database Management System (DBMS) is software that allows users to define, create,
maintain, and control access to a database. It provides a structured way to store, manage,
and retrieve data efficiently, ensuring data integrity, security, and consistency. Unlike
traditional file systems, which store data as unstructured files, a DBMS organizes data into
tables with defined relationships, enabling complex queries and centralized management.
Key Advantages of DBMS over Traditional File Systems:
• Reduced Data Redundancy & Inconsistency:
DBMS eliminates duplicate data through normalization, ensuring that data is stored
in a single, centralized location. This prevents inconsistencies that arise when the
same data is stored in multiple files.
• Improved Data Integrity:
DBMS enforces integrity constraints (e.g., primary keys, foreign keys, data types)
automatically, ensuring that only valid data is entered and maintained.
• Enhanced Data Security:
DBMS offers advanced security mechanisms, including user authentication, role-
based access control, and authorization, protecting data from unauthorized access.
• Concurrent Access & Transaction Management:
DBMS supports multiple users accessing data simultaneously through built-in
concurrency control and ensures ACID properties (Atomicity, Consistency, Isolation,
Durability) for reliable transactions.
• Efficient Query Processing:
Users can retrieve data using standardized query languages like SQL, enabling
powerful, flexible, and fast data retrieval without writing custom programs.
• Automatic Backup and Recovery:
DBMS includes built-in backup and recovery mechanisms to restore data after system
crashes or failures, minimizing data loss.
• Data Independence:
DBMS provides logical and physical data independence, meaning changes to the
database structure or storage do not affect application programs.
• Scalability and Performance:
Designed for large datasets, DBMS uses indexing, caching, and optimization
techniques to handle high volumes of data efficiently, unlike file systems that
degrade with scale.
• Centralized Data Management:
Data is stored centrally, making sharing, maintenance, and updates easier across
multiple users and applications.

10. Discuss the role and responsibilities of a Database Administrator(DBA).

ANSWER:
A Database Administrator (DBA) is an individual or person responsible for controlling,
maintaining, coordinating, and operating a database management system. Managing,
securing, and taking care of the database systems is a prime responsibility. They are
responsible and in charge of authorizing access to the database, coordinating, capacity,
planning, installation, and monitoring uses, and acquiring and gathering software and
hardware resources as and when needed. Their role also varies from configuration, database
design, migration, security, troubleshooting, backup, and data recovery. Database
administration is a major and key function in any firm or organization that is relying on one or
more databases. They are overall commanders of the Database system.
Role and Duties of Database Administrator (DBA)
• Decides Hardware: They decide on economical hardware, based on cost,
performance, and efficiency of hardware, and best suits the organization. It is
hardware that is an interface between end users and the database.
• Manages Data Integrity and Security: Data integrity needs to be checked and
managed accurately as it protects and restricts data from unauthorized use. DBA eyes
on relationships within data to maintain data integrity.
• Database Accessibility: Database Administrator is solely responsible for giving
permission to access data available in the database. It also makes sure who has the
right to change the content.
• Database Design: DBA is held responsible and accountable for logical, physical design,
external model design, and integrity and security control.
• Database Implementation: DBA implements DBMS and checks database loading at
the time of its implementation.
• Query Processing Performance: DBA enhances query processing by improving speed,
performance, and accuracy.
• Tuning Database Performance: If the user is not able to get data speedily and
accurately then it may lose organization's business. So by tuning SQL commands DBA
can enhance the performance of the database.
Responsibilities :
1. Software Installation and Maintenance
A DBA often collaborates on the initial installation and configuration of a new Oracle, SQL
Server etc database. The system administrator sets up hardware and deploys the operating
system for the database server, and then the DBA installs the database software and
configures it for use. As updates and patches are required, the DBA handles this ongoing
maintenance.
And if a new server is needed, the DBA handles the transfer of data from the existing system
to the new platform.
2. Data Extraction, Transformation, and Loading
Known as ETL, data extraction, transformation, and loading refers to efficiently importing large
volumes of data that have been extracted from multiple systems into a data warehouse
environment.
This external data is cleaned up and transformed to fit the desired format so that it can be
imported into a central repository.
3. Specialised Data Handling
Today’s databases can be massive and may contain unstructured data types such as images,
documents, or sound and video files. Managing a very large database (VLDB) may require
higher-level skills and additional monitoring and tuning to maintain efficiency.
4. Database Backup and Recovery
DBAs create backup and recovery plans and procedures based on industry best practices, then
make sure that the necessary steps are followed. Backups cost time and money, so the DBA
may have to persuade management to take necessary precautions to preserve data.
System admins or other personnel may actually create the backups, but it is the DBA’s
responsibility to make sure that everything is done on schedule.
In the case of a server failure or other form of data loss, the DBA will use existing backups to
restore lost information to the system. Different types of failures may require different
recovery strategies, and the DBA must be prepared for any eventuality. With technology
change, it is becoming ever more typical for a DBA to backup databases to the cloud, Oracle
Cloud for Oracle Databases and MS Azure for SQL Server.
5. Security
A DBA needs to know potential weaknesses of the database software and the company’s
overall system and work to minimise risks. No system is one hundred per cent immune to
attacks, but implementing best practices can minimise risks.
In the case of a security breach or irregularity, the DBA can consult audit logs to see who has
done what to the data. Audit trails are also important when working with regulated data.