12) The following is an attempt to keep track of touring bands. However, it is n

Question

12) The following is an attempt to keep track of touring bands. However, it is not assembled very well. Your task is to: . identify the current normal form of that relation specify why it is in that normal form . move the relation to third normal form specify the keys of the 3NF relations (use standardized form notation) Use meaningful relation names. You do not have to specify the functional dependencies of the intermediate or final relations. Each sub-question deals with independent data a) SHOW (SHOW-ID, SHOW-DATE, LOCATION, BAND-ID, BAND-NAME) SHOW-ID SHOW-DATE SHOW-ID LOCATION SHOW-ID BAND-D BAND-ID BAND-NAME b] TOURS (BAND-ID, BAND-NAME, (TOUR-ID, TOUR-NAME, REVENUE )) BAND-ID BAND-NAME TOUR-ID TOUR-NAME BAND-ID, TOUR-ID REVENUE c] ATTENDANCE (BAND-ID, BAND-NAME. (TOUR-ID, TOUR-NAME, ( SHOW-ID, LOCATION, ATTENDANCE))) BAND-ID BAND-NAME TOUR-ID TOUR-NAME SHOW-ID LOCATION BAND-ID. TOUR-ID, SHOW-ID ATTENDANCE

Explanation / Answer

• First move the relation to 2NF as follows:

[b] TOURS(BAND-ID, BAND-NAME, TOUR-ID,TOUR-NAME,REVENUE )

can be broken into two relations as:

BAND(BAND-ID, BAND-NAME)

BAND-ID -> BAND-NAME

TOUR(BAND-ID, TOUR-ID, TOUR-NAME, REVENUE )
[both columns combined as primary key]

TOUR_ID -> TOUR-NAME
BAND-ID, TOUR-ID -> REVENUE

[c] ATTENDANCE(BAND-ID, BAND-NAME, (TOUR-ID,TOUR-NAME,(SHOW-ID,LOCATION, ATTENDANCE)))

can be broken into three relations as:

BAND(BAND-ID, BAND-NAME)
BAND-ID -> BAND-NAME

TOUR(TOUR-ID, TOUR-NAME,)
TOUR_ID -> TOUR-NAME

SHOW(BAND-ID, TOUR-ID, SHOW-ID , LOCATION, ATTENDANCE)
SHOW-ID -> LOCATION
BAND-ID, TOUR-ID, SHOW-ID -> ATTENDANCE

Move relations to 3 NF :

[a] Remove transitive dependencies(last two rules):

SHOW(SHOW-ID,SHOW-DATE,LOCATION,BAND-ID,BAND-NAME)

Can be broken into:
SHOW(SHOW-ID,SHOW-DATE,LOCATION,BAND-ID)
SHOW_ID -> SHOW-DATE
SHOW_ID -> LOCATIN
SHOW_ID -> BAND-ID

BAND(BAND_ID,BAND-NAME)

BAND_ID -> BAND_NAME

[b]

BAND(BAND-ID, BAND-NAME)
BAND-ID -> BAND-NAME

TOUR(BAND-ID, TOUR-ID, REVENUE )
BAND-ID, TOUR-ID -> REVENUE

TOUR1(TOUR-ID, TOUR-NAME)
TOUR_ID -> TOUR-NAME

[c]

BAND(BAND-ID, BAND-NAME)
BAND-ID -> BAND-NAME

TOUR(TOUR-ID, TOUR-NAME,)
TOUR_ID -> TOUR-NAME

SHOW(BAND-ID, TOUR-ID, SHOW-ID , ATTENDANCE)
BAND-ID, TOUR-ID, SHOW-ID -> ATTENDANCE

LOC(SHOW-ID,LOCATION)
SHOW-ID -> LOCATION

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.