There are 4 relations in the schema. Each are described below along with their i
ID: 3715600 • Letter: T
Question
There are 4 relations in the schema. Each are described below along with their integr
ity
constraints.
?
Player (
playerID:number(9),
name:varchar2(50),
position:varchar2(10),
height:number(3),
weight:number(3),
team:varchar2(30)
)
oEach Player is assigned a unique playerID.
oThe positionof a player can either be Guard,Center or Forward.
oThe team field is a foreign key to Team.
oheight and weight can be NULL, but no other column may be.
?Team (
name:varchar2(30),
city:varchar2(20)
)
oEach Team has a unique name associated with it.
oThere can be multiple teams from the same city.
o No column may be NULL
?Game (
gameID:number(9),
homeTeam:varchar2(30),
awayTeam:varchar2(30),
homeScore:number(3),
awayScore:number(3),
)
oEach Game has a unique gameID.
oThe fields homeTeam and awayTeam are foreign keys to Team.
oTwo teams may play each other multiple times each season.
oThere is an integrity check to ensure homeTeam and awayTeam are different.
oBoth homeScore and awayScore must be non-negative values.
oNo column may be NULL.
?GameStats (
playerID:number(9),
gameID:number(9),
points:number(3),
assists:number(3),
rebounds:number(3),
)
oGameStats records the performance statistics of a player within a game.
oA player may not play in every game, in which case he/she will not have his/her statistics recorded for that game.
ogameID is a foreign key to Game.
o playerID is a foreign key to Player.
oNo column may be NUll
Explanation / Answer
If you have any doubts, please give me comment...
CREATE TABLE Team(
name VARCHAR(30) NOT NULL PRIMARY KEY,
city VARCHAR(20)
);
CREATE TABLE Player(
playerID INT(9) NOT NULL PRIMARY KEY,
name VARCHAR(50),
position ENUM('Guard', 'Center', 'Forward'),
height INT(3),
weight INT(3),
team VARCHAR(30),
FOREIGN KEY(team) REFERENCES Team(name)
);
CREATE TABLE Game(
gameID INT(9) NOT NULL PRIMARY KEY,
homeTeam VARCHAR(30),
awayTeam VARCHAR(30),
homeScore INT(3),
awayScore INT(3),
FOREIGN KEY(homeTeam) REFERENCES Team(name),
FOREIGN KEY(awayTeam) REFERENCES Team(name)
CHECK(homeTeam<>awayTeam),
CHECK(homeScore>=0),
CHECK(awayScore>=0)
);
CREATE TABLE GameStats(
playerID INT(9),
gameID INT(9),
points INT(3),
assists INT(3),
rebounds INT(3),
PRIMARY KEY(playerID, gameID),
FOREIGN KEY(playerID) REFERENCES Player(playerID),
FOREIGN KEY(gameID) REFERENCES Game(gameID)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.