Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2003
    Posts
    14

    Foreign keys in an ERD

    I'm quite new to databases and I feel very confused as to where the foreign keys should go.
    If for example I have an entity called "Sightings" with PK as sightingId which records types of birds and where they were seen (gridReference) and then an entity called location with gridReference as PK and then the actual name of the location. Shall I put a foreign key sightingId in the location entity or shall I put a foreign key gridReference in the sighting entity?

    Thanks
    ariane

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Foreign keys in an ERD

    The key concept here is "one to many". Each Sighting occurs at ONE location, but each Location may be where MANY Signtings occur.

    Now, if you implemented the FK by creating a column called sightingId in Location you could only record ONE Sighting at each Location - wrong!

    Sightings
    ID, Date, Bird, etc.
    1, 01-Jan-2003, Sparrow
    2, 02-Jan-2003, Duck

    Locations
    gridRef, Name, sightingID
    123456, Norwood Lake, 1 <-- can't record any more sightings here!

    But if you implement the FK correctly by putting gridReference in the Sightings table then you can have many Sightings records for a Location:

    Locations
    gridRef, Name
    123456, Norwood Lake

    Sightings
    ID, gridRef, Date, Bird, etc.
    1, 123456, 01-Jan-2003, Sparrow
    2, 123456, 02-Jan-2003, Duck

  3. #3
    Join Date
    Jan 2003
    Posts
    14
    Thank you very much! So I shoudn't think in terms of parent-child relationship but in terms of multiplicity?
    ariane

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by abogain
    Thank you very much! So I shoudn't think in terms of parent-child relationship but in terms of multiplicity?
    Well, parent-child is OK as long as you remember that a mother can have many children, but a child can only have one mother. So if you had tables MOTHER and CHILD you would have to put a mother_id in the CHILD table, not a child_id in the MOTHER table!

    Then of couse there are many-to-many relationships like: an EMPLOYEE works on many PROJECTS; each PROJECT is worked on by many EMPLOYEES. To handle this you would need to create an "intersection" table like EMPLOYEE_PROJECTS( emp_id, proj_id ). This would have an FK to EMPLOYEES and an FK to PROJECTS.

  5. #5
    Join Date
    Jan 2003
    Posts
    14
    Ok, then if in my original question location is a child of Sightings as a birdwatcher records sightings and sightings occur in location which are part of an Area then I should put the FK of Sightings in Location and the FK of location in Area but you showed me that it is not possible since many sightings can occur in one location and one area has many location therefore sightings should have the PK of location and location should have the PK of area. And i can't interpret Sightings as being the child of location as sightings is also linked to the entity type of birds which describes the general characteristics of a particular class of birds and a child cannot have many parents. Oh god!!!
    ariane

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    No, you are getting confused here - maybe the "parent-child" terminology is a bad idea after all! When I said a child can only have one MOTHER, I didn't mean that a "child" table can only have a foreign key to one "parent" table. I meant that each foreign key can only point to one record from the parent table. To take the CHILD/MOTHER analogy further (too far?) a child usually has TWO parents, a FATHER and a MOTHER. So we could have the following tables:
    MOTHER(mother_name PK);
    FATHER(father_name PK);
    CHILD(child_name PK, mother_name FK to MOTHER, father_name FK to FATHER);

    As you can see, the CHILD table has 2 "parent" tables (MOTHER and FATHER). However, each foreign key only allows 1 value, i.e.:
    A MOTHER can have MANY Children
    A FATHER can have MANY Children
    A CHILD can have ONLY ONE Mother
    A CHILD can have ONLY ONE Father

    A common way to show parent:child (i.e. many:one) relationships is with an ERD (Entity Relationship Diagram). This uses lines with symbols to indicate the ONE and MANY ends, e.g. PARENT ----< CHILD
    (The < represents a symbol called a "crow's foot" that indicates "many")
    This can also be shown as CHILD >--- PARENT.

    Your data looks like this:

    BIRDWATCHER ---< SIGHTING >--- LOCATION >--- AREA

    So the tables would be:
    AREA(area_id PK,...);
    LOCATION(loc_id PK, area_id FK, ...);
    BIRDWATCHER(bw_id PK, ...);
    SIGHTING( sighting_id PK, bw_id FK, loc_id FK, ...);

    You seem to be wanting to put all these backwards, which is why you have a problem!

  7. #7
    Join Date
    Jan 2003
    Posts
    14
    Dear Tony,

    thank you very much, I understand it now!
    Thank you again,
    Ariane
    ariane

  8. #8
    Join Date
    Jan 2003
    Posts
    14
    I have another question!

    Each type of bird has an identification number and attributes describing its characteritistics. At the same time each bird belongs to a particular class that keeps details about the common characteristics of several types of birds (for example the migrating ones). Is the best solution in an ERD to have a link between Sightings and types of birds and then between types of birds and Classbird or should Sightings be linked to both as a birdwatcher will report both?

    Thank you!
    Ariane
    ariane

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by abogain
    I have another question!

    Each type of bird has an identification number and attributes describing its characteritistics. At the same time each bird belongs to a particular class that keeps details about the common characteristics of several types of birds (for example the migrating ones). Is the best solution in an ERD to have a link between Sightings and types of birds and then between types of birds and Classbird or should Sightings be linked to both as a birdwatcher will report both?

    Thank you!
    Ariane
    I would expect to see Class ---< Bird ---< Sighting
    The class information for the sighting is derived via the bird.

    Unless you want to be able to record a sighting of a Class of bird without recording the specific Bird?

  10. #10
    Join Date
    Jan 2003
    Posts
    14
    I thought it made more sense the way you indicate but if the birdwatcher reports both then does it not mean that class id should appear as an attribute in Sighting, in which case a link should be made to class as well?
    ariane

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by abogain
    I thought it made more sense the way you indicate but if the birdwatcher reports both then does it not mean that class id should appear as an attribute in Sighting, in which case a link should be made to class as well?
    Not sure what you mean. Do you mean that the birdwatcher reports a sighting of a great-spotted goose or whatever, which is a migrating bird, so he should record both that Bird="greater-spotted goose" and that Class="migrating"?
    No, that would be redundant and wrong design: since greater-spotted geese ARE migrating birds, there is no need to restate that fact in every Sighting. That breaks 2nd Normal Form. It also introduces scope for data corruption: what if he records a greater-spotted goose but records the wrong class?

    Or do you mean he may want to just report a sighting of a migrating bird, without recording exactly what kind of bird it is? Maybe because he doesn't recognise it? In that case you may have an "arc" relationship: each Sighting is for one Bird OR for one Class (not both). So your Sighting data might look like:

    ID, Location, Bird, Class, ....
    1, 123456, 111, NULL
    2, 123456, NULL, 44

    This shows 2 sightings at location 123456:
    1: a bird of type 111 (greater-spotted goose)
    2: an unidentified bird of class 44 (migrating bird)

  12. #12
    Join Date
    Jan 2003
    Posts
    14
    At the moment my entities for sightings, type of birds and class of birds are the following;

    Sightings: SightingId (Pk), OsRef (FK - for the location), Sighting date, Sighting time, BirdId(FK)

    Type of birds: BirdId (PK), description, ClassNumber (FK)

    Class of bird: ClassNumber (PK), description.

    If I want to be able to produce a report showing all the sightings of a particular Class of birds (like the migrating ones), are these attributes enough?

    Thanks again!
    ariane

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by abogain
    At the moment my entities for sightings, type of birds and class of birds are the following;

    Sightings: SightingId (Pk), OsRef (FK - for the location), Sighting date, Sighting time, BirdId(FK)

    Type of birds: BirdId (PK), description, ClassNumber (FK)

    Class of bird: ClassNumber (PK), description.

    If I want to be able to produce a report showing all the sightings of a particular Class of birds (like the migrating ones), are these attributes enough?

    Thanks again!
    Yes, that looks fine. Your report in SQL would be something like:

    SELECT s.OSRef, s.SightingDate, s.SightingTime, b.description
    FROM Sightings s, Birds b, Class c
    WHERE s.birdId = b.BirdId
    AND b.ClassNumber = c.ClassNumber
    AND c.description = 'migrating';

  14. #14
    Join Date
    Jan 2003
    Posts
    14
    Thank you!

    So if I transposed the birdwatching design to another form of hobby like trainspotting where the only difference would be that in addition to reporting the sighting of a locomotive and its location one also reports the number of carriages, the type of carriage and the number of 1st class/2nd class (which depends on the routes the train has been allocated to), would the following be ok:

    Sightings linked to locomotive itself linked to class of locomotive
    Sightings linked to location
    Sightings linked to Train type whose composite PK would be TrainId and RouteId.

    Thanks
    ariane

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by abogain
    Thank you!

    So if I transposed the birdwatching design to another form of hobby like trainspotting where the only difference would be that in addition to reporting the sighting of a locomotive and its location one also reports the number of carriages, the type of carriage and the number of 1st class/2nd class (which depends on the routes the train has been allocated to), would the following be ok:

    Sightings linked to locomotive itself linked to class of locomotive
    Sightings linked to location
    Sightings linked to Train type whose composite PK would be TrainId and RouteId.

    Thanks
    Probably. Exactly which table the information about number of carriages etc. goes into depends on whether it is dependent purely on the route, or whether it is more variable (e.g. one day there could be a sighting of a Route 11 train with 6 carriages, another day a Route 11 train with 8 carriages).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •