Question

Use SQL commands to create a table for the following ER diagram.

GDate MatchResult Stadium Score2 Score1 PlaysAt Match GType SName City Capacity Team1 Sld Gld Team2 Goals Plays Cards Time CName Team Plays Confederation Cld Population Gets ubsitute BelongsTo Continent FirstName 23 Height record time PJname Player PName MiddleName Club BirthDate LastName Position PNo Weight

Answer 1

Create Table Match(

Gid int NOT NULL,

GType varchar(100) NOT NULL,

MatchResult varchar(100),

Score1 int,

Score2 int,

Team1 varchar(100),

Team2 varchar(100),

Primary Key(Gid));

Create Table PlaysAt(

Gid int NOT NULL,

Sid int NOT NULL,

GDate date,

Primary Key(Gid,Sid),

Foreign Key(Gid) References Match(Gid),

Foreign Key(Sid) References Stadium(Sid));

Create Table Stadium(

Sid int NOT NULL,

SName varchar(100),

City varchar(100),

Capacity int,

Primary Key(Sid));

Create Table Team(

Cid int NOT NULL,

CName varchar(100) NOT NULL,

Confederation varchar(100),

Population int,

Continent varchar(50),

Primary key(Cid,CName));

Create Table Plays(

Gid int NOT NULL,

PNo int NOT NULL,

Goals int,

Cards varchar(50),

Time time,

Primary Key(Gid,Sid),

Foreign Key(Gid) References Match(Gid),

Foreign Key(PNo) References Player(PNo));

Create Table Player(

PNo int NOT NULL,

FirstName varchar(50),

MiddleName varchar(50),

LastName varchar(50),

PJName varchar(50),

BirthDate date,

Weight decimal,

Height decimal,

Club varchar(100),

Position varchar(100),

Primary Key(PNo));

Create Table GetSubstitutes(

Cid int NOT NULL,

PNo int NOT NULL,

Record_time time,

Primary Key(Cid,PNo),

Foreign Key(PNo) references Player(PNo));

Do ask if any doubt. Please upvote.