[This question paper contains 10 printed pages.] Sr. No. of Question Paper : Unique Paper Code Name of the Paper Name of the Course Semester Duration : 3 Hours Your Roll No.. 2291 Ic 42341202 : Database Management Systems : [Link]. Prog.) /Math. Science : 0 Maximum Marks : 75 Instru ns_for Candidates 1. Write your Roll No. on the top immediately on receipt of this question paper. 2. Question 1 is compulsory. 3. Answer any five questions out of remaining questions (Q2-Q8). 4. Answer all parts of a question together. 1. (a) What is data redundancy? What are the ; disadvantages of having redundancy within a database? 2) (b) What is meant by degree of a relationship type? (2) P.T.O.2291 2 (c) From the tables R and S, find the following : @RUS (1) di) S-R qd) Gii) Cartesian Product of R and S (2) R s Sno Dept ‘Sno Dept sl Phy s10 Maths1 S2 Psy s3 Chem S3 Chem S15 Eng s4 Jour sié Maths2 d) Given the following table and its associated ‘) 4 >) functional dependencies, (3) 7 Emp_proj Emp id [Project id/Hours ]Emp_nama | Proj_name Emp_id >Emp_name Proj_id > Project_id Emp_id,Proj_id Hours What is the highest normal form that the relation Emp_proj satisfies? Justify your answer. (e) Give an example for each of the following: (2) (i) Total participation Gi) Recursive relationship2291 3 (f) Illustrate with the help of an example an anomaly that might arise if referential integrity constraint is not satisfied in a relational schema? (2) (®) Consider the following table TABLE 1: (2) TABLE 1 x i¥ Z 2 3 4 5 6 H]eyafe}ro}a wala 2 Which of the following functional dependency constraints do not hold in the table (i) ¥Z>X Gi) X>Z Gil) X> Y (iv) Z>X TABLE1? @) What is Cardinality ratio? For the binary relationships below suggest cardinality ratios based on the meaning of the Entity ty assumptions you make. pes. State any GB) P.T.O. as: ————— —2291 4 } ENTITY 1 Cardinality Ratio ENTITY 2 STUDENT TEACHER COUNTRY CURRENT_PRESIDENT ITEM ORDER (in an order ) : (@) What enhancements distinguish the EER model from the ER model? 2) i) Identify the tables needed to store the following model. You should not introduce tables unnecessarily. (3) 2. (a) What do you mean by the following terms? (4) (i) Database catalog (ii) Meta data (b) What are the responsibilities of a database administrator? (2)7 91 (c) 5 Describe tibe the 3-schema architecture. Why do we nee appi ed mappings between schema levels? (4) A iversi regi ., . baernty registrar’s office maintains a database about the students having the following entities : (10) courses, including number, title, credits, syllabus and prerequisites; course offerings, including course number, year, semester, section number, instructor(s), timings and classroom; students, including student-id, name and program; instructors, including identification number, name, department, and title. Further, for each student’s enrollment in courses and grades awarded in each course in which the student is € @ Gi) (ii) nrolled must be appropriately modeled. Identify the entities of interest. Identify essential attributes associated with each entity with primary attributes marked. Construct an E-R diagram for the registrar’s office. State all assumptions that you make about the mapping constraints. P.T.O.2291 6 4. (a) Consider the following relational schema: (6) | Employee (eno,ename,dnum,dob Works for (eno Department (dname ;Salary,street,city) | xO} no, hrs) , dno, mgr_no) Give the following queries in relational algebra : (i) Retrieve the name and address of all employees who work for Research department (ii) Find the employee names who are either working in department no 4 and earn over 25,000 or are in department no 5 and earn over 30,000. (iii) Find the names and salary of all managers. (b) Differentiate between the following : (4) (i) single valued attributes and multivalued attributes q (ii) intension and extension 5. (a) Given below is the ER diagram which models the Car Insurance System. Map the diagram into relational schema. Specify the primary key and foreign key. (6)2291 6. ER Diagram for the Car Insurance System (b) Consider a relation R(A,B,C,D) with the following functional dependencies. (4) ABOC cD-E DE>B Find out the candidate key for the above dependencies. (a) Consider the following table : ® P.T.O.2291 8 EMP_DuPT(EId, Enana, bdate, addzenn, daumbar, dnana, mgrnan ) (eae bene pt Deeped | 06/ If the following operations are performed, check if one or more of the following constraints are violated : domain constraint, key constraint, entity integrity constraint (i) Insert a tuple (*E106’, ‘Supriya’, ‘01/01/ ' 1992’, ‘X-22, Wasant Kunj’, D5, plies) *B105’) into the EMP_DEPT table. (ii) Insert a tuple (‘E122’, ‘Rama’, ‘11/07/ 1989”, ‘MM-122, Kalu Saray’, D1, ‘Sales’, | E106’) into the EMP_DEPT table. (iii) Insert a tuple (NULL, ‘Zaheer’, ‘11/02/ 1995’, ‘AA-98, ‘Rajpur Road’sant Kunj’, D5, ‘IT’, ‘E105’) into the EMP_DEPT table (b) Using diagrams give an examples illustrating the following concepts. | specialization hierarchy and specialization lattice. | | | (5)2291 9 Dentist_no | [Link] | Appttime | Dentist | Patient No.| Patient | Surgery Name Name No. Feat eg tg Consider the above relation having the depicted functional dependencies. Apply normalization successively till 3NF. State the reasons behind each decomposition. (b) Prove that a relation schema R(A, B) with two attributes is always in BCNF. (2) 8. (a) Consider the following schema about a library system having several branches. BOOK ( BookId , Title , PublisherName ) ; BOOK_COPIES ( BookId, Branchid , NoOfCopies) LIBRARY_BRANCH ( Branchid, BranchName , Address ) BOOK_AUTHORS (BookId , AuthorName) BOOK_LOANS (BookId, BranchId,CardNo,DateIssue,DueDate) BORROWER ( CardNo, Name, phone , Address ) Write the following queries in SQL: (2x5=10) (i) List the title of books issued to ‘Ramesh’. P.T.O.“ 2291 10 (ii) Change the Publisher Name of BookId B10 to ‘BPB’. (iii) Find the maximum number of copies that a book has. (iv) How many copies of the book titled ‘Fundamentals of Accounting’ are & owned by each library branch? (v) Give the list of book titles, branch-wise. yeer (1800)