3

I'm trying to translate a class diagram (school project of disc rental store) to sql, but I'm stuck on a generlization relationship.

I have the parent class of Disc, and child classes of Music, Movie and Game. How can I represent these in sql (oracle)?

What I've written so far is:

CREATE TABLE Disc
(
disc_id numeric(10) not null,
disc_title varchar2(50) not null,
disc_cost numeric(10) not null

CONSTRAINT disc_pk PRIMARY KEY (disc_id)
);

--extends Disc
CREATE TABLE Music
(
);

Yet I have no idea what to do with the Music table, or even if I'm going in the wrong direction.. or even if I'm using the wrong terminology :P

Any help would be appreactiated :)

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
mhorne
  • 243
  • 3
  • 10
  • See [this recent question][1] for a solution to this. [1]: http://stackoverflow.com/questions/7025416/how-to-guarantee-uniqueness-when-many-tables-has-a-11-relationship-with-the-same/7025717#7025717 – Tony Andrews Aug 11 '11 at 15:39

2 Answers2

4

Been there, done that.

You need a field in Disc to specify the type (Music, Movie, ...)

In Music (or Movie or...), you have to have the disc_id and then the Music specific fields.

When querying for Music, inner join Disc and Music via disc_id.

The type in Disc will hep you for Disc listing and filtering, when working in the Disc abstraction level.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
4

In entity relation modeling, this setup is modeled as superentity DISC and three subentities MUSIC DISC, MOVIE DISC and GAME DISC. And implementing these entities, you have the four classic options:

  1. Supertype implementation
  2. Implicit subtype implementation
  3. Explicit subtype implementation
  4. Arc-type implementation

Option 1: one table DISCS with a Disc_Type column with three allowable values 'MUSIC', 'MOVIE' and 'GAME'. And optional columns for all attributes and relationships of the three subentities. And some check constraints on those columns to ensure they only have a value when the Disc_Type column allows so.

Option 2: four tables, one for each entity, allowing discs that are of a unknown type. The tables representing the subentities have a foreign key to the DISCS table.

Option 3: three tables: MUSIC_DISCS, MOVIE_DISCS and GAME_DISCS, where all attributes belonging to the super entity are copied to the three tables.

Option 4: four tables where the DISCS table contains three optional foreign key columns to the otter three tables. Exactly one of those three foreign key columns must be filled.

There is no "right" way. You just have to choose one that best suits your needs.

Regards, Rob.

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55