Conduct normalization analysis on CLYSTMS by listing the FDs and the highest nor
ID: 3816676 • 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 relation schema:
1
Swimmer(SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
Candidate Keys
[1] SwimmerId
Foreign Keys
[1] Main_CT_Id references CareTaker(CT_Id), [2] CurrentLevelId references Level(LevelId).
Nullable Attributes
Notes
[1] The relationship “Main Caretaker” is implemented as two attributes Main_CT_Id and Main_CT_Since. It is also acceptable to use a separate relation to implement the relationship. [2] CurrentLevelId is a derived column that can be obtained from the table LevelHistory.
Normalization Analysis
FD:
Highest NF:
2
Caretaker(CT_Id, FName, LName, Phone, Email)
Candidate Keys
[1] CT_Id
Foreign Keys
Nullable Attributes
Notes
Normalization Analysis
FD:
Highest NF:
3
OtherCaretaker(OC_Id, SwimmerId, CT_Id, Since)
Candidate Keys
[1] OC_Id, [2] {SwimmerId, CT_Id}
Foreign Keys
Nullable Attributes
Notes
[1] A surrogate key, OC_Id, is created as the primary key. This is optional.
Normalization Analysis
FD:
Highest NF:
4
Level(LevelId, Level, Description)
Candidate Keys
[1] LevelId, [2] Level
Foreign Keys
Nullable Attributes
Possibly Description, depending on assumptions made.
Notes
Normalization Analysis
FD:
Highest NF:
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:
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:
1
Swimmer(SwimmerId, FName, LName, Phone, Email, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
Candidate Keys
[1] SwimmerId
Foreign Keys
[1] Main_CT_Id references CareTaker(CT_Id), [2] CurrentLevelId references Level(LevelId).
Nullable Attributes
Notes
[1] The relationship “Main Caretaker” is implemented as two attributes Main_CT_Id and Main_CT_Since. It is also acceptable to use a separate relation to implement the relationship. [2] CurrentLevelId is a derived column that can be obtained from the table LevelHistory.
Normalization Analysis
FD:
Highest NF:
2
Caretaker(CT_Id, FName, LName, Phone, Email)
Candidate Keys
[1] CT_Id
Foreign Keys
Nullable Attributes
Notes
Normalization Analysis
FD:
Highest NF:
3
OtherCaretaker(OC_Id, SwimmerId, CT_Id, Since)
Candidate Keys
[1] OC_Id, [2] {SwimmerId, CT_Id}
Foreign Keys
Nullable Attributes
Notes
[1] A surrogate key, OC_Id, is created as the primary key. This is optional.
Normalization Analysis
FD:
Highest NF:
4
Level(LevelId, Level, Description)
Candidate Keys
[1] LevelId, [2] Level
Foreign Keys
Nullable Attributes
Possibly Description, depending on assumptions made.
Notes
Normalization Analysis
FD:
Highest NF:
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:
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
1.
FD's:-
Present the highest normal form is 2 normal form. And in this relation there is a transitive dependency between Main_CT_Id --> Main_CT_Since, Transtive dependency means non prime attribute is dependent on other non prime attribute. Here Main_CT_Id is itself depends on candidate key SwimmerId and it is non prime key attribute. then Main_CT_Since is dependent on Main_CT_Id which is transitive. This doesnot allow in 3 normal form.
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.
This doent satisfy the 2 normal form because no non prime attribute will be depend on part of candidate key. Here in this all OC_Id, SwimmerId, CT_Id are the candidate keys. Since is the only non prime attribute. But this is dependent of part of candidate key CT_ID only which doen't allow in Second normal form.
4.
This satisfies all the rules of Normal forms upto BCNF as there is no transitive and no non trivial dependencies in the relation. In this LevelId, Level are the candidate keys and Description is non prime attribute but it depends on both the candidate keys.
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
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.