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) *****************