QUERIES THROUGHOUT USED, TO MODEL
THE CRIC-IT
-create database Cric_It
-create table Classification
(
C_id int not null,
C_name varchar(50),
constraint pk_C_id primary key(C_id),
)
-create table Series
(
S_id int not null,
S_name varchar(50),
C_id int,
constraint fk_SeriesClassification_id foreign key(C_id) references Classification(C_id),
)
-create table Ground
(
G_id int not null,
G_name varchar(50),
G_country varchar(50),
G_city varchar(50),
G_capacity varchar(50),
constraint pk_G_id primary key(G_id),
)
-create table Pitch
(
P_id int not null,
P_number varchar(10),
P_type varchar(50),
G_id int,
constraint pk_P_id primary key(P_id),
constraint fk_GroundPitch_id foreign key(G_id) references Ground(G_id),
)
-create table Weather
(
W_id int not null,
W_type varchar(30),
W_temp varchar(10),
constraint pk_W_id primary key(W_id),
)
-create table Batting_Style
(
BtStl_id int not null,
BtStl_style varchar(50),
constraint pk_BtStl_id primary key(BtStl_id),
)
-create table Bowling_Style
(
BowlStl_id int not null,
BowlStl_Style varchar(30),
constraint pk_BowlStl_id primary key(BowlStl_id),
)
-create table Specialization
(
Splzn_id int not null,
Splzn_Specialization varchar(30),
constraint pk_Splzn_id primary key(Splzn_id),
)
-create table Match_Closure
(
MC_id int not null,
MC_reason varchar(30),
constraint pk_MC_id primary key(MC_id),
)
-create table Innings_Closure
(
IC_id int not null,
IC_reason varchar(30),
constraint pk_IC_id primary key(IC_id),
)
-create table Match
(
M_id int not null,
M_T1_captain varchar(20),
M_T2_captain varchar(20),
M_T1_wicketKeeper varchar(20),
M_T2_wicketKeeper varchar(20),
M_TossWonBy varchar(10),
P_id int,
W_id int,
MC_id int,
constraint pk_M_id primary key(M_id),
constraint fk_MatchPitch_id foreign key(P_id) references Pitch(P_id),
constraint fk_MatchWeather_id foreign key(W_id) references Weather(W_id),
constraint fk_MatchClosure_id foreign key(MC_id) references Match_Closure(MC_id),
)
-create table Team
(
T_id int not null,
T_name varchar(20),
T_ranking varchar(10),
T_status varchar(20),
T_level varchar(10),
constraint pk_T_id primary key(T_id),
)
-alter table Series
add constraint pk_S_id primary key(S_id)
-create table Fixtures
(
F_id int not null,
F_date varchar(15),
F_time varchar(10),
W_id int,
G_id int,
S_id int,
M_id int,
constraint pk_F_id primary key(F_id),
constraint fk_ScheduleWeather foreign key(W_id) references Weather(W_id),
constraint fk_ScheduleGround foreign key(G_id) references Ground(G_id),
constraint fk_ScheduleSeries foreign key(S_id) references Series(S_id),
constraint fk_ScheduleMatch foreign key(M_id) references Match(M_id),
)
-create table Innings
(
Inns_id int not null,
Inns_number int,
Inns_StartTime varchar(10),
Inns_EndTime varchar(10),
IC_id int,
M_id int,
constraint pk_Inns_id primary key(Inns_id),
constraint fk_InningsClosure_id foreign key(IC_id) references Innings_Closure(IC_id),
constraint fk_MatchInnings_id foreign key(M_id) references Match(M_id),
)
-create table Innings_Batting
(
Inns_Bat_id int not null,
Inns_id int,
constraint pk_Inns_Bat_id primary key(Inns_Bat_id),
constraint fk_BattingInnings foreign key(Inns_id) references Innings(Inns_id),
)
-create table Innings_Bowling
(
Inns_Bowl_id int not null,
Inns_id int,
constraint pk_Inns_Bowl_id primary key(Inns_Bowl_id),
constraint fk_BowlingInnings foreign key(Inns_id) references Innings(Inns_id),
)
-create table Overs
(
O_id int not null,
O_number varchar(5),
Inns_id int,
Inns_Bowl_id int,
constraint pk_O_id primary key(O_id),
constraint fk_TotalInningsOver_id foreign key(Inns_id) references Innings(Inns_id),
constraint fk_BowlinInningsOver_id foreign key(Inns_Bowl_id) references
Innings_Bowling(Inns_Bowl_id),)
-create table Extras
(
E_id int not null,
constraint pk_E_id primary key(E_id),
)
-create table Dot
(
D_id int not null,
constraint pk_D_id primary key(D_id),
)
-create table Dismissal
(
Dsmsl_id int not null,
constraint pk_Dsmsl_id primary key(Dsmsl_id),
)
-create table Runs
(
R_id int not null,
R_type varchar(10),
constraint pk_Runns_id primary key(R_id),
)
-create table Boundaries
(
Bndrs_id int not null,
Bndrs_Type varchar(10),
constraint pk_Boundary_id primary key(Bndrs_id),
)
-create table Ball
(
Ball_id int not null,
Ball_number int,
O_id int,
Inns_id int,
E_id int,
D_id int,
Dsmsl_id int,
R_id int,
Bndrs_id int,
constraint pk_Ball_id primary key(Ball_id),
constraint fk_OverBalls_id foreign key(O_id) references Overs(O_id),
constraint fk_InningBalls_id foreign key(Inns_id) references Innings(Inns_id),
constraint fk_ExtrasPerBall_id foreign key(E_id) references Extras(E_id),
constraint fk_DotBall_id foreign key(D_id) references Dot(D_id),
constraint fk_DismissalBall_id foreign key(Dsmsl_id) references Dismissal(Dsmsl_id),
constraint fk_RunsPerBall_id foreign key(R_id) references Runs(R_id),
constraint fk_BoundaryBall_id foreign key(Bndrs_id) references Boundaries(Bndrs_id),
)
-create table Player
(
Plyr_id int not null,
Plyr_FullName varchar(30),
Plyr_Date_Of_Birth varchar(15),
Plyr_Place_Of_Birth varchar(30),
Plyr_Nick varchar(15),
Plyr_height varchar(10),
Plyr_education varchar(30),
BtStl_id int,
BowlStl_id int,
Splzn_id int,
constraint pk_Plyr_id primary key(Plyr_id),
constraint fk_PlayerBattingStyle_id foreign key(BtStl_id) references
Batting_Style(BtStl_id),
constraint fk_PlayerBowlingStyle_id foreign key(BowlStl_id) references
Bowling_Style(BowlStl_id),
constraint fk_PlayingRole_id foreign key(Splzn_id) references Specialization(Splzn_id),
)
-create table Team_Player
(
T_id int,
Plyr_id int,
constraint fk_Team_id foreign key(T_id) references Team(T_id),
constraint fk_Player_id foreign key(Plyr_id) references Player(Plyr_id),
)
-create table Fixture_Team
(
F_id int,
T_id int,
constraint fk_FixtureTeam_id foreign key(T_id) references Team(T_id),
constraint fk_Fixture_id foreign key(F_id) references Fixtures(F_id),
)
-