# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Database Design Concepts
# ---------------------------------------------------------------------------------------------------------
#
# In our previous session on databases, we introduced some of the fundamental concepts and definitions applicable to databases in general, along with a brief intro to SQL and SQLite in particular. Some use cases and platforms were also discussed.
#
# In this session, we are going to dig a little deeper into databases as representions of systems and processes. A database with a single table may not feel or function much differently from a spreadsheet. Much of the benefit of using databases results from designing them as models of complex systems in ways that spreadsheets just can't do:
#
# * Inventory control and billing
# * Human resources
# * Blogging platforms
# * Ecosystems
#
# There will be some more advanced SQL statements this time, though we will still be using SQLite. Concepts which will be discussed and implemented in our code include
#
# * Entities and attributes
# * Keys
# * Relationships
# * Normalization
#
# For this session we are also going to play as we go. We will use data from the Portal Project Teaching Database:
#
# > <NAME>; <NAME>; <NAME>; <NAME>. (2018): Portal Project Teaching Database. figshare. Dataset. https://doi.org/10.6084/m9.figshare.1314459.v10 
#
# Go to the item record in figshare and click on the button to _Download all_. Download and unzip the data to your preferred location on your computer.

# # The Entity Relationship Data Model
# ------------------------------------------------------------------------------------------------
#
# The entity relationship (ER) model is commonly used to define and develop databases. In the simplest terms, the model defines the things (entities) that are important or interesting within a system or process and the relationships between them.
#
#
# ## Design Round 1: A flat, spreadsheet-like table
#
# For this part of the workshop, we will use Jamboards to collaboratively identify the entities represented within the data. 
#
# > #### Exercise
# >
# > 1. Go to the Jamboard shared in the workshop chat channel. 
# > 1. Go to the folder with the data just downloaded from figshare. Open the file *combined.csv*.
# > 1. We have also shared a file with some example field notes (*field_notes.xslx*). Try adding the survey information > from the field notes to *combined.csv*. What problems or issues do you run into? Add notes to the Jamboard.
# > 1. It turns out there was an error in data collection. All of the data in *combined.csv* with a date of March 5, 2000, was actually collected on March 6. Try to update all the affected records. As before, add notes to the Jamboard about any issues you encounter while doing this.
#
# Some of the columns in the CSV file have dependencies on information from other columns. We can simplify our data entry and reduce the risk of human error if we split or decompose our single table into multiple tables to eliminate these dependencies.

# The entity relationship model provides a process for developing a more robust representation of the system we are observing with our survey data.
#
# The following provides a useful example of an ER diagram, and includes each of the concepts to be discussed below:
#
# ![Entity Relationship example diagram](./images/1011px-ER_Diagram_MMORPG.png)
#
# By <a href="https://en.wikipedia.org/wiki/User:TheMattrix" class="extiw" title="en:User:TheMattrix">TheMattrix</a> at the <a href="https://en.wikipedia.org/wiki/" class="extiw" title="w:">English language Wikipedia</a>, <a href="http://creativecommons.org/licenses/by-sa/3.0/" title="Creative Commons Attribution-Share Alike 3.0">CC BY-SA 3.0</a>, <a href="https://commons.wikimedia.org/w/index.php?curid=2278339">Link</a>
#
#
# ## Entities
#
# Entities are *nouns*, and can be physical or logical:
#
# * People - teachers, students, courses
# * Places - stores, websites, states
# * Things - donuts, grades, purchases
#
# Entities are represented as tables within a database. 
#
# ## Attributes
#
# Entities have properties or attributes which describe them. For each attribute there is domain, or a range of legal values. Domains can be limited by data type - integer, string, etc. - and may be further limited by allowable values. For example, the domain of month names is limited to January, February, etc.
#
# There are several types of attributes:
#
# * __Simple attributes__ are atomic values which cannot be decomposed or divided. Examples include _age_, _last name_, _glaze_, etc.
# * __Composite attributes__ consist of multiple simple attributes, such as _address_, _full name_, etc.
# * __Multivalued attributes__ can include a set of more than one value. _Phone numbers_, _certifications_, etc. are examples of multivalued attributes.
# * __Derived attributes__ can be calculated using other attributes. A common example is _age_, which can be calculated from a date of birth.
#
# ## Keys
#
# A key is an attribute or combination of attributes which can be used to uniquely identify individual entities within the entity set. That is, keys enforce a uniqueness constraint.
#
# There are multiple types of keys. 
#
# * A __candidate key__ is a simple or composite key that is both unique and minimal. _Minimal_ here means that every included attribute is needed to establish uniqueness. A table or entity set may have more than one candidate keys.
# * A __composite key__ is a key composed of two or more attributes. Composite keys are also minimal.
# * A __primary key__ is the candidate key which is selected to uniquely identify entities in the entity set.
# * A __foreign key__ is an attribute that references the primary key of another table or entity set in the database. A __foreign key__ is not required to be unique within its containing table. 
#
#
# ## Design Round 2: Defining entities and attributes
#
# > #### Exercise
# >
# > 1. In the Jamboard, the columns in *combined.csv* have been added as stickies. Each column can be considered an attribute of an entity, for example a plot. Working together, rearrange the stickies into groups of attributes describing a single entity. Use a marker or another sticky to name to name the entity (for example "plot").
# > 1. Use DB Browser to open the *portal_mammals.sqlite* database included in the data we downloaded from figshare. Compare the data table definitions with the entities and attributes defined in the previous step.
# >> 1. Identify an attribute or group of attributes that uniquely identify individual entities. Circle it - this is the primary key.
#
# #### Creating tables
#
# It may be useful to have information about the person who collected the survey data in the field. Let's add a new table, "recorder," to hold this information. What are some attributes of this entity?
#
# * first name
# * last name
# * what else?
#
# Use the _Modify Table_ feature to create a table and add additional attributes for "status" (undergraduate, graduate, staff) and date of birth: 
#
# ```
# DROP TABLE IF EXISTS recorder;
# CREATE TABLE IF NOT EXISTS recorder ('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
#                                      'fname' TEXT,
# 									 'lname' TEXT NOT NULL,
#                                      'status' TEXT NOT NULL CHECK(status="undergraduate" OR
#                                      status="graduate" OR status="staff"),
#                                      'dob' TEXT);
# ```
#
# Below is the syntax for adding data to the new table:
#
# ```
# INSERT INTO recorder (fname, lname, status, dob) VALUE ("Joe", "Smith", "staff", "1985-03-22");
# ```
#
# Refer to the data table definition above - some fields can be left blank and others can't. The _status_ field also has a constraint on the values that can be provided. 
#
# > #### Exercise
# >
# > What happens if we try to enter the following?
# >
# > ```
# > INSERT INTO recorder (fname, status, dob) VALUE ("Jane", "faculty", "Sept. 31, 3022");
# > ```
# > 1. Correct the errors in the insert statement above until it works. Leave the value of the date field as-is. What do you notice?
# > 1. Use the same syntax to add some additional people to the table.

# # Relationships
# ------------------------------------------------------------------------------------------------------------
#
# Relationships represent connections between entities. In keeping with the idea that entities are nouns, relationships are verbs. The MMORPG example above demonstrates this: a character _has_ an account, a region _contains_ characters.
#
# _Cardinality_ determines the type of relationship that exists between two entities.
#
# * __One to many (1:M)__: In the example above, region -> character is a 1 to many relationship. That is, one region can have many characters in it.
# * __One to one (1:1)__: Not in the diagram above. One to one relationships indicate possible design issues when entities might really reference the same things.
# * __Many to many (M:N)__: In the example, character and creep have a many to many relationship. Within a databae, these need to be implemented as a set of 1:M relationships with. 
#
#
# > #### Exercise
# >
# > 1. Identify the relationships between the entities in our database. Since we are working with text, we will use notation similar to the examples as [https://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html](https://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html):
# >
# >> Plot -> Species; 1 plot can contain multiple species -> 1:N
#
#
# # Normalization
# ------------------------------------------------------------------------------------------------------------
#
# Normalization is a process of analyzing entities and attributes to reduce redundancy and prevent anomalies:
#
# * Update anomaly: Redundant values within a table must be updated multiple times. In the example below, if Smith's favorite donut changes, the table has to be updated twice. Otherwise, there will be inconsistent  values.
# * Delete anomaly: Deleting data forces the deletion of other attributes. For example, removing apple cider donuts from our table would also force the deletion of Wilson and Wilson's dependent, Pete. (Remeber that DELETE operations delete a whole row, not just a single attribute value.)
# * Insert anomaly: Data cannot be added to the table without also adding other attributes. If null values are not allowed in the *Favorite_Donut* column, it becomes impossible to add information about an employee who doesn't have a favorite donut.
#
#
# | EmployeeID | LName      |Favorite_Donut | Dependent |
# |------------|------------|---------------|-----------|
# | 115        | Smith      | glazed        | James     |
# | 115        | Smith      | glazed        | Sandy     |
# | 116        | Wilson     | apple cider   | Pete      |
#
#
# Normalization involves removing dependencies among attributes to improve the logical structure and consistency of a database.
#
# There are progressive degrees of normalization across multiple _normal forms_ (NF). There are six normal forms, but generally a database is considered normalized if the tables satisfy the requirements of the first three NF.
#
# * 1NF: No repeating columns. 
# * 2NF: A table must be 1NF AND the primary key is either a single attribute or, if composite, each non-key attribute must be dependent on the entire key for uniqueness. That is, eliminate redundant values.
# * 3NF: A table must be 2NF AND eliminate transitive dependencies. That is, remove non-key attributes that depend on other non-key attributes. For example, consider the following alternative table definition for _species_:
#
# ```
# CREATE TABLE species (
#     'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
#     'taxon' TEXT,
#     'common_name' TEXT,
#     'count' INTEGER,
#     'notes' TEXT
# );
#
# ```
#
# The attributes _count_ and _notes_ depend on each other as survey attributes. We need a way to link this information with species _per survey_ by creating a dependent entity to resolve the M:N relationship between species and surveys.
#
# ## Design Round 3: Data Integrity
#
#
# ### CRUD: Create, Read, Update, Delete
#
# We have already created a table above. Let's look at how we can view and manipulate the data, and whether changes we make to the data affect or "break" the database.
#
# Above we listed some anomalies that normalization is supposed to prevent. In the SQL tab of DB Browser, we can recreate the data as represented in *combined.csv* using the following query:
#
# ```
# SELECT surveys.*, plots.plot_type, species.genus, species.species, species.taxa
# FROM surveys
# INNER JOIN plots ON surveys.plot_id = plots.plot_id
# INNER JOIN species ON surveys.species_id = species.species_id
# ```
#
# Now that we have identified the relationships between the entities in the database, we can modify the _surveys_ table to reference the ID fields of the _plots_, _species_, and _recorder_ tables as foreign keys.
#
# 1. In the "Database Structure" tab of DB Browser, right click _surveys_ and select "Modify Table."
# 1. Add a column, *recorder_id*. Set the data type to "integer." To the right, under the "Foreign Key" setting, select the "recorder" table from the first drop down list, then select "id" from the second drop down list.
# 1. Add the corresponding IDs from the _plots_ and _species_ tables as foriegn keys to the "plot_id" and "species_id" fields.
#
# > #### Exercise
# >
# > 1. Update the SQL statement for the combined view to include the recorder's last name and status.
#
# We have just made a lot of changes to the structure of our database. How do these changes affect our ability to query (or Read) the data? Let's go back and redo some of the examples from last week, which will also give us an opportunity to refresh our memories of the SQL syntax.
#
# In DB Browser we can insert data into tables two ways. In addition to using **INSERT** statements as above, we can also manually edit a table in the "Browse Data" tab.
#
# > #### Exercise
# >
# > 1. Use one of the two methods to add survey data from *field_notes.xslx* to the *surveys* table.
#
# Let's also correct the date error for surveys recorded as March 5, 2000. We can do this manually in the table itself by filtering, but in this case it's preferred to use SQL. First, we will select the data we need to update in order to see which rows will be affected:
#
# ```
# SELECT * 
# FROM surveys
# WHERE month = "3" AND day = "5" AND year = "2000";
# ```
#
# How many rows will be affected by the update? Once we are ready, we can update the table:
#
# ```
# UPDATE surveys
# SET day = '6'
# WHERE month = "3" AND day = "5" AND year = "2000";
# ```
#
# Finally, we can add information about who recorded the surveys. Here's an example where every rodent exclosure since 1995 was surveyed by <NAME>:
#
# ```
# UPDATE surveys
# SET recorder_id = 1
# WHERE plot_id = 5 AND year > 1995
# ```
#
# Finally, we can delete data using the **DELETE** clause. Our data don't lend themselves well to deletion - deleting rows from any one table shouldn't break the other tables or views, but there's also no benefit to deleting anything. An example where deletion would make sense would be to remove rows from a _product_ table in an inventory database after that product is discontinued.
#
# But for the sake of demonstration, let's say we decide to remove one of our species, *Neotoma albigula*. We could delete the species info from the _species_ table using:
#
# ```
# DELETE FROM species
# WHERE species_id = "NL";
# ```
#
# > #### Exercise
# >
# > 1. The above SQL command should have thrown an error. What is the error, and why might it be a good thing? If we still want to delete these data, what do we have to do?
# > 1. Why would or wouldn't we also want to remove corresponding rows from the _surveys_ table? If we were going to delete rows from both tables, which would we delete rows from first?
#
# > #### Exercise
# >
# > 1. Pick one of the people added to your _recorder_ table earlier. Update the _surveys_ table to add their ID information to specific survey observations based on plot or species.
# > 1. Sometimes species names change. Pick a species and use an **UPDATE** query to change its name in the _species_ table. Then re-run the combined view. Has the changed been applied to all the affected rows? How can you double check without having to scroll through the entire view?

# # References
#
# <NAME> and <NAME> (n.d.) Databse Design - 2nd Edition. Retrieved from [https://opentextbc.ca/dbdesign01/](https://opentextbc.ca/dbdesign01/)
#
# Datanamic (n.d.) Database normalization. Retrieved from [https://www.datanamic.com/support/database-normalization.html](https://www.datanamic.com/support/database-normalization.html)

# + jupyter={"outputs_hidden": true}


StarcoderdataJupyterScriptsDedupFiltered