Back to the 2024 paper
Similar questions
Database Management SystemConsider the following employee database, primary keys are underlined. Employee (ename, street, city) Works (ename, cname, salary) Company (cname, city) Manages (ename, manager-name) Write SQL queries to (i) Find the names of all the employees who work for XYZ. (ii) Find all employees who live in the same city as the company for which they work. (iii) Find all employees who live in the same cities and on the same streets as do their managers. (iv) Find all employees who earn more than the average salary of all employees of their company.202214mDATABASE MANAGEMENT SYSTEMWrite an SQL query to retrieve all employees from an "Employees" table with specific criteria (e.g., department, salary range).20247mDatabase Management SystemConsidering the following schema, create the appropriate tables and insert at least 5 records: AUTHOR (author-id, name, city, country) PUBLISHER (publisher-id, name, city, country) CATALOG (book-id, title, author-id, publisher-id, category-id, year, price) CATEGORY (category-id, description) ORDER-DETAILS (order-no, book-id, quantity) Write each of the following queries in SQL and relational algebra: (a) Obtain the names of authors who have 2 or more books in the catalog. (b) Find the author of the book which has maximum sales. (c) Obtain the names of author who have maximum number of publisher. (d) Obtain the name of the city, author, publisher where publisher and author belong to same city. (e) Obtain the title of books which has maximum sales. (f) Obtain the book-id, description for the author who have exactly 3 books in the catalog. (g) Obtain the author and publisher who have published books in more than or equal to 2 categories.201914mDatabase Management SystemConsider the following schema for institute library: Student (RollNo, Name, Father_Name, Branch) Book (ISBN, Title, Author, Publisher) Issue (RollNo, ISBN, Date-of-Issue) Write the following queries in SQL and relational algebra: (i) List roll number and name of all students of the branch 'CSE'. (ii) Find the name of student who has issued a book published by 'ABC' publisher. (iii) List title of all books and their authors issued to a student 'RAM'. (iv) List title of all books issued on or before December 1, 2020. (v) List all books published by publisher 'ABC'20247m
PreviousConsider the relation R (P, Q, S, T, X, Y, Z, W) with the following functional dependencies. PQ → X; P → YX; Q → Y; Y → ZW Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes. D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)] D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)] Identify whether it is a lossless or lossy decomposition and justify your answer.NextConsider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price), Collection (Title, Author, Catalog_no) with in the following functional dependencies: I. Title Author → Catalog_no II. Catalog_no → Title, Author, Publisher, Year III. Publisher Title Year → Price Analyze and find the highest normal form of the relation Book and Collection.