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.
Asked
Active
Viewed 1,144 times
1
-
I don't know much about ER modelling but in the given example what would prevent a user from borrowing the same book multiple times at the exact same time (hence having same issue date) ? – Etienne Mar 20 '17 at 01:43
-
issue date is datetime type so there would be a difference. – Pooja Gupta Mar 20 '17 at 01:45
-
1I would make borrow as an entity and not a relationship. – Etienne Mar 20 '17 at 04:28
-
How do you chose that? – Pooja Gupta Mar 20 '17 at 05:08
-
The relationship is accurate, the issue date serves to uniquely identify , unless a user borrows the same book twice in a day ! – asdf Aug 03 '17 at 17:27
-
@Etienne Check my below answer, I think this is what, you are saying. – omkar sirra Sep 28 '17 at 06:23
2 Answers
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.
reaanb
- 9,806
- 2
- 23
- 37
0
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.
omkar sirra
- 696
- 10
- 28