DMS QUESTION BANK SOLUTION (SYAN)
2 MARKS
1. State any two advantages of DBMS over file processing system. (Unit-1)
Answer:
1) Reduce Data redundancy
2) Avoid Data Inconsistency
3) Data Sharing
4) Centralized Control
2. Define i) Database ii) DBMS
Answer:
1) Database : A database is an organized collection of related data items store and manage in
structure manner within a computer system
2) A dbms is a software used to create , store, organize and manage databases .It allows
users to add ,update,delete and retrieve data efficiently .
[Link] between Hierarchical and Network data model any two points . (Unit-1)
Answer:
4. Define
i) Data Abstraction:
Answer:
Data abstraction is the process of hiding Unwanted & irrelevant details from the end User.
It help to store information in such way that the end user can access a data Which is necessary,
the user will not be able to see What data is stored in a database
ii)Schema (Unit-1)
Answer:
1. Schema :-
A description of data in terms of data model is & called a database schema, which s Specified
during database design & it does. not expect to change frequently.
A database Schema is structure of database -represent the Logical view of the entire database.
5. Define Primary key and Foreign key. (Unit-2)
Answer:
Primary key :-
A primary key is in a table that uniquely identifies a tuple or record (row) in a relational table.
column or group of Column's
Primary key Cannot be null Value & it must be unique for each tuple in relational table.
Foreign Key:-
A foreign key is an attribute in one table. that reference to the primary key in another table.
The table that contains the foreign key is called the referencing table & the table that is called the
reference table.
6. List DML commands. (Unit-3)
Answer:
A)Insert
B)Update
C)Delete
D)select
7. Define Normalization. Enlist its types. (Unit-2)
Answer:
Normalization in DBMS is a systematic process of arranging data in a relational database to
minimize redundancy & improve data integrity.
It involves decomposing large tables into Smaller, related tables. Defining relationships among
them using keys.
Types:
a) First Normal Form(1NF)
b) Second Normal Form(2NF)
c) Third Normal Form(3NF)
8. State any four symbol use in ER diagram.(Unit-2)
Answer:
9. Enlist components of relational database systems . (Unit-1)
Answer:
1) relation(tables)
2) attribute(column)
3) entity
4) tuple(rows)
5) Domain
10. Enlist any four applications of DBMS. (Unit-3)
Answer:
[Link] management system
[Link] management system
[Link] system
[Link] sector
[Link] Media Platform
[Link] Shopping
11. Enlist any four data types in SQL. (Unit-1)
Answer:
1) integer(int)
2) char
3)varchar
4)float
5)double
6)datetime
7)boolean
8)timestamp
12. Define attribute and Enlist its type (Unit-1)
Answer:
Attributes are the properties that define an entity.
Various properties that describe an entity are known as attributes.
The attributes value that describes each entity becomes a major part of the data Store in the
database.
For example, Roll. No, Name, address
Types:
Simple Attributes
Single Value Attributes
Multivalue Attributes
Composite Attributes
Derived Attributes
Key Attributes
Stored Attributes
Complex Attributes
4 Marks questions:
1. Draw the overall structure of [Link] Explain the components of storage manager.
(Unit-1)
Answer:
The storage manager components include:
1. Authorisation and integrity manager: Checks for integrity constraints and authority of users to
access data.
2. Transaction manager: This ensures that the database remains in a consistent (correct) state
although there are system failures.
3. File manager: This manages the allocation of space on disk storage and the data structures
used to represent information stored on disk.
4. Buffer manager: This is responsible for retrieving data from disk storage into main memory.
The buffer manager is an important part of the database system, as it enables the database to
handle data sizes that are much larger than the size of the main memory.
Data structures implemented by the storage manager,
1. Data files: Stored in the database itself.
2 Data dictionary: Stores metadata about the structure of the database.
3. Indices: Provide fast access to data items.
2. List and explain any five rules of EF Codds. (Unit-1)
Answer:
Codd's Rules in DBMS
Rule 1: The Information Rule
All information, whether it is user information or metadata, that is stored in a database must be
entered as a value in a cell of a table. It is said that everything within the database is organized in
a table layout.
Rule 2: The Guaranteed Access Rule
Each data element is guaranteed to be accessible logically with a combination of the table name,
primary key (row value), and attribute name (column value).
Rule 3: Systematic Treatment of NULL Values
Every Null value in a database must be given a systematic and uniform treatment.
Rule 4: Active Online Catalog Rule
The database catalog, which contains metadata about the database, must be stored and accessed
using the same relational database management system.
Rule 5: The Comprehensive Data Sublanguage Rule
A crucial component of any efficient database system is its ability to offer an easily
understandable data manipulation language (DML) that facilitates defining, querying, and
modifying information within the database.
Rule 6: The View Updating Rule
All views that are theoretically updatable must also be updatable by the system.
Rule 7: High-level Insert, Update, and Delete
A successful database system must possess the feature of facilitating high-level insertions,
updates, and deletions that can grant users the ability to conduct these operations with ease
through a single query.
Rule 8: Physical Data Independence
Application programs and activities should remain unaffected when changes are made to the
physical storage structures or methods.
Rule 9: Logical Data Independence
Application programs and activities should remain unaffected when changes are made to the
logical structure of the data, such as adding or modifying tables.
Rule 10: Integrity Independence
Integrity constraints should be specified separately from application programs and stored in the
catalog. They should be automatically enforced by the database system.
Rule 11: Distribution Independence
The distribution of data across multiple locations should be invisible to users, and the database
system should handle the distribution transparently.
Rule 12: Non-Subversion Rule
If the interface of the system is providing access to low-level records, then the interface must not
be able to damage the system and bypass security and integrity constraints.
3. Draw a three-tier architecture and explain their functions. (Unit-1)
Answer:
The 3-tier architecture is a client-server architecture that separates the user interface, application
processing, and data management into three distinct tiers or layers. The 3-tier architecture is
widely used in modern web applications and enterprise systems because it offers scalability,
flexibility, and security. Here is a brief description of each tier in the 3-tier architecture:
Presentation Tier (User Interface Layer)
Application Tier (Business Logic Layer)
Data Management Tier (Database Layer)
Functions of the 3-Tier Architecture
1. Presentation Tier (User Interface Layer)
The presentation tier is the user interface or client layer of the application. It is responsible for
presenting data to the user and receiving input from the user. This tier communicates with the
Application Tier to process user requests and display relevant information. This tier can be a web
browser, mobile app, or desktop application.
Example: If you’re using a banking app, the presentation tier would display your account
balance, allow you to make transfers, and display results based on your actions.
2. Application Tier ( Business Logic Layer)
The application tier is the middle layer of the 3-tier architecture. It acts as the intermediary
between the Presentation Tier and the Data Management Tier. It is responsible for processing
and managing the business logic of the application. This tier communicates with the
presentation tier to receive user input and communicates with the data management tier to
retrieve or store data. This tier may include application servers, web servers, or APIs.
Example: If you're purchasing an item through an e-commerce platform, the Application Layer
handles all the logic like verifying available stock, applying discounts, calculating taxes, and
confirming your payment method.
3. Data Management Tier ( Database Layer)
The Data Management tier is the bottom layer of the 3-tier architecture. It is responsible for
managing and storing data. This tier can include databases, data warehouses, or any other
persistent data storage solution. The data management tier communicates with the application
tier save, retrieve, or manipulate data according to the business logic.
Example: In a customer relationship management (CRM) system, this tier would store customer
details, transaction history, and other data relevant to the business.
4. List the significant difference between file processing system and database
management system (any 4 point) (Unit -1)
Answer:
5. State and explain 1NF and 2NF with example. (Unit-2)
Answer:
6. Draw an E-R diagram of the Hospital Management system. (Unit-2)
Answer:
7. Describe Data Abstraction and its level with a neat diagram. (Unit2)
Answer:
Data Abstraction is one of the most important concepts in DBMS. Data abstraction is the process
of hiding unwanted and irrelevant details from the end user. It helps to store information in such
a way that the end user can access data which is necessary, the user will not be able to see what
data is stored or how it is stored in a database.
Data abstraction hides unwanted details, showing users only relevant data.
It keeps data safe by hiding how and where data is stored.
Abstraction simplifies access while maintaining data security and efficiency.
For example, while buying clothes, users see only color, size, and brand, not production details.
Levels of Abstraction in DBMS
There are three levels of data abstraction in DBMS that are mentioned below:
Physical or Internal level
Logical or Conceptual level
View or External level
Physical or Internal Level
It is the lowest level of data abstraction which defines how data is stored in a database . It defines
data structures used to store data and methods to access data in the database. It is very complex
to understand and hence kept hidden from users. The database administrator decides how and
where to store the data in the database.
Physical level deals with actual storage details like data organization, disk space allocation and
data access methods.
Logical or Conceptual Level
It is an intermediate level present next to the physical level. It defines what data is present in the
database and their relationships between them . It is less complex as compared to physical level.
Programmers generally work at this level and depending on data, structure of tables,
relationships and their constraints is decided at this level.
View or External Level
It is the highest level in abstraction. There are different levels of views and each view defines
only a part of the whole data required to be used. This level defines many views of the same
database for simplification of view to user. This is the highest level and easiest to understand for
user
8. List and Explain any two DDL commands along with examples. (Unit-3)
Answer:
DDL (Data Definition Language) commands are used to create, modify, and delete the structure
of database objects such as tables, databases, indexes, and views.
1. CREATE Command
Use: The CREATE command is used to create a new database object such as a table or database.
Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
Example:
CREATE TABLE Student (
RollNo INT,
Name VARCHAR(50),
City VARCHAR(30),
Marks DECIMAL(5,2)
);
Explanation:
The above query creates a table named Student with four columns: RollNo, Name, City, and
Marks.
2. ALTER Command
Use: The ALTER command is used to modify the structure of an existing table, such as adding,
modifying, renaming, or dropping a column.
Syntax (Add Column):
ALTER TABLE table_name
ADD column_name datatype;
Example:
ALTER TABLE Student
ADD Email VARCHAR(100);
Explanation:
The above query adds a new column named Email to the Student table.
9. Define Entity and explain strong and weak entity set (Unit-2)
Answer:
10. Explain 3NF in detail (Unit-2)
Answer:
Third Normal Form (3NF)
Definition:
A relation is said to be in Third Normal Form (3NF) if:
1. It is already in Second Normal Form (2NF).
2. It has no transitive dependency, i.e., no non-key attribute depends on another non-key
attribute.
In simple words, every non-key attribute should depend only on the primary key and not on
any other non-key attribute.
Why is 3NF Required?
● Eliminates transitive dependency.
● Reduces data redundancy.
● Prevents update, insertion, and deletion anomalies.
● Improves data consistency and integrity.
Example
Student Table (Before 3NF)
RollNo (PK) Name DeptID DeptName
101 Rahul D01 Computer
102 Priya D02 Mechanical
103 Amit D01 Computer
Here:
● RollNo → DeptID
● DeptID → DeptName
Therefore,
● RollNo → DeptName (through DeptID)
This is called transitive dependency, because DeptName depends on DeptID, not directly on
the primary key (RollNo).
Converting to 3NF
Student Table
RollNo (PK) Name DeptID
101 Rahul D01
102 Priya D02
103 Amit D01
Department Table
DeptID (PK) DeptName
D01 Computer
D02 Mechanical
Now:
● In the Student table, all non-key attributes depend only on RollNo.
● In the Department table, DeptName depends only on DeptID.
● The transitive dependency is removed, so the tables are in Third Normal Form (3NF).
Advantages of 3NF
● Removes transitive dependency.
● Reduces duplicate data.
● Improves data integrity.
● Makes database maintenance easier.
● Prevents update, insertion, and deletion anomalies.
11. Write the SQL queries for the following EMP table. Emp (empno, deptno, ename,
salary, designation, city.) (Unit-3)
Answer:
i) Set the salary of employee ‘Ramesh’ to 50000.
Query:
UPDATE EMP
SET salary = 50000
WHERE ename = 'Ramesh';
ii) change the employee name ‘Rahul’ to ‘Ramesh’.
Query:
UPDATE EMP
SET ename = 'Ramesh'
WHERE ename = 'Rahul';
iii) Remove the Record of employees whose empno is 10.
Query:
DELETE FROM EMP
WHERE empno = 10;
iv) Remove the column deptno from EMP table. (Unit-3)
Query:
ALTER TABLE EMP
DROP COLUMN deptno;
12. Explain in detail domain constraints and Integrity constraints. (Unit-2)
Answer:
Domain Constraints
Domain constraints are a type of integrity constraint that ensure the values stored in a column (or
attribute) of a database are valid and within a specific range or domain. In simple terms, they
define what type of data is allowed in a column and restrict invalid data entry. The data type of
domain includes string, char, time, integer, date, currency etc. The value of the attribute must be
available in comparable domains.
Example: Below table demonstrates domain constraints in action by enforcing rules for each
column
1. Student_Id: Must be unique and follow a specific format like
21CSE###. No duplicates or invalid formats allowed.
2. Name: Accepts only valid text (no numbers) and cannot be left
empty (NOT NULL constraint).
3. Semester: Allows specific values like 5th, 6th, etc., and ensures
valid input (e.g., no 10th if not permitted).
4. Age: Must be an integer within a reasonable range (e.g., 18-30) and
cannot contain invalid data like negative numbers or text.
Types of Domain Constraints:
● NOT NULL Constraint: Ensures No records can have NULL value.
● CHECK Constraint: This Constraint Checks for any specified
condition over any attribute.
Integrity constraints in a Database Management System are rules that help
keep the data in a database accurate, consistent and reliable. They act like a
set of guidelines that ensure all the information stored in the database
follows specific standards.
Example: Making sure every customer has a valid email address & ensuring
that an order in the database is always linked to an existing customer.
Types of Integrity Constraints
There are Different types of Integrity Constraints used in DBMS, these are:
1. Domain Constraints
2. Entity Integrity Constraints
3. Key Constraints
4. Referential integrity constraints
5. Assertion
6. Triggers