# Difference: DBLab7Solutions ( vs. 1)

#### Revision 12013-11-27 - JimSkon

Line: 1 to 1
>
>
 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.

Copyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback