Seat No.: ________ Enrolment No.
___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER– III (New) EXAMINATION – WINTER 2019
Subject Code: 2130703 Date: 30/11/2019
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.
Q.1 (a) Define Materialize view, Transaction, Metadata 03
(b) Explain Three level architecture of DBMS. 04
(c) Explain Aggregate functions with example. 07
Q.2 (a) Describe sub query and Correlated sub query. 03
(b) Explain Advantages of Database management system over File oriented 04
system.
(c) Define E-R Diagram. Draw an E-R Diagram for Hospital Billing 07
System.
OR
(c) Discuss various steps of Query processing with diagram. 07
Q.3 (a) What are the different types of relationships in the DBMS? 03
(b) Explain ACID Properties of Transaction. 04
(c) What is Normalization? Explain 1NF, 2NF, 3NF, BCNF with example. 07
OR
Q.3 (a) Write short note on Cursor. 03
(b) What do you mean by atomicity and aggregation? 04
(c) Define Join. Explain all Joins with examples. 07
Q.4 (a) Define Encryption, DCL and DML. 03
(b) Explain Conflict serializability. 04
(c) Write PL/SQL block to print whether the given number is Armstrong 07
number or not.
OR
Q.4 (a) Define commit, rollback and save point. 03
(b) Compare Discretionary access control and Mandatory Access Control. 04
(c) What is Functional dependency? Explain all its types with example. 07
Q.5 (a) Define wait-Die and Wound-wait. 03
(b) Write short note on stored Procedure with example. 04
(c) Compute the closure of the following set F of functional dependencies 07
for relation schema R = (ABCDE),
F = {A -> C, E -> D, B -> C}
List the candidate keys for R.
OR
Q.5 (a) Write short note on 2PC Protocol. 03
(b) Differentiate Grant and Revoke with example. 04
1
(c) Write SQL statements (Query) for following tables: 07
E1 (eno,ename,age,city,bcode)
E2 (bcode, bname)
1. Display employees’ details whose branch code is between 1 to 5.
2. Find an average age of all employees whose branch code is 3.
3. Retrieve branch information in descending order.
4. Add new branch in E2 table.
5. Display eno, ename and age of employees whose city is
Ahmedabad.
6. Delete employee details whose age is 29.
7. Change age of employee to 25 whose employee no is 6.
*************
2
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER–III (NEW) EXAMINATION – SUMMER 2019
Subject Code: 2130703 Date: 07/06/2019
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) Explain query evaluation process. 03
(b) Explain disadvantages of file processing systems compare to Database 04
management system.
(c) Consider the following relations: 07
EMP(empno, ename, jobtitle, managerno, hiredate, sal, commission,
deptno)
DEPT(deptno, dname, location)
Answer the following queries in SQL and Give an expression in the
relational algebra to express each of the following queries.
a. Find the Employees working in the department number10, 20, 30
only.
b. Find Employees whose names start with letter A or letter a.
c. Find Employees along with their department name.
d. Find the Employees who are working in Smith's department
e. Find the Employees who get salary more than Allen’s salary.
f. Display employees who are getting maximum salary in each
department.
g. Find list of employees whose hire date is on or before 1-April-18.
Q.2 (a) What is the difference between data security and data integrity? 03
(b) What is transaction? List and explain ACID property of transaction. 04
(c) Design a database for an airline. The database must keep track of 07
customers and their reservations, flights and their status, seat assignments
on individual flights, and the schedule and routing of future flights. Your
design should include an E-R diagram, a set of relational schemas, and a
list of constraints, including primary-key and foreign-key constraints.
OR
(c) Design a database for a hospital with a set of patients and a set of medical 07
doctors. Associate with each patient a log of the various tests and
examinations conducted Your design should include an E-R diagram, a
set of relational schemas, and a list of constraints, including primary-key
and foreign-key constraints.
Q.3 (a) What is constraint? Explain types of constraints. 03
(b) Consider following set F of functional dependencies on schema R(A,B,C) 04
and compute canonical cover for F.
A→ BC
1
B→ C
A→B
AB → C
(c) Why should normalization be performed on a table and what are its 07
benefits. Explain 3NF and BCNF
OR
Q.3 (a) Describe the following SQL functions: (i) Add_months(x,y) (ii) Rtrim() 03
(iii) To_date
(b) Normalize the following schema, with given constraints, to 4NF. 04
books(accessionno, isbn, title, author, publisher)
users(userid, name, deptid, deptname)
List of constraints:
accessionno → isbn
isbn → title
isbn → publisher
isbn →→ author
userid → name
userid → deptid
deptid → deptname
(c) What is redundant functional dependency? Explain trivial and non trivial 07
functional dependency with example.
Q.4 (a) Explain on delete cascade with example. 03
(b) Explain system recovery procedure with Checkpoint record concept. 04
(c) Explain following relational algebraic operation (i) Division (ii) inner join 07
(iii) intersection
OR
Q.4 (a) Explain commit, rollback and savepoint command. 03
(b) Explain Log based recovery method. 04
(c) Explain following relation algebra operations: (i) full outer join (ii) 07
projection (iii) Cartesian product.
Q.5 (a) Write a PL/SQL block to print the sum of Numbers from 1 to 100. 03
(b) Explain the purpose of sorting with example with reference to query 04
optimization.
(c) Explain conflict serializability and view serializability with example. 07
OR
Q.5 (a) Write a PL/SQL block to print the given number is prime or not. 03
(b) Explain the measures of finding out the cost of a query in query 04
processing.
(c) What is a recoverable schedule? Why is recoverability of schedules 07
desirable? Are there any circumstances under which it would be desirable
to allow non-recoverable schedules? Explain your answer
*************
2
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER–III (New) EXAMINATION – WINTER 2018
Subject Code: 2130703 Date:01/12/2018
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.
Q.1 (a) Define primary key, NOT NULL key and Unique key. 03
(b) Explain types of database users. 04
(c) Explain advantage of database management system over a file oriented system. 07
Q.2 (a) Define DDL, DML and DCL. 03
(b) List the reason why NULL values introduces might be introduces in database. 04
(c) Explain three level architecture of DBMS. 07
OR
(c) Explain different types of outer join with example. 07
Q.3 (a) Write a note on two phase commit protocol. 03
(b) List and explain aggregation functions with suitable example. 04
(c) Explain specialization and generalization concepts in ER diagram with suitable 07
example.
OR
Q.3 (a) Write a note on trigger. 03
(b) Explain difference between strong entity set and weak entity set. 04
(c) Define E-R diagram. Draw an E-R diagram for Library Management System. 07
Assume relevant entities and attributes for the given system.
Q.4 (a) Write differences between shared lock and exclusive lock. 03
(b) Define transaction. Explain various states of transaction with suitable diagram. 04
(c) Explain query processing step with diagram. 07
OR
Q.4 (a) Describe GRANT and REVOKE commands. 03
(b) Write a note on two phase locking protocol. 04
(c) Explain stored procedure with proper example. 07
Q.5 (a) What is log based recovery? Explain Immediate database modification technique 03
for database recovery.
(b) List and discuss ACID properties of transaction. 04
(c) Write a note on conflict serializability. 07
OR
Q.5 (a) What is deadlock of transaction? Explain wound wait technique for prevention of 03
deadlock.
(b) What is meant by normalization? Write its need. List and discuss database anomaly 04
during database design.
1
(c) Consider following schema and write SQL for given statements. 07
employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager_name)
1. Find the names of all employees who work for First Bank Corporation.
2. Give all employees of First Bank Corporation a 10-percent raise.
3. Find the names and cities of residence of all employees who work for First Bank
Corporation.
4. Find the names, street addresses, and cities of residence of all employees who
work for First Bank Corporation and earn more than $10,000.
5. Find all employees in the database who live in the same cities as the companies for
which they work.
6. Find all employees in the database who do not work for First Bank Corporation.
*************
2
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER–III (NEW) - EXAMINATION – SUMMER 2018
Subject Code:2130703 Date:23/05/2018
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.
MARKS
Q.1 (a) Define view. Write syntax to create view .Give an example of view. 03
(b) Explain tasks of DBA. 04
(c) Explain disadvantages of conventional file-based system compared to Database 07
management system.
Q.2 (a) List and explain DML statements with suitable example. 03
(b) Define: Primary key, Foreign Key and NOT NULL constraint 04
(c) Explain three level architecture of DBMS. 07
OR
(c) Explain different types of outer join with example. 07
Q.3 (a) Write a note on two phase commit protocol. 03
(b) List and explain aggregation functions with suitable example. 04
(c) Explain specialization and generalization concepts in ER diagram with suitable 07
example.
OR
Q.3 (a) Write a note on trigger. 03
(b) Compare rollback with commit SQL commands. 04
(c) Draw E-R diagram for bank management system. 07
Q.4 (a) Write differences between shared lock and exclusive lock. 03
(b) Define transaction. Explain various states of transaction with suitable diagram. 04
(c) Explain steps of query processing with the help of neat diagram. 07
OR
Q.4 (a) Describe GRANT and REVOKE commands. 03
(b) Write a note on two phase locking protocol. 04
(c) Explain stored procedure with proper example. 07
Q.5 (a) What is log based recovery? Explain Immediate database modification technique 03
for database recovery.
(b) List and discuss ACID properties of transaction. 04
(c) Write a note on conflict serializability. 07
OR
Q.5 (a) What is deadlock of transaction? Explain wound wait technique for prevention of 03
deadlock.
(b) Elaborate Normalization. Explain any two normal forms with suitable 04
example(s).
(c) Consider following schema and write SQL for given statements. 07
Client_master(clientno, name, address, city, pincode, state, baldue)
Product_master(productno, name, profitpercent, unitmeasure, sellprice, costprice)
Salesman_master(Salesmanno,name,address,city,pincode,state,salary,tgtotget,
remarks)
1) Find out the names of all clients.
2) List all the clients who are located in Mumbai.
3) Delete all salesmen from salesman_master whose salaries are equal to
Rs.3500.
4) Destroy the table client_master along with data.
5) List the name of all clients having ‘a’ as the second letter in their names.
6) Count the number of products having cost price is less than or equal to
500.
7) Calculate the average, minimum and maximum sell price of product.
*************
Seat No.: ________ Enrolment No.___________
GUJARAT TECHNOLOGICAL UNIVERSITY
BE - SEMESTER–III (NEW) EXAMINATION – WINTER 2017
Subject Code: 2130703 Date:17/11/2017
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.
MARKS
Q.1 (a) Define Primary key, Candidate key and Super key. 03
(b) Define DBMS. Describe significant differences between a file-processing system and a 04
DBMS.
(c) Explain the ACID properties using suitable example. 07
Q.2 (a) Explain the difference between a weak and a strong entity set. 03
(b) What are main functions of a Database Administrator? 04
(c) Design a generalization–specialization hierarchy for a motor-vehicle sales company. The 07
company sells motorcycles, passenger cars, vans, and buses. Justify your placement of
attributes at each level of the hierarchy. Explain why they should not be placed at a
higher or lower level.
OR
(c) Construct an E-R diagram for a car-insurance company whose customers own one or 07
more cars each. Each car has associated with it zero to any number of recorded accidents.
Q.3 (a) What is Relational Algebra? Define Relational Algebra Operation Cross product with 03
example.
(b) Describe GRANT and REVOKE commands with suitable example. 04
(c) Explain Two phase locking protocol. 07
OR
Q.3 (a) List reasons why null values might be introduced into the database. 03
(b) Consider the relational database given below. Give an expression in the relational algebra 04
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 the names of all employees in this database who live in the same city as the
company for which they work.
2) Find the names, street address, and cities of residence of all employees who work
for HCL and earn more than $10,000 per annum.
(c) Discuss various steps of query processing with proper diagram. 07
Q.4 (a) Consider schema R = (A, B, C, G, H, I) and the set F of functional dependencies 03
{A → B, A → C, CG → H, CG → I, B → H}.( Use F+ )
Prove that AG → I Holds.
(b) Consider schema EMPLOYEE(E-ID,E-NAME,E-CITY,E-STATE) and 04
FD = {E-ID->E-NAME, E-ID->E-CITY, E-ID->E-STATE, E-CITY->E-STATE}
1) Find attribute closure for: (E-ID)+
2) Find(E-Name)+
1
(c) Explain Aggregate Functions of SQL with suitable example. 07
OR
Q.4 (a) Explain Outer Joins operations. 03
(b) Define View. Explain with appropriate example. 04
(c) Consider following relations and write SQL queries for given statements. 07
Assume suitable constrains.
Instructor(ID, Name, Dept_name , Salary)
Teaches(ID, Course_id, Sec_id, Semester(even/odd),Year)
1) Write SQL query to create Instructor table.
2) Find the average salary of the instructors in computer department.
3) Find the number of instructors in each department who teach a course in even
semester of 2016.
4) Find the names of instructor with salary amounts between 30000 and 50000.
Q.5 (a) Explain various types of LOCKs used in Lock base protocol for concurrency control. 03
(b) What are triggers? Explain the advantages and the needs of triggers. 04
(c)Write a PL/SQL program for inserting even numbers in EVEN table and odd number in 07
ODD table from number 1 to 50.
OR
Q.5 (a) Explain Cursor in PL/SQL 03
(b) Explain deadlock with suitable example. 04
(c) What is Normalization? Normalize below given relation up to 3NF. 07
STUDENT:
Stu Stu City Pin Project Project Course Content
ID Name code ID Name
S101 Ajay Surat 326201 P101 health programming C++, Java,C
S102 Vijay Pune 325456 P102 social WEB HTML,PHP,ASP
*************