Modellazione ER e SQL:
Scuderia di Formula Uno

Il seguente esercizio è dato come soluzione alla traccia: TASK – Modelazione ER e MySQL 08/07/2022 – Percorso di Data Engineer

La soluzione è stata sviluppata e gentilmente resa disponibile per tutti gli studenti da Andrea Forbicia.

 

TRACCIA:

Si vuole realizzare il software gestionale di una scuderia di Formula Uno. Ogni anno la scuderia presenta la vettura che parteciperà al mondiale.

Ogni anno viene disputato un solo mondiale ed è composto da diversi tracciati di cui si vuol sapere il nome, la nazione di appartenenza, la lunghezza, il numero di giri previsti e la data in cui verrà disputata la gara.

Di tutti i componenti del team si vuole tenere traccia del nome, cognome, del contratto e del codice univoco del badge che consente l’ingresso alle aree riservate. Il responsabile del team è il team principale che ha il compito di gestire piloti, ingegneri e meccanici.

Dei piloti si vuole sapere il numero di licenza e del loro ruolo all’interno del team, in particolare se si tratta di piloti ufficiali o tester. Degli ingegneri indicare in quale area tecnica sono specializzati (motore, elettronica, aerodinamica, analisi dati, ecc..), mentre dei meccanici il reparto in cui operano (pneumatici, sospensioni, motore, carrozzeria, ecc..)

 

IL DIAGRAMMA ER:

Il seguente diagramma è consultabile o modificabile direttamente dal suo file sorgente con il programma draw.io scaricabile dall’indirizzo https://www.diagrams.net/ o utilizzabile direttamente sul sito.

SCARICA IL DIAGRAMMA: CLICCA QUI

 

 

DDL – Data Definition Language

 

DROP DATABASE IF EXISTS scuderiaDB;
CREATE DATABASE scuderiaDB;
USE scuderiaDB;

CREATE TABLE team_principal (
	ID INTEGER AUTO_INCREMENT,
	nome VARCHAR(250) NOT NULL,
	cognome VARCHAR(250) NOT NULL,
	contratto INTEGER,
	badge VARCHAR(6) UNIQUE,
	PRIMARY KEY (ID)
);

CREATE TABLE ingegnere (
	ID INTEGER AUTO_INCREMENT,
	nome VARCHAR(250) NOT NULL,
	cognome VARCHAR(250) NOT NULL,
	contratto INTEGER,
	badge VARCHAR(6) UNIQUE,
	specializzazione VARCHAR(250),
	team_principalID INTEGER NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (team_principalID) REFERENCES team_principal (ID) ON DELETE CASCADE
);

CREATE TABLE meccanico (
	ID INTEGER AUTO_INCREMENT,
	nome VARCHAR(250) NOT NULL,
	cognome VARCHAR(250) NOT NULL,
	contratto INTEGER,
	badge VARCHAR(6) UNIQUE,
	reparto VARCHAR(250),
	team_principalID INTEGER NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (team_principalID) REFERENCES team_principal (ID) ON DELETE CASCADE
);

CREATE TABLE pilota (
	ID INTEGER AUTO_INCREMENT,
	nome VARCHAR(250) NOT NULL,
	cognome VARCHAR(250) NOT NULL,
	contratto INTEGER,
	badge VARCHAR(6) UNIQUE,
	ruolo VARCHAR(250),
	licenza VARCHAR(6) UNIQUE,
	team_principalID INTEGER NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (team_principalID) REFERENCES team_principal (ID) ON DELETE CASCADE
);

CREATE TABLE tracciato (
	ID INTEGER AUTO_INCREMENT,
	nome VARCHAR(250) NOT NULL UNIQUE,
	nazione VARCHAR(250) NOT NULL,
	lunghezza INTEGER NOT NULL,
	giri INTEGER NOT NULL,
	data_gara DATE NOT NULL UNIQUE,
	PRIMARY KEY (ID)
);

CREATE TABLE mondiale (
	ID INTEGER AUTO_INCREMENT,
	anno YEAR NOT NULL UNIQUE,
	PRIMARY KEY (ID)
);

CREATE TABLE vettura (
	ID INTEGER AUTO_INCREMENT,
	nome VARCHAR(250) NOT NULL,
	pilotaID INTEGER NOT NULL UNIQUE,
	mondialeID INTEGER NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (pilotaID) REFERENCES pilota (ID) ON DELETE CASCADE,
	FOREIGN KEY (mondialeID) REFERENCES mondiale (ID) ON DELETE CASCADE
);

CREATE TABLE team_tecnico (
	vetturaID INTEGER NOT NULL,
	ingegnereID INTEGER NULL NULL,
	FOREIGN KEY (vetturaID) REFERENCES vettura (ID) ON DELETE CASCADE,
	FOREIGN KEY (ingegnereID) REFERENCES ingegnere (ID) ON DELETE CASCADE
);

CREATE TABLE team_operativo (
	vetturaID INTEGER NOT NULL,
	meccanicoID INTEGER NULL NULL,
	FOREIGN KEY (vetturaID) REFERENCES vettura (ID) ON DELETE CASCADE,
	FOREIGN KEY (meccanicoID) REFERENCES meccanico (ID) ON DELETE CASCADE
);

 

DML – Data Manipilation Language

 

INSERT INTO team_principal (nome, cognome, contratto, badge) VALUES
	("Mattia", "Binotto", 1000000, "bdg001");

INSERT INTO ingegnere (nome, cognome, contratto, badge, specializzazione, team_principalID) VALUES
	("Mario", "Rossi", 200000, "bdg002", "Aerodinamica", 1),
	("Luca", "Conti", 200000, "bdg003", "Analisi dati", 1),
	("Marco", "Rizzo", 200000, "bdg004", "Elettronica", 1);

INSERT INTO meccanico (nome, cognome, contratto, badge, reparto, team_principalID) VALUES
	("Valerio", "Verdi", 80000, "bdg005", "Motore", 1),
	("Armando", "Esposito", 80000, "bdg006", "Pneumatici", 1),
	("Alessandro", "Bruno", 80000, "bdg007", "Carrozzeria", 1);

INSERT INTO pilota (nome, cognome, contratto, badge, ruolo, licenza, team_principalID) VALUES
	("Charles", "Leclerc", 10000000, "bdg008", "Ufficiale", "dl001", 1),
	("Carlos", "Sainz", 5000000, "bdg009", "Ufficiale", "dl002", 1),
	("Antonio", "Giovinazzi", 1000000, "bdg010", "Riserva", "dl003", 1);

INSERT INTO tracciato (nome, nazione, lunghezza, giri, data_gara) VALUES
	("Autodromo Nazionale Monza", "Italia", 5793, 53, '2022-09-11'),
	("Spa-Francorchamps", "Belgio", 7004, 44, '2022-08-28'),
	("Interlagos", "Brasile", 4309, 71, '2022-05-12');

INSERT INTO mondiale (anno) VALUES
	('2022');

INSERT INTO vettura (nome, pilotaID, mondialeID) VALUES
	("F1-75 #16", 1, 1),
	("F1-75 #55", 2, 1);

INSERT INTO team_tecnico (vetturaID, ingegnereID) VALUES
	(1, 1),
	(1, 2),
	(1, 3),
	(2, 1),
	(2, 2),
	(2, 3);

INSERT INTO team_operativo (vetturaID, meccanicoID) VALUES
	(1, 1),
	(1, 2),
	(1, 3),
	(2, 1),
	(2, 2),
	(2, 3);