Complete the following exercises, normalizing the specified schemas. (solutions)

  1. The Super Baseball League wants to keep track of information about its players, its teams, and the minor league teams (which we will call minor league “clubs” to avoid using the word “team” twice). Minor league clubs are not part of the Super Baseball League but players train in them with the hope of eventually advancing to a team in the Super Baseball League. The intent in this problem is to keep track only of the current team on which a player plays in the Super Baseball League. However, the minor league club data must be historic and include all of the minor league clubs for which a player has played. Team names, minor league club names, manager names, and stadium names are assumed to be unique, as, of course, is player number.

    Design a well structured relational database for this Super Baseball League environment using the data normalization technique. Progress from first to second normal form and then from second to third normal form justifying your design decisions at each step based on the rules of data normalization. The attributes and functional dependencies in this environment are as follows (Minicase 7.2):

    Attributes
    Player Number, Player Name, Player Age, Team Name, Manager Name, Stadium Name, Minor League Club Name, Minor League Club City, Minor League Club Owner, Minor League Club Year Founded, Start Date, End Date, Batting Average
    Functional Dependencies
    • Player Number \(\rightarrow\) Player Name
    • Player Number \(\rightarrow\) Age
    • Player Number \(\rightarrow\) Team Name
    • Player Number \(\rightarrow\) Manager Name
    • Player Number \(\rightarrow\) Stadium Name
    • Minor League Club Name \(\rightarrow\) City
    • Minor League Club Name \(\rightarrow\) Owner
    • Minor League Club Name \(\rightarrow\) Year Founded
    • Team Name \(\rightarrow\) Manager Name
    • Team Name \(\rightarrow\) Stadium Name
    • Player Number, Minor League Club Name \(\rightarrow\) Start Date, End Date, Batting Average
    First Normal Form (1NF)
    Player Data
    Player Number,* Player Name, Age, Team Name, Manager Name, Stadium Name, Minor League Club Name,* City, Owner, Year Founded, Start Date, End Date, Batting Average

    Note: Team Name is not included in the primary key. If it is included, then the primary key (the attributes Player Number, Team Name, and Minor League Club Name) is not the minimal set of attributes that uniquely identify a tuple – i.e., a primary key with only the Player Number and Minor League Club Name is still sufficient to uniquely identify tuples.

    Second Normal Form (2NF)
    Player
    Player Number,* Player Name, Age, Team Name, Manager Name, Stadium Name
    Minor League Club
    Minor League Club Name,* City, Owner, Year Founded
    History
    Player Number,* Minor League Club Name,* Start Date, End Date, Batting Average
    Third Normal Form (3NF)
    Player
    Player Number,* Player Name, Age, Team Name
    Team
    Team Name,* Manager Name, Stadium Name
    Minor League Club
    Minor League Club Name,* City, Owner, Year Founded
    History
    Player Number,* Minor League Club Name,* Start Date, End Date, Batting Average

  2. The following functional dependencies exist among a set of attributes:

    \[\begin{align} A & \rightarrow D, E, F\\ B & \rightarrow C, D\\ D & \rightarrow F\\ A, B & \rightarrow C, D, E, F, G \end{align}\]

    What is the relational schema in first, second, and third normal forms?

    Because names of relations cannot be inferred, they are omitted in favor of listing the attributes that appear in each relation. For example, there is a single relation in First Normal Form (1NF) but three relations in Second Normal Form (2NF).

    First Normal Form (1NF)
    A,* B,* C, D, E, F, G
    Second Normal Form (2NF)
    A,* D, E, F
    B,* C, D
    A,* B,* G
    Third Normal Form (3NF)
    A,* D, E
    D,* F
    B,* C, D
    A,* B,* G

  3. The following functional dependencies exist among a set of attributes:

    \[\begin{align} A & \rightarrow B, C, D\\ D & \rightarrow E, F\\ E & \rightarrow F \end{align}\]

    What is the relational schema in first, second, and third normal forms?

    First Normal Form (1NF)
    A,* B, C, D, E, F
    Second Normal Form (2NF)
    A,* B, C, D, E, F
    Third Normal Form (3NF)
    A,* B, C, D
    D,* E
    E,* F