Wt92dZs5

1-- create the database
2DROP DATABASE IF EXISTS premierwatrousp;
3CREATE DATABASE premierwatrousp;
4
5-- select the databaseorganizersorganizers
6USE premierwatrousp;
7
8-- create the tables
9CREATE TABLE stadiums
10(
11  venue		VARCHAR(60)		PRIMARY KEY,
12  team		VARCHAR(60)		UNIQUE NOT NULL
13);
14
15CREATE TABLE managers
16(
17	manager_id		INT			PRIMARY KEY,
18    manager_name	VARCHAR(60) NOT NULL,
19	team			VARCHAR(60) NOT NULL,
20    nationality		VARCHAR(60) NOT NULL,
21    status			VARCHAR(6) 	NOT NULL,
22	CONSTRAINT managers_fk_stadiums
23		FOREIGN KEY (team)
24		REFERENCES stadiums (team)
25		ON DELETE CASCADE
26);
27
28CREATE TABLE matches
29(
30	match_number 		INT			PRIMARY KEY,
31    match_day			INT			NOT NULL,
32    date				DATE		NOT NULL,
33    team1				VARCHAR(60) NOT NULL,
34    team2				VARCHAR(60) NOT NULL,
35    half_score_team1	INT 		NOT NULL,
36    half_score_team2	INT			NOT NULL,
37    full_score_team1	INT			NOT NULL,
38    full_score_team2	INT			NOT NULL,
39	CONSTRAINT team1_fk_stadiums
40		FOREIGN KEY (team1)
41		REFERENCES stadiums (team)
42		ON DELETE CASCADE,
43	CONSTRAINT team2_fk_stadiums
44		FOREIGN KEY (team2)
45		REFERENCES stadiums (team)
46		ON DELETE CASCADE
47);