1 | drop schema if exists project cascade;
|
---|
2 | create schema project;
|
---|
3 |
|
---|
4 | drop table if exists pilots cascade;
|
---|
5 | drop table if exists pilot_videos cascade;
|
---|
6 | drop table if exists pilot_images cascade;
|
---|
7 | drop table if exists organizers cascade;
|
---|
8 | drop table if exists drones cascade;
|
---|
9 | drop table if exists countries cascade;
|
---|
10 | drop table if exists cities cascade;
|
---|
11 | drop table if exists races cascade;
|
---|
12 | drop table if exists participants cascade;
|
---|
13 |
|
---|
14 |
|
---|
15 | create table pilots(
|
---|
16 | pilot_id serial not null,
|
---|
17 | pilot_name varchar(100) not null,
|
---|
18 | first_name varchar(100) not null,
|
---|
19 | last_name varchar(100) not null,
|
---|
20 | birth_date date null,
|
---|
21 | email varchar(100) not null,
|
---|
22 | "password" varchar not null,
|
---|
23 | member_since date not null,
|
---|
24 | bio varchar(2000) null,
|
---|
25 | constraint pilot_id_pk primary key (pilot_id)
|
---|
26 | );
|
---|
27 |
|
---|
28 | ---------------------------------------------------------------
|
---|
29 |
|
---|
30 | create table pilot_videos(
|
---|
31 | video_id serial not null,
|
---|
32 | video_link varchar(100) not null,
|
---|
33 | pilot_id integer not null,
|
---|
34 | CONSTRAINT pilot_video_pk primary key (video_id),
|
---|
35 | constraint pilot_videos_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id)
|
---|
36 | );
|
---|
37 |
|
---|
38 | ---------------------------------------------------------------
|
---|
39 |
|
---|
40 |
|
---|
41 | create table pilot_images(
|
---|
42 | image_id serial not null,
|
---|
43 | image_data bytea not null,
|
---|
44 | is_profilePicture boolean not null,
|
---|
45 | pilot_id integer not null,
|
---|
46 | constraint image_id_pk primary key (image_id),
|
---|
47 | constraint pilot_images_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id)
|
---|
48 | );
|
---|
49 |
|
---|
50 |
|
---|
51 | ---------------------------------------------------------------
|
---|
52 |
|
---|
53 | create table organizers(
|
---|
54 | organizer_id serial not null,
|
---|
55 | organizer_name varchar(100) not null,
|
---|
56 | constraint organizer_id_pk primary key (organizer_id)
|
---|
57 | );
|
---|
58 |
|
---|
59 | ---------------------------------------------------------------
|
---|
60 |
|
---|
61 | create table drones(
|
---|
62 | drone_id serial not null,
|
---|
63 | motors varchar(100) null,
|
---|
64 | esc varchar(100) null,
|
---|
65 | frame varchar(100) null,
|
---|
66 | flight_controller varchar(100) null,
|
---|
67 | battery varchar(100) null,
|
---|
68 | drone_pilot_id integer not null,
|
---|
69 | constraint drone_id_pk primary key (drone_id),
|
---|
70 | constraint drone_pilot_id_fk foreign key (drone_pilot_id) references pilots(pilot_id)
|
---|
71 | );
|
---|
72 |
|
---|
73 | ---------------------------------------------------------------
|
---|
74 |
|
---|
75 | create table countries(
|
---|
76 | country_id serial not null,
|
---|
77 | country_name varchar(100) not null,
|
---|
78 | constraint country_id_pk primary key (country_id)
|
---|
79 |
|
---|
80 | );
|
---|
81 |
|
---|
82 |
|
---|
83 |
|
---|
84 | ---------------------------------------------------------------
|
---|
85 |
|
---|
86 | create table cities(
|
---|
87 | city_id serial not null,
|
---|
88 | city_name varchar(100) not null,
|
---|
89 | country_id integer not null,
|
---|
90 | constraint city_id_pk primary key (city_id),
|
---|
91 | constraint country_id_fk foreign key (country_id) references countries(country_id)
|
---|
92 | );
|
---|
93 |
|
---|
94 |
|
---|
95 |
|
---|
96 | ---------------------------------------------------------------
|
---|
97 |
|
---|
98 |
|
---|
99 | create table races(
|
---|
100 | race_id serial not null,
|
---|
101 | track_photo bytea null,
|
---|
102 | race_description varchar(100) null,
|
---|
103 | start_date date not null,
|
---|
104 | end_date date not null,
|
---|
105 | city_id integer not null,
|
---|
106 | organizer_id integer not null,
|
---|
107 | constraint organizer_id_fk foreign key (organizer_id) references organizers(organizer_id),
|
---|
108 | constraint race_id_pk primary key (race_id),
|
---|
109 | constraint race_location_fk foreign key (city_id) references cities(city_id)
|
---|
110 | );
|
---|
111 |
|
---|
112 | ---------------------------------------------------------------
|
---|
113 |
|
---|
114 | create table participants(
|
---|
115 | pilot_id integer not null,
|
---|
116 | race_id integer not null,
|
---|
117 | constraint participating_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id),
|
---|
118 | constraint participating_race_id_fk foreign key (race_id) references races (race_id)
|
---|
119 | ); |
---|