-
Notifications
You must be signed in to change notification settings - Fork 0
/
a2.ddl
161 lines (140 loc) · 4.88 KB
/
a2.ddl
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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
DROP SCHEMA IF EXISTS A2 CASCADE;
CREATE SCHEMA A2;
SET search_path TO A2;
DROP TABLE IF EXISTS country CASCADE;
DROP TABLE IF EXISTS player CASCADE;
DROP TABLE IF EXISTS record CASCADE;
DROP TABLE IF EXISTS court CASCADE;
DROP TABLE IF EXISTS tournament CASCADE;
DROP TABLE IF EXISTS event CASCADE;
DROP TABLE IF EXISTS Query1 CASCADE;
DROP TABLE IF EXISTS Query2 CASCADE;
DROP TABLE IF EXISTS Query3 CASCADE;
DROP TABLE IF EXISTS Query4 CASCADE;
DROP TABLE IF EXISTS Query5 CASCADE;
DROP TABLE IF EXISTS Query6 CASCADE;
DROP TABLE IF EXISTS Query7 CASCADE;
DROP TABLE IF EXISTS Query8 CASCADE;
DROP TABLE IF EXISTS Query9 CASCADE;
DROP TABLE IF EXISTS Query10 CASCADE;
-- The country table contains some countries in the world.
-- 'cid' is the id of the country.
-- 'cname' is the name of the country.
CREATE TABLE country(
cid INTEGER PRIMARY KEY,
cname VARCHAR NOT NULL
);
-- The player table contains information about some tennis players.
-- 'pid' is the id of the player.
-- 'pname' is the name of the player.
-- 'globalrank' is the global rank of the player.
-- 'cid' is the id of the country that the player belongs to.
CREATE TABLE player(
pid INTEGER PRIMARY KEY,
pname VARCHAR NOT NULL,
globalrank INTEGER NOT NULL,
cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT
);
-- The record table contains information about players performance in each year.
-- 'pid' is the id of the player.
-- 'year' is the year.
-- 'wins' is the number of wins of the player in that year.
-- 'losses' is the the number of losses of the player in that year.
CREATE TABLE record(
pid INTEGER REFERENCES player(pid) ON DELETE RESTRICT,
year INTEGER NOT NULL,
wins INTEGER NOT NULL,
losses INTEGER NOT NULL,
PRIMARY KEY(pid, year));
-- The tournament table contains information about a tournament.
-- 'tid' is the id of the tournament.
-- 'tname' is the name of the tournament.
-- 'cid' is the country where the tournament hold.
CREATE TABLE tournament(
tid INTEGER PRIMARY KEY,
tname VARCHAR NOT NULL,
cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT
);
-- The court table contains the information about tennis court
-- 'courtid' is the id of the court.
-- 'courtname' is the name of the court.
-- 'capacity' is the maximum number of audience the court can hold.
-- 'tid' is the tournament that this court is used for
-- Notice: only one tournament can happen on a given court.
CREATE TABLE court(
courtid INTEGER PRIMARY KEY,
courtname VARCHAR NOT NULL,
capacity INTEGER NOT NULL,
tid INTEGER REFERENCES tournament(tid) ON DELETE RESTRICT
);
-- The champion table provides information about the champion of each tournament.
-- 'pid' refers to the id of the champion(player).
-- 'year' is the year when the tournament hold.
-- 'tid' is the tournament id.
CREATE TABLE champion(
pid INTEGER REFERENCES player(pid) ON DELETE RESTRICT,
year INTEGER NOT NULL,
tid INTEGER REFERENCES tournament(tid) ON DELETE RESTRICT,
PRIMARY KEY(tid, year));
-- The event table provides information about certain tennis games.
-- 'eid' refers to the id of the event.
-- 'year' is the year when the event hold.
-- 'courtid' is the id of the court where the event hold.
-- 'pwinid' is the id of the player who win the game.
-- 'plossid' is the id of the player who loss the game.
-- 'duration' is duration of the event, in minutes.
CREATE TABLE event(
eid INTEGER PRIMARY KEY,
year INTEGER NOT NULL,
courtid INTEGER REFERENCES court(courtid) ON DELETE RESTRICT,
winid INTEGER REFERENCES player(pid) ON DELETE RESTRICT,
lossid INTEGER REFERENCES player(pid) ON DELETE RESTRICT,
duration INTEGER NOT NULL
);
-- The following tables will be used to store the results of your queries.
-- Each of them should be populated by your last SQL statement that looks like:
-- "INSERT INTO QueryX (SELECT ...<complete your SQL query here> ... )"
CREATE TABLE query1(
pname VARCHAR,
cname VARCHAR,
tname VARCHAR
);
CREATE TABLE query2(
tname VARCHAR,
totalCapacity INTEGER
);
CREATE TABLE query3(
p1id INTEGER,
p1name VARCHAR,
p2id INTEGER,
p2name VARCHAR
);
CREATE TABLE query4(
pid INTEGER,
pname VARCHAR
);
CREATE TABLE query5(
pid INTEGER,
pname VARCHAR,
avgwins REAL
);
CREATE TABLE query6(
pid INTEGER,
pname VARCHAR
);
CREATE TABLE query7(
pname VARCHAR,
year INTEGER
);
CREATE TABLE query8(
p1name VARCHAR,
p2name VARCHAR,
cname VARCHAR
);
CREATE TABLE query9(
cname VARCHAR,
champions INTEGER
);
CREATE TABLE query10(
pname VARCHAR
);