Databases & Web Programming — Weeks 1

Total: 100 marks | Suggested time: 75–90 minutes
Section A: 35 marks | Section B: 30 marks | Section C: 35 marks
Revision paper based on your Week 1 and Week 2 lecture slides, not a prediction of exam questions. No answer key included.

Instructions: Answer Section A in your own words. For Section B circle one answer per question. Complete Section C using SQL and JDL, with explanations where requested. Extra paper may be used for longer answers.

Section A — Written Theory (35 marks)

A1. What is a database? Give one example of how a university might use one. [2 marks]

A2. What is a Database Management System (DBMS)? Explain two benefits of using one instead of separate spreadsheets. [3 marks]

A3. Explain the difference between a database schema and the actual data stored in a database. [2 marks]

A4. What are entities, attributes and relationships in an ER model? Give one example of each. [3 marks]

A5. Explain the difference between a primary key and a foreign key, using a Student and Module database as an example. [3 marks]

A6. Explain the differences between one-to-one, one-to-many and many-to-many relationships. Give an example of each. [3 marks]

A7. Why is a junction table needed for a many-to-many relationship in a relational database? [2 marks]

A8. What is database normalisation, and why is it useful? [3 marks]

A9. State the requirements of First Normal Form (1NF). [2 marks]

A10. What extra requirement does Second Normal Form (2NF) introduce? Explain what a partial dependency is. [3 marks]

A11. What is a transitive dependency? Explain why Third Normal Form (3NF) aims to remove it. [3 marks]

A12. Explain the difference between an INSERT anomaly, UPDATE anomaly and DELETE anomaly. [3 marks]

A13. What does CRUD stand for? Give the SQL command associated with each operation. [2 marks]

A14. What is the difference between SQL commands and psql commands? Give one syntactic distinction. [1 marks]

Section B — Multiple Choice (30 marks)

B1. Which statement best describes a database schema? [2 marks]

A. A collection of current records

B. A description of the database structure and constraints

C. A copy of the database

D. A program that executes queries

B2. Which is a key benefit of a DBMS? [2 marks]

A. It removes the need for data validation

B. It guarantees no data can ever be lost

C. It supports structured, controlled multi-user access

D. It prevents all cyberattacks

B3. In an ER model, StudentID is most naturally an example of: [2 marks]

A. An entity

B. An attribute

C. A relationship

D. A DBMS

B4. A lecturer teaches several modules, each with one lecturer. The relationship is: [2 marks]

A. One-to-one

B. Many-to-many

C. One-to-many

D. No relationship

B5. Where is the foreign key normally placed in a one-to-many relationship? [2 marks]

A. On the one side

B. On the many side

C. In both tables without exception

D. In neither table

B6. How is a many-to-many relationship normally implemented in relational tables? [2 marks]

A. Repeating columns

B. A junction table

C. Storing IDs as a comma-separated string

D. Removing the relationship

B7. Which issue violates First Normal Form (1NF)? [2 marks]

A. A column contains multiple values in one cell

B. A table has a foreign key

C. A table has a single-column primary key

D. A table stores integers

B8. What does Second Normal Form (2NF) eliminate? [2 marks]

A. All foreign keys

B. Partial dependencies of non-key attributes on candidate keys

C. All rows containing NULL

D. The need for primary keys

B9. Which best describes a transitive dependency? [2 marks]

A. A non-key attribute depends on another non-key attribute, which depends on a key

B. All attributes are primary keys

C. A table contains two rows

D. A key contains more than one column

B10. A student's deletion also removes the only stored record of a class. This is: [2 marks]

A. INSERT anomaly

B. UPDATE anomaly

C. DELETE anomaly

D. Primary-key anomaly

B11. Which statement about a primary key is correct? [2 marks]

A. It may identify several rows

B. It identifies a row uniquely and cannot be NULL

C. It must always be text

D. It must reference another table

B12. Which SQL command retrieves records from a table? [2 marks]

A. UPDATE

B. INSERT

C. SELECT

D. DELETE

B13. What happens when DELETE FROM students; executes successfully without a WHERE clause? [2 marks]

A. One student is deleted

B. The entire table definition is removed

C. All rows in the table are deleted

D. Nothing happens

B14. Which statement is a psql meta-command rather than a SQL command? [2 marks]

A. CREATE TABLE

B. SELECT

C. INSERT

D. \c database_name

B15. In JDL, what does relationship ManyToMany describe? [2 marks]

A. One entity must have exactly one related entity

B. Multiple instances on each side can be related

C. Both entities must have identical attributes

D. The database cannot use a junction table

Section C — Coding & Interpretation (35 marks)

Five questions: two fill-in-the-blanks, two code reasoning tasks and one extended Instate/JDL task.

C1. Fill in the missing SQL [5 marks]

Complete the statement to create a table named Student with a unique, non-null integer student_id as its primary key and a text name.

CREATE _____ Student (
    student_id INTEGER _____ _____,
    name _____,
    PRIMARY KEY (_____)
);

C2. Fill in the missing JDL [5 marks]

Complete the JDL for Student and Module. Each student can enrol in many modules, and each module can contain many students.

entity Student {
    name _____ required
}

entity Module {
    title _____ required
}

relationship _____ {
    Student{_____} to Module{_____}
}

C3. Find and explain the SQL mistake [5 marks]

The command below is intended to delete only the student with ID 7. Explain the problem, give the corrected SQL and describe the danger of running the original command.

DELETE FROM student;

C4. Predict and explain a database result [5 marks]

A table called Module contains (1, 'Databases'), (2, 'Web Programming') and (3, 'AI'), with columns module_id and title. What does this query return? Explain SELECT and WHERE.

SELECT title
FROM Module
WHERE module_id = 2;

C5. Long JDL task — university database [15 marks]

Write a JDL model for Student (name String required, email String required unique), Module (title String required, credits Integer required) and Lecturer (fullName String required). A student can enrol in many modules; a module can have many students. One lecturer teaches many modules; each module has one lecturer. Include useful navigation names. Explain which tables and foreign keys you expect from JHipster. Test the JDL at https://instate.shah.fyi/jdl if you wish.

// Write your full JDL model in the space below.