519DgicH

1CREATE TABLE IF NOT EXISTS categories(
2	id SERIAL PRIMARY KEY,
3	name VARCHAR(50) NOT NULL
4);
5
6
7CREATE TABLE IF NOT EXISTS addresses(
8	id SERIAL PRIMARY KEY, 
9	street_name VARCHAR(100) NOT NULL, 
10	street_number INT NOT NULL,
11	town VARCHAR(30) NOT NULL,
12	country VARCHAR(50) NOT NULL,
13	zip_code INT NOT NULL,
14	
15	CONSTRAINT ch_addresses_street_number
16		CHECK (street_number > 0),
17	CONSTRAINT ch_addresses_zip_code
18		CHECK (zip_code > 0)
19);
20
21
22CREATE TABLE IF NOT EXISTS publishers(
23	id SERIAL PRIMARY KEY,
24	name VARCHAR(30) NOT NULL,
25	address_id INT NOT NULL,
26	website VARCHAR(40),
27	phone VARCHAR(20),
28	
29	CONSTRAINT fk_publishers_addresses
30		FOREIGN KEY (address_id)
31		REFERENCES addresses(id)
32		ON UPDATE CASCADE
33		ON DELETE CASCADE
34);
35
36
37CREATE TABLE IF NOT EXISTS players_ranges(
38	id SERIAL PRIMARY KEY,
39	min_players INT NOT NULL,
40	max_players INT NOT NULL,
41	
42	CONSTRAINT ch_players_ranges_min_players
43		CHECK (min_players > 0),
44	CONSTRAINT ch_players_ranges_max_players
45		CHECK (max_players > 0)
46);
47
48
49CREATE TABLE IF NOT EXISTS creators(
50	id SERIAL PRIMARY KEY,
51	first_name VARCHAR(30) NOT NULL,
52	last_name VARCHAR(30) NOT NULL,
53	email VARCHAR(30) NOT NULL
54);
55
56
57CREATE TABLE IF NOT EXISTS board_games(
58	id SERIAL PRIMARY KEY,
59	name VARCHAR(30) NOT NULL,
60	release_year INT NOT NULL,
61	ratings NUMERIC(3, 2) NOT NULL,
62	category_id INT NOT NULL,
63	publisher_id INT NOT NULL,
64	players_range_id INT NOT NULL,
65	
66	CONSTRAINT ch_board_games_release_year
67		CHECK(release_year > 0),
68	CONSTRAINT fk_bord_games_categories
69		FOREIGN KEY(category_id)
70		REFERENCES categories(id)
71		ON UPDATE CASCADE
72		ON DELETE CASCADE,
73	CONSTRAINT fk_board_games_publishers
74		FOREIGN KEY(publisher_id)
75		REFERENCES publishers(id)
76		ON UPDATE CASCADE
77		ON DELETE CASCADE,
78	CONSTRAINT fk_board_games_players_ranges
79		FOREIGN KEY(players_range_id)
80		REFERENCES players_ranges(id)
81		ON UPDATE CASCADE
82		ON DELETE CASCADE	
83);
84
85CREATE TABLE IF NOT EXISTS creators_board_games(
86	creator_id INT NOT NULL,
87	board_game_id INT NOT NULL,
88	
89	CONSTRAINT fk_creators_board_games_creators
90		FOREIGN KEY(creator_id)
91		REFERENCES creators(id)
92		ON UPDATE CASCADE
93		ON DELETE CASCADE,
94	CONSTRAINT fk_creators_board_games_board_games
95		FOREIGN KEY(board_game_id)
96		REFERENCES board_games(id)
97		ON UPDATE CASCADE
98		ON DELETE CASCADE
99);	
100
101