Conduct normalization analysis on CLYSTMS by listing the FDs and the highest nor

Question

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."

so far we have this done:

1.

FD's:-

Swimmer --> SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId,Main_CT_Id

Main_CT_Id --> Main_CT_Since

.

2.

Since This relation satifies all the normal forms upto BCNF. as there is no transitive and no non trivial dependencies in the relation.

3.

4.

The relation schema for the next 4 tables:

5

LevelHistory(LH_Id, SwimmerId, LevelId, StartDate, Comment)

Candidate Keys

[1] LH_Id, [2] SwimmerId, LevelId

Foreign Keys

[1] SwimmerId references Swimmer(SwimmerId), [2] LevelId references Level(LevelId).

Nullable Attributes

Comment

Notes

(1) A surrogate key, LH_Id, is created as the primary key. This is optional.

Normalization Analysis

FD:

Highest NF:

6

Coach(CoachId, FName, LName, Phone, Email)

Candidate Keys

(1) CoachId

Foreign Keys

Nullable Attributes

Notes

Normalization Analysis

FD:

Highest NF:

7

Meet(MeetID, Title, Date, StartTime, EndTIme, VenueId, CoachId)

Candidate Keys

[1] MeetId

Foreign Keys

[1] CoachId references Coach(CoachId), [2] VenueId references Venue(VenueId)

Nullable Attributes

Notes

Normalization Analysis

FD:

Highest NF:

8

Venue(VenueId, Name, Address, City, State, ZipCode, Phone)

Candidate Keys

[1] VenueId, [2] Name (likely), [3] {Address, City, State, ZipCode} (likely)

Foreign Keys

Nullable Attributes

Notes

Normalization Analysis

FD:

Highest NF:

:

Swimmer(SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)

FD's:-

Swimmer --> SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId,Main_CT_Id

Main_CT_Id --> Main_CT_Since

Highest Noraml Form:- 2 Normal Form

Explanation / Answer

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."

1 Swimmer(SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
FD's:- Swimmer --> SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId,Main_CT_Id
Main_CT_Id --> Main_CT_Since
Highest Noraml Form:- 2 Normal Form

2. Caretaker(CT_Id, FName, LName, Phone, Email)
FD:- CT_Id --> FName, LName, Phone, Email
Highest NF: BCNF

Since This relation satifies all the normal forms upto BCNF. as there is no transitive and no non trivial dependencies in the relation.

3. OtherCaretaker(OC_Id, SwimmerId, CT_Id, Since)
FD:- CT_Id --> Since
Highest Normal Form:- 1 Normal form

4. Level(LevelId, Level, Description)
FD:- LevelId, Level --> Description
Highest Normal Form:- BCNF

The relation schema for the next 4 tables:

5. LevelHistory(LH_Id, SwimmerId, LevelId, StartDate, Comment)
Candidate Keys-> [1] LH_Id, [2] SwimmerId, LevelId
Foreign Keys -> [1] SwimmerId references Swimmer(SwimmerId), [2] LevelId references Level(LevelId).
Nullable Attributes-> Comment
Notes-> (1) A surrogate key, LH_Id, is created as the primary key. This is optional.

Normalization Analysis
   FD: SwimmerId --> StartDate
   Highest NF: 2NF

6. Coach(CoachId, FName, LName, Phone, Email)
Candidate Keys-> (1) CoachId
Foreign Keys->
Nullable Attributes-> Email(optional)
Notes-> email may or may not be provided.
Normalization Analysis
   FD: CoachId --> FName, LName, Phone, Email
   Highest NF: BCNF

7. Meet(MeetID, Title, Date, StartTime, EndTIme, VenueId, CoachId)
Candidate Keys-> [1] MeetId
Foreign Keys-> [1] CoachId references Coach(CoachId), [2] VenueId references Venue(VenueId)
Nullable Attributes->
Notes->
Normalization Analysis
   FD: MeetID --> Title, Date, StartTime, EndTIme
   Highest NF: 2NF

8. Venue(VenueId, Name, Address, City, State, ZipCode, Phone)
Candidate Keys-> [1] VenueId, [2] Name (likely), [3] {Address, City, State, ZipCode} (likely)
Foreign Keys->
Nullable Attributes->
Notes-> We can break Address, City, State, ZipCode into another table where zipcode is candidate key.
Normalization Analysis
   FD: VenueId -> Name,Phone, ZipCode 2) ZipCode-> Address, City, State
   Highest NF: 3NF

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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