Difference: DBLab7Solutions ( vs. 1)

Revision 12013-11-27 - JimSkon

Line: 1 to 1
Added:
>
>
META TOPICPARENT name="Lab7DB2013"

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.
 
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