From UML to SQL (PostgreSQL)

Question

I am training for an upcoming exam and just finished this (simple) exercise.

I just wanted to be sure that I implemented everything correctly, especially the Composition with the multiplicities 1 and 0..*

My Answer:

CREATE TABLE exam.A(
    idA integer,
    b text NOT NULL,
    c float DEFAULT -1.0 CONSTRAINT negative_c CHECK (c < 0.0),
    PRIMARY KEY(idA));

CREATE TABLE exam.B(
    idB integer,
    c integer,
    PRIMARY KEY(idB));

CREATE TABLE exam.RelationAandB(
    idA integer NOT NULL ON DELETE CASCADE,
    idB integer,
    b integer,
    c text,
    FOREIGN KEY (idA) REFERENCES exam.A(idA),
    FOREIGN KEY (idB) REFERENCES exam.B(idB),
    PRIMARY KEY (idA, idB));

Answer 1

Your SQL code is pretty good, but I see the following issues:

1. In UML class diagrams, attributes are mandatory by default. They would be optional only when qualified with the multiplicity expression [0..1]. Consequently, all attributes would need to be coded as NOT-NULL columns. Possibly, however, your instructor has not been aware of this or is using a non-standard reading for "UML data models".
2. The string-valued attribute A::b has a "{not empty}" property modifier, which reads as a constraint requiring non-empty strings. Notice that having a non-empty string value is not the same as being mandatory (NOT NULL) because the empty string "" satisfies the NOT NULL constraint.
3. You also need a CASCADE DELETE rule on idB in the RelationAandB table, because no matter if an A or a B is deleted, the associated tuples in RelationAandB have to be deleted as well.
4. I think, for readability, it is preferable to add the PRIMARY KEY declaration to the column definition if the key is non-composite (has just one column). The same holds for single-column FOREIGN KEY declarations.
5. Many people think that a composition implies a deletion dependency, although this is not warranted by the UML semantics (see Aggregation versus Composition), and it is also not based on common sense (see my remarks below). In your SQL code, you did not implement such a dependency ("whenever an A is deleted, all dependent Bs have to be deleted as well"), which is correct according to the UML semantics of the class diagram, but which may have been the intention of your instructor, especially since he made it mandatory for a B component to have an A composite (by the multiplicity 1 at the composite side). Such a mandatory composite constraint implies that, when their composite is deleted, components either have to be deleted as well or they have to be re-assigned to another (A) composite. If your instructor's intention was that there should be a deletion dependency, then you should better add a corresponding foreign key declaration in exam.B from idB to RelationAandB with a CASCADE DELETE rule: idB integer FOREIGN KEY REFERENCES exam.RelationAandB CASCADE DELETE,

Concerning the question if a composition implies a lifecycle dependency between a composite and its components, we have to distinguish between three levels of abstraction: 1) the purely conceptual (philosophical) level, which should be the common sense of a data modeler, 2) the UML semantics, which is often not precisely defined, and 3) the level of (e.g., SQL) code. At the conceptual level, it should be clear that there are compositions with and without such a lifecycle dependency, so the very fact that there is a composition does not imply a lifecycle dependency.

Unfortunately, UML didn't define any means how to declare that a composition has existentially dependent components. In my SO answer Aggregation versus Composition, I have proposed to use a stereotype "inseparable" for such a composition.