Database Management Systems - Visvesvaraya Technological University December 2012 Question

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)

***********