 |
DPG621S - DATABASE PROGRAMMING - 2ND OPP - JAN 2025 |
|
|
1 Page 1 |
▲back to top |
n Am I BIA u n IVER s ITY
OF SCIEnCE Ano TECHnOLOGY
FACULTY OF COMPUTING AND INFORMATICS
SCHOOL OF COMPUTING
DEPARTMENTOF SOFTWAREENGINEERING
QUALIFICATIONS:BACHELOR OF COMPUTER SCIENCE; BACHELOR OF INFORMATICS;
BACHELOR OF GEOINFORMATION TECHNOLOGY
QUALIFICATIONCODES: 07BCMS;
07BACS 07BAIT; 07BAIF; 07GITB
LEVEL: 6
COURSECODE: DPG621S
COURSENAME: DATABASE PROGRAMMING
SESSION:JANUARY 2025
PAPER: PAPER 1
DURATION: 3 HOURS
MARKS: 100
SECONDOPPORTUNITY/SUPPLEMENTARYEXAMINATION QUESTION PAPER
EXAMINER:
DR GEREON KOCH KAPUIRE, MS SHILUMBE CHIVUNO-KURIA, MS JOSEPHINA
MUNTUUMO, MR ANDREW TJIRARE
MODERATOR:
MS ROSETHA KAYS
INSTRUCTIONS
1. Answer ALL the questions.
2. Read all the questions carefully before answering.
3. Number the answers clearly.
4. Write your "Section A" answers in the answer booklet provided.
5. Copy and paste your final code into Notepad and save it to the exam
folder on E-Learning.
THIS QUESTION PAPERCONSISTSOF 4 PAGES(Including this front page)
|
|
2 Page 2 |
▲back to top |
Section A - Theory (10 Marks)
Question 1
Choose True or False
[10 Marks]
No
Questions
True (T)
1 A stored procedure in SQL Server can encapsulate multiple
SQL statements to perform a specific task.
2 The IF statement in SQL Server is used to manage the flow of
execution in stored procedures or batches.
3
Stored procedures can return more than one result set in SQL
Server.
4 SQL Server allows stored procedures to have both input and
output parameters.
5 Triggers in SQL Server execute automatically in response to
events like INSERT,UPDATE,or DELETE.
6
The FETCHstatement retrieves the next row from the cursor's
result set in SQL Server.
7
The DECLARECURSORstatement specifies the attributes of a
cursor in SQL Server.
8 SQLServer utilizes a TRY...CATCHblock for managing
exceptions.
9
Parameters in a SQL Server stored procedure must match the
data types of the corresponding columns in the database.
10 The ERRORstatement is used in SQLServer to raise exceptions
explicitly.
False (F)
Section B - Practical (90 Marks)
Please utilise SQL Server Management Studio (SSMS)on your computer for this section.
Instructions: Copy and paste your final code into Notepad and save it to the exam folder on E-
Learning. Ensure that you number your code appropriately. Refer to the scenario and table
below to aid your understanding of the preceding questions.
Scenario:
Design and develop an application aimed at providing career guidance and counseling. This
application will focus on enhancing an individual's self-awareness, exploring educational and
occupational options, and facilitating career planning. Its purpose is to help individuals make
informed educational and occupational decisions and implement those choices effectively.
For this application, we can create a table named UserProfiles to store information about
individuals using the career guidance application. Here's a suggested structure for the
UserProfiles table:
2
|
|
3 Page 3 |
▲back to top |
CREATE TABLE UserProfiles
(
UserID INT PRIMARY KEY IDENTITY(l,1),
FirstName VARCHAR(l00) NOT NULL,
LastName VARCHAR(l00) NOT NULL,
Email VARCHAR(l00) UNIQUE NOT NULL,
EducationLevel
VARCHAR(S0) NOT NULL,
Careerinterests
VARCHAR(l00),
LastGuidanceSession
DATETIME,
CreatedAt DATETIME DEFAULT GETDATE(),
LastUpdated DATETIME DEFAULT GETDATE() );
Question 1
[9 Marks]
Write a stored procedure to insert a new user profile into the UserProfiles table. The procedure
should check if the email already exists before inserting. If it exists, return a messageindicating
that the email is already in use.
Question 2
[9 Marks]
Create a stored procedure to update a user's career interests based on their email. If the email
does not exist, print a message indicating that the user is not found.
Question 3
[5 Marks]
Write a trigger that automatically logs changes made to the UserProfiles table in a
ProfileChangelog table whenever an update occurs.
Question 4
[5 Marks]
Write a stored procedure that retrieves all user profiles based on a specified education level.
Question 5
[12 Marks]
Create a stored procedure that loops through all user profiles and sends a reminder messageto
users whose last career guidance session was more than 6 months ago. Print a reminder for
each relevant user.
Question 6
[4 Marks]
Write a stored procedure that analyses the most common career interests among users and
returns the top three career interests.
Question 7
[10 Marks]
Write a stored procedure to insert a new user profile with error handling. UseTRY...CATCHto
manage potential errors, such as attempting to insert a profile with an existing email.
Question 8
[8 Marks]
Write a stored procedure that deletes a user profile based on their email address. If the user
does not exist, print a message indicating that the user cannot be found.
Question 9
[4 Marks]
Write a stored procedure that counts the number of users for each education level and returns
the results.
3
|
|
4 Page 4 |
▲back to top |
Question 10
[6 Marks]
Write a stored procedure that retrieves all user profiles that match a specific career interest
provided as a parameter. If no users are found with that interest, print a message indicating so.
Question 11
[8 Marks]
Create a stored procedure that resets a user's career interests and education level to default
values based on their email address. If the user does not exist, print a message indicating that.
Question 12
[10 Marks]
Create a stored procedure to insert a new user profile into the UserProfiles table using a
transaction. If any part of the insertion process fails (e.g., due to a duplicate email), roll back
the transaction and print an error message. Ensure that the user profile is only committed if
the entire operation is successful.
<««<End of Exam Paper»>>>
4