1. Registered User
Join Date
Jan 2005
Posts
14

## circular relationship

Hi all,

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

Thanks

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

-PatP

3. Registered User
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. Registered User
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. Registered User
Join Date
Jan 2005
Posts
14
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. Registered User
Join Date
Dec 2003
Location
Posts
710
Post the question.

7. Registered User
Join Date
Jan 2005
Posts
14
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. Registered User
Join Date
Dec 2003
Location
Posts
710
Create the tables and look at the foreign keys. Do you notice any peculiarities?

9. Registered User
Join Date
Jan 2005
Posts
14
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

10. Registered User
Join Date
Dec 2003
Location
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. Registered User
Join Date
Dec 2003
Location