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.
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.
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.
You correctly identified the composite key and repeated course facts.
ImproveName the functional dependencies explicitly before using them to justify a decomposition.
You used the assumption that a student may enrol in multiple courses.
ImproveExplain why neither StudentID nor CourseID alone identifies one enrolment.
You proposed separating student and course details.
ImproveAccount for the separate InstructorID-to-InstructorOffice dependency as well.
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.
Yours are written from your own notes, and the free plan needs no payment details.
Guides
- How to Study Database Systems: A Practical GuideA practical method for studying database systems, from relational models and SQL to transactions, indexes and query plans.
- Database Systems Study Plan: A Practical Four-Week MethodA four-week database systems study plan with focused sessions for SQL, data modelling, normalisation, transactions and query processing.
- Database Systems Flashcards: A Practical Study DeckStudy 18 database systems flashcards on keys, SQL, normalisation, indexes and transactions, with a method for grading and reviewing each card.
- Database Systems Practice Questions: 8 Worked MCQsWork through eight database systems practice questions on keys, normalisation, SQL, transactions and indexes, with explanations for every answer.
- Database Systems Viva Questions: A Practical Oral Practice GuidePractise database systems viva questions with a structured method, worked oral answers and focused feedback on key concepts.
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