circular relationship

roustabout · #1 (**permalink**) 01-30-05, 20:41

Hi all,

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

Thanks

Pat Phelan · #2 (**permalink**) 01-30-05, 21:53

Poor design?

-PatP

Vmusic · #3 (**permalink**) 01-31-05, 00:10

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

Vmusic · #4 (**permalink**) 01-31-05, 00:11

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

roustabout · #5 (**permalink**) 01-31-05, 01:25

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.

certus · #6 (**permalink**) 01-31-05, 06:17

Post the question.

roustabout · #7 (**permalink**) 01-31-05, 19:37

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

certus · #8 (**permalink**) 01-31-05, 22:12

Create the tables and look at the foreign keys. Do you notice any peculiarities?

roustabout · #9 (**permalink**) 02-01-05, 03:52

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;

certus · #10 (**permalink**) 02-01-05, 05:12

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.

certus · #11 (**permalink**) 02-01-05, 06:11

Consider this as a solution:

Internet Services

Web Development

Digital Marketing

Consumer Tech