06 keys

%23%20%2F%2F%2F%20script%0A%23%20requires-python%20%3D%20%22%3E%3D3.13%22%0A%23%20dependencies%20%3D%20%5B%0A%23%20%20%20%20%20%22marimo%22%2C%0A%23%20%20%20%20%20%22marimo-learn%3E%3D0.7.0%22%2C%0A%23%20%20%20%20%20%22polars%3D%3D1.24.0%22%2C%0A%23%20%20%20%20%20%22sqlalchemy%22%2C%0A%23%20%5D%0A%23%20%2F%2F%2F%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.21.1%22%0Aapp%20%3D%20marimo.App(width%3D%22medium%22)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%20%20%20%20import%20marimo_learn%20as%20mol%0A%20%20%20%20import%20sqlalchemy%0A%0A%20%20%20%20db_path%20%3D%20mol.localize_file(%22survey.db%22)%0A%20%20%20%20DATABASE_URL%20%3D%20f%22sqlite%3A%2F%2F%2F%7Bdb_path%7D%22%0A%20%20%20%20engine%20%3D%20sqlalchemy.create_engine(DATABASE_URL)%0A%20%20%20%20return%20engine%2C%20mo%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20from%20marimo_learn%20import%20ConceptMapWidget%2C%20FlashcardWidget%0A%0A%20%20%20%20return%20ConceptMapWidget%2C%20FlashcardWidget%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20Primary%20and%20Foreign%20Keys%0A%0A%20%20%20%20The%20previous%20tutorial%20explained%20how%20to%20combine%20information%20from%20two%20tables%20using%20%60inner%20join%60%20and%20%60left%20join%60.%20This%20tutorial%20will%20explain%20how%20we%20can%20tell%20when%20it%20makes%20sense%20to%20do%20this%2C%20and%20introduce%20our%20first%20complex%20database.%20To%20start%2C%20let's%20look%20at%20a%20diagram%20showing%20the%20four%20tables%20in%20the%20%60survey%60%20database.%0A%0A%20%20%20%20!%5Bsurvey%20tables%5D(%2Fpublic%2Fsurvey_tables.svg)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Let's%20start%20with%20%60person%60%2C%20which%20has%20four%20columns%3A%20%60persond_id%60%2C%20%60personal%60%2C%20%60family%60%2C%20and%20%60supervisor_id%60%20(which%20we%20will%20discuss%20in%20the%20next%20section).%20%60person_id%60%20is%20shown%20in%20__*bold%20italics*__%20to%20indicate%20that%20it%20is%20the%20table's%20**primary%20key**%3A%20each%20row%20in%20the%20table%20has%20a%20non-%60null%60%20%60person_id%60%2C%20and%20each%20of%20those%20values%20is%20unique.%20These%20values%20can%20therefore%20be%20used%20to%20uniquely%20identify%20specific%20rows%20in%20the%20table.%20We%20can%20check%20that%20by%20selecting%20all%20of%20the%20people%20and%20inspecting%20the%20%60person_id%60%20values%20by%20eye%3A%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20person_id%20from%20person%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20A%20better%20way%20is%20to%20count%20the%20number%20of%20rows%20in%20the%20table%2C%20the%20number%20of%20non-%60null%60%20%60person_id%60%20values%2C%20and%20the%20number%20of%20distinct%20person%20ID%20values.%20Remember%2C%20%60count(*)%60%20counts%20rows%2C%20while%20%60count(column_name)%60%20counts%20the%20number%20of%20non-%60null%60%20values%20in%20that%20particular%20column.%20We%20haven't%20seen%20%60count(distinct%20column_name)%60%20before%2C%20but%20as%20you%20might%20guess%2C%20it%20counts%20the%20number%20of%20distinct%20values%20in%20a%20particular%20column.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%0A%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20as%20num_rows%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(person_id)%20as%20num_non_null%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(distinct%20person_id)%20as%20num_distinct%0A%20%20%20%20%20%20%20%20from%20person%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Now%20let's%20take%20a%20look%20at%20the%20%60survey%60%20table.%20Each%20survey%20has%20a%20survey%20ID%2C%20the%20ID%20of%20the%20person%20who%20did%20the%20survey%2C%20and%20the%20survey's%20start%20and%20end%20dates.%20%60survey_id%60%20is%20in%20__*bold%20italics*__%2C%20which%20tells%20us%20that%20each%20survey%20has%20a%20unique%20ID.%20%60person_id%60%2C%20on%20the%20other%20hand%2C%20is%20just%20in%20*italics*%2C%20and%20there's%20an%20arrow%20connecting%20it%20to%20the%20%60person%60%20table's%20primary%20key%2C%20which%20is%20also%20called%20%60person_id%60.%20The%20use%20of%20italics%20and%20the%20arrow%20signals%20that%20%60survey.person_id%60%20is%20a%20**foreign%20key**%2C%20i.e.%2C%20a%20value%20stored%20in%20one%20table%20that%20references%20the%20primary%20key%20of%20another%20table.%20This%20relationship%20tells%20us%20that%3A%0A%0A%20%20%20%201.%20It%20makes%20sense%20to%20use%20%60survey.person_id%20%3D%20person.person_id%60%20as%20a%20condition%20in%20a%20join%20because%20every%20%60survey.person_id%60%20is%20guaranteed%20to%20refer%20to%20an%20existing%20%60person.person_id%60.%0A%20%20%20%202.%20%20Several%20surveys%20might%20refer%20to%20the%20same%20person%20(or%20equivalently%2C%20one%20person%20might%20have%20done%20several%20surveys).%20This%20is%20called%20a%20**one-to-many%20relationship**.%0A%0A%20%20%20%20Let's%20write%20some%20queries.%20Who%20is%20in%20the%20%60person%60%20table%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20*%20from%20person%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20How%20many%20surveys%20has%20Ascensi%C3%B3n%20Sierra%20done%3F%20Her%20%60person_id%60%20is%20%60P001%60%2C%20so%20we%20can%20answer%20the%20question%20by%20filtering%20the%20%60survey%60%20table%20and%20then%20aggregating.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20count(*)%20as%20num_surveys%20from%20survey%0A%20%20%20%20%20%20%20%20where%20person_id%20%3D%20'P001'%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20What%20if%20we%20want%20Ascensi%C3%B3n's%20name%20displayed%20along%20with%20this%20count%3F%20To%20get%20that%2C%20we%20need%20to%20join%20the%20tables.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20person.personal%2C%20person.family%2C%20count(*)%0A%20%20%20%20%20%20%20%20from%20person%20join%20survey%0A%20%20%20%20%20%20%20%20on%20person.person_id%20%3D%20survey.person_id%0A%20%20%20%20%20%20%20%20where%20person.person_id%20%3D%20'P001'%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20What%20if%20we%20want%20to%20get%20Ascensi%C3%B3n's%20full%20name%20in%20a%20single%20column%3F%20We%20can%20do%20that%20by%20concatenating%20her%20personal%20and%20family%20name%20using%20the%20%60%7C%7C%60%20operator%20(which%20is%20sometimes%20called%20%22glue%22).%20As%20the%20output%20of%20the%20query%20below%20shows%2C%20%60%7C%7C%60%20does%20for%20text%20what%20%60%2B%60%20does%20for%20numbers.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20person.personal%20%7C%7C%20person.family%20as%20full_name%2C%20count(*)%0A%20%20%20%20%20%20%20%20from%20person%20join%20survey%0A%20%20%20%20%20%20%20%20on%20person.person_id%20%3D%20survey.person_id%0A%20%20%20%20%20%20%20%20where%20person.person_id%20%3D%20'P001'%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Whoops%3A%20we%20probably%20want%20a%20space%20between%20Ascensi%C3%B3n's%20personal%20and%20family%20names%2C%20so%20we%20will%20glue%20her%20personal%20name%20to%20a%20space%20and%20then%20glue%20that%20to%20her%20family%20name%20(just%20as%20we%20would%20write%201%20%2B%202%20%2B%203).%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20person.personal%20%7C%7C%20'%20'%20%7C%7C%20person.family%20as%20full_name%2C%20count(*)%0A%20%20%20%20%20%20%20%20from%20person%20join%20survey%0A%20%20%20%20%20%20%20%20on%20person.person_id%20%3D%20survey.person_id%0A%20%20%20%20%20%20%20%20where%20person.person_id%20%3D%20'P001'%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Now%2C%20what%20if%20we%20want%20the%20number%20of%20surveys%20done%20by%20each%20person%20ordered%20by%20family%20and%20personal%20name%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20person.personal%20%7C%7C%20'%20'%20%7C%7C%20person.family%20as%20full_name%2C%20count(*)%0A%20%20%20%20%20%20%20%20from%20person%20join%20survey%0A%20%20%20%20%20%20%20%20on%20person.person_id%20%3D%20survey.person_id%0A%20%20%20%20%20%20%20%20group%20by%20person.person_id%0A%20%20%20%20%20%20%20%20order%20by%20person.family%2C%20person.personal%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Notice%20that%20%22%C3%81guila%22%20(with%20an%20acute%20accent)%20comes%20after%20%22Sierra%22.%20Correcting%20this%20mistake%20is%20out%20of%20the%20scope%20of%20this%20tutorial%2C%20but%20can%20be%20done%20by%20installing%20the%20%5BInternational%20Components%20for%20Unicode%5D(https%3A%2F%2Ficu.unicode.org%2F)%20and%20writing%20the%20query%20like%20this%3A%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20select%20*%20from%20person%20order%20by%20family%2C%20personal%20collate%20'es_ES'%3B%0A%20%20%20%20%60%60%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20When%20did%20the%20earliest%20survey%20done%20by%20each%20person%20start%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Which%20people%20have%20done%2017%20or%20more%20surveys%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Just%20as%20%60sum%60%20adds%20up%20all%20the%20values%20in%20a%20column%2C%20%60group_concat%60%20concatenates%20all%20the%20text%20in%20a%20column.%20For%20example%2C%20if%20the%20column%20is%20called%20%60name%60%2C%20then%20%60select%20group_concat(name%2C%20'%3A')%60%20joins%20all%20the%20values%20in%20%60name%60%20with%20colons.%20Use%20this%20to%20write%20a%20query%20that%20generates%20two%20columns%3A%20a%20person's%20full%20name%2C%20and%20a%20comma-separated%20list%20of%20the%20IDs%20of%20the%20survey%20that%20person%20has%20done.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Explain%20what%20the%20following%20query%20produces%20and%20why.%0A%20%20%20%20%3E%0A%20%20%20%20%3E%20%60%60%60sql%0A%20%20%20%20%3E%20select%20person.personal%20%7C%7C%20'%20'%20%7C%7C%20person.family%0A%20%20%20%20%3E%20from%20person%20left%20join%20survey%0A%20%20%20%20%3E%20on%20person.person_id%20%3D%20survey.person_id%0A%20%20%20%20%3E%20where%20survey.survey_id%20is%20null%3B%0A%20%20%20%20%3E%20%60%60%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20Self-Joins%0A%0A%20%20%20%20As%20a%20reminder%2C%20here's%20the%20structure%20of%20the%20survey%20database.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.image(src%3D%22survey_tables.svg%22%2C%20alt%3D%22table%20diagram%20of%20survey%20database%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Notice%20that%20the%20%60person%60%20table%20has%20a%20foreign%20key%20called%20%60supervisor_id%60%20that%20refers%20back%20to%20the%20table's%20own%20primary%20key%2C%20%60person_id%60.%20This%20relationship%20makes%20sense%3A%20supervisors%20are%20people%2C%20so%20they're%20stored%20in%20the%20same%20table%20as%20everyone%20else.%20However%2C%20if%20we%20want%20to%20generate%20a%20list%20of%20people's%20names%20and%20their%20supervisors'%20names%2C%20we%20_can't_%20just%20join%20%60person%60%20to%20%60person%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20*%0A%20%20%20%20%20%20%20%20from%20person%20inner%20join%20person%0A%20%20%20%20%20%20%20%20on%20person.person_id%20%3D%20person.supervisor_id%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20problem%20is%20that%20%60person.person_id%60%20and%20%60person.supervisor_id%60%20are%20ambiguous%3A%20are%20we%20referring%20to%20the%20left-hand%20use%20of%20the%20%60person%60%20table%20or%20the%20right-hand%20use%3F%20To%20resolve%20this%2C%20we%20give%20each%20copy%20of%20the%20table%20an%20**alias**%20using%20%60as%60%2C%20just%20as%20we%20gave%20columns%20names%20using%20%60as%60.%20We%20also%20have%20to%20specify%20the%20columns%20that%20we%20want%20using%20two-part%20%60table.column%60%20notation.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%0A%20%20%20%20%20%20%20%20%20%20%20%20pa.personal%20as%20pa_personal%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20pa.family%20as%20pa_family%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20pb.personal%20as%20pb_personal%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20pb.family%20as%20pb_family%0A%20%20%20%20%20%20%20%20from%20person%20pa%20join%20person%20pb%0A%20%20%20%20%20%20%20%20on%20pa.person_id%20%3D%20pb.supervisor_id%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Joining%20a%20table%20to%20itself%20is%20called%20a%20**self%20join**.%20The%20hard%20part%20is%20figuring%20out%20whether%20%60pa%60%20is%20the%20minion%20and%20%60pb%60%20is%20the%20supervisor%20or%20vice%20versa.%20The%20logic%20is%20that%20the%20supervisor%20of%20person%20%60pb%60%20is%20person%20%60pa%60%2C%20which%20means%20%60pa%60%20is%20the%20supervisor%20and%20%60pb%60%20is%20the%20minion.%20(Alternatively%2C%20we%20can%20inspect%20the%20first%20couple%20of%20rows%2C%20check%20back%20against%20the%20%60person%60%20table%2C%20and%20decide%20that%20way.)%20Let's%20rewrite%20the%20query%20to%20show%20the%20relationship%20explicitly.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%0A%20%20%20%20%20%20%20%20%20%20%20%20pa.personal%20%7C%7C%20'%20'%20%7C%7C%20pa.family%20as%20supervisor%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20pb.personal%20%7C%7C%20'%20'%20%7C%7C%20pb.family%20as%20minion%0A%20%20%20%20%20%20%20%20from%20person%20pa%20join%20person%20pb%0A%20%20%20%20%20%20%20%20on%20pa.person_id%20%3D%20pb.supervisor_id%0A%20%20%20%20%20%20%20%20order%20by%20pa.family%2C%20pa.personal%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Write%20a%20query%20that%20finds%20the%20full%20names%20of%20everyone%20who%20doesn't%20have%20a%20supervisor.%20(Hint%3A%20you%20do%20not%20need%20to%20use%20a%20%60join%60.)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Write%20a%20query%20to%20find%20all%20the%20people%20who%20supervise%20someone%20who%20supervises%20someone.%20(Hint%3A%20you%20will%20need%20to%20join%20three%20copies%20of%20%60person%60%20to%20get%20the%20person%2C%20their%20boss%2C%20and%20their%20grand-boss.)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20Many-to-Many%20Relationships%0A%0A%20%20%20%20Each%20survey%20is%20done%20by%20one%20person%2C%20which%20means%20that%20people%20have%20a%20one-to-many%20relationship%20with%20surveys.%20However%2C%20any%20number%20of%20people%20can%20have%20ratings%20for%20any%20number%20of%20machines%20and%20vice%20versa%2C%20which%20means%20these%20two%20tables%20have%20a%20**many-to-many%20relationship**.%20These%20relationships%20can%20be%20hard%20to%20express%20in%20a%20table%3A%20if%2C%20for%20example%2C%20we%20knew%20that%20people%20never%20have%20ratings%20for%20more%20than%20three%20machines%2C%20we%20could%20add%20%60machine_1%60%2C%20%60machine_2%60%2C%20and%20%60machine_3%60%20columns%20to%20%60person%60%2C%20but%20(a)%20we%20would%20have%20to%20check%20several%20columns%20if%20we%20wanted%20to%20find%20a%20particular%20machine%2C%20and%20(b)%20we%20would%20have%20to%20redesign%20our%20table%20if%20the%20rules%20changed%20and%20people%20could%20have%20ratings%20for%20four%20or%20five%20machines.%0A%0A%20%20%20%20A%20better%20approach%20is%20to%20create%20another%20intermediate%20table%20that%20stores%20the%20relationship%20between%20the%20two%20tables%20we're%20interested%20in.%20Such%20a%20table%20is%20sometimes%20called%20a%20**join%20table**%20because%20its%20main%20purpose%20is%20to%20allow%20us%20to%20join%20two%20other%20tables.%20The%20%60rating%60%20table%20in%20our%20database%20is%20an%20example%20of%20a%20join%20table.%20Each%20row%20stores%20a%20foreign%20key%20into%20%60person%60%20and%20a%20foreign%20key%20into%20%60machine%60%2C%20which%20shows%20that%20the%20person%20has%20some%20relationship%20to%20the%20machine.%20The%20table%20also%20stores%20%60level%60%2C%20which%20is%20the%20actual%20rating%20(or%20%60null%60)%2C%20but%20it%20is%20quite%20common%20for%20join%20tables%20to%20only%20store%20pairs%20of%20foreign%20keys.%0A%0A%20%20%20%20So%2C%20which%20people%20have%20ratings%20for%20which%20machines%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%0A%20%20%20%20%20%20%20%20%20%20%20%20person.personal%2C%20person.family%2C%20machine.machine_type%2C%20rating.level%0A%20%20%20%20%20%20%20%20from%0A%20%20%20%20%20%20%20%20%20%20%20%20person%20join%20rating%20join%20machine%0A%20%20%20%20%20%20%20%20on%0A%20%20%20%20%20%20%20%20%20%20%20%20person.person_id%20%3D%20rating.person_id%0A%20%20%20%20%20%20%20%20%20%20%20%20and%0A%20%20%20%20%20%20%20%20%20%20%20%20rating.machine_id%20%3D%20machine.machine_id%0A%20%20%20%20%20%20%20%20where%0A%20%20%20%20%20%20%20%20%20%20%20%20rating.level%20is%20not%20null%0A%20%20%20%20%20%20%20%20order%20by%0A%20%20%20%20%20%20%20%20%20%20%20%20person.family%2C%20person.personal%2C%20machine.machine_type%0A%20%20%20%20%20%20%20%20%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Which%20people%20have%20a%20level%20of%203%20or%20more%20on%20at%20least%20one%20machine%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Write%20a%20query%20that%20generates%20a%20comma-separated%20list%20of%20the%20machines%20that%20Asensio%20Amaya%20is%20rated%20on%2C%20even%20if%20the%20level%20is%20%60null%60.%20(Hint%3A%20use%20%60group_concat%60.)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%3E%20Many%20of%20the%20%60level%60%20values%20in%20%60rating%60%20are%20%60null%60.%20What%20do%20you%20think%20this%20might%20mean%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20Check%20Understanding%0A%0A%20%20%20%20!%5Bconcept%20map%5D(%2Fpublic%2F06_concepts.svg)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(FlashcardWidget%2C%20mo)%3A%0A%20%20%20%20_widget%20%3D%20mo.ui.anywidget(%0A%20%20%20%20%20%20%20%20FlashcardWidget(%0A%20%20%20%20%20%20%20%20%20%20%20%20question%3D%22Database%20Key%20and%20Relationship%20Concepts%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20cards%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22front%22%3A%20%22Primary%20key%22%2C%20%22back%22%3A%20%22A%20column%20(or%20set%20of%20columns)%20whose%20values%20are%20unique%20and%20non-null%20for%20every%20row%2C%20used%20to%20uniquely%20identify%20each%20row%20in%20a%20table%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22front%22%3A%20%22Foreign%20key%22%2C%20%22back%22%3A%20%22A%20column%20in%20one%20table%20whose%20values%20reference%20the%20primary%20key%20of%20another%20table%2C%20establishing%20a%20link%20between%20the%20two%20tables%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22front%22%3A%20%22One-to-many%20relationship%22%2C%20%22back%22%3A%20%22A%20relationship%20where%20one%20row%20in%20table%20A%20can%20be%20referenced%20by%20many%20rows%20in%20table%20B%20%E2%80%94%20e.g.%2C%20one%20person%20can%20have%20many%20surveys%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22front%22%3A%20%22Many-to-many%20relationship%22%2C%20%22back%22%3A%20%22A%20relationship%20where%20rows%20in%20table%20A%20can%20relate%20to%20many%20rows%20in%20table%20B%20and%20vice%20versa%20%E2%80%94%20requires%20a%20join%20table%20to%20represent%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22front%22%3A%20%22Join%20table%22%2C%20%22back%22%3A%20%22An%20intermediate%20table%20storing%20pairs%20of%20foreign%20keys%20to%20represent%20a%20many-to-many%20relationship%20between%20two%20other%20tables%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22front%22%3A%20%22Self-join%22%2C%20%22back%22%3A%20%22Joining%20a%20table%20to%20itself%20using%20two%20aliases%2C%20used%20when%20rows%20in%20a%20table%20relate%20to%20other%20rows%20in%20the%20same%20table%20(e.g.%2C%20supervisors%20and%20employees)%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A%20%20%20%20_widget%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(ConceptMapWidget%2C%20mo)%3A%0A%20%20%20%20_widget%20%3D%20mo.ui.anywidget(%0A%20%20%20%20%20%20%20%20ConceptMapWidget(%0A%20%20%20%20%20%20%20%20%20%20%20%20question%3D%22Connect%20these%20database%20design%20concepts%20by%20selecting%20a%20relationship%20term%20and%20clicking%20two%20concepts.%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20concepts%3D%5B%22primary%20key%22%2C%20%22foreign%20key%22%2C%20%22one-to-many%22%2C%20%22many-to-many%22%2C%20%22join%20table%22%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20terms%3D%5B%22referenced%20by%22%2C%20%22implemented%20with%22%2C%20%22requires%22%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20correct_edges%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22from%22%3A%20%22primary%20key%22%2C%20%22to%22%3A%20%22foreign%20key%22%2C%20%22label%22%3A%20%22referenced%20by%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22from%22%3A%20%22many-to-many%22%2C%20%22to%22%3A%20%22join%20table%22%2C%20%22label%22%3A%20%22implemented%20with%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22from%22%3A%20%22one-to-many%22%2C%20%22to%22%3A%20%22foreign%20key%22%2C%20%22label%22%3A%20%22requires%22%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A%20%20%20%20_widget%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A