What makes somthing many-to-one relationship?

[c***@us.ibm.com]

Hello everyone.

I was confused on what makes a many-to-one relationship. For instance I modeled this .ddl db2 code in Rational Software Architect and its showing its a many to 1 relationship based on the code I'm going to paste.

The reason Im' asking this is because I'm writing a program and I need to show a many to one relationship so when I model the code it will also appear.

Here is the .ddl:


CREATE TABLE NODE_OBJ (
HOSTNAME VARCHAR(250),
MGMT_IPADDR VARCHAR(60),
NETVIEW_ID CHAR(32),
NODE_ID CHAR(32) NOT NULL,
OBJ_ID DECIMAL(20 , 0),
SELECTION_NAME VARCHAR(120),
STATUS INTEGER,
TCPIP_JOB_NAME VARCHAR(25),
SYSPLEX_NAME VARCHAR(25),
SYSTEM_NAME VARCHAR(25),
IS_MANAGED INTEGER,
USER_CREATED INTEGER,
IS_DELETED TIMESTAMP,
IS_DHCP CHAR(1)
)
DATA CAPTURE NONE ;

CREATE TABLE ITM_SERVICES (
NODE_ID CHAR(32) NOT NULL,
SVC_NAME VARCHAR(120) NOT NULL
)
DATA CAPTURE NONE ;

ALTER TABLE NODE_OBJ ADD CONSTRAINT PK_NODE_OBJ PRIMARY KEY (NODE_ID);

ALTER TABLE ITM_SERVICES ADD CONSTRAINT PK_ITM_SERVICES PRIMARY KEY (NODE_ID, SVC_NAME);

ALTER TABLE NODE_OBJ ADD CONSTRAINT NODE_OBJ_NETVIEW_ID_MAP_FK FOREIGN KEY (HOSTNAME)
REFERENCES NETVIEW_ID_MAP (HOSTNAME)
NOT ENFORCED;

ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
REFERENCES NODE_OBJ (NODE_ID)
NOT ENFORCED;

Now here is the image that is produced from that code:
Loading Image...

s you can see its showing:
NODE_OBJ is having a 1 to many relationship on the table: ITM_SERVICES

But through script, what makes somthing a 1 to 1 relationship, a 1 to many relationship or a many to many relationship?

I see that NODE_OBJ's primary key is NODE_ID and I also see that ITM_SERVICES is using NODE_ID as its foriegn key, so there is a relationship, but what is making that relationship a many?

Thanks for the help!


Note: I'm using DB2 v8.2

[Mark A]

Post by c***@us.ibm.com
I see that NODE_OBJ's primary key is NODE_ID and I also see that
ITM_SERVICES is using NODE_ID as its foriegn key, so there is a
relationship, but what is making that relationship a many?
Thanks for the help!

DB2 does not enforce the kind of rules that you are talking about, and
cannot guarantee that something is one to many, instead one to one. A
mandatory one to may (instead of one to one) is hard to enforce since the
rule would be violated as soon as you insert the first row to the dependent
table.

However, from a normalization point of view (which applies to all relational
databases), your design allows a one to many relationship by way of a FK on
the dpendent table that refers back to the PK on the other table. If it
were a one to one relationship, and the database design were normalized,
then both tables should share the same PK.

In order to have a many to many relationship between two tables, an
addtional relationship or junction table must be created that has the PK's
of each of the two tables concatenated together as its PK (PK of
relationship table).

[c***@us.ibm.com]

Thanks Mark!

So what your saying is this is where the many to 1 relationship is formed?


ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
REFERENCES NODE_OBJ (NODE_ID)



And if It just had the following:

ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)

Then it would be a 1 to 1 relationship?

But if this is the case, I'm alittle confused on the 1 to 1 relatinoship.

For instance:
ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)

How does ITM_SERVICES know that NODE_ID is the NODE_ID of NODE_OBJ and not some other table? Or does REFERENCES NODE_OBJ (NODE_ID) Tell ITM_SERVICES that the forgien key is infact the primary key of NODE_OBJ by the REFERENCES keyword?

Thanks again, very helpful information!

[Mark A]

Post by c***@us.ibm.com
Thanks Mark!
So what your saying is this is where the many to 1 relationship is formed?
ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
REFERENCES NODE_OBJ (NODE_ID)
ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
Then it would be a 1 to 1 relationship?
But if this is the case, I'm alittle confused on the 1 to 1 relatinoship.
ALTER TABLE ITM_SERVICES ADD CONSTRAINT ITM_SERVICES_NODE_OBJ_FK FOREIGN KEY (NODE_ID)
How does ITM_SERVICES know that NODE_ID is the NODE_ID of NODE_OBJ and not
some other table? Or does REFERENCES NODE_OBJ (NODE_ID) Tell
ITM_SERVICES that the forgien key is infact the primary key of NODE_OBJ by
the REFERENCES keyword?
Thanks again, very helpful information!

1. DB2 is a database, not a modeling tool.

2. If you have a one-to-one relationship, your data model is not normalized.
Sometimes it is OK to denormalize a physical design for performance reasons.
If you have a one-to-one relationship between two tables, the both have the
same exact PK, regardless of whether they have a FK.

[c***@us.ibm.com]

Thanks for the help!

I think I have enough info now to start coding!