Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    14

    Question circular relationship

    Hi all,

    Why the cycle in relationship between CREW and SHIP is true?

    Thanks
    Attached Thumbnails Attached Thumbnails diagram.JPG  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Poor design?

    -PatP

  3. #3
    Join Date
    Dec 2004
    Posts
    54

    Relationships

    Hi,
    Simply ask yourself what the relationship is.

    A crew (person) could work, over time, on one or many ships.

    A ship could have one or many crew (people) working on it.

    Simple?
    Vmusic

  4. #4
    Join Date
    Dec 2004
    Posts
    54

    Relationships

    I couldn't see the cardinality very well.....but I thought that was a many to many. That's not recursive...

  5. #5
    Join Date
    Jan 2005
    Posts
    14
    Quote Originally Posted by Vmusic
    Hi,
    Simply ask yourself what the relationship is.

    A crew (person) could work, over time, on one or many ships.

    A ship could have one or many crew (people) working on it.

    Simple?
    Vmusic
    hi all, this is actually a past year exam question.

    Here what I understand:

    A ship is assigned to one crew which is the captian. The same captain can be a captain on one or many ships.

    One crew is assigned to one ship

    haha this is confusing... am i correct?

    I have implemented the tables in Mysql.

    I found out that i have to create the crew table without declaring ship_id foreign key first. Otherwise I can't input some data into the tables. After I have completed entering the data, I used the alter table command to ship's foreign key to crew's tables.
    Last edited by roustabout; 01-31-05 at 02:32.

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Post the question.

  7. #7
    Join Date
    Jan 2005
    Posts
    14
    Quote Originally Posted by certus
    Post the question.
    1. Why the circular relationship is true (refer to the diagram).
    2. Create Tables using the sql command

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Create the tables and look at the foreign keys. Do you notice any peculiarities?

  9. #9
    Join Date
    Jan 2005
    Posts
    14
    Quote Originally Posted by certus
    Create the tables and look at the foreign keys. Do you notice any peculiarities?
    Yes....

    Code:
    create table crew
    (c_id int not null,
    c_name varchar(50),
    c_dob date,
    s_id int,
    primary key(c_id),
    foreign key(s_id) references ship(s_id) on delete cascade;
    );

    Code:
    create table ship
    (s_id int not null,
    s_name varchar(50),
    c_id int,
    primary key(s_id),
    foreign key(c_id) references crew(c_id) on delete cascade
    );

    IF I were to create the crew table first, it gave an error because of the s_id fk for ship table havent been created . THe problem will be similar if I start creating the ship table first. haha

    One way to do it is... to create the ship table without the s_id foreign key and later on after inserting the dummy data, I use this command to add the foreign into the crew table.

    Code:
    alter table crew
    add foreign key(s_id) references ship(s_id) on delete cascade;

  10. #10
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You have discovered why the circular reference is true.

    You've been presented with a design flaw and you found it. You don't have to beat it.

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Consider this as a solution:
    Attached Thumbnails Attached Thumbnails untitled.JPG  

Posting Permissions

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