Step 1: We mapped our regular entity types with relations. For example, we have
Players(Name, Age, playerID, Height, Weight, Tnum) stats in Entity Games.
Step 2: Since our 4 entities are all regular types, we do not have weak entity types.
Step 3: We do not have 1:1 relationship types in our entity setup.
Step 4: For all 1:n relationships, we used foreign keys approach to relate each other.
Step : We only have one M:N relationship which is Players entities to Games entities.
Step 6 - Step 9: We do not have such relationships.
Relation Name ER diagram components
Players E(Players)+R(statsin)+R(membersof)
Games E(Games)+R(Occursin)
Teams E(Teams)+R(Play)
Season E(Season)
Statsin R(Statsin)
(2) Database Schema:
Create table Players (
playerID VARCHAR(50) PRIMARY KEY,
Name VARCHAR(50) ,
Age VARCHAR(20),
Height DECIMAL(10,2) ,
Weight DECIMAL(10,2)
TNum INT );
Create table Teams (
TName VARCHAR(50),
City VARCHAR(50),
Conference VARCHAR(50),
TeamID VARCHAR(50),
S_year INT,
Foreign key(TeamID) references Players(TNum)
);
Create table Games (
GameID VARCHAR(50) PRIMARY KEY,
Date VARCHAR(50),
away_id VARCHAR(50),
home_id VARCHAR(50),
ht_score INT,
at_score INT,
Foreign key(away_id) references Teams(TeamID),
Foreign key(home_id) references Teams(TeamID)
);
Create table StatsIn (
Point Decimal(10,2),
Rebound Decimal(10,2),
Assist Decimal(10,2),
p_ID INT,
Foreign key(p_ID) references Players(playerID)
);
Create table Season (
Year INT,
date_start VARCHAR(50),
date_end VARCHAR(50),
Foreign key(Year) references Teams(S_year)
);