/*
*@author:wangzhesi
*  053597
*/
Step1:
We map all the entities types into tables and define their primary keys PKs and alternate keys AKs. Note that Colors in TEAM is a multi-valued attribute and as such will be handled in Step 7.

S1  COACH(Name,Age)
S1  PK(Name)

S1  PLAYER(Name,Age)
S1  PK(Name)

S1  TEAM(Name,Colors)
S1  PK(Name)

S1  GAME(Number,Date,Score,Time)
S1  PK(Number)

S1  STADIUM(Name,Size,Location)
S1  PK(Name)

Step2:
We do not take any action, since there are no weak entities satisfying the 1:1 relationship constraint.

Step3:
We create a table for each remaining weak entity that was not handled in step 2.But we do not take any action, since there are no weak entities satisfying the 1:N relationship constraint.

Step4:
We consider the binary 1:1 relationships.

S1  COACH(Name,Age,Team)
S1  PK(Name)
S4  FK(Team) -> TEAM(Name)

Step5:
We consider 1:N relationships. Here we have only one such relationships in our example. In each case, we choose the table that corresponds to the N-side of the relationship and collapse the relationship in the selected table including all the attributes of the relationship as columns in the table. 

S1  PLAYER(Name,Age,Playteam)
S1  PK(Name)
S5  FK(Playteam) -> TEAM(Name)

We consider the N-arys relationship.Here PLAYSWITH is such a relationship.We create a new table to store this relationship.It has three attributes,namely,Host,Visitor and Game, to which we include a foreign key referring to the primary key of HOST Team,VISITOR Team and Game.

S5  PLAYSWITH(Host,Visitor,Game);
S5  PK(Host,Visitor,Game)
S5  FK(Host) -> TEAM(Name)
S5  FK(Visitor) -> TEAM(Name)
S5  FK(Game) -> GAME(Number)

Step6:
We consider the M:N relationships. In our example, PRACTICE is such a relationship. We create a new table to store this relationship and its attribute, namely Date. As above, the relationship is expressed using foreign keys. For each participating table, in our example, TEAM and STADIUM, we include a foreign key referring to their primary keys, Name of TEAM and Name of STADIUM. 

S6  PRACTICE(Team,Stadium,Date)
S6  PK(Team,Stadium)
S6  FK(Team) -> TEAM(Name)
S6  FK(Stadium) -> STADIUM(Name)

Step7:
We consider all multi-value attributes. In our example, we have one multi-valued attribute, namely, Colors in TEAM. We store such an attribute as a separate table. Colors, whose rows correspond to each of the values of the multi-valued attribute. In order to relate these values to the originating table, we include the primary key of the originating table, in our case, TEAM, as a foreign key in Colors. 

S7  COLORS(Colors,Team)
S7  PK(Colors,Team)
S7  FK(Team) -> TEAM(Name)

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Let us conclude by listing the entire relational schema produced above. It is worth recalling here that PK, AK, and FK correspond to PRIMARY KEY, UNIQUE and FOREIGN KEY REFERENCES statements, respectively, in SQL. 

S1  TEAM(Name,Colors)
S1  PK(Name)

S1  GAME(Number,Date,Score,Time)
S1  PK(Number)

S1  STADIUM(Name,Size,Location)
S1  PK(Name)

S1  COACH(Name,Age,Team)
S1  PK(Name)
S4  FK(Team) -> TEAM(Name)

S1  PLAYER(Name,Age,Playteam)
S1  PK(Name)
S5  FK(Playteam) -> TEAM(Name)

S5  PLAYSWITH(Host,Visitor,Game);
S5  PK(Host,Visitor,Game)
S5  FK(Host) -> TEAM(Name)
S5  FK(Visitor) -> TEAM(Name)
S5  FK(Game) -> GAME(Number)

S6  PRACTICE(Team,Stadium,Date)
S6  PK(Team,Stadium)
S6  FK(Team) -> TEAM(Name)
S6  FK(Stadium) -> STADIUM(Name)

S7  COLORS(Colors,Team)
S7  PK(Colors,Team)
S7  FK(Team) -> TEAM(Name)