S10v0VRh

1-- create database
2SET FOREIGN_KEY_CHECKS=1;
3CREATE DATABASE IF NOT EXISTS chefdotcom;
4USE chefdotcom;
5
6-- drop tables
7DROP TABLE IF EXISTS CHEF;
8DROP TABLE IF EXISTS PROGRAMMATV;
9DROP TABLE IF EXISTS APPARIZIONE;
10
11-- create tables
12CREATE TABLE IF NOT EXISTS CHEF (
13    CodiceFiscaleChef VARCHAR(16) UNIQUE NOT NULL,
14    Nome VARCHAR(255) NOT NULL,
15    Cognome VARCHAR(255) NOT NULL,
16    Nazione VARCHAR(255) NOT NULL,
17    Eta INT,
18    PRIMARY KEY (CodiceFiscaleChef)
19);
20
21CREATE TABLE IF NOT EXISTS PROGRAMMATV (
22    CodProgramma VARCHAR(3) UNIQUE NOT NULL,
23    Titolo VARCHAR(255) NOT NULL,
24    CanaleTV VARCHAR(255) NOT NULL,
25    Editore VARCHAR(255) NOT NULL,
26    PRIMARY KEY (CodProgramma)
27);
28
29CREATE TABLE IF NOT EXISTS APPARIZIONE (
30    CodiceFiscaleChef VARCHAR(16) NOT NULL,
31    CodProgramma VARCHAR(3) NOT NULL,
32    NumeroPuntata INT NOT NULL,
33    `Data` DATE NOT NULL,
34    OraInizio TIME NOT NULL,
35    OraFine TIME DEFAULT NULL,
36    PRIMARY KEY (CodiceFiscaleChef, CodProgramma, `Data`, OraInizio),
37    
38    FOREIGN KEY (CodiceFiscaleChef)
39        REFERENCES CHEF(CodiceFiscaleChef)
40        ON DELETE CASCADE
41        ON UPDATE CASCADE,
42    
43    FOREIGN KEY (CodProgramma)
44        REFERENCES PROGRAMMATV(CodProgramma)
45        ON DELETE CASCADE
46        ON UPDATE CASCADE,
47    
48    CONSTRAINT CHK_NumeroPuntata 
49        CHECK (NumeroPuntata > 0),
50    
51    CONSTRAINT CHK_Orario 
52        CHECK (OraInizio < OraFine)
53);