i haven't taken sql yet and i'm having some trouble getting some concepts straight. i have a tables with foreign keys that emulate a library(book, member,book classification). i can populate the database fairly easy; my problem is this- what if a member has more than one book? is there a query that allows this since the bookID is a foreign key in the member table? should (or can i) insert the bookID(s)-plural- into the member table at creation time? lastly and most importantly can a record hold more than one foreign key in the same field? i wouldn't ask but all the sources i've read by googling do not go in depth in theory. the following code works; it is here to provide the database structure. any help, especially with the concept questions will be greatly appreciated.
Imagination is more important than knowledge "Albert Einstein"
Have you not been given a database already set up? The hard part about writing SQL is ensuring you don't have any many-to-many relations, nor do you duplicate lines, so you don't insert lots of copies of a borrower if he borrows lots of books.
You should get the database set up before trying to access it with Java code. I would suggest something like this for a simple library; it is not exactly the same as yours. Pk means primary key and FK means foreign key, with the table it refers to. It is worthwhile finding out about things like NOT NULL and ON DELETE CASCADE, but those are refinements for the database.You can have several copies of the same bookYou can have several books borrowed by the same person; you can prevent several people having the same copy at the same time by consolidating Borrowing and Copy_of_Book or by judicious use of the UNIQUE keywordTo find which books somebody has borrowed, try something like this (there are more elegant versions available):
SELECT name, title FROM Borrower, Book WHERE Borrower.Borrower_number = Borrowing.Borrower_number AND Borrowing.Library_number = Copy_of_Book.Library_number AND Copy_of_Book.ISBN = Book.ISBN;
f. nikita thomas
Joined: Mar 02, 2008
Have you not been given a database already set up?
nope. i am forced to use the set up that my tutee's instructor has given them<no database, write schema?,code java>. the following is directly from their assignment :
this essentially setups a one to many relationship(member to # of book(s))... i think, and this is where i'm getting frustrated. the last clue that he gave them is :
For the java classes there are two interesting relationships between objects that I want to see if you understand. One is the relationship between a library member and the books that member has checked out. The other is the relationship between a book and it's book classification.
i wouldn't mind this so much if i had already taken sql and these weren't cobol programmers for the state . so again, how do i approach the instance of a member with multiple books? does it have something to do with the foreign key? should the Books field in member be a foreign key? btw, considering my knowledge of sql(none), i'm surprised that i've gotten this far in so short a time.
Does Availability have to be boolean type or you can choose another? You could maybe use it as a reference to a member who has borrowed the book, if null or 0 the book is available? And you could use 'Books' from the Member Table as a history containing books borrowed by that member?
Then when you want to know which books are borrowed by that member you would search Books table for his ID (Availability).
Joined: Oct 13, 2005
In fact the presence of an "available" flag means the tables are being "de-normalised"; you can derive "available" from there not being a link to a borrower.
Your "id" corresponds to my "Library_number", and your classification presumably refers to something like the Dewey system.
INSERT INTO classification (dewey_number, type) VALUES (823, 'English Fiction');
INSERT INTO book(title, author, copyright_year, ISBN, classification) VALUES ('A Tale of Two Cities', 'Charles Dickens', 0140430547, 823);
Then, if you have the id set up with auto-increment, . . .
INSERT INTO Copy_of_book (ISBN) VALUES (0140430547);
INSERT INTO Copy_of_book (ISBN) VALUES (0140430547); . . .
will tell the system you have bought two copies of A Tale of Two Cities.
Joined: Oct 13, 2005
Any luck with it? I presume you noticed I missed out the copyright date by mistake in my posting yesterday.