The teacher gave us a team assignment, and me and my teammate are quite struggling with it (especially since we need to use things like TRIGGERS and PROCEDURES, things we didn't see in class yet …).
We need to implement an arc-relationship, and we fail to understand how … But before I tell you guys what I need to accomplish, I will give you part of the description of the task, so you guys can understand the situation a bit better …
We basically need to make an ERD for a VLSI CAD-system and we need to implement it. Now, we have our CELL entity, the attributes of which aren't really relevant … The only thing you guys need to know in order to help us is that it has a primary key, CELL_CODE, which is a VARCHAR.
Each CELL has many (I think at least four, I don't think you can have triangular CELLS, but doesn't matter anyways) SIDES. A SIDE can be logically identified by its CELL, and to make matters ridiculously difficult, each SIDE has to be numbered by its CELL, like so:
CELLS:
CELL_CODE
1
2
SIDES:
SEQUENCE_NUMBER CELL_CODE
1 1
2 1
3 1
1 2
2 2
3 2
Now, each SIDE has its CONNECTION_PINS. CONNECTION_PINS is also uniquely identified by SIDES, which are basically numbered in a similar manner:
CELLS:
CELL_CODE
1
2
SIDES:
SEQUENCE_NUMBER CELL_CODE
1 1
2 1
3 1
1 2
2 2
3 2
CONNECTION_PINS:
SEQUENCE_NUMBER SIE_SEQUENCE_NUMBER CELL_CODE
1 1 1
2 1 1
1 2 1
2 2 1
1 3 1
2 3 1
1 1 2
2 1 2
1 2 2
2 2 2
1 3 2
2 3 2
I tried to explain the numbering issue we have here: Data model - PRIMARY KEY numbering issue, but yeah, I didn't really explain it the way it should be explained ...
Now, we have one final entity, which is where the Arc comes in: CONNECTIONS. CONNECTIONS has 2 CONNECTION_PINS: one for START_FROMand one for END_OF. Now, logically seen the start pin can't be the end pin as well, for a given connection. And that's our struggle. Basically, this shouldn't be allowed:
CELLS:
CELL_CODE
1
2
SIDES:
SEQUENCE_NUMBER CELL_CODE
1 1
2 1
3 1
1 2
2 2
3 2
CONNECTION_PINS:
SEQUENCE_NUMBER SIE_SEQUENCE_NUMBER CELL_CODE
1 1 1
2 1 1
1 2 1
2 2 1
1 3 1
2 3 1
1 1 2
2 1 2
1 2 2
2 2 2
1 3 2
2 3 2
CONNECTIONS:
(you shouldn't be able to put this in …)
CPI_SEQNUM_START SIE_SEQNUM_START CELL_CODE_START CPI_SEQNUM_END SIE_SEQNUM_END CELL_CODE_END
1 1 1 1 1 1
Now, this is basically the ERD for this part:
ERD with barred relationships and the arc-relationship in question
and this is the physical model:
I basically thought a simple CHECK might do (CHECK (CPI_SEQNUM_START <> CPI_SEQNUM_END AND CELL_CODE_START <> CELL_CODE_END AND SIE_SEQNUM_START <> SIE_SEQNUM_END) ), but that prevented us from inserting anything somehow … Any advice?
Your approach was correct to use a CHECK constraint. Your logic for the constraint was wrong though. You need an OR condition. Only one of the three fields needs to be different.
CPI_SEQNUM_START <> CPI_SEQNUM_END OR
CELL_CODE_START <> CELL_CODE_END OR
SIE_SEQNUM_START <> SIE_SEQNUM
... assuming all three fields are not nullable.