using ER Diagrams for writing SQL DDL

Question

Hi,

I'm trying to make sense of ER Diagrams and how their symbols convert when writing the corresponding DDL.  When searching around the internet to find information on this topic, I came acrosshttp://db4u.wikidot.com/ertosql.  While reading about the 6 binary relationships on that site I began making charts to compare the differences.  What I have so far can be found here:http://www.mwt.net/~hlotz/ERDtoSQL/ER%20Diagrams%20to%20SQL%20Code%20Summary_customized.pdf.  This is a work in progress.  Note that the CREATE TABLE syntax used in the charts is generic (not syntactically correct for SQL Server).  Here are some questions about it so far:

1. Is it correct that cardinality determines whether a foreign key is unique or not unique?  (See blue text in PDF).

2. Is it correct that optionality determines whether a foreign key is null or not null?  (See red text in PDF).

3. In the first chart (top of the page) do the examples of relative row counts for tables A and B look correct for each relationship?

4. When optionality is omitted (see first relationship example...one (mandatory) to many), how is that typically interpreted? [note: in the one (mandatory) to many example from the db4u website I interpreted it to mean mandatory based on the narrative for the example]

5. Can the "one (optional) to many" relationship [in lower chart, highlighted yellow] fit into the "one (mandatory) to many" table structure in the first chart?  If so, please explain how that works.

6. An addition to these charts that might be helpful are real life examples of these relationships. The first 4 relationships have examples, the last 2 need examples:

one (mandatory) to many: table A-order header, table B-order detail

one (mandatory) to one (mandatory): table A-employees, table B-social security numbers (the idea being that SSNs are confidential and they're in a separate table with special user permissions)

one (optional) to one (mandatory): table A-employees, table B-cell phone numbers (assumes employees may or may not have a cell phone, assumes that employees have only one cell phone if they have one at all)

many to many: table A-students, table B-classes, table R-students taking classes

one (optional) to many: ? need an example here

one (optional) to one (optional): ? need an example here

Thanks, Knot

Answer 1

Hi Knot,

The second link on your post gives the good ol '404-file not found error, so I can't really look at the charts you are talking about here. I can speak about a few of the items you mention .

1. cardinality refers to the degree of uniqueness of the values stored in a particular column. It is usually represented as a decimal number going from 0 to 1. 0 means all rows in that column have same value, 1 means that column has all unique value. So, to answer your first question - in the right situation( like your example of table a-employee , table b-ssn) you can use cardinality to determine if a FK is unique or not. But its used to only provide a yes or no kind of answer.

2. yes, techinically speaking, optionality determines if the is nullable or not. However, a good data model usually does allow NULLS in a FK column. If there a need for nullability in an FK column, it can usually be attributed to some shortcoming or the other in the data modeling process. 

The other points in your post seem to be heavily pertinent upon the document in link 2, which errors out.

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

Answer 2

Note that the diagram should be a reflexction of a model that generically specifies all of the different things you can specify about a database.

As far as your questions:

1. Is it correct that cardinality determines whether a foreign key is unique or not unique?  (See blue text in PDF).

It can. When cardinality is 1-1, then yes, you would want to make the values in the foreign key reference. If it is 1-Many (or really 1-2 or more) then no.

2. Is it correct that optionality determines whether a foreign key is null or not null?  (See red text in PDF).

Yes.

3. In the first chart (top of the page) do the examples of relative row counts for tables A and B look correct for each relationship?

I think so

4. When optionality is omitted (see first relationship example...one (mandatory) to many), how is that typically interpreted? [note: in the one (mandatory) to many example from the db4u website I interpreted it to mean mandatory based on the narrative for the example]

I would say that indicates that they doesn't have to be children, but there does need to be a parent for child rows to exists

5. Can the "one (optional) to many" relationship [in lower chart, highlighted yellow] fit into the "one (mandatory) to many" table structure in the first chart?  If so, please explain how that works.

I think that this is saying that a parent doesn't need to have children to exist, whereas in the top section, when the optionality of the parent is mandantory, a child needs to exist for the parent to exist.

6. An addition to these charts that might be helpful are real life examples of these relationships. The first 4 relationships have examples, the last 2 need examples:

one (mandatory) to many: table A-order header, table B-order detail

one (mandatory) to one (mandatory): table A-employees, table B-social security numbers (the idea being that SSNs are confidential and they're in a separate table with special user permissions)

one (optional) to one (mandatory): table A-employees, table B-cell phone numbers (assumes employees may or may not have a cell phone, assumes that employees have only one cell phone if they have one at all)

many to many: table A-students, table B-classes, table R-students taking classes

one (optional) to many: ? need an example here

customers to orders

one (optional) to one (optional): ? need an example here

Resident to parking space? One parking space is assigned to one resident and vice versa.  I don't know too that I would build a central table, but usually would rather do:

Resident (ResidentId PK, ParkingSpaceId FK)
ParkingSpace(ParkingSpaceId PK)

Choosing the most "important" table as the primary in the relationship...

Hopefully this isn't homework, but if it is, let me know how we all did :)

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 3

Thank-you for your responses Sanil and Louis.  They helped to clarify things.  No, it isn't homework--although my habit of numbering questions makes it seem so.  I'm actually preparing to create a data model for a project using a combination of hand written DDL and a data modeling tool and wanted to have a better understanding of how ERDs and SQL are related.