Database Conduct normalization analysis on CLYSTMS by listing the FDs and the hi

Question

Database

Conduct normalization analysis on CLYSTMS by listing the FDs and the highest normal form of each relation. Complete the task by filling in the row "Normalization Analysis."   

The relation schema:

Clipboard Font Paragraph 5 LevelHistory(LH ld. Swimmerld. Levelld. StartDate. Comment) Candidate Keys [1] LH ld, [2] Swimmerld, Levelld [1] Swimmerld references swimmer (Swimmerld), [2] Levelld references Foreign Keys Level Levelld). Nullable Attributes Comment Notes (1) A surrogate key, LH ld, is created as the primary key. This is optional Normalization Analysis FD Highest NF 6 Coach (Coachld, FName, LName, Phone, Email) poachld Candidate Keys Foreign Keys Nullable Attributes Notes Normalization Analysis FD Highest NF 7 Meet(MeetID, Title. Date. StartTime. EndTIme, Venueld. Coachld Candidate Keys [11 Meetid [11 Coachld references Coach(Coachld). I21 Venueld references Foreign Keys Venue (Venueld) Nullable Attributes Notes Normalization Analysis FD Highest NF. 8 Venue (Venueld, Name, Address, City, State, ZipCode, Phone) Candidate Keys [1] Venueld, 12] Name (likely), [3] {Address, City, State, ZipCode (likely) Nullable Attributes Notes Normalization Analysis FD Highest NF.

Explanation / Answer

Hi,

Please find below the answer-

Ans 5 -
FDs-
LH_ID--> StartDate
LH_ID--> Comment
{SwimmerId, LevelId }--> StartDate
{SwimmerId, LevelId }--> Comment
LevelId --> StartDate

Highest NF-
First Normal Form.
Explanation :
This relation has partial dependency on its composite primary key hence

it does not satisfy the second normal form. The table has a composite

primary key {SwimmerId, LevelId}. We can see that the attribute

Start_Date can be determined by the LevelId itself and has no dependency

on SwimmerId. This means there is partal dependency of the non-prime

attributes on its primary key.

Ans 6-
FDs-
CoachId--> Fname
CoachId--> LName
CoachId--> Phone
CoachId--> Email
Highest Normal form- 0
This relation does not satisfy normal form 1. This is beacuse all of its

attributes will contain multiple values. Example- a typical row will look

like-
coachid FName LName Phone Email
101   abc, def,mno mmm, lll, jjj 101, 201, 809 ppp, nnn, ggh

Ans 7 -
FDs-
MeetId --> Title
MeetId --> Data
MeetId --> StartTime
MeetId --> EndTime
Highest Normal Form-
Third normal form

Ans 8-

FDs
VenueId --> Name
VenueId --> Address
VenueId --> City
VenueId --> State
VenueId --> Zipcode
VenueId --> Phone
Highest Normal Form - 2nd normal form
Explanation: This is because if we consider the key as

{Address,city,state,zipcode}, we can see that attribute name is

independent of all of the attributes that forms the key. The attribute

can thus be determined by VenueId itself. Hence, we can see that there is

a transitive dependency here.

Ans 9-

FDs-
EventId --> Title
EventId --> StartTime
EventId --> EndTime
Highest NF- This is in Third normal form.

Ans 10 -

FDs-
ParticipationID --> Committed
ParticipationID --> CommitTime
ParticipationID --> Participated
ParticipationID --> Result
ParticipationID --> Comment

Highest NF - Second NF
Explanation : This is because if we consider composite key {Swimmer_ID,EventId}, then we can see that the ParticipationID --> Result. Hence there is a transitive dependency here and two non prime attributes can determine each other which is against third normal form

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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