Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: optionality and cardinality moving from from ERD to TABLES.

Re: optionality and cardinality moving from from ERD to TABLES.

From: Heinz Huber <hhuber_at_racon-linz.at>
Date: Thu, 27 Sep 2001 08:09:45 +0200
Message-ID: <3BB2C2A9.CA4786CE@racon-linz.at>


Andrea Fare' wrote:
>
> I am a beginner in database design and I am experiencing some problems
> trying to translate with accuracy an ERD into
> code.
>
> Let's say I have two entities CAR and PERSON and the following
> relationships exist between them:
>
> each CAR MUST be property of one and only one PERSON.
> each PERSON MAY own one or more CARs.
>
> Now as soon as I start creating the tables it seems logical to me to put a
> foreign key
> in the CAR table that references the primary key in the PERSON table.
> Because each CAR MUST be property of one and only one PERSON it seems also
> logical that the foreign key in the CAR table will not be allowed to have
> NULL values,but how do I enforce the condition that each PERSON MAY HAVE a
> car, or in other words what do I have to put in my tables to make a
> distinction between this situation and a situation in which each PERSON MUST
> have a CAR?( this last sentence could be translated in to a condition that
> says that each entry in PERSON TABLE must be referenced at least one time
> in the foreign key field of the CAR table).
>
> Should this be done at the table (creation) level and how?
> Any good URLs to further investigate these concepts

As I read it, the second line above (PERSON MAY ...) is no restriction but only a statement that there is no restriction on the PERSON table. The condition that each PERSON MAY HAVE a car will always be true, regardless of whether it has one, none or many cars.
Therefore, you should be fine with what you have done (FK in CAR on PERSON).

For EACH PERSON MUST have a car, you would have to put a table constraint on the PERSON table requiring a corresponding entry in the CAR table (just as you write above ;-).

hth,
Heinz Received on Thu Sep 27 2001 - 01:09:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US