Database Management Systems

HW 5

Due Friday, September 30

1. Design a relational database corresponding to the E-R diagram shown below. Use MS Visio.

2. A database for a collage football team would need to contain information about the players and coaches. A team member (player or coach) has an ID (T-ID), name (T-NAME) and phone (T-PHONE). In addition a coach has a salary (C-SALARY) and load (C-LOAD). Each player has a jersy number (P-JNUMBER) and level (freshman, sophomore, etc) (P-LEVEL). A player is able to play one or more positions (POS-NAME) and for each position played receives an integer rating (RATING). Each position is designated as either defensive or offensive (D-O). Multiple payers can play the same position. Finally each coach coaches one or more positions. However only one coach coaches any specific position.

a. What are the entities in this database and what are their attributes? What are their keys?

b. What are the relationships and their attributes? Indicate which relationships are 1:N, N:M, or 1:1.

c. Use Visio to create an entity-relationship (E-R) diagram for this database. Be sure to use subtyping as needed.

d. Determine five (5) non-trivial functional dependencies for this problem.

A functional dependency ( FD ) is a constraint between two sets of attributes in a relation from a database.

Given a relation R , a set of attributes X in R is said to functionally determine another attribute Y , also in R , (written X ? Y ) if, and only if, each X value is associated with precisely one Y value. Customarily we call X the determinant set and Y the dependent attribute . Thus, given a tuple and the values of the attributes in X , one can determine the corresponding value of the Y attribute. In simple words, if X value is known, Y value is certainly known.

-- JimSkon - 2011-09-25

Topic revision: r1 - 2011-09-25 - 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