Entity relationship diagrams (library example)

DJMK4

DJMK4

DJMK4

Soldato
Joined
1 Dec 2004
Posts
22,686
Location
S.Wales
Ok, anyone who remembers my thread?

Im having 2nd thoughts about my entity attributes now..

Here they are

erd3.JPG



I need to accomodate attributes to somehow define the fines part of the system, i was thinking about doing this in the loans entity.

Basically if the book late back in, the customer is fined £1 for every week that passes for every book. Would this work by including an:

"Weeks overdue" attribute in the loans table? then adding another attribute in the customer table called "Outstanding fines" this would then somehow get the information from the loans table.

Also the customer can only take out 10 books at a time, how would i link between the loans and the customers table to keep records of how many books the customer has on file?
 
im trying aint i?? iv done it all infact, before i print out the final draft im having trouble positioning the 2 attributes i have left out, iv racked my brain, and my notes, which havnt helped.
 
Hi,

Few quick thoughts:

"Weeks Overdue" is a function of the current date and "date due" and therefore should not be stored in the database at all really, although you can do so if you wish (would require periodic updating, so why not just compute it in the application when it is required?).

To view loan count by customer:

select customer.name, l.count(*)
from customer c, loans l
where c.customerID = l.customerId
group by c.customerID

Also, wouldn't the Copies tables be better having bookId as the PK and a column called "stock" for example, to track how many of each book the library has?

T
 
Last edited:
Question:

would the bookID not be unique, and therefore act as a copyID? You can then eliminate the copies table. Or was that table auto generated when converting it to a relational DB?
 
panthro said:
Question:

would the bookID not be unique, and therefore act as a copyID? You can then eliminate the copies table. Or was that table auto generated when converting it to a relational DB?
Click to expand...

I was working on the assumption that books are unique (per dewey number) but the library has multiple copies of each book - thinking about it you could just have a copies column in the book table and eliminate the copies tables - provided we understand your requirements correctly.
 
Trimbo said:
Also, wouldn't the Copies tables be better having bookId as the PK and a column called "stock" for example, to track how many of each book the library has?

T
Click to expand...


panthro said:
Question:

would the bookID not be unique, and therefore act as a copyID? You can then eliminate the copies table. Or was that table auto generated when converting it to a relational DB?
Click to expand...

I'm assuming he's doing A-Level ICT, and it looks like a similar system to what I had to do. I think the system their doing lets you identify each particular copy of a book. This way you can assign a physical copy of a book to someone rather than just 'any' book.

For example, A library may have 3 copies fo book XYZ.

XYZ - Copy 1 (Serial A)
XYZ - Copy 2 (Serial B)
XYZ - Copy 3 (Serial C)

so for example you can loan out the book with Serial number C to someone, which is more fitting for a real time library system.
 
Pho said:
I'm assuming he's doing A-Level ICT, and it looks like a similar system to what I had to do. I think the system their doing lets you identify each particular copy of a book. This way you can assign a physical copy of a book to someone rather than just 'any' book.

For example, A library may have 3 copies fo book XYZ.

XYZ - Copy 1 (Serial A)
XYZ - Copy 2 (Serial B)
XYZ - Copy 3 (Serial C)

so for example you can loan out the book with Serial number C to someone, which is more fitting for a real time library system.
Click to expand...

Makes sence in that case.
 
Trimbo said:
I was working on the assumption that books are unique (per dewey number) but the library has multiple copies of each book - thinking about it you could just have a copies column in the book table and eliminate the copies tables - provided we understand your requirements correctly.
Click to expand...
Obviously this wont work if he wants to assign a specific copy of a book to a loan, rather than just an arbitary copy.

EDIT: Pho beat me to it
 
Another small point - the PK of the Loans entity is actually (CopyId, CustomerId), not just CopyId - as the same copy could be lent to a different customer at a later date (assuming here that you don't intend to delete rows from the Loans entity when a book is retured). Similar problem if the same customer borrows the same book again at a later date. Do you intend to delete rows from Loans when books are returned?
 
Last edited:
[Sniper][Wolf] said:
im trying aint i?? iv done it all infact, before i print out the final draft im having trouble positioning the 2 attributes i have left out, iv racked my brain, and my notes, which havnt helped.
Click to expand...

I love it when people fail to notice smilies and take comments to seriously.
 
As regards to the books/loans/copies i would rather stick to what i got..


The library has multiple copies of the same book, the book obviously is recognised by the ISBN number (Yes im going to change it from BookID to ISBN), the library may or may not stock multiple copies of each book. This brings me to the copies table..

The copies table will contain the CopyID and the ISBN number which will identify each book's copy.

The problem i am faced with is how to get the information based on tracking the books to each customer when they are loaned out (can this be done by simple queries?) and also sorting out the fines?

Anyone??
 
Last edited:
[Sniper][Wolf] said:
The problem i am faced with is how to get the information based on tracking the books to each customer when they are loaned out (can this be done by simple queries?) and also sorting out the fines?
Click to expand...
It may just be me (I'm no database expert), but I'm not really sure what you mean by this? What are you trying to do?
 
You must log in or register to reply here.
Back
Top Bottom