Databases & Web Programming
Weeks 1–2 · Focused Revision Paper · Difficult Questions
20 questions · 35 marks · 35–45 minutes
15 multiple-choice questions (1 mark each) + 5 written/code questions (20 marks).
Based on the Week 1 and Week 2 lecture material only. No answers are printed on this paper.
Name: ____________________________ Date: __________________
Instructions: Circle one answer per multiple-choice question. Write your answers to the later questions in the space provided. Show dependencies and key fields clearly. No notes if attempting under exam conditions.
Part A — Multiple Choice (15 marks)
1. In Enrolment(StudentID, ModuleID), neither column is individually unique, but each pair is unique. What is the most appropriate primary key? [1]
A. StudentID
B. ModuleID
C. (StudentID, ModuleID)
D. StudentName
2. Given StudentID -> StudentName, what does this dependency guarantee? [1]
A. Every student has a different name
B. Rows with the same StudentID have the same StudentName
C. StudentName uniquely determines StudentID
D. StudentName cannot be NULL
3. Enrolment(StudentID, ModuleID, StudentName) has composite PK (StudentID, ModuleID). StudentName depends only on StudentID. Which issue is present? [1]
A. Transitive dependency
B. No issue because there is a PK
C. Violation of 1NF only
D. Partial dependency
4. Student(StudentID, Postcode, City) has StudentID -> Postcode and Postcode -> City, assuming each postcode specifies a city. Which is the relevant issue? [1]
A. Transitive dependency
B. Composite primary key
C. Many-to-many relationship
D. Repeating attribute
5. Which is the best correction to a partial dependency in Enrolment(StudentID, ModuleID, StudentName)? [1]
A. Add more StudentName columns
B. Remove ModuleID from every table
C. Move StudentName into Student(StudentID, StudentName) and retain enrolment pairs
D. Replace the PK with StudentName
6. If a table is in 1NF and its primary key has one column, which statement best matches the Week 2 lecture? [1]
A. It automatically satisfies 3NF
B. There can be no partial dependency on part of a composite primary key
C. It cannot have foreign keys
D. It must have an UPDATE anomaly
7. A lecturer teaches many modules, and each module has one lecturer. Which table normally stores lecturer_id as a foreign key? [1]
A. Lecturer
B. A mandatory many-to-many join table
C. Student
D. Module
8. A student may take multiple modules; a module may have multiple students. Which relational design avoids a repeating list of module IDs in a Student cell? [1]
A. Enrolment junction table with StudentID and ModuleID
B. One column called Modules containing comma-separated IDs
C. A column per possible module
D. Only a Module table
9. A Student table stores DepartmentID and DepartmentName. DepartmentID determines DepartmentName. Assuming StudentID is PK, this is most directly tested by: [1]
A. 1NF atomicity
B. 2NF partial dependencies
C. 3NF transitive dependencies
D. psql commands
10. What does DELETE FROM Student; do if it succeeds? [1]
A. Removes the table definition
B. Removes all rows but preserves the table structure
C. Removes the first row
D. Removes rows only when the PK is NULL
11. Which command correctly modifies only StudentID 7, assuming the column is student_id? [1]
A. UPDATE Student WHERE student_id=7 SET name='Amira';
B. INSERT INTO Student SET name='Amira' WHERE student_id=7;
C. SELECT name='Amira' FROM Student WHERE student_id=7;
D. UPDATE Student SET name='Amira' WHERE student_id=7;
12. Which of these is a psql meta-command rather than SQL? [1]
A. \c university
B. CREATE TABLE Student (id INTEGER);
C. SELECT * FROM Student;
D. GRANT SELECT ON Student TO reader;
13. Which JDL declaration matches a mandatory, non-duplicated email field? [1]
A. email TEXT NOT NULL UNIQUE
B. required unique email String
C. email String required unique
D. email required String unique
14. In relationship OneToMany { Lecturer{modules} to Module{lecturer} }, which navigation is available? [1]
A. Only module.getModules()
B. lecturer.getModules() and module.getLecturer()
C. Only lecturer.getLecturer()
D. Neither side has navigation
15. JDL defines relationship ManyToMany { Student{modules} to Module{students} }. What does JHipster normally create in a relational database? [1]
A. One column with a list in Student
B. A separate database for each student
C. A direct FK in Student that can store several values
D. A junction table referencing both entities
Part B — Written and Code Reasoning (20 marks)
16. Explain in your own words what A -> B means in a functional dependency. Use StudentID and StudentName, and explain why the reverse direction need not be true. [4 marks]
17. Use this table: Enrolment(StudentID, ModuleID, StudentName, ModuleTitle), with composite PK (StudentID, ModuleID). Identify TWO partial dependencies and propose a 2NF decomposition into tables. [5 marks]
18. Student(StudentID, Postcode, City) has the dependencies StudentID -> Postcode and Postcode -> City. Explain how this creates a transitive dependency and show a sensible 3NF decomposition. [5 marks]
19. The intention is to delete only the student with student_id 7. Explain what is dangerous about DELETE FROM Student; and write the corrected SQL statement. [3 marks]
20. Write JDL for Student(name String required) and Module(title String required) with a bidirectional ManyToMany relationship. Then state what relational table is normally generated to represent it. [3 marks]