f1-dwh-example/ddl.sql at main · basharah/f1-dwh-example · 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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
CREATE TABLE circuits (
    circuitId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    circuitRef VARCHAR(255) NOT NULL DEFAULT '',
    name VARCHAR(255) NOT NULL DEFAULT '',
    location VARCHAR(255) DEFAULT NULL,
    country VARCHAR(255) DEFAULT NULL,
    lat FLOAT DEFAULT NULL,
    lng FLOAT DEFAULT NULL,
    alt INT DEFAULT NULL
);

CREATE TABLE constructors (
    constructorId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    constructorRef VARCHAR(255) NOT NULL DEFAULT '',
    name VARCHAR(255) NOT NULL DEFAULT '',
    nationality VARCHAR(255) DEFAULT NULL,
    CONSTRAINT unique_constructor_name UNIQUE (name)
);

CREATE TABLE drivers (
    driverId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    driverRef VARCHAR(255) NOT NULL DEFAULT '',
    number INT DEFAULT NULL,
    code VARCHAR(3) DEFAULT NULL,
    forename VARCHAR(255) NOT NULL DEFAULT '',
    surname VARCHAR(255) NOT NULL DEFAULT '',
    dob DATE DEFAULT NULL,
    nationality VARCHAR(255) DEFAULT NULL
);

CREATE TABLE seasons (
    year INT PRIMARY KEY
);

CREATE TABLE status (
    statusId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    status VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE TABLE races (
    raceId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    year INT NOT NULL REFERENCES seasons(year),
    round INT NOT NULL DEFAULT 0,
    circuitId INT NOT NULL REFERENCES circuits(circuitId),
    name VARCHAR(255) NOT NULL DEFAULT '',
    date DATE NOT NULL
);

CREATE TABLE constructorresults (
    constructorResultsId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    raceId INT NOT NULL REFERENCES races(raceId),
    constructorId INT NOT NULL REFERENCES constructors(constructorId),
    points FLOAT DEFAULT NULL,
    status VARCHAR(255) DEFAULT NULL
);

CREATE TABLE constructorstandings (
    constructorStandingsId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    raceId INT NOT NULL REFERENCES races(raceId),
    constructorId INT NOT NULL REFERENCES constructors(constructorId),
    points FLOAT NOT NULL DEFAULT 0,
    position INT DEFAULT NULL,
    positionText VARCHAR(255) DEFAULT NULL,
    wins INT NOT NULL DEFAULT 0
);

CREATE TABLE driverstandings (
    driverStandingsId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    raceId INT NOT NULL REFERENCES races(raceId),
    driverId INT NOT NULL REFERENCES drivers(driverId),
    points FLOAT NOT NULL DEFAULT 0,
    position INT DEFAULT NULL,
    positionText VARCHAR(255) DEFAULT NULL,
    wins INT NOT NULL DEFAULT 0
);

CREATE TABLE pitstops (
    raceId INT NOT NULL REFERENCES races(raceId),
    driverId INT NOT NULL REFERENCES drivers(driverId),
    stop INT NOT NULL,
    lap INT NOT NULL,
    time TIME NOT NULL,
    duration VARCHAR(255) DEFAULT NULL,
    milliseconds INT DEFAULT NULL,
    PRIMARY KEY (raceId, driverId, stop)
);

CREATE TABLE qualifying (
    qualifyId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    raceId INT NOT NULL REFERENCES races(raceId),
    driverId INT NOT NULL REFERENCES drivers(driverId),
    constructorId INT NOT NULL REFERENCES constructors(constructorId),
    number INT NOT NULL DEFAULT 0,
    position INT DEFAULT NULL,
    q1 VARCHAR(255) DEFAULT NULL,
    q2 VARCHAR(255) DEFAULT NULL,
    q3 VARCHAR(255) DEFAULT NULL
);

CREATE TABLE results (
    resultId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    raceId INT NOT NULL REFERENCES races(raceId),
    driverId INT NOT NULL REFERENCES drivers(driverId),
    constructorId INT NOT NULL REFERENCES constructors(constructorId),
    number INT DEFAULT NULL,
    grid INT NOT NULL DEFAULT 0,
    position INT DEFAULT NULL,
    positionText VARCHAR(255) NOT NULL DEFAULT '',
    positionOrder INT NOT NULL DEFAULT 0,
    points FLOAT NOT NULL DEFAULT 0,
    laps INT NOT NULL DEFAULT 0,
    time VARCHAR(255) DEFAULT NULL,
    milliseconds INT DEFAULT NULL,
    fastestLap INT DEFAULT NULL,
    rank INT DEFAULT 0,
    fastestLapTime VARCHAR(255) DEFAULT NULL,
    fastestLapSpeed VARCHAR(255) DEFAULT NULL,
    statusId INT NOT NULL REFERENCES status(statusId)
);

CREATE TABLE sprintresults (
    sprintResultId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    raceId INT NOT NULL REFERENCES races(raceId),
    driverId INT NOT NULL REFERENCES drivers(driverId),
    constructorId INT NOT NULL REFERENCES constructors(constructorId),
    number INT NOT NULL DEFAULT 0,
    grid INT NOT NULL DEFAULT 0,
    position INT DEFAULT NULL,
    positionText VARCHAR(255) NOT NULL DEFAULT '',
    positionOrder INT NOT NULL DEFAULT 0,
    points FLOAT NOT NULL DEFAULT 0,
    laps INT NOT NULL DEFAULT 0,
    time VARCHAR(255) DEFAULT NULL,
    milliseconds INT DEFAULT NULL,
    fastestLap INT DEFAULT NULL,
    fastestLapTime VARCHAR(255) DEFAULT NULL,
    statusId INT NOT NULL REFERENCES status(statusId)
);