1 / 68

SQL: Amministrazione e Viste

SQL: Amministrazione e Viste. SQL per definire ed amministrare. Ad ogni utente tipicamente viene associata una base di dati, creata dall’amministratore del sistema. L’utente diventa l’amministratore potendo stabilire gli accessi di eventuali altri utenti alla sua base di dati.

lazaro
Download Presentation

SQL: Amministrazione e Viste

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL: Amministrazione e Viste

  2. SQL per definire ed amministrare • Ad ogni utente tipicamente viene associata una base di dati, creata dall’amministratore del sistema. • L’utente diventa l’amministratore potendo stabilire gli accessi di eventuali altri utenti alla sua base di dati. • La creazione consiste nel definire incrementalmente uno schema con un certo nome, interattivamente o da programma, e tutti i suoi elementi vengono registrati in un catalogo. Giugno-Pulvirenti AA 2005-2006

  3. CREATE SCHEMA CREATE SCHEMA Nome AUTHORIZATION Utente Definizioni • Dove: • Si crea il database chiamato Nome • Utente e’ l’amministratore • Le Definizioni creano gli elementi dello schema (Tabelle,Viste,Indici,etc…) Giugno-Pulvirenti AA 2005-2006

  4. DROP SCHEMA DROP SCHEMA Nome [RESTRICT | CASCADE] • cancella la base di dati Nome • Restrict: drop non viene eseguito se il database non è vuoto. • Cascade: Vengono rimossi automaticamente tutti i dati presenti nel database Giugno-Pulvirenti AA 2005-2006

  5. Definizione dei dati in SQL • Istruzione CREATE TABLE: • definisce uno schema di relazione e ne crea un’istanza vuota • specifica attributi, domini e vincoli Giugno-Pulvirenti AA 2005-2006

  6. Create Table CREATE TABLE Nome “(“Attributo Tipo[Vincolo {,Vincolo}] {,Attributo Tipo[Vincolo {,Vincolo}]}”)” [VincoloDiTabella {, VincoloDiTabella}] Giugno-Pulvirenti AA 2005-2006

  7. CREATE TABLE, esempio CREATE TABLE Impiegato( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Dipart CHAR(15), Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip), UNIQUE (Cognome,Nome) ) Giugno-Pulvirenti AA 2005-2006

  8. Domini • Domini elementari (predefiniti) • Domini definiti dall'utente (semplici, ma riutilizzabili) Giugno-Pulvirenti AA 2005-2006

  9. Domini elementari • Carattere: singoli caratteri o stringhe, anche di lunghezza variabile • Bit: singoli booleani o stringhe • Numerici, esatti e approssimati • Data, ora • Sistemi diversi estendono il set di base con domini non standard (vettori, periodi, ecc.) Giugno-Pulvirenti AA 2005-2006

  10. Domini o tipi • CHAR(n) stringhe di lunghezza n • VARCHAR(n) stringhe di lunghezza variabile con al massimo n caratteri • INTEGER interi • REAL reali • NUMERIC (p,s) p cifre di cui s decimali • FLOAT(p) binari con p cifre significative • DATE ,TIME per date ed ore. Giugno-Pulvirenti AA 2005-2006

  11. Definizione di domini • Istruzione CREATE DOMAIN: • definisce un dominio (semplice), utilizzabile in definizioni di relazioni, anche con vincoli e valori di default Giugno-Pulvirenti AA 2005-2006

  12. CREATE DOMAIN, esempio CREATE DOMAIN Voto AS SMALLINT DEFAULT NULL CHECK ( value >=18 AND value <= 30 ) Giugno-Pulvirenti AA 2005-2006

  13. Vincoli intrarelazionali • NOT NULL • UNIQUE definisce chiavi • PRIMARY KEY: chiave primaria (una sola, implica NOT NULL) • CHECK, vedremo più avanti Giugno-Pulvirenti AA 2005-2006

  14. UNIQUE e PRIMARY KEY • due forme: • nella definizione di un attributo, se forma da solo la chiave • come elemento separato Giugno-Pulvirenti AA 2005-2006

  15. CREATE TABLE, esempio CREATE TABLE Impiegato( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Dipart CHAR(15), Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip), UNIQUE (Cognome,Nome) ) Giugno-Pulvirenti AA 2005-2006

  16. PRIMARY KEY, alternative Matricola CHAR(6) PRIMARY KEY Matricola CHAR(6),…,PRIMARY KEY (Matricola) Giugno-Pulvirenti AA 2005-2006

  17. CREATE TABLE, esempio CREATE TABLE Impiegato( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Dipart CHAR(15), Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip), UNIQUE (Cognome,Nome) ) Giugno-Pulvirenti AA 2005-2006

  18. Chiavi su più attributi, attenzione Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, UNIQUE (Cognome,Nome), Nome CHAR(20) NOT NULL UNIQUE, Cognome CHAR(20) NOT NULL UNIQUE, • Non è la stessa cosa! Giugno-Pulvirenti AA 2005-2006

  19. Vincoli interrelazionali • CHECK, vedremo più avanti • REFERENCES e FOREIGN KEY permettono di definire vincoli di integrità referenziale • di nuovo due sintassi • per singoli attributi • su più attributi • E' possibile definire politiche di reazione alla violazione Giugno-Pulvirenti AA 2005-2006

  20. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Matricola Cognome Nome Vigili 3987 Rossi Luca 3295 Neri Piero 9345 Neri Mario 7543 Mori Gino Giugno-Pulvirenti AA 2005-2006

  21. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Prov Numero Cognome Nome Auto MI 39548K Rossi Mario TO E39548 Rossi Mario PR 839548 Neri Luca Giugno-Pulvirenti AA 2005-2006

  22. CREATE TABLE, esempio CREATE TABLE Infrazioni( Codice CHAR(6) NOT NULL PRIMARY KEY, Data DATE NOT NULL, Vigile INTEGER NOT NULL REFERENCES Vigili(Matricola), Provincia CHAR(2), Numero CHAR(6) , FOREIGN KEY(Provincia, Numero) REFERENCES Auto(Provincia, Numero) ) Giugno-Pulvirenti AA 2005-2006

  23. Modifiche degli schemi ALTER DOMAIN ALTER TABLE DROP DOMAIN DROP TABLE ... Giugno-Pulvirenti AA 2005-2006

  24. Definizione degli indici • è rilevante dal punto di vista delle prestazioni • ma è a livello fisico e non logico • in passato era importante perché in alcuni sistemi era l'unico mezzo per definire chiavi • CREATE INDEX Giugno-Pulvirenti AA 2005-2006

  25. SQL, operazioni sui dati • interrogazione: • SELECT • modifica: • INSERT, DELETE, UPDATE Giugno-Pulvirenti AA 2005-2006

  26. Insert INSERT INTO Tabella [“(”Attributo {,Attributo} “)”] VALUES “(” Valore {,Valore} “)” • Esempio • INSERT INTO Esami VALUES (‘DB1’, 123456, 27) Giugno-Pulvirenti AA 2005-2006

  27. Delete DELETE FROM Tabella WHERE Condizione • Esempio: • DELETE FROM Esami WHERE Matricola = 123456 Giugno-Pulvirenti AA 2005-2006

  28. Update UPDATE Tabella SET Attributo = Espr {,Attributo = Espr} WHERE Condizione • Esempio: • UPDATE Aule SET Aula = 126 WHERE Aula = 3 Giugno-Pulvirenti AA 2005-2006

  29. Esempi CREATE TABLE Studenti( Nome CHAR(30), Matricola INTEGER, Indirizzo CHAR(30), Telefono INTEGER) CREATE TABLE FuoriCorso LIKE Studenti Giugno-Pulvirenti AA 2005-2006

  30. Altri modi di definire tabelle CREATE TABLE Nome AS EsprSelect Esempio: CREATE TABLE EsamiBuoni LIKE Esami AS SELECT * FROM Esami WHERE Voto > 27 Giugno-Pulvirenti AA 2005-2006

  31. SQL: vincoli d’Integrità

  32. Vincoli d’integrita’ • Riguardano i valori ammissibili degli attributi di una tupla • Vincoli Intrarelazionali: nell’ambito della stessa relazione • Vincoli Referenziali (o Interrelazionali): tra diverse relazioni • Vengono controllati durante le tre possibili operazioni di modifica SQL • INSERT,DELETE e UPDATE • Devono essere sempre soddisfatti altrimenti la transazione fallisce • Oppure, l’utente puo’ opzionalmente definire della azioni (correttive) da intraprendere per ripristinare l’integrita’ Giugno-Pulvirenti AA 2005-2006

  33. A cosa servono i vincoli d’integrita’ • Migliorare la qualità dei dati • Arricchire semanticamente la base di dati • La loro definizione è parte del processo di progettazione del data base • Usati internamente dal sistema per ottimizzare l’esecuzione Giugno-Pulvirenti AA 2005-2006

  34. Esempio di DB semanticamente errato Esami Studente Voto Lode Corso 32 01 30 e lode 02 276545 787643 03 276545 739430 24 04 27 27 e lode e lode Studenti Matricola Cognome Nome 276545 Rossi Mario Neri Piero 787643 787643 787643 Bianchi Luca 787643 32 739430 Giugno-Pulvirenti AA 2005-2006

  35. Vincoli su una n-upla • NOT NULL : • e’ implicito se l’attributo fa parte di una chiave primaria • CHECK Condizione : • specifica i valori ammissibili; esempio: Voto INTEGER NOT NULL CHECK (18  Voto AND Voto  31) • DEFAULT(Costante|NULL) • assegna quel valore di default per ogni inserimento • CHECK Condizione : • anche per attributi diversi della stessa n-upla Giugno-Pulvirenti AA 2005-2006

  36. Vincoli Intrarelazionali • UNIQUE: • l’attributo e’ una chiave • PRIMARY KEY [Nome Chiave] “(”Attributo{,Attributo} “)” • dove gli attributi devono essere dichiarati tutti NOT NULL • UNIQUE “(”Attributo{,Attributo} “)” • definisce una chiave con piu’ attributi Giugno-Pulvirenti AA 2005-2006

  37. Vincoli Interrelazionali FOREIGN KEY [NomeChiaveEsterna] “(”Attributo{,Attributo} “)” REFERENCES TabellaRef ON DELETE {NO ACTION,CASCADE,SET NULL} • dove per la TabellaRef e’ stata definita una chiave primaria. • Impedisce l’inserzione di n-uple con il valore della chiave esterna che non corrisponde ad un valore della chiave primaria della TabellaRef . Se un’operazione di cancellazione su TabellaRef viola il vincolo referenziale allora vengono applicate le tre azioni: Giugno-Pulvirenti AA 2005-2006

  38. Azioni per Vincoli su Chiavi Esterne • ON DELETE NO ACTION : • rifiuta l’operazione (la piu’ diffusa nei DBMS) • ON DELETE CASCADE : • cancella tutte le n-uple con valori della chiave esterna corrispondenti alla chiave primaria delle n-uple cancellate • ON DELETE SET NULL • assegna il valore NULL agli attributi della chiave esterna Giugno-Pulvirenti AA 2005-2006

  39. Vincoli d’integrita’ referenziali (o interrelazionali) • Tuple di relazioni diverse sono correlati per mezzo del valore di chiavi (primarie) • Servono a garantire che i valori in una certa tabella facciano riferimento a valori reali di un’altra tabella • Esempio: • Esami(...,Matricola), Studenti( Matricola,...) Giugno-Pulvirenti AA 2005-2006

  40. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 3987 MI 39548K 3295 53524 4/3/95 3295 TO E39548 3295 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 9345 PR 839548 Vigili Matricola Cognome Nome 3987 Rossi Luca 3987 3295 Neri Piero 3295 9345 Neri Mario 9345 7543 Mori Gino 3987 3295 3295 9345 Giugno-Pulvirenti AA 2005-2006

  41. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI MI 39548K 39548K 53524 4/3/95 3295 TO TO E39548 E39548 64521 5/4/96 3295 PR PR 839548 839548 73321 5/2/98 9345 PR PR 839548 839548 Auto Prov Numero Cognome Nome MI 39548K Rossi Mario TO E39548 Rossi Mario PR 839548 Neri Luca MI 39548K TO E39548 PR 839548 Giugno-Pulvirenti AA 2005-2006

  42. Vincolo di integrità referenziale • Un vincolo di integrità referenziale(“foreignkey”) fra gli attributi X di una relazione R1 e un’altra relazione R2 impone ai valori su X in R1 di comparire come valori della chiave primaria di R2 • Nell‘esempio precedente: • vincoli di integrità referenziale fra: • l’attributo Vigile della relazione INFRAZIONI e la relazione VIGILI • gli attributi Prov e Numero di INFRAZIONI e la relazione AUTO • NULL per evitare il controllo del vincolo Giugno-Pulvirenti AA 2005-2006

  43. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Auto Prov Numero Cognome Nome MI E39548 Rossi Mario TO F34268 Rossi Mario E39548 PR 839548 Neri Luca TO Violazione di vincolo di integrità referenziale Giugno-Pulvirenti AA 2005-2006

  44. Integrità referenziale e valori nulli Impiegati Matricola Cognome Progetto 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 BOH 09/2001 24 150 Giugno-Pulvirenti AA 2005-2006

  45. Azioni compensative • Esempio: Viene eliminata una tupla causando cosi' una violazione • Tre possibili azioni • Rifiuto dell'operazione • ON DELETE NO ACTION (la piu’ diffusa nei DBMS) • Eliminazione in cascata • ON DELETE CASCADE: cancella tutte le tuple con valori della chiave esterna corrispondenti alla chiave primaria delle tuple cancellate • Introduzione di valori nulli • ON DELETE SET NULL assegna il valore NULL agli attributi della chiave esterna Giugno-Pulvirenti AA 2005-2006

  46. Rifiuto della cancellazione Impiegati Matricola Cognome Progetto 53524 Neri XYZ 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 XYZ 07/2001 24 120 BOH 09/2001 24 150 XYZ 07/2001 24 120 XYZ 07/2001 24 120 53524 Neri XYZ • La transazione fallisce e XYZ non puo’ essere cancellato dalla relazione Progetti Giugno-Pulvirenti AA 2005-2006

  47. Eliminazione in cascata Impiegati Matricola Cognome Progetto 53524 Neri XYZ 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 XYZ 07/2001 24 120 BOH 09/2001 24 150 XYZ 07/2001 24 120 XYZ 07/2001 24 120 • La transazione termina e XYZ viene cancellato anche dalla relazione Impiegati Giugno-Pulvirenti AA 2005-2006

  48. Introduzione di valori nulli Impiegati Matricola Cognome Progetto 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 XYZ 07/2001 24 120 BOH 09/2001 24 150 XYZ 07/2001 24 120 XYZ 07/2001 24 120 NULL • La transazione termina e all’attributo Impiegati.Progetto viene assegnato NULL Giugno-Pulvirenti AA 2005-2006

  49. Vincoli multipli su più attributi Auto Prov Numero Cognome Nome MI 39548K Rossi Mario TO E39548 Rossi Mario PR 839548 Neri Luca Incidenti Codice Data ProvA NumeroA ProvB NumeroB 34321 1/2/95 TO E39548 MI 39548K 64521 5/4/96 PR 839548 TO E39548 Giugno-Pulvirenti AA 2005-2006

  50. Vincoli Interrelazionali, Sintassi • FOREIGN KEY [NomeChiaveEsterna]“(”Attributo{,Attributo} “)”REFERENCES TabellaRefON DELETE {NO ACTION,CASCADE,SET NULL} • dove per la TabellaRef e’ stata definita una chiave primaria • Quindi: impedisce l’inserimento di tuple con il valore della chiave esterna che non corrisponde ad un valore della chiave primaria della TabellaRef Giugno-Pulvirenti AA 2005-2006

More Related