WINTER – 2023 EXAMINATION

Model Answer
Subject Name: Database Management System Subject Code:313302

Que Sub Max Marks

No. Que Marks Distribution
No. for Question & Answer
Sub
Que.

Q1 10M Attempt any FIVE of the following

a 2M Differentiate between network model and hierarchical model. Any 2 points

Ans (Any two points) 1 Mark each

b 2M State any four symbols of the ER diagram. ½ marks

Ans Each
 c 2M Define the term i)View ii) Sequence 1 Mark
Ans View: A view is a logical table based on another table.View logically 1 Mark
represents subsets of data from one or more tables .A view contains
no data of it‟s own but is like a window through which data from
tables can be viewed or changed .The tables on which the view is
based are called base tables.

Sequence:A sequence is used to generate numbers in sequence. You

can use sequences to insert unique values in Primary Key and
Unique Key columns of tables. To create a sequence, give the
CREATE SEQUENCE statement.

d 2M List any two characteristic of Big Data Any 2

Ans Volume 1 Mark each
The name Big Data itself is related to an enormous size. Big Data is
a vast 'volumes' of data generated from many sources daily, such as
business processes, machines, social media platforms, networks,
human interactions, and many more.
Example: Facebook can generate approximately a billion messages,
4.5 billion times that the "Like" button is recorded

Variety
Big Data can be structured, unstructured, and semi-structured that
are being collected from different sources. Data will only be
collected from databases and sheets in the past, But these days the
data will come in array forms, that are PDFs, Emails, audios, SM
posts, photos, videos, etc.
Example: Web server logs, i.e., the log file is created and
maintained by some server that contains a list of activities.

Veracity
Veracity means how reliable the data is. It has many ways to filter or
translate the data. Veracity is the process of being able to handle and
manage data efficiently.
Example: Facebook posts with hashtags.

Value
Value is an essential characteristic of big data. It is not the data that
we process or store. It is valuable and reliable data that we store,
process, and also analyze.

Velocity
Velocity plays an important role compared to others. Velocity creates
the speed by which the data is created in real-time. It contains the
linking of incoming data sets speeds, rate of change, and activity
bursts. The primary aspect of Big Data is to provide demanding data
rapidly.
 e 2M Draw PL/SQL Block Structure 2 Mark
Ans SET SERVEROUTPUT ON;
DECLARE
-- Variable and cursor declarations
BEGIN
-- PL/SQL executable statements
EXCEPTION
-- Exception handling code
END;
/

f 2M State any two application of trigger Any 2 points

Ans • Triggers set database object rules and roll back if any change does 1 Mark each
not satisfy those rules. The trigger will inspect the data and make
changes if necessary.

• Triggers help us to enforce data integrity.

• Triggers help us to validate data before inserted or updated.

• Triggers help us to keep a log of records.

• Triggers increase SQL queries' performance because they do not

need to compile each time they are executed.

• Triggers reduce the client-side code that saves time and effort.

• Triggers are easy to maintain.

g 2M Write a syntax of create table command for creating table with 2 Mark
Ans primary key constraint
Syntax:
CREATE TABLE table_name (
column1 data_type, …....,
[CONSTRAINT constraint_name] PRIMARY KEY (column1));
OR
CREATE TABLE table_name ( column_1 datatype Primary Key,
column_2 datatype, …………, column_n datatype);

Que Sub Max Marks

No. Que Marks Distribution
No. for Question & Answer
Sub
Que.

Q2 12M Attempt any THREE of the following

a 4M List and explain any four advantages of DBMS over traditional Any 4 Points
Ans file processing systems. 1 Mark Each
 1. Data redundancy and inconsistency: Redundancy is the
concept of repetition of data i.e. each data may have more than a
single copy. The file system cannot control the redundancy of data
as each user defines and maintains the needed files for a specific
application to run. There may be a possibility that two users are
maintaining the data of the same file for different applications.
Hence changes made by one user do not reflect in files used by
second users, which leads to inconsistency of data. Whereas
DBMS controls redundancy by maintaining a single repository of
data that is defined once and is accessed by many users. As there is
no or less redundancy, data remains consistent.
2. Data sharing: The file system does not allow sharing of data or
sharing is too complex. Whereas in DBMS, data can be shared
easily due to a centralized system.
3. Data concurrency: Concurrent access to data means more than
one user is accessing the same data at the same time. Anomalies
occur when changes made by one user get lost because of changes
made by another user. The file system does not provide any
procedure to stop anomalies. Whereas DBMS provides a locking
system to stop anomalies to occur.
4. Data searching: For every search operation performed on the
file system, a different application program has to be written.
While DBMS provides inbuilt searching operations. The user only
has to write a small query to retrieve data from the database.
5. Data integrity: There may be cases when some constraints need
to be applied to the data before inserting it into the database. The
file system does not provide any procedure to check these
constraints automatically. Whereas DBMS maintains data integrity
by enforcing user-defined constraints on data by itself.
6. System crashing: In some cases, systems might have crashed
due to various reasons. It is a bane in the case of file systems
because once the system crashes, there will be no recovery of the
data that’s been lost. A DBMS will have the recovery manager
which retrieves the data making it another advantage over file
systems.
7. Data security: A file system provides a password mechanism to
protect the database
but how long can the password be protected? No one can guarantee
that. This doesn’t happens in the case of DBMS. DBMS has
specialized features that help provide shielding to its data.
8. Backup: It creates a backup subsystem to restore the data if
required.
9. Interfaces: It provides different multiple user interfaces like
graphical user interface and application program interface.
10. Easy Maintenance: It is easily maintainable due to its
centralized nature.

b 4M Draw ER diagram of Library Management System 1 Mark Each

Ans
 c 4M List and explain the types of database users ½ Marks for
Ans Database Users and User Interfaces Listing each
There are four different types of database-system users, user ½ for
differentiated by the way they expect to interact with the system. each
explanation
i) Database Administrator
ii)Naive and Parametric End Users
iii)Sophisticated End Users
iv) Application Programmers

i) Database Administrator :
Database administrator is responsible for managing the whole
database system. He/she designs, creates and maintains the
databases. He/she also manages the users who can access this
database and controls the integrity issues. He/she also monitors the
performance of the system and makes changes in the system as and
when required.
ii) Naive and Parametric End Users :
These are the people who use permanent application programs.
Examples of such users are airline reservation clerk, bank teller,
data entry clerks, etc.
iii) Sophisticated End Users :
These are the users who are familiar with the DBMS facilities like
engineers and business analysts. They interact with the system
through their requests written using a database query language.
Sophisticated users are the users who are familiar with the
structures of databases and facilities of DBMS. They use queries
 such as SQL to perform the operation on the database.
iv) Application Programmers :
Application programmer is the person who is responsible for
implementing the required functionality of the database for the end
users. They work according to the specification provided by the
system analyst.

d 4M Differentiate between function and procedure. (any four point) 1 Mark each
Ans

Que Sub Max Marks

No. Que Marks Distribution
No. for Sub Question & Answer
Que.

Q3 12M Attempt any THREE of the following

a 4M List and explain any four rules of EF codd. ½ Marks for

Ans 1. The Information rule: All information in an RDBMS is listing each
represented logically in just one way - by values in tables. rule
2. The Guaranteed Access rule: Each item of data in an RDBMS ½ mark for
is guaranteed to be logically accessible by resorting to a explanation
combination of table name, primary key value, and column name.
3. The Systematic Treatment of Null Values rule: Null values
(distinct from an empty character string or a string of blank
characters and distinct from zero or any other number) are
supported in a fully relational DBMS for representing missing
4. The Dynamic Online Catalog Based on the Relational Model
rule:
The database description is represented at the logical level in the
same way as ordinary data, so that authorized users can apply the
same relational database.
5. The Comprehensive Data Sublanguage rule: A relational
system may support several languages and various modes of
terminal for data definition, view definition, data manipulation etc.
6. The View Updating rule: All views of the data which are
theoretically updatable must be updatable in practice by the
DBMS.
7. The High-level Insert, Update, and Delete rule: The capability
of handling a base relation or a derived relation as a single database
to perform all DML operations.
8. The Physical Data Independence rule: Application programs
 and terminal activities remain logically unchanged whenever any
changes are made in either storage representations or access
methods.
9. The Logical Data Independence rule: Application programs
and terminal activities remain logically unchanged when
information preserving changes of any kind are made to the base
tables.
10. The Integrity Independence rule: Integrity constraints must
be definable in the RDBMS sub-language and stored in the system
catalogue and not within individual application programs.
11. The Distribution Independence rule: An RDBMS has
distribution independence. Distribution independence implies that
users should not have to be aware of whether a database is
distributed.
12. The No subversion rule: If the database has any means of
handling a single record at a time that low-level language must not
be able avoid the integrity rules which are expressed in a
higher-level language that handles multiple records at a time.

b 4M Draw and explain three level architecture of data Abstraction Diagram

Ans Database systems are made-up of complex data structures. To ease 2 Marks
the user interaction with database, the developers hide internal Explanation
irrelevant details from users. This process of hiding irrelevant 2 Marks
details from user is called data abstraction.

•Physical level: This is the lowest level of data abstraction. It

describes how data is actually stored in a database. You can get the
complex data structure details at this level.
•Logical level: This is the middle level of 3-level data abstraction
architecture. It describes what data is stored in the database.
•At view level, user just interact with the system with the help of
GUI and enter the details at the screen, they are not aware of how
the data is stored and what data is stored; such details are
hidden from them.
 c 4M Normalize the following database up to 3NF Studinfo(roll no, 1 Marks
Ans mobile, subID, sub Marks, deptno, dept name, dept head) here 1 Marks
(roll no, subID ) is primary key 2 Marks
 d 4M State and explain Properties of the transaction. ½ Marks for
Ans listing each
Properties
½ mark for
explanation

Atomicity: This principle states that database transactions must be

all or nothing. If the transaction fails, the entire transaction is rolled
back. Atomicity prevents partial and incomplete transactions.
Consistency: According to this property, only valid data is written
to the database. Consistency enforces integrity constraints to
maintain the accuracy and correctness of data.
Isolation: Running transactions independently is the core of
isolation. Changes in one transaction will not impact others until
committed. Isolation maintains data integrity across concurrent
transactions.
 Durability: Durability guarantees that all committed transactions
are permanently recorded in the database. They persist even after
system failure. Durability provides recoverability.

Que Sub Max Marks

No. Que Marks Distribution
No. for Question & Answer
Sub
Que.

Q4 12M Attempt any THREE of the following

a 4M Write SQL queries for the following. 1 Mark

Ans i) Create user named 'user1' having Password user@2024' 1 Mark
ii) Assign create session and create table privileges to above user 1 Mark
iii) Assign 'insert' and update' Privilege on ‘Emptable’ to 1 Mark
'userl".
iv) Remove delete privilege on ‘Emptable’ from above user
userl.

i) CREATE USER user1 IDENTIFIED BY "user@2024";

ii) GRANT CREATE SESSION, CREATE TABLE TO user1;
iii) GRANT INSERT, UPDATE ON Emptable TO user1;
iv) REVOKE DELETE ON Emptable FROM user1;

b 4M State the difference between instance and schema. State and Definition
Ans explain the types of data independence 2 Mark
Definition of schema: Design of a database is called the schema. Explanation
Schema can be changed infrequently as information is inserted and 2 Mark
deleted.
Definition of instance: The data stored in a database at a particular
moment of time is called an instance of the database.
•Database schema defines the variable declarations in tables that
belong to a particular database; the value of these variables at a
moment of time is called the instance of that database.

Data Independence
Definition-The ability to modify a schema definition in one level
without affecting a schema definition in a higher level is called Data
Independence
.
Types of Data Independence:
1) Physical data independence
•The ability to modify the physical schema without causing
application programs to be rewritten
•Modifications at this level are usually to improve performance
2) Logical data independence
 •The ability to modify the conceptual schema without causing
application programs to be rewritten
•Usually done when logical structure of database is altered
•Logical data independence is harder to achieve as the application
programs are usually heavily dependent on the logical structure of
the data. An analogy is made to abstract data types in programming
languages.

c 4M Compare strong entity set and weak entity set with example Any 4 Point
Ans (any four points) 4 Marks
 d 4M List and explain types of joins ½ Marks for
Ans Joins: As the term suggests, SQL JOINs refers to a clause in SQL listing each
that is used to combine rows of two or more tables by using Types
common values. ½ mark for
INNER JOIN explanation
INNER JOIN statement returns only those records or rows that
have matching values and is used to retrieve data that appears in
both tables.

Syntax:
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
OUTER JOIN
When applying an SQL INNER JOIN, the output returns only
matching rows from the stated tables. In contrast, if you use an SQL
OUTER JOIN, it will retrieve not only the matching rows but also
the unmatched rows as well.

Syntax:
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
LEFT OUTER JOIN
Left Outer Join returns all the rows from the table on the left and
columns of the table on the right is null padded. Left Outer Join
retrieves all the rows from both the tables that satisfy the join
condition along with the unmatched rows of the left table.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
RIGHT OUTER JOIN
The RIGHT OUTER JOIN works by the same principle as the
LEFT OUTER JOIN. The RIGHT OUTER JOIN selects data
from the right table (Table B) and matches this data with the rows
from the left table (Table A). The RIGHT JOIN returns a result set
that includes all rows in the right table, whether or not they have
matching rows from the left table. In case, a row in the right table
does not have any matching rows in the left table, the column of the
left table in the result set will have nulls.

Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
SELF JOIN
The SELF JOIN allows you to join a table to itself. This implies
that each row of the table is combined with itself and with every
other row of the table. The SELF JOIN can be viewed as a join of
two copies of the same table. The table is not actually copied, but
SQL performs the command as though it were. This is
accomplished by using table name aliases to give each instance of
the table a separate name. It is most useful for extracting
hierarchical data or comparing rows within the same table.

Syntax:
SELECT t1.column1, t2.column2
FROM table AS t1
JOIN table AS t2 ON t1.related_column = t2.related_column;
CROSS JOIN
 The CROSS JOIN command in SQL, also known as a cartesian
join, returns all combinations of rows from each table. Envision that
you need to find all combinations of size and color. In that case, a
CROSS JOIN will be an asset. Note, that this join does not need any
condition to join two tables. In fact, CROSS JOIN joins every row
from the first table with every row from the second table and its
result comprises all combinations of records in two tables.

Syntax:
SELECT t1.column1, t1.column2, ..., t2.columnA, t2.columnB, ...
FROM table1
CROSS JOIN table2;

e 4M Define Exception and explain two types of Exception ½ Mark for

Ans Exception handling in PL/SQL: definition of
An exception is an error condition during a program execution. each type
PL/SQL supports programmers to catch such conditions using the 1 ½ marks
EXCEPTION block in the program and an appropriate action is for each
taken against the error condition. syntax

There are two types of exceptions −

1) System-defined exceptions/Predefined exceptions/Built-in
Exceptions
2) User-defined exception

1) Predefined exceptions- PL/SQL provides predefined

Exceptions, which are executed when any database rule is violated
by a program.

Example: NO_DATA_FOUND, ZERO_DIVIDE.

Syntax for Predefined Exception Handling:

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
 ........
WHEN others THEN
exception3-handling-statements
END;
/

2) User defined Exceptions:

PL/SQL allows us to define our own exceptions according to the
needs of our program. A user defined exception must be declared
and then raised explicitly.

Syntax for User defined Exception:

DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
/

Que Sub Max Marks

No. Que Marks Distribution
No. for Question & Answer
Sub
Que.

Q5 12M Attempt any THREE of the following

a 6M Consider the table Employee (empid, empname, salary, 1 Mark

Ans designation) and write queries to Each correct
i) Update salary of all employees by 5% Query
ii) Delete all employees whose designation is ‘ Analyst’
iii) Delete employee ID and name of those employees whose
salary is in range 20,000 to 40,000
iv) Insert one record in the employee table
v) Add a column mobile no in above table schema
vi) Delete all rows from the table employee

i)UPDATE Employee SET salary = salary * 1.05;

ii) DELETE FROM Employee WHERE designation = 'Analyst';
iii) DELETE FROM Employee WHERE salary BETWEEN 20000
AND 40000;
iv) INSERT INTO Employee (empid, empname, salary, designation)
VALUES (101, 'John Doe', 50000, 'Manager');
OR
INSERT INTO Employee VALUES (101, 'John Doe', 50000,
 'Manager');
v)ALTER TABLE Employee ADD mobile_no VARCHAR2(15);
vi)DELETE FROM Employee; OR TRUNCATE TABLE Employee;

b 6M Write a PL/SQL program to find if the entered number is even Correct

Ans or odd. Handle the exception Declare
2 Mark
DECLARE
num NUMBER; Correct
result VARCHAR2(10); Begin
BEGIN 2 Mark
DBMS_OUTPUT.PUT_LINE('Enter a number:');
num := &input_number; Correct
Exception
IF MOD(num, 2) = 0 THEN 2 Mark
result := 'Even';
ELSE
result := 'Odd';
END IF;

DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is ' || result ||

'.');
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Invalid input! Please enter a valid
number.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' ||
SQLERRM);
END;
/

c 6M State the use of sequence. Write and explain the syntax of create
Ans sequence and alter sequence
A sequence is used to generate numbers in sequence. You can use 1 Marks
sequences to insertunique values in Primary Key and Unique Key each
columns of tables.

To create a sequence, 2 Marks

give the CREATE SEQUENCE statement.

Syntax for creating sequence: 2 Marks

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE|NOCYCLE ;

Syntax Alter sequence:

alter sequence <sequence_name> maxvalue <number>;
Alter sequence can change the maxvalue in the sequence created.
 sequence_name: Name of the sequence.
initial_value: starting value from where the sequence starts.
Initial_value should be greater than or equal to minimum value and
less than equal to maximum value.
increment_value: Value by which sequence will increment itself.
Increment_value can be positive or negative.
minimum_value: Minimum value of the sequence.
maximum_value: Maximum value of the sequence.
cycle: When a sequence reaches its set_limit it starts from the
beginning.
nocycle: An exception will be thrown if the sequence exceeds its
max_value.

Que Sub Max Marks

No. Que Marks Distribution
No. for Question & Answer
Sub
Que.

Q6 12M Attempt any THREE of the following

a 6M Write a function which takes customer ID as parameter and returns Correct

Ans age of the customer, Use following table schema Logic
Customer(custid,custname,dob,city) 3 Mark

DECLARE FUNCTION Correct

CREATE OR REPLACE FUNCTION get_customer_age (p_custid IN Syntax
NUMBER) 3 Mark
RETURN NUMBER IS
v_dob DATE;
v_age NUMBER;
BEGIN
SELECT dob INTO v_dob FROM Customer WHERE custid =
p_custid;
v_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob) / 12);
RETURN v_age;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No customer found with the provided
ID.');
RETURN NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
RETURN NULL;
END;
/

CALLING FUNCTION
DECLARE
customer_age NUMBER;
 BEGIN
customer_age := get_customer_age(101);
IF customer_age IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('The age of the customer is: ' ||
customer_age);
ELSE
DBMS_OUTPUT.PUT_LINE('Customer age could not be
determined.');
END IF;
END;
/

b 6M Write SQL commands for the following 2 Mark

Ans i) Create two tables 2 Mark
Student(rn, name, city) where rn is primary key and stud result(rn, 1 Mark
subid, smarks) where rn is foreign key referencing the student table. 1 Mark
ii) Display roll number and total marks of the students
iii) Display subject wise average marks
i)
-- Create Student table with rn as the primary key
CREATE TABLE Student (
rn NUMBER PRIMARY KEY,
name VARCHAR2(50),
city VARCHAR2(50)
);
-- Create stud_result table with rn as a foreign key referencing
Student
CREATE TABLE stud_result (
rn NUMBER,
subid VARCHAR2(10),
smarks NUMBER,
CONSTRAINT fk_student FOREIGN KEY (rn) REFERENCES
Student(rn)
);

ii)
SELECT
sr.rn AS Roll_Number,
SUM(sr.smarks) AS Total_Marks
FROM
stud_result sr
GROUP BY
sr.rn;

iii)
SELECT
sr.subid AS Subject_ID,
AVG(sr.smarks) AS Average_Marks
FROM
stud_result sr
GROUP BY
sr.subid;
 c 6M Explain the super key, Candidate key, Primary key and Foreign key Explanation
Ans with example 1 Mark each
Keys
•Keys play an important role in the relational database. Example
•It is used to uniquely identify any record or row of data from the table. ½ Mark
Each
It is also used to establish and identify relationships between tables.
Types of keys:

1. Primary key
•It is the first key used to identify one and only one instance of an entity
uniquely. An entity can contain multiple keys, as we saw in the PERSON
table. The key which is most suitable from those lists becomes a primary
key.
•In the EMPLOYEE table, ID can be the primary key since it is unique
for each employee. In the EMPLOYEE table, we can even select
License_Number and Passport_Number as primary keys since they are
also unique.
•For each entity, the primary key selection is based on requirements and
developers.

2. Candidate key
•A candidate key is an attribute or set of attributes that can uniquely
identify a tuple.
•Except for the primary key, the remaining attributes are considered a
candidate key. The candidate keys are as strong as the primary key.
•
For example: In the EMPLOYEE table, id is best suited for the primary
key. The rest of the attributes, like SSN, Passport_Number,
License_Number, etc., are considered a candidate key.

3. Super Key
Super key is an attribute set that can uniquely identify a tuple. A super
key is a superset of a candidate key.
For example: In the below EMPLOYEE table, for(EMPLOEE_ID,
EMPLOYEE_NAME), the name of two employees can be the same, but
their EMPLYEE_ID can't be the same. Hence, this combination can also
be a key.
The super key would be EMPLOYEE-ID (EMPLOYEE_ID,
EMPLOYEE-NAME), etc.