Create the database with a name ‘LIBRARY’. (05 Marks) ii. Write the SQL statements required to create the above tables in the ‘LIBRARY’ database using mysql including appropriate data types as given below. Books BookId VARCHAR(10) NOT NULL ISBN INTEGER(30 BookAuthor VARCHAR(50 BookName VARCHAR(50) BookEdition VARCHAR(10) Branch BranchId VARCHAR(10) NOT NULL, BranchName VARCHAR(50) BranchLocation VARCHAR(30)

Problem StatementGiven a library management system with tables for publishers, library branches, books, book authors, book copies, and book lending. Write a query to retrieve details about books, including the book ID, title, publisher name, publication year, author name, number of copies, branch name, and branch address.The following table is already created, and the records are inserted into the table.Table Details:The sample records are given below. Input format :The input records are already prepopulated, as given in the problem statement.Output format :The output is a detailed dataset that includes the following information for each book as shown below:Book_id Title Publisher_Name Pub_Year Author_Name No_of_Copies Branch_Name Branch_Address1 Introduction to SQL Tech Publications 2020 John Smith 10 Main Library 123 Library St2 Python Programming Code Books 2019 David Miller 15 Downtown Branch 456 Downtown St3 Data Science Handbook Data Insights 2021 Anna White 20 Suburb Branch 789 Suburb St4 Algorithms Unlocked Algorithm House 2018 Chris Brown 8 Main Library 123 Library St5 Database Management Systems Tech Publications 2017 Emma Wilson 12 Downtown Branch 456 Downtown StRefer to the sample output for the column headers.Note :The program will be evaluated only after the “Submit Code” is clicked.Extra spaces and new line characters in the program output will result in the failure of the test case.

Archie is developing a library management system to manage books, authors, and borrowers. The system needs to handle three main types of data: BOOKS, AUTHORS, and BORROWERS.Write the DDL statements to create the following tables with the mentioned constraints to handle the data: symbol refers to the primary key NN refers to Not NULL After creating the tables, Archie needs to:Alter the BOOKS table to change the primary key to Title to ensure each book is uniquely identified by its title.Drop the primary key from the BORROWERS table as it is no longer requiredAlter the AUTHORS table to change the Nationality to NULL Note: The user must write only the query to create and alter the table. The query to display the description of the table is already given.Input format :No console inputOutput format :The output displays the successful table creation status and the structure of all three tablesRefer to the sample output.Sample test cases :Input 1 :Output 1 :Table BOOKS created.Table AUTHORS created.Table BORROWERS created.Table BOOKS altered.Table BOOKS altered.Table BORROWERS altered.Table AUTHORS altered. Name Null? Type __________________ ___________ ________________ BOOKID NOT NULL NUMBER TITLE NOT NULL VARCHAR2(200) AUTHORID NOT NULL NUMBER PUBLICATIONYEAR NOT NULL NUMBER Name Null? Type ______________ ___________ ________________ AUTHORID NOT NULL NUMBER NAME NOT NULL VARCHAR2(100) NATIONALITY VARCHAR2(100) Name Null? Type _________________ ___________ ________________ BORROWERID NOT NULL NUMBER NAME NOT NULL VARCHAR2(100) MEMBERSHIPDATE NOT NULL DATE TABLE_NAME COLUMN_NAME POSITION STATUS _____________ ______________ ___________ __________ BOOKS TITLE 1 ENABLED 1 row selected. Note :

Consider the following schema for institute library: Student (Roll No, Name, Father_ Name, Branch) Book (ISBN, Title, Author, Publisher) Issue (Roll No, ISBN, Date-of –Issue) Write the following queries in SQL and relational algebra: List roll number and name of all students of the branch ‘CSE’. Find the name of student who has issued a book published by ‘ABC’ publisher. List title of all books and their authors issued to a student ‘RAM’. List title of all books issued on or before December 1, 2020.List all books published by publisher ‘ABC’.

Design a database for a library management system. Each book in the library has multiple copies, and you want to implement afeature that allows users to check the availability of a specific book by its ISBN (International Standard Book Number).Write PL/pgSQL block using stored function. If book is available then it must return 1 else return 0.Consider relation:Books ( books_isbn,book_name) Now what you will write to check availability of book having ISBN-9780451524935

Refer to the given schema. Write a query to create customer and branch tables with the constraints mentioned.