Translating a optional/mandatory to tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Translating a optional/mandatory to tables

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-07 : 12:00:26

I am having trouble getting my head round participation for some scenarios. Consider the following:

River MUST flow through 1 or more cities.
City MUST have at least 1 river.

This is represented the pk of River being put as a fk in city AND (to represent that city must have a river), the foreign key does NOT allow nulls.

So how about...

River MAY flow through 0,1 or more cities. City MAY have ONLY 1 river.
This time we allow the foreign key in city to have NULLs, but how about the otherside, the pk of river. How do I represent the MAY here, and a pk value cannot be null.

Thanks

Drew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-07 : 18:41:37

If I read your case correctly, then having a NULLable FK on City to the PK of River enables a City to have 0 or 1 rivers. Therefore, I think you already solved it.

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 04:45:15

quote:
Originally posted by drewsalem

River MUST flow through 1 or more cities.
City MUST have at least 1 river.

You can't do this directly.
It is called circular reference.

Think about it. If you insert a city with a new river, it will fail because the river is not there.
If you insert a river with a new city, it will fail because the city is not there.
And you can't insert two records at the same time.

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-08 : 05:40:20

Peter, you're really confusing me now, as that wasn't the bit I had a prbolem with!! We'll assume that such occurances that you are refering to have already been populated and that referencial integrity is not enforced until after such occurances have been inserted.

So back to the other problem. Looking at the diagram below...

My understanding is that since the FK in the City table in Figure 1 is set to not allow nulls, then every river must have at least 1 city that it flows through. And, in the River table, since a primary key is unique and can never be null, a city will only ever have 1 river flowing through it.

Now in figure 2, we can say that a river MAY flow through 0,1 or more cities by allowing the foreign key in the city table to have nulls, which is the opposite of the above. But how do I represent the fact that a city MAY have a river flowing through it, as the Primary key of the river table can never be null.

Thanks

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 06:01:36

Confused?

quote:
Originally posted by drewsalem

River MUST flow through 1 or more cities.
City MUST have at least 1 river.

quote:
Originally posted by drewsalem

River MAY flow through 0,1 or more cities. City MAY have ONLY 1 river.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 06:04:28

And with the design above, how do you handle the case where two rivers floats through same city?

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-08 : 06:14:03

The MUST part (figure 1) is not the part I have a problem with. It would deviate from the purpose of this post if we were to dwell on the circular reference.

quote:
And with the design above, how do you handle the case where two rivers floats through same city?

The design isn't to be used in some real application, I just drew it up before I posted this to clarify the question... which is...

How is a 1:m with optional participation on both sides of the relationship translated into tables?

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 06:21:46

Do you understand circular reference?
You can't have constraints here.

How do you even check "River MUST flow through 1 or more cities"?

Make three tables. One for cities, one for rivers and one for the combination of the two.
Add a calculated column for the two first tables, summing up instances of the record id in the third table.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 06:34:45

[code]CREATE TABLE Rivers (RiverID INT IDENTITY(1, 1) PRIMARY KEY, RiverName VARCHAR(20))

INSERT Rivers
SELECT 'Amazon' UNION ALL
SELECT 'Nile'

CREATE TABLE Cities (CityID INT IDENTITY(1, 1) PRIMARY KEY, CityName VARCHAR(20))

INSERT Cities
SELECT 'Cairo' UNION ALL
SELECT 'SomewhereInBrazil' UNION ALL
SELECT 'NoRiverCity'

CREATE TABLE Third (CityID INT, RiverID INT)

INSERT Third
SELECT 1, 2 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2
GO

CREATE FUNCTION dbo.fnRiverCount
(
@RiverID INT
)
RETURNS INT
AS
BEGIN
RETURN
(
SELECT COUNT(*)
FROM Third
WHERE RiverID = @RiverID
)
END
GO

CREATE FUNCTION dbo.fnCityCount
(
@CityID INT
)
RETURNS INT
AS
BEGIN
RETURN
(
SELECT COUNT(*)
FROM Third
WHERE CityID = @CityID
)
END
GO

ALTER TABLE Cities
ADD CityCount AS dbo.fnCityCount(CityID)
GO

ALTER TABLE Rivers
ADD RiverCount AS dbo.fnRiverCount(RiverID)
GO

SELECT *
FROM Cities

SELECT *
FROM Rivers

DROP TABLE Rivers,
Cities,
Third

DROP FUNCTION dbo.fnRiverCount,
dbo.fnCityCount[/code]

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-08 : 06:41:31

Gees, you write all that up just now?

OK, let me play around and get back to you.

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 07:13:08

Those three tables does not have any constraints.
You should put some in, between Third and Cities, and between Third and Rivers, so that only valid RiverID and CityID are inserted.

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-08 : 09:03:54

quote:
You should put some in, between Third and Cities, and between Third and Rivers, so that only valid RiverID and CityID are inserted.

RiverID and CityID in "Third Table" are a composite pk, that references corresponding columns in the other 2 tables?

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 09:10:27

[code]ALTER TABLE Third WITH CHECK ADD CONSTRAINT FK_Third_Cities FOREIGN KEY(CityID) REFERENCES Cities (CityID)
GO

ALTER TABLE Third CHECK CONSTRAINT FK_Third_Cities
GO

ALTER TABLE Third WITH CHECK ADD CONSTRAINT FK_Third_Rivers FOREIGN KEY(RiverID) REFERENCES Rivers (RiverID)
GO

ALTER TABLE Third CHECK CONSTRAINT FK_Third_Rivers
GO[/code]

Peter Larsson
Helsingborg, Sweden

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-08 : 10:29:13

OK, I see what you've done. I had been taught (at least I think i have) that what you've done there is only done in a scenario where there is a many to many relationship i.e. a river can flow through many cities, and a city can have more than 1 river.

But that is not what the model is saying.

quote:
How do you even check "River MUST flow through 1 or more cities"?

I don't know. But have a look at any basic conceptual modeling text book and you will find scenarios of:

1:m, 1:1 and m:n relationships. These will be broken down into mandatory at both ends, optional at both ends, optional at one end.

A quick search on the net brought up this:
http://www.cilco.co.uk/briefing-studies/acme-fashion-supplies-feasibility-study/slides/logical-data-structure.html

Look at the relationship between "Sales Order" and "Sales Order Line".

They are both MUSTS and are at both ends of the relationship. This is a specific requirement, and surely has to be represented somehow in the database, otherwise what's the point in writing it in?!

There are writings that tell you how to draw a model. There are also writings that tell you how to create tables and their constraints. But very few that tell you how to map one into the other. This is my last year at Uni, in all this time, I have not found a single text book or reference on the web that specifically says how to translate EACH one of the above scenarios (1:m; mandatory at both ends, mandatory at one end etc. ) in terms of PKs and FKs. They always seem to be incomplete methods.

So can anyone tell me...

quote:
How do you even check "River MUST flow through 1 or more cities"?

Drew

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-03-09 : 04:00:12

Theoretically, you can have a many to many relationship with both sides mandatory. In pratice, though, you cannot insert two rows simultaneously. You can insert only one at a time.
That means that in many to many relationship, one side has to allow nulls in the model. It also means that software inserting the data in the database should make sure that when one side of the relationship is inserted it is followed by insert of the other side too.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-09 : 04:39:39

So what's the point in all those participation options in the threory? I'm a bit bewildered here as I'm spending the last of year of uni (along side a full time job and a new born baby) studying db thoery. I feel like I'm wasting my time.

This warrants a conversation with my tutor.

Drew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-09 : 04:58:19

Excellent idea.

Peter Larsson
Helsingborg, Sweden

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-03-09 : 05:39:07

Calm down there Drew.

Implementation of these rules is dependant on the DBMS.
Don't forget the critical difference between the physical layout (tables) and the users perspective (views etc..)

To get you started...

One-to-one rule using views...
http://weblogs.sqlteam.com/davidm/archive/2003/11/28/623.aspx

Cardinality RI
http://weblogs.sqlteam.com/davidm/archive/2003/11/18/539.aspx

DavidM

Production is just another testing cycle

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-09 : 06:27:21

quote:
Calm down there Drew.

This theory crud's a farce, I tell you! A terrible farce!

Seriously though, I was always under the impression that a conceptual model could be simply mapped (after normalization etc) to a real database via a relatively straightforward set of steps of posting constraints here and there. But it seems not. I suppose this explains why you hardly ever see a cross-over in textual resources between the two areas. And if there is, it's always vague. You'd have thought they come hand-in-hand.

I understand that every model is different, every situation can be interpreted differently, but am I alone in being bemused that there is little info on how to express all the scenarios (1:m, mandatory, 1:1 optional...) into a database, whatever the DBMS.

So my mission now to to draw out every fundamental scenario (in terms of participation and cardinality) and document how to implement each and every one, whether that's by using udfs, triggers, checks etc.

Drew

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-03-09 : 06:46:25

>> This theory crud's a farce, I tell you! A terrible farce!

It's not the theory, it's the practice!

>>Seriously though, I was always under the impression that a conceptual model could be simply mapped (after normalization etc) to a real database via a relatively straightforward set of steps of posting constraints here and there.

LOL.. Sorry to burst your bubble... Checkout Alphora for something completely different.

>> but am I alone in being bemused that there is little info on how to express all the scenarios into a database

That depends if you make a living out of it.

DavidM

Production is just another testing cycle

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-03-09 : 07:00:41

quote:
Checkout Alphora for something completely different.

For others that may stumble across this thread one day...

Dataphor is a truly relational database management system (TRDBMS). Dataphor is considered a virtual, or federated, DBMS. Dataphor was created by Alphora and is the first truly RDBMS since IBM Business System 12. Its D4 language is based on the principles of Christopher J Date's and Hugh Darwen's Tutorial D, but with a Pascal-like syntax.

Dataphor does not employ SQL as its primary database language since SQL inherently violates several principles of the relational model.

Interesting! Thanks, David...

Do you know, is it in use much?

Drew

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources