QUESTIONS AND
ANSWERS
FOR ASSISTANCE CONTACT
EMAIL:gabrielmusyoka940@gmail.com
, lOMoARcPSD|46589353
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
Examenquiries@unisa.ac.za or refer to Get-Help for the list of additional contact numbers.
[Turn over]
Open Rubric
, lOMoARcPSD|46589353
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]
, lOMoARcPSD|46589353
3 INF3720
OCT/NOV
2.7 You realised that Col1 and Col2 are too small to accommodate large variables, write an SQL code
that increases the size of Col1 from 2 to 25 and Col2 from 10 to 25. (4 marks)
2.8 Create two more columns (Col3 and Col4) of VARCHAR2() type that can accomodate data with
length of upto 25 character. (4 marks)
2.9 Insert data into three columns of myCustomers, by populating Col1, Col3 and Col4 with values A, B
and D in that order. Do not insert any data into Col2, store a null value. (4 marks)
2.10 If the primary key of this table was created with the following statement:
Col# Number GENERATED AS IDENTITY PRIMARY KEY; What output do you expect after
inserting the following data into myCustomers table? Explain your answer.
Insert into MyTable (Col#, Col1, Col2, Col3, Col4) value (001, ‘A’, ‘B’, ‘C’,’D’);
(2 marks)
2.11 You created a sequence that generated some numbers for Col3 of myCUstomers table. The sequence
is as follows:
Create sequence seq1
Increment by 10
Maxvalue 200
Cycle
Nocache;
After generating some numbers from the sequence a few times, you want to reinitialize the sequence so
that it can reuse the numbers already generated.? Describe how you can achieve this. (2 marks)
Question 3 18 marks
3.1 Create a view that selects every book in the Books table of the JustLee Database with a retail price
higher than the average retail price of all the books. Name the View Books_Above_Average_Price and
check the data in the view. (10 marks)
3.2 Based on the data extracted from the Books table of the JustLee database, see table below, write a
Top-N analysis query that displays 5 percent of the books with the highest retail price. (8 marks)
ISBN TITLE RETAIL
1059831198, BODYBUILD IN 10 30.95
MINUTES A DAY
0401140733 REVENGE OF MICKEY 22
4981341710 BUILDING A CAR WITH 59.95
TOOTHPICKS
8843172113 DATABASE 55.95
IMPLEMENTATION
3437212490 COOKING WITH 19.95
MUSHROOMS
[Turn over]
Downloaded by Gabriel Musyoka (gabudeking@gmail.com)
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller gabrielmusyoka940. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for £2.04. You're not tied to anything after your purchase.
