Esercizi di modellazione concettuale e logica
Compagni di Classe
Progetta un DB per una classe della scuola Archimede. Per ogni studente si vogliono memorizzare i nominativi dei compagni di classe ritenuti simpatici e dei compagni di classe ritenuti antipatici. Dopo aver disegnato il diagramma ER (documentato e con le associazioni commentate nei 2 versi), realizzare il modello logico.
Zoo
In un giardino zoologico ci sono animali di diverse età e specie. Ogni specie è localizzata in un settore (caratterizzato da un nome) dello zoo ma alcune specie sono ospitate in più settori. Di tanto in tanto lo zoo viene ampliato con nuovi settori. Ogni animale ha nome scientifico e caratteristiche particolari descritte in dettaglio.
Popolazione
Le persone hanno CF, cognome ed età; gli uomini anche la posizione militare; gli impiegati hanno lo stipendio e possono essere segretari, direttori o progettisti (un progettista può essere anche responsabile di progetto); gli studenti (che non possono essere impiegati) un numero di matricola; esistono persone che non sono né impiegati né studenti (ma i dettagli non ci interessano).
Università
Progettare la base di dati che consideri la realtà di una università con studenti e materie. Considerare anche la possibilità di archiviare gli esiti avuti nelle varie prove sostenute da ogni studente.
Autonoleggio
Un’agenzia di noleggio di autovetture ha un parco auto suddiviso per categorie auto (p.e. utilitaria, media, suv, wagon, …).
Dalla categoria dipende la tariffa di noleggio.
Sarà utile registrare anche i dati dei clienti che effettuano il noleggio ed il periodo di noleggio.
Nuovissima Compagnia Aerea Italiana Una compagnia aerea offre voli caratterizzati da un numero tratta, una data, un orario di partenza, uno di arrivo, laeroporto di partenza e quello di destinazione. Ci sono voli nazionali ed internazionali. I voli internazionali possono avere uno o più scali. Dei voli realmente effettuati é inoltre utile memorizzare lorario reale di partenza e di arrivo.
I voli sono prenotabili dai clienti ed è utile conoscere leventuale disponilibità residua dei posti.
Palestra
Si vuole progettare la base di dati di una palestra. Si memorizzano le informazioni sui clienti che includono il Codice Fiscale, il nome, l’indirizzo e un numero di telefono. Ogni cliente deve aver ottenuto un certificato di sana e robusta costituzione prima di cominciare lattività della palestra: occorre pertanto assegnargli un appuntamento con uno dei medici sportivi che seguono la palestra. Il cliente può effettuare liscrizione a corsi del quale é noto il nome, il maestro e una descrizione. Per ogni iscrizione é definita la data di inizio, il costo complessivo e la situazione dei suoi pagamenti: quanto ha pagato, il costo complessivo e quando scade la prossima rata.
Per ognuno dei clienti iscritti si memorizzano gli esercizi componenti il programma assegnatogli.
Di ogni esercizio si sa il nome, il muscolo principale coinvolto, le controindicazioni e, per ogni cliente, il numero di esecuzioni che gli viene assegnato.
Produrre l’analisi, lo schema E/R e lo schema relazionale derivato.
Laboratorio di analisi
Si vogliono gestire i dati relativi ai pazienti e alle analisi effettuate presso un laboratorio. Esistono diverse tipologie di esami. Ogni esame ha un costo specifico. Ogni paziente effettua il prelievo per uno o più esami.
Creare le tabelle necessarie perchè sia possibile soddisfare le seguenti richieste:
- Visualizzare lelenco dei dati dei pazienti che hanno effettuato prelievi in un determinato giorno
- Visualizzare lelenco degli esami effettuati da un paziente il cui nominativo viene fornito da tastiera indicando per ogni esame anche i valori normali
- Visualizzare lelenco delle analisi che presentano valori fuori limite per lesame la cui descrizione viene data in input
Bocconi
In un corso di laurea ci sono studenti (ciascuno con matricola, nome, cognome, indirizzo) che frequentano corsi (ciascun corso ha un nome) tenuti da docenti (nome, cognome). Alcuni corsi prevedono una sperimentazione (numero di ore) da effettuarsi in precise aule laboratorio (numero aula).
Biblioteca
Desideriamo impostare una base di dati per una biblioteca comunale. Sarà possibile prestare i libri agli utenti registrati per massimo 30 gg. Per ogni utente sarà utile archiviare le informazioni anagrafiche. Si potranno avere più libri dello stesso titolo, anche di editori diversi. I libri saranno catalogati per genere e autore. Non sarà possibile prestare libri ad un utente che ne ha già 2. Gli utenti che hanno sempre restituito i libri entro la scadenza dei 30 gg saranno riconosciuti dei crediti da memorizzare. Agli utenti che non restituiranno i libri entro 60 gg, verrà addebitato il valore attuale di acquisto. Ogni anno il valore del libro sarà rivalutato del 5%. L’utente rimarrà bloccato fino al pagamento del debito.
Campionato di calcio
E richiesto un database per organizzare alcune informazioni relative al campionato di calcio di serie A: dati anagrafici dei calciatori, delle squadre di calcio (nome, colori sociali, città), degli scudetti annuali con relativa squadra vincitrice, punti, numero squadre concorrenti, i dati dei calciatori, delle squadre in cui ha militato, in quali anni, gol segnati e ruolo ricoperto (attaccante, difensore, portiere, centrocampista) nei vari anni.
Federazione Gioco Calcio
Realizzare una base di dati per la gestione di squadre e giocatori di calcio.
Le squadre possono stipulare contratti con molti giocatori; i calciatori di solito hanno contratti con una sola società sportiva; può verificarsi il caso della comproprietà di un giocatore con più squadre nei cui contratti sarà indicata la percentuale di possesso. In ogni caso il giocatore indossa la maglia di una sola squadra.
Per ogni contratto si avrà una data di inizio ed una data di scadenza.
Interrogazioni SQL:
- Per ogni squadra visualizzare il codice e numero dei giocatori con contratto in corso
- Denominazione delle squadre che hanno più di 30 giocatori con contratto in corso
- Dato il codice di un giocatore fornire lelenco delle squadre in cui ha militato
- Cognome, nome e ruolo dei giocatori attualmente svincolati
- Cognome, nome e squadra dei giocatori attualmente in comproprietà
- Cognome, nome e squadra del giocatore con l’ingaggio in vigore più alto
Voli
Realizzare diagramma E/R e modello logico della seguente realtà:
Un passeggero viaggia su uno o più voli, un volo può essere scelto da più passeggeri.
Uno volo afferisce ad una sola tratta.
Esercizi di interrogazioni SQL
INTERROGAZIONI SQL
- Laboratorio di analisi
Interrogazioni SQL della base di dati creata con lesercizio LABORATORIO DI ANALISI:- Visualizzare lelenco dei dati dei pazienti che hanno effettuato prelievi in un determinato giorno
- Visualizzare lelenco degli esami effettuati da un paziente il cui nominativo viene fornito da tastiera indicando per ogni esame anche i valori normali
- Visualizzare lelenco delle analisi che presentano valori fuori limite per lesame la cui descrizione viene data in input
- Esistono diverse tipologie di esami
Ogni esame ha un costo specifico
Ogni paziente effettua il prelievo per uno o più esami
- Compagni di Classe
Interrogazioni SQL della base di dati creata con l’esercizio compagni di classe:- Elencare la classifica degli antipatici
- Individuare il più antipatico ai compagni
- Elencare gli alunni che non sono antipatici a nessuno
- Bocconi
Interrogazioni SQL della base di dati creata per lesercizio BOCCONI:- Elencare i nomi degli studenti che frequentano corsi del primo anno.
- Elencare i nomi degli studenti che seguono lezioni del Prof. Paradiso Santo
- Biblioteca
Desideriamo impostare una base di dati per una biblioteca comunale. Sarà possibile prestare i libri agli utenti registrati per massimo 30 gg. Per ogni utente sarà utile archiviare le informazioni anagrafiche. Si potranno avere pi ù libri dello stesso titolo, anche di editori diversi. I libri saranno catalogati per genere e autore. Non sarà possibile prestare libri ad un utente che ne ha già 2. Gli utenti che hanno sempre restituito i libri entro la scadenza dei 30 gg saranno premiati con ricchi premi e cotillons. Agli utenti che non restituiranno i libri entro 60 gg, verrà addebitato il valore attuale di acquisto. Lutente rimarrà bloccato fino al pagamento del debito. Ogni anno il valore del libro sarà rivalutato del 5%. Interrogazioni Sql:- Elenco libri di una determinata opera letteraria, specificando la posizione in biblioteca, se il libro è presente o in prestito
- Elenco clienti con prestiti da pi ù di 30 gg
- Elenco dei clienti che non abbiano mai restituito fuori termine massimo
- Valore da addebitare agli utenti con restituzioni oltre i 60 gg
- Adeguamento annuale del valore di ogni libro
- Aggiornamento del debito dei clienti morosi
- Autonoleggio2
Base di dati di un autonoleggio per gestire clienti, autovetture, costi di manutenzione sostenuti sui mezzi e ricavi derivati dal noleggio degli stessi.
Autovetture(targa, marca, modello, colore, tipo, optional, data_acquisto, note)
Clienti(id, cognome, nome, data_nascita, num_patente, data_emissione_patente, ente_emittente, …)
Noleggi(id, data_i, data_f, cliente, autovettura, importo, km_percorsi…)
Manutenzioni(id, data, autovettura, tipo, costo)
Tipi_manutenzione(id, descrizione)
Tipi_vettura(id, descrizione, tariffa_giornaliera)
Scrivere le seguenti interrogazioni SQL:- Elenco delle informazioni relative ai noleggi di un mezzo in un determinato periodo
- Totale costi sostenuti per la manutenzione di un automezzo specifico
- Numero noleggi per ogni autovettura in un determinato periodo
- Nome e cognome dei clienti che hanno noleggiato nel mese di gennaio 2013
- Un pò più Difficili
- Numero dei mezzi non noleggiati in gennaio 2013
- Elenco autovetture acquistate da più di 3 anni o che hanno percorso più di 150000 km
- Guadagno ottenuto da un modello ben preciso di autovettura (guadagno=costi – ricavi)
- Ospedale
Data la seguente base di dati:
pazienti(cf,cognome,nome,data_nascita)
ricoveri(id,data,paziente,medico,concluso,data_conclusione,motivazione*,recapito_parente)
*motivazione: dimissione,trasferimento,…
MEDICI(matricola,cognome,nome,specializzazione)
VISTE(id,data,ora,paziente,note)
MEDICI_VISITANTI(visita,medico)
DIAGNOSI(visita,malattia)
CURA(visita,medicina)
MEDICINE(codice,nome,costo)
MALATTIE(id,nome)
SOMMINISTRAZIONI(id,data,ora,paziente,medicina,dose)
gli attributi sottolineati indicano Primary key
gli attributi in corsivo indicano Foreign Key
Scrivere le seguenti interrogazioni SQL:- elenco ricoveri per uno/tutti paziente (indicato con il nome) e una/tutte le motivazioni elenco diagnosi per paziente (indicato con il nome) specifico
- elenco pazienti per ogni medico elenco cronologico di pazienti,data e ora, medicina di assunzione per ogni medicina elenco pazienti, data prescrizione
- elenco cronologico farmaci,data,ora,paziente per ogni paziente di uno/tutti farmaci (indicato con il codice)
- Volare
Dato il seguente schema logico in cui necessita ancora individuare chiavi primarie ed esterne:
AEROPORTI (IdAeroporto, Citta, Nazione, NumPiste)
VOLI (IdVolo, GiornoSett, CittaPart, OraPart, CittaArr, OraArr, TipoAereo)
AEREI (IdAereo, TipoAereo, NumPasseggeri, QtaMerci)
Scrivere le interrogazioni SQL che permettono di determinare:- Le città con un aeroporto di cui non é noto il numero di piste;
- Le nazioni da cui parte e arriva il volo con codice AZ274;
- I tipi di aereo usati nei voli che partono da Torino;
- I tipi di aereo e il corrispondente numero di passeggeri per i tipi di aereo usati nei voli che partono da Torino. Se la descrizione dellaereo non é disponibile, visualizzare solamente il tipo;
- Le città da cui partono voli diretti a Bologna, ordinate alfabeticamente;
- Il numero di voli che partono il giovedì da Napoli;
- Le città che sono servite dallaereo caratterizzato dal massimo numero di passeggeri.
- Università
Date le tabelle:
voti (matricola, voto, materia, data)
studenti (matricola, nome, cognome)
professori (nome, materia)- Restituire nome e cognome degli studenti che hanno preso 27 in Informatica il 3 luglio 2006
- Per ogni studente restituire il voto massimo ottenuto
- Trovare il numero di materie insegnate dal prof. Tresoldi.
- Impiegati
- Creare il database IMPIEGATI
- Creare 2 tabelle con le seguenti strutture
- Impiegati (id, name, age, salary)
- Dirigenti (capo, impiegato)
- Scrivere le seguenti SQL per la creazione
- Popolare le tabelle con i dati indicati
- Scrivere le istruzioni SQL per inserire i dati richiesti
- Scrivere le seguenti istruzioni SQL:
- Trovare id, nome ed età di tutti gli impiegati che guadagnano più di 40 mila euro
- Trovare il numero dei responsabili degli impiegati che guadagnano più di 40 mila euro
- Trovare gli impiegati che guadagnano più dei loro responsabili e visualizzare numero, nome e salario sia dellimpiegato che del responsabile
- Trovare id e nome dei responsabili i cui impiegati guadagnano TUTTI più di 40 mila euro
- Impiegati…continua
Data la precedente base di dati scrivere le istruzioni SQL relative alle seguenti interrogazioni- Elencare i nomi dei dirigenti con il numero dei rispettivi subalterni
- Elencare i nomi dei dipendenti con il salario minimo
- Calcolare la media degli stipendi
- Contare i dirigenti che percepiscono un salary minore della media
- Aumentare del 10% i salary dei dipendenti con salario minore della media
- Cancellare (dopo averli eventualmente creati) i dipendenti non dirigenti, in mobbing (senza dirigente)
- Esportare la tabella impiegati selezionando solo i dirigenti
- Aggiungere il campo ALIAS, testo, 40 caratteri
- Copiare il campo NAME nel campo ALIAS
- Meteo
Date le tabelle:
minime (provincia, data,temperatura)
massime (provincia, data,temperatura)
medie (provincia, data,temperatura)- restituire la temperatura massima, e minima del 3 luglio 2006 di tutte le province
- restituire massima assoluta raggiunta il 3 luglio 2006
- per ogni provincia restituire la temperatura massima assoluta raggiunta.
