CS50 - FAVORITES AND PROPHECY: PRACTICE PROBLEMS WEEK 7 | SOLUTIONS 2023

[@DorsCodingSchool]

👨‍💻 Learn How to Code with Private Classes - www.dorscodingschool.com/coachingplans
❓Having a hard time with CS50, FreeCodeCamp or Odin Project? Practice with our exclusive free coding platform: www.codingdors.com/
🎯 Are You A Coding Expert? Take Our Free Quiz and Find Out - www.dorscodingschool.com/quiz

[@aswinharish]

Actually this isn't the correct way to do this problem.
You're inserting "student_name" and "house" into relationship table.
It creates repetition of house name in relationship table.
The correct way is to insert "student_id" and "house_id" to relationship table.
Inorder to do this, we need to mention FOREIGN KEY( ) in relatonship table and we need to insert data into "new_students" and "house" tables before inserting data into relationship table
correct query:
db.execute("INSERT INTO relationship (student_id, house_id) VALUES ((SELECT id FROM students WHERE student_name = ?), (SELECT id FROM houses WHERE house = ?))", rel["student_name"], rel["house"])

[@aswinharish]

CREATE TABLE relationship (
id INTEGER,
student_id INTEGER,
house_id INTEGER,
PRIMARY KEY(id),
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(house_id) REFERENCES houses(id)
);

[@عبدالرحمنمجدي-ف2س]

thanks that's helpful

[@ed3137]

@@aswinharish do we need create function ralatetionships to put data or just crate table relationship and then put FOREIGN KEY in

[@ed3137]

thank you I got it, does relationships table shows student_id and house_id right?

[@МаркоКондић]

Is there any need for PRIMARY KEY(id) in relationship table? I don't see any use for it. I came here just to see how other people solved this cs50 practice problem.
This was my way of solving. Pls comment If there is any room to improve.
1) step complete schema.sql
CREATE TABLE students (
id INTEGER,
student_name TEXT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE houses (
id INTEGER,
house TEXT NOT NULL,
head TEXT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE assignments (
student_id INTEGER,
house_id INTEGER,
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(house_id) REFERENCES houses(id)
);
2) step use terminal to create database
$ sqlite3 students.db
Are you sure you want to create students.db? [y/N] y
sqlite> .read schema.sql
3) step created and executed students.py
import csv
from cs50 import SQL
# Open database that I manually created before with schema.sql
db = SQL("sqlite:///students.db")
# Create list to avoid repetation in house table in students.db
houses = []
# Open csv file and read line by line
with open("students.csv", "r") as file:
reader = csv.DictReader(file)
for line in reader:
# Filter data from dictionary "line"
student = line["student_name"]
house = line["house"]
head = line["head"]
# Insert name of students directly from csv to db
db.execute("INSERT INTO students(student_name) VALUES (?)", student)
# Insert house and head of house in database directly from csv to db
if house not in houses:
houses.append(house)
db.execute("INSERT INTO houses(house, head) VALUES (?, ?)", house, head)
# Insert relationship between every student and house/head
db.execute("INSERT INTO assignments(student_id, house_id) VALUES ((SELECT id FROM students WHERE student_name = ?), (SELECT id FROM houses WHERE house = ?))", student, house)

[@edwinchan6521]

Thank you for this, I have been trying to do the practice questions too so I can really get into the thick of it and barely/no one actually does videos for the practice questions. So really, thank you for this!

[@andreavisani9055]

Thank you for this needed explanation!
I watched the lesson, the section and the short, but it wasn't clear how to implement SQL with Python.
Anyway, as is presented in the lectures, the relationship table would be much better with the actual id of the students and the houses, and would be a step forward in the learning process by working with foreign keys

[@leudisfit6001]

thank you

[@davide9227]

Thank you for your videos! what is the website you use to show the table getting updated?

[@ibrahemel-qersh2865]

Thank you for this wonderful explanations!

[@junhuama68]

Hi, question. For adding house into houses list, why not use "if not in houses"? like this "
for row in reader:
if row[house] not in houses:
houses[row[house]]=row[head]
"

[@eurotium]

hi! thanks a lot for your tutorials , i´ve learned a lot.
i was trying to build the third table with foreign keys, and i think i did it, however, when doing that, the third table doesnt show the names of students and houses, but rather the name_id and the house_id . is that the way it was supposed to work ? or was it supposed to fetch the names and houses from the ids ? i failed to understand ...
thanks for helping out !

[@DorsCodingSchool]

We’d love to help you more. Did you know you can join our Discord Group and get help from me and from people around the world that are also learning how to code? dorscodingschool.com/discord

[@junhuama68]

Also I think the relationship table should be student_id and house_id. Not the actual student names and house names

[@andrewsuryadarmahendry]

yeah, i made like this one

[@jotap2284]

thank you

[@ductive]

Thanks

[@BijouBakson]

The name assignments table should include ids of house, head, respectively, not their name.

[@emp1re656]

How do you execute the favorites.sql file?

[@jotap2284]

isnt necesary, its only for save the sql command. Nonetheless you need execute in your sqlite

[@leandroh.sanchezg.499]

I don't understand... Regardless of whether I have written the commands in favorites.sql, should I enter each command, one by one, in the console?

[@haiqal6941]

u can ctrl+c the sql commands, then in terminal just right click (dont ctrl + v)@@leandroh.sanchezg.499

[@peppeciccone9662]

why do you pronounce to -> "ciu"??????