Database Lab 7

Solution

A database for a football team would need to contain information about the players and coaches. In particular, the database would contain thename( P-NAME) and phone number ( P-PHONE) for each player and similarly the name ( C-NAME) and phone number ( C-PHONE) for each coach. IN addition each player is able to play one or more positions ( POS-NAME) and for each position played receives a rating ( RATING). Each position is designated as either defensive or offensive ( D-O). Finally each coach coaches one or more positions. However only one coach coaches any specific position.

A proposed table for this is:

TEAM (P-NAME, P-PHONE, C-NAME, C-PHONE, POS-NAME, RATING, D-O)

A. Propose what you believe the key should be for the table above. Why?

| P-NAME, POS-NAME |

All other fields are determine by one, or both, of these.

B. Find and list at least 5 functional dependancies. At least one dependancy will depend on more than one attribute.

  1. P-NAME -> P-PHONE
  2. C-NAME -> C-PHONE
  3. P-NAME,POS-NAME -> RATING
  4. POS-NAME ->D-O
  5. POS-NAME ->C-NAME
C. Find and descripe clearly at least two examples of each the the following types of possible update anomaly's :
  1. Insertion anomalies
    1 - If you insert a new player with a coach, you could give the coach a different phone then they have elsewhere
    2 - You cannot insert a coach without inserting a player
  2. Deletion anomalies
    1- If you delect the last player associated with a coach, you delete the coach
    2- If you wish to delete a coach, you may delete a player also
  3. Modification anomalies
    1 - If a player phone changes, you could miss changing it for all instances of the player
    2 - If a coach changes position, you must change the position for every instance of the coach.
For each explain why it is an anomaly, and what problem results.
Topic revision: r1 - 2013-11-27 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback