Database Management Systems - Visvesvaraya Technological University December 2012 - January 2013 Questions / December 2012 - January 2013 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers
USN 06CS54
Fifth
Semester B.E. Degree Examination, December 2012
Database Management Systems
Time:
3 hrs. Max.
Marks: 100
Note: Answer any FIVE full
questions, selecting at least TWO questions from each part.
PART – A
1. a. Define the following with examples: i)
Value set ii) Complex attribute iii) Data model iv) Schema
v) Metadata. (10 Marks)
b. Explain the component modules of DBMS and
their interaction with the help of a diagram. (10 Marks)
2. a. What are the structural constraints on
a relationship type? Explain with examples. (05 Marks)
b. What is weak entity type? Explain the role
of partial key in design of weak entity type. (05 Marks)
c. Design an ER diagram for a movie database
considering the following requirements:
(i)
Each movie is identified by its title and year of release, it has length in
minutes and can have zero or more quotes, language.
(ii)
Production companies are identified by name, they have address, and each
production company can produce one or more movies.
(iii)
Actors are identified by name and date of birth, they can act in one or more
movies and each actor has a role in a movie.
(iv)
Directors are identified by name and date of birth, each director can direct
one or more movies and each movie can be directed by one or more directors.
(v)
Each movie belongs to any one category like Horror, action, drama, etc. (10 Marks)
3. a. What is meant by integrity
constraint? Explain the importance of referential integrity constraint. How referential
integrity constraint is implemented in SQL?
(10 Marks)
b. Consider the following schema
and write the relational algebra queries.
SAILORS (SID, SNAME,
RATING, AGE)
BOATS (BID, BNAME, COLOR)
RESERVE (SID, BID, DAY)
i) Retrieve the sailors’ names
who have reserved red and green boats.
ii) Retrieve the sailors’ names with
age over 20 years and reserved black boat.
iii) Retrieve the number of boats
which are not reserved.
iv) Retrieve the sailors’ names
who have reserved green boat on Monday.
v) Retrieve the oldest sailor’s
name whose rating is 10. (10 Marks)
4. a. Consider the following schema
and write the SQL queries:
EMP (SSN, NAME, ADDR, SALARY, SEX, DNO)
DEP (DNO, DNAME, MGRSSN)
DEP_LOC (DNO, DLOCN)
PROJ (PNO, PNAME, PLOCN, DNO)
WORKSON (SSN, PNO, NOHRS)
DEPENDENT (SSN, DEPENDENTNAME,
DEPNTSEX, DEPNTRELATIONSHIP)
(i) Retrieve the manager name with
at least 1 dependent.
(ii) Retrieve the employee name
who work on any of the project that Kumar works.
(iii) Retrieve the pno, pname,
number of man hours work done on each project.
(iv) Retrieve the pname which are
controlled by Research department.
(v) Retrieve the employee name
who work for dept 10 and have a daughter. (10
Marks)
b) Consider the following schema
and write the SQL queries:
STUDENT (SID, SNAME, MAJOR, GPA)
FACULTY (FID, FNAME, DEPT, DESIGNATION, SALARY)
COURSE (CID, CNAME, FID)
ENROLL (CID, SID, GRADE)
(i) Retrieve the student name who
is studying under facilities of “Mechanical dept”.
(ii) Retrieve the student name
who have enrolled under any of the courses in which ‘Kumar’ has enrolled.
(iii) Retrieve the faculty name
who earn salary which is greater than the average salary of all the faculties.
(iv) Retrieve the sname who are
not been taught by faculty ‘Kumar’.
(v) Retrieve the faculty names
who are assistant professors of computer science department. (10 Marks)
PART
– B
5. a. How is view created and
dropped? What problems are associated with updating views? (08 Marks)
b. How are triggers and
assertions defined in SQL? Explain. (06
Marks)
c. Explain the concept of stored
procedure in brief. (06 Marks)
6. a. Consider R = {A B C D E F};
FD’s {A → BC, C → E, CD → EF}; Show
that AD → F. (06
Marks)
b. BOOK = { book_title,
auth_name, book_type, listprice, affiliation, publication }; FD’s { book_title → book_type, publication; auth_name → affiliation; book_type → listprice}; Find the key and normalize. (08
Marks)
c. When is a set of functional dependencies
F said to be minimal? Give an algorithm for finding a minimal cover G for F. (06
Marks)
7. a. Consider R = {A B C D E F};
FD’s { AB → CD, A → CF, B → F, BGD → F, D → E, DE → F }; Find an irreducible cover for this set of
FDs. (06 Marks)
b. Explain the properties of
Relational Decomposition. (06 Marks)
c. Consider R = {A B C D E F}; FD’s
{ AB → C, B → E, A → DF };
Check whether decomposition is lossless.
(08 Marks)
8. a. What are ACID properties? Explain.
(06 Marks)
b. What is schedule? Explain with
example conflict serializable schedule. (08
Marks)
c. What is two-phase locking
protocol? How does it guarantee serializability? (06 Marks)
***********