Ternary

It would be impossible to list how to translate all the higher order relations into SQL. In this section I will present an example and provide instructions on how similar relations would be translated. Consider the example of employees using skills on a project. The basic ER diagram is as follows:

e7.png

The table structures are as follows:

employee(__id__, name, <e_other>)
project(__id__, name, <p_other>)
skill(__name__)
esp(emp_id*, project_id*, skill*)

The SQL create statements for the first three tables are standard. It is the create statement for the esp table that is the most interesting. Each of the three columns are foreign keys. Each of these columns is also classified as not null since all the foreign keys of a relationship table must have values. None of the columns are classified as unique since you cannot determine (generally) this characteristic. The only thing that will vary is whether or not the column is part of the primary key of the relationship table. Here are the rules:

MMM : All three columns must be part of the primary key
MM1 : The two M columns must be the primary key
M11 : The M column and one of the 1 columns must be the primary key
111 : Any pair of columns can make up the primary key

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License