Hey all,
I've got a book database here, and I'm trying to create a single VIEW that lists *all* of the books. Here are the
relaventparts:
Data tables: book, author, publisher, category, series, set
These tables store the appropriate data for each table; for instance, the book table stores the ISBN (if any), title,
publisher(index to publisher table), copyright year, and a primary key series.
Joining tables: bookauthor, bookcategory, bookseries, bookset
These tables are generally columns containing integers for each of the keys to join them; the bookauthor has a book key
andan author key, the bookseries has a book key, series key, and the place in the series (book 1, 2, etc).
What I am trying to do is get a single VIEW to include *all* of the books, as well as the series, place in the series,
andset *if* they have them. But, since not all books belong to a series or set, all I have been able to manage is to
getseveral VIEWs: one with all of the books in a series, one with books in a set, one with books in both series *and*
sets,and one with the others --- with some overlap (books in sets *and* series, for instance, end up in all three of
theVIEWs).
Is there a way to get all of these into a single VIEW with a single query? Or am I going to have to do several queries
fromJava or Perl and put them together (final project is in Java)?
Also, I'm trying to figure out how much should be implemented as FUNCTIONS in the database, and how much should be in
theJava/Web portion. For instance, when a book is deleted out of the book table, should a I delete all of the
referencesto that book in the database with a trigger, or do it with the Java portion?
Thanks,
Wyatt