 |
DPG621S - DATABASE PROGRAMMING - 1ST OPP - NOV 2024 |
|
|
1 Page 1 |
▲back to top |
nAmlBIA unlVERSITY
OF SCIEnCE Ano TECHnOLOGY
FACULTY OF COMPUTING AND INFORMATICS
SCHOOL OF COMPUTING
DEPARTMENTOF SOFTWAREENGINEERING
QUALIFICATIONS:BACHELOR OF COMPUTER SCIENCE;BACHELOROF INFORMATICS;
BACHELOR OF GEOINFORMATION TECHNOLOGY
QUALIFICATIONCODES: 07BCMS;
07BACS 07BAIT; 07BAIF; 07GITB
LEVEL: 6
COURSECODE:DPG621S
COURSENAME: DATABASE PROGRAMMING
SESSION:NOVEMBER 2024
PAPER: PAPER 1
DURATION: 3 HOURS
MARKS: 100
EXAMINERS:
FIRSTOPPORTUNITYEXAMINATION QUESTION PAPER
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
1 The EXECUTEstatement in SQLServer is used to invoke a
stored procedure.
2 Transactions cannot be explicitly started and committed
within a stored procedure.
3 The order in which parameters are defined in a SQLServer
stored procedure must match the order in which they are
passed during the procedure call.
4 Input parameters in SQLServer stored procedures can be
modified within the procedure.
5 Triggers can be used to log information about INSERT,
UPDATE,and DELETEoperations for auditing changes to a
table.
6 The CASEstatement in SQLServer can only be used in
SELECTand WHEREclauses.
7 SQLServer supports a traditional FORloop, similar to those
found in procedural programming languages.
8 Parameters in SQLServer stored procedures can have
default values.
9 Cursors in SQLServer are used to iterate through a result set
row by row.
10 Transactions can be explicitly started and committed within
a stored procedure.
True (T}
False (F}
Section B - Practical (90 Marks}
(For this section, utilise SQLServer Management Studio (SSMS)on your computer).
Instructions: Copy and paste your final code into Notepad and save it on 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:
You are tasked with designing and developing an application that tracks university graduates
(alumni) for the university's alumni association. This on line-based application aims to improve
the current process of tracking alumni and providing their data to the relevant college faculties.
To achieve this, you must use the following table structure to store the alumni data:
2
|
|
3 Page 3 |
▲back to top |
CREATE TABLE Alumni (
AlumniID INT PRIMARY KEY IDENTITY(l,l),
FirstName VARCHAR(lOO),
LastName VARCHAR(lOO),
GraduationYear
INT,
Degree VARCHAR(lOO),
Email VARCHAR(lOO) UNIQUE,
ContactNumber VARCHAR(20),
EmploymentStatus
VARCHAR(50),
Faculty VARCHAR(lOO),
LastUpdated DATETIME DEFAULT GETDATE() ) ;
Question 1
[10 Marks]
Write a stored procedure to insert a new alumnus into the Alumni table, ensuring that no
duplicate email addresses are allowed.
Question 2
[6 Marks]
Create a stored procedure that updates the employment status of an alumnus based on their
AlumnilD.
Question 3
[5 Marks]
Develop a stored procedure that retrieves alumni data for a specific faculty and graduation
year.
Question 4
Write a trigger that prevents the deletion of any alumni record.
[4 Marks]
Question 5
[4 Marks]
Create a stored procedure that returns a summary of alumni employment status, grouped by
employment status.
Question 6
[12 Marks]
Write a stored procedure that updates the employment status of all alumni who graduated in a
particular year. The update should be wrapped in a transaction, and if any error occurs, the
transaction should be rolled back.
Question 7
[10 Marks]
Write a stored procedure that checks if an email address already exists in the Alumni table. If it
does, print an error message; otherwise, insert the new alumni record.
Question 8
[14 Marks]
Write a stored procedure using a cursor that retrieves alumni from a specific faculty and prints
their full names.
Question 9
[10 Marks]
Create a stored procedure that inserts a new alumni record into the Alumni table. If an error
occurs during the insert (e.g., duplicate email), catch the error and print a user-friendly
3
|
|
4 Page 4 |
▲back to top |
message.
Question 10
[8 Marks]
Write a stored procedure that prevents the insertion of a new alumni record if the contact
number is not provided (i.e., the parameter @ContactNumber is empty or NULL). Use an IF
statement to check this condition and print a relevant message.
Question 11
[7 Marks]
Write a stored procedure that updates the degree of all alumni who graduated before the year
2000. Use an IF statement to check whether the GraduationYear is before 2000, and if true,
update the degree to 'Legacy Graduate'. The procedure should print a message if no alumni
qualify for the update.
<«<<<End of Exam Paper»>»
4