Conduct normalization analysis on CLYSTMS by listing the FDs and the highest nor
ID: 3825489 • Letter: C
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." the following 8 tables are already done, we just need the last ones at the bottom of the question. Thanks in advance!!
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
the following tables are incomplete:
9
Event(EventId, Title, StartTime, EndTime, MeetId, LevelId)
Candidate Keys
[1] EventId
Foreign Keys
[1] MeetId references Meet(MeetId), [2] LevelId references Level(LevelId).
Nullable Attributes
Notes
Normalization Analysis
FD:
Highest NF:
10
Participation(ParticipationId, SwimmerId, EventId, Committed, CommitTime, Participated, Result, Comment, CommentCoachId)
Candidate Keys
[1] ParticipationId, [2] SwimmerId, EventId.
Foreign Keys
[1] SwimmerId references Swimmer(SwimmerId), [2] EventId references Event(EventId), [3] CommentCoachId references Coach(CoachId)
Nullable Attributes
Committed, CommitTime, Participated, Result, Comment, CommentCoachId
Notes
(1) A surrogate key, ParticipationId, is created as the primary key. It is optional.
Normalization Analysis
FD:
Highest NF:
11
V_TaskList(VTL_Id, MeetId, Required, Description, Penalty, PenaltyAmt)
Candidate Keys
[1] VTL_Id, [2] MeetId
Foreign Keys
[1] MeetId references Meet(MeetId)
Nullable Attributes
Penalty, PenaltyAmt
Notes
[1] A surrogate key, VTL_Id, is created as the primary key.
Normalization Analysis
FD:
Highest NF:
12
V_Task(VT_Id, VTL_Id, Name, Comment, Num_V)
Candidate Keys
[1] VT_Id, [2] {VTL_Id, Name} (likely)
Foreign Keys
[1] VTL_Id references V_TaskList(VTL_Id)
Nullable Attributes
Comment (possibly)
Notes
[1] A surrogate key, VT_Id, is created as the primary key. [2] Num_V is not nullable and has a default value of 1.
Normalization Analysis
FD:
Highest NF:
13
Commitment(CommitmentId, CT_Id, VT_Id, CommitTime, Rescinded, RescindTime, CarriedOut, Comment, CommentCoachId)
Candidate Keys
[1] CommitmentId, [2] {CT_ID, VT_Id}
Foreign Keys
[1] CT_Id references Caretaker(CT_Id), [2] VT_Id references V_Task(VT_Id), [3] CommentCoachId references Coach(CoachId)
Nullable Attributes
Rescinded, RescindTime, CarriedOut, Comment, CommentCoachId
Notes
[1] A surrogate key, CommitmentId, is created as the primary key.
Normalization Analysis
FD:
Highest NF:
9
Event(EventId, Title, StartTime, EndTime, MeetId, LevelId)
Candidate Keys
[1] EventId
Foreign Keys
[1] MeetId references Meet(MeetId), [2] LevelId references Level(LevelId).
Nullable Attributes
Notes
Normalization Analysis
FD:
Highest NF:
10
Participation(ParticipationId, SwimmerId, EventId, Committed, CommitTime, Participated, Result, Comment, CommentCoachId)
Candidate Keys
[1] ParticipationId, [2] SwimmerId, EventId.
Foreign Keys
[1] SwimmerId references Swimmer(SwimmerId), [2] EventId references Event(EventId), [3] CommentCoachId references Coach(CoachId)
Nullable Attributes
Committed, CommitTime, Participated, Result, Comment, CommentCoachId
Notes
(1) A surrogate key, ParticipationId, is created as the primary key. It is optional.
Normalization Analysis
FD:
Highest NF:
11
V_TaskList(VTL_Id, MeetId, Required, Description, Penalty, PenaltyAmt)
Candidate Keys
[1] VTL_Id, [2] MeetId
Foreign Keys
[1] MeetId references Meet(MeetId)
Nullable Attributes
Penalty, PenaltyAmt
Notes
[1] A surrogate key, VTL_Id, is created as the primary key.
Normalization Analysis
FD:
Highest NF:
12
V_Task(VT_Id, VTL_Id, Name, Comment, Num_V)
Candidate Keys
[1] VT_Id, [2] {VTL_Id, Name} (likely)
Foreign Keys
[1] VTL_Id references V_TaskList(VTL_Id)
Nullable Attributes
Comment (possibly)
Notes
[1] A surrogate key, VT_Id, is created as the primary key. [2] Num_V is not nullable and has a default value of 1.
Normalization Analysis
FD:
Highest NF:
13
Commitment(CommitmentId, CT_Id, VT_Id, CommitTime, Rescinded, RescindTime, CarriedOut, Comment, CommentCoachId)
Candidate Keys
[1] CommitmentId, [2] {CT_ID, VT_Id}
Foreign Keys
[1] CT_Id references Caretaker(CT_Id), [2] VT_Id references V_Task(VT_Id), [3] CommentCoachId references Coach(CoachId)
Nullable Attributes
Rescinded, RescindTime, CarriedOut, Comment, CommentCoachId
Notes
[1] A surrogate key, CommitmentId, is created as the primary key.
Normalization Analysis
FD:
Highest NF:
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
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.