INF3703
exams
+ Solutions
(Databases II)
,2018-06
Section A
1. Horizontal data fragmentation is a process in a distributed database design that breaks a table
into subsets of columns from the original table.
FALSE. Horizontal fragmentation divides a relation (table) into subsets of rows not columns.
p575
2. Database Management System (DBMS) governs the storage and processing of logically related
data over interconnected computer systems in which both data and processing are distributed
among several sites.
FALSE. The definition refers to a distributed database management system (DDBMS). p554
3. Like any IT infrastructure, Business Intelligence (BI) architecture is composed of many
interconnected parts such as data, people, technology, and process that work together to
facilitate and enhance a business management and governance.
TRUE. p592
4. In the data preparation phase of data-mining, identifying, cleaning any data impurities and
integrating data are the main tasks executed by data-miners.
FALSE. In this phase, the data has already been integrated into the data warehouse. The data
warehouse usually is the target for data-mining operations.
5. The characteristic of big data that refers to the changes in the meaning of the data based on
context is known as veracity.
FALSE. This is known as variability.
6. The three basic Open Database Connectivity (ODBC) architectures are ODBC driver that
communicates directly to the DMBS, a high-level ODBC API, and a driver manager in charge of all
database connections.
TRUE. Components, not architectures (?) p683
7. A server side extension is a program that interacts directly with the web browser to handle
specific types of requests and display the results.
FALSE. Server side extensions interact directly with the web server. p693
8. All of the following (delivering, testing, training, organising and monitoring) except testing are
the managerial role of a Database Administrator.
FALSE. Testing is included. p733
9. Backup and recovery, system support, security auditing and monitoring, and performance
auditing and monitoring are the four main areas of DBMS operations.
, FALSE. Performance monitoring and tuning (not auditing). p742
10. Serialisability is a transaction property ensuring that once transaction changes are done and
committed, they cannot be undone or lost, even in the event of a system failure.
FALSE. Serialisability is the property in which the selected order of concurrent transaction
operations creates the same final database state that would have been produced if the
transactions had been executed in a serial fashion. The question refers to Durability. p487
2018-6 SECTION B
Question 2 [20]
2.1. Shopcheap is a South Africa (SA) based company. They also have other branches in different
African countries like Nigeria and Angola. Shopcheap database has an EMPLOYEE table
containing the following attributes: EM_NAME, EMP_SALARY, EMP_ADDRESS, EMP_BRANCH,
EMP_DEPARTMENT, EMP_DOB. The employees’ data are distributed over three locations:
South Africa, Nigeria, and Angola. The table is divided by location, that is, the South African
employees’ data are stored in fragment A, Nigeria employees’ data are stored in fragment B
and Angola employees’ data are stored in fragment C. See Figure 1 below.
As a newly graduated Database Administrator (DBA) employed by Shopcheap to work with their
IT team, one of the managers approached you to assist him in listing all employees born on the
10 of December 1988. Given the above scenario, do the following.
a. Name and discuss the three levels of distributed transparency. [6]
Fragmentation transparency is the highest level of distribution transparency. The end
user or programmer does not need to know that a database is partitioned. Therefore,
neither fragment names nor fragment locations are specified prior to data access.
Location transparency exists when the end user or programmer must specify the
database fragment names but does not need to specify where those fragments are
located.
Local mapping transparency exists when the end user or programmer must specify
both the fragment names and their locations.
b. Use the request above to illustrate the use of various transparency levels. Do this by writing
three different cases of SQL query with each case conforming to each level of distribution
transparency. [14]
, Case 1 – Fragmentation transparency:
SELECT * FROM EMPLOYEE WHERE EMP_DOB = ’10-DEC-1988’;
Case 2 – Location transparency:
SELECT * FROM A WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM B WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM C WHERE EMP_DOB = ’10-DEC-1988’;
Case 3 – Local mapping transparency:
SELECT * FROM A NODE SAF WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM B NODE NGA WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM A NODE ANG WHERE EMP_DOB = ’10-DEC-1988’;
Question 3 [14]
3.1. Khumo Ndlovu is an inventory manager for a marketing research company based in Western
Cape. He wants to analyse the use of suppliers by the company departments. Khumo realised
that his friend Lesedi Ndindela has developed a spreadsheet-based data warehouse model that
she uses to analyse sales data. Khumo is interested in developing a data warehouse like Lesedi’s
so he can analyse orders by department, supplier and product.
a. Identify the appropriate fact table components. [2]
Khumo will require an ORDERS fact table.
b. Identify the appropriate dimension tables. [3]
Since Khumo wants to analyse by department, supplier and product, they will be the
required dimension tables: DEPARTMENT, SUPPLIER and PRODUCT.
c. Identify the attributes for the dimension tables. [2]
Each dimension table will have a primary key (such as DEPT_ID, SUPP_ID, PROD_ID) as well
as any additional useful attributes (see below).
d. Draw a star schema diagram for this data warehouse. [3]
exams
+ Solutions
(Databases II)
,2018-06
Section A
1. Horizontal data fragmentation is a process in a distributed database design that breaks a table
into subsets of columns from the original table.
FALSE. Horizontal fragmentation divides a relation (table) into subsets of rows not columns.
p575
2. Database Management System (DBMS) governs the storage and processing of logically related
data over interconnected computer systems in which both data and processing are distributed
among several sites.
FALSE. The definition refers to a distributed database management system (DDBMS). p554
3. Like any IT infrastructure, Business Intelligence (BI) architecture is composed of many
interconnected parts such as data, people, technology, and process that work together to
facilitate and enhance a business management and governance.
TRUE. p592
4. In the data preparation phase of data-mining, identifying, cleaning any data impurities and
integrating data are the main tasks executed by data-miners.
FALSE. In this phase, the data has already been integrated into the data warehouse. The data
warehouse usually is the target for data-mining operations.
5. The characteristic of big data that refers to the changes in the meaning of the data based on
context is known as veracity.
FALSE. This is known as variability.
6. The three basic Open Database Connectivity (ODBC) architectures are ODBC driver that
communicates directly to the DMBS, a high-level ODBC API, and a driver manager in charge of all
database connections.
TRUE. Components, not architectures (?) p683
7. A server side extension is a program that interacts directly with the web browser to handle
specific types of requests and display the results.
FALSE. Server side extensions interact directly with the web server. p693
8. All of the following (delivering, testing, training, organising and monitoring) except testing are
the managerial role of a Database Administrator.
FALSE. Testing is included. p733
9. Backup and recovery, system support, security auditing and monitoring, and performance
auditing and monitoring are the four main areas of DBMS operations.
, FALSE. Performance monitoring and tuning (not auditing). p742
10. Serialisability is a transaction property ensuring that once transaction changes are done and
committed, they cannot be undone or lost, even in the event of a system failure.
FALSE. Serialisability is the property in which the selected order of concurrent transaction
operations creates the same final database state that would have been produced if the
transactions had been executed in a serial fashion. The question refers to Durability. p487
2018-6 SECTION B
Question 2 [20]
2.1. Shopcheap is a South Africa (SA) based company. They also have other branches in different
African countries like Nigeria and Angola. Shopcheap database has an EMPLOYEE table
containing the following attributes: EM_NAME, EMP_SALARY, EMP_ADDRESS, EMP_BRANCH,
EMP_DEPARTMENT, EMP_DOB. The employees’ data are distributed over three locations:
South Africa, Nigeria, and Angola. The table is divided by location, that is, the South African
employees’ data are stored in fragment A, Nigeria employees’ data are stored in fragment B
and Angola employees’ data are stored in fragment C. See Figure 1 below.
As a newly graduated Database Administrator (DBA) employed by Shopcheap to work with their
IT team, one of the managers approached you to assist him in listing all employees born on the
10 of December 1988. Given the above scenario, do the following.
a. Name and discuss the three levels of distributed transparency. [6]
Fragmentation transparency is the highest level of distribution transparency. The end
user or programmer does not need to know that a database is partitioned. Therefore,
neither fragment names nor fragment locations are specified prior to data access.
Location transparency exists when the end user or programmer must specify the
database fragment names but does not need to specify where those fragments are
located.
Local mapping transparency exists when the end user or programmer must specify
both the fragment names and their locations.
b. Use the request above to illustrate the use of various transparency levels. Do this by writing
three different cases of SQL query with each case conforming to each level of distribution
transparency. [14]
, Case 1 – Fragmentation transparency:
SELECT * FROM EMPLOYEE WHERE EMP_DOB = ’10-DEC-1988’;
Case 2 – Location transparency:
SELECT * FROM A WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM B WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM C WHERE EMP_DOB = ’10-DEC-1988’;
Case 3 – Local mapping transparency:
SELECT * FROM A NODE SAF WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM B NODE NGA WHERE EMP_DOB = ’10-DEC-1988’
UNION SELECT * FROM A NODE ANG WHERE EMP_DOB = ’10-DEC-1988’;
Question 3 [14]
3.1. Khumo Ndlovu is an inventory manager for a marketing research company based in Western
Cape. He wants to analyse the use of suppliers by the company departments. Khumo realised
that his friend Lesedi Ndindela has developed a spreadsheet-based data warehouse model that
she uses to analyse sales data. Khumo is interested in developing a data warehouse like Lesedi’s
so he can analyse orders by department, supplier and product.
a. Identify the appropriate fact table components. [2]
Khumo will require an ORDERS fact table.
b. Identify the appropriate dimension tables. [3]
Since Khumo wants to analyse by department, supplier and product, they will be the
required dimension tables: DEPARTMENT, SUPPLIER and PRODUCT.
c. Identify the attributes for the dimension tables. [2]
Each dimension table will have a primary key (such as DEPT_ID, SUPP_ID, PROD_ID) as well
as any additional useful attributes (see below).
d. Draw a star schema diagram for this data warehouse. [3]
