Seat No.: ________ Enrolment No.

___________

GUJARAT TECHNOLOGICAL UNIVERSITY

BE- SEMESTER–III (NEW) EXAMINATION – WINTER 2020
Subject Code:3130703 Date:04/03/2021
Subject Name:Database Management Systems
Time:10:30 AM TO 12:30 PM Total Marks:56
Instructions:
1. Attempt any FOUR questions out of EIGHT questions.
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.

Marks
Q.1 (a) Define following terms.
1) Schema 2) Database Management System 3) Physical Data 03
Independence
(b) Describe tasks performed by the Database
Administrator. 04
(c) Differentiate strong entity set and weak entity set. Demonstrate the
concept of both using real-time example using E-R diagram. 07

Q.2 (a) Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} and the set of
functional dependencies
{{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R.
03
What is the key for R?
(b) Consider a relation scheme R = (A, B, C, D, E, H) on which the following
functional dependencies hold: {A–>B, BC–> D, E–>C, D–>A}. What are the 04
candidate keys of R?( Any 1 in case of more than one candidate key
(c) Draw an E-R diagram of following scenario. Make necessary
assumptions and clearly note down the same.
07
We would like to make our College’s manually operated Library to fully
computerized .

Q.3 (a) Define the terms: a) Primary Key b) Super Key 03

(b) List the type of joins in relational algebra.
04
Explain with example.
(c) Which operator is used for “For All “type of queries? Explain same with
07
example.

Q.4 (a) Define the terms : a) foreign key b) candidate key 03

(b) List unary relational operators and explain with example. 04
(c) Consider the following relational database schema consisting of the
four relation schemas:
passenger ( pid, pname, pgender, pcity)
agency ( aid, aname, acity)
flight (fid, fdate, time, src, dest)
booking (pid, aid, fid, fdate)
07
Answer the following questions using relational algebra queries.
a. Get the details about all flights from Chennai to New Delhi.
b. Get the complete details of all flights to New Delhi.
c. Find the passenger names for passengers who have bookings on at least
one flight.

1
Q.5 (a) List and explain ACID properties with respect to Database transaction. 03
(b) Explain RAID Levels with respect to Data Storage. 04
(c) Explain the concept of Conflict Serializable with suitable schedules. 07

Q.6 (a) List and explain types of locks in transactions. 03

(b) With neat diagram explain data storage hierarchy so far. 04
(c) Explain deadlock with suitable scheduling examples.
07
Q.7 (a) Explain following SQL commands with syntax and significance.
03
Grant & Revoke
(b) TABLE Worker(WORKER_ID INT NOT NULL PRIMARY
KEY,FIRST_NAME CHAR(25), LAST_NAME CHAR(25),SALARY
INT(15),JOINING_DATE DATETIME,DEPARTMENT CHAR(25));

TABLE Bonus(WORKER_REF_ID INT,BONUS_AMOUNT

INT(10),BONUS_DATE DATETIME,FOREIGN KEY
(WORKER_REF_ID),REFERENCES Worker(WORKER_ID));

TABLE Title(WORKER_REF_ID INT,WORKER_TITLE

CHAR(25),AFFECTED_FROM DATETIME,FOREIGN KEY 04
(WORKER_REF_ID)REFERENCES Worker(WORKER_ID));

Consider above 3 tables ,assume appropriate data and solve

following SQL queries
1. Find out unique values of DEPARTMENT from Worker table
2. Print details of the Workers whose SALARY lies between 100000
and 500000.
3. Print details of the Workers who have joined in Feb’2014.
4. Fetch worker names with salaries >= 50000 and <= 100000.
(c) Write short note on query processing. 07

Q.8 (a) Explain following SQL commands with syntax and significance.
03
Commit & Rollback
(b) TABLE Worker(WORKER_ID INT NOT NULL PRIMARY
KEY,FIRST_NAME CHAR(25), LAST_NAME CHAR(25),SALARY
INT(15),JOINING_DATE DATETIME,DEPARTMENT CHAR(25));

TABLE Bonus(WORKER_REF_ID INT,BONUS_AMOUNT

INT(10),BONUS_DATE DATETIME,FOREIGN KEY
(WORKER_REF_ID),REFERENCES Worker(WORKER_ID));

TABLE Title(WORKER_REF_ID INT,WORKER_TITLE CHAR(25),

AFFECTED_FROM DATETIME,FOREIGN KEY
(WORKER_REF_ID)REFERENCES Worker(WORKER_ID));
04
Consider above 3 tables ,assume appropriate data and
solve following SQL queries

1. Print details of the Workers who are also Managers.

2. SQL query to clone a new table from another table.
3. Fetch the list of employees with the same salary.
4. Fetch “FIRST_NAME” from Worker table in upper case.

(c) List the techniques to obtain the query cost. Explain any one. 07

***********************

2
Seat No.: ________ Enrolment No.___________

GUJARAT TECHNOLOGICAL UNIVERSITY

BE - SEMESTER– III EXAMINATION – SUMMER 2020
Subject Code: 3130703 Date:29/10/2020
Subject Name: Database Management Systems
Time: 02:30 PM TO 05:00 PM Total Marks: 70
Instructions:
1. Attempt all questions.
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.

Marks

Q.1 (a) What are the main functions of a database 03

administrator?
(b) Explain the difference between physical and logical 04
data independence.
(c) Explain DBMS System Architecture. 07

Q.2 (a) Describe the differences in meaning between the 03

terms relation and relation schema.
(b) Write the following queries in relational algebra: 04
(1) Find the names of suppliers who supply
some red part.
(2) Find the IDs of suppliers who supply some
red or green part.
(c) An ER diagram can be viewed as a graph. What do 07
the following mean in terms of the structure of an
enterprise schema?
(1) The graph is disconnected.
(2) The graph is acyclic.
OR
(c) Draw ER diagram for university database consisting 07
four entities Student, Department, Class and
Faculty.

Student has a unique id, the student can enroll for

multiple classes and has a most one major. Faculty
must belong to department and faculty can teach
multiple classes. Each class is taught by only
faculty. Every student will get grade for the class
he/she has enrolled.
Q.3 (a) What is normalization? Explain 2NF. 03
(b) Explain typical query processing strategy of 04
DBMS?
(c) Compute the closure of the following set F of 07
functional dependencies for relation schema R = (A,
B, C, D, E).
A  BC
1
 CD  E
BD
EA
List the candidate keys for R.
OR
Q.3 (a) What is normalization? Explain 3NF. 03
(b) Write short on block nested loop join. 04
(c) Use the definition of functional dependency to argue 07
that each of Armstrong’s axioms (reflexivity,
augmentation, and transitivity) is sound.
Q.4 (a) Explain hashing. 03
(b) What is transaction? What are the functions of 04
commit and rollback?
(c) Write a short note on SQL injection. 07
OR
Q.4 (a) Explain B-trees. 03
(b) Explain conflict serializability and view 04
serializability.
(c) Write a short note on intrusion detection. 07

Q.5 (a) What is trigger? Explain its type with their syntax. 03
(b) Write a PL/SQL block to print the given number is 04
odd or even.
(c) Consider the following relational schemas: 07
EMPLOYEE (EMPLOYEE_NAME, STREET,
CITY)
WORKS (EMPLOYEE_NAME,
COMPANYNAME, SALARY)
COMPANY (COMPANY_NAME, CITY)

Give an expression in SQL for each of queries

below::
(1) Specify the table definitions in SQL.
(2) Find the names of all employees who work
for first Bank Corporation.
(3) Find the names and company names of all
employees sorted in ascending order of
company name and descending order of
employee names of that company.
(4) Change the city of First Bank Corporation to
‘New Delhi’.
OR

Q.5 (a) Explain cursor and its types. 03

(b) Write a PL/SQL block to print the sum of even 04
numbers from 1 to 50.
(c) Given the following relations 07
TRAIN (NAME, START, DEST)
TICKET (PNRNO., START, DEST, FARE)
2
PASSENGER (NAME, ADDRESS, PNRNO.)

Write SQL expressions for the following queries:

Note: Assume NAME of Train is a column of
Ticket.
(1) List the names of passengers who are
travelling from the start to the destination
station of the train.
(2) List the names of passengers who have a
return journey ticket.
(3) Insert a new Shatabti train from Delhi to
Bangalore.
(4) Cancel the ticket of Tintin.

3
Seat No.: ________ Enrolment No.___________

GUJARAT TECHNOLOGICAL UNIVERSITY

BE - SEMESTER–III (NEW) EXAMINATION – SUMMER 2021
Subject Code:3130703 Date:11/09/2021
Subject Name:Database Management Systems
Time:10:30 AM TO 01:00 PM Total Marks:70
Instructions:
1. Attempt all questions.
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.
4. Simple and non-programmable scientific calculators are allowed.

Marks
Q.1 (a) What is Data Definition Language? List DDL statements and explain 03
anyone with an example.
(b) List and describe ACID property of transactions. 04
(c) Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of 07
functional dependencies
F={{A, B} → C, A → {D, E}, B → F, F →{G, H}, D →{I, J}}
What is the key for R? Decompose R into 2NF, then 3NF relations.

Q.2 (a) Differentiate shared lock and exclusive lock in lock-based protocol. 03
(b) Describe the Cartesian Product operation in relational algebra. 04
(c) Draw E-R diagram for student management system with the necessary 07
assumption.
OR
(c) Consider the relational database given below. Give an expression in the 07
relational algebra to express each of the following queries:
Employee (person-name, street, city),
Works (person-name, company-name, salary),
Company (company-name, city),
Manages (person-name, manager-name)
(1) Find name of all employees.
(2) Find city of employee whose name is ‘jashu’.
(3) Find name and city of all employees who are having salary>50000.
(4) Find total salary of all employees who are working for company
‘HCL’.

Q.3 (a) Describe various state of transaction. 03

(b) List and explain mapping cardinalities of E-R diagram with example. 04
(c) What is the use of two-phase locking protocol in concurrency control? 07
Describe the two-phase locking protocol in detail.
OR
Q.3 (a) What is dirty write in the transaction? Explain with example. 03
(b) What are the importance of Primary key and Unique key in database? 04
Explain with example.
(c) What is a deadlock in transaction? How to detect deadlock in system? 07
Explain with example.

Q.4 (a) Differentiate closed hashing and open hashing in DBMS. 03

1
 (b) What is the role of an index in the database management system? Explain 04
dense index with example.
(c) What is the schedule in truncation? How to identify that the given schedule 07
is conflict serializable? Explain with example.
OR
Q.4 (a) What is log-based recovery? List and explain various fields use in log 03
records for log-based recovery.
(b) Discuss view serializability in transactions. 04
(c) Explain various steps involved in query processing with example. 07

Q.5 (a) Explain SQL injection in brief. 03

(b) What is the use of a cursor in PL/SQL? Explain with example. 04
(c) Consider the following relations and write SQL queries for given 07
statements. Assume suitable constraints.
job(job-id, job-title, minimum-salary, maximum-salary)
employee(emp-no, emp-name, emp-salary,dept-no)
deposit(acc-no, cust-name, branch-name, amount, account-date)
borrow(loan-no, cust-name, branch-name, amount)
department (dept-no, dept-name)
(1) Give name of employees whose employee number is '001'
(2) Give name of depositors whose branch name starts from ‘S’.
(3) Give employee name(s) whose salary is between Rs. 20000 to 30000
and department name is Finance.
(4) Update the salary of employee by 10% of their salary who is working
in the Finance department.
OR
Q.5 (a) Describe two rules of mandatory access control. 03
(b) Describe Grant and Revoke commands with suitable example. 04
(c) Write a PL/SQL program that fetches records of all students and insert 07
record as students having CPI > 4 in ELIGIBLE table and students having
CPI <= 4 in NOT_ELIGIBLE table from student_master table.