Enrolment No. 230073 ADANI UNIVERSITY Faculty of Engineering Sciences and Technology B. TECH ~ IIl- SEMESTER ~ MID SEMESTER EXAMINATION ~OCTOBER 2024 Department of CSE (AI-ML) and ICT Subject Code: ECSC124202 Date: 21/10/2024 Subject Name: Database Management System Total Marks: 30 ‘Time: 3:30PM TO 5:00PM Instructions: 1. Attempt all questions. 2. Make suitable assumptions wherever necessary. 3. Figures to the right indicate full marks. Qi A) Answer True or False. [3] (6) ¥ 1. Ina relational database, a foreign key can reference multiple primary keys. F 2. Triggers can only be executed before an event occurs in the database. P 3. SQL views cannot contain aggregated data like SUM or AVG. * B) What is the purpose of the SQL GROUP BY clause? [1] a. To filter rows based on a specified condition b. To sort the result set in ascending or descending order c. To aggregate data based on one or more columns 4. To join multiple tables in a query C) Ina database, what is the purpose of a trigger? [1] a. To automate backups b. To automatically execute a specified function in response to certain events ¢. To roll back transactions 4. To define default values for table column BRR ge of join zeturas all rows when theres a match tn ctiner tabletiat or right)? (1] a, INNER JOIN b. FULL OUTER JOIN c, LEFT JOIN d. CROSS JOIN #Q2(A) What is DDL? Explain it with any two commands. re} \ Q.2(B) What is a weak ent with diagram. (4) e Example and explai OR Q2(B) Explain all types of attributes. Explain with diagrar {4 >Q.3(A) List and explain different tasks/roles/functions/duties of DBA (Database [3] Administrator). 4Q3(B) Write a relational algebra expressions for the given tables. 1} 1) passenger (p_id, p_name, p_gender, p_city); 2) agency (a_id, a_name, a_city); 3) flight (f id, £ ime, sre, dest); 4) booking (p_id, a_id, f id, f date) 1) Find only the flight numbers for passengers with p_id 123 for flights to Chennai before 06/11/2020] Find the agency nymes for agencies who do not have any bookings for passenger with id 1233 Find the details of all male passengers who are associated with Jet agency OR ©Q.3(A) List and explain different categories/types of database users. 13} Q.3(B) Write a relational algebra expressions for the given tables. 13)) Q4(A) /Q4(B) Q.4(a) Q.4(B) 7 Q5(A) 4 Q5(B) 50a) 05 (B) 1) Author (author id, first name, last name); 2) author pub (author id, pub id, author position); 3) book (book id, book title, month, year, editor); 4) pub (pub id, title, book id) 1) Write a relational algebra expression that returns the names of all authors who are not book editors. 2) List all the authors (first_name, last_name) who have worked on publications in the year 2022. 3) Get the list of book titles of author name “Ravindranath Tagore”. Refer to the Customer-product schema given below and write sql query. Customers (customer_id, first_name, last_name, city, country); Orders (order _id, order_date, customer id, total_amount); Order_items (order_item_id, order_id, product id, quantity, price); Products (product id, product_name, category, unit_price) 1) Retrieve the total number of orders and the total revenue for each product and. display the products which generate the second and third highest revenue only. Convert E-R diagram to schema. Explain all the steps. @Q Gm GD Ge OR Refer to the library schema given below and write sql query. BOOKS (book_id (INT, PK), title (VARCHAR), genre (VARCHAR), publication_year (YEAR), author_id (INT, FK)); Authors (author_id (INT, PK), first_name (VARCHAR), last_name (VARCHAR), birth_year (YEAR)); Members. (member-id (INT, PK), firstname (VARCHAR), last_name (VARCHAR), membership date (DATE); Loans (loan id (INT, PK), book id (INT, FK), member_id (INT, FK), loan_date (DATE), return_date (DATE)) 1) Find the most recent loan date for each member and the title of the book they borrowed. Conyert E-R diagram to schema. Explain all the steps. 7 Refer Library schema form Q.4 (A) and write simple Procedure to Retrieve Books Loaned by a Member. Write difference between CTE and VIEW. OR Refer Customer-product schema from Q.4 (A) and write simple Procedure to Calculate Total Spending for a Customer. Explain Trigger and explain steps of implementing trigger after INSERT operation. 2] [4] (2 [4] 1 {4 PR]