Homework Three
Normalization

Out April 2, Due April 11

You have been asked to help design a database for the UMBC Bungi Jumping Club. The club has many members, each with a member number (MID), name (MNAME), address (MADDRESS) and jumping ability (JA). The club holds meetings on various dates (DATE) at different locations (LOCATION). Attendance is recorded so that the club can tell which members attended which meetings.

Each meeting has a single speaker and sometimes a speaker comes back to speak at another meeting. A record is kept for each meeting of the speaker's name (SNAME), address (SADDRESS) and phone number (SPHONE).

One of the students in the club, E.F. Sodd, suggests storing all of the information in a single relation: 


     UMBCJC(MID,MNAME,MADDRESS,JA,DATE,SNAME,
            SADDRESS,SPHONE,LOCATION,ATTENDED)
For this database,

  1. What are the functional dependencies in UMBCJC? Write down any domain assumptions you are making.

  2. What should the key of this relation be?

  3. Convert this to a set of relations in Second Normal Form.

  4. Are these relations in Third Normal Form? If not, further decompose them to a minimal number of 3NF relations.

  5. What advantages does this schema have over Sodd's original proposal.

  6. Sketch an ER diagram for this problem. Does this suggest a different relational schema? If so, give it.