sqlqueries.txt

create database playpal_db;
use playpal_db;

create table User(
user_id int auto_increment primary key,
name varchar(30) NOT NULL,
dob date,
age int NOT NULL,
email varchar(40),
address varchar(100),
weight numeric(5,2),
height int,
username varchar(20) NOT NULL UNIQUE,
password varchar(20) NOT NULL,
bmi numeric(4,2));

create table Event(
event_id int auto_increment primary key,
event_name varchar(100) NOT NULL UNIQUE,
sport varchar(100),
ageLimit int,
venue varchar(255),
time time,
date date,
noOfParticipants int);

create table Event_creator(
event_id int,
user_id int,
primary key(event_id),
foreign key(event_id) references Event(event_id),
foreign key(user_id) references User(user_id));

create table Tournament(
tour_id int auto_increment primary key,
noOfTeams int,
tour_name varchar(255) NOT NULL UNIQUE,
entryFee numeric(5,2),
tour_ageLimit int,
tour_venue varchar(20),
tour_time varchar(20),
tour_date varchar(20),
tour_noOfParticipants int);


create table tourn_participation(
user_id int,
tour_id int,
foreign key(user_id) references User(user_id),
foreign key(tour_id) references Tournament(tour_id),
primary key(user_id, tour_id));


create table equipment(
equip_id int auto_increment,
userid_owner int,
userid_rentee int,
EquipName varchar(50) NOT NULL,
address varchar(100),
rentamt numeric(10,2),
status varchar(255),
primary key(equip_id),
foreign key(userid_owner) references User(user_id),
foreign key(userid_rentee) references User(user_id));


NEW EDIT QUERIES:

alter table user add gender varchar(15);
alter table user drop height;
alter table user add height_in_feet int;
alter table user add height_in_inches int;
alter table user drop password;
alter table user add password varchar(100);
alter table user drop event_id;
alter table Event add user_id int;
alter table event add constraint foreign key(user_id) references user(user_id);
alter table Tournament add tour_sport varchar(50);
alter table tournament modify tour_time varchar(100);
alter table tournament modify tour_date varchar(100);
alter table tournament modify entryFee decimal(10,2);
alter table tournament add constraint foreign key(tour_owner) references user(user_id);
alter table user drop dob;
alter table user add balance numeric(10,2);
alter table tournament add tour_owner int;
alter table equipment add description varchar(255);
alter table user add latitude numeric(30,20);
alter table user add longitude numeric(30,20);