 |
DBF510S - DATABASE FUNDAMENTALS - 1ST OPP - JUNE 2022 |
|
|
1 Page 1 |
▲back to top |
n Am I BI A u n IVE Rs ITY
OFSCIEnCEAno TECHn OLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENTOF COMPUTERSCIENCE
QUALIFICATION: BACHELOR OF COMPUTER SCIENCE, BACHELOR OF GEOMATICS, BACHELOR OF LAND
ADMINISTRATION, BACHELOR OF GEO INFORMATION TECHNOLOGY, BACHELOR OF INFORMATICS,
BACHELOR OF COMPUTER SCIENCE IN CYBER SECURITY, DIPLOMA IN GEOMATICS
QUALIFICATION CODE: 07BACS, 07GITB,
07BLAD, 07BGEM, 07BCCS, 07BGEI, 07BAIT,
LEVEL: 5
06DGEM,
COURSE CODE: DBFSlOS
COURSE NAME: DATABASE FUNDAMENTALS
SESSION: JUNE 2022
DURATION: 2 HOURS
PAPER: THEORY
MARKS: 100
EXAMINER(S)
FIRST OPPORTUNITY EXAMINATION QUESTION PAPER
MS TERESSA CHIKOHORA, MS. SHILUMBE CHIVUNO-KURIA, MS. JOSEPHINA
MUNTUUMO, MR RIAHAMA MUSUTUA, MR HEKEREKO KAVIMAKA
MODERATOR:
Mr. G. KAPUIRE
INSTRUCTIONS TO CANDIDATES
1. Answer ALL questions in ALL sections of this paper.
2. Please, ensure that your writing is legible, neat and presentable.
3. Number your answers clearly on the answer booklet provided.
4. Write your student number, study mode and group number clearly on your
answer booklet.
THIS QUESTION PAPERCONSISTSOF _9_ PAGES{Including this front page)
|
|
2 Page 2 |
▲back to top |
SECTION A MULTIPLE CHOICE 20 MARKS
1.The function of a database is to --------
A. Collect and organise input data
B. Check all input data
C. Check all spelling
D. Output data
2. A ___
is the set of allowable values for one or more attributes.
A. Cardinality
B. Tuple
C. Degree
D. Domain
3. Within a table, the primary key must be unique so that it will identify each row. When this
is the case, the table is said to exhibit
-----
integrity.
A. Referential
B. Entity
C. Enforced
D. Key
4. The set of possible values of a column is called a ________
_
A. Range
B. Product
C. Domain
D. Function
5.Which of the following are Data Manipulation Language (DML) statements?
i. SELECT
ii. ALTER
iii. DROP
iv. DELETE
A. i, iv
B. ii, iii
C. i, ii, iii
2
|
|
3 Page 3 |
▲back to top |
D. ii, iii, iv
6.The following operation is used to update the structure of the database table.
A. Alter
B. Update
C. Change
D. None of the above
7. Values of key attributes cannot be NULL is the requirement of ______
_
A. Key Constraint
B. Entity Integrity Constraint
C. Referential Integrity
D. None of the above
8. E-R model uses this symbol to represent a weak entity set?
A. Dotted rectangle.
B. Diamond
C. Doubly outlined rectangle
D. None one of these
9. To remove duplicate rows from the results of an SQL SELECTstatement, we include
----
in the statement.
A. ONLY
B. UNIQUE
C. DISTINCT
D. SINGLE
10. Which SQLkeyword is used to sort the result-set?
A. SORTBY
B. ALIGN BY
C. ORDERBY
D. GROUP BY
11. The degree of an entity related to itself is _____
_
A. Recursive
B. Ternary
3
|
|
4 Page 4 |
▲back to top |
C. Binary
D. Single
12. An attribute of an entity with many values is called a ______
A. Derived
B. Multivalued
C. Composite
D. Comparison
attribute.
13. Examine the structure of the MEMBERS table:
MEMBER_ID NOT NULL VARCHAR(6)
FIRST_NAME VARCHAR(S0)
LAST_NAME NOT NULL VARCHAR(S0)
ADDRESS VARCHAR(S0)
CITY VARCHAR(25)
STATE VARCHAR(3)
You want to display details of all members who reside in states starting with the letter 'A'
followed by exactly one character. Which SQL statement must you execute?
A. SELECT* FROM MEMBERS WHERE state LIKE '%A_*';
B. SELECT* FROM MEMBERS WHERE state LIKE 'A_*';
C. SELECT* FROM MEMBERS WHERE state LIKE 'A_%';
D. SELECT* FROM MEMBERS WHERE state LIKE 'A%';
14. Create table is a?
A. DDL Command
B. DML Command
C. Both A and B
D. None of the above
15. Examine the structure of the MEMBERS table:
MEMBER_ID NOT NULL VARCHAR(6)
FIRST_NAME VARCHAR(S0)
LAST_NAME NOT NULL VARCHAR(S0)
ADDRESS VARCHAR(S0)
4
|
|
5 Page 5 |
▲back to top |
You execute the SQL statement:
SELECTMEMBER_ID, '', FIRST_NAME, '', LAST_NAME "ID FIRSTNAME LASTNAME" FROM
MEMBERS;
What is the outcome?
A. It fails because the alias name specified after the column names is invalid
B. It fails because the space specified in single quotation marks after the first two column
names is invalid
C. It executes successfully and displays the column details in a single column with only the
alias column heading
D. It executes successfully and displays the column details in three separate columns and
replaces only the last column heading with the alias
16. The Acronym SQL stands for:
A.Structured Quick Language
B. Structured Queue Language
C. Structured Query Learning
D. Structured Query Language
17. We want to find all Students whose name contains 'na'. Which statement will execute
successfully to get the desired output?
A. SELECT*
FROM students
WHERE first_name LIKE '%na%' OR
last_name LIKE '%na%';
B. SELECT*
WHERE first_name LIKE '%na%' OR
last_name LIKE '%na%';
C. SELECT*
FROM students
WHERE first_name LIKE'%na%';
last_name LIKE '%na%';
D. SELECT*
FROM students
first_name LIKE '%na%' OR
last_name LIKE'%na%';
5
|
|
6 Page 6 |
▲back to top |
18. Which SQL statement is used to remove data from a database?
A.DROP
B. DELETE
C. COLLAPSE
D. REMOVE
19. Which statement is true regarding the default behavior of the ORDERBY clause?
A. In a character sort, the values are case sensitive
B. NULL values are not considered at all by the sort operation
C. Only those columns that are specified in the SELECTlist can be used in the ORDERBYclause
D. Numeric values are displayed from the maximum to the minimum value if they have
decimal positions
20. Which statement is true regarding the UNION operator?
A. By default, the output is not sorted
B. Null values are not ignored during duplicate checking
C. Names of all columns must be identical across all select statements
D. The number of columns selected in all select statements need not be the same.
SECTIONB: (40 MARKS}
Question 1:
Explain how the following constraints are enforced.
i.
Domain constraint
ii.
Referential integrity constraint
iii.
Entity constraint
Question 2:
Using examples, differentiate the following terms:
i.
Relation degree and relation cardinality
ii.
Strong entity and weak entity
iii.
Candidate key and primary key
iv.
Database schema and database instance
6
[2 marks]
[2 marks]
[2 marks]
[3 marks]
[3 marks]
[3 marks]
[3 marks]
|
|
7 Page 7 |
▲back to top |
V.
Select operator and project operator
[3 marks]
Question 3:
You are a database administrator at OurBooks company. Your management board requires
your input on scenarios they have received on a possible Government client.
Based on the following scenario, determine what type of database you would recommend
and briefly explain why.
[10 marks]
i.
A database that supports the SAP enterprise information System and is used by all
government employees.
ii.
The Ministry of health wants to send a social worker to visit patients at an old age
home. The social worker has to record set of data describing patient visits. Only
the social worker will use the database.
iii. A database that supports the work of five scientists performing research on a new
flu drug.
iv. A database that supports eight Human resources department officers to share
staff related information for all government employees.
Question 4:
Using appropriate examples, describe the following languages
i.
DDL
ii.
DML
iii.
DCL
[3 marks]
[3 marks]
[3 marks]
SECTION C: (40 MARKS}
Question 1:
Table 1 PlayerDetails
PlayerNumber
2
7
9
3
19
playerName
Rudiger
Kante
Lukaku
Tierney
Mount
Position
Defender
Midfielder
Forward
Defender
Midfielder
Team
Chelsea
Chelsea
Chelsea
Arsenal
Arsenal
Given the PlayerDetails table above (Table 1), write SQL commands/ statements to:
i.
Create Tablel
[4 marks]
7
|
|
8 Page 8 |
▲back to top |
ii.
Add all the records shown to the table created in (i)
[5 marks]
iii.
Display all the records stored in Tablel in ascending order of PlayerNumber
[3 marks]
iv. Change PlayerNumber 19's team to Liverpool
[3 marks]
V.
Delete all the details of the players that play for Arsenal
[3 marks]
vi. Add a new player with the following details
[2 marks]
PlayerNumber
23
playerName
Dabo
Position
Defender
Team
Coventry City
Question 2:
Consider the following Employees relation
EmployeelD
1
2
3
FirstName
Sam
Ellouise
Geoffrey
4
Sam
5
Issy
Surname
Shokongo
liping
#Eixas
Beukes
Shikongo
Occupation
Nurse
Lecturer
Teacher
Police
Officer
Lecturer
HireDate
15 Spet 2005
23 April 2006
12 March
2004
2 Dec 2001
Salary
N$25000
N$35000
N$15000
N$55000
2 Dec 2001 N$35000
Using relational algebra, write expressions to
i.
List all employees with a salary of more than N$ 30000
[5 marks]
ii.
Display the EmployeelD, FirstName, Surname and salary of all employees
[5 marks]
Question 3:
Read the scenario below and answer the question that follows:
The publishing company produces books and publications on various subjects from its
branches. The company is identified by its name, company registration number and branch
code. A book title, ISBN number, edition, publication date identify a book. Subjects are
identified by subject code with a subject genre as the other attribute. An author writes books
in one particular subject and are identified by their authorlD among other attributes. Many
authors may write a book and an author can write many books. The company employs editors
who are responsible for editing one or more publications. To differentiate the editors, each
editor is assigned an editorlD and the editor records the date of editing for all publications. A
8
|
|
9 Page 9 |
▲back to top |
publication covers one of the specialist subjects and is written by a single author though an
author can write many publications.
i.
Draw an ERdiagram for the described database.
[10 marks]
9