Re: Entity modelling - newbie question.

Borris,

There are a few things that can be looked at here with entity modelling.
I am going to consider foreign and primary keys and incorporate weak entity relationships to try and explain the model.

Suppose we have an organisation with employees, departments, and managers. Departments can only have one manager but many employees can be manage many departments.

• -------- -------------
  • - - - - -
  • emp - N:N - mngs - 1:N - deprt -
  • - - - - -
• -------- -------------

so this is how it might look(excuse the mess). The realationship between emp and deprt is manages and here is one ddl:

create table manages ( ssn char(11), did integer, since DATE, PK (did), FK (ssn) references employees, FK (did) references Departments)

All the foreign keys says is that in order to exist in this table the value must be in the other table. Incorporating it with the PK, since the PK of one table references another PK than there can only be a 1:N relationship. In this case manages' PK = did and deprt PK = did. In the case of emp PK = sin and is just a key in mngs, then N:N relatioship.

I think this answers your question. (Mgr(FK) references Empl_Num(PK) mean? N:N)
becuase Mgr is not the PK of that table and Empl_Num is a PK of its table and it is referenced.

1:1 is when both PKs are referenced.

WEAK ENTITY RELATIONSHIPS:
If we force participation with a not null it then becomes a weak entity relationship. At that point, collapse the table dprt and manages into one table with an attribute not null.

I hope this helps.

Eric

borrisking_at_hotmail.com (borris) wrote in message news:<3811f825.0201121221.7158e7fb_at_posting.google.com>...
> Hi there,
> I'm new to entity modelling and am currently looking at an example of a
> database schema that is not consistent with what I thought previously.
>
> It is from "SQL the complete reference"
>
> Offices: SalesReps:
>
> Office(PK) Empl_Num(PK)
> City Name
> Mgr(FK) Age
> Target Rep_Office(FK)
> Sales Title
> Hire_Date
>
> --------------------------
> PK=Primary Key
> FK=Foreign Key
> --------------------------
>
> the entity constraints are:
> Rep_Office(FK) references Office(PK)
> and Mgr(FK) references Empl_Num(PK)
> -------------------------------------
>
> I can see the basic thing that : Rep_Office(FK) references Office(PK)
> models the fact that there are 'many' Salesreps associated with any 'one'
> office.
>
> What does: Mgr(FK) references Empl_Num(PK) mean?
>
> My understanding is that the Foreign Key models the
> child and the Primary Key models the parent in the
> above, and thus, it is modeling the fact that every
> SalesRep has many managers whereas this should be
> the other way around, and even if it was the other way
> around i.e that Empl_Num somehow referenced Mgr in a
> FK to PK way(to model the fact that many SalesReps have one mgr),
> then this would be nonsense looking at the schema presented above.
>
>
> Can someone please shed some light on this for me?
>
> thanks in advance ,
> Borris.
Received on Mon Jan 21 2002 - 16:57:08 CET