Creazione della tabella enrollments per collegare studenti e corsi

Facciamo ancora un po' i pignoli. Dai, vediamo passo passo come si crea una struttura MANY-TO-MANY. Pronti?

La relazione MANY-TO-MANY tra studenti e corsi non può essere rappresentata direttamente in una sola tabella. Uno studente può essere iscritto a più corsi, e un corso può essere frequentato da più studenti allo stesso tempo.

Per risolvere questa cosa creiamo una tabella intermedia enrollments, che tiene traccia delle iscrizioni, cioè quale coppia studente-corso esiste. Questo ci permette non solo di mantenere l'integrità dei dati, ma anche di estendere facilmente le funzionalità, tipo aggiungere la data di iscrizione.

Com'è fatta la tabella enrollments?

Ormai lo sai, la tabella enrollments sarà il nodo centrale tra le tabelle students e courses. Ecco la sua struttura:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- ID univoco della riga
    student_id INT REFERENCES students(student_id), -- Foreign key verso la tabella students
    course_id INT REFERENCES courses(course_id),    -- Foreign key verso la tabella courses
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Quando lo studente è stato iscritto al corso
);

Vediamo ogni riga:

• enrollment_id: È l'identificatore univoco di ogni riga. Ogni studente iscritto a un corso deve essere identificato in modo unico.
• student_id: Indica quale studente è iscritto. È una foreign key che punta alla tabella students (colonna student_id).
• course_id: Indica a quale corso lo studente è iscritto. Questa colonna è collegata alla tabella courses (colonna course_id).
• enrollment_date: Una cosa utile che mostra la data di iscrizione. Usiamo DEFAULT CURRENT_DATE per mettere automaticamente la data corrente quando creiamo la riga.

Creazione delle tabelle students e courses

Prima di andare avanti, assicuriamoci di avere già le tabelle students e courses:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- Identificatore univoco dello studente
    name TEXT NOT NULL,                 -- Nome dello studente
    email TEXT NOT NULL UNIQUE          -- Email dello studente, niente doppioni
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- Identificatore univoco del corso
    title TEXT NOT NULL,                -- Titolo del corso
    description TEXT,                   -- Descrizione del corso
    start_date DATE                     -- Data di inizio corso
);

Nota che qui abbiamo aggiunto dettagli utili, tipo l'email unica per gli studenti e la descrizione del corso nella tabella courses.

Colleghiamo tutto insieme

Ora che le nostre tabelle sono pronte, creiamo la tabella enrollments:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- ID univoco dell'iscrizione
    student_id INT NOT NULL REFERENCES students(student_id), -- Foreign key
    course_id INT NOT NULL REFERENCES courses(course_id),    -- Foreign key
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Data di iscrizione
);

Inserimento dati nelle tabelle

Ok, le tabelle ci sono, ma sono tristi senza dati. Aggiungiamo qualche studente, corso e le loro iscrizioni:

Inserimento studenti:

INSERT INTO students (name, email)
VALUES
    ('Alex Lin', 'alex.lin@example.com'),
    ('Maria Chi', 'maria.chi@example.com'),
    ('Otto Song', 'otto.song@example.com');

Inserimento corsi:

INSERT INTO courses (title, description, start_date)
VALUES
    ('Fondamenti di programmazione', 'Corso per programmatori principianti.', '2023-11-01'),
    ('Basi di dati', 'Studiamo SQL e database relazionali.', '2023-11-15'),
    ('Sviluppo web', 'Creazione di siti e applicazioni web.', '2023-12-01');

Inserimento iscrizioni:

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin su "Fondamenti di programmazione"
    (1, 2), -- Alex Lin su "Basi di dati"
    (2, 2), -- Maria Chi su "Basi di dati"
    (3, 3); -- Otto Song su "Sviluppo web"

Qui student_id e course_id corrispondono agli identificatori nelle rispettive tabelle.

Controlliamo le relazioni con le query

Recupero di tutte le iscrizioni:

SELECT e.enrollment_id, s.name AS student_name, c.title AS course_title, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

Risultato:

Esercizio per fare pratica

Prova ad aggiungere altri studenti e corsi, poi iscrivili nella tabella enrollments. Per esempio, aggiungi il corso "Apprendimento automatico" e iscrivi lì 1-2 studenti. Usa la query sopra per controllare il risultato.

Errori tipici che puoi incontrare

Lavorando con le foreign key e le tabelle intermedie ci sono alcune trappole in cui si cade spesso:

1. Manca la riga nella tabella principale: Se provi ad aggiungere una riga in enrollments con student_id o course_id che non esistono nelle tabelle students o courses, ti becchi un errore. La foreign key lo controlla alla grande.

2. Violazione dell'integrità dei dati quando elimini: Se elimini uno studente o un corso che sono già usati nella tabella enrollments, senza aver impostato ON DELETE CASCADE, ti darà errore.

3. Duplicazione delle righe: Assicurati di non iscrivere lo stesso studente allo stesso corso più volte, a meno che non sia previsto dalla logica di business.

Ora hai un modello funzionante per rappresentare la relazione MANY-TO-MANY tra studenti e corsi in PostgreSQL. Questa struttura si usa un sacco nelle applicazioni reali, tipo sistemi di gestione dell'apprendimento, CRM e tanti altri. Avanti con la prossima lezione!

2

Compito

SQL SELF, livello 20, lezione 1

Bloccato

Creazione della tabella `enrollments`

Creazione della tabella `enrollments`

Apri

2

Compito

SQL SELF, livello 20, lezione 1

Bloccato

Visualizzazione dei dati uniti sulle iscrizioni

Visualizzazione dei dati uniti sulle iscrizioni

Apri