Question

NEED DONE ASAP POSTED 3 TIMES HIGHKEY

Suppose you have the task of defining and setting up a relational database of books and authors. Some attributes (columns) of possible interest include:  last name of author,  first name, authorID, book title, book ISBN number, book category, publication date, and perhaps others. In an early (basic) version of the system, we’ll assume thateach book can have only one author, but each author can write many books.   

a) if an index is not set up, briefly describe what the system or program would need to do(in terms of search strategy, query plan, For Loops, scanning methods, etc.) to find the desired author(s) on the basis of Author criteria provided by the user.  Assume that no information from the Book table will be used to find the desired Author.

b) Suppose you eventually decide that the database also needs to be able to handle the case where there can be multiple authors for one book.  How would you handle the general case in which there can be several authors per book and also many books per author? Give a brief answer in your own words.

Answer 1

First table of database needs to be defined which can be created as follows:

Attributes given:

last name of author,  first name, authorID, book title, book ISBN number, book category, publication date

Based on this we can have following table:

Authors: AuthorID, LName, Fname

Books: BookID, ISBNNumber, BookTitle, CategoryID, PublicationDate

BooksCategory(CategoryID, BookCategory)

As only one Author writes the book we can have AuthorID in Book table as follows:

Books: BookID,AuthorID, ISBNNumber, BookTitle, CategoryID, PublicationDate

So final tables are

Authors: AuthorID, LName, Fname

Create table Authors

(AuthorID varchar(8),

LName varchar(30),

Fname varchar(30))

Books: BookID,AuthorID, ISBNNumber, BookTitle, CategoryID, PublicationDate

Create table Books

(BookID varchar(8),

AuthorID varchar(8),

ISBNNumber varchar(13),

BookTitle varchar(40),

CategoryID varchar(8),

PublicationDate Date)

BooksCategory(CategoryID, BookCategory)

Create table Books

(CategoryID varchar(8),

BookCategory varchar(20))

1. Parameterized queries can be used to get user input and display author details as follows:

SELECT * FROM [Authors] where AuthorID= & AuthorID;

SELECT * FROM [Authors] where LName =& LName;

SELECT * FROM [Authors] where FName =& FName;

2. If there are multiple authors of one book we can change the database as follows:

Authors: AuthorID, LName, Fname

Books: BookID, ISBNNumber, BookTitle, BookCategory, PublicationDate

AuthorBooks(AuthorID, BookID)

BooksCategory(CategoryID, BookCategory)

Sample data of these tables can be

Authors
AuthorID	LName	Fname
1	Charles	Emmanuel
2	Simon	Nancy
3	Miller	Harry
4	Wills	Smith

Books
BookID	ISBNNumber	BookTitle	CategoryID	PublicationDate
B01	11112222	Winds	C02	12-12-2014
B02	33311233	Lovely poems	C01	10-10-2013
B03	2231133	Essays of World	C01	10-09-2011

BooksCategory
CategoryID	BookCategory
C01	Science
C02	English Literature

AuthorBooks
AuthorID	BookID
A01	B01
A03	B01
A02	B02
A04	B02
A02	B03

Which is handling a case where there can be several authors per book and also many books per author