Olympics--database_management_react/olympics_ddl.sql at main · michellevarghese/Olympics--database_management_react · 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
139
140
141
142
143
144
145
146
drop database olympics;
create database olympics;

\c olympics


CREATE TABLE 	COUNTRY
 (	country_id SERIAL NOT NULL PRIMARY KEY,
	country_name VARCHAR(30) NOT NULL,

	UNIQUE (country_name));


CREATE TABLE 	PARTICIPANT
 (	player_id SERIAL NOT NULL ,
	country_id INT NOT NULL ,
	player_name VARCHAR(50) NOT NULL,
	gender VARCHAR(1) ,
	age INT CHECK (age > 16),
	weight INT,

	PRIMARY KEY (player_id),
	FOREIGN KEY (country_id) REFERENCES COUNTRY(country_id)    );

CREATE TABLE 	EVENT
 (	event_id SERIAL NOT NULL ,
	event_name VARCHAR(30) NOT NULL,
	world_record DECIMAL(5,2) DEFAULT NULL,
	record_holder_id INT DEFAULT NULL,

	UNIQUE (event_name) ,
	PRIMARY KEY (event_id),
	FOREIGN KEY (record_holder_id) REFERENCES PARTICIPANT(player_id)  );


CREATE TABLE 	VENUE
 (	venue_id SERIAL NOT NULL ,
	venue_name VARCHAR(30) NOT NULL ,
	no_of_seats INT,
	type VARCHAR(30),

	UNIQUE (venue_name) ,
	PRIMARY KEY (venue_id)       );

 CREATE TABLE 	COMPETES
 (	player_id SERIAL NOT NULL ,
	event_id INT NOT NULL ,
	year INT NOT NULL,
	score DECIMAL(5,2) DEFAULT NULL,

	PRIMARY KEY (player_id, event_id, year),
	FOREIGN KEY (player_id) REFERENCES PARTICIPANT(player_id),
	FOREIGN KEY (event_id) REFERENCES EVENT(event_id)    );

CREATE TABLE 	HELD_AT
 (	venue_id SERIAL NOT NULL ,
	event_id INT NOT NULL ,
	date DATE NOT NULL ,

	--UNIQUE (date) ,
	PRIMARY KEY (event_id, venue_id, date),
	FOREIGN KEY (event_id) REFERENCES EVENT(event_id),
    FOREIGN KEY (venue_id) REFERENCES VENUE(venue_id)     );


CREATE TABLE 	WINNER
 ( player_id SERIAL NOT NULL ,
	event_id INT NOT NULL,
	year INT NOT NULL,
	medal VARCHAR(7),

	PRIMARY KEY (player_id, event_id, year),
	FOREIGN KEY (player_id,event_id,year) REFERENCES COMPETES(player_id,event_id,year));


CREATE TABLE 	PENALTY
 (	penalty_id SERIAL NOT NULL ,
	player_id INT NOT NULL ,
	event_id INT NOT NULL,
	year INT NOT NULL,
	type VARCHAR(40),

	PRIMARY KEY (penalty_id),
	FOREIGN KEY (player_id,event_id,year) REFERENCES COMPETES(player_id,event_id,year)       );

CREATE TABLE 	EQUIPMENT
	( equip_id SERIAL NOT NULL ,
	event_id INT NOT NULL,
	cost INT ,
	type VARCHAR(10),

	PRIMARY KEY (equip_id),
	FOREIGN KEY (event_id) REFERENCES EVENT(event_id)       );


CREATE TABLE 	AUDIENCE
 (	ticket_id SERIAL NOT NULL ,
	venue_id INT NOT NULL ,
	person_name VARCHAR(30) NOT NULL,
	ticket_price DECIMAL(6,2) NOT NULL,
	event_id INT NOT NULL,
	date DATE NOT NULL,

	PRIMARY KEY (ticket_id),
	FOREIGN KEY (venue_id,event_id,date) REFERENCES HELD_AT(venue_id,event_id,date)       );

-- 	--On delete cascade using ALTER
-- ALTER TABLE winner
-- DROP CONSTRAINT winner_event_id_fkey,
-- ADD CONSTRAINT c1 FOREIGN KEY (player_id,event_id,year)
-- REFERENCES COMPETES (player_id,event_id,year) ON DELETE CASCADE;


ALTER TABLE winner
DROP CONSTRAINT winner_player_id_event_id_year_fkey,
ADD CONSTRAINT c1 FOREIGN KEY (player_id,event_id,year)
REFERENCES COMPETES (player_id,event_id,year) ON DELETE CASCADE;


ALTER TABLE penalty
DROP CONSTRAINT penalty_player_id_event_id_year_fkey,
ADD CONSTRAINT c2 FOREIGN KEY (player_id)
REFERENCES PARTICIPANT (player_id) ON DELETE CASCADE;


ALTER TABLE audience
DROP CONSTRAINT audience_venue_id_event_id_date_fkey,
ADD CONSTRAINT c3 FOREIGN KEY (venue_id)
REFERENCES VENUE (venue_id) ON DELETE CASCADE;


ALTER TABLE equipment
DROP CONSTRAINT equipment_event_id_fkey,
ADD CONSTRAINT c4 FOREIGN KEY (event_id)
REFERENCES EVENT (event_id) ON DELETE CASCADE;

ALTER TABLE held_at
DROP CONSTRAINT held_at_event_id_fkey,
ADD CONSTRAINT c4 FOREIGN KEY (event_id)
REFERENCES EVENT (event_id) ON DELETE CASCADE;

ALTER TABLE held_at
DROP CONSTRAINT held_at_venue_id_fkey,
ADD CONSTRAINT c5 FOREIGN KEY (venue_id)
REFERENCES VENUE (venue_id) ON DELETE CASCADE;