CSC 240                                               Database Systems                               Fall 2005                               Project Database Design

In Phase I (Due Oct 20), I am interested in: 1) tables; 2) their attributes; 3) their primary key; 4) their relationships (with cardinality/connectivity and foreign key); 5) explanations of any data redundancies in your design.

I have said that this could be done either graphically (Visio or Access) or textually, or a combination. The below is a partial example of a textual description. (primary keys are underlined) for a different project (done in Word):

· Recording

o RecordingID

o SongID       

o YearRecorded

o Genre

o Producer

o Company

o Copyrights (e.g. BMI)

o Version (live, dance, ...)

o Relationships:       M:1 to Song (FK SongID)

· Song

o songID

o Title

o Writer

o Year Written

o Relationships: 1:M to Recording

· CD

o CDID

o CD Title

o Price  (if selling CD’s too)

o FileType

o FileSize

o AvailableDate

o Relationships: M:M to Recording

· CDRecording (bridge)

o CDID

o RecordingID

o Track#

o Relationships:       M:1 to CD (FK CDID)

M:1 to Recording (FK RecordingID)

· Customer

o CustomerID

o LastName

o FirstName

o AgeBracket (e.g. 8-12, 13-16, 17-19, 20-24, 25-30, 31-35, 36-40, 41-50, 51-60, above60)

o Gender

o City

o State

o Zip

o PreferredPaymentMethod

o PreferredCreditCard

o Relationships:       M:M to Recording

etc

Violations of 3NF in this design:  Zip è City; Zip è State  - pretty standard violation – ease of retrieval is more important here because access is frequent and changes are infrequent.