ANANGPURIA SCHOOL OF MANAGEMENT AND TECHNOLOGY

Question Bank

Subject with Code: DBMS(BCA -23-202)

Course & Branch: BCA (DATA SCIENCE) Year & Sem:2025-2026 & 4th sem

I. Short questions: -
1. What are advantages and disadvantages of database system?

2. Define External schema.

3. What is data independence? What is difference between physical and logical data independence?

4. Define the following Super key, primary key, foreign key.

5. What is DDL,DML and DCL?

6.What do you mean by DBMS catalog and meta data?

7.What is generalization,specialization and aggregation?

8.What is Entity set?

9.What are mapping cardinalities?

10.What is distributed database? Write one advantage of distributed database.

11.Explain usage of commercial DBMS.

II. Long questions: -

1. a) What are various components of DBMS with the help of neat diagram?

b) Elaborate in depth the three schema architecture of DBMS.

2. What is ER Model? Discuss basic concepts used in ER Model including design notation and various
type of constraints.

3. a) Give reason why logical data independence is difficult to achieve in database.

b) Differentiate between weak entity and strong entity with ER diagram notations. How a weak
entity set can be converted into strong entity set?

4. a)Explain three tier client server architecture of DBMS.

b) Differentiate between file system and database system approach. Discuss pros and cons of both
approaches

5. a) Differentiate between Hierarchical, Network and Relational data model.

6. Draw an ER diagram for library management system which has student, teacher, inventory and
requirement? Clearly highlight entities, relationship, primary key and foreign key.

7. What do you understand by the term integrity constraint? Explain entity and referential integrity
constraint along with example.

8. a) What are responsibilities of database administrator?

b) What do you understand by relational model? What are the characteristics of relational database
management system?

9.a) Draw E-R Diagram for Hospital management system.

9.) Explain insertion, deletion and updation anamolies with suitable example.

10.Suppose you are given the following requirements for a simple database for National Hockey
League(NHL)

(i)NHL has many teams.

(ii)Each team has a name,a city,a coach,a captain,a set of players.

(iii)Each player belongs to only one team.

(iv)Each player has a name position(such as left wing or goalie),a skill level and a set of injury
records.

(v)A team captain is also a player.

(vI)A game is played between two teams(host_team and guest_team)and has a date(such as
May11th,1999) and a score(such as 4 or 2).

Construct a clean and concise ER diagram for NHL database. List your assumptions and clearly
indicate the cardinality mapping as well as any role indicators in ER diagram.

11.What are applications of database system?

. Short Questions

1. What are advantages and disadvantages of database system?

Advantages:

o Data Redundancy Control: Centralized control reduces redundant data.

o Data Integrity: Enforces data integrity and consistency.
o Data Security: Centralized security mechanisms protect data.
o Data Sharing: Multiple users can access the data concurrently.
o Data Independence: Logical and physical data independence allow easier
changes to the schema without affecting applications.

Disadvantages:

o Complexity: DBMS systems are complex and require specialized knowledge

to set up and maintain.
o Cost: High initial setup and ongoing maintenance costs.
o Performance Issues: DBMS may have performance overhead due to
centralization and concurrency control.
o Security Risks: A centralized system can be a target for attacks if not
properly secured.
2. Define External schema.

An External Schema refers to the user's view of the database. It is a level of

abstraction in the three-schema architecture that defines how data is organized for
end-users, showing only relevant data to specific users, and ensuring privacy and
security.

3. What is data independence? What is the difference between physical and logical
data independence?

Data Independence is the ability to change the schema at one level of the database
system without having to change the schema at the next higher level.

o Physical Data Independence refers to the ability to change the physical

storage structure (like file organization, indexing methods) without affecting
the logical schema.
o Logical Data Independence refers to the ability to change the logical schema
(the structure and relationships of data) without affecting the external schema
or applications.
4. Define the following: Super key, primary key, foreign key.
o Super Key: A set of attributes that can uniquely identify a tuple (row) in a
table. It may include unnecessary attributes.
o Primary Key: A minimal set of attributes that uniquely identifies a tuple in a
table. It must be unique and not null.
o Foreign Key: An attribute (or set of attributes) in one table that refers to the
primary key of another table. It is used to establish and enforce a link between
the two tables.
5. What is DDL, DML, and DCL?
o DDL (Data Definition Language): Defines the structure of the database.
Includes commands like CREATE, ALTER, and DROP.
o DML (Data Manipulation Language): Deals with the manipulation of data.
Includes commands like INSERT, UPDATE, DELETE, and SELECT.
o DCL (Data Control Language): Deals with the permissions and access
control of the database. Includes commands like GRANT and REVOKE.
6. What do you mean by DBMS catalog and metadata?
o DBMS Catalog: A collection of data that describes the structure of the
database (e.g., tables, columns, indexes).
o Metadata: Data about the data, including definitions, types, constraints, and
relationships, stored in the DBMS catalog.
7. What is generalization, specialization, and aggregation?
o Generalization: The process of abstracting common features from two or
more entities to form a higher-level entity (e.g., creating a "Person" entity
from "Employee" and "Student").
o Specialization: The process of dividing an entity into multiple sub-entities
based on certain attributes (e.g., dividing "Person" into "Employee" and
"Student").
o Aggregation: The process of combining multiple entities into a higher-level
entity when there is a relationship between them (e.g., a "Department" entity
that includes both "Employee" and "Manager").
8. What is Entity set?

An Entity Set is a collection of similar types of entities that share common properties.
Each entity in the set is a real-world object or concept.

9. What are mapping cardinalities?

Mapping Cardinalities define the relationships between entities in terms of numbers.

They specify how many entities from one set can relate to entities from another set.
Common types include:

o 1:1 (one-to-one)
o 1:N (one-to-many)
o M:N (many-to-many)
10. What is distributed database? Write one advantage of distributed database.

A Distributed Database is a database that is stored across multiple physical

locations. Data may be spread across different machines, which can be located in the
same or different geographical locations.

Advantage: It improves data availability and fault tolerance by distributing the data
across multiple sites.

11. Explain the usage of commercial DBMS.

Commercial DBMS (like Oracle, SQL Server, MySQL, etc.) are used to manage
large-scale databases in organizations. They provide:
 o Support for multiple users and concurrent transactions.
o High reliability, performance, and security features.
o Scalable solutions for large volumes of data.
o Tools for backup, recovery, and maintenance.

II. Long Questions

Diagram:

From notebook

1. a) Components of DBMS with Diagram:

A Database Management System (DBMS) is a software system that facilitates the

management of databases. It allows users to create, store, manage, and retrieve data
efficiently while maintaining the integrity and security of the data. A DBMS consists of
various components that work together to provide a fully functional system.

Components of DBMS:

1. DBMS Engine: The core part of the DBMS responsible for managing data storage,
retrieval, and updates. It handles tasks such as query processing, transaction
management, and data manipulation.
2. Database Schema: The schema defines the logical structure of the database, such as
the tables, views, and relationships. It acts as a blueprint for the organization of data.
3. Database Manager: The database manager includes the Query Processor and
Transaction Manager:
o Query Processor: Translates high-level user queries into low-level operations
that the DBMS can understand and execute.
o Transaction Manager: Ensures that database operations are executed in a
manner that adheres to the ACID properties (Atomicity, Consistency,
Isolation, Durability).
4. Data Dictionary/Repository: A system catalog that contains metadata (data about the
data), such as table structures, constraints, and access permissions.
5. Storage Manager: Manages the physical storage of data. It includes the File
Manager, Buffer Manager, and Disk Space Manager, which are responsible for
storing, retrieving, and maintaining the data on disk.
6. Data Access Language (SQL): The language used by users to interact with the
DBMS. SQL is typically used for querying and manipulating data.

b) Elaborate in depth the three-schema architecture of DBMS.

1. b) Three Schema Architecture of DBMS:

The three-schema architecture of a DBMS is a framework for understanding how data is
organized and accessed in a database system. It separates the user's view, the logical view,
and the physical view of the data. This separation provides data abstraction, which helps in
maintaining data independence.

Three Layers of Schema Architecture:

1. Internal Schema (Physical Schema):

o This level deals with the physical storage of the data and defines how the data is
stored on the disk. It describes the data's internal organization, including indexing,
file structures, and access paths.
o The internal schema ensures that the system can efficiently store and retrieve data
without exposing the underlying physical structure to users.

2. Conceptual Schema (Logical Schema):

o The conceptual schema represents the logical view of the entire database. It defines
the relationships between the data and includes constraints, entities, attributes, and
tables. It provides a high-level view of the database structure without detailing how
data is physically stored.
o This schema acts as an intermediary between the user and the internal schema.

3. External Schema (View Schema):

o The external schema represents the way users or user groups interact with the data.
It defines individual user views, which are tailored to the specific needs of users or
applications.
o Each user may have a different view of the data, but these views do not affect the
underlying structure of the database.

2. What is ER Model? Basic Concepts and Constraints:

The Entity-Relationship (ER) Model is a conceptual framework used to describe the

structure of a database. It is based on real-world entities and their relationships, providing a
high-level, visual representation of the database design.

Basic Concepts in ER Model:

1. Entity:
o An entity represents a real-world object or concept in the database. For example, a
Student or Course could be entities in a university database.
o Entities have attributes that describe their properties. For example, a Student
entity may have attributes like Student_ID, Name, and Age.

2. Entity Set:
o An entity set is a collection of similar types of entities. For example, all students in a
university form a Student entity set.

3. Attribute:
o An attribute is a property or characteristic of an entity. It represents the data that
needs to be stored. Attributes can be simple (e.g., Name), composite (e.g., Full
Name), or multi-valued (e.g., Phone Numbers).
 4. Relationship:
o A relationship represents an association between two or more entities. For example,
a Student enrolls in a Course, forming a relationship between the two entities.

5. Relationship Set:
o A relationship set is a collection of similar relationships. For example, all enrollments
of students in courses form an Enrollment relationship set.

6. Primary Key:
o A primary key is an attribute or a set of attributes that uniquely identifies an entity
within an entity set. For example, Student_ID can serve as the primary key for the
Student entity.

ER Diagram Notations:

 Rectangle: Represents an entity.

 Ellipse: Represents an attribute.
 Diamond: Represents a relationship.
 Lines: Connect entities to relationships and attributes to entities.
 Double Ellipse: Represents a multi-valued attribute.

Types of Constraints:

1. Cardinality Constraints:
o Specifies the number of instances of an entity that can or must be associated with
another entity. The cardinality can be one-to-one, one-to-many, or many-to-many.

2. Participation Constraints:
o Specifies whether all or only some instances of an entity participate in a relationship.
This can be total or partial participation.

3. Key Constraints:
o Specifies that each entity in an entity set must have a unique identifier.

3. a) Logical Data Independence Challenges:

Logical Data Independence refers to the ability to change the conceptual schema without
having to change the external schemas or application programs. Achieving logical data
independence is difficult due to several reasons:

1. Complexity of Data Transformation: Changes in the conceptual schema (e.g.,

changing the structure of tables) often require substantial changes in application
programs that access the data. This is because application programs are usually
designed based on the specific data structure, and modifications to the schema may
cause compatibility issues.
2. Dependency on Existing Applications: Many existing applications may be tightly
coupled with the current logical schema. Modifying the schema without affecting
 these applications requires significant effort in modifying or re-writing the
applications.
3. Lack of Flexibility in Schema Changes: The real-world nature of data is often
dynamic, and businesses may need to change data models frequently. Achieving
logical data independence requires a high level of flexibility in the DBMS to allow
changes without breaking dependencies.
4. Performance Issues: Some logical changes (such as adding new entities or
modifying relationships) may require complex data transformations or re-indexing,
which can degrade system performance.

3. b) Weak Entity vs. Strong Entity with ER Diagram Notations:

 Strong Entity: A strong entity can exist independently and has a primary key that
uniquely identifies each record in the entity set. A strong entity is typically
represented as a rectangle with a single primary key.
 Weak Entity: A weak entity cannot exist without a corresponding strong entity. It
relies on a "parent" strong entity for its identification and usually does not have a
complete key of its own. Weak entities are represented by a double rectangle and
require a relationship with a strong entity for identification.

Conversion of Weak Entity to Strong Entity:

A weak entity can be converted into a strong entity by creating a unique identifier (primary
key) for the weak entity and ensuring it can exist independently.

4. a) Three-Tier Client-Server Architecture of DBMS:

The three-tier architecture of a DBMS refers to the separation of the DBMS system into
three distinct layers:

1. Presentation Layer (Client Tier): The front-end interface where users interact with
the database. This layer handles the presentation and user interaction, usually through
an application or web interface.
2. Application Layer (Logic Tier): This layer contains the business logic or processing
logic. It interprets user requests, performs the necessary computations, and passes data
to/from the database.
3. Data Layer (Database Tier): The back-end layer, which is responsible for managing
the database. This layer stores the data and responds to queries and updates from the
application layer.

Advantages:

 Improved scalability and maintenance, as each layer can be independently modified or

replaced.
 Enhanced security, as the database is isolated from direct user interaction
4. b) File System vs. Database System Approach:

Aspect File System Approach Database System Approach

Data is stored in files with no Data is stored in tables, with a DBMS
Data Storage
central management. managing access and updates.
Data High redundancy and Reduced redundancy and more
Redundancy inconsistency. consistency due to normalization.
No centralized security Centralized security features such as
Security
mechanisms. authentication and authorization.
Data retrieval is manual and based Data retrieval is optimized through
Data Retrieval
on the file structure. indexing and query processing.
Provides full support for concurrent
Concurrency Limited concurrency control.
access with ACID properties.

Pros of File System:

 Simple to implement for small-scale applications.

 Lower initial setup costs.

Cons of File System:

 Lack of data integrity and security.

 Difficult to maintain and manage with growing data.

Pros of Database System:

 Centralized management and better data integrity.

 Efficient handling of large volumes of data.

Cons of Database System:

 Higher setup and maintenance costs.

 Complexity in implementation.

5.a) Differences Between Hierarchical, Network, and Relational Models:

Aspect Hierarchical Model Network Model Relational Model

Data Tree-like structure Graph structure with complex Table-based structure (rows
Structure (parent-child). relationships. and columns).

One-to-many More flexible with many-to- Supports one-to-one, one-to-

Relationships
relationships only. many relationships. many, and many-to-many.

Less flexible, rigid More flexible, but still Highly flexible and easy to
Flexibility
structure. complex. modify.

Data Retrieval Simple, but inefficient More complex queries are Efficient data retrieval using
 Aspect Hierarchical Model Network Model Relational Model

for complex queries. possible but hard to manage. SQL.

File systems, early IMS (Information

Example MySQL, PostgreSQL, Oracle.
database models. Management System).

7. Integrity Constraints:

Integrity constraints are rules that ensure data in a database is accurate, consistent, and
reliable. These rules prevent the entry of invalid data and ensure that relationships between
entities are logically sound. Integrity constraints help maintain data quality and consistency
throughout the lifecycle of a database. There are two major types of integrity constraints:
entity integrity and referential integrity.

7.1 Entity Integrity Constraint:

The entity integrity constraint ensures that each row in a table is uniquely identifiable. This
is generally achieved through the use of a primary key, which guarantees that each record in
the table is distinct and can be uniquely referenced.

 Primary Key: A primary key is a column (or combination of columns) in a table that
uniquely identifies each row in the table. The values in the primary key column(s)
must be unique and cannot be null.
 Example: Consider a Students table. Each student is uniquely identified by a
Student_ID which serves as the primary key. No two students can have the same
Student_ID, and the Student_ID cannot be null because it is essential to identify
each student.

Students Table:

Student_ID (PK) Name Age Major

101 John Doe 21 CS

102 Jane Smith 22 Math

In this table, the Student_ID uniquely identifies each student, and no two rows can
have the same Student_ID. The Student_ID column cannot contain null values, as
that would violate the entity integrity.

7.2 Referential Integrity Constraint:

The referential integrity constraint ensures that relationships between tables remain
consistent. Specifically, it ensures that a foreign key in one table must either point to a valid
primary key in another table or be null.
  Foreign Key: A foreign key is a column (or a combination of columns) in one table
that refers to the primary key in another table. The value in the foreign key column
must match an existing value in the referenced table or be null.
 Example: Consider a Courses table that references a Students table. If we have a
Student_ID column in the Courses table as a foreign key that refers to the
Student_ID column in the Students table, the Student_ID in the Courses table
must correspond to an existing Student_ID in the Students table.

Courses Table:

Course_ID Student_ID (FK) Course_Name

501 101 Database

502 102 Algorithms

In this case, Student_ID in the Courses table must be valid and match an existing
Student_ID from the Students table. If Student_ID = 103 appears in the Courses
table but does not exist in the Students table, it would violate referential integrity.

8. a) Responsibilities of a Database Administrator (DBA):

A Database Administrator (DBA) is a specialized professional responsible for overseeing

the design, implementation, management, and maintenance of a database system. The DBA
ensures that the database is functioning efficiently, securely, and reliably. The main
responsibilities of a DBA include:

1. Database Design: A DBA is involved in designing the database structure, including

defining tables, relationships, constraints, and indexes. This design phase ensures that
the database is optimized for both data integrity and performance.
2. Security and Access Control: The DBA is responsible for managing the security of
the database by ensuring that only authorized users have access to the database. This
involves setting up user roles, permissions, and access controls to safeguard sensitive
data.
3. Backup and Recovery: The DBA ensures that regular backups of the database are
taken and that these backups are securely stored. In the event of a system failure or
data corruption, the DBA manages the recovery process to restore the database to its
previous state.
4. Performance Tuning: The DBA monitors the database performance and optimizes
queries, indexes, and other database components to improve performance. This
involves identifying slow-running queries and taking actions to optimize them.
5. Data Integrity: The DBA ensures that the data stored in the database is accurate and
consistent. This includes enforcing integrity constraints (such as primary and foreign
keys) and checking for anomalies that might compromise data integrity.
6. Monitoring and Troubleshooting: The DBA continuously monitors the health of the
database and resolves any issues that arise. This includes detecting bottlenecks,
diagnosing errors, and ensuring the database operates smoothly.
7. Database Maintenance: Routine maintenance tasks like updating database software,
patching security vulnerabilities, and optimizing the database for efficiency are
 carried out by the DBA. These tasks are essential to keeping the database up-to-date
and secure.
8. User Management: The DBA manages database user accounts and assigns
appropriate permissions to users based on their roles within the organization. This
ensures that sensitive data is accessible only to authorized personnel.
9. Upgrades and Migration: The DBA is responsible for upgrading the database
management system (DBMS) and migrating the database to newer versions or
different platforms as needed.

b) Relational Model and Characteristics of RDBMS:

The relational model is a database model that organizes data into tables (also called
relations). The relational model was introduced by E.F. Codd in 1970. In this model, data is
stored in rows and columns, and relationships between different pieces of data are established
through foreign keys.

 Tables: Each table consists of rows and columns. Each row represents a unique
record, and each column represents an attribute of the entity the table describes.
 Primary Key: A primary key is a column (or a set of columns) that uniquely
identifies each row in the table.
 Foreign Key: A foreign key is a column that establishes a link between two tables by
referring to the primary key in another table.
 Normal Forms: The relational model includes normalization rules (such as First
Normal Form, Second Normal Form, etc.) that help eliminate redundancy and ensure
data consistency.

Characteristics of RDBMS:

1. Tabular Structure: Data is stored in tables (relations) with rows and columns.
2. Data Integrity: RDBMS enforces data integrity through primary and foreign keys, ensuring
accurate and consistent data.
3. SQL (Structured Query Language): RDBMS uses SQL for querying and managing the data in
the database.
4. Normalization: RDBMS supports data normalization techniques to eliminate redundancy and
ensure data consistency.
5. ACID Transactions: RDBMS supports ACID (Atomicity, Consistency, Isolation, Durability)
properties for transaction management, ensuring that database transactions are reliable.
6. Data Independence: RDBMS provides logical and physical data independence, allowing
changes to the database schema without affecting the application.
7. Security: RDBMS provides strong security mechanisms to control access to the data and
protect sensitive information.
8. Multi-User Support: RDBMS supports multiple concurrent users, providing mechanisms for
locking and isolation to prevent conflicts.

9. a) E-R Diagram for Hospital Management System:

In a Hospital Management System, there are various entities and relationships that need to
be represented. Common entities include Patient, Doctor, Appointment, Treatment,
Nurse, and Department. The relationships between these entities involve appointments,
treatments, and assignments of nurses to patients.

Entities and Attributes:

1. Patient: Patient_ID (PK), Name, DOB, Gender, Address

2. Doctor: Doctor_ID (PK), Name, Specialization, Phone
3. Appointment: Appointment_ID (PK), Patient_ID (FK), Doctor_ID (FK), Date, Time
4. Treatment: Treatment_ID (PK), Patient_ID (FK), Doctor_ID (FK), Details
5. Nurse: Nurse_ID (PK), Name, Department_ID (FK)
6. Department: Department_ID (PK), Name

Relationships:

 A Doctor works in a Department.

 A Patient has many Appointments.
 An Appointment involves a Doctor and a Patient.
 A Doctor provides many Treatments for Patients.
 A Nurse is assigned to a Department and may assist in treating Patients.

9. b) Insertion, Deletion, and Update Anomalies:

1. Insertion Anomaly:
o An insertion anomaly occurs when new data cannot be added to the database
without including other unnecessary data.
o Example: Suppose a table stores Student_ID, Course_ID, and Instructor_ID
all together. If a new course is being added but there is no instructor assigned yet,
we cannot insert the course without an instructor. This leads to the insertion
anomaly.

2. Deletion Anomaly:
o A deletion anomaly occurs when the deletion of certain data leads to unintended
loss of other related data.
o Example: If a Course table has Course_ID, Instructor_ID, and Student_ID,
deleting a course could inadvertently remove the Instructor_ID from the table,
which is not intended.

3. Update Anomaly:
o An update anomaly occurs when data needs to be updated in multiple places, but it
is not updated everywhere, leading to inconsistencies.
o Example: If a student's address is stored in several places (e.g., in Courses,
Students, etc.), updating the address in one place but not in the other will lead to
inconsistent data.

10. ER Diagram for NHL Database:

The National Hockey League (NHL) database involves several entities such as Team,
Player, Game, and relationships between them.

Entities:

1. Team: Team_ID (PK), Team_Name, City, Coach, Captain

2. Player: Player_ID (PK), Name, Position, Skill_Level, Injury_Records
3. Game: Game_ID (PK), Date, Score

Relationships:

 A Team has many Players.

 A Player belongs to one Team.
 A Team plays many Games (as host_team and guest_team).

11. Applications of Database Systems:

Database systems are essential in a variety of sectors, ranging from business and finance to
healthcare and research. Here are some key applications:

1. Banking Systems: Banks use databases to manage customer accounts, transactions, loans,
and other financial services.
2. Healthcare: Patient records, medical history, appointment scheduling, and treatment
tracking are all managed using databases.
3. E-commerce: Online retail platforms use databases to store customer data, inventory,
orders, and payment information.
4. Education: Schools, universities, and online education platforms store student records,
grades, course materials, and schedules.
5. Telecommunications: Telecommunication companies use databases to track customers,
billing information, call records, and network management.
6. Social Media: Social media platforms manage user profiles, posts, messages, and
relationships using databases.
7. Government: Government departments use databases to manage public records, taxes,
welfare services, and more.

In each of these applications, the database ensures that data is organized, secure, and
accessible when needed, contributing to operational efficiency and informed decision-
making.