Scarica Appunti Database Relazionali e più Appunti in PDF di Database Relazionali solo su Docsity!
Sommario
Cos’è un database?............................................................................................................................................ 1 I database e i suoi motori .................................................................................................................................. 1 Database Administrator e Relazioni .................................................................................................................. 2 Progettazione del database ............................................................................................................................... 3 Esercizio 1 svolto e corretto .......................................................................................................................... 5 SQL (Structure Query Language) ....................................................................................................................... 7 DDL e DHL ...................................................................................................................................................... 7 MySQL Workbech e create database ............................................................................................................ 8 La sintassi del CRUD....................................................................................................................................... 8 La clausola Distinct .................................................................................................................................. 12 Show e Show Tables .................................................................................................................................... 12 Inner Join ..................................................................................................................................................... 12 SubQuery ..................................................................................................................................................... 14 Integrità Referenziale e Foreign Key ........................................................................................................... 14 Esempio di Query effettuate ....................................................................................................................... 14
Cos’è un database?
Un database è un archivio di dati organizzato in modo omogeneo, perché ogni elemento è
strutturato nello stesso modo (immaginiamo che ogni elemento sia un “oggetto”, che ha
specifiche proprietà).
La struttura si costruisce ponendo “domande” dette “campi” , mentre le risposte sono dette
“valori di campo”.
es. nome: Valentina;
Affinché un database possa avere una scheda correttamente registrata , bisogna compilare dei
“campi obbligatori”; similmente, esistono “campi non obbligatori” cioè campi che non è
necessario inserire per la registrazione. Ogni scheda (elemento) del database ha un “campo
chiave” , chiamato “Primary Key” che identifica in maniera univoca ogni scheda del database.
Tutta la scheda, con campi e Primary Key, è detta “Record”; l’insieme dei record è chiamato
“tabella”, cioè il database.
Per ogni campo, come in Java, deve essere assegnato un “tipo” di dato, cioè se è un intero, una
stringa o altro.
I database e i suoi motori
Esistono diverse tipologie di database, il primo file di tipo database era di tipo: Campo (^) Valore di campo
*.DBF (Database Format), dove ogni file era una tabella da lì si sono sviluppati in:
*1. .XSLX , cioè “excel”
- *MDB , cioè “access”: in un unico file, abbiamo più tabelle (le tabelle sono l’unico posto fisso dove risiedono i dati), che possono essere “interrogate” con le “Query”. In access sono presenti anche le “maschere” cioè interfacce grafiche per l’utilizzo dei dati, mentre i “report” sono stampe fisiche dei dati presenti. In ultima, vi sono le “macro”, che simulano un linguaggio di programmazione. All’interno delle macro, vi è un linguaggio di programmazione chiamato “Visual Basic For Application “ cioè VBA , ed è presente solo in Access. Grazie a questa completezza, è diventato uno strumento molto usato dalle piccole aziende.
- Oracle sono “ambienti”, cioè richiedono l’installazione dell’istanza,* si definiscono
- MySQL “database relazionali”. Cioè posso creare più tabelle relazionate tra loro
- SQL Server 4. PostgreSQL *processo di installazione del database, ogni istanza è a sé.
- MongoDB
Database Administrator e Relazioni
In una “Istanza”, posso gestire più tabelle, appartenente a più gruppi. Per lavorare con le istanze posso accedere da qualsiasi parte voglia, compilando le credenziali , composte da “nome utente” e “password”. Colui che gestisce l’istanza è il Database Administrator (DBA), cioè gestisce gli accessi al database creando nuovi utenti per l’accesso. Esso “logga”, cioè registra tutti i movimenti effettuati all’interno del database, in quanto responsabile penalmente per il database. La relazione tra le tabelle può essere di tipo “logica” cioè è stabilita dal programmatore, che deve controllare i dati, gestirli e manipolarli. La relazione “fisica” invece significa portare a conoscenza il database che le tabelle sono relazionate creando la “Foreign Key” cioè chiave esterna, così facendo, se inserisco dati non esistenti, non me lo farà inserire. Creando la Foreign Key applico “L’integrità Referenziale” , cioè che le tabelle hanno una foreign key, in modo che il database garantisca l’integrità referenziale dei dati, cioè la relazione corretta tra i database. Le foreign key possono essere multiple in una tabella, a differenza della primary key che deve essere univoca. La relazione tra “cliente” e “fatture” è detta 1: infinita perché un cliente può avere più fatture ma la fattura non può avere più clienti. La relazione viene mostrata graficamente con frecce come di seguito. Max 300.000 schede Sono Motori di Database
In questa situazione , ogni veterinario può avere più specializzazioni , e ogni specializzazione può essere assegnata a più veterinari. Per ottimizzare l’anomalia, creiamo una nuova tabella dove inseriremo le foreign key delle tabelle Veterinari e Specializzazioni (le altre tabelle non avranno Foreign Key).
Esercizio 1 svolto e corretto (^) ENTITA’
(Però così facendo c’è il rischio di ripetere la fatturazione per lo stesso ” foglionoleggio ”).
- Aggiungo “ idFattura ” dentro la tabella “ foglioNoleggio ”, così non fattura più volte lo stesso foglio.
SQL (Structure Query Language)
Il linguaggio del database è univoco per tutti i database, e si chiama “Structure Query Language” (SQL). Questo perché il database non riconosce linguaggi diversi da SQL. Diverso dal linguaggio SQL, è il motore di database “MySQL Server” che è appunto un motore. Dunque, per poter gestire l’istanza (l’ambiente database), si deve usare un software chiamato “Workbench”, che si connette al database per gestirlo. Grazie a Workbench ci connettiamo al database come se fosse Java quindi, qualsiasi applicazione esterna che cerca di estrarre dati dal database deve parlare il linguaggio SQL, che non è un linguaggio “procedurale”, in quanto SQL riceve “in blocco” le operazioni da svolgere, e le esegue insieme, dando un feedback finale. Una volta imparato il linguaggio SQL su Workbench possiamo usare ciò che abbiamo appreso in ambiente JAVA. In SQL si parla del concetto di transazione, cioè un operazione che si apre, si esegue e si chiude , detta comunemente istruzione SQL. Fino a che tutto il blocco d’istruzione non è stato eseguito, la transazione non può dirsi conclusa. Tuttavia, non sempre le istruzioni SQL da eseguire vanno a buon fine. Infatti possono esserci due riscontri:
- Istruzioni OK : la transazione è andata a buon fine (“ Commit ”, cioè il database accetta tutto)
- Istruzioni KO : la transazione non è andata a buon fine, per cui devo ritornare indietro e il database con mia autorizzazione, annulla le istruzioni della transazione ( Rollback ). Con il rollback, cancella i record inseriti nella transazione. Può farlo perché, una volta iniziata la transazione, fa una copia virtuale delle tabelle, aggiunge i record e se non riesce a portare a termine la transazioni, mi segnala il ko. Per cui, se lo sviluppatore ordina il “rollback”, il motore cancella le copie virtuali, senza mai toccare le tabelle originali. Invece se la transazione va in porto, sostituisce le tabelle con le tabelle virtuali.
DDL e DML
SQL → Linguaggio non procedurale (cioè esegue un blocco di istruzioni) Le istruzioni si dividono in diverse aree:
- DDL – Data Definition Language: Sono istruzioni che modificano la struttura degli oggetti (come si crea una tabella, per esempio); modifica dunque la struttura delle schede con le relative funzioni: - CREATE TABLE - seguito dal nome della tabella e dall’elenco degli attributi; per ogni attributo occorre specificare il nome e il tipo di dato. - ALTER TABLE - per aggiungere una nuova colonna (ADD) a quelle già esistenti, per togliere una colonna (DROP). - DROP - seguito dal nome della tabella, per eliminare una tabella.
- DML – Data Manipulation Language: Sono le istruzioni che manipolano i dati sull’istanza (es. inserire, cancellare e leggere i dati). Sono queste le istruzioni che si utilizzano in Java e si definiscono istruzioni CRUD che sono: - CREATE – La sua sintassi è: INSERT - READ – La sua sintassi è: SELECT - UPDATE – La sua sintassi è: UPDATE - DELETE – La sua sintassi è: DELETE La DDL modifica la struttura delle schede, aggiungendo nuovi campi. La DHL impatta direttamente su uno specifico record andando a modificare valori di campo. MySQL Workbech e create database In MySQL Workbench gli “ schemas ” sono i database. Lo schema “ sys ” NON SI DEVE MAI TOCCARE perché è il database di sistema, cioè tutta l’istanza. Per creare un nuovo database clicco nella parte vuota di “ schemas ” con il tasto dx del mouse e clicco → “ create schema ”, rinominare il nuovo database con le lettere minuscole e applicare. Mostrerà l’istruzione che effettua per creare il nuovo database (cioè l’operazione DDL) che sarebbe: create table nometabella( campo1 int campo2 varchar(20) → stringa di 20 caratteri campo3 int ) Dopodiché cliccando due volte con il tasto sx del mouse sul database creato diventa in grassetto, cioè lo selezioniamo come DEFAULT. Per cancellare la tabella si esegui la DDL in questo modo: drop table nometabella Sul Workbech NN significa “ Not null ” quindi quel campo diventa obbligatorio, AI significa Auto increment cioè che si autoincrementa da solo ed è di tipo numerico. Si ricorda che il primo campo della scheda è sempre IDnometabella diventando PRIMARY KEY! La sintassi del CRUD
- CREATE – INSERT INTO nometabella (campo1,campo2,campo3) Values (‘Berlina’,1,1500) Prima il campo e poi il tipo
SQL NON E’ CASE SENSITIVE!
sintassi Campi da popolare tra parentesi Sintassi di valore Tra gli apici inserisco la stringa, gli interi senza apici
In SQL ci sono delle clausole che sono degli elementi aggiuntivi per operare che sono: La sintassi è: SELECT * FROM tabella WHERE costo > 220 Questo ci restituirà tutti i costi maggiori di 220 Con “ Like ” posso usare anche il simbolo “%”. %lina per individuare stringhe nella tabella che finisco per “lina” (esempio abbiamo ‘Berlina’, ci restituirà Berlina perché finisce con %lina) posizionando la percentuale dopo cerchiamo le iniziali, quindi mantenendo lo stesso esempio dico: berl% che mi restituirà ‘Berlina’ SELECT * FROM tabella WHERE descrizione like ‘%lina’ SELECT * FROM tabella WHERE descrizione like ‘berl%’ Con l’underscore, cioè con il simbolo “_” utilizzando sempre lo stesso esempio voglio cercare berlina ma non so se ho scritto berlina o berlino, scrivo ‘berlin_’, quindi lui mi restituirà berlina o berlino fregandosene dell’ultimo parametro che inserirà lui. O magari ricordo solo vari parametri: SELECT * FROM tabella WHERE descrizione like ‘berlin_’ SELECT * FROM tabella WHERE descrizione like ‘b_rl_n_’ And e Or
Poi abbiamo is null e is not null che servono per sapere se quei campi sono popolati o no SELECT * FROM tabella WHERE descrizione is null SELECT * FROM tabella WHERE descrizione is not null Abbiamo Order by(campo1) che ci ordina i valori in senso crescente in base al campo o in ordine decrescente come in esempio: SELECT * FROM tabella ORDER BY costo desc descrizione Abbiamo Group by(campo1) che ci raggruppa i dati in base al campo scelto, esempio se stiamo raggruppando le persone, la sintassi è questa: SELECT COUNT() FROM tabella GROUP BY nomi; Così però usciranno anche i nomi che sono stati ripetuti più volte, inserendo invece il campo che vogliamo raggruppare evitiamo il doppio, scrivendo così: SELECT COUNT() nomi from archivio GROUP BY nomi E’ buona cosa dopo aver fatto un group by mettere anche un order by per vedere non solo il raggruppamento ma anche un ordine che sia crescente o decrescente. Abbiamo having che filtra i dati che ho raggruppato in group by.
- UPDATE – aggiorna la tabella con i dati che ho immesso, questo mando aggiorna l’intera tabella nel campo specificato e la sintassi e come segue: UPDATE nometabella SET campo1 = ‘dato_aggiornato’; E’ sempre buona abitudine utilizzare sempre una condizione in modo da non compromettere tutti i dati come da esempio dell’esercizio svolto UPDATE MARCHE SET DESCRIZIONE = ‘WOLKSWAGEN’ WHERE IDMARCHE = 2; Sto ordinando la tabella costo in ordine crescente e in ordine decrescente la descrizione che me li metterà in ordine alfabetico in ordine decrescente Sintassi Sintassi
c_id cognome nome r_id 4 Terragna Elisabetta 2 5 Verdi Marco Zero La tabella mostra due peculiarità: i collaboratori Rossi e Padovan lavorano nello stesso reparto. Il dipendente Verdi non è stato ancora assegnato a un reparto. La tabella “reparti” elenca tutti i reparti dell'azienda, compresi ID e ubicazione. Tabella: reparti r_id denominazione città 1 Distribuzione Roma 2 IT Milano 3 Personale Firenze 4 Ricerca Milano Entrambe le tabelle sono collegate da una relazione a chiave esterna. L'ID del reparto, che funge da chiave primaria nella tabella “reparti”, è stato integrato come chiave esterna nella tabella “collaboratori”. Questo collegamento ci permette di avere una inner join in entrambe le tabelle. Ciò è necessario, ad esempio, per determinare dove un collaboratore stia lavorando. Quando si interrogano database relazionali, la corrispondenza tra la chiave primaria e la chiave esterna viene solitamente definita come condizione di selezione. La condizione si considera soddisfatta se la chiave esterna selezionata di una tabella corrisponde alla chiave primaria dell'altra tabella (=). Ciò significa che vengono restituiti solo i record di dati che contengono valori comuni. Una inner join simile è annotata nell' algebra relazionale come segue. collaboratori⋈r_id=r_idreparti Tuttavia, i sistemi di database relazionali non accettano comandi nella sintassi dell'algebra relazionale, ma sotto forma di dichiarazioni SQL. SELECT * FROM collaboratori INNER JOIN reparti ON collaboratori.r_id = reparti.r_id; come nell’esempio visto insieme la sintassi era questa: SELECT * FROM VETTURE INNER JOIN TIPOLOGIA ON VETTURE.IDTIPOLOGIA=TIPOLOGIA.IDTIPOLOGIA
INNER JOIN MARCHE
ON MARCHE.IDMARCHE=VETTURE.IDMARCA
Così facendo, vedrò i dati combacianti tra tutte le tabelle (vetture, tipologia e marche). Se ci fossero delle ambiguità nei nomi dove magari ci sono due campi simili come descrizione vettura e descrizione tipologia, basta specificare davanti al nome del campo il nome della tabella usando questa sintassi: SELECT tipologia.descrizione FROM VETTURE SubQuery La SubQuery è una query innestata in un’altra, si scrive nelle parentesi tonde e restiuisce un solo risultato! Mi raccomando, UN SOLO RISULTATO. Integrità Referenziale e Foreign Key Finora abbiamo liberamente popolato le tabelle perché non avevamo relazionato fisicamente le tabelle, per farlo, le tabelle devono essere per consiglio vuote perché non possiamo effettuare verifiche su dati già immessi. Per applicare l’integrità referenziale dobbiamo creare la foreign key, per farlo dobbiamo impostarlo sul workbench o sull’ambiente di sviluppo modificando la struttra della tabella utilizzando la sintassi come segue: vetture_tipologie Esempio di Query effettuate
- UNA LISTA DOVE RIPORTA TARGA, MARCA, COSTO ACQUISTO, TIPOLOGIA, COSTO E CILINDRATA Tabella di accoglienza Tabella da collegare
select marche.descrizione as Marca_Auto, tipologia.descrizione as Tipo_Auto, avg(costoacquisto) as mediacosto from vetture inner join marche on marche.idmarche=vetture.idmarca inner join tipologia on tipologia.idtipologia=vetture.idTipologia group by Marca_auto,Tipo_Auto
- la somma dei costi di acquisto per ogni marca select marche.descrizione as Marca_Auto, sum(costoacquisto) as sommacosto from vetture inner join marche on marche.idmarche=vetture.idmarca group by Marca_auto
- Inserire una nuova vettura dove non conosciamo marca e tipologia, utilizzando una subquery insert into vetture (targa,costoacquisto,idmarca,idtipologia) values ('AV197JU',120000,
select idmarche from marche where descrizione like 'ford'), ( select idtipologia from tipologia where descrizione like 'cassonato' ) )
- contare le sedi select count(*) numerosedi, descrizione from citta inner join sede on sede.idcitta=citta.idcitta group by descrizione
- aggiornare tutte le tipologie maggiori di 4 e inferiori di 7 update tipologia set descrizione ='SUV',costo= where idtipologia >4 and idtipologia<
- Cos’è un campo chiave? serve per identificare una tabella all’interno di un archivio, detta primary key
- Cosa sono le costaint? I constraint sono vincoli sono delle regole che vengono stabilite all’interno di una tabella foreign key e primary key per stabilire regole di inserimento di valori di campo
- Cosa faccio con show tables? “Show Tables” mi mostra invece la lista delle tabelle che ci ha assegnato il DBA e si scrive così: USE nomedatabase
- Select * from vetture where idvettura = 5 Non cancelli nessuna vettura
- Delete from clienti where descrizione like ‘ciao’ Cancella solo i campi dove all’interno c’è la parola ciao
- La transazione inizia con: In SQL si parla del concetto di transazione, cioè un operazione che si apre, si esegue e si chiude, detta comunemente istruzione SQL. Fino a che tutto il blocco d’istruzione non è stato eseguito, la transazione non può dirsi conclusa. Tuttavia, non sempre le istruzioni SQL da eseguire vanno a buon fine. Infatti possono esserci due riscontri: Istruzioni OK: la transazione è andata a buon fine (“Commit”, cioè il database accetta tutto) Istruzioni KO: la transazione non è andata a buon fine, per cui devo ritornare indietro e il database con mia autorizzazione, annulla le istruzioni della transazione (Rollback). Con il rollback, cancella i record inseriti nella transazione. Può farlo perché, una volta iniziata la transazione, fa una copia virtuale delle tabelle, aggiunge i record e se non riesce a portare a termine la transazioni, mi segnala il ko. Per cui, se lo sviluppatore ordina il “rollback”, il motore cancella le copie virtuali, senza mai toccare le tabelle originali. Invece se la transazione va in porto, sostituisce le tabelle con le tabelle virtuali. Parte la transazione con la start transaction.
- La clausola where serve per restringere la ricerca dei dati
- Update la sintassi è update vetture set descrizione = ‘fiat’
- Delete * from vetture, ha un errore di sintassi. Si scrive direttamente delete from
- Cancellare colonna data di nascita, lo faccio con alter table
- La subquery è una query innestata che deve restituire un solo valore
- L’ordine delle clausole sono: select +( from +tabella)operazioni matematiche + where + group by+ having + order by
- Operatori di confronto: vedere sopra!
