[RESOLVED] Help with database normalization...

Hi guys, I'm not so sure if my question is supposed to be placed in this section of the forums, so please move it as needed. Anyway, my question is about whether this database that I'm working on is already normalized. It's about a library borrowing system...

*PK - Primary Key; FK - Foreign Key... just a heads-up note

[Book Table]
CallNum (PK)
BookTitle
ISBN
Description
Subject
CopyQty
ClassNum(FK)
AuthorID(FK)

[Author Table]
AuthorID(PK)
AuthorLname
AuthorFname

[Publisher Table]
PublisherID
PublisherName -- the company name
ContactAddress
ContactNumber
ContactEmail

[Class Table] -- classification, eg: psychology, mathematics, etc.
ClassNum (PK)
ClassName
ClassDescription

[Subclass Table]
SubclassNum (PK)
SubclassName
SubclassDescription
ClassNum (FK)

[Patron Table] -- the table for users of the library...(?)
PatronID (PK)
LName
FName

[Borrowed_Books Table]
PatronID (PK)
CallNum (PK)
WithdrawDate
ReturnDate

Some more info, the database is for a high school company, so it's a bit compact, and it only processes books, not other articles, like magazines, newspapers and other stuff. Also, I'm going to use SQL Server 2000 for this. I'll be posting the ERD for the database as well in my later posts.

It would be very kind of you to help me point out errors in this database, and help me normalize it as well. Thanks much~!

Rie

You did post in the correct place, this sub-forum is for any questions about databases (or using databases from code).

Your design is pretty well normalised, but there are two things that don't seem ideal to me.

The first is that something that I suspect you simply missed out when writing your post. The Publisher Table doesn't link to/from the other tables, I would expect PublisherID to be a PK, and for it to also be in the Book Table as an FK.

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.

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.

-tg

Quote:

---

Originally Posted by si_the_geek

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.

Quote:

---

Originally Posted by techgnome

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!

Code:

---

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

---

From what I remember the ISBN is unique to the title... so if you have two copies of a book, they have the same ISBN... which is why you would neet the CopyNumber as well to create a unique identifier.

-tg

I think you need an extra table. I would suggest one table that would contain all the information for each book as published. If you had 1 copy or 1000 copies of a particular title, you'd still only have one record in that table. You'd then have another table for each physical copy of any book, so if you had 1 copy of a book you'd have one record in that table and if you had 1000 copies you have 100 records. You might name those tables Title and Book, or maybe Book and BookCopy, or something else along those lines. You'd then relate your Patron table to the second of those tables.

Quote:

---

Originally Posted by techgnome

From what I remember the ISBN is unique to the title... so if you have two copies of a book, they have the same ISBN... which is why you would neet the CopyNumber as well to create a unique identifier.

---

I just made a bit of research on that, and yes, the ISBN is unique to a title. Definitely a PK candidate/candidate key.

Quote:

---

Originally Posted by jmcilhinney

I think you need an extra table. I would suggest one table that would contain all the information for each book as published. If you had 1 copy or 1000 copies of a particular title, you'd still only have one record in that table. You'd then have another table for each physical copy of any book, so if you had 1 copy of a book you'd have one record in that table and if you had 1000 copies you have 100 records. You might name those tables Title and Book, or maybe Book and BookCopy, or something else along those lines. You'd then relate your Patron table to the second of those tables.

---

Okay, I'm not sure if I'm getting your idea right, but would it look like this?

[--Table Relationships--]
BookTable -(1 to many)-> BookCopy
ISBN(PK) ISBN, CopyNum(PKs)

BookCopy -(1 to 1?)-> BorrowedBook
ISBN, CopyNum(PKs) LibTransacID(PK)

BorrowedBook <-(1 to many)- PatronTable
LibTransacID(PK) PatronID(PK)

[--Table Layouts--]

[BorrowedBookTable]
LibTransac(PK)
ISBN(FK)
CopyNum(FK)
PatronID(FK)
WithdrawDate
ReturnDate

[PatronTable]
PatronID(PK)
PatronInfo...

[BookTable]
ISBN(PK)
BookInfo...

[BookCopy]
ISBN(PK)
CopyNum(PK)

And one more thing, should I divide the Author field to AuthorFname and AuthorLname? And will there be a need for the Publisher table, Address field to be divided into Street, City, State, etc.?

Quote:

---

Originally Posted by riechan

should I divide the Author field to AuthorFname and AuthorLname?

---

It is debatable, especially as some books have more than one author.

In terms of having 'clean' data it would be a good idea, as you won't get issues like one person entering "Brad Jones" and another entering the same thing as "Jones, Brad". The downside is you have to deal with additional authors somehow.

Quote:

---

And will there be a need for the Publisher table, Address field to be divided into Street, City, State, etc.?

---

If you think you might ever want to make use of parts of the Address (perhaps for doing a search by State) then it is a good idea to use separate fields right from the start - as splitting it out afterwards is time consuming and prone to errors (often needs to be done manually).

I can't think of a reason you would need separate fields for it, as it seems to be just "additional info" that will rarely be relevant (and only when contacting that publisher, so any splitting could be done manually as needed). However, I obviously don't know the system as well as you, so there may be reasons I haven't thought of!

Personally I would have one Book Title record with a field representing the number of copies avail. As you check out a book you decrement the value and on check in you increment the value.

@si_the_geek: Okay, well, I decided to split the Address and the Author fields.
@GaryMazzone: Got that! I just (re)included that field into the Book table.

Anyways, here is the layout that I was able to create after considering everyone's ideas. Please point out anymore errors or inconsistencies as needed.

I'm not so sure that I would use ISBN for a PK. It's a long and ugly string. Plus I just opened a book sitting on my kitchen counter and it's got two ISBN's listed.

It's like Social Security Number. One would be inclined to use that as a PK on a PERSON table, for instance.

And keep in mind that I really dislike IDENTITY or AUTONUMBER fields!

But this is one case where it's preferred.

The PK for the BOOK table should be an INT type field that is an IDENTITY value.

For the single reason that a typo is possible in an ISBN value. If that value needed to be changed you would have to consider cascading the value across all tables.

We have had many times that SSN's have been changed on people in our PERSON-type tables.

As for your address fields. Street, City and Zipcode - that's missing some stuff. When we store all aspects of an address they are Apartment Number, Complex Name, PO Box, Street Number, Street Name, City, State and Zip. Sometimes we even store Direction - Prefix Direction and Postfix Direction. That's for really detailed address storage.

For you case - I would think that it would be better to simply have

Address Line 1
Address Line 2 (maybe even an Address Line 3)
City
State
Zip

In your book table - that Author name - I'm not sure that breaking it down to First and Last name will work in all cases. I would personally make it a single large field. Many books are written by more then one person.

Of course these are just my opinions :)

Quote:

---

Originally Posted by szlamany

I'm not so sure that I would use ISBN for a PK. It's a long and ugly string. Plus I just opened a book sitting on my kitchen counter and it's got two ISBN's listed.

It's like Social Security Number. One would be inclined to use that as a PK on a PERSON table, for instance.

And keep in mind that I really dislike IDENTITY or AUTONUMBER fields!

But this is one case where it's preferred.

The PK for the BOOK table should be an INT type field that is an IDENTITY value.

For the single reason that a typo is possible in an ISBN value. If that value needed to be changed you would have to consider cascading the value across all tables.

We have had many times that SSN's have been changed on people in our PERSON-type tables.

As for your address fields. Street, City and Zipcode - that's missing some stuff. When we store all aspects of an address they are Apartment Number, Complex Name, PO Box, Street Number, Street Name, City, State and Zip. Sometimes we even store Direction - Prefix Direction and Postfix Direction. That's for really detailed address storage.

For you case - I would think that it would be better to simply have

Address Line 1
Address Line 2 (maybe even an Address Line 3)
City
State
Zip

In your book table - that Author name - I'm not sure that breaking it down to First and Last name will work in all cases. I would personally make it a single large field. Many books are written by more then one person.

Of course these are just my opinions :)

---

@szlamany: Okay, I get your point.. so what you're saying is that I should use a BookID field or something like that? Well, I get the idea and all, and it's a good thing that I found out about it as soon as possible to make all the necessary changes. With regards to the Author field, what if I like place all the other authors in the description field instead?

In other words:

[BookTable]
BookID(PK) -- which would be an autonumber field?
Other book info...

I didn't know that a book can have 2 ISBNs. Maybe it was a different edition?

Umm... I don't think the Address field (when split up) doesn't really have to be that specific you know (in our system, at least). I might add the PO BOX field if necessary, though. But the other fields, I don't think so.

Anyways, thanks for the suggestion!

I simply opened a book and on the inside page it shows two completely different ISBN's. One might be ISBN-10 - maybe that's a new scheme for identifying books.

Yes - BookId - autonumber field (if ACCESS - MS SQL calls it IDENTITY column).

I don't think you should break down your address - I think address line 1, address line 2 and city, state, zip are more then enough for you.

For the issue with multiple authors could you have AuthorGroupID in your Book table, which replaces the AuthorFName and AuthorLName.

Then you could create a AuthorsGroup table and a Author table.

AuthorsGroup Table (Two-Way Junction Table)
AuthorGroupID (composite key)
AuthorID (composite key)

Author Table
AuthorID (key)
AuthorFName
AuthorSName

The AuthorsGroup table is a many to many realtionship between the Book table and the Author table

e.g. A book has 3 Authors, Bill Jones, Fred Banks, Hillary Smith and another book has 1 Author which is Hillary Smith again.

@kev - that's a very nice representation of reality!

Excellent.

It allows you to find books with any author - either individual or several authors. And respects 3rd normal form very nicely - no duplication of author names or chance of typo's. An author only exists once.

If you use a Number of Copies field in the Bool table why use a BookCopy table? I think that table should be droped at this point.

Quote:

---

Originally Posted by GaryMazzone

If you use a Number of Copies field in the Bool table why use a BookCopy table? I think that table should be droped at this point.

---

Well, the NumberofCopies field in the Book table will "monitor" how many books have been borrowed, which is found in the LibTransac table.

A little bit of heads up again...

BookCopy table determines how many copies of a book has. In other words, the instance of a title/book.

eg: vbforums.net 2nd Edition --> BookID = 1, NumberofCopies = 3

[BookId(PK), CopyNum(PK) -- BookCopy table]
1,1
1,2
1,3

Let's say if I were to borrow that book (which will be placed in the LibTransac table)...

[LibTransacId(PK), BookId, CopyNum -- LibTransac table]
1, 1, 1 ---> which pertains to the first transaction, the vbforums.net book, and the first copy of that book

Then, in the Book table, NumberofCopies field, it will monitor if the amount of books borrowed/to be borrowed will correspond to the data in this field. Let's say all books were borrowed by me again (can't have enough of one book with the same title and the same contents, huh?). If someone else other than me attempts to borrow that book, the system will deny that request, because the number of instances of the book, "vbforums.net 2nd edition", will exceed the NumberofCopies as stated in the Book table.

Did that sort of answer why I'm using the NumberofCopies field in the Book table?

Just wondering how I would determine which copy of the book is being/currently borrowed from the library when I get this database to my application. Anyways, I might ask again from you guys, in the VB.net section -- heehee.

Quote:

---

Originally Posted by kevchadders

For the issue with multiple authors could you have AuthorGroupID in your Book table, which replaces the AuthorFName and AuthorLName.

---

@kevchadders: Hey, thanks much for the suggestion. While it adds more work to be done in the system (like having to add an Author 'Registration' form and processing that and all), it does certainly help that my professor won't have to question my dbase structure -- heehee. Again, thanks for the tip!

Quote:

---

Originally Posted by szlamany

I don't think you should break down your address - I think address line 1, address line 2 and city, state, zip are more then enough for you.

---

@szlamany: Well, I was just wondering if my professor would ask me as to why I didn't break the Address field into separate fields. And I'm wondering why as well. Would that not sort of violate the first normal form of dbase normalization wherein you would have to make atomic fields from non-atomic fields (or was it the other way around)? Anyways, I'll take that idea into consideration. Thanks again~!

With the address information, again i think it would be worth considering replacing street, city and zipcode in the Patrons table with just an AddressID field, which would link to a Address table.

Then you can create the Address table which can store all the addresses, thus keeping it seperate from the Patron table.

The amount of infomation can vary a lot when it come to storing address. As szlamany pointed out you could need to store Apartment Number, Complex Name, PO Box, Street Number, Street Name, City, State and Zip etc..

Personally, would take szlamany sugguestion with the extra address field. e.g.

Address Table
AddressID
AddressLine1
AddressLine2
AddressLine3
City
State
Zip

If more fields are needed then it should be straightforward to add any extra fields to the table.

@everyone: well, I'm pretty much done with everything now. I'm going to post my final erd within a few days and probably marked this thread resolved. To everyone who helped, thanks much again!