need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help

Author

Topic

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2013-08-22 : 11:47:34

Hi,

I have a question which i need help in:

This question assesses your knowledge and understanding on functional dependencies and
the normalisation techniques. You should be able to answer this question once you have
studied Chapter 3 (The Relational Model and Normalization) and Chapter 4 (Database
Design Using Normalization).
A student with poor attendance will be referred to the Student Counselling Centre, thereby
one or more counsellors will be assigned to the student’s case. In any counselling session, the
student will be seen by any one of the assigned counsellors. That counsellor will then record
the outcome for the session with that student. Counsellors can counsel different students on
different sessions, but any counsellor will see only one student at any one counselling
session.

table given is: CounsellingCase(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName,
StudentContact, CounsellorName, CounsellorContact, sessionDate, sessionStartTime,
sessionDuration, sessionOutcome)

formulate and list the functional and multi-valued dependencies.

my ans:
CaseNum-->StartDate, EndDate, CaseDetail, StudentNumber
StartDate-->EndDate
StudentNumber-->StudentName, StudentContact
CounsellorName-->CounsellorContact
SessionDate-->-->CounsellorName
CaseNum-->-->CounsellorName

Can anyone help me to see if i have done it correct or is there anymore i could add.

Thanks

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2013-08-24 : 09:48:01

Can any one check if i have done correctly. My Ans:

Question 2

a)

CounsellingCase(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName,
StudentContact, CounsellorName, CounsellorContact, sessionDate, sessionStartTime,
sessionDuration, sessionOutcome)

Functional Dependencies:
CaseNum?StartDate, EndDate, CaseDetail, StudentNumber
StudentNumber?StudentName, StudentContact
CounsellorName?CounsellorContact
CaseNum,CounsellorName?SessionDuration,SessionOutcome

Multivalued Dependency:
CaseNum?? CounsellorName
CaseNum?? sessionDate, sessionStartTime

b)
Candidate key for Case table:
(CaseNum , CounsellorName , sessionDate, sessionStartTime)

c)
Consider the CounsellingCase table:

CounsellingCase(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName,
StudentContact, CounsellorName, CounsellorContact, sessionDate, sessionStartTime,
sessionDuration, sessionOutcome)

Step One: Move Multivalued Dependencies into separate tables:
There are two multivalued dependencies

CaseNum??CounsellorName?CounsellorContact
CaseNum?? sessionDate, sessionStartTime

CounsellingCase(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName,
StudentContact, CounsellorName, CounsellorContact, sessionDate, sessionStartTime,
sessionDuration, sessionOutcome)

CounsellingCase_Counsellor_Detail (CaseNum, CounsellorName, CounsellorContact)
CounsellingCase_Session(CaseNum, sessionDate, sessionStartTime)

Step Two: Check each of the resulting tables for BNCF:
CounsellingCase_2(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName, StudentContact, sessionDuration, sessionOutcome)

Step Two (A): Check CASE_2
CounsellingCase_2(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName, StudentContact, sessionDuration, sessionOutcome)

Is every determinant a candidate key?
No, StudentNumber is NOT a candidate key.
Therefore, move StudentNumber?StudentName, StudentContact
into another table leaving StudentNumber as a foreign key in the original table

CounsellingCase_Student_Detail(StudentNumber, StudentName, StudentContact)

CounsellingCase _3 (CaseNum, StartDate, EndDate, CaseDetail, StudentNumber , sessionDuration, sessionOutcome)

Step Two (A) (1): Check CounsellingCase_Student_Detail:
CounsellingCase_Student_Detail(StudentNumber, StudentName, StudentContact)

Is every determinant a candidate key?
Yes, Therefore CounsellingCase_Student_Detail is in BNCF.

Step Two (A) (2): Check CASE_3
CounsellingCase _3 (CaseNum, StartDate, EndDate, CaseDetail, StudentNumber sessionDuration, sessionOutcome)

Is every determinant a candidate key?
CounsellingCase _3 is in BNCF.

Step Two (B): Check CounsellingCase_Counsellor_Detail:
CounsellingCase_Counsellor_Detail(CaseNum, CounsellorName, CounsellorContact)

Is every determinant a candidate key?
No, CounsellorName is NOT a candidate key. Therefore, move CounsellorName -> CounsellorContact into another table leaving CounsellorName as a foreign key in the original table

CounsellingCase _Counsellor_Detail (CounsellorName, CounsellorContact)
CounsellingCase_Counsellor_Detail_2(CaseNum, CounsellorName)

Step Two (B) (1): Check Counselling_Detail:
CounsellingCase _Counsellor_Detail (CounsellorName, CounsellorContact)

Is every determinant a candidate key?
Yes, Therefore CounsellingCase _Counsellor_Detail is in BNCF.

Step Two (B) (2): Check CASE_SOLICITOR_DETAILS_2
CounsellingCase_Counsellor_Detail _2 (CaseNum, CounsellorName)

Is every determinant a candidate key?
CaseNum does not determine CounsellorName, and CounsellorName does not determine CaseNum. In this case there are NO determinants other than (CaseNum, CounsellorName), but this meets the criteria! Therefore CounsellingCase_Counsellor_Detail_2 is in BCNF.

d)

CounsellingCase_Student_Detail(StudentNumber(PK), StudentName, StudentContact)

CounsellingCase _3 (CaseNum, StartDate, EndDate, CaseDetail, StudentNumber(FK) sessionDuration, sessionOutcome)

Where CounsellingCase _3.StudentNumber must exist CounsellingCase_Student_Detail

CounsellingCase _Counsellor_Detail (CounsellorName(PK), CounsellorContact)

CounsellingCase_Counsellor_Detail _2 (CaseNum(PK)(FK), CounsellorName (FK))
Where CounsellingCase _Counsellor_Detail_2.CaseNum must exist in CounsellingCase _3.CaseNum

And

CounsellingCase_Counsellor_Detail _2. CounsellorName must exist in
CounsellingCase _Counsellor_Detail. CounsellorName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources