Database Management Assignment

Topics: Database normalization, Foreign key, Data modeling Pages: 6 (684 words) Published: August 25, 2013
ICT 321 Database Systems: Modelling and Design|
Tutor-Marked Assignment July 2013 Name : Ng Xuan Bin
Student No. : K1073312
Name : Ng Xuan Bin
Student No. : K1073312
|
|

1(a)
SELECT StudentNumber, Name, Contact, Address, DateOfBirth
FROM Student
WHERE DATEDIFF(year, [DateOfBirth], getDate())>=17
AND StudentNumber LIKE 'IT%'
ORDER BY DateOfBirth DESC , Name ASC;

1(b)

SELECT StudentNumber, Student.Name as [Student Name], Subject.Name as [Subject Name], LessonDate as [Date Absent] FROM Student, Subject, Absence
WHERE Student.StudentNumber = Absence.StudentID AND Absence.SubjectCode = Subject.SubjectCode ORDER BY Subject.Name ASC, StudentNumber ASC, LessonDate ASC;

1(c)

Select Student.StudentNumber, Student.Name as [Student Name], Subject.Name as [Subject Name], (((10.0-COUNT(*))/10)*100) as [Attendance(%)] FROM Student, Subject, Absence
WHERE Absence.SubjectCode = Subject.SubjectCode AND Absence.StudentID = Student.StudentNumber GROUP BY StudentNumber, Student.Name, Subject.Name
HAVING (((10.0-COUNT(*))/10)*100)<85;

1(d)

SELECT Subject.SubjectCode, Name, Description
FROM Subject
WHERE SubjectCode in (Select SubjectCode from Absence WHERE LessonDate LIKE '05%' AND SubjectCode LIKE 'BS%');

2(a)

Multi-valued depencies

CaseNum ->-> counsellorName

Functional dependencies

CaseNum -> startDate, EndDetail, CaseDetail, StudentNumber

StudentNumber -> StudentName, StudentContact

CounsellorName -> CounsellorContact

CaseNum, sessionDate, sessionTime -> CounsellorName, sessionDuration, sessionOutcome, caseDetail

2(b)

Proposed candidate key is (sessionDate, sessionTime)

2(c)

STEP ONE: MOVE MULTIVALUED DEPENDENCIES INTO SEPARATE TABLES: Consider the MVDs

CaseNum ->-> counsellorName

CounsellingCase_2(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName, StudentContact, sessionDate, sessionStartTime, sessionDuration, sessionOutcome)

CaseCounsellor(CaseNum, CounsellorName, CounsellorContact)

STEP TWO: CHECK EACH OF THE RESULTING TABLES FOR BNCF:
STEP TWO (A): CHECK CounsellingCase_2

CounsellingCase_2(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, StudentName, StudentContact, sessionDate, sessionStartTime, sessionDuration, sessionOutcome)

NO, StudentNumber is NOT a candidate key.
Therefore the relation is NOT in BCNF.
Therefore, move StudentNumber->StudentName, StudentContact into another table leaving StudentNumber as a foreign key in the original table

CounsellingCase_3(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, sessionDate, sessionStartTime, sessionDuration, sessionOutcome)

StudentDetails(StudentNumber, StudentName, StudentContact)

STEP TWO (A) (1): CHECK StudentDetails:

StudentDetails(StudentNumber, StudentName, StudentContact)

Is every determinant a candidate key?

YES, Therefore StudentDetails is in BNCF.

STEP TWO (A) (2): Check CounsellingCase_3:

CounsellingCase_3(CaseNum, StartDate, EndDate, CaseDetail, StudentNumber, sessionDate, sessionStartTime, sessionDuration, sessionOutcome)

Is every determinant a candidate key?
NO, CaseNum is NOT a candidate key.
Therefore the relation is NOT in BCNF.
Therefore, move CaseNum-> startDate, EndDate, CaseDetail into another table leaving StudentNumber as a foreign key in the original table

CounsellingCase_4(CaseNum, StudentNumber, sessionDate, sessionStartTime, sessionDuration, sessionOutcome)

StudentDetails(StudentNumber, StudentName, StudentContact)

CaseInfo(CaseNum, StartDate, EndDate, CaseDetail)

STEP TWO (A) (3): CHECK CaseInfo:

CaseInfo(CaseNum, StartDate, EndDate, CaseDetail)

Is every determinant a candidate key?

YES, Therefore CaseInfo is in BNCF.

STEP TWO (A) (4): CHECK CounsellingCase_4:

Is every determinant a candidate key?

YES, Therefore CounsellingCase_4 is in BNCF.

STEP TWO (B): CHECK CaseCounsellor:

CaseCounsellor(CaseNum, CounsellorName,...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • MMIS 630 Database Systems Final Essay
  • Database Management Research Paper
  • Database Management Essay
  • DATABASE MANAGEMENT Essay
  • An Overview of Database Management Systems Essay
  • Database Management System Essay
  • Relational Database Management System Essay
  • Database Management Systems Essay

Become a StudyMode Member

Sign Up - It's Free