Hall Ticket No:
Question Paper Code: A3516
VARDHAMAN COLLEGE OF ENGINEERING
(AUTONOMOUS) B. Tech IV Semester Regular/Supplementary Examinations, May 2018
(Regulations: VCE-R15) DATABASE MANAGEMENT SYSTEMS
(Computer Science and Engineering) Date: 25 May, 2018 FN
Time: 3 hours
Max Marks: 75
Answer ONE question from each Unit
All Questions Carry Equal Marks
Unit I
1.
With a neat diagram explain the various components of a database system and their connections.
8M
Explain the differences between physical and logical data independence.
7M
2.
Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.
8M
Define the concept of aggregation. Give two examples of where this concept is useful.
7M
Unit II
3.
Discuss the strengths and weaknesses of the trigger mechanism. Contrast triggers with other integrity constraints supported by SQL.
7M
Consider the following relational database.
employee (person_name, street, city) works (person_name, company_name, salary) company (company_name, city) manages (person_name, manager_name) Give a relational algebra expression for each of the following queries:
i. Find the company with the most employees
ii. Find the company with the smallest payroll
iii. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation
8M
4.
Consider a database schema with the following relations:
Student name) Prof name) Course (number, instructor-ssn, title, credits, room#) Enroll (student-ssn, course#) Room (number, capacity) i. Write an SQL query that lists, in alphabetical order, the title of all courses either taught by "Smith" OR are taught in room number 444. Do not list duplicate titles.
ii. Write an SQL query that considers all the courses that have ever been taught by "Brown" and are of 3 credits, and groups them according to title. For each course, the query gives the average capacity of rooms in which the course has been offered, and the query only returns courses for which this average is more than 20.
7M
Consider a database schema with the following relations:
Student name) Prof name) Course (number, instructor-ssn, title, credits, room#) Enroll (student-ssn, course#) Room (number, capacity) i. Write a tuple relational calculus query that finds the names of all students who are enrolled in a class taught by "Jones," AND are enrolled in a class called "Physics"
ii. Write a tuple relational calculus query that finds the names of all students who are NOT enrolled in two classes held in the same room
8M
Cont…2

Unit III
5.
What is a functional dependency? How is a functional dependency like a candidate key? What is the meaning of an FD with multiple columns on the right-hand side?
7M
Consider the following relation scheme for compact discs
CD (Company, Date, CatalogNum, Composer, Track, Group, Artist, Title, Instrument,
Duration) together with the following functional dependencies:
{Artist} {Instrument, Group}
{Composer, Title, Company} {CatalogNum, Track, Duration}
{CatalogNum} {Company, Date}
{CatalogNum, Title} {Composer}
i. Calculate the closure of the attribute set {CatalogNum, Title} with respect to the
given functional dependencies
ii. Find two candidate keys for CD
8M
6.
Explain briefly 4NF and 5 NF.
7M
Consider a relational schema R with attributes E and the set of functional dependencies A → CD, B → CE, E →
i. Give a lossless-join decomposition of R into BCNF
ii. Give a lossless-join decomposition of R into 3NF preserving FD. Is you answer is in BCNF
8M
Unit IV
7.
What is recoverable schedule? Why is recoverability of schedules desirable? Are there any circumstances under which it would be desirable to allow non recoverable schedules? Explain your answer.
7M
Consider the following two transactions:
T11:

if A=0 then
write T12:

if B=0 then
write
Add lock and unlock transactions to transaction T11 and T12, so that they observe the two phase locking protocol. Can the execution of these transactions result in a deadlock?
8M
8.
Disk space allocated to a file as a result of a transaction should not be released even if the transaction is rolled back. Explain why, and explain how ARIES ensures that such actions are not rolled back.
7M
One problem that can arise with the use of locking protocols is DEADLOCK Justify with suitable example.
8M
Unit V
9.
What is the deference between a clustered index and an unclustered index? If an index contains data records as 'data entries,' can it be unclustered.
8M
What is a tree? Explain its dynamic structure.
7M
10.
Write short notes on RAID.
7M
What is index data structure? Discuss various types of file organizations and their pros and cons.
8M