Pronuntia/database.sql at main · DesperateColleagues/Pronuntia · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
CREATE DATABASE IF NOT EXISTS yii2basic;
USE yii2basic;

CREATE TABLE IF NOT EXISTS logopedista (
  nome CHAR(60) NOT NULL,
  cognome CHAR(60) NOT NULL,
  dataNascita DATE NOT NULL,
  email CHAR(255) NOT NULL PRIMARY KEY,
  passwordD CHAR(255) NOT NULL,
  authKey CHAR(50) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS caregiver (
  nome CHAR(60) NOT NULL,
  cognome CHAR(60) NOT NULL,
  dataNascita DATE NOT NULL,
  email CHAR(255) NOT NULL PRIMARY KEY,
  passwordD CHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS utente (
  username CHAR(60) PRIMARY KEY,
  nome CHAR(60) NOT NULL,
  cognome CHAR(60) NOT NULL,
  dataNascita DATE NOT NULL,
  email CHAR(255) NOT NULL,
  passwordD CHAR(255) NOT NULL,
  logopedista CHAR(255) NOT NULL,
  caregiver CHAR(255) NULL DEFAULT NULL,
  FOREIGN KEY (logopedista) REFERENCES logopedista(email),
  FOREIGN KEY (caregiver) REFERENCES caregiver(email)
);

CREATE TABLE IF NOT EXISTS appuntamento (
  dataAppuntamento DATE NOT NULL,
  oraAppuntamento TIME NOT NULL,
  logopedista CHAR(255) NOT NULL,
  utente CHAR(255) NULL,
  caregiver CHAR(255) NULL,
  PRIMARY KEY (dataAppuntamento,oraAppuntamento,logopedista),
  FOREIGN KEY (logopedista) REFERENCES logopedista(email),
  FOREIGN KEY (caregiver) REFERENCES caregiver(email),
  FOREIGN KEY (utente) REFERENCES utente(username)
);

CREATE TABLE IF NOT EXISTS diagnosi (
  id VARCHAR(23),
  mediaFile BLOB,
  path VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS appuntamento (
  dataAppuntamento DATE NOT NULL,
  oraAppuntamento TIME NOT NULL,
  logopedista CHAR(255) NOT NULL,
  utente CHAR(255) NULL,
  caregiver CHAR(255) NULL,
  diagnosi VARCHAR(23),
  PRIMARY KEY (dataAppuntamento,oraAppuntamento,logopedista),
  FOREIGN KEY (logopedista) REFERENCES logopedista(email),
  FOREIGN KEY (caregiver) REFERENCES caregiver(email),
  FOREIGN KEY (utente) REFERENCES utente(username),
  FOREIGN KEY (diagnosi) REFERENCES diagnosi(id)
);


SELECT * FROM logopedista;
SELECT * FROM caregiver;
SELECT * FROM utente;

/*DROP TABLE utente;
DROP TABLE caregiver;
DROP TABLE logopedista;*/