Database Lab 7


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:


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


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
  4. POS-NAME ->D-O
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.

This topic: Main > WebHome > CSC3032DatabaseManagement2013 > Lab7DB2013 > DBLab7Solutions
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