 |
DBF510S - DATABASE FUNDAMENTALS - 2ND OPP - JULY 2022 |
|
|
1 Page 1 |
▲back to top |
n Am I 8 I A Un IVERS ITY
OF SCIEnCE Ano TECHn OLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENT OF COMPUTER SCIENCE
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: DBF510S
COURSE NAME: DATABASE FUNDAMENTALS
SESSION: JULY2022
DURATION: 2 HOURS
PAPER: THEORY
MARKS: 100
SECOND OPPORTUNITY/ SUPPLEMENTARY EXAMINATION QUESTION PAPER
EXAMINER($)
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 PAPER CONSISTS OF _6_ PAGES (Including this front page)
|
|
2 Page 2 |
▲back to top |
SECTION A MULTIPLE CHOICE (20 MARKS)
1. The relationship between the instances of a single entity type is called a(n)__
A. Ternary
B. Primary
C. Binary
D. Unary
2. A business rule
A. Defines or constrains some aspect of the business
B. Asserts business structure
C. Controls or influences the behaviour of the business
D. None of the above
3. A local area network-based environment in which a database server performs
commands sent to it from the client workstation is called a(n) ____
architecture.
A. Workstation/server
B. Database/workstation
C. Server/client
D. Client/server
4. Which statement should you use to delete entire contents of a table
A. ALTERTABLE
B. DELETETABLE
C. DROP TABLE
D. TRUNCATETABLE
5. Which of the following are valid Data Definition Language (DDL) commands?
A. ALTER
B. INSERT
C. DELETE
D. CREATE
6. What is a primary key?
A. Allows us to access any field immediately like in RAM
B. Allows us to select which fields can be linked to other databases
C. It is a unique key that is an index to the data
D. It is a key that references another key in a relating table
2
|
|
3 Page 3 |
▲back to top |
7. You issue the following command to drop the PRODUCTStable
DROPTABLE PRODUCTS;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. All indexes on the table remain but they are invalidated
C. A pending transaction in the session is committed
D. All views and synonyms on the table remain but are invalidated
8. In which normal form is a table, if it has multi-valued attributes and no partial
dependencies?
A. First Normal Form
B. Second Normal Form
C. Third Normal Form
D. Fourth Normal Form
9. Which statement is true regarding the UNION operator?
A. Null values are not ignored during duplicate checking
B. The number of columns selected in all SELECTstatements need not be the same
C. Names of all columns must be identical across all SELECTstatements
D. By default, the output is not stored
10. Which SQL statement is used to delete data from a database?
A. REMOVE
B. DELETE
C. DROP
D. COLLAPSE
11. Which statement will display the last name and enrolment date of all students who
registered after March 1989?
A. SELECTlast_name, enrolment_date
WHERE enrolment_date > '31-MAR-89';
B. SELECTlast_name
FROM student
WHERE enrolment_date > '31-MAR-89';
C. SELECTlast_name, enrolment_date
FROM student
WHERE enrolment_date > '31-MAR-89';
D. SELECTenrolment date
FROM student
3
|
|
4 Page 4 |
▲back to top |
WHERE enrolment_date > '31-MAR-89';
12. Which SQL statement selects all the columns from a table named Products?
A. SELECTProducts;
B. SELECT[all] FROM Products;
C. SELECT* FROM Products;
D. SELECT*.Products;
13. Which SQL statement is used to extract data from a database?
A. GET
B. DISPLAY
C. EXTRACT
D. SELECT
14. The entity integrity rule states that
A. No primary key attribute can be null
B. Referential integrity must be maintained across all entities
C. Each entity must have a foreign key
D. A primary key must have only one attribute
15. Which two statements are true regarding the GROUP BY clause in a SQL statement?
{Choose two)
A. Using the WHERE clause after the GROUP BY clause excludes the rows after
creating groups
B. Using the WHERE clause before the GROUP BY clause excludes the rows before
creating groups
C. The GROUP BY clause is mandatory if you are using an aggregate function in the
SELECTclause
D. If the SELECTclause has an aggregate function, then those individual columns
without an aggregate function in the SELECTclause should be included in the
GROUP BY clause
SECTIONB ANSWERTRUEOR FALSE(10 MARKS)
1. SQL keywords are case sensitive.
2. A NATURALJOIN can be classified as an equijoin.
3. Values that are specified by the BETWEENoperator are inclusive.
4. When using the set operators, the SELECTlists must match in data type.
5. SQL DROP is used to remove objects from the database.
4
|
|
5 Page 5 |
▲back to top |
6. An UPDATE... SET... statement can modify multiple rows based on multiple conditions
on a table.
7. A business rule is a table that defines some aspects of the DBMS.
8. A tuple is synonymous with record.
9. Update anomalies can occur in a normalised relation.
10. Date and character Literal values must be enclosed in single quotations.
SECTION C (40 MARKS}
Question 1
Using examples, differentiate the following terms
i.
Attribute and tuple
ii.
Drop and delete commands
iii.
Union operator and intersection operator
iv.
Referential integrity and entity integrity
V.
Primary key and foreign key
[3 marks]
[3 marks]
[3 marks]
[3 marks]
[3 marks]
Question 2
i.
Explain the phrase "three- tier architecture" with respect to databases and the
ANSI-SPARCmodel
[10 marks]
ii.
A bank has opened in the country and they would like to have a database that they
can use for all their staff and clients in all main towns. They want to use the same
database management software at all their locations. Briefly explain what type of
database you would recommend.
[S marks]
Question 3
i.
State and explain any two (2) normalization goals
[4 marks]
ii.
With the aid of your own table, describe the three (3) data anomalies. [6 marks]
5
|
|
6 Page 6 |
▲back to top |
SECTION D {30 MARKS)
Question 1
Read the scenario given below and draw an ER diagram in Chen's notation for the described
database.
(15 marks]
A company transports many chemical Products from one location to another on behalf of a
registered Customer. Each transport operation is called a Job, which involves picking up one
or more Loads of the same product from a customer's requested start location and delivers it
to a customer's requested destination. A unique number is given for each Job and for each
Load when they are created. A Load is transported using a particular Transport Unit, which
consists of a lorry, a driver, a container (for carrying the product) and occasionally specialist
loading equipment (such as Jacks and Pumps). A container is fixed to a trailer so it needs to
be coupled to a lorry at the start of a Job. Transport operations are run from 5 regional
Depots. Jobs are allocated to individual depots to service. Depots are usually located near to
pick up points such as chemical plants. Depots hold, manage and maintain their own transport
units which are permanently allocated to them to service Jobs. A container, and sometimes
associated loading equipment, may on occasions be left at destinations or pick-ups after a Job
is finished. But normally transport units are held at the base Depot they belong.
Question 2
Table 1 below shows the definition details for the BOOK table:
Column Name
ISBNNumber
Book Title
Publication Date
PublisherName
PrintingCompany
AuthorName
Other Constraints
NOT NULL, PRIMARY KEY
Type
number
varchar(S0)
Date
varchar(20)
varchar(25)
varchar(30)
i.
Write the SQ~·statement _to create the BOOK table based on the details given
above
[10 marks]
ii.
Write the SQL statements to add the following record to the BOOKtable created
in i.
[5 marks]
ISBNNumber
9202874
BookTitle
Oxford
Thesarus
PublicationDate
30 June 2006
PublisherName
Oxford
University
Press
PrintingCompany
Interactive
Sciences
AuthorName
Maurice
Waite
6