Database Management System — CACS255 (4th Sem)

ae Tribhuvan University ON Faculty of Humanities & Social Sciences he \ Fat [Sat obit OFFICE OF THE DEAN ey 2023 Bachelor in Computer Applications Full Marks: 60 Course Title: Database Management System Pass Marks: 24 Code No: CACS255 Time: 3 hours Semester: IV Batch: 2020 Candidates are required to answer the questions in their own words as far as possible. Group B 3 Attempt any SIX questions. [6x5 = 30] 2, Define database and DBMS. Explain the benefits of DBMS in detail. [2+3] 3.—What are different SQL constraints to specify the rules for the data in the database? Explain. [5] 4. What is relational algebra? Explain different RA operations. ; [1+4] 5. How candidate key is different from primary key? Also explain foreign key in detail. [2+3] 6: What is a stored procedure? How can it be used in a database? Explain with an example. [2+3] = 7. What are the main purposes of concurrency control? Explain two phase locking protocol in detail. [1+4] 8. What is database security? Describe steps of query processing. [2+3] | Group C Attempt any TWO questions. [2x10 = 20] 9.7 What are fully and partial functional dependencies? Explain 2NF and 3NF in detail. [4+6] 10. How different join operations can be performed? Explain with proper examples. Consider a database of a shipment company. Shipped items are feceived into the system at a single retail center. Shipped items make their way to their destination via one or more standard transportation events (1.¢., flights, truck deliveries). The transportation events follow a regular schedule. Create an ER diagram to represent this information. | [5+5] 11. Consider the following tables: [10] Book(Bookid, Title, Publisher, PublishedDate, Price) Book_Authors(Bookid,Author_name) Borrower(CardNo, Name, Address, Contact) BooklIssue(BookId, CardNo, IssuedDate, DueDate Write SQL query for the following: : a) Find the name of the books taken by Ramesh Sharma. b) Find the Issued book name and the borrower’s name who have Issued date bef th 2023 and due date after 15" June, 2023. coe Ma c) Find the borrower’s name who has taken the book written by Laxmi Prashad d) Find the number of books issued on the date 25" April, 2023. See e) Find the borrower’s name who has taken the book having price more than average price.