Additional Text for "What is a relational database?"

[andrewnolanhall]

Here is some suggested text for inclusion in the "What different types of keys are there?" section in the "What is a relational database" lesson.

Original text: "In addition to the primary key, a table may have one or more Foreign keys. A foreign key does not have to be unique or identified as a foreign key when the table is created. A foreign key in one table will relate to the primary key in another table. This allows a relationship to be created between the two tables. If a table needs to be related to several other tables, then there will be a foreign key (column) for each of those tables."

Updated Text:
In addition to the primary key, a table may have one or more Foreign keys. A foreign key does not have to be unique or identified as a foreign key when the table is created. A foreign key in one table will relate to the primary key in another table. This allows a relationship to be created between the two tables. If a table needs to be related to several other tables, then there will be a foreign key (column) for each of those tables.

There are multiple types of Foreign keys and the specific type of interest to you will depend on your data usage needs. The following four keys can help in conceptualizing the type of key you need:

• Primary Key: This is a unique identifier for each observation in a table. It will normally be created with the database itself (i.e., not created by data users after the fact) since it must uniquely identify all observations from others. Typically this means that the person who created the dataset meant for it to be relational-ready.
• Surrogate Key: This is a key created by the user to identify observations when there is not a primary key built-in. It is not a primary key because it was not created to be unique originally.
• Composite Key: This is a key created by the user by combining different columns in the dataset to create combinations that would be very unlikely to repeated across observations. For example, if you had a last_name, date_of_birth variable, you might combine them to make last_name_date_of_birth and use this as a composite key.
• Compound Key: This is a key created by combining different variables across datasets, storing the keys in separate datasets. For example, you might have one primary key that is StudentID in one table and another primary key that is CourseID in another. Then a third dataset will have a compound key of StudentID_CourseID that can be used to link across datasets. Like composite keys, compound keys are typically created by combining different variable names.

[ucsdhealthrevcycle]

This note is submitted as part of the instructor checkout process. One of the reasons I was drawn to participating in the carpentries is how it blends subject matter expertise with fundamental principles of curation and information governance commonly expressed in library and archival settings.

Although the linking of the carpentries methods to existing international standards is an important overall goal (and one which occupies a good deal of my data analysis work at UCSD) - I wanted to follow the guidance to make a substantive (but small) contribution to an existing issue.

background

User 'andrewnolanhall'
Andrew N Hall

From https://github.com/andrewnolanhall

Mr Hall is a PhD candidate at Northwestern University who provided updated text touching on four types of foreign keys.

When I read his (excellent) contribution - I was reminded as I often am that the International organization for standardization ISO and the International electrotechnical commission IEC are the primary entities establishing a specialized system for worldwide standardization.

Practicing archivists (especially) and librarians are often confronted with terms and concepts that other have developed which differ from or otherwise increase the amount of ambiguity surrounding core concepts.

As an example consider 'A glossary of archival and records terminology' published by the Society of American Archivists based primarily on archival literature from the US and Canada, see < http://bit.ly/2MCxxls >.

Next consider 'The United Nations Terminology Database' which publishes terms in the six UN official languages, German and Portugese.

From https://unterm.un.org/UNTERM/portal/welcome

In developing the different lesson within the Carpentries I would mention in some respect an ISO term that equates with the tasks at hand - given the learner / user a point that is almost universally accepted as a probably the 'best' or 'fittest' definition of a term or concept.

In connection with Mr Hall's note I would include the following (from ISO/IEC/IEE 24765:2017(en) Systems and software engineering - vocabulary):

3.136
alternate key
1. candidate key of an entity other than the primary key [IEEE 1320.2-1998 (R2004) IEEE Standard for Conceptual Modeling Language tax and Semantics for IDEF1X97 (IDEFobject), 3.1.5]
Note 1 to entry: [ key style]

From https://www.iso.org/obp/ui/#iso:std:iso-iec-ieee:24765:ed-2:v1:en

3.471
candidate key
1. attribute, or combination of attributes, of an entity for which no two instances agree on the values [IEEE 1320.2-1998 (R2004) IEEE Standard for Conceptual Modeling Language Syntax and Semantics for IDEF1X97 (IDEFobject), 3.1.14]
Note 1 to entry: [ key style]

From https://www.iso.org/obp/ui/#iso:std:iso-iec-ieee:24765:ed-2:v1:en

3.704
composite key
1. key comprising of two or more attributes [IEEE 1320.2-1998 (R2004) IEEE Standard for Conceptual Modeling Language Syntax and Semantics for IDEF1X97 (IDEFobject), 3.1.38]
Note 1 to entry: [ key style]

From https://www.iso.org/obp/ui/#iso:std:iso-iec-ieee:24765:ed-2:v1:en

3.1632
foreign key
migrated key
1. attribute, or combination of attributes, of a child or category entity instance whose values match those in the primary key of a related parent or generic entity instance [IEEE 1320.2-1998 (R2004) IEEE Standard for Conceptual Modeling Language Syntax and Semantics for IDEF1X97 (IDEFobject), 3.1.62]
Note 1 to entry: A foreign key results from the migration of the parent or generic entity's primary key through a generalization structure or a relationship. [ key style]

From https://www.iso.org/obp/ui/#iso:std:iso-iec-ieee:24765:ed-2:v1:en

3.3011
primary key
1. candidate key selected as the unique identifier of an entity [IEEE 1320.2-1998 (R2004) IEEE Standard for Conceptual Modeling Language Syntax and Semantics for IDEF1X97 (IDEFobject), 3.1.149]2. value that uniquely identifies component instances within the scope of the home that manages them [ISO/IEC 19500-3:2012 Information technology — Object Management Group — Common Architecture Request Broker Architecture (CORBA) — Part 3: Components, 4.1]
Note 1 to entry: [ key style]

From https://www.iso.org/obp/ui/#iso:std:iso-iec-ieee:24765:ed-2:v1:en

3.3906
split key
1. foreign key containing two or more attributes, where at least one of the attributes is a part of the entity's primary key and at least one of the attributes is not a part of the primary key [IEEE 1320.2-1998 (R2004) IEEE Standard for Conceptual Modeling Language Syntax and Semantics for IDEF1X97 (IDEFobject), 3.1.183]
Note 1 to entry: [ key style]

From https://www.iso.org/obp/ui/#iso:std:iso-iec-ieee:24765:ed-2:v1:en

In my view, jumping off from these firmly established definitions (and better still coded) terms gives learners / users exceptional standards based guidance and confidence when applying the concepts to their own work or use cases.