Relationship in ER model

问题:

I am learning about ER modelling about Database systems. My problem is that there is a entity called books,enitity named user and I want to create a borrows relationship between user and book with attribute issue date. I modelled it as described but it was pointed that borrows cannot be a relationship because a same user can borrow a book twice. Can anybody explain me what this is as I am using issue data as an attribute so records in borrows relationship would not collide as I will use PK as userid,bookid and issue date. How can I model this accurately? I am a little confused in this.

回答1:

In the ER model, entity relations consist of attributes of a single entity set, in which the PK identifies only one entity set. Relationship relations have a composite PK that represents two or more entity sets.

Your question uses a composite PK that represents two entity sets (userid and bookid) and a value set (issue date). Strictly speaking, it's neither an entity relation nor a relationship relation. It's a combination of a relationship relation (two entity keys) and a weak entity set (issue date functions similar to a weak key). If we want to be creative, we might call it a weak relationship.

If I was forced to draw an ER diagram for this, I might present it like this:

The ER model isn't a complete logical model (unlike the relational model) and there are some situations which aren't handled well or at all. This is one of those situations.

回答2:

As per description, User and Book are the entities.

One user can borrow an instance of book. Similary, one user can borrow multiple instances of book, whether It can be same instance or various instances.

So every transaction between the User and Book has the Issue Date. Neither the user nor the book has the Issue Date.

Here, the relationship between User and Book are Many to Many.

The Bridge table is Transaction. We can name it as Borrow also as per your interest.

Now, The user has one to many transactions. Every Book has one to many transactions.

Every transaction is a combination of a User and Book.

Note: Since every user can have the same book multiple times and at the same day. So we can have a composite primary key of user_id, book_id and Issue_timestamp as there is a chance of redundancy in the Issue Date in the same combination.