xx

Hi.

IMHO you left out one entity on your ERD: the one that holds the cd copies.

With the design you propose you´ll have to repeat the title, synopsis and all data for each CD copy available, also the ISBN alone wouldn´t keep track of a rented cd, since all copies of a CD will have the same one, so you need a way to identify each physical CD.

On the other hand, the Category Entity is a little mixed up, I guess that it holds the category where the cd is classified (ie. action, comedy, etc)

I would do it more or less like this:

Member (member_id, membership_num, name, sex, age, address... etc)

cds (cd_isbn, title, synopsis, category_id fk)

categories (category_id, description, num_titles)

copies (copy_id, cd_isbn fk, date_purchased) * imho it's wrong to assume all copies will be bought the same day, think about replacements

rentals (rental_id, copy_id fk, member_id fk, rental_date, due_date)

reservations(reservation_id, cd_isbn fk, date_reserved, time_reserved)

Note the categories table, you shouldn't include any cd information there.

Hope this helps

Hi Zemog,

Thanks for the reply. What you mentioned is kinda true, and I did not realise it too.

I actually assume that even for the same CD Title, they will have their own different unique ISBN. I do not know how true this is in the real market. Perhaps what you said is true, every same title will have the same ISBN.

If you can just do me a favor ( I know abit too much ) ... on the diagram itself ...

I drew a new ERD .. wonder how correct it is ...

xx

Yup, you got it, that looks fine to me

Thx Zemog ... I showed this to my lecturer ... what he says that we only need to have copies in the CD entity ...

That means it will be the formal diagram but add a new column, COPY in CD entity.

Erm ...

It is possible to do it like that, but it is not efficient at all.

It is far better to have a separate table to hold the copies, but if that makes him happy so let it be. But you'll know better than that, right?

What will that column hold? the number of available copies?

That approach works if there are no plans to track each individual copy, which in my opinion should be desirable (to track each one).

If he's suggesting that you'll have a row for each copy then he's way out of order

Anyway, removing the "copies" table you'll lose track of when a specific cd was bought, or how many times a specific copy was out, or who had the copy out the last time (provided that you have a history table for that).

Don't you remember on the old times of videotape rentals when you had to go back and return the tape because the previous member messed it up?

Well I think you got the idea by now.

So if he's happy then go ahead.