⋆
⋆
INF3707: Database Design and Implementation
May/June Examination 2026 — Covers 2023 to 2025 Past Papers
⋆ ⋄ ⋆ ⋄ ⋆ ⋄ ⋆ ⋄ ⋆
õ Oracle SQL & Database Design õ
_ Exam Revision Guide
INF3707
Module Code:
Database Design and Implementation
Module Name:
May/June Examination 2026
Paper / Exam:
May/June 2023, 2024 & 2025 Past Papers
Covers:
100 per paper
Total Marks:
2 Hours
Duration:
Every question answered. All SQL verified. Study smart.
Exam Revision Notes | INF3707 | 2026
,INF3707 | Exam Revision 2026 Database Design & Implementation
z PART A: May/June 2025 Examination Paper z
Question 1 (30 marks)
1.1 (5 marks)
Question: Which Oracle Database Management System is prescribed for this mod-
ule? Describe THREE roles that a Database Administrator (DBA) plays in managing a
database environment.
Answer: Oracle Database 12c (or Oracle XE 18c in recent years) is the prescribed
DBMS for INF3707.
Three core DBA responsibilities, and these come up almost every year:
• Installation and Configuration: The DBA installs the Oracle software, sets up
the database instance, and configures memory, storage, and network settings so the
system actually runs. Without this groundwork, nothing else functions.
• Security Management: Creating user accounts, assigning roles, granting and
revoking privileges — the DBA controls who sees what. This ties directly into
Questions 1.3–1.5 below.
• Backup and Recovery: Regular backups prevent catastrophic data loss. The DBA
designs and tests recovery procedures so the database can be restored after failures
— hardware crashes, corruption, or accidental deletions.
Other valid roles include performance tuning, capacity planning, and applying patches.
⋆ Exam Tip
The exam usually asks for exactly three roles and awards one mark per valid
role plus one mark for a brief explanation each. Don’t just list names — add a
sentence of context.
1.2 (5 marks)
Page 2 of 11
,INF3707 | Exam Revision 2026 Database Design & Implementation
Question: Create a sequence named CUST_SEQ that generates integers starting at 1020,
incremented by 1, with no cycling and no caching. Then use it to insert a new customer
into the CUSTOMERS table with: Last name = BROWN, First name = SUSAN, ZIP =
49345.
Answer: Two steps here. First create the sequence, then insert using NEXTVAL.
-- Step 1: Create the sequence
CREATE SEQUENCE CUST_SEQ
START WITH 1020
INCREMENT BY 1
NOCYCLE
NOCACHE ;
-- Step 2: Insert using the sequence
INSERT INTO CUSTOMERS ( CUSTOMER_NUM , LASTNAME , FIRSTNAME , ZIP )
VALUES ( CUST_SEQ . NEXTVAL , ’ BROWN ’ , ’ SUSAN ’ , 49345) ;
COMMIT ;
Listing 1: Creating CUST_SEQ and inserting a customer
The NOCYCLE clause stops the sequence from restarting once it hits the max. NOCACHE
means no values are pre-allocated in memory, so every call to NEXTVAL hits the data
dictionary directly. Worth mentioning both in an exam.
. Watch Out
CURRVAL can only be called after NEXTVAL has been used at least once in the
current session. Calling CURRVAL first will throw an ORA-08002 error.
1.3 (4 marks)
Question: Distinguish between a GRANT and a REVOKE statement in Oracle SQL.
Give the syntax for each.
Page 3 of 11
, INF3707 | Exam Revision 2026 Database Design & Implementation
Answer: GRANT hands privileges to a user or role; REVOKE takes them back.
Straightforward concept, but the syntax trips people up under pressure.
-- Grant SELECT and INSERT on BOOKS to user JANE
GRANT SELECT , INSERT ON BOOKS TO JANE ;
-- Grant with ability to pass the privilege on
GRANT SELECT ON CUSTOMERS TO JOHN WITH GRANT OPTION ;
-- Revoke SELECT from JANE
REVOKE SELECT ON BOOKS FROM JANE ;
Listing 2: GRANT and REVOKE syntax
Key Concept
WITH GRANT OPTION lets the recipient hand the same privilege to other
users. If you revoke from the original grantee, all cascading grants they made are
also revoked automatically — Oracle handles that cascade.
1.4 (7 marks)
Question: Create a role called BOOKMANAGER. Grant SELECT, INSERT, and UPDATE
privileges on the BOOKS table to the role. Then create a user called ALICE identified by
the password Bookshop1, and assign the BOOKMANAGER role to ALICE. Also grant ALICE
the CONNECT privilege.
Answer:
-- Create the role
CREATE ROLE BOOKMANAGER ;
-- Grant object privileges to the role
GRANT SELECT , INSERT , UPDATE ON BOOKS TO BOOKMANAGER ;
-- Create the user
CREATE USER ALICE IDENTIFIED BY Bookshop1 ;
Page 4 of 11
⋆
INF3707: Database Design and Implementation
May/June Examination 2026 — Covers 2023 to 2025 Past Papers
⋆ ⋄ ⋆ ⋄ ⋆ ⋄ ⋆ ⋄ ⋆
õ Oracle SQL & Database Design õ
_ Exam Revision Guide
INF3707
Module Code:
Database Design and Implementation
Module Name:
May/June Examination 2026
Paper / Exam:
May/June 2023, 2024 & 2025 Past Papers
Covers:
100 per paper
Total Marks:
2 Hours
Duration:
Every question answered. All SQL verified. Study smart.
Exam Revision Notes | INF3707 | 2026
,INF3707 | Exam Revision 2026 Database Design & Implementation
z PART A: May/June 2025 Examination Paper z
Question 1 (30 marks)
1.1 (5 marks)
Question: Which Oracle Database Management System is prescribed for this mod-
ule? Describe THREE roles that a Database Administrator (DBA) plays in managing a
database environment.
Answer: Oracle Database 12c (or Oracle XE 18c in recent years) is the prescribed
DBMS for INF3707.
Three core DBA responsibilities, and these come up almost every year:
• Installation and Configuration: The DBA installs the Oracle software, sets up
the database instance, and configures memory, storage, and network settings so the
system actually runs. Without this groundwork, nothing else functions.
• Security Management: Creating user accounts, assigning roles, granting and
revoking privileges — the DBA controls who sees what. This ties directly into
Questions 1.3–1.5 below.
• Backup and Recovery: Regular backups prevent catastrophic data loss. The DBA
designs and tests recovery procedures so the database can be restored after failures
— hardware crashes, corruption, or accidental deletions.
Other valid roles include performance tuning, capacity planning, and applying patches.
⋆ Exam Tip
The exam usually asks for exactly three roles and awards one mark per valid
role plus one mark for a brief explanation each. Don’t just list names — add a
sentence of context.
1.2 (5 marks)
Page 2 of 11
,INF3707 | Exam Revision 2026 Database Design & Implementation
Question: Create a sequence named CUST_SEQ that generates integers starting at 1020,
incremented by 1, with no cycling and no caching. Then use it to insert a new customer
into the CUSTOMERS table with: Last name = BROWN, First name = SUSAN, ZIP =
49345.
Answer: Two steps here. First create the sequence, then insert using NEXTVAL.
-- Step 1: Create the sequence
CREATE SEQUENCE CUST_SEQ
START WITH 1020
INCREMENT BY 1
NOCYCLE
NOCACHE ;
-- Step 2: Insert using the sequence
INSERT INTO CUSTOMERS ( CUSTOMER_NUM , LASTNAME , FIRSTNAME , ZIP )
VALUES ( CUST_SEQ . NEXTVAL , ’ BROWN ’ , ’ SUSAN ’ , 49345) ;
COMMIT ;
Listing 1: Creating CUST_SEQ and inserting a customer
The NOCYCLE clause stops the sequence from restarting once it hits the max. NOCACHE
means no values are pre-allocated in memory, so every call to NEXTVAL hits the data
dictionary directly. Worth mentioning both in an exam.
. Watch Out
CURRVAL can only be called after NEXTVAL has been used at least once in the
current session. Calling CURRVAL first will throw an ORA-08002 error.
1.3 (4 marks)
Question: Distinguish between a GRANT and a REVOKE statement in Oracle SQL.
Give the syntax for each.
Page 3 of 11
, INF3707 | Exam Revision 2026 Database Design & Implementation
Answer: GRANT hands privileges to a user or role; REVOKE takes them back.
Straightforward concept, but the syntax trips people up under pressure.
-- Grant SELECT and INSERT on BOOKS to user JANE
GRANT SELECT , INSERT ON BOOKS TO JANE ;
-- Grant with ability to pass the privilege on
GRANT SELECT ON CUSTOMERS TO JOHN WITH GRANT OPTION ;
-- Revoke SELECT from JANE
REVOKE SELECT ON BOOKS FROM JANE ;
Listing 2: GRANT and REVOKE syntax
Key Concept
WITH GRANT OPTION lets the recipient hand the same privilege to other
users. If you revoke from the original grantee, all cascading grants they made are
also revoked automatically — Oracle handles that cascade.
1.4 (7 marks)
Question: Create a role called BOOKMANAGER. Grant SELECT, INSERT, and UPDATE
privileges on the BOOKS table to the role. Then create a user called ALICE identified by
the password Bookshop1, and assign the BOOKMANAGER role to ALICE. Also grant ALICE
the CONNECT privilege.
Answer:
-- Create the role
CREATE ROLE BOOKMANAGER ;
-- Grant object privileges to the role
GRANT SELECT , INSERT , UPDATE ON BOOKS TO BOOKMANAGER ;
-- Create the user
CREATE USER ALICE IDENTIFIED BY Bookshop1 ;
Page 4 of 11
