UNIVERSITY EXAMINATIONS
OCT/NOV 2021
INF3707
DATABASE DESIGN AND IMPLEMENTATION
Date: November 2021
Duration: 2 hours
Total marks: 100
Examiners:
First: Dr B. Chipangura
Second: Dr L. Motsi
External: Dr P. Nkomo
This is a closed book examination, under IRIS invigilation. Unisa exam policy applies.
Instructions to students
1. Answer all questions in the given order.
2. You are not allowed to use Mobile devices, Calculators or Oracle XE during this exam.
3. Study the tables in Annexure A.
4. Students must upload their answer scripts in a single PDF file (answer scripts must not be
password protected or uploaded as “read only” files.)
5. NO emailed scripts will be accepted.
6. Students are advised to preview submissions (answer scripts) to ensure legibility and that
the correct answer script file has been uploaded.
7. Students are permitted to resubmit their answer scripts should their initial submission be
unsatisfactory.
8. Incorrect file format and uncollated answer scripts will not be considered.
9. Incorrect answer scripts and/or submissions made on unofficial examinations platforms
(including the invigilator cellphone application) will not be marked and no opportunity will
be granted for resubmission.
10. Mark awarded for incomplete submission will be the student’s final mark. No opportunity
for resubmission will be granted.
11. Mark awarded for illegible scanned submission will be the student’s final mark. No
opportunity for resubmission will be granted.
12. Submissions will only be accepted from registered student accounts.
13. Students who have not utilised invigilation or proctoring tools will be subjected to
disciplinary processes.
14. Students suspected of dishonest conduct during the examinations will be subjected to
disciplinary processes. UNISA has a zero tolerance for plagiarism and/or any other forms
of academic dishonesty.
15. Students are provided one hour to submit their answer scripts after the official examination
time. Submissions made after the official examination time will be rejected by the
examination regulations and will not be marked.
16. Students experiencing network or load shedding challenges are advised to apply together
with supporting evidence for an Aegrotat within 3 days of the examination session.
Students experiencing technical challenges, contact the SCSC 080 000 1870 or email
or refer to Get-Help for the list of additional contact numbers.
[Turn over]
, 2 INF3720
OCT/NOV
Question 1 25 marks
1.1 From what you learnt in this module, list any four functions of a DBMS? (4 marks)
1.2 Assume that you successfully installed Oracle database, how do you view the available databases on
the DBMS? Write the SQL*plus command to achieve that. (2 marks)
1.3 By default, Oracle pluggable databases remain closed and must be opened manually or set to
automatically open. Which Oracle 18c XE service must you configure to do that? (2 marks)
1.4 Oracle Database XE comes with a sample database user named HR. This user owns several database
tables in a sample schema for a fictional Human Resources department. However, for security reasons,
this user's account is locked. You need to unlock this account before you can view the HR objects or
build any applications that use these objects. Assume that you are logged in as System Administrator,
provide the SQL*plus commands for unlocking the HR account, making sure that the password reuse
and life time is unlimited. (4 marks)
1.5 John is an Accountant at JustLee Books and require access to the OrderItems table. Create an account
for John and provide him with a password that expire. Make sure that John has a role of an accountant.
As an Accountant, John should have the rights to provide other employees with access priviledges to the
OrderItems table. (9 marks)
1.6 Write a query to view all privileges that are assigned to the Accountant role. (4 marks)
Question 2 30 marks
Assume that the following table named myCustomers exist in your database and has three columns Col#,
Col1, Col2.
NAME NULL TYPE
-------- ------ ---------
Col# NOT NULL Number
Col1 NOT NULL VARCHAR2(2)
Col2 NOT NULL VARCHAR2(10)
2.1 Write an SQL statement to view the structure of myCustomers. (2 marks)
2.2 Write SQL code to check if the table containts any data. (2 marks)
2.3 Verify the existance of the myCustomers table in the data dictionary. (3 marks)
2.4 myCustomers table does not have a primary key, make Col# column the primary key and name the
constraint SA_Customer_pk. (3 marks)
2.5 myCustomers table contains some columns that are marked as unused. Write an SQL code that drops
all the columns marked as unused. (2 marks)
2.6 All the data entered into myCustomers is not correct. Write an SQL code that deletes the data from
the table but leaving it intact. (2 marks)
[Turn over]
OCT/NOV 2021
INF3707
DATABASE DESIGN AND IMPLEMENTATION
Date: November 2021
Duration: 2 hours
Total marks: 100
Examiners:
First: Dr B. Chipangura
Second: Dr L. Motsi
External: Dr P. Nkomo
This is a closed book examination, under IRIS invigilation. Unisa exam policy applies.
Instructions to students
1. Answer all questions in the given order.
2. You are not allowed to use Mobile devices, Calculators or Oracle XE during this exam.
3. Study the tables in Annexure A.
4. Students must upload their answer scripts in a single PDF file (answer scripts must not be
password protected or uploaded as “read only” files.)
5. NO emailed scripts will be accepted.
6. Students are advised to preview submissions (answer scripts) to ensure legibility and that
the correct answer script file has been uploaded.
7. Students are permitted to resubmit their answer scripts should their initial submission be
unsatisfactory.
8. Incorrect file format and uncollated answer scripts will not be considered.
9. Incorrect answer scripts and/or submissions made on unofficial examinations platforms
(including the invigilator cellphone application) will not be marked and no opportunity will
be granted for resubmission.
10. Mark awarded for incomplete submission will be the student’s final mark. No opportunity
for resubmission will be granted.
11. Mark awarded for illegible scanned submission will be the student’s final mark. No
opportunity for resubmission will be granted.
12. Submissions will only be accepted from registered student accounts.
13. Students who have not utilised invigilation or proctoring tools will be subjected to
disciplinary processes.
14. Students suspected of dishonest conduct during the examinations will be subjected to
disciplinary processes. UNISA has a zero tolerance for plagiarism and/or any other forms
of academic dishonesty.
15. Students are provided one hour to submit their answer scripts after the official examination
time. Submissions made after the official examination time will be rejected by the
examination regulations and will not be marked.
16. Students experiencing network or load shedding challenges are advised to apply together
with supporting evidence for an Aegrotat within 3 days of the examination session.
Students experiencing technical challenges, contact the SCSC 080 000 1870 or email
or refer to Get-Help for the list of additional contact numbers.
[Turn over]
, 2 INF3720
OCT/NOV
Question 1 25 marks
1.1 From what you learnt in this module, list any four functions of a DBMS? (4 marks)
1.2 Assume that you successfully installed Oracle database, how do you view the available databases on
the DBMS? Write the SQL*plus command to achieve that. (2 marks)
1.3 By default, Oracle pluggable databases remain closed and must be opened manually or set to
automatically open. Which Oracle 18c XE service must you configure to do that? (2 marks)
1.4 Oracle Database XE comes with a sample database user named HR. This user owns several database
tables in a sample schema for a fictional Human Resources department. However, for security reasons,
this user's account is locked. You need to unlock this account before you can view the HR objects or
build any applications that use these objects. Assume that you are logged in as System Administrator,
provide the SQL*plus commands for unlocking the HR account, making sure that the password reuse
and life time is unlimited. (4 marks)
1.5 John is an Accountant at JustLee Books and require access to the OrderItems table. Create an account
for John and provide him with a password that expire. Make sure that John has a role of an accountant.
As an Accountant, John should have the rights to provide other employees with access priviledges to the
OrderItems table. (9 marks)
1.6 Write a query to view all privileges that are assigned to the Accountant role. (4 marks)
Question 2 30 marks
Assume that the following table named myCustomers exist in your database and has three columns Col#,
Col1, Col2.
NAME NULL TYPE
-------- ------ ---------
Col# NOT NULL Number
Col1 NOT NULL VARCHAR2(2)
Col2 NOT NULL VARCHAR2(10)
2.1 Write an SQL statement to view the structure of myCustomers. (2 marks)
2.2 Write SQL code to check if the table containts any data. (2 marks)
2.3 Verify the existance of the myCustomers table in the data dictionary. (3 marks)
2.4 myCustomers table does not have a primary key, make Col# column the primary key and name the
constraint SA_Customer_pk. (3 marks)
2.5 myCustomers table contains some columns that are marked as unused. Write an SQL code that drops
all the columns marked as unused. (2 marks)
2.6 All the data entered into myCustomers is not correct. Write an SQL code that deletes the data from
the table but leaving it intact. (2 marks)
[Turn over]
