Database Management Systems - Visvesvaraya Technological University December 2014 January 2015 Question

Database Management Systems - Visvesvaraya Technological University December 2014 January 2015 Questions / December 2014 January 2015 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                          10CS54 Fifth Semester B.E. Degree Examination, Dec. 2014/Jan. 2015 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. Explain the typical component module of a DBMS, with a neat diagram. (10 marks) b. Define the following with examples; (10 marks) i) Value set ii) Complex attribute iii) Data model iv) Schema construct v) Metadata. 2. a. What are the structural constraints on a relation type? Explain with examples. (05 marks) b. What is a weak entity type? Explain the role of partial key in design of weak entity type. (05 marks) c. Draw an ER diagram for mail order database considering the following requirements. Here employee takes order for parts from customers. (10 marks) i) The mail order company has employees each identified by a unique employee ID, first and last name, address, gender, zip code. ii) Each customer of the company is identified by a unique customer ID, first and last name, address, location, and zip code. iii) Each part sold by the company is identified by a unique part number, part name, price and quantity in stock. iv) Each order placed by a customer taken by an employee and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a date of receipt as well as an expected ship date. The actual ship date is also recorded. v) Each customer can place number of orders and each order placed by one customer only. vi) Each employee can take any number of orders but each order belongs to only one employee. vii) Each part placed by number of customers and each customer can place order for number of parts. viii) Write assumptions made. 3. a. Discuss the entity integrity and referential integrity constraints. Why each is considered important? (05 marks) b. Discuss the various types of JOIN operations. Why is theta join required? (05 marks) c. Given the schema; STUDENT (USN, NAME, BRANCH, PERCENTAGE) FACULTY (FID, FNAME, DEPT, DESIGNATION, SALARY) COURSE (CID, CNAME, FID) ENROLL (CID, USN, GRADE) Give the relational algebra expressions for the following; i) Retrieve the name and percentage of all students for the course 10CS54. ii) List the departments having an average salary of the faculties above Rs. 30,000. iii) List the name of the course having students grade ‘A’ maximum. (10 marks) 4. a. Explain the different constraints that can be applied during table creation in SQL, with an example.  (08 marks) b. Write the SQL queries for the following database schema; Works (pname, cname, salary) Lives (pname, street, city) Located_in (cname, city) Manager (pname, mgrname) i) Find the names of all persons who live in the city ‘Bangalore’. ii) Retrieve the names of all persons of ‘Infosys’ whose salary is between Rs. 50,000 and Rs. 90,000. iii) Find the names of all persons who live and work in same city. iv) List the names of the people who work for ‘Tech M’ along with the cities they live in. v) Find the average salary of ‘Infosys’ persons. (12 Marks) PART – B 5. a. Explain the syntax of SELECT statement in SQL. (04 marks) b. How is view created and dropped? What problems are associated with updating views? (06 Marks) c. Explain the following; i) Embedded SQL ii) Database stored procedure. (10 Marks) 6. a. What is functional dependency and who specifies the functional dependency that holds among the attributes of a relation schema? (05 Marks) b. Consider R = {A, B, C, D, E, F}. FDs are {A → BC, C → E, CD → EF}. Show that AD → F. (05 Marks) c. Find the key and normalize. (10 marks) Book_title | Auth_name | Book_type | List_price | Affiliation | Publication. FDs are {Book_title → Book_type, Publication,                    Auth_name → Affiliation,                    Book_type → List_price} 7. a. Which normal form is based on the concept of multi-valued functional dependency? Explain the same with example. (10 Marks) b. Given a relation R with 4 attributes R = (A, B, C, D) and following FDs. Identify the candidate key for R and highest normal form. i) C → D, C → A, B → C                  ii) B → C, D → A. (10 Marks) 8. Write short notes on the following; a. Two phase locking protocol. b. Transaction support in SQL. c. Time stamp ordering algorithms. d. ACID properties.                          (20 marks) *****************

Database Management Systems - Visvesvaraya Technological University June July 2014 Question

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

USN                                                      10CS54 Fifth Semester B.E. Degree Examination, June/July 2014 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 is database? Explain the implicit properties of database. (08 marks) b. With neat diagram, explain “three schema architecture”. (08 marks) c. Define the following terms: (04 marks) i) Data model       ii) Schema   iii) Metadata                  iv) Snapshot 2. a. Explain with sketch the different phases of database design. (10 marks) b. Draw an ER diagram of hospital management system. Assume your own entities (minimum 4), attributes and relations. (10 Marks) 3. a. What is constraint? Give the detailed explanation of key constraints.         (10 marks) b. Consider the following schema and write the relational algebra expressions for the queries given below; Suppliers (sid : integer, sname : string, address : string) Parts (pid : integer, pname : string, color : string) Catalog (sid : integer, pid : integer, cost : real) i) Find the names of suppliers who supply some red parts. ii) Find the sids of suppliers who supply some red parts or at 221 packer street. iii) Find the sids of suppliers who supply some red part and some green part. (10 marks) 4. a. Consider the data given in question 3(b) and write the following queries in SQL: i) Find the sids of suppliers who supply some red and some green parts. ii) Find the pairs of sids such that the supplier with first sid charges more for some part than the supplier with the second sid. iii) Find the pids of parts supplied by at least two different suppliers.      (10 marks) b. Write a note on NULL and three valued logic. (10 marks) PART – B 5. a. Explain insert, delete, and update statements in SQL, with example. (09 marks) b. How is view created and dropped? What problems are associated with updating of views? (11 marks) 6. a. State the informal guidelines for relational schema design. Illustrate how violation fo these guidelines may be harmful. (12 marks) b. What is normalization? Explain third normal form with example. (08 marks) 7. a. Define multi-valued dependency. Explain 4NF with example. (10 marks) b. Let R = {ssn, ename, pnumber, pname, plocation, hours} and D = {R1, R2, R3} where R1 = EMP = {ssn, ename} R2 = PROJ = {pnumber, pname, plocation} R3 = WORKS_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. Write a short note on: a. Two phase locking protocol. b. Transaction support in SQL. c. Write ahead log protocol. d. Time stamp ordering algorithm.                       (20 marks) *****************

Database Management Systems - Visvesvaraya Technological University December 2013 January 2014 Question

Database Management Systems - Visvesvaraya Technological University December 2013 January 2014 Questions / December 2013 January 2014 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                          10CS54 Fifth Semester B.E. Degree Examination, Dec. 2013/Jan. 2014 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 responsibilities of DBA and Database designers? (04 marks) b. With a neat diagram, explain the component modules of DBMS and their interactions. (08 marks) c. List the advantages and disadvantages of DBMS. Discuss any five advantages by comparing with files systems. (08 marks) 2. a. Define the following terms: i) Recursive relationship ii) Weak entity type iii) Atomic attributes iv) Participation role. (04 marks) b. Discuss the conventions for displaying an ER schema as an ER diagram. (04 marks) c. Draw an ER diagram for Musicians who perform for album. Assume any four entities. Indicate all key and cardinality constraints and any assumptions that are made. (12 marks) 3. a. List and explain characteristics of relations. (06 marks) b. List set theory operations used in relational data model. Explain any two with example. (06 marks) c. Consider the following relations for a sailor database that keeps track of reservation of boats by sailors. SAILORS (SID, SNAME, RATING, AGE) BOATS (BID, BNAME, COLOR) RESERVES (SID, BID, DAY) Specify the following queries in relational algebra. i) Find the SIDs of sailors with age over 20 who have not reserved a ‘Red’ boat. ii) Find the names of sailors who have reserved all boats. iii) Find the names of sailors who have reserved boat 103.     (08 marks) 4. a. Describe the six clauses in the syntax of an SQL retrieval query. Show what type of constructs can be specified in each of the six clauses. Which of the six clauses are required and which are optional.      (06 marks) b. Explain how the GROUP BY clause works. What is the difference between WHERE and HAVING clause. (04 marks) c. Consider the following relations of a database; Supplier (Sno, Sname, Status, City) Product (Pno, Pname, Color, Weight, City) Shipments (Sno, Pno, Qty) Specify the following queries in SQL. i) Retrieve names of supplier who supply part P2. ii) Retrieve the names of suppliers who do not supply any part supplied by S2. iii) Retrieve parts number for all parts supplied by more than one supplier. iv) For each part supplied, get the part number, maximum quantity, minimum quantity supplied for that part. v) Retrieve supplier numbers for suppliers with status less than the current maximum in the supplier table. (10 Marks) PART – B 5. a. List the differences between Independent nested and co-related nested query. (04 marks) b. Discuss main approaches to database programming. What you mean by Impedance mismatch. (08 Marks) c. With program segment, explain retrieving of tuples with embedded SQL. (08 Marks) 6. a. Discuss insertion, deletion and modification anomalies. Why are they considered bad? Illustrate with examples. (08 Marks) b. What you mean by closure of attribute? Write an algorithm to find closure of attribute. (06 Marks) c. Given below are two sets of FDs for a relation R(A, B, C, D, E).  Are they equivalent? i) A → B , AB → C, D → AC, D → E                ii) A → BC, D → AE    (06 Marks) 7. a. Consider the following Universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies. F = {{A, B} → C, A → {D, E}, B → F, F → {G, H}, D → {I, J}}. What is the key of R? Decompose R into 2NF, then 3NF relations. (08 Marks) b. What is the dependency preservation property for a decomposition? Why is it important? (06 Marks) c. Define fourth normal form. When is it violated? Why is it useful? (06 Marks) 8. a. What are the anomalies occur due to interleave execution? Explain them with example. (08 Marks) b. Consider the three transaction T1, T2, and T3 and schedules S1 and S2 given below. Determine whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule (S).                         (08 marks) T1: R1(X); R1(Z); W1(X); T2: R2(Z); R2(Y); W2(Z); W2(Y); T3: R3(X); R3(Y); W3(Y); S1: R1(X); R2(Z); R1(Z); R3(X); R3(Y); W1(X); W3(Y); R2(Y); W2(Z); W2(Y); S2: R1(X); R2(Z); R3(X); R1(Z); R2(Y); R3(Y); W1(X); W2(Z); W3(Y); W2(Y); c. Describe the three steps in crash recovery in ARIES. What is the goal of each phase? (04 marks) *****************