1

I am trying to complete a question for one of my courses, and it revolves around which relationship is the best solution - binary or ternary.

Relationship examples

enter image description here

So it is a video store, with three entities - Video, Member, and Casual.

Members, and Casuals can borrow videos, and the date it was borrowed is recorded.

Would a ternary relationship be the most suitable solution, as there is no distinction between a member and a casual borrowing a video?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Smeckop
  • 69
  • 8
  • 1
    What are the implications of either approach? What would the DB schema look like, and what would have to be considered when data is stored? – JimmyB Feb 11 '16 at 09:33
  • Hint: Can there ever be a video which is borrowed by a member and a casual customer at the same time? - If not, how could that constraint be enforced on the data? – JimmyB Feb 11 '16 at 09:37
  • @HannoBinder No other information provided in the question, so you would assume there would be multiple copies on a single video which can be borrowed, but a member and a casual could not borrow the exact same copy. – Smeckop Feb 11 '16 at 09:40
  • You should assume that each single copy of any video has exactly one unique `videoID`, so that every `videoID` can only ever be borrowed by a single customer for any given `loanDate`; and every customer can be either a member *or* a casual, but not both. – JimmyB Feb 11 '16 at 09:43
  • @HannoBinder Sorry, that's what I meant, just much clearer the way you worded it. So Ternary Relation would be best suited as there is no distinction between loans, and only one entity can borrow a video at a time? – Smeckop Feb 11 '16 at 09:55
  • Yes, that should be it. Using a ternary you can more easily make sure that there's not more than one loan per videoID and loanDate ever. Additionally, you may think about how to ensure that a single loan does not refer to a member and a casual at the same time. – JimmyB Feb 11 '16 at 10:16
  • Thanks for your help @HannoBinder – Smeckop Feb 11 '16 at 10:19
  • Were these diagrams given to you or did you make them? If the latter, why have you set up the Borrows so? Because you really want one binary (entity-wise) Borrows relationship "CLIENT is borrowing VIDEO (until LOANDATE)". – philipxy Feb 11 '16 at 10:32
  • @philipxy They were given as a tutorial question. Sorry, just to clarify, you are saying a binary relationship is the correct answer because Members and Casuals shouldn't be related (which is what is occuring in the ternanry diagram?). – Smeckop Feb 11 '16 at 10:35
  • What is required is a ternary relation with CLIENT, VIDEO and LOANDATE, where CLIENT is subtyped into MEMBER or CASUAL. The date is not an attribute of the relationship since it's not determined by CLIENT and VIDEO. – reaanb Feb 11 '16 at 10:37
  • @philipxy I'm saying LOANDATE qualifies as such. In ER an attribute is a function from an entity or relationship set to a value set. An entity is any thing that is the subject of attributes and relationships. – reaanb Feb 11 '16 at 10:43
  • @philipxy If LOANDATE was an attribute, a CLIENT could only borrow a specific VIDEO once. I doubt that's what was intended. – reaanb Feb 11 '16 at 10:54
  • I read the 1-ternary diagram as specifying the FD `videoID, memberID, driversLicense -> loanDate` and the 2-binary diagram as `videoID, memberID -> loanDate` and `videoID, driversLicense -> loanDate`. If I was modeling a video store I would want `videoID, loanDate -> clientID` and `memberID <: clientID` and `casualID <: clientID`. – reaanb Feb 11 '16 at 11:00
  • @reaanb Even the school-given diagrams are labeled "ternary" and "binary". Please read what my comments say about the convention for choice of arity in ER diagrams. It doesn't have to be a good idea, that's just how it is. The relationship *is* 4-ary, but ER terminology only counts the boxed-entity roles and so calls it ternary. Also the lines are not FDs, the lines from boxes are FKs & the lines to attributes are just connecting the attribute to its entity or relationship. ER doesn't understand the relational model. – philipxy Feb 11 '16 at 11:01
  • I would change the LOANDATE to a boxed-entity since it's a determinant in the relationship, not determined by the rest. – reaanb Feb 11 '16 at 11:03
  • @reaanb That is not how you draw this kind of diagram or model in an ER manner. – philipxy Feb 11 '16 at 11:06
  • @philipxy Chen's original paper states "An attribute can be formally defined as a function which maps from an entity set or a relationship set into a value set or a Cartesian product of value sets", which doesn't apply to LOANDATE. – reaanb Feb 11 '16 at 11:07
  • See sections 2.2.1,2.2.2 & 2.2.3. – philipxy Feb 11 '16 at 11:09
  • I already am. It also says "A specific ... event is an example of an entity." and "A relationship is an association among entities." which applies if a client can borrow the same video more than once, i.e. LOANDATE is part of the candidate key. – reaanb Feb 11 '16 at 11:14
  • @reaanb I didn't say it wasn't part of the candidate key. I said it wasn't an entity. You seem to think that ERM should be some particular thing because of your expectations and relational bias. It doesn't understand the relational model, and it's an impediment to good design. But it is what it is. Best wishes. – philipxy Feb 11 '16 at 11:25
  • @philipxy If it's an attribute, what is it an attribute of? What determines LOANDATE? I suspect you're transferring common network model misconceptions onto the ER model. Non-Chen ER diagrams are really DSDs using ER terminology. Carefully reading Chen indicates a bit more thought than you seem to give him credit for. – reaanb Feb 11 '16 at 11:46
  • @reaanb LOANDATE is an attribute of the (3-entity) relationship. LOANDATE as attribute can be part of a CK: "an attribute can now have several values for the same entity [or relationship]"--[Chen's 2nd ER paper](http://www.computer.org/csdl/proceedings/afips/1977/5085/00/50850077.pdf). Qv ERM & UML chapters in (either edition of) [Information Modeling and Relational Databases](http://www.orm.net/) as quick IM method semantics summary. I welcome a reference for your interpretation of this/Chen style of ER diagram. But I am confident that your comments do not reflect the semantics. – philipxy Feb 12 '16 at 01:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103248/discussion-between-philipxy-and-reaanb). – philipxy Feb 12 '16 at 01:42

1 Answers1

1

What ternary (3-entity) relationship do you consider the "Borrows" diamond to stand for? It's between a member client, a video, a casual client and a loan date. "MEMBER borrowed VIDEO from CASUAL on LOANDATE"? "VIDEO is of CASUAL punching MEMBER born on LOANDATE"? "MEMBER borrowed VIDEO due on LOANDATE and CASUAL is null or CASUAL borrowed VIDEO due on LOANDATE and MEMBER is null"? Ditto for each of the left & right binary "Borrows" relationships.

(I am using a predicate (statement template parameterized by columns) to express the "meaning" of a table. When you plug in a row you get a proposition (statement). Every table (base or query result) holds the rows that make its predicate into a true proposition. Relation operators (join, union, etc) are chosen to alternatively express, and to calculate, predicate expressions using logic non-terminals (AND, OR, etc) of a desired query predicate. )

The diagram has a diamond. It has to stand for a relationship between MEMBER, VIDEO and CASUAL entities plus LOANDATE. It does seem hard to come up with a sensible one for the ternary Borrows other than the AND or other combination of smaller ones, doesn't it? If you can't think of one, then that's not a diagram that describes your situations. I can't think of one that is limited to triplets from those three kinds of entities either. Whereas the binaries make sense easily.

PS My only caveat is: The third option I gave really is a typical relationship as embodied in database tables. But it isn't a relationship just on entities of the model. It also involves a "null" value. If we consider the relationships to be among values that identify entities or don't, then we can have that relationship that's using null.

Borrows:
    "MEMBER borrowed VIDEO due on LOANDATE and CASUAL is null
    or CASUAL borrowed VIDEO due on LOANDATE and MEMBER is null"
MEMBER  VIDEO   CASUAL   LOANDATE
=================================
  1       a      null    1/1/2000
  2       b      null    1/2/2000
 null     c    1234567   2/3/2000

I don't know what your teachers intended. But I suspect that they didn't expect you to involve null.

PPS Re "Would the binary relationship have a problem where a MEMBER and a CASUAL could BORROW the same VIDEO at the same time?" I'm not sure what you are thinking. But the relationship just contains the rows that make a true statement from its statement template. It describes the situation. It cannot cause the situation to be a certain way. The "business rules" (and physics!) limit the situations that arise. But you do need to record enough information to run the business. And the DBMS can be told about impossible database states so it can reject them.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thank you for you help @philipxy! So from the information provided, and the out of the two provided options - The best solution would be the Binary Relationship. – Smeckop Feb 11 '16 at 11:32