preventive_maintenance/create_db.sql at main · Dim2960/preventive_maintenance · 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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
-- Active: 1718803304234@@127.0.0.1@5432
-- CREATE DATABASE preventive_maintenance;


CREATE TABLE aeronefs (
    ref_aero VARCHAR(15) NOT NULL PRIMARY KEY UNIQUE,
    type_model VARCHAR(15) NOT NULL,
    debut_service DATE NOT NULL,
    last_maint DATE NOT NULL,
    en_maintenance BOOLEAN NOT NULL,
    end_maint TIMESTAMP
);

CREATE TABLE aeronefs_cdc (
    operation CHAR(1) NOT NULL, -- 'I' pour INSERT, 'U' pour UPDATE, 'D' pour DELETE
    ref_aero VARCHAR(15),
    type_model VARCHAR(15),
    debut_service DATE,
    last_maint DATE,
    en_maintenance BOOLEAN,
    end_maint TIMESTAMP,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE composants (
    ref_compo VARCHAR(25) PRIMARY KEY,
    categorie VARCHAR(50) NOT NULL,
    aero VARCHAR(15) NOT NULL,
    desc_compo  VARCHAR(255) NOT NULL,
    lifespan INT NOT NULL,
    taux_usure_actuel FLOAT,
    cout INT NOT NULL
);

CREATE TABLE composants_cdc (
    operation CHAR(1) NOT NULL, -- 'I' pour INSERT, 'U' pour UPDATE, 'D' pour DELETE
    ref_compo VARCHAR(25) NOT NULL,
    categorie VARCHAR(50) NOT NULL,
    aero VARCHAR(15) NOT NULL,
    desc_compo VARCHAR(255) NOT NULL,
    lifespan INT NOT NULL,
    taux_usure_actuel FLOAT,
    cout INT NOT NULL,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE degradations (
    clef VARCHAR(100) NOT NULL PRIMARY KEY,
    ref_deg VARCHAR(15) NOT NULL,
    linked_aero VARCHAR(15) NOT NULL,
    compo_concerned VARCHAR(25) NOT NULL,
    usure_nouvelle FLOAT NOT NULL,
    measure_day DATE NOT NULL,
    need_replacement BOOLEAN NOT NULL
);

CREATE TABLE degradations_cdc (
    operation CHAR(1) NOT NULL, -- 'I' pour INSERT, 'U' pour UPDATE, 'D' pour DELETE
    clef VARCHAR(100) NOT NULL,
    ref_deg VARCHAR(15) NOT NULL,
    linked_aero VARCHAR(15) NOT NULL,
    compo_concerned VARCHAR(25) NOT NULL,
    usure_nouvelle FLOAT NOT NULL,
    measure_day DATE NOT NULL,
    need_replacement BOOLEAN NOT NULL,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE logs_vols (
    ref_vol VARCHAR(15) NOT NULL PRIMARY KEY,
    aero_linked VARCHAR(15) NOT NULL,
    jour_vol DATE NOT NULL,
    time_en_air FLOAT NOT NULL,
    sensor_data VARCHAR(255) NOT NULL,
    etat_voyant INT NOT NULL,
    temp FLOAT NOT NULL,
    temp_unit VARCHAR(255) NOT NULL,
    pressure FLOAT NOT NULL,
    pressure_unit VARCHAR(255) NOT NULL,
    vibrations FLOAT NOT NULL,
    vibrations_unit VARCHAR(255) NOT NULL,
    prediction_etat_voyant INT NOT NULL DEFAULT 0
);

CREATE TABLE logs_vols_cdc (
    operation CHAR(1) NOT NULL, -- 'I' pour INSERT, 'U' pour UPDATE, 'D' pour DELETE
    ref_vol VARCHAR(15) NOT NULL,
    aero_linked VARCHAR(15) NOT NULL,
    jour_vol DATE NOT NULL,
    time_en_air FLOAT NOT NULL,
    sensor_data VARCHAR(255) NOT NULL,
    etat_voyant INT NOT NULL,
    temp FLOAT NOT NULL,
    temp_unit VARCHAR(255) NOT NULL,
    pressure FLOAT NOT NULL,
    pressure_unit VARCHAR(255) NOT NULL,
    vibrations FLOAT NOT NULL,
    vibrations_unit VARCHAR(255) NOT NULL,
    prediction_etat_voyant INT NOT NULL DEFAULT 0,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);