 |
DPT621S - DATABASE PROGRAMMING AND TECHNIQUES - 2ND OPP - JAN 2020 |
|
|
1 Page 1 |
▲back to top |
NAMIBIA UNIVERSITY
OF SCIENCE AND TECHNOLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENT OF COMPUTER SCIENCE
QUALIFICATION: BACHELOR OF COMPUTER SCIENCE
QUALIFICATION CODE: 07BACS
LEVEL: 6
ners || PROGRAMMING AND COURSE CODE: DPT621S
DATE: JANUARY 2020
DURATION: 2 HOURS
SESSION: 2
MARKS: 70
SECOND OPPORTUNITY / SUPPLEMENTARY EXAMINATION QUESTION PAPER
EXAMINER(S)
MS JOSEPHINA MUNTUUMO
MODERATOR:
PROF JOSE QUENUM
INSTRUCTIONS
Answer all questions.
Please, ensure that your writing is legible, neat and presentable.
When answering questions you should be led by the allocation of marks.
Clearly mark rough work as such or cross it out unambiguously in ink.
THIS QUESTION PAPER CONSISTS OF 5 PAGES (Including this front page)
|
|
2 Page 2 |
▲back to top |
Section B- Practical (70 Marks)
(NOTE: Use SQL Server Management System (SSMS) on your computer for this section)
Instructions: Copy and Paste your final code onto notepad and save it on the exam drive (z). The
drive is the drive with your student number.
Question 1
Mewiliko Car Dealership (MCD) is a Namibian owned company and it comprises of. many
departments such as HR, Auxiliary department etc... The auxiliary department is interested in
tracking cars they buy. Create a simple database called MCD and a table called Cars. The columns in
the Cars table are named registration ID, Car Name, Price, and Car Description (gives the full details
of the car including make, model and year). The registration ID column is the primary key of the
table. Use the appropriate data types. Only the Price and Car Description columns can have no data
when a row is inserted or changed. Create a statement to check if the table has been created. [8
Marks]
Question 2
Write a statement to create a procedure with input parameters which adds new Car details records
(values of your choice) to the Car table created in question 1. Keep the values for the Car Price, Car
Description null. The procedure should have a condition to check if the car exist already and print
message 'This car already exist’.
[8 Marks]
Question 3
Write a query to invoke your procedure in Question 2 and adds four (4) rows to the dbo.Cars table.
Show all the values that are passed to the procedure to insert the four rows of data. [2 Marks]
Question 4
Create a stored procedure name sp_countcars, which accepts an input parameter named @Carlabel
and returns the total number cars based on the car Name. The output parameter should be named
carcount. The stored procedure executes SELECT statements based on the table named Cars created
in question 1. The columns registration ID, Car Name, and Car Description should be shown in the
output. Create a statement to invoke the procedure.
[8 Marks]
Question 5
Write a stored procedure that checks if the average car price is less than $300 000, introduce the
WHILE loop that doubles the prices and then selects the maximum price. If the maximum price is less
than or equal to $500 000, the WHILE loop restarts and doubles the prices again. If the average price
is greater than 300k while the max is still less than 500000, This loop continues doubling the prices
until the maximum price is greater than $500 000 and then exits the WHILE loop. [7 Marks]
Question 6
Write a statement to create a trigger to restrict updates on the CarName column in the dbo.table
created in question 1. Print a message 'Updates to Car Name require DBA involvement.’
[5 Marks]
|
|
3 Page 3 |
▲back to top |
Question 7
Create a trigger on the dbo.Cars table that will record in the ActivityLog table the user who is
deleting cars from the database. The log should contain the user name of the person deleting
records, the date of the deletion, and the registration numbers of the cars deleted. [7 Marks]
Question 8
The HR department is interested in managing Mewiliko Car Dealership employees ‘data (in order to
solve this question, you may need to create an employee’s table first, do not show this on your
answer sheet as no marks will be given on the creation of the table). You have been tasked by HR to
create a procedure named sp EmployeeRewards that will assist HR in handing out rewards to
employees who meet the following criteria for those that have served the company. The stored
procedure should accept an input parameter named @hdate. The procedure should calculate the
numbers of years the employees have worked in the company using the following criteria. Those
who have worked for 24 years should receive 10% Discount on cars reward, 23 years should receive
1 free car service reward, 22 years should receive Gift Pack reward, 21 years should receive Gift
voucher reward, and 20 years should receive Certificate of Service reward and those who served less
just Company Pens rewards. They would a like a report that will display the employees first name,
last name, the years worked in the company based on their hire date rounded to the nearest whole
number displayed as age in company and the type of reward. The procedure should have on input
parameter and an output parameter that returns the total number of employees who have worked
for the same number of years. Use Case statements and the employees table. [11 marks]
Question 9
Study the schema below. Write a statement to create a stored procedure that returns an element-
centric XML document and schema with information about a specified employee. Use @empno as
an input parameter.
[5 Marks]
Employee table
A
Salesman
B
Manager
c
Manager
10
10
|| 20
Department table
Deptno
(PK)
10
Sales
Chicago
20
Sales
Chicago
30
Finance
New York
a DataCamp
Question 10
In order for customers of MCD to buy cars they need to have bank accounts at a Banking Institution
of their choice. Create a table with bank account information (not less than four columns of your
choice including the primary key) and then a stored procedure for transferring funds from one
account to another. The stored procedure should contain transaction processing and exception
handling.
[6 Marks]
|
|
4 Page 4 |
▲back to top |
Question 11
Construct a simple XML schema that describes a Bank Information. Include the Account owner’s
account number, branch name, balance, and depositor’s info account number and customer name
as child elements of the BANK element.
[3 Marks]
|
|
5 Page 5 |
▲back to top |
NAMIBIA UNIVERSITY
OF SCIENCE AND TECHNOLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENT OF COMPUTER SCIENCE
QUALIFICATION: BACHELOR OF COMPUTER SCIENCE
QUALIFICATION CODE: 07BACS
LEVEL: 6
COURSE: DATABASE PROGRAMMING AND TECHNIQUES | COURSE CODE: DPT621S
DATE: JANUARY 2020
SESSION: 2 (Practical)
DURATION: 2 HOURS
MARKS: 70
SECOND OPPORTUNITY/SUPPLEMENTARY EXAMINATION QUESTION PAPER
EXAMINER(S)
MR GEREON KOCH KAPUIRE
MODERATOR
PROF. JOSE QUENUM
THIS QUESTION PAPER CONSISTS OF 3 PAGES
(Including this front page)
INSTRUCTIONS
1.
Answer all questions.
2.
When writing take the following into account: The style should inform more than
impress. It should be formal, in third person, paragraphs set out according to ideas or
issues and the paragraphs flowing in a logical order. Information provided should be
brief and accurate.
Please, ensure that your writing is legible, peat and presentable.
|
|
6 Page 6 |
▲back to top |
Section B — Practical (70 Marks)
(Use SQL Server Management System (SSMS) on your computer for this section)
Instructions: Copy and Paste your final code onto notepad and save it on the exam drive (z).
The drive is the drive with your student number.
Question 1
[4 Marks]
With so much activity at airports, with planes flying i n and outa huge amount of data can be
collected daily. This result in big data that can be mined to reveal patterns and trends that can
used to improve the services and operations of the airport. The data mining system is going to
be able answer these following questions:
- How many planes fly in and out of Namibia ‘during a given period e.g. day, month, year
etc.
- How often are flights delayed or how often are they on time, how often do people fly to
a specific country and how many of them jn average?
- How many passengers each plane carries in each time together with how many times is
a flight fully booked.
at
Using the description above, create a table named FLIGHT_INFO using six fields of your choice.
Choose relevant data types and include one primary key.
Question 2
[5 Marks]
Write a statement to create a procedure which adds the passenger’s age to the table created in
Question 1. Create a statement to invoke the procedure, and a statement to verify that the
record was successfully added.
Question 3
.
[11 Marks]
Write a statement to create a procedure which adds records to the FLIGHT_INFO table. The
flight information should be added until it reaches a maximum of three. Make use of a loop.
Make use of parameters to accept values. Write a statement to invoke the procedure.
Question4
[S Marks]
Write a statement to create a procedure which calculates and displays the average age of the
passengers. Create a statement to invoke the procedure.
Question 5
.
:
[13 Marks]
Modify the procedure created in Question 4 to accept destination and total number of visits as
input. The procedure must check if Germany was visited more than ten times, and then display
the minimum age of the passengers. If Germany was visited less than ten times, display the
maximum age of the passengers. Create a statement to invoke the procedure.
|
|
7 Page 7 |
▲back to top |
Question 6
[4 Marks]
Modify the procedure created in Question 5 to accept an input value. The result once
processed should be returned as output for both conditions.
Question 7
[4 Marks]
Write a statement to create a procedure which deeepts the result outputted from the
procedure created in Question 6 and invoke the procedure. The results should be printed.
Question 8
;
[7 Marks]
Write a statement to create a procedure which accept the capacity as input. The input should
check if the capacity is more than 100 and change the status of the flight to Not Available. If the
capacity is less than 100, display that the flight can be booked. Write a statement to invoke the
procedure.
Question 9
[6 Marks]
Write a statement to display the status of the flight three times. Please do not retype the same
statement. Write a statement to invoke the procedure.
Question 10
-
[5 Marks]
Write a statement to create a trigger. Print a message when anyone tries to delete data from
the FLIGHT_INFO table. Create a statement to verify that the trigger is fired successfully.
Question 11
,
uo
[6 Marks]
Write a statement to create a procedure which performs transactions. If the flight destination
is Australia, the statement must delete the record in the FLIGHT_INFO table; perform. a
COMMIT TRANSACTION. Create a statement to verify that the transaction was successful or
not.
;
<<<<<<End of Exam >>>>>