This post details about relational theory relationships. I’ve struggled to find on the Internet any decent explanations of what these are in simplistic terms which are degree/masters degree level, and not primary school or PhD examples! I’ll introduce the types of relationship, with some simple examples showing how the relational theory of relationships is actually applied into some pseudo-SQL. I use pseudo-SQL as it will give you the idea of what the SQL will look like but allows you to use whichever flavour of SQL you choose with some syntax adjustments that you’ll need to make. This will detail how to convert from an E-R (entity relationship model) to pseudo-SQL and any issues that you need to be aware of when you do so.
If you spot any mistakes please let me know, and i’ll correct them!
Scenario
For the purposes of this post, i’m going to use an example with two relations (tables) called A and B, with a relationship between them. There is also a recursive relationship back to A, but we’ll examine this in separate examples. Each relationship will have a name, so it can be easily identified.
1 = Must be one
1..* = Must be one, or more.
0..* = May be none, one or more.
0..1 = May be none, but at most one
We are also assuming for ease that all attributes (columns) are integer values.
1 – One-to-Many Relationship (1:n) – Mandatory Participation at the Many (:n) End
Reading the relationship from left to right, a single instance of A must be related to one row in B, or many rows in B.
Reading the relationship from right to left, a single instance of B is related to only one row in A.
Example if A is a table of managers, and B is a table of staff, a manager must manage at least one member of staff, but they may of course manage many members of staff. Conversely a member of staff (B) may only have one manager, i.e. they can only be managed by one manager and must have a manager at all times.
A( A1, A2 )B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
--relationship Rel1 - 1:n - Mandatory Participation
CHECK (A1 IN (SELECT A1 FROM B))
PRIMARY KEY (A1)
) CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
-- relationship Rel1 - 1:n - Mandatory Participation
FOREIGN KEY (A1) REFERENCES A(A1) NOT NULL
PRIMARY KEY (B1)
)So to represent the one-to-many relationship, we paste a foreign key into the Table B, in this case the A1 column from the Table A, this relates the two tables. To enforce mandatory participation at the “many” end we say it must be NOT NULL. Reading the other way from right to left, a single row of B is now related to only a single row of A, because A1 is a primary key and must be unique and may not be NULL. To ensure each manager (A) has at least one member of staff they manage we add a CHECK constraint which looks into TABLE B to ensure that manager row exists at least once in Table B.
2 – One-to-Many Relationship (1:n) – Optional Participation at the Many (:n) End
Reading the relationship from left to right, a single instance of A must be related to only one row in B.
Reading the relationship from right to left, a single instance of B may be related to none, one or more than one row of A. (Optional participation.)
Example: If A is a list of employees and B is a table of managers, it means that a member of staff must be managed by a single manager (B), but a manager may manage no members of staff (A), one member of staff or many member of staff.
A( A1, A2 )B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (B1) REFERENCES B(B1) NULL
PRIMARY KEY (A1)
)CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
PRIMARY KEY (B1)
)So to represent the one-to-many relationship, we paste a foreign key into the Table A, using the B1 column from table B. Because we need to enforce mandatory participation at the “one” (1:) end we use the primary key B1, which must have a unique value and must not be null. Then at the “many” (:n) end we have the foreign key pasted in.
3 – One to Many Relationship (1:n) – Optional Participation at the One (1:) End and Mandatory Participation at the Many (:n) End
Reading the relationship from left to right, a single instance of A must be related to at least one or more rows of B.
Reading the relationship from right to left, a single instance of B may not be related to any rows in A, but it may be to at most one row in A.
Example: If A is a list of managers, and B a list of staff members. It means that a manager must manage at least one member of staff (table B) (mandatory participation) and may manage more than one member of staff. Reading the other way, a member of staff may or may not have a manager, but if they do they may only have at most one manager.
A( A1, A2 )B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:n - Mandatory Participation
CHECK (A1 IN (SELECT A1 FROM B))
PRIMARY KEY (A1)
)CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (A1) REFERENCES A(A1) ALLOW NULL
PRIMARY KEY (B1)
)So to represent the one-to-many relationship, we paste a foreign key into Table B using column A1 from table A. As each instance of A must exist at least once in B, we set a CHECK constraint to check it exists at least once in B. Going from right to left, we ensure that the optional participation is allowed by allowing null in the foreign key within table B.
4 – One to Many Relationship (1:n) – Optional Participation at the One (1:) End and Optional Participation at the Many (:n) End
Reading the relationship from left to right, a single instance of A may be related to none, one or many rows of B.
Reading the relationship from right to left, a single instance of B may be related to none or at most one row in A.
Example: If A is a list of managers, and B a list of staff members. It means a manager (table A) may manage no members of staff, one member of staff, or many members of staff (optional participation in table B.) Reading the other way, a member of staff may or may not have a manager but if they do they may only have at most one manager.
A( A1, A2 )B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
PRIMARY KEY (A1)
)CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (A1) REFERENCES A(A1) ALLOW NULL
PRIMARY KEY (B1)
)So to represent the one-to-many relationship we paste a foreign key into Table B using column A1 from table A. As there is only an optional participation for a row in table B, we show this by allowing the foreign key to be NULL. Going the other way the relationship uses A1 which is a primary key in table A, because the foreign key in table B allows NULL we can leave it blank, or if it is filled in and matches a row in table A it can only be linked to one and only one row.
5 – One to Many Relationship (1:n) – Recursive – Optional Participation at the Many (:n) End and Mandatory Participation at the One (1:) End
can also be shown as:
So in a recursive relationship, a table is related to itself in some way, I like to draw it out as two separate tables for called A1 and A2 as it can sometimes be confusing, when you do this you can see its just a normal one-to-many relationship. So reading from left to right a single instance of A is related to another none, one or many rows in A (as another table.) Reading the other way a single row of A must be related to a row of A.
Example: If table A contains a list of managers, it is assumed a manager might have a manager so, this means that a manager may manage none, one or many other managers. For example a CEO might manage 3 people below him or her. You might also have a figurehead manager, who is in the managers table A but doesn’t actually do any work and has no other managers that he/she manages. Reading the other way a manager who is managed by another manager, can only have and must have one manager hence the 1 meaning mandatory participation at the one end.
A( A1, A2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (A3) REFERENCES A(A1) ALLOW NULL
PRIMARY KEY (A1)
)So with this recursive relationship, we add a column called A3 to represent the relationship between A to A and allow it to be NULL, this is the many end of the one-to-many relationship and by allowing null we allow it to be blank. I.e. we allow a manager not to manage any other managers (the figurehead manager discussed earlier.) Reading the other way a manager is always managed by another manager hence the mandatory participation at the one (1:) end, because this is a primary key it must exist, but of course the foreign key does not have to.
6 – One to Many Relationship (1:n) – Recursive – Mandatory Participation at the Many (:n) End and Mandatory Participation at the One (1:) End
can also be shown as:
So in a recursive relationship, a table is related to itself in some way, I like to draw it out as two separate tables for called A1 and A2 as it can sometimes be confusing, when you do this you can see its just a normal one-to-many relationship. So reading from left to right a single instance of A must be related to one or more rows of A, and read the other way, A must be related to one and only one row of A.
Example: If table A contains a list of managers, it is assumed a manager might have a manager so, this means that a manager must manage one or more other managers, seems reasonable. This means the CEO must manage one or more other managers, and those in turn must manage other managers. We can’t have the figurehead manager any more who doesn’t do any work as they don’t manage anyone because each row of A must be related to at least one (or more) rows of A.
A( A1, A2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:n - Mandatory Participation
FOREIGN KEY (A3) REFERENCES A(A1) NOT NULL
PRIMARY KEY (A1)
)So with this recursive relationship, we add a column called A3 to represent the relationship between A to A and do not allow it to be NULL. This means that A3 might contain the name/staff ID of the manager’s manager which is A1. So the mandatory participation is shown by not allowing NULL for the many end of the relationship shown as the foreign key. And reading the other way at the one (1:) end the mandatory participation is enforced by the A1 being a primary key.
7 – One to One Relationship (1:1) – Mandatory Participation at the (Right) One (1:) End and Optional Participation at the (Left) One (1:) End
Reading the relationship from left to right, a single instance of A must be related to a single row in B.
Reading the relationship from right to left, a single row of B may be related to none or at most one row of A.
Example: If A is a list of members of staff and table B is a list of telephone numbers. So a member of staff must have a telephone number, i.e. A must be related to a row in B. But reading the other way a row of B may or may not be related to a row of A but if it is then it may only relate to at most one row. So in our example this means a telephone number (table B) can only be related to at most one member of staff, but there might be times when a telephone number has not been assigned to any member of staff.
A( A1, A2 )
B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:1 - Mandatory Participation
FOREIGN KEY (B1) REFERENCES B(B1) NOT NULL UNIQUE
PRIMARY KEY (A1)
) CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
PRIMARY KEY (B1)
)So to represent the one to one relationship, we post the the primary key B1 of the table B into the table A as both a foreign key and an alternate key because we have made it not allow NULL and force the UNIQUE constraint. The mandatory participation at the right hand (1:) end of the relationship is enforced by ensuring the foreign/alternate key is not allowed to be NULL. The optional participation of B at the 1: end of the relationship is represented by not showing anything, i.e. we don’t specifically impose any constraint.
8 – One to One Relationship (1:1) – Optional Participation at Both Ends
Reading the relationship from left to right, a single row of A may related to a single row of B, or may not be related to any rows at all.
Reading the relationship from right to left, a single row of B may be related to a single row of A, or may not be related to any rows at all.
Example: If A is a list of members of staff and table B is a list of mobile telephone numbers. So in this situation a member of staff may or may not have a company mobile ‘phone. So A may or may not be related to B and B may or may not be related to A. But if a member of staff does have a mobile phone they have at most one. And reading the other way a mobile ‘phone (table B) number may be owned by no member of staff, but if it is it may only be owned by one.
A( A1, A2 )
B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:1 - Optional Participation
FOREIGN KEY (B1) REFERENCES B(B1) ALLOW NULL UNIQUE
PRIMARY KEY (A1)
)CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
PRIMARY KEY (B1)
)Here choosing the place for the foreign key doesn’t matter, either place could do, you might choose that you’d like the mobile phone number listed as a foreign key B1 in the table A staff table, rather than the staff name against an attribute in table B. So to represent this we paste the B1 column into table A as a foreign key, we allow it to be NULL (optional participation but we define it must be UNIQUE if it exists, only one member of staff may have a particular mobile ‘phone number at one time.
9 – One to One Relationship (1:1) – Mandatory Participation at Both Ends
Reading the relationship from left to right, a single row of A must be related to one and only one row of B.
Reading the relationship from right to left, a single row of B must be related to one and only one row of A.
Example: If A is a list of staff members, and B is a list of payroll numbers, each member of staff (table A) must have a payroll number (table B), and each payroll number must be own by a single member of staff.
A( A1, A2 )
B( B1, B2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:1 - Mandatory Participation
FOREIGN KEY (B1) REFERENCES B(B1) NOT NULL UNIQUE
PRIMARY KEY (A1)
ALTERNATE KEY (B1)
)CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
PRIMARY KEY (B1)
)A weird relationship, because you would normally have what ever columns that are in table B in table A if there is a one to one relationship, you might not want this if table B contained sensitive information and you wanted it seperate for example. Anyway, to implement this we add a foreign key in A representing the relationship to B, using B1, but because we must show mandatory participation the foreign key is NOT NULL and UNIQUE, meaning it also becomes an ALTERNATE KEY in table A. Meaning a single row of A is related to a single row of B, as its a one to one mapping from the foreign key B1 in table A (which must be unique) to a single row in table B using column B1 which is the primary key and must be unqiue too. The reverse mandatory participation is enforced by the NOT NULL and UNIQUE constraints on the foreign key.
10 – One to Many Relationship (Recursive) – Optional Participation at Both Ends
may also be shown as:
So in a recursive relationship, a table is related to itself in some way, I like to draw it out as two separate tables for called A1 and A2 as it can sometimes be confusing, when you do this you can see its just a normal one-to-many relationship. So reading from left to right a single instance of A(1) may be related to none, one or more rows of A(2), read the other way, A(2) may be related to none or one rows of A(1).
Example: If table A contains a list of managers, it is assumed that a manager might also have a manager. So a manager may manage, no members of staff, one member of staff or many members of staff if read from left to right. Conversely a manager may or may not be managed by another manager, but if they are then they may only be managed by one at any one time.
A( A1, A2 )
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (A3) REFERENCES A(A1) ALLOW NULL
PRIMARY KEY (A1)
)So with this recursive relationship, we add a column called A3 to represent the relationship between A to A and allow it to be NULL. This means that A3 might contain the name/staff ID of the manager’s manager which is A1. So the optional participation is shown by allowing NULL for the many end of the relationship shown as the foreign key. And reading the other way because A3 might be NULL and is allowed to be we can show optional participation at the 0..1 (left hand end.)
11 – Many to Many Relationship (m:n) – Optional Participation at Both Ends
This is a weird type of relationship, basically it can’t exist as its invalid, you might find you need duplicate values in a primary key, or many values in a single column, where you want it to be atomic. So in this case before we can use a posted relation method with foreign or primary key, we need to put in a intersection relation (or mapping table) between the two before hand.
So to explain, a row of A may be related to none, one or many rows of B when read left to right, reading the other way, a single row of B may be related to none, one or many rows of A. This is where we get a problem.
Example: Say table A contains a list of staff, and table B contains a list of items on the company lunch menu. A member of staff might want to pick none, one or many items off the lunch menu. Of course going the other way a single item (row) on the lunch list (table B) might be wanted by many members of staff at the same time. We can’t represent this so we need to add an intersection relation or mapping table to break the many to many relationship (m:n) down into two one-to-many relationships (1:n) instead. Which we can then represent using the primary key/foreign key methods we done before.
A (A1, A2)
B (B1, B2)
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
PRIMARY KEY (A1)
)CREATE TABLE C
(
A1 AS INTEGER
B1 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (A1) REFERENCES A(A1)
-- relationship Rel2 - 1:n - Optional Participation
FOREIGN KEY (B1) REFERENCES B(B1)
PRIMARY KEY (A1, B1)
) CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
PRIMARY KEY (B1)
)Right, now, we’ve created a new table called table C, within this we have put two foreign keys representing the many end (:n) of each of the two relationships Rel1 and Rel2. Both allow NULL so as to provide optional participation (0..*) and provide mandatory participation going out from C to A or B as they reference the primary keys A1 and B1 in the tables A and B respectively.
12 – Many to Many Relationship (m:n) – Mandatory Participation Both Ends
This is a weird type of relationship, basically it can’t exist as its invalid, you might find you need duplicate values in a primary key, or many values in a single column, where you want it to be atomic. So in this case before we can use a posted relation method with foreign or primary key, we need to put in a intersection relation (or mapping table) between the two before hand. Like the above this is basically the same however we now have enforced mandatory participation at each end of the Many to Many relationship.
So to explain, a row of A may be related to one or many rows of B when read left to right, reading the other way, a single row of B may be related to one or many rows of A. This is where we get a problem.
Example: Say table A contains a list of staff, and table B contains a list of items on the company lunch menu. The company creates a rule that the canteen will cook a certain amount of each item, and all items must be eaten at lunch time, so each item must have been eaten by someone. A member of staff might want to pick one or many items off the lunch menu. Of course going the other way a single item (row) on the lunch list (table B) might be wanted by one or many members of staff at the same time. We can’t represent this so we need to add an intersection relation or mapping table to break the many to many relationship (m:n) down into two one-to-many relationships (1:n) instead. Which we can then represent using the primary key/foreign key methods we done before.
A (A1, A2)
B (B1, B2)
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
-- relationship Rel1 - 1:n - Mandatory Participation
CHECK (A1 IN (SELECT A1 FROM B))
PRIMARY KEY (A1)
)CREATE TABLE C
(
A1 AS INTEGER
B1 AS INTEGER
-- relationship Rel1 - 1:n - Mandatory Participation
FOREIGN KEY (A1) REFERENCES A(A1) NOT NULL
-- relationship Rel2 - 1:n - Mandatory Participation
FOREIGN KEY (B1) REFERENCES B(B1) NOT NULL
PRIMARY KEY (A1, B1)
) CREATE TABLE B
(
B1 AS INTEGER
B2 AS INTEGER
-- relationship Rel2 - 1:n - Mandatory Participation
CHECK (B1 IN (SELECT B1 FROM B))
PRIMARY KEY (B1)
)So a bit more complicated now, to represent each of the two one to many relationships we’ve added a FOREIGN KEY into the new TABLE C table we’ve added. To ensure that the participation is mandatory we set this to NOT NULL so it must take a value. Then to ensure we have mandatory participation at the 1 end, we add a CHECK constraint to check that the value for the primary key at the 1: end match the foreign key values in rows in table C at the many end.
13 – Many to Many Relationship (Recursive) – Optional Participation at Each End
This is a variation on number 11, but is included here because it can appear to be confusing at first glance.
So to explain a single row of A may be related to none, one or many rows of A when read from left to right, reading the other way, a single row of A may be related to none, one or many rows of A.
If this is flattened out into two tables A1 and A2 we can see it is a regular many to many relationship.
Example: This is a weird set up with a recursive relationship, but take for example a chaotic company where the table A represents the list of staff in the company. A member of staff may supervise none, one or many staff, they might however then be supervised by none, one or many members of staff. This many to many relationship makes a problem. We can’t represent this so we need to add an intersection relation or mapping table to break the many to many relationship (m:n) down into two one-to-many relationships (1:n) instead. Which we can then represent using the primary key/foreign key methods we done before.
A (A1, A2)
CREATE TABLE A
(
A1 AS INTEGER
A2 AS INTEGER
PRIMARY KEY (A1)
)CREATE TABLE C
(
A3 AS INTEGER
A4 AS INTEGER
-- relationship Rel1 - 1:n - Optional Participation
FOREIGN KEY (A3) REFERENCES A(A1) ALLOW NULL
-- relationship Rel2 - 1:n - Optional Participation
FOREIGN KEY (A4) REFERENCES A(A1) ALLOW NULL
PRIMARY KEY (A3, A4)
)So with this recursive relationship, to represent the one to many relationship from A back to A via our intersection relation, we add a foreign key into the newly created table C. We allow it to take the value of NULL so its optional participation, so we can represent this relationship from A1 to C. Then we add another foreign key A4 to represent the relationship from C to A2 (or C back to A), again this is allow NULL so it is optional participation. Going back the other way, a single row of C may only be related to one row of A because it uses the primary key A1.
This concludes the topic, please let me know if you find any mistakes in this, this was written as revision for an exam so its good to know if I’ve gone wrong somewhere as i’ve said there are lots of questions in this form, but no documentation or texts explaining how to do it hence, why I wrote this!