Study guides

Database Systems

5 guides for this subject — how to prepare, what to practise, and how to know it has gone in.

A practice question

Choose an answer and it is marked, with the explanation. From Database Systems Practice Questions: 8 Worked MCQs.

Question 11 mark

For relation R(A, B, C, D), the functional dependencies are A → BC, C → D, and D → A. Which set contains all candidate keys?

A determines B and C, then C determines D, so A is a key. C determines D, D determines A, and A determines B and C, so C is a key. D determines A and then B and C, so D is a key. Each is minimal. Option 1 misses the keys C and D; adding attributes to a key, as in option 2, does not make a candidate key.

A spoken answer, marked

An examiner's question, answered out loud and marked criterion by criterion. From Database Systems Viva Questions: A Practical Oral Practice Guide.

Oral — Normalisation and functional dependenciesMarked

Examiner

Assume StudentID determines StudentName; CourseID determines CourseName and InstructorID; InstructorID determines InstructorOffice; and (StudentID, CourseID) determines Grade. Identify a candidate key for the original relation, explain two anomalies, and propose a decomposition that avoids them.

2:323:00Mark answer
75%Normalisation and functional dependencies — marked75/100 · Developing · 2:32 spoken of 3:00
Technical correctness and terminology16/20

You correctly identified the composite key and repeated course facts.

ImproveName the functional dependencies explicitly before using them to justify a decomposition.

Reasoning from stated assumptions15/20

You used the assumption that a student may enrol in multiple courses.

ImproveExplain why neither StudentID nor CourseID alone identifies one enrolment.

Trade-offs and limitations14/20

You proposed separating student and course details.

ImproveAccount for the separate InstructorID-to-InstructorOffice dependency as well.

Clear, complete explanation15/20

Your answer was understandable and linked repetition to update problems.

ImproveFinish by naming the attributes in each resulting relation.

A strong answerThe candidate key for the original relation is (StudentID, CourseID), because a student can take several courses and a course has several students. StudentID determines StudentName, while CourseID determines CourseName and InstructorID, and InstructorID determines InstructorOffice; the key pair determines Grade. Repeating these facts across enrolment rows can cause update anomalies, such as changing an instructor’s office in some rows but not others. I would separate Student(StudentID, StudentName), Course(CourseID, CourseName, InstructorID), Instructor(InstructorID, InstructorOffice) and Enrolment(StudentID, CourseID, Grade). Under the stated dependencies, these relations separate the independently determined facts while retaining the enrolment grade.

Start studying database systems

Yours are written from your own notes, and the free plan needs no payment details.

Guides

Studying database systems? Build your database systems board from your own notes — the cards, papers and lectures are written from it. Free plan: 20 cards, a paper, a lecture and a viva to try.

Start on MySummaries

Other subjects