Databases & Web Programming — Answer Sheets

A1. Database — An organised collection of related data, e.g. students, modules and enrolments at a university.

A2. DBMS — Software for creating, managing and accessing databases. Benefits include controlled concurrent access, less redundancy, constraints and improved integrity.

A3. Schema versus data — A schema describes tables, columns, types, constraints and relationships; data are the actual stored records.

A4. ER components — Entity: Student. Attribute: StudentID. Relationship: Student enrols in Module.

A5. PK versus FK — A primary key uniquely identifies a row and cannot be NULL. A foreign key references a key in another table; for example Enrolment.StudentID refers to Student.StudentID.

A6. Relationship cardinalities — 1:1 Person–Passport; 1:M Lecturer–Module; M:M Student–Module. Participation may be optional depending on the requirements.

A7. Junction table — An Enrolment(StudentID, ModuleID) junction table represents a many-to-many link as two one-to-many links, without repeating module columns or storing lists in a cell.

A8. Normalisation — Organising tables to reduce unnecessary duplicate data, maintain consistency and avoid insert, update and delete anomalies.

A9. 1NF — The slides require atomic values, a primary key and no duplicated rows or columns / repeating groups.

A10. 2NF — Satisfies 1NF and no non-key attribute depends on only part of a composite candidate key. E.g. StudentName depends on StudentID, not the whole (StudentID,ModuleID) pair.

A11. 3NF — A transitive dependency is a non-key attribute determining another non-key attribute, e.g. StudentID → Postcode → City; splitting related facts prevents redundancy and update anomalies.

A12. Database anomalies — INSERT: cannot store a fact without unrelated data. UPDATE: duplicated facts become inconsistent. DELETE: removing one fact accidentally removes another.

A13. CRUD — Create=INSERT; Read=SELECT; Update=UPDATE; Delete=DELETE.

A14. SQL and psql — SQL statements commonly end with a semicolon, e.g. SELECT ...; psql special commands start with backslash, e.g. \c database_name.

C1. SQL fill-in (5 marks)

Blanks in order: TABLE, NOT, NULL, TEXT, student_id.

CREATE TABLE Student (
    student_id INTEGER NOT NULL,
    name TEXT,
    PRIMARY KEY (student_id)
);

CREATE TABLE is SQL, unlike JDL entity. A primary key is unique and non-null.

C2. JDL fill-in (5 marks)

Blanks in order: String, String, ManyToMany, modules, students.

entity Student {
    name String required
}

entity Module {
    title String required
}

relationship ManyToMany {
    Student{modules} to Module{students}
}

Lowercase field/navigation names, and JDL String rather than SQL TEXT. Equivalent meaningful navigation names can work in free-response JDL.

C3. DELETE error (5 marks)

The statement DELETE FROM student; has no WHERE and deletes all student rows.

DELETE FROM student WHERE student_id = 7;

The statement removes only the row with student_id 7, assuming the table uses that column name.

C4. SELECT result (5 marks)

The result is one row with title = Web Programming.

SELECT title
FROM Module
WHERE module_id = 2;

SELECT specifies the returned column, FROM selects the table, and WHERE chooses matching rows.

C5. Long JDL task (15 marks)

Correct entities and two relationships; a bidirectional OneToMany models Lecturer–Module. Equivalent ManyToOne with correct direction may also express the same cardinality.

entity Student {
    name String required
    email String required unique
}

entity Module {
    title String required
    credits Integer required
}

entity Lecturer {
    fullName String required
}

relationship ManyToMany {
    Student{modules} to Module{students}
}

relationship OneToMany {
    Lecturer{modules} to Module{lecturer}
}

JHipster typically creates Student, Module and Lecturer tables; a Student–Module join table holding their foreign keys; and a lecturer foreign key on the Module (many) side. Generated names may vary.

16. Functional dependency (4 marks)

Definition (1): A → B means that whenever two rows agree on A, they must also agree on B; A determines B.

Example (1): StudentID → StudentName: a given StudentID identifies one stored StudentName.

Reverse direction (2): StudentName → StudentID need not hold because different students may share the same name. Note that a functional dependency does not mean different IDs must have different names.

17. Partial dependencies and 2NF (5 marks)

Dependency one (1): StudentID → StudentName.

Dependency two (1): ModuleID → ModuleTitle.

Why 2NF is violated (1): Each non-key attribute depends on only one part of composite PK (StudentID, ModuleID).

Correct decomposition (2): Student(StudentID PK, StudentName); Module(ModuleID PK, ModuleTitle); Enrolment(StudentID FK, ModuleID FK, composite PK(StudentID, ModuleID)).

18. Transitive dependency and 3NF (5 marks)

First dependency (1): StudentID → Postcode.

Second dependency (1): Postcode → City (as assumed by the question).

Identify issue (1): StudentID → City indirectly through Postcode, a non-key attribute: transitive dependency.

Decomposition (2): Student(StudentID PK, Postcode FK); PostcodeCity(Postcode PK, City). This assumes each postcode determines exactly one city.

19. Safe DELETE (3 marks)

Risk (1):

DELETE FROM Student; has no WHERE clause and removes every student row.

Correct SQL (2):

DELETE FROM Student WHERE student_id = 7;

20. JDL and junction table (3 marks)

JDL entities and relationship (2):

entity Student {
  name String required
}

entity Module {
  title String required
}

relationship ManyToMany {
  Student{modules} to Module{students}
}

Generated table (1): A student–module junction table holding references to Student and Module, often with a composite key or unique pair.