[RESOLVED] Help with database normalization...

[riechan]

The other thing is that the Book table doesn't seem to distinguish between different copies of the same book. I'm assuming CopyQty is the number of copies you have of that book (so one record for all copies of a book), but it may be what I would recommend instead, which is a "copy number" (so one record per copy). That way you can keep track of who has which copy, and which ones are still in stock. However, this isn't something that can be done purely from your side of things, there also needs to be some way of telling the copy number from the physical book itself.

I somewhat thought of that as I was working with the database earlier today, and you're right. The table above can't monitor the copies borrowed. Anyways, I'll take due note of that once I revise the dbase structure.

Also on that, I would actually use the ISBN as the book's PK (actually, to extend off of si's post, the ISBN and the CopyNumber should be a compound PKey) rather than the CallNumber.... having worked in libraries, I've seen Call Number collisions, as well as books that have been reclassified or moved for one reason or another. I also don't see how the subClass table works... since the books link to the Class table. Other than that, seems pretty reasonable.

I think ISBNs are different for each and every book (eg: SQL Tutorial 2nd Edition, Learn to do stuff, etc.). But, are all ISBNs different per instance of a book, like if I had 2 copies of SQL Tutorial 2nd edition, will they each have a different ISBN? I'm sort of thinking to make 2 PKs in the Book table now, like taking your suggestion as part of it, it would be...

BookNum(PK), ISBN(PK)

so as to differentiate each instance of a book title. Will this work? And with the Subclass table, I was wondering if it's okay to omit it? I think the Class table itself would suffice (eg: Psychology, Mathematics, etc.)

Oh yeah, please take a look at this code (have not included your suggestions as of now, as I have just read it) and the relationship table, and kindly point out anymore errors I may have done. Thanks again, guys!

create table Class (
 ClassNum int not null,
 ClassName varchar(30) not null,
 ClassDescription varchar(40),
 CONSTRAINT ClassPK PRIMARY KEY(ClassNum)
)
--Line7
create table Patron (
 PatronID varchar(10) not null,
 PatronFname varchar(40) not null,
 PatronLname varchar(40) not null,
 CONSTRAINT PatronPK PRIMARY KEY(PatronID)
)
--Line14
create table Publisher (
 PublisherID varchar(20) not null,
 PublisherName varchar(30) not null,
 ContactPerson varchar(40) not null,
 Street varchar(30),
 City varchar(20),
 ZipCode varchar(10),
 ContactNumber varchar(15) not null,
 ContactEmail varchar(30),
 CONSTRAINT PublisherPK PRIMARY KEY(PublisherID),
)
--Line26
create table Book (
 BookID int default 1 not null,
 CallNum varchar(20) not null, 
 BookTitle varchar(50) not null, 
 ISBN varchar(25) not null, 
 Description varchar(60), 
 AuthorFname varchar(40) not null,
 AuthorLname varchar(40) not null,
 Subject varchar(30), 
 CopyQty int not null default 1, 
 ClassNum int not null,
 PublisherID varchar(20) not null,
 CONSTRAINT BookPK PRIMARY KEY(BookID),
 CONSTRAINT Book_ClassFK FOREIGN KEY(ClassNum) REFERENCES Class(ClassNum),
 CONSTRAINT Book_PublisherFK FOREIGN KEY(PublisherID) REFERENCES Publisher(PublisherID)
)
--Line43
create table BorrowedBooks (
 PatronID varchar(10) not null,
 BookID int default 1 not null,
 WithdrawDate datetime not null,
 ReturnDate datetime not null,
 CopyQty int not null,
 CONSTRAINT BorrowedBooksPK PRIMARY KEY(PatronID,BookID),
 CONSTRAINT Borrowedby_Patron FOREIGN KEY(PatronID) REFERENCES Patron(PatronID),
 CONSTRAINT Borrowed_Book FOREIGN KEY(BookID) REFERENCES Book(BookID)
)
--Line54
create table Subclass (
 SubclassNum int not null,
 SubclassName varchar(30) not null,
 SubclassDescription varchar(40),
 ClassNum int not null,
 CONSTRAINT SubclassPK PRIMARY KEY(SubclassNum),
 CONSTRAINT Subclass_ClassFK FOREIGN KEY(ClassNum) REFERENCES Class(ClassNum)
)
--Line63