sql query/ procedure for finding six degrees of Kevin Bacon Numbers

Question

I'm trying to design a and entirely query/ procedure driven "Kevin Bacon number finder" in SQL Server and am not even sure about where to start. If anyone can give some help, or point me to something similar, I would be appreciative as I have searched and am unable to find something similar to what I need that doesn't rely on using OO programming.

The "Kevin Bacon number" refers to the "Six Degrees Of Kevin Bacon" where actors can be linked through his or her film roles to actor Kevin Bacon within six steps. However I need to find the number of steps in the process even if exceeding 6 steps.

Six_Degrees_of_Kevin_Bacon

The schema that I have to use is:

ACTOR (id, fname, lname, gender)
MOVIE (id, name, year, rank)
DIRECTORS (id, fname, lname)
CASTS (pid, mid, role)
MOVIE_DIRECTORS (did, mid)
GENRE (genre, mid)

With CASTS.pid refers to ACTOR.id, CASTS.mid refers to MOVIE.id.

Yes. that's why I'm asking for help, not just the answer... Unless someone wants to give that, that would be fine too. — TheBeeKeeper, Apr 11 '12 at 21:30
I'd be inclined to try writing a query if you had table create statements and a bunch of sample data insert commands that I could cut/past and start working with. — KM., Apr 12 '12 at 13:10

score 0 · Answer 1 · answered Apr 11 '12 at 21:32

Any chance you can extend the schema for actor and director to add a kbnumber field for their Kevin Bacon number? Then you could easily create a stored procedure along the lines of

update a 
set a.kbnumber = EXPR(a2.kbnumber + 1) 
from top 1 actor a inner join
movie m on a.mid = m.id inner join
actor a2 on a2.mid = m.id
order by a2.kbnumber ASC
where a.kbnumber is null or a.kbnumber > a2.kbnumber + 1

update a
set a.kbnumber = EXPR(a2.kbnumber + 1) 
from top 1 actor a inner join
movie m on a.mid = m.id inner join
directors a2 on a2.mid = m.id
order by a2.kbnumber ASC
where a.kbnumber is null or a.kbnumber > a2.kbnumber + 1

update a 
set a.kbnumber = EXPR(a2.kbnumber + 1) 
from top 1 directors a inner join
movie m on a.mid = m.id inner join
actor a2 on a2.mid = m.id
order by a2.kbnumber ASC
where a.kbnumber is null or a.kbnumber > a2.kbnumber + 1

update a
set a.kbnumber = EXPR(a2.kbnumber + 1) 
from top 1 directors a inner join
movie m on a.mid = m.id inner join
directors a2 on a2.mid = m.id
order by a2.kbnumber ASC
where a.kbnumber is null or a.kbnumber > a2.kbnumber + 1

Run this procedure until no more kbnumbers are being updated. Any remaining nulls have no relation traceable back to Kevin Bacon

sql query/ procedure for finding six degrees of Kevin Bacon Numbers

1 Answers1