Formal database design - multiplicity in ternary relationship

Gąska

Trigger warning: college assignment.

I'm designing a database of car races. I have the following relation: a single driver can start in multiple races, and a single race can have multiple drivers, and a single driver in a single race can drive only one car, and a single car in a single race can be driven by only one driver.

In SQL, it would look like this:

CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL UNIQUE,
    PRIMARY KEY (race_id, driver_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)
)

But how to write it down as a formal relation (say, in ERD diagram)? Would it be a singe ternary relationship, two/three binary relationships, or a single binary relationship with car as an attribute? What would be the multiplicities on each end of relation(s)? Note that it's for school, so it must not only be understandable - it has to be correct.

PJH

@gąska said in Formal database design - multiplicity in ternary relationship:

have the following relation: a single driver can start in multiple races, and

You a word or phrase there I think. Also:

PRIMARY KEY (race_id, driver_id),

?

And, corrected or not, this table appears to permit multiple 'drivers' to race the same car in the same 'race.' :

Is that the intention?

PJH

@gąska said in Formal database design - multiplicity in ternary relationship:

What would be the multiplicities on each end of relation(s)?

Anyway, the implication appears to be three one-to-many relationships from this table to three others - i.e. what the FOREIGN KEY lines are telling you.

Unperverted Vixen

I think you're missing a unique constraint on race_id and car_id - you can't have the same car driven by multiple drivers in the same race.

Assuming that's in place, this seems like it would be a ternary relationship to me.

Cardinality is trickier. I think they're all going to be 1 Race/Driver/Car to N Entries - even though the combination of the three can resolve to exactly one Entry, you'll still have many Entries per Race, many Entries per Driver, and many Entries per car.

Gąska

@pjh said in Formal database design - multiplicity in ternary relationship:

@gąska said in Formal database design - multiplicity in ternary relationship:

have the following relation: a single driver can start in multiple races, and

You a word or phrase there I think. Also:

PRIMARY KEY (race_id, driver_id),

?

Fixed.

And, corrected or not, this table appears to permit multiple 'drivers' to race the same car in the same 'race.' :

It seems it's now you a code. Anyway, no, it's not the intention. Also, what do you mean? Is thi UNIQUE constraint not enough to make it unique?

PJH

@gąska said in Formal database design - multiplicity in ternary relationship:

And, corrected or not, this table appears to permit multiple 'drivers' to race the same car in the same 'race.' :

It seems it's now you a code. Anyway, no, it's not the intention. Also, what do you mean? Is thi UNIQUE constraint not enough to make it unique?

Ah - missed that. So each car may only appear once in the table, ever, no matter how many races or drivers you have?

Gąska

@pjh said in Formal database design - multiplicity in ternary relationship:

@gąska said in Formal database design - multiplicity in ternary relationship:

What would be the multiplicities on each end of relation(s)?

Anyway, the implication appears to be three one-to-many relationships from this table to three others - i.e. what the FOREIGN KEY lines are telling you.

The thing is, I have to make tables out of the relations I write down - I know exactly what the final result should be (the table above), and have to figure out how to write down relation exactly so they unfold into that table. I'm pretty sure three unrelated relations don't do that - especially since driver-race is many-to-many.

Gąska

@pjh said in Formal database design - multiplicity in ternary relationship:

@gąska said in Formal database design - multiplicity in ternary relationship:

And, corrected or not, this table appears to permit multiple 'drivers' to race the same car in the same 'race.' :

It seems it's now you a code. Anyway, no, it's not the intention. Also, what do you mean? Is thi UNIQUE constraint not enough to make it unique?

Ah - missed that. So each car may only appear once in the table, ever, no matter how many races or drivers you have?

I fucked up. Obviously no. How do I fix it so that race-driver and race-car-driver tuples are unique, but driver-car and race-car aren't?

PJH

@gąska said in Formal database design - multiplicity in ternary relationship:

How do I fix it so that race-driver and race-car-driver tuples are unique, but driver-car and race-car aren't?

You can't in one table.

And you still seem to want to allow

cars to appear more than once in a single race
drivers to appear more than once in a single race

The following does

cars may only appear at most once in a race
drivers may only appear at most once in a race
cars may be driven by different drivers in different races

All I've done is add an extra unique key:

CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL UNIQUE,
    PRIMARY KEY (race_id, driver_id),
    UNIQUE KEY `race_car` (race_id, car_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)
)

That presumes my three predicates are what you're actually after, which I'm not too sure about..

Gąska

@pjh let's forget my previous posts and just assume I meant the only sensible thing - many drivers, many races, one car per driver per race. Will this work like I hope it would?

CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL,
    PRIMARY KEY (race_id, driver_id),
    UNIQUE KEY (race_id, driver_id, car_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)
)

PJH

@gąska said in Formal database design - multiplicity in ternary relationship:

@pjh let's forget my previous posts and just assume I meant the only sensible thing - many drivers, many races, one car per driver per race. Will this work like I hope it would?
CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL,
    PRIMARY KEY (race_id, driver_id),
    UNIQUE KEY (race_id, driver_id, car_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)
)

No. Yours will still allow a car to appear more than once per race (with different drivers who aren't driving other cars in that race.)

Gąska

@pjh How about this:

CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL,
    PRIMARY KEY (race_id, driver_id),
    UNIQUE KEY (race_id, car_id),
    UNIQUE KEY (race_id, driver_id, car_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)

Unperverted Vixen

@Gąska That will work, though UNIQUE KEY (race_id, driver_id, car_id) is redundant. (The primary key enforces that the Entry has a unique Race/Driver combination, and the other unique key a unique Race/Car association.)

blakeyrat

Warning: incoming Blakey-opinions.

If your class is teaching you SQL in text instead of using something like SQL Server Management Studio's data diagrams:

Then they are doing you a huge disservice. The correct way to design a database is to draw your ERD on the screen in rectangles and then hit the "save" button and then your database schema appears as if by magic. It's not 1979 anymore.
Your Entries table doesn't have its own key. I'm a firm believer that every SQL table should have a primary key, even if its purpose is just to link other tables. (As you can see above in the SteamGameSteamTag table.) Caution: some ORMs (like Entity Framework) don't like this.

PJH

@gąska said in Formal database design - multiplicity in ternary relationship:

@pjh How about this:

CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL,
    PRIMARY KEY (race_id, driver_id),
    UNIQUE KEY (race_id, car_id),
    UNIQUE KEY (race_id, driver_id, car_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)

Except for the redundant key using all three, that's exactly what I proposed earlier.

PleegWat

@pjh said in Formal database design - multiplicity in ternary relationship:

Ah - missed that. So each car may only appear once in the table, ever, no matter how many races or drivers you have?

As I read the formula 1 articles in the paper, that seems pretty realistic.

Gąska

@blakeyrat duly noted. Now, what about my problem? Because it doesn't go away just because it's drawings instead of text.

Gąska

@pjh said in Formal database design - multiplicity in ternary relationship:

@gąska said in Formal database design - multiplicity in ternary relationship:
@pjh How about this:
CREATE TABLE Entries(
    race_id Integer NOT NULL,
    driver_id Integer NOT NULL,
    car_id Integer NOT NULL,
    PRIMARY KEY (race_id, driver_id),
    UNIQUE KEY (race_id, car_id),
    UNIQUE KEY (race_id, driver_id, car_id),
    FOREIGN KEY (race_id) REFERENCES (Races.id),
    FOREIGN KEY (driver_id ) REFERENCES (Drivers.id),
    FOREIGN KEY (car_id) REFERENCES (Cars.id)
Except for the redundant key using all three, that's exactly what I proposed earlier.

Ah, right. Sorry. Was reading on a short break between classes and didn't quite understand.

So now that we know what exactly we're talking about. Any idea how to draw a diagram of this?

Unperverted Vixen

@blakeyrat said in Formal database design - multiplicity in ternary relationship:

Your Entries table doesn't have its own key. I'm a firm believer that every SQL table should have a primary key, even if its purpose is just to link other tables.

The table in question has a primary key. Do you mean a surrogate key?

Gąska

@unperverted-vixen IT IS NOT ABOUT THE TABLES GODDAMMIT I WANT TO MAKE RELATIONS

Unperverted Vixen

@gąska For that I defer to my earlier answer: ternary relationship, all 1 to N from other tables to Entries.

@blakeyrat actually had what I thought was an interesting idea: set up the tables and see what diagram the software generates for you.

MathNerdCNU

@gąska https://www.youtube.com/watch?v=paCKFwIKsJw

PJH

@unperverted-vixen said in Formal database design - multiplicity in ternary relationship:

@gąska For that I defer to my earlier answer: ternary relationship, all 1 to N from other tables to Entries.

This. Your table has three foreign keys. Each record has three one to many relationships to the underlying tables.

0_1523220990326_3925e19f-5575-4e0f-8f24-2b3876b58e2f-image.png