Re: Newbie to RDBMS, Informix...E/R modeling question
David Carmean wrote:
> [...]
> So say I create these tables with the following SQL fragments:
>
> create table name
> (
> rec_num serial,> [...]
> primary key (rec_num)
> );
>
> create table address
> (
> addr_idx serial,> [...]
> rec_num integer,
> primary key (addr_idx),
> foreign key (rec_num) references name (rec_num)
> );
> [...]
> BUT...if I do as suggested in the tutorial, and move the foreign keys
> to a new base relation which is the intersection of the two original
> relations, what technique can I use to populate the "address" table
> then? How do I associate unordered records in the two original base
> relations "name" and "address" with each other, when there are no longer
> any foreign keys to use? I mean, how do I create/derive the
> intersection when one of the tables is still empty?
>
> I must be missing some concepts still.
> [...]
You got pretty close. Your problem arose when adding address.recnum.
Here's an example of using a relation (name_address) to implement a
many-many relationship between two other relations (name and address).
create table name
(
rec_num serial,
/* non-key columns not shown */
primary key (rec_num)
);
create table address
(
addr_idx serial,
/* non-key columns not shown */
primary key (addr_idx)
);
create table name_address
(
name_id integer,
addr_id integer,
primary key (name_id, addr_id),
foreign key (name_id) references name (rec_num),
foreign key (addr_id) references address (addr_idx)
);
--
Karen A. Morrissey kxmorr4@uswest.com (USW business)
US West Communications kmorris692@aol.com (other business & personal)
1801 California (on assignment from Analysts International)
Suite 310
Denver, CO 80202
303-965-8473