Scribd
Upload
17 views

Dbms Major 2016 (1)

The document outlines the structure and content of an end semester examination for a Database Management Systems course, including sections with various questions on database concepts, relational algebra, transaction management, and database design. It consists of multiple sections, with Section A containing six questions, Section B with three questions, and Section C being compulsory. Each question has specific marks allocated and covers topics such as SQL queries, normalization, and transaction protocols.

Uploaded by

Divyansh Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views

Dbms Major 2016 (1)

The document outlines the structure and content of an end semester examination for a Database Management Systems course, including sections with various questions on database concepts, relational algebra, transaction management, and database design. It consists of multiple sections, with Section A containing six questions, Section B with three questions, and Section C being compulsory. Each question has specific marks allocated and covers topics such as SQL queries, normalization, and transaction protocols.

Uploaded by

Divyansh Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

[NO.

OF PRINTED PAGES : 6]

CSE201 Enrol No. .................


[ET]
END SEMESTER EXAMINATION : NOV - DEC | 2016
DATABASE MANAGEMENT SYSTEMS
TIME : 3 HRS Maximum Marks : 70

SECTION A (30 Marks)


Attempt any five questions out of six.
Each question carriest 06 marks.

1. Discuss the man characteristics of the database


approach and how it differs from traditional file systems.

2. Let the following relation schemas be given :


R = (A, B, C) S = (D,E,F). Let relations r(R) and s(S) be
given. Give an expression in the tuple relational calculus
that is equivalent to each of the following

(a) πA (r)
(b) σ B-17 (r)
(c) r x s
(d) πA,F ( σ C = D(r x s) )

visit www.aminotes.com for more


3. Discuss the steps for implementing JOIN and UNION
by using sort-merge algorithms.

4. Differentiate between the following terms

(a) Pipelining and materialization (3)


(b) Wait die and wound wait protocols (3)

5. Give an algorith to find the non-redundent cover. Find


the minimal cover of the following set of FDs
F = { A → BC , CD → E, E → C, D → AEH, ABH → BD,
DH → BC }

6. Discuss horizontal and vertical fragmentation with the


help of suitable examples.
SECTION B (20 Marks)
Attempt any two questions out of three
Each question carriest 10 marks.

7.
(a) Discuss the reasons for converting SQL queries into
relational algebra queries before optimization is done. (5)

(b) Draw the tree and explain how heuristic query


optimization i performed on following query (5)

visit www.aminotes.com for more


SELECT Lname
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE pname = ‘Aquarius’ AND Pnumber = Pno
AND Essn = Ssn
And Bdate > ‘1957-12-31’;

8. Consider the following two transactions :

T31:read(A);
read(B);
if A = 0thenB:= B+1
write(B).
T32: read(B);
read(A);
if B = 0thenA:= A+1
write(A).

(a) Add lock and unlock instructions to transactions T31


and T32, so that they observe the two-phase looking
protocol. (6)

(b) How can the execution of these transactions result in


a deadlock? (4)

visit www.aminotes.com for more


9. Consider the relation R , which has attributes that hold
schedules of courses and sections at a university;
R = { CourseNo, SecNo, OfferingDept, CreditHours,
CourseLevel, InstructorSSN, Semester, Year, Days_Hours,
RoomNo, NoOfStudents} , Suppose that the following
funtional dependencises hold on R:
{CourseNo} → {OfferngDept, CreditHours, CourseLevel}

{CourseNo, SecNo, Semester, Year} → {Days_Hours,


RoomNo, NoOfStudents, InstructorSSN}

{RoomNo, Days_Hours, Semester, Year} → {InstructorSSN,


CourseNo, SecNo}
D
Determine which sets of attributes form keys of R. How
would you normalize this relation?
SECTION C (20 Marks)
(Compulsory)

10. (a) The company is organized intto departments, Each


department has a unique name, a unique number, and a
particular employee who manages the department. We
keep track of the start date when that employee began
managing the department. A department may have several
locations. A department controls a number of projects, each
of which has a unique name, a unique number, and a single
location. We store each employee’s name,
visit www.aminotes.com for more
Social security number, 2 address, salary, sex(gender), and
birth date. An employee is assigned to one department, but
may work on several projects, which are not necessarily
controlled by the same department. We keep track of the
current number of hours per week that an employee works
on each project. We also keep track of the direct supervisor
of each employee (who is another employee). We want to
keep track of the dependants of each employee for
insurance purposes. We keep each dependant’s first name,
sex, birth date, and relationship to the employee. Design
an ER diagram and schema Diagram for the given situation
state clearly if any assumption made. (10)

(b) Consider the relational database given below, where the


primary keys are underlined. Give an epression in the
relational algebra and SQL 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)

(i) Find the names of all employees in this database who


live in the same city as the company for which they work.

visit www.aminotes.com for more


(ii) Find the names of all employees who live in the same
city and on the same street as do their managers.

(iii) Find the names of all employees in this database who


do not work for First Bank Corporation.

(iv) Find the names of all employees who earn more than
every employee of Small Bank Corporation.

(v) Assume the companies ay be located in several cities.


Find all companies located in every city in which Small
Bank Corporation is located. (10)

visit www.aminotes.com for more

You might also like