Post on 28-Jul-2019
transcript
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 1
Esercitazione 2
SQL 1(installazione MySQL)
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 2
La famigliaBase di dati contenete la seguente informazioni:
– Informazione sulle persone:• Nome• Sesso• Anno di nascita• Città
– Informazioni sui matrimoni:• Nomi dei coniugi• Data del matrimonio
– Informazioni sulla discendenza:• Relazione genitore-figlio
– Informazioni sui luoghi• Città• Stato di appartenenza• Periodo temporale
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 3
Schema della base di dati
Persone (Nome, Sesso, Anno, Città)
Discendenza (Genitore, Figlio)
Stato (Città, Inizio, Fine, Stato)
Matrimoni (Marito, Moglie, Anno)
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 4
Vincoli di integrità relazionale
– Tra Genitore in Discendenza e Persone– Tra Figlio in Discendenza e Persone– Tra Marito in Matrimoni e Persone– Tra Moglie in Matrimoni e Persone
Hanno senso valori nulli ?– Anno e Città in Persone– Anno in Matrimoni
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 5
Definizione della tabella Persona
create table Persone (
Nome character(20) primary key,
Sesso character(1) not null,
Città character(20),
Anno Numeric(4),
check (Sesso ='M' OR sesso ='F')
);
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 6
Definizione della tabella Discendenza
create table Discendenza (
Genitore character(20),
Figlio character(20),
primary key (Genitore, Figlio),
foreign key(Genitore) references Persone(Nome),
foreign key(Figlio) references Persone(Nome)
);
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 7
Definizione della tabella Stato
create table Stato (
Città character(20),
Inizio numeric(4),
Fine numeric(4),
Stato character(30),
primary key (Città, Inizio, Fine)
);
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 8
Definizione della tabella Matrimoni
create table Matrimoni (
Marito character(20) references Persone(Nome),
Moglie character(20) references Persone(Nome),
Anno numeric(4)
primary key (Marito, Moglie)
);
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 9
La piattaforma di sviluppo• Conviene installare subito una piattaforma completa di sviluppo
web/database, XAMPP 1.8.3, comprendente:
– DBMS (MySQL 5.6.14)
– Web Server (Apache 2.4.7)
– Altre componenti (Tomcat, FileZilla, ….)
• Inizialmente useremo solo la parte DBMS, ma conviene installare direttamente tutto
• Si sconsiglia di scaricare le versioni più recenti, per problemi di compatibilità con l'interfaccia di accesso
• I link aggiornati nella slide seguente
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 10
Link per scaricare XAMMP
Windowshttps://sourceforge.net/projects/xampp/files/XAMPP%20Windows/1.8.3/scaricare l'installatorehttps://sourceforge.net/projects/xampp/files/XAMPP%20Windows/1.8.3/xampp-win32-1.8.3-5-VC11-installer.exe/download
Machttps://sourceforge.net/projects/xampp/files/XAMPP%20Mac%20OS%20X/1.8.3/scaricare l'installatorehttps://sourceforge.net/projects/xampp/files/XAMPP%20Mac%20OS%20X/1.8.3/xampp-osx-1.8.3-5-installer.dmg/download
Linux
https://sourceforge.net/projects/xampp/files/XAMPP%20Linux/1.8.3/Scaricare l'installatore per la versione a 32 o 64 bit secondo necessità
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 11
L'interfaccia di accesso
• Per accedere comodamente a MySQL useremo un'applicazione con interfaccia grafica: MySQL-Workbench
• Scaricare MySQL-Workbench per il proprio SO da:
http://dev.mysql.com/downloads/tools/workbench/
• Eseguire l’installazione del tool
• Dopo aver avviato il server MySQL, ci si potrà connettere con il tooldi interfaccia
• Le credenziali sono: userid root con password vuota
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 12
Avvio del DBMS
• Lanciare il pannello di controllo di XAMPP• Avviare il DBMS MySQL
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 13
Connessione al DBMS
• Lanciare l’interfaccia WorkBench• Connettersi al DBMS (menu: Database>Connect)• Il DBMS è su localhost (127.0.0.1) e ci si logga come root
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 14
L’interfaccaia Workbench
• Consente l’amministrazione e l’interrogazione dei DB
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 15
Creazione della base dati tramite script• Scaricare dal sito il file zip del DB La Famiglia 16, che contiene:
– lo script def-schema.sql (definizione dello schema)– lo script carica-tab.sql (caricamento delle tabelle)– I file .txt che contengono i dati da caricare nelle tabelle
• Scaricare tutti i file nella cartella C:/Basidati/Famiglia• Nel WorkBench (WB) lanciare lo script def-schema.sql
tramite il comando ‘Open SQL script’ del menu ‘File’• Questo comporta la creazione dello schema della base di dati • Nel riquadro a destra compare il nuovo schema di base di dati• Le tabelle sono attualmente vuote, come si può facilmente
verificare
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 16
Creazione dello schema tramite script
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 17
Consultare lo schema delle tabelle
Comando Alter Table dal menu destro del mouse sulla tabella
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 18
Popolazione della base di dati
• Due possibilità:– Inserire le tuple singolarmente– Importare i valori da file
• Comando SQL:load data infile ′<nomefile con path>′
into table <nometabella>
fields terminated by ′<separatore campi>′
lines terminated by ′<carattere fine-linea>′;• Carichiamo ciascuna tabella con un comando singolo, cominciando
da Persone (altre tabelle hanno vincoli verso di essa)
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 19
Consultare la sintassi online
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 20
Il file contenente i dati
• Ciascuna riga contiene i valori degli attributi di una tupla (nell’ordine di definizione)
• Valori separati dal carattere ‘;’ e linee separate da ‘\r\n’
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 21
Lo script di caricamento del DB
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 22
Ispezionare il contenuto
Comando Select Rows dal menu destro del mouse sulla tabella
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 23
Esempi 1Query 1: I nomi e gli anni di nascita di tutte le persone nate a
Messina durante il ‘700
select Nome, Annofrom Personewhere Anno >=1700 AND Anno < 1800
AND Città = 'Messina'
Query 2: Le città in cui sono nate Persone durante il ‘700
select distinct Cittàfrom Personewhere Anno >=1700 AND Anno < 1800order by Città
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 24
Esempio 1 su MySQL
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 25
Esempi 2Query 3: Il nomi dei genitori di tutte le persone di sesso
femminile nate a Roma dopo il 1950
select Genitore, Figlio, Annofrom Discendenza D, Persone Pwhere D.Figlio = P.Nome
AND P.Città = 'Roma'AND P.Anno >= 1950AND P.Sesso = 'F'
order by Genitore
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 26
Esempi 3
Query 4: Tutte le persone nate a Novara durante il Regno di Sardegna
Select Nome, AnnoFrom Persone P, Stato Swhere Persone.Città = Stato.Città
AND P.Anno >= S.Inizio AND P.Anno <= S.FineAND S.Stato = 'Regno di Sardegna'AND P.Città = 'Novara'
order by Anno
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 27
Esempi 4Query 5: Il nomi dei padri di tutte le persone nate a Roma
dopo il 1950
select Genitore as Padre, Figlio, F.Annofrom Discendenza D, Persone F, Persone Pwhere D.Figlio = F.Nome
AND D.Genitore = P.NomeAND F.Città = 'Roma'AND F.Anno >= 1950AND P.Sesso = 'M'
order by Genitore
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 28
Esempi 5Query 6: I nonni di Eugenia II e le rispettive città e anni di
nascita
select N.Genitore as Nonno, Città, Annofrom Discendenza G, Discendenza N,
Persone Pwhere G.Genitore = N.Figlio
AND N.Genitore = NomeAND G.Figlio = 'Eugenia II'
order by N.Genitore
Basi di dati - prof. Silvio Salza - a.a. 2017-2018 E2 - 29
Esempi 6Query 7: Nome delle persone nate a Roma dopo il 1950, e
del loro padre e della loro madre, ordinati per nascita del figlio
select P.Genitore as Padre,M.Genitore as Madre, P.Figlio
from Discendenza P, Discendenza M, Persone PP, Persone PM, Persone PF
where M.Figlio = PF.NomeAND P.Figlio = PF.Nome AND P.Genitore = PP.Nome AND PP.Sesso = 'M'AND M.Genitore = PM.Nome AND PM.Sesso = 'F'AND PF.Città=‘Roma’ AND PF.Anno >= 1950
order by PF.Anno