Database Management Systems - Visvesvaraya Technological University June July 2013 Question

Database Management Systems - Visvesvaraya Technological University June July 2013 Questions / June July 2013 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                      10CS54 Fifth Semester B.E. Degree Examination, June/July 2013 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. What are the main characteristics of the database approach over the file processing approach? (08 marks) b. Explain the different categories of data models. (06 marks) c. Explain the three-schema architecture. (06 marks) 2. a. Explain the different types of attributes that occur in ER model. Write their corresponding notations. (08 marks) b. Draw an ER diagram for an employee database. The constraints are as follows; i) An employee works for a department. ii) Every department is headed by a manager. iii) An employee works on one or more projects. iv) An employee has dependents. v) A department controls the projects.                          (12 marks) 3. a. Write the relational algebra operations to perform the following queries on the schema specified in question 2: i) Retreive the name and addresses of all employees who work for the ‘Accounts’ department. ii) Retrieve the names of all employees who have no dependents. iii) Find the names of employees who work on all projects controlled by department number 2.             (12 marks) b. Explain the relational algebra operations from set theory, with examples. (08 marks) 4. a. Explain the different constraints that can be applied during table creation in SQL with a suitable example.          (08 marks) b. Write the SQL queries for the following database schema; (12 marks) STUDENT (USN, NAME, BRANCH, PERCENTAGE) FACULTY (FID, FNAME, DEPARTMENT, DESIGNATION, SALARY) COURSE (CID, CNAME, FID) ENROLL (CID, USN, GRADE) i) Retrieve the names of all students enrolled for the course ‘CS-54’ ii) List all the departments having an average salary of the faculties above Rs. 10,000. iii) List the names of the students enrolled for the course ‘CS-51’ and having ‘B’ grade. PART – B 5. a. Define views. Give an example to create a view. (08 marks) b. Explain the different approaches for database programming. Explain the problem that arises in some of the approaches. (12 marks) 6. a. List the inference rules for functional dependencies. Write the algorithm to determine the closure of X (set of attributes) under F (set of functional dependencies). (08 marks) b. Define the 1NF, 2NF, and 3NF with a suitable example for each. (12 marks) 7. a. Write the algorithm for testing non-additive join property. (10 marks) b. Explain the 4NF with suitable example. (10 marks) 8. a. Explain the ACID properties of a database transaction. (04 marks) b. Briefly explain the two phase locking protocol used in concurrency control. (08 marks) c. Explain the three phases of ARIES recovery model. (08 marks) *****************

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)

***********

Database Management Systems - Visvesvaraya Technological University May/June 2010 Question

Database Management Systems - Visvesvaraya Technological University May/June 2010 Questions / May-June 2010 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                      06CS54

Fifth Semester B.E. Degree Examination, May/June 2010

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. Briefly discuss the advantages of using the DBMS. (10 Marks)

b. Explain the component modules of DBMS and their interaction with the help of a diagram. (10 Marks)

2. a. Define an entity and an attribute. Explain the different types of attributes that occur in an ER model, with an example. (10 Marks)

b. Define the following with an example:

        (i) Weak entity type

        (ii) Participation constraints

        (iii) Cardinality ratio

        (iv) Ternary relationship

        (v) Recursive relationship (10 Marks)

3. a. Discuss the characteristics of a relation with examples. (08 Marks)

b. Briefly discuss different types of update operations on relational database. Show an example of a violation of the referential integrity in each of the update operation. (09 Marks)

c. What is valid state and an invalid state with respect to a database? (03 Marks)

4. a. Consider the following two tables T1 and T2. Show the results of the following operations:

        (i) T1 ⋈_{T1.P = T2.A} T2

        (ii) T1 ⋈_{T1.Q = T2.B} T2

        (iii) T1 ⟕_{T1.P = T2.A} T2

        (iv) T1 ⋈_{T1.P = T2.A AND T1.R = T2.C} T2

        (v) T1 U T2

(Assume T1 and T2 are union compatible). (10 Marks)

Table T1					Table T2
P	Q	R			A	B	C
10	a	5			10	b	6
15	b	8			25	c	3
25	a	6			10	b	5

b) Explain with an example, the basic constraints that can be specified, when you create a table in SQL. (10 Marks)

PART – B

5. a. Explain the syntax of a SELECT statement in SQL. Write the SQL query for the following relational algebra expression. (06 Marks)

Π_{bdate, address}(σ_{fname=’john’ ᴧ minit=’B’ ᴧ lname=’smith’}(Employee))

b. Explain DROP command with an example. (04 Marks)

c. Consider the following tables:

WORKS (Pname, cname, salary)

LIVES (Pname, street, city)

LOCATED_IN (Cname, city)

MANAGER (Pname, mgrname)

Write the SQL query for the following:

        (i) Find the names of all persons who live in the city ‘Mumbai’.

        (ii) Retrieve the names of all person of ‘Infosys’ whose salary is between Rs. 30,000 and Rs. 50,000.

        (iii) Find the names of all persons who live and work in the same city.

        (iv) List the names of the people who work for ‘Wipro’ along with the cities they live in.

        (v) Find the average salary of all persons of ‘Infosys’. (10 Marks)

6. a. What is a functional dependency? Write an algorithm to find a minimal cover for a set of functional dependencies. (10 Marks)

b. What is the need for normalization? Explain second normal form. Consider the relation EMP-PROJ = {ssn, pnumber, hours, ename, pname, plocation}. Assume {ssn, pnumber} as primary key. The dependencies are;

ssn pnumber → hours

ssn → ename

pnumber → pname, plocation.

Normalize the above relation into 2NF.  (10 Marks)

7. a. Explain multi-valued dependency and fourth normal form with an example. (10 Marks)

b. Let R = {ssn, pnumber, hours, ename, pname, plocation} and D = {R1, R2, R3}, where;

R1 = EMP = {ssn, ename}

R2 = PROJ = {pnumber, pname, plocation}

R3 = WORK_ON = {ssn, pnumber, hours}

The following functional dependencies hold on relation R.

F = {ssn → ename; pnumber → {pname, plocation}; {ssn, pnumber} → hours}

Prove that the above decomposition of relation R has the lossless join property. (10 Marks)

8. a. Explain the problems that can occur when concurrent transactions are executed. Give examples. (10 Marks)

b. Briefly discuss the Two phase locking protocol used in concurrency control. (10 Marks)

***********