Introduction_to_Relational_Databases.Rpres

Data Management
========================================================

For Scientific Research

[//]: # (author: Brian High, UW DEOHS)
[//]: # (date: 2014-04-17)
[//]: # (license: CC0 1.0 Universal, linked-content/images)
[//]: # (note: License does not apply to external content such as quoted material, linked web pages, images, or videos. These are licensed separately by their authors, publishers or other copyright holders. See attribution links for details.)
[//]: # (note: Any of the trademarks, service marks, collective marks, design rights, personality rights, or similar rights that are mentioned, used, or cited in the presentations and wiki of the Data Management For Scientific Research workshop/course are the property of their respective owners.)
[//]: # (homepage: https://github.com/brianhigh/data-workshop)

<p style="width: 600px; float: right; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://www.stanza.co.uk/body/stanza_+BODY-copy.jpg" alt="stanza body copy" style="padding-bottom:0.5em;" />Photo: &copy; <a href="http://www.stanza.co.uk/body/index.html">Stanza</a>. Used with permission.</p>

Systems Development Life Cycle (SDLC)
==================================

One of several approaches to systems development is the [SDLC](http://en.wikipedia.org/wiki/Systems_development_life_cycle), also called the "Waterfall" model.

<p style="width: 500px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/thumb/e/e2/Waterfall_model.svg/500px-Waterfall_model.svg.png" alt="waterfall model" style="padding-bottom:0.5em;" />Image: <a href="http://commons.wikimedia.org/wiki/File:Waterfall_model.svg">Peter Kemp / Paul Smith / Wikimedia</a></p>

========================================================

<p style="width: 900px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://mariovalle.name/sdm/img/Slide6.png" alt="publish and forget model" style="padding-bottom:0.5em;" />Image: <a href="http://mariovalle.name/sdm/scientific-data-management.html">Mario Valle</a>. Used with permission.</p>


========================================================

<p style="width: 900px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://mariovalle.name/sdm/img/Slide7.png" alt="publish and publish and publish model" style="padding-bottom:0.5em;" />Image: <a href="http://mariovalle.name/sdm/scientific-data-management.html">Mario Valle</a>. Used with permission.</p>


Session 4: Introduction to Relational Databases
========================================================

<p style="width: 500px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/thumb/3/3d/Erd-entity-relationship-example1.svg/500px-Erd-entity-relationship-example1.svg.png" alt="ERD example" style="padding-bottom:0.5em;" />Image: <a href="http://en.wikipedia.org/wiki/File:Erd-entity-relationship-example1.svg">Wikimedia</a></p>

<p style="width: 500px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/thumb/8/8b/CPT-Databases-ExampleReceipt.svg/500px-CPT-Databases-ExampleReceipt.svg.png" alt="several relations" style="padding-bottom:0.5em;" />Image: <a href="http://commons.wikimedia.org/wiki/File:CPT-Databases-ExampleReceipt.svg">Wikimedia</a></p>

Relational Databases
========================================================

A relational database ...

* Is based on the relational model developed by <a href="http://en.wikipedia.org/wiki/Codd%27s_12_rules">E.F. Codd</a>
* Allows the definition of ...
  - data structures
  - storage and retrieval operations
  - integrity constraints

In such a database, the data and relationships between them are organized into tables. 

*Source*: <a href="http://databasemanagement.wikia.com/wiki/Relational_Database_Model">Wikia.com</a>


Relational Model in Action
========================================================

Example: 

Given an *Activity* table and *Event* table, find all events of the "Overlay" activity.

1. Find code for "Overlay".
2. "Overlay" has a code of 24.
3. Find dates with code = 24.
4. Two dates have code = 24.

---

<p style="width: 500px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/d/da/Relational_Model.svg" alt="relational model" style="padding-bottom:0.5em;" />Image: <a href="http://en.wikipedia.org/wiki/File:Relational_Model.svg">Wikimedia</a></p>


Designing a Relational Data System
========================================================

To design a data system, we need to identify requirements and map out interactions and components.

* Use Cases
* Process Models
* Data Flow Diagrams
* Entity Relationship Diagrams

---

<p style="width: 500px; float: right; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="images/data_modeling.png" alt="data modeling" style="padding-bottom:0.5em;" />Graphic: <a href="http://en.wikipedia.org/wiki/File:4-3_Data_Modelling_Today.svg">EPISTLE and its successors /  Matthew West, Julian Fowler, Razorbliss / Wikimedia</a></p>


Conceptual Models and Logical Models
========================================================

*Conceptual Model*: 

For non-technical or higher-level stakeholders

![artist performs song conceptual erd](http://upload.wikimedia.org/wikipedia/commons/thumb/3/3d/Erd-entity-relationship-example1.svg/500px-Erd-entity-relationship-example1.svg.png)

---

*Logical Model*: 

For technical stakeholders involved in design or implementation

![artist performs song logical erd](images/artist_performs_song.png)


Entity-Relationship Diagrams
========================================================

For a Data Model, ERDs present ...

* <A href="http://en.wikipedia.org/wiki/Entity">Entities</a>
* <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Relationships.2C_roles_and_cardinalities">Relationships</a>
* <A href="http://en.wikipedia.org/wiki/Database_constraints">Constraints</a>
* <a href="http://en.wikipedia.org/wiki/Cardinality_%28data_modeling%29">Cardinality</a>
* <a href="http://en.wikipedia.org/wiki/Attribute_%28computing%29">Attributes</a>
* <a href="http://en.wikipedia.org/wiki/Data_domain">Attribute Types</a>
* <a href="http://en.wikipedia.org/wiki/Primary_keys">Keys</a>

---

<p style="width: 500px; float: right; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="images/mozilla_places-erd.jpg" alt="relational database" style="padding-bottom:0.5em;" />Graphic: <a href="https://developer.mozilla.org/en-US/docs/The_Places_database">Mozilla/dietrich</a></p>


ER Diagram Symbols
========================================================

<p style="width: 400px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/f/f1/ERD_Representation.svg" alt="several relations" style="padding-bottom:0.5em;" />Image: <a href="http://en.wikipedia.org/wiki/File:ERD_Representation.svg">Wikimedia</a></p>


Conceptual Data Model ERD
========================================================

Shows:

* <A href="http://en.wikipedia.org/wiki/Entity">Entities</a>
* <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Relationships.2C_roles_and_cardinalities">Relationships</a>

May also show:

* <A href="http://en.wikipedia.org/wiki/Database_constraints">Constraints</a>
* <a href="http://en.wikipedia.org/wiki/Cardinality_%28data_modeling%29">Cardinality</a>

---

![artist performs song conceptual erd](http://upload.wikimedia.org/wikipedia/commons/thumb/3/3d/Erd-entity-relationship-example1.svg/500px-Erd-entity-relationship-example1.svg.png)

Keep in mind:

* Uses common language of the business or field
* For non-technical or higher-level stakeholders



Example Use Case Diagram
========================================================

![example use case diagram](images/Lab_Data_System_Use_Case_Diagram_uc2_v3.png)

Example Use Case Diagram Highlighted
========================================================

![example use case diagram](images/Lab_Data_System_Use_Case_Diagram_uc2_v3_HL3.png)

Example Conceptual ERD
========================================================

![example conceptual ERD](images/Lab_Data_System_Conceptual_Model_ERD.png)


Example Conceptual ERD Simplified
========================================================

![example conceptual ERD simplified](images/Lab_Data_System_Conceptual_Model_ERD_v2.png)

Logical Data Model ERD
========================================================

Shows:

* <A href="http://en.wikipedia.org/wiki/Entity">Entities</a>
* <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Relationships.2C_roles_and_cardinalities">Relationships</a>
* <A href="http://en.wikipedia.org/wiki/Database_constraints">Constraints</a>
* <a href="http://en.wikipedia.org/wiki/Cardinality_%28data_modeling%29">Cardinality</a>
* <a href="http://en.wikipedia.org/wiki/Attribute_%28computing%29">Attributes</a>
* <a href="http://en.wikipedia.org/wiki/Data_domain">Attribute Types</a>
* <a href="http://en.wikipedia.org/wiki/Primary_keys">Keys</a>

---

![artist performs song logical erd](images/artist_performs_song.png)

Database Normalization 
========================================================

> Normalization is part of successful database design; without normalization, database systems can be inaccurate, slow, and inefficient, and they might not produce the data you expect.

-- Michelle A. Poolet, <a href="http://sqlmag.com/database-performance-tuning/sql-design-why-you-need-database-normalization">SQL by Design: Why You Need Database Normalization</a>

Goals of Database Normalization
========================================================

* Logical groupings
* Minimal duplication
* Efficient access
* Data integrity


Normalization in a Nutshell
========================================================

> "Each attribute must represent a [single] fact about the key,^1 the whole key,^2 and nothing but the key."^3

-- <a href="http://en.wikipedia.org/wiki/Third_normal_form#cite_note-DateIntro-9">Chris Date</a>, *An Introduction to Database Systems*

1. Attributes contain single values -- no attribute groups
2. Non-primary-key attributes depend on entire primary key
3. Non-primary-key attributes depend only on primary key


Normalization Forms
========================================================

* 1st Normal Form (1NF): Remove repeating groups of data
* 2nd Normal Form (2NF): Remove partial dependencies
* 3rd Normal Form (3NF): Remove transitive dependencies

There are <a href="http://en.wikipedia.org/wiki/Database_normalization#Normal_forms">other forms</a>, but they become increasingly tedious.

Basically, the other forms are about dividing a table into smaller tables to avoid anomalies and reduce duplication.

The First Three Forms: 1NF-3NF
========================================================

The first three forms will usually cover most real-world situations adequately.

<p style="width: 640px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/thumb/7/75/NormalFormDiagram.png/640px-NormalFormDiagram.png" alt="normal forms" style="padding-bottom:0.5em;" />Graphic: <a href="http://commons.wikimedia.org/wiki/File:NormalFormDiagram.png">Wikimedia</a></p>


Basic Normalization Tips
========================================================

Break up attributes that ...

* Don't directly relate
* Are inconsistently structured
* Lead to repetition

... into separate entities.

Generally, imagine having to input or maintain the data in your tables. What problems or annoyances might come up?

Detailed Normalization Tips
========================================================

Cardinality:

* Separate tables into natural (real world) entities
* Indentify cardinality (1:1, 1:many, many:many, etc.)
* 1:1 are rare ... think about combining tables
* many:many are common and messy: divide into more tables

Primary Keys:

* Using a "natural" unique identifier is often recommended
* Auto-numbered "id" fields as primary keys avoid problems


Example Logical ERD
========================================================

![example logical ERD](images/exposure_lab_eer6.png)

Hands-on Group Exercise
========================================================

Working as a group create Conceptual and Logical Model ERDs for this use case: Subject takes survey. (Keep it simple.)

![Subject takes survey](images/subject_takes_survey.png)


Discussion
========================================================

Explain your ERDs.

<p style="width: 275px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/e/eb/User_journey_discussion.png" alt="discussion" style="padding-bottom:0.5em;" />Graphic: <a href="http://upload.wikimedia.org/wikipedia/commons/e/eb/User_journey_discussion.png">Jagbirlehl / Wikimedia</a></p>


Confusing Relational Database Terms
========================================================

We have puposely avoided the use of some basic jargon of relational database theory. 

The terms are mathematical in nature and conflict with the terminology of the tools we have just been using. 

The next few slides are included for the curious, but can be safely skipped by the impatient, bored, overwhelmed or confused reader.

Okay, let's get <a href="http://www.merriam-webster.com/dictionary/pedantic">pe · dan · tic</a> ...


Basic Relational Database Terms
========================================================

Here is a comparison of three sets of terms commonly used with relational databases.

| Table | Row | Column |
| ----- | --- | ------ |
| Relation | Tuple | Attribute |
| File | Record | Field |

In our previous diagrams, we have used the terms "Actor" and "Entity". In relational-theory-speak these become "Relations".

---

<p style="width: 473px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/thumb/7/7c/Relational_database_terms.svg/800px-Relational_database_terms.svg.png" alt="relational database terms" style="padding-bottom:0.5em;" />Image: <a href="http://en.wikipedia.org/wiki/File:Relational_database_terms.svg">Wikimedia</a></p>

<p style="width: 473px; float: left; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://www.pythonlearn.com/html-008/cfbook016.png" alt="relational database terms" style="padding-bottom:0.5em;" />Image: <a href="http://www.pythonlearn.com/html-008/cfbook015.html">Charles Severance</a></p>

Relations??
========================================================

> It is a common error to think that "relational" in a database context has something to do with relating data items. It does not. It comes from the mathematical concept of a "relation," basically a collection of data elements that all relate to a single object.

-- Egmont, "relational database technology?", <a href="http://forum.wordreference.com/showthread.php?t=2260355">wordreference.com</a>


Relations versus Relationships
========================================================

A "relation" is different from a "relationship".

* Relation (noun)
* Relationship (verb)

For example:

Artist (relation) performs (relationship) song (relation).

![artist performs song conceptual erd](http://upload.wikimedia.org/wikipedia/commons/thumb/3/3d/Erd-entity-relationship-example1.svg/500px-Erd-entity-relationship-example1.svg.png)


Relations (Tables)
========================================================

A <a href="http://en.wikipedia.org/wiki/Relation_%28database%29">relation</a> is a table organized by rows and columns, according to these rules:

1. Rows represent a unique instance of an entity.
2. Uniquely-named columns are the attributes of an entity.
3. Cells only hold a single value.
4. All cells in a column hold values of the same data type.


In the Coming Sessions...
========================================================

* Building Database Tables
* Database Applications
* Structured Query Language (SQL)

Action Items (videos, readings,  and tasks)
========================================================

<table>
   <tr border=0>
      <td width="128" valign="middle"><img width="128" height="128" alt="watching" src="images/watching.jpg">
      </td>
      <td valign="middle">
         <ul>
            <li><a href="https://www.youtube.com/watch?v=kyGVhx5LwXw">The Relational Model</a> and <a href="https://www.youtube.com/watch?v=IfaqkiHpIjo">Data Modeling and the ER Model</a>
            <li><a href="http://www.youtube.com/watch?v=X_umYKqKaF0">MySQL WB: Intro</a>
            <li><a href="https://www.youtube.com/watch?v=ez6FnOo3Dz0">MySQL WB: Connections and Queries</a>
            <li><A href="https://www.youtube.com/watch?v=owSSIlx1yVg">MySQL WB: sqlinfo.net Course Prerequisite</a>
         </ul>
      </td>
   </tr>
   <tr>
      <td width="128" valign="middle"><img width="128" height="128" alt="readings" src="images/reading.jpg">
      </td>
      <td valign="middle">
               <ul>
            <li><A href="http://practicalcomputing.org/about">PCfB</a> textbook: Chapter6. Scripting with the Shell
            <li>Skim: <a href="http://www.1keydata.com/datawarehousing/data-modeling-levels.html"</a>Data Modeling Levels, <a href="http://inst.eecs.berkeley.edu/~cs186/sp06/lecs/lecture19ER.pdf">Conceptual Design</a>, and <a href="http://classes.soe.ucsc.edu/cmps180/Winter03/Lectures/ER.pdf">ER Lecture</a>
            <li>Skim: <A href="http://en.wikibooks.org/wiki/A-level_Computing/AQA/Problem_Solving,_Programming,_Operating_Systems,_Databases_and_Networking/Databases/Databases">Databases</a>, <a href="http://en.wikibooks.org/wiki/A-level_Computing/AQA/Problem_Solving,_Programming,_Operating_Systems,_Databases_and_Networking/Databases/Primary_keys">Primary Keys</a>, <a href="http://en.wikibooks.org/wiki/A-level_Computing/AQA/Problem_Solving,_Programming,_Operating_Systems,_Databases_and_Networking/Databases/Entity_relationship_modelling">ER Modeling</a>, and <a href="http://en.wikibooks.org/wiki/A-level_Computing/AQA/Problem_Solving,_Programming,_Operating_Systems,_Databases_and_Networking/Databases/Normalisation">Normalisation</a>
            <li>Optional- Skim: <a href="http://www.amazon.com/dp/0123747309">RDDaI3CE</a> textbook: Chapters 5-6
         </ul>
      </td>
   </tr>
   <tr>
      <td width="128" valign="middle"><img width="128" height="128" alt="tasks" src="images/tasks.jpg"></td>
      <td valign="middle">
         <ul>
            <li> Create a <a href="http://en.wikipedia.org/wiki/Conceptual_data_model">Conceptual Data Model</a> (<a href="http://en.wikipedia.org/wiki/Entity-relationship_diagram">ERD</a>) in Visio or similar tool
            <li> Begin creating a <a href="http://en.wikipedia.org/wiki/Logical_data_model">Logical Data Model</a> (<a href="http://en.wikipedia.org/wiki/Entity-relationship_diagram">ERD</a>) in MySQL WB
            <li> Post your diagram(s) in your project wiki
            <li> Refer to the <a href="https://github.com/brianhigh/data-workshop/wiki/Example-Requirements-Document">example</a> in the "Data Management" project wiki
         </ul>
      </td>
   </tr>
</table>


See Also
========================================================

* [UW Libraries Data Management Guide](http://guides.lib.washington.edu/datapub)
* [How to Develop a Data Management and Sharing Plan](http://www.dcc.ac.uk/resources/how-guides/develop-data-plan)
* [C2.com Database Best Practices](http://c2.com/cgi/wiki?DatabaseBestPractices)
* [Normal Forms, without the logic fetish](http://en.wikibooks.org/wiki/Relational_Database_Design/Normalization#Normal_Forms.2C_without_the_logic_fetish)
* [A Simple Guide to Five Normal Forms](http://www.bkent.net/Doc/simple5.htm)
* [The Normal Forms: In a Nutshell](http://rogersaccessblog.blogspot.com/2009/03/normal-forms-in-nutshell.html) (lots of related guides here)
* [Enhanced entity–relationship (EER) model](http://en.wikipedia.org/wiki/Enhanced_entity%E2%80%93relationship_model)
* [MySQL Workbench EER Diagram](https://dev.mysql.com/doc/workbench/en/wb-creating-eer-diagram.html)
* [Gliffy](http://www.gliffy.com/)
* [ERD Tutorial](https://creately.com/blog/diagrams/er-diagrams-tutorial/)
* [DB Designer](http://www.fabforce.net/dbdesigner4/index.php)


Questions and Comments 
========================================================

<p style="width: 380px; float: right; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://upload.wikimedia.org/wikipedia/commons/thumb/2/23/Happy_Question.svg/380px-Happy_Question.svg.png" alt="question" style="padding-bottom:0.5em;" />Image: <a href=http://commons.wikimedia.org/wiki/File:Happy_Question.svg">© Nevit Dilmen</a> / Wikimedia</p>


Some Parting Words
========================================================

> The greatest value of a picture is when it forces us to notice what we never expected to see.

-- <a href="http://www.analyticshero.com/2012/10/25/31-essential-quotes-on-analytics-and-data/">John Tukey, American Mathematician</a>

---

<p style="width: 450px; float: right; clear: right; margin-bottom: 5px; margin-left: 10px; text-align: right; font-weight: bold; font-size: 14pt;"><img src="http://discovermagazine.com/~/media/Images/Issues/2014/March/tardigrade.jpg?mw=900" alt="flammarion" style="padding-bottom:0.5em;" />Source: <a href="http://discovermagazine.com/2014/march/01-inside-a-tardigrade">Andreas Schmidt-Rhaesa, Corinna Schulze and Ricardo Neves/Nikon Small World/Discover Magazine</p>