Problems on n-ary associations in SQL/UML

0
votes

I have two (linked) questions about class diagrams UML and SQL.

FIRST: ABOUT UML In class diagram UML can i have ternary associations with recursion? I mean something like this:

enter image description here

(ternary association with recursion)

If it is possible, how have i to implement it in SQL? Should i have a unique table with three attributes (one for each class involved)?

SECOND: ABOUT SQL How to implement association cardinality? Suppose to have a situation like this:

enter image description here

How can i ensure that each instance of class A is associated with two and only two instances of class B?

2
sqluml
A cardinality constraint essentially requires locking of the entire table when perfoming DML operations. This is expensive and badly affects system perfomance. So dbmses have no such options. Solution is to implement it in your server-side DML code, stored procedures etc.. – Serg
You should not ask two question in one post. – qwerty_so

2 Answers

0
votes

First: Yes, you can do that with no problem. Of course it depends on what and why you want to do it. And this is in no way recursive.

Second: Implementation is up to the developer. If there's a multiplicity constraint then the developer needs to code a check for either the array or the field1 up to fieldN to contain object references. In your example a ClassA instance needs exactly two references (you didn't name them) to ClassB while ClassB's have an optional ClassA reference. (I have no idea why you tagged this with SQL)

0
votes

Question 1:

TableA(int id, varchar fieldA) ->PK id
TableB(int id, varchar fieldB) ->PK id

association table

TableAB(int id, int keyA, int keyB) -> PK id,
FK1(keyA) ref TableA(id)
FK2(KeyB) ref TableB(id)

Question 2: (java)

class B 
{

}
class A
{
      //can put constrains on list(methods) not to able to add more the 2(or n) elements
      List<B> l;
      // or B b1; B b2; 
}