MDS-DW-AviationProject/table_creation.sql at main · AimbotParce/MDS-DW-AviationProject · 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
---------------------------------------------------------------------------
-- MULTIDIMENSIONAL MODELING FOR THE ACME-FLYING USE CASE
-- Table creation
-- 20241029
-- Parcerisa Marc - Vendrix Alexis
---------------------------------------------------------------------------

-- Model information

CREATE TABLE DimManufacturer(
    ManufacturerID CHAR(6) NOT NULL,
    ManufacturerName CHAR(255) NOT NULL,
    PRIMARY KEY(ManufacturerID)
);

CREATE TABLE DimModel(
    ModelID CHAR(6) NOT NULL,
    ManufacturerID CHAR(6) NOT NULL,
    ModelName CHAR(255) NOT NULL,
    PRIMARY KEY(ModelID),
    FOREIGN KEY(ManufacturerID) REFERENCES DimManufacturer(ManufacturerID)
);

CREATE TABLE DimAircraft(
    AircraftID CHAR(6) NOT NULL,
    ModelID CHAR(6) NOT NULL,
    PRIMARY KEY(AircraftID),
    FOREIGN KEY(ModelID) REFERENCES DimModel(ModelID)
);

-- Reporter/Airport information

CREATE TABLE DimAirport(
    AirportCode CHAR(6) NOT NULL,
    AirportName CHAR(255) NOT NULL,
    PRIMARY KEY(AirportCode)
);

CREATE TABLE DimReporteur(
    ReporteurID NUMBER(20) NOT NULL,
    AirportCode CHAR(6) NULL,
    ReporteurType CHAR(255) NOT NULL, --PIREP or MAREP
    ReporteurName CHAR(255) NOT NULL,
    PRIMARY KEY (ReporteurID),
    FOREIGN KEY(AirportCode) REFERENCES DimAirport(AirportCode)
);

-- Maintenance events information

CREATE TABLE DimMaintenance(
    MaintenanceID CHAR(30) NOT NULL,
    MaintenanceStart DATE NOT NULL,
    IsScheduled CHAR(1) NOT NULL, -- Oracle v<23c doesn't support BOOLEAN values, so char "Y"/"N" must be used.
                                  -- Also, it's better to use CHAR than INT, bc CHAR is 8-bit and INT is 32-bit
    PRIMARY KEY(MaintenanceID)
);

CREATE TABLE FactMaintenance(
    MaintenanceID CHAR(30) NOT NULL,
    AircraftID CHAR(6) NOT NULL,
    MaintenanceDuration FLOAT(53) NOT NULL,
    PRIMARY KEY(MaintenanceID),
    FOREIGN KEY(AircraftID) REFERENCES DimAircraft(AircraftID),
    FOREIGN KEY(MaintenanceID) REFERENCES DimMaintenance(MaintenanceID)
);

-- Flights information

CREATE TABLE DimFlight(
    FlightID CHAR(26) NOT NULL,
    ScheduledDepartureTime DATE NOT NULL,
    DelayType CHAR(255) NOT NULL,
    PRIMARY KEY(FlightID)
);

CREATE TABLE FactFlight(
    FlightID CHAR(26) NOT NULL,
    AircraftID CHAR(6) NOT NULL,
    FlightDuration FLOAT(53) NOT NULL,
    DelayDuration FLOAT(53) NOT NULL,
    PRIMARY KEY(FlightID),
    FOREIGN KEY(FlightID) REFERENCES DimFlight(FlightID),
    FOREIGN KEY(AircraftID) REFERENCES DimAircraft(AircraftID)
);

-- Flight reports

CREATE TABLE FactReportEntries(
    FlightID CHAR(26) NOT NULL,
    ReporteurID NUMBER(20) NOT NULL,
    EntryCount INT NOT NULL,
    PRIMARY KEY(FlightID, ReporteurID),
    FOREIGN KEY(ReporteurID) REFERENCES DimReporteur(ReporteurID),
    FOREIGN KEY(FlightID) REFERENCES DimFlight(FlightID)
);