1. Define database. Elaborate component modules of DBMS and their interactions.

Ans: A database is a collection of related data. These data are known facts that can be recorded and
that have implicit meaning.
Example: Names, telephone numbers, and addresses of people.
"A DBMS is a software system that stores, manages, and retrieves data efficiently in a structured
manner."
Keywords: Software – It is a program that handles data storage and retrieval.
Store – Saves data in an organized format.
Manage – Ensures data consistency, security, and integrity.
Retrieve – Allows users to access and manipulate data using queries.
Component Modules of DBMS and Their Interactions:
The component modules of a DBMS are categorized into two parts:
1. User-level interfaces
2. Internal DBMS modules

https://www.instagram.com/basavaraj_n__
1. User-Level Interfaces:

• DDL Compiler: Parses and compiles schema definitions into metadata stored in the catalog.
• Interactive Query Interface: Used by casual users to type queries.
• Application Programs: Written in host languages (e.g., Java, C) with embedded DML.
• Parametric Interfaces: For naive users like bank tellers, using predefined canned transactions.
• DBA Interface: Special commands to manage users, storage, authorization, etc.

2. Internal DBMS Modules:

1. Query Compiler:
o Parses and validates queries.
o Uses query optimizer to choose an efficient execution plan.
2. Query Optimizer:
o Consults catalog for statistics.
o Determines best execution strategy.
3. Runtime Database Processor:
o Executes queries, privileged commands, and canned transactions.
o Updates catalog with execution statistics.
4. Stored Data Manager:
o Handles low-level disk I/O.
o Works closely with the OS and hardware.
5. Concurrency Control Manager:
o Ensures correct concurrent transaction execution.
6. Backup and Recovery System:
o Restores DB after failures or crashes.

➢ Interaction Summary:
• A user sends a query via GUI or code.
• The query is compiled → optimized → executed.
• The processor interacts with the stored data manager for disk operations.
• All updates and reads are done under transaction control.
• If a crash happens, recovery system restores the database.

https://www.instagram.com/basavaraj_n__
2. Explain the three-schema architecture with neat diagram.Why do we need mappings
among schema levels?
Ans : The three-schema architecture is a framework that separates the database system into three
different levels:

1. Internal Level (Physical Schema):

• Describes how data is physically stored on storage devices.
• Uses physical data models to define storage structures and access paths.

2. Conceptual Level (Logical Schema):

• Describes the logical structure of the whole database for a community of users.
• Hides storage details and focuses on entities, data types, relationships, constraints.

3. External Level (User Views):

• Describes individual user views of the database.
• Hides the rest of the database from the user.
• Each view is defined using an external schema.

Goal of This Architecture:

• Data Independence: Changes at one level do not affect the other levels.
• Logical and physical data independence are key benefits.

https://www.instagram.com/basavaraj_n__
 Need for Mappings Among Schema Levels:

Mappings are essential in the three-schema architecture to ensure data independence and
synchronization across different levels of abstraction (external, conceptual, and internal).

1. To Achieve Logical Data Independence

• Allows changes in the conceptual schema (like adding/removing attributes) without affecting
the external schemas or application programs.
• Mapping ensures that user views remain consistent even if the underlying structure changes.

2. To Achieve Physical Data Independence

• Allows changes in the internal schema (e.g., storage structures or indexing) without altering
the conceptual schema.
• This means performance optimizations can be made without disrupting the application logic.

3. To Translate User Requests

• External level → Conceptual level → Internal level.

• Mappings enable query translation and processing across different levels so the user doesn’t
need to know how the data is stored physically.

4. To Synchronize Data Representation

• Mappings maintain consistency across levels.

• If data is updated at one level, mappings help ensure that all other levels reflect the correct
structure and constraints.

3. What are the advantages of using DBMS Explain.

Ans : Database Management System (DBMS) offers several advantages over traditional file-
processing systems. These benefits make DBMS a robust and efficient way to manage large volumes
of data securely and accurately.
1. Controlling Redundancy
• Traditional file systems store duplicate copies of the same data.
• DBMS minimizes data duplication by storing each logical data item only once.
• This leads to better storage utilization and data consistency.
Example: A student's birthdate is stored only once, not in every department’s file.
2. Restricting Unauthorized Access
• DBMS provides authorization mechanisms to control who can access or modify which data.
• The DBA (Database Administrator) can assign roles and privileges.
Example: Financial records are only accessible to authorized personnel.
3. Persistent Storage for Program Objects

https://www.instagram.com/basavaraj_n__
 • Object-oriented DBMSs provide persistent storage for complex objects used in programming.
• Solves the impedance mismatch problem between programming languages and DBMS.
4. Efficient Query Processing
• DBMS uses specialized data structures and indexing techniques to speed up data retrieval.
• Includes a query optimization module that selects the best execution plan.
5. Backup and Recovery
• DBMS provides automated backup and recovery systems to restore the database after
hardware/software failures.
• Ensures data durability even after system crashes.
6. Multiple User Interfaces
• Supports various interfaces like:
o Query languages (SQL)
o Forms
o Menu-driven GUIs
o Natural language and speech
Enables both technical and non-technical users to interact with the DBMS.
7. Representing Complex Relationships
• DBMS can handle complex interrelated data and define new relationships as needed.
Example: One-to-many or many-to-many relationships between entities.
8. Enforcing Integrity Constraints
• Ensures data accuracy and validity using constraints like:
o Data type checks
o Key constraints (uniqueness)
o Referential integrity
9. Inferencing and Triggers
• Allows rules and triggers to automatically perform actions or infer new data when certain
events occur.
10. Other Benefits
• Flexibility: Can adapt as user or business requirements evolve.
• Real-time updates: All users see the latest data instantly.
• Enforcement of standards: Naming conventions, formats, and rules can be standardized across
the organization.

https://www.instagram.com/basavaraj_n__
4. Explain the types of end users with examples.
Ans : End users are the people who interact with the database to perform operations such as querying,
updating, and generating reports. The database primarily exists for their use.

Types of End Users:

1. Casual End Users

• Access the database occasionally.

• Use complex query languages to retrieve different types of data as needed.
• Typically are middle or high-level managers, analysts, or auditors.
Example: A department head checking staff performance metrics using ad hoc queries.

2. Naive or Parametric End Users

• Frequently interact with the database.

• Use predefined and canned transactions with minimal inputs (usually via forms).
• Don’t require knowledge of the query language or schema.
Examples:
• Bank teller: Checks account balances and performs deposits/withdrawals.
• Reservation agent: Books airline, hotel, or car reservations.

3. Sophisticated End Users

• Have deep knowledge of DBMS features.

• Develop complex applications and use advanced tools for their tasks.
• Often include engineers, scientists, researchers, and business analysts.
Example: A data scientist writing SQL and Python scripts to perform advanced data analytics.

4. Standalone Users

• Use off-the-shelf DBMS applications for personal use.

• Do not require interaction with DBAs or developers.
• Typically interact via menu-driven or graphical interfaces.
Example: A person using a tax preparation software that stores financial data in a database.

5. What are the responsibilities of DBA and Database Designers?

Ans : Database Administrator (DBA):
A Database Administrator (DBA) is responsible for the overall control and management of the
database system. The DBA ensures the security, performance, availability, and integrity of the
database.
Key Responsibilities of DBA:
1. Authorization Control

https://www.instagram.com/basavaraj_n__
 o Granting/revoking user access privileges.
o Ensuring data security and protection from unauthorized access.
2. Monitoring Usage and Performance
o Identifying and resolving performance issues (slow queries, resource bottlenecks).
3. System Configuration and Maintenance
o Installing and upgrading DBMS software.
o Managing storage and hardware resources.
4. Backup and Recovery
o Ensuring regular backups are taken.
o Recovering data in case of system failure.
5. Data Integrity and Consistency
o Enforcing constraints to ensure data accuracy.
6. Troubleshooting
o Resolving database-related problems, including crashes and errors.

2. Database Designers:

Database Designers are responsible for designing the structure and schema of the database to meet
user requirements.
Key Responsibilities of Database Designers:
1. Identify Data Requirements
o Determine what data needs to be stored in the database.
2. Schema Design
o Create the conceptual, logical, and physical schemas.
o Design entities, attributes, and relationships (ER Model).
3. Ensure Efficient Storage
o Choose appropriate data structures and indexes.
4. User Interaction
o Communicate with various user groups to understand their needs.
5. Design Views
o Create user-specific views for different roles and access levels.
6. Ensure Scalability and Flexibility
o Design for future enhancements and modifications.

https://www.instagram.com/basavaraj_n__
6. Describe the characteristics of database.
Ans : A database is not just a collection of data, but a well-organized and meaningful structure. The
database approach provides several characteristics that make it superior to traditional file processing
systems.

Characteristics of the Database Approach:

1. Self-Describing Nature of a Database System

• A database includes metadata (data about data), stored in a catalog.

• This metadata describes the structure of the database (e.g., tables, fields, data types,
constraints).
This allows programs to access the structure without hardcoding it.

2. Insulation Between Programs and Data (Program-Data Independence)

• In DBMS, data definitions are separated from application programs.

• Programs do not need to be modified when the database structure changes.
Known as data abstraction—simplifies database access and maintenance.

3. Support for Multiple Views of the Data

• Different users can have customized views of the same database.

• A view may show only a subset of the database, or present the data in a specific format.
Example: A student’s transcript view vs. a course prerequisite view.

4. Sharing of Data and Multiuser Transaction Processing

• Multiple users can access and update the database simultaneously.

• The DBMS uses concurrency control to prevent conflicts and ensure correctness.
Example: In a banking system, two tellers can safely update different accounts at the same time.

7. Explain the difference between logical and physical data independence.

Ans : In the three-schema architecture of DBMS, data independence refers to the capacity to change
the schema at one level without having to change the schema at the next higher level.

1. Logical Data Independence:

• It is the ability to change the conceptual schema without having to change the external
schemas or application programs.

Changes allowed:

• Adding/removing attributes or entities

• Modifying constraints or relationships

Example:

https://www.instagram.com/basavaraj_n__
 • Adding a new attribute BirthDate to a STUDENT entity should not require changes in all the
user views or application programs using the STUDENT data.

2. Physical Data Independence:

• It is the ability to change the internal schema (physical storage details) without affecting the
conceptual schema.

Changes allowed:

• Reorganizing files
• Changing indexing or access paths
• Moving data to different storage media

Example:

• Adding an index to speed up query performance does not require changes to the logical
(conceptual) database design.

Feature Logical Data Independence Physical Data Independence

Affects Conceptual ↔ External Levels Internal ↔ Conceptual Levels

Schema change
Conceptual level Internal level
at

No change needed in user views or

Impact No change needed in conceptual schema
applications

Adding indexes or reorganizing physical

Example Adding a new attribute to a table
storage

8. Draw an ER diagram for an COMPANY database with employee, department,

project as strong entities and dependent as weak entity. Specify the constraints,
relationships and ratios in the ER diagram..
Ans : Strong Entity Sets:

1. EMPLOYEE
o Attributes: Emp_ID (PK), Name, Address, Salary
2. DEPARTMENT
o Attributes: Dept_ID (PK), Dept_Name, Location
3. PROJECT
o Attributes: Proj_ID (PK), Proj_Name, Budget

Weak Entity Set:

https://www.instagram.com/basavaraj_n__
 4. DEPENDENT
o Attributes: Name (Partial Key), Birth_Date, Relationship
o Dependent is weak because it depends on EMPLOYEE.

9. Explain the different types of attributes in ER model.

Ans : In the ER (Entity-Relationship) model, attributes represent the properties or characteristics of an
entity or a relationship. Attributes help describe the data stored in the database.

https://www.instagram.com/basavaraj_n__
 Types of Attributes in ER Model:

1. Simple (Atomic) vs. Composite Attributes

• Simple (Atomic):
Cannot be divided further.
• ER symbol :oval
Example: Age, Salary, Gender
• Composite:
Can be divided into subparts.

Er symbol :Oval connected to sub-ovals

Example: Address → (Street, City, Zip)

2. Single-Valued vs. Multi-Valued Attributes

• Single-Valued:
Each entity has only one value for the attribute.
Example: A Student has one RollNumber
• Multi-Valued:
Each entity can have multiple values for the attribute.

• Symbol: Double oval

Example: A Person can have multiple phone numbers

3. Stored vs. Derived Attributes

• Stored:
Values are physically stored in the database.
Example: BirthDate
• Derived:
Values are calculated from other attributes.
• Symbol : dashed oval
Example: Age is derived from BirthDate

4. Null-Valued Attributes

• These are attributes where no value is applicable or value is unknown.

Example: SpouseName for an unmarried person = NULL

5. Complex Attributes

• Attributes that are nested using a combination of composite and multi-valued attributes.
Example: Residence → { (Street, City, Phones[]) }

These are represented using parentheses () for composite and braces {} for multivalued parts.

https://www.instagram.com/basavaraj_n__
10. Explain the following:
1. Cardinality Ratio
2. Weak Entity
3. Participation Role / Entity Participation
4. Data Dictionary
Ans :

1. Cardinality Ratio

Definition:
Cardinality ratio specifies the maximum number of relationship instances in which an entity can
participate.

2. Weak Entity

Definition:
A Weak Entity is an entity that does not have a primary key of its own and depends on a strong entity
for identification.

Characteristics:

• Has a partial key (like DependentName)

• Must have total participation in an identifying relationship
• Represented by double rectangles in ER diagrams
Example: DEPENDENT depends on EMPLOYEE to be uniquely identified.

3. Participation Role / Entity Participation

Definition:
It indicates whether the existence of an entity depends on being related to another entity through a
relationship.

Types of Participation:

https://www.instagram.com/basavaraj_n__
Type Meaning Notation

Total Participation Every entity must participate in the relationship Double line

Partial Participation Some entities may not participate Single line

Example: Every DEPENDENT must belong to an EMPLOYEE (Total); not every EMPLOYEE
manages a department (Partial)

4. Data Dictionary

Definition:
A Data Dictionary (also called a catalog) is a repository that stores metadata, i.e., data about the data
stored in the database.

Contains:

• Names of tables, columns, data types

• Constraints like keys, nullability
• Indexes, views, access privileges
• Relationships between entities

Purpose:

• Helps query compilers, optimizers, and authorization modules

Analogy: Like an index or glossary in a book that describes what’s inside and how it’s structured.

11. Explain the categories of Data Models.

Ans : 1. High-Level (Conceptual) Data Models
• Provide concepts that are close to how users perceive data.
• Describe data using entities, attributes, and relationships.
• Do not include implementation details.
• Used in initial database design (ER diagrams).
Example: Entity-Relationship (ER) Model, Enhanced ER (EER) Model

2. Representational (Implementation) Data Models

• Bridge the gap between high-level conceptual models and low-level physical models.
• Focus on how data is logically represented using record structures.
Example:
• Relational model (most widely used)
• Network model
• Hierarchical model
These are sometimes called record-based models.

https://www.instagram.com/basavaraj_n__
 3. Low-Level (Physical) Data Models

• Describe how data is actually stored on storage media.

• Includes file structures, record formats, access paths like indexes.
• Meant for system designers and database administrators.
Example: Physical storage model showing blocks, pointers, indexing strategies.

12. Explain three tire acritecture with diagram.

Ans :A three-tier architecture separates a web application into three distinct layers:

1. Presentation Tier (Client Tier):

• The user interface layer.

• Executed in a web browser or client app.
• Handles user inputs and displays outputs.
Example: HTML/CSS pages, JavaScript, mobile apps

2. Application Tier (Middle Tier or Business Logic Layer):

• Contains application logic, processing rules, and decisions.

• Runs on a web or application server.
• Acts as a bridge between the user interface and the database.
Example: Servlet, PHP, Java, Python, or Node.js logic

3. Data Tier (Database Tier):

• Stores and manages persistent data.

• Typically a relational database (e.g., MySQL, Oracle, PostgreSQL).
• The application tier sends queries and updates to this layer.

Advantages of Three-Tier Architecture:

• Separation of concerns: Each layer can be developed independently.

• Scalability: Application logic can be replicated to handle load.
• Security: Database is not directly exposed to the client.
• Maintainability: Easier to update or debug one layer without affecting others.

https://www.instagram.com/basavaraj_n__
13. Demonstration of notations used in E-R diagrams and the design of an ER diagram
for a BANK database.
Ans : Notations in E-R Diagrams:

https://www.instagram.com/basavaraj_n__
14. Explain DBMS Languages. What are DDL and DML? Mention their functions with
examples.
Ans: A DBMS (Database Management System) supports several specialized languages to define,
manipulate, and view data stored in the database. The primary ones are:

1. Data Definition Language (DDL):

• Used by DBA and database designers to define:

o Conceptual schema
o Internal schema
o External views (in some DBMSs)
• DDL statements are processed by the DDL compiler which stores metadata in the catalog
(data dictionary).

2. Data Manipulation Language (DML):

• Used by end users and application programs to manipulate the data in the database.
• Supports operations like retrieval, insertion, deletion, and updates.

https://www.instagram.com/basavaraj_n__