DDL_Commands.txt

● DATABASE NAME: IPL AUCTION
Create database Ipl_Auction;

● TABLE: TEAM
Create table Team(
    Team_Name varchar(100) not null,
    Full_Name varchar(100) not null,
    Tropies_Won int,
    Captain varchar(100) not null,
    Vice_Captain varchar(100),
    Home_Ground_Name varchar(100) not null,
    Purchase_Bag_Available int not null,
    Primary key(Team_Name)
);


● TABLE: PLAYER
Create table Player(
    PID int not null,
    Name varchar(100) not null,
    Age int not null,
    Height int not null,
    Nationality varchar(100) not null,
    State_Association varchar(100) not null,
    Batting_Style varchar(100) not null,
    Bowling_Style varchar(100) not null,
    Speciality varchar(100) not null,
    Base_Price int not null,
    IPL_Debut_Year int not null,
    Capped Boolean not null,
    Status varchar(100) not null,
    Type varchar(100) not null,
    Primary key(PID)
);


● TABLE: TEAM STAFF
Create table Team_Staff(
    Team_Name varchar(100) not null,
    Head_Coach varchar(100) not null,
    Batting_Coach varchar(100) not null,
    Bowling_Coach varchar(100) not null,
    Fielding_coach varchar(100) not null,
    Throwdown_Specialist varchar(100) not null,
    Physio varchar(100) not null,
    High_Performer_Analyst varchar(100) not null,
    Physical_Trainer varchar(100) not null,
    Team_Doctor varchar(100) not null,
    Primary key(Team_Name),
    Foreign key(Team_Name) references Team(Team_Name)
);


● TABLE: HOME GROUND
Create table Home_Ground(
    Name varchar(100) not null,
    Location varchar(100) not null,
    Capacity varchar(100) not null,
    Pitch_Type varchar(100) not null,
    Dimensions varchar(100) not null,
    Year_Of_Establishment int not null,
    Avg_First_Inning_Score float not null,
    Avg_Second_Inning_Score float not null,
    Curator_Name varchar(100) not null,
    Team_Name varchar(100) not null,
    Primary key(Team_Name),
    Foreign key(Team_Name) reference Team(Team_Name)
);


● TABLE: STATS
Create table Stats(
    PID int not null,
    Runs int,
    Matches_Played int,
    Strike_Rate double,
    Batting_Average double,
    Bowling_Average double,
    6s int,
    4s int,
    100s int,
    50s int,
    No_of_wickets int,
    No_of_catches int,
    No_of_Stumpings int,
    Primary key(PID),
    Foreign key(PID) references Player(PID)
);


● TABLE: AUCTION
Create table Auction(
    Date date not null,
    Time time not null,
    Location varchar(100) not null,
    Auctioneer_Name varchar(100) not null,
    Auctioneer_Age varchar(100) not null,
    Auctioneer_Nationality varchar(100) not null,
    Primary key(Auctioneer_Name)
);


● TABLE: USER
Create table User(
    ID int not null,
    Name varchar(100) not null,
    Gender varchar(100) not null,
    Age int not null,
    Favourite_Team varchar(100) not null,
    Favourite_Player varchar(100) not null,
    Primary key(ID)
);


● TABLE: OWNER
Create table Owner(
    ID int not null,
    Name varchar(100) not null,
    Team_Name varchar(100) not null,
    Primary key(ID),
    Foreign key(Team_Name) references Team(Team_Name)
);


● TABLE: TEAM PLAYED UNDER
Create table Team_Played_Under(
    PID int not null,
    Teams_Name varchar(100) not null,
    Primary Key(PID),
    Foreign key(PID) references Player(PID)
);


● TABLE: TROPIES WON
Create table Tropies_Won(
    Team_Name varchar(100) not null,
    Year int not null,
    Primary key(Year),
    Foreign key(Team_Name) references Team(Team_Name)
);


● TABLE: INTERNATIONAL RECORD
Create table International_Record(
    PID int not null,
    Test_Caps int,
    ODI_Caps int,
    T20_Caps int,
    Primary key(PID),
    Foreign key(PID) references Player(PID)
);


● TABLE: PLAYER LIST FOR AUCTION
Create table Players_List_For_Auction(
    PID int not null,
    Set_No int not null,
    2021_Set varchar(10) not null,
    Primary key(PID),
    Foreign key(PID) references Player(PID)
);


● TABLE: RETAINED PLAYER LIST
Create table Retained_Player_List(
    PID int not null,
    Team_Name varchar(100) not null,
    Salary int not null,
    Primary key(PID),
    Foreign key(PID) references Player(PID)
);


● TABLE: PREVIOUS STANDING
Create table Previous_Standing(
    Year int not null,
    Team_Name varchar(100) not null,
    Position int not null,
    Foreign key(Team_Name) references Team(Team_Name)
);


● TABLE: SPONSOR
Create table Sponsor(
    SID int not null,
    Name varchar(100) not null,
    Primary key(SID)
);


● TABLE: PLAYER SPONSOR
Create table Player_Sponsor(
    SID int not null,
    PID int not null,
    Primary key(PID),
    Foreign key(PID) references Player(PID),
    Foreign key(SID) references Sponsor(SID)
);


● TABLE: TEAM SPONSOR
Create table Team_Sponsor(
    SID int not null,
    Team_Name varchar(100) not null,
    Foreign key(Team_Name) references Team(Team_Name),
    Foreign key(SID) references Sponsor(SID)
);


● TABLE: TEAM RECORD
Create table Team_Record(
    Team_Name varchar(100) not null,
    Name varchar(100) not null,
    Stats varchar(100) not null,
    Primary key(Name),
    Foreign key(Team_Name) references Team(Team_Name)
);


● TABLE: PLAYER RECORD
Create table Player_Record(
    PID int not null,
    Name varchar(100) not null,
    Stats varchar(100) not null,
    Primary key(Name),
    Foreign key(PID) references Player(PID)
);


● TABLE: BUYS IN
Create table Buys_In(
    Team_Name varchar(100) not null,
    Money_Spent float not null,
    Primary Key(Team_Name),
    Foreign key(Team_Name) references Team(Team_Name)
);


● TABLE: AUCTIONED IN
Create table Auctioned_In(
    PID int not null,
    Status varchar(100) not null,
    Price int not null,
    Team_Name varchar(100) not null,
    Primary Key(PID),
    Foreign key(PID) references Player(PID)
);