- Database Creation
– DROP TABLES
DROP TABLE IF EXISTS plays;
DROP TABLE IF EXISTS team;
DROP TABLE IF EXISTS bets;
DROP TABLE IF EXISTS odds;
DROP TABLE IF EXISTS game;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS customer_details;
DROP TABLE IF EXISTS account_details;
– CREATE TABLES
CREATE TABLE account_details (
account_no int(6) NOT NULL auto_increment,
balance NUMERIC(8,2),
PRIMARY KEY (account_no)
);
CREATE TABLE customer_details (
customer_id int(6) NOT NULL auto_increment,
first_name VARCHAR(30),
last_name VARCHAR(30),
date_of_birth DATE,
email VARCHAR(50),
phone INT(11),
street_address VARCHAR(50),
city VARCHAR(20),
postcode CHAR(8),
user_password CHAR(8),
account_no int(6),
PRIMARY KEY (customer_id),
FOREIGN KEY (account_no) REFERENCES account_details(account_no)
);
CREATE TABLE staff (
staff_id int(6) NOT NULL auto_increment,
first_name VARCHAR(30),
last_name VARCHAR(30),
PRIMARY KEY (staff_id)
);
CREATE TABLE game (
game_number int(6) NOT NULL auto_increment,
kick_off_time datetime,
home_score int (2),
away_score int (2),
result_code char (1),
PRIMARY KEY (game_number)
);
CREATE TABLE odds (
game_number int(6) NOT NULL auto_increment,
result char (1),
first_term int,
second_term int,
PRIMARY KEY (game_number, result),
FOREIGN KEY (game_number) REFERENCES game(game_number)
);
CREATE TABLE bets (
bet_id int(6) NOT NULL auto_increment,
amount NUMERIC(8,2),
customer_id int(6),
staff_id int(6),
game_number int(6),
result char(1),
PRIMARY KEY (bet_id),
FOREIGN KEY (customer_id) REFERENCES customer_details(customer_id),
FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
FOREIGN KEY (game_number, result) REFERENCES odds(game_number, result)
);
CREATE TABLE team (
team_name char(25) NOT NULL,
position int(2),
stadium varchar (25),
city varchar (20),
PRIMARY KEY (team_name)
);
CREATE TABLE plays
(
game_number int(6) NOT NULL auto_increment,
home_or_away char (1),
team_name char (25),
PRIMARY KEY (game_number, home_or_away),
FOREIGN KEY(team_name) REFERENCES team(team_name),
FOREIGN KEY (game_number) REFERENCES game(game_number)
);