database design what do u guys think

[RickyD]

The exercise is to store the following fields for a library database: AuthorCode, AuthorName, BookTitle, BorrowerAddress, BorrowerAddress, BorrowerName, BorrowerCardNumber, CopiesOfBook, ISBN, LoanDate, PublisherCode, PublisherName, and PublissherAddress. A one to many relationship exists between the publishers and books and many to many relationship exists between authors and books and between borrowers and books. Tables need to be in third normal form

I came up with 6 tables

Author (AuthorCode, AuthorName)
PK AuthorCode
AK AuthorName

Borrower (BorrowerCardNumber, BorrowerName, BorrowerAddress)
PK BorrowerCardNumber
SK BorrowerName

BorrowedBooks (BorrowerCardNumber, BookID, LoanDate)
PK BorrowerCardNumber
PK BookID

AuthorsBooks (AuthorCode, ISBN)
PK AuthorCode
PK ISBN

Book (BookID, ISBN, BookTitle, CopiesOfBook, PublisherCode)
PK BookID <--- created a artificial key because ISBN could be repeating with more than 1 copy
AK ISBN
SK BookTitle
FK PublisherCode --> Publisher

Publisher (PublisherCode, PublisherName, PublisherAddress)
PK PublisherCode
SK PublisherName

[BlackOps]

Hello,



i am implementing database model of my organization, with records of the Employees, and their Area access cards.

My organization will have some divisions.
Every Employee will have any title.
Every Employee was born in some place...
Every Employee was given a passport by some Passport issuer.
Every Employee lives in some country.
Every Employee has card,give on some date which gives him a permission for one or more areas.

i am attaching the relations schema,
and i want you, the proffesional guys take a look at it, and say how good is my database design according to my needs.

thanks in advance, i will wait for your reply, its very important to me.

[davide++]

Hi all

@RickyD

The design seems to be good. Some considerations follow.
First, in table Author AuthorName shouldn't be an alternative key because you won't insert two authors with the same name.
The table Book (a better name for it is Books, always plural) has an ID as primary key because there're more than one book (identified by ISDN), one for any copy, so you'll have n records for n copies, all with same ISBN; I suggest another way to manage the copies: instead of having n records for n copies, add a field to Book table, called COPIES_AVAILABLE, that holds the number of copies for a book; so, you can have one instance for every book, and will be easier to manage the availability of books, simply checking the value of the field, when COPIES_AVAIABLE = 0 there aren't book that can be borrowed.