# NOTES ON THE LINKEDIN COURSE

this is just a spot for us to type up notes on the [LinkedIn Learning course we're watching](https://www.linkedin.com/learning/programming-foundations-databases-2/why-use-a-database?u=2194065).

## 0. INTRODUCTION

### [WHY USE A DATABASE?](https://www.linkedin.com/learning-login/share?forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fprogramming-foundations-databases-2%3Ftrk%3Dshare_ent_url&account=2194065)

0:29 => *"these are found in many places from banking and order applications to mobile apps, etc."*

It's really key to point out that databases are EVERYWHERE -- so it's almost harder to figure out something they're NOT good for than the reverse.

Every experience you have on the internet that feels large and unbounded -- shopping for [necessities](https://www.amazon.com/Colors-Rainbow-Netting-Fabric-Ribbon/dp/B07JLMJ18T/ref=sr_1_7?dchild=1&keywords=tulle&qid=1603136145&sr=8-7) on Amazon, hopping from [clip](https://www.youtube.com/watch?v=SwYN7mTi6HM) to [clip](https://www.youtube.com/watch?v=oRdxUFDoQe0) to [clip](https://www.youtube.com/watch?v=ZcJjMnHoIBI) on Youtube, checking out [Maine yurts on AirBnb](https://www.airbnb.com/rooms/7671036?source_impression_id=p3_1603135168_zFd%2BklO3HPNGAPeP) -- all of these experiences are rendered possible by databases.

And what's cool, from a teaching and learning perspective, is that if the database as a form is capable of encoding a world as complex as Amazon's system of products, or YouTube's web of videos, or AirBnB's [better-as-an-idea-than-in-reality, hipster-targeting "experiences"](https://www.airbnb.com/experiences/1236954?currentTab=experience_tab&federatedSearchId=3568a7be-d026-448b-96d7-2e11e8b5cd93&searchId=&sectionId=4a148d0e-1e8d-4a45-ba55-041983c4dbea&source=p2), then I think one could argue that it's the perfect medium for academic work.

And, obviously, databases are already used across most of Harvard's disciplines for *research* . . . but what might it look like as a *teaching and learning tool?*

### [WHAT YOU SHOULD KNOW](https://www.linkedin.com/learning/programming-foundations-databases-2/what-you-should-know?u=2194065)

talking about databases in general, not specifics. This is good for us, because Airtable isn't all that much like an SQL DB from a user's perspective.

### [Understanding databases: Benefits of structured data](https://www.linkedin.com/learning/programming-foundations-databases-2/understanding-databases-benefits-of-structured-data?u=2194065)

"...We can see why adding structure to data can help us answer questions, and why we might choose a database instead of just a spreadsheet" - this is kind of a guiding thought/question I have as we move *everything*(?) to Airtable - DD

* Katie thinks about this in terms of what she wants to save and link back to existing structures.
* Enthusiasm for having a unique ID for everyone who interacts with us, so everything that can go into airtable should (even if that's not all of the text, but a link to the google drive doc that was written). so wherever it is in google docs, it all is linked under their name in airtable.

## 1. DATABASE FOUNDATIONS

### [RELATIONAL DATABASES](https://www.linkedin.com/learning/programming-foundations-databases-2/relational-databases-2?resume=false&u=2194065)

* Tables as entities
* Rows as instances
* Columns/Fields as information about each instance (each will have a defined type)

### [KEYS AND UNIQUE VALUES](https://www.linkedin.com/learning/programming-foundations-databases-2/keys-and-unique-values-2?resume=false&u=2194065)

The part about unique values in tables is interesting, because in our airtable bases, we tend to use formulas nade of concatentating other attributes in two or more columns.

QUESTION: do we think of the key as that first field? or as the Airtable Record ID?
* there is a unique airable key that is often hidden. when do we want to hide it or have it shown?
* do we always need a formula as a fake record ID? when are times we want this, and when do we not need this? note: in automations, you only see the primary field without needing a lookup

### [ACID AND TRANSACTIONS](https://www.linkedin.com/learning/programming-foundations-databases-2/acid-and-transactions-2?resume=false&u=2194065)

note: getting into specifics, kind of a glance "under the hood"of databases. good to know as a reference, but info about ACID and CRUD and communicating with the database through SQL are mostly taken care of for us by airtable's gooey ui/ux.

question: but do we change this when we start adding automations and external data?

^I'm interested in this question too! I was thinking of automations while watching this section. How else would airtable use ACID? Automations seem to abide by these principles, but they're still (of course) not foolproof. -DD

### [BASIC SQL](https://www.linkedin.com/learning/programming-foundations-databases-2/basic-sql?resume=false&u=2194065)

not necessarily relevant for us?

Though it is interesting to compare and contrast SQL queries and Airtable Views and Filters.
* Views are for sure easier to grasp, but "feel" more static and permanent than a line of code that you type once.
* Also I suppose that code-like queries are possible in the scripting blocks you can add
* Views are restricted to a table, and any queries spanning multiple tables are "built in" from advance, because you need to have added a column that links to another table

Like this sample query code to list out customer orders (data which exists across two tables: one for customers, one for orders) would be done with a column in customers that links to orders (and vice versa), then a filter in customers to show only customers with orders:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

If for some reason we want to be able to create

### EXTRA NOTES & QUESTIONS

And here it gives us [a good overview of relationships](https://www.linkedin.com/learning-login/share?forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fprogramming-foundations-databases-2%3Ftrk%3Dshare_ent_url&account=2194065).

## 2. TABLES

### [MODELING AND PLANNING A DATABASE](https://www.linkedin.com/learning/programming-foundations-databases-2/modeling-and-planning-a-database?resume=false&u=2194065)

This feels like a good step for us to think about performing for Bok (coming up with a list of all the entities at Bok we want to track, the properties of these entities we might care about, and then some relationships between these entities).

Capitalization conventions = Capitalize table names, upper-CamelCase (PascalCase) field names.

Avoid using spaces in field names.

### NAMING TABLES

Simple notes: tables should be labeled with the plural of the object they track.

### COLUMNS AND DATA TYPES

Fields should be labeled with the singular of the object they track.

Airtable offers us different options than the ones he lists, but it's still good to check these out.

Q: is it true that the only difference between long and short text fields on airtable is in the view? or are they really stored differently? (like `VARCHAR(50)` or `TINYTEXT` vs `LONGTEXT`?)

### NUMBERS AND OTHER TYPES

note: in AT, the date is a date object rather than a string, meaning that you can manipulate the way you view it down the line

### PRIMARY AND FOREIGN KEYS

In Airtable, the primary key is the recordID, but this is hidden from you by default. Would we ever want to make this visible in a convenience column by default in every Main Base we create?

## 3. RELATIONSHIPS

One-to-many most common relationship used

Use primary key to represent data (it never changes and is unique) when creating relationships

One-to-one is not commonly used. Often used to protect secure information.

"In most DBMS tools, we can't model a many-to-many relationship directly. so we need to create a linking table, which has a one-to-many relationship with both of the tables we want to use." - is this true in Airtable too? (particulary with the examples used in the video uses, I am wondering if airtable's linked records solve this problem without needing a linking table?) As we are thinking this through, the LPCE airtable might be a good example/case study- it's small enough, and already running into these issues -DD

Referntial integrity: is this also seamlessly taken care of by Airtable? What are examples of this happening in Airtable? -DD

* In the LPCE example, can we create a table of just questions, and build forms from that?
* Idea of creating more bases and tables - so bases just for forms per program?
## 4. DATABASE OPTIMIZATION

### First Normal Form (1NF)
* requires us to have one (and only one) value in each cell ---- so multiple select, and multiple linking records would violate this? but that seems intentional, so then we wouldn't need a new table... but are there times when we wouldn't want to to get around this, when we would want to create a new table instead of violating 1NF? -DD

* if the order of thee records is important, that's a red flag! you will want to use an auto-incrementing unique value or a timestamp to indicate that instead

### Second Normal Form (2NF)
* for every column in a table that isn't a key, values must be dependent on the full and whole key, not only part of the key. the values must describe something about the row that we can't determine from part of the key

### Third Normal Form (3NF)
* we shouldn't be able to figure out any value in any column, from a field that isn't a key.

### Denormalization
* the process of intentionally duplicating information in a table, in violation of normalization rules
* it's a trade-off: gaining speed may reduce consistency