SATHYABAMA UNIVERSITY
(Established under section 3 of UGC Act,1956)
Course & Branch :B.E - ECE
Title of the Paper :Data Base Management Systems Max. Marks:80
Sub. Code :613601(2006-07-08-09) Time : 3 Hours
Date :28/04/2012 Session :FN
______
PART - A (10 x 2 = 20)
Answer ALL the Questions
1. Define database management system.
2. What are the main differences in using a file processing system and a DBMS?
3. Distinguish between primary key and foreign key.
4. Consider the following relation:
EMP(Eno, Name, Date_of_Birth, Sex, Date_Of_Joining, Basic_Pay, Dept). Develop an SQL query that will find and display the Dept and Average Basic_Pay in each Dept.
5. List the fundamental operations in the relational algebra.
6. How a query in the tuple relational calculus is expressed?
7. Mention the difference between 2NF and 3NF.
8. Define Boyce-Codd normal form.
9. What is the salient feature of using hierarchical model for data modeling?
10. What is meant by object identifiers?
PART – B (5 x 12 = 60)
Answer ALL the Questions
11. Explain in detail on the functions of a database administrator and the different types of database users.
(or)
12. Design an E-R diagram for a hospital with a set of patients and doctors specialized in different disciplines. Associate each patient with the specialized doctors and maintain for each partients a log of various tests conducted.
13. With appropriate example discuss the basic operations in relational algebra.
(or)
14. Consider the following relations for a company database application:
Employee(Eno, Name, Sex, DOB, Doj, Designation, Basic_Pay, Dept_No)
Department (Dept No, Name)
Project (Proj No, Name, Dept_No)
Worksfor (Eno, Proj No, Date, Hours)
The attributes specified for each relation is self-explanatory. However the business rules are stated as follows. A department can control any number of projects. But only one department can control a project. An employee can work on any number of projects on a day. However an employee cannot work more than once on a project he/she worked on that day. The primary keys are underlined.
(a) Identify the foreign keys. Develop DDL to implement the above schema. (4)
(b) Develop an SQL query to list the department number and the number of employees in each department. (2)
(c) Develop a view that will keep track of the department number, the number of employees in the department, and the total basic pay expenditure for each department. (3)
(d) Develop an SQL query to list the details of employees who have worked in more than three projects on a day. (3)
15. Write the formal definition for the domain relational calculus and explain with example queries.
(or)
16. Consider the following relational schema for a library:
Member(memb_no, name, dob)
Books(isbn, title, authors, publisher)
Borrowed(memb_no, isbn, date)
Write the following questions in relational algebra.
(a) Find the names of members who have borrowed any book published by “McGraw-Hill”. (2)
(b) Find the name of members who have borrowed all books published by “McGraw-Hill”. (2)
(c) Find the name and membership number of members who have borrowed more than five books published by “McGraw_Hill”.(2)
(d) For each publisher, find the name and membership number of members who have borrowed more than five books of that publisher. (3)
(e) Find the average number of books borrowed per member. Take into account that if an member does not borrow any books, then that members does not appear in the borrowed relation at all. (3)
17. (a) Define and give example to explain 3NF.
(b) What are the desirable properties of decomposition?
(or)
18. Consider the universal relation R = {A,B,C,D,E,F,G,H,I} and the set of functional dependencied F = {{A,B} - > {C}, {A} - > {D,E}, {B} -> {F},{F} -> {G,H}, {D} -> {I,J}}. What is the key for R? Decompose R into 2NF and then 3NF relations.
19. Explain in detail about object relational databases.
(or)
20. Perform a comparative study between the hierarchical data model and network data model.