kevin.md

Q1. Overall structure of a Library Database is given below

Create(5) those tables and insert(5) 5 rows in each table.
Represent the primary key(5) of different table and also foreign key(5) relationship.
Write(5) a query to display the name of the book and the name of the author of the book.

Tables:
    Author ( authorname, citizenship, birthyear)
    Book(isbn, title, authorname)
    Topic(isbn, subject) Branch(libname, city)
    Instock(isbn, libname, quantity)

A1. Ans:

  CREATE TABLE Author ( authorname varchar(20), citizenship varchar(20), birthyear int);
  CREATE TABLE Book(isbn varchar(20), title varchar(20), authorname varchar(20));
  CREATE TABLE Topic(isbn varchar(20), subject varchar(20));
  CREATE TABLE Branch(libname varchar(20), city varchar(20));
  CREATE TABLE Instock(isbn varchar(20), libname varchar(20), quantity int);

  INSERT INTO Author VALUES ('Author1', 'USA', 1990);
  INSERT INTO Author VALUES ('Author2', 'USA', 1991);
  INSERT INTO Author VALUES ('Author3', 'USA', 1992);
  INSERT INTO Author VALUES ('Author4', 'USA', 1993);
  INSERT INTO Author VALUES ('Author5', 'USA', 1994);    

  INSERT INTO Book VALUES ('ISBN2001', 'Book1', 'Author1');
  INSERT INTO Book VALUES ('ISBN2002', 'Book2', 'Author2');
  INSERT INTO Book VALUES ('ISBN2003', 'Book3', 'Author3');
  INSERT INTO Book VALUES ('ISBN2004', 'Book4', 'Author4');
  INSERT INTO Book VALUES ('ISBN2005', 'Book5', 'Author5');

  INSERT INTO Topic VALUES ('ISBN2001', 'Subject1');
  INSERT INTO Topic VALUES ('ISBN2002', 'Subject2');
  INSERT INTO Topic VALUES ('ISBN2003', 'Subject3');
  INSERT INTO Topic VALUES ('ISBN2004', 'Subject4');
  INSERT INTO Topic VALUES ('ISBN2005', 'Subject5');

  INSERT INTO Branch VALUES ('Lib1', 'City1');
  INSERT INTO Branch VALUES ('Lib2', 'City2');
  INSERT INTO Branch VALUES ('Lib3', 'City3');
  INSERT INTO Branch VALUES ('Lib4', 'City4');
  INSERT INTO Branch VALUES ('Lib5', 'City5');

  INSERT INTO Instock VALUES ('ISBN2001', 'Lib1', 1);
  INSERT INTO Instock VALUES ('ISBN2002', 'Lib2', 2);
  INSERT INTO Instock VALUES ('ISBN2003', 'Lib3', 3);
  INSERT INTO Instock VALUES ('ISBN2004', 'Lib4', 4);
  INSERT INTO Instock VALUES ('ISBN2005', 'Lib5', 5);

  ALTER TABLE Book ADD CONSTRAINT pk_book PRIMARY KEY (isbn);
  ALTER TABLE Author ADD CONSTRAINT pk_author PRIMARY KEY (authorname);
  ALTER TABLE Topic ADD CONSTRAINT pk_topic PRIMARY KEY (isbn, subject);
  ALTER TABLE Branch ADD CONSTRAINT pk_branch PRIMARY KEY (libname);
  ALTER TABLE Instock ADD CONSTRAINT pk_instock PRIMARY KEY (isbn, libname);

  ALTER TABLE Book ADD CONSTRAINT fk_book_author FOREIGN KEY (authorname) REFERENCES Author(authorname);
  ALTER TABLE Topic ADD CONSTRAINT fk_topic_book FOREIGN KEY (isbn) REFERENCES Book(isbn);
  ALTER TABLE Instock ADD CONSTRAINT fk_instock_book FOREIGN KEY (isbn) REFERENCES Book(isbn);
  ALTER TABLE Instock ADD CONSTRAINT fk_instock_branch FOREIGN KEY (libname) REFERENCES Branch(libname);

  SELECT Book.title, Author.authorname FROM Book, Author WHERE Book.authorname = Author.authorname;

Output

  Book1 Author1
  Book2 Author2
  Book3 Author3
  Book4 Author4
  Book5 Author5

Q2

1. Create a view such that by providing the Book ISBN, anyone can know the details of the author(all info available). (5 marks)

   • Ans:

   CREATE VIEW author_details AS SELECT * FROM Author, Book WHERE Author.authorname = Book.authorname;

   Output

       Author1 USA 1990 ISBN2001 Book1 Author1
       Author2 USA 1991 ISBN2002 Book2 Author2
       Author3 USA 1992 ISBN2003 Book3 Author3
       Author4 USA 1993 ISBN2004 Book4 Author4
       Author5 USA 1994 ISBN2005 Book5 Author5
   

2. From the previous view(created in Q1), show the author details where the book ISBN is "ISBN2001". (5 marks)

   • Ans:

   SELECT * FROM author_details WHERE isbn = 'ISBN2001';

   Output

   Author1 USA 1990 ISBN2001 Book1 Author1