School of Computing and Mathematics

Examination Exams Session 3, 2018

ITC556 Database Systems
This paper is for Distance Education (Distance), Study Centre Melbourne, Study Centre
Sydney and Study Centre Brisbane students.

EXAM CONDITIONS:
NO REFERENCE MATERIALS PERMITTED
No calculator is permitted
No dictionary permitted
The student may NOT retain the question paper

WRITING TIME: 2 hours plus 10 minutes reading time

Writing is permitted during reading time

MATERIALS SUPPLIED BY UNIVERSITY:

1 x 12 page answer booklet
1 x General purpose answer sheet (GPAS)
MATERIALS PERMITTED IN EXAMINATION:

(No electronic aids are permitted e.g. laptops, phones)

Ruler - any type

2B Pencil Eraser

NUMBER OF QUESTIONS: 28

VALUE: 50%

INSTRUCTIONS TO CANDIDATES:
1. Enter your name and student number and sign the space provided below.
2. This is a closed book examination; therefore no written material, reference books or notes will
be permitted in the examination room.
3. The examination paper, together with all answer booklets, must be returned at the completion
of the examination.
4. Section 1 consists of 20 multiple choice questions, each worth 1.25 mark. The answers to these
questions must be marked on the GPAS supplied with a 2B pencil.
7. Section 2 consists of five short answer questions each worth 5 marks. Section 3 has 3 design
questions. The marks allocated to each question are indicated. Write your answers in the answer
booklet provided.
6. You should answer ALL questions.

Exams Session 3, 2018 – ITC556 Page 1 of 16

 STUDENT NAME: …………………………………………. STUDENT ID: ……………………….

SIGNATURE …………………………………………………………………………………………….

CALCULATOR USED (IF ANY) …………………...…………………………………………………

Exams Session 3, 2018 – ITC556 Page 2 of 16

PART A: MULTIPLE CHOICE (25 marks)

Instructions:

 For each question, select the single best response from the options given. Circle the
corresponding letter on your supplied GPAS.
 There is no negative marking for incorrect answer. Attempt all questions. All questions
have equal value (1.25 mark each).

1. A _____ is any key that uniquely identifies each row.

A) Candidate key
B) Foreign key
C) Special key
D) Super key

2. A(n) _____ join links tables by selecting only the rows with common values in
their common attribute(s).
A) attribute
B) natural
C) unique
D) foreign

3. Each table _____ represents an attribute.

A) row
B) column
C) value
D) name

4. _____ is defined as the condition in which all of the data in the database are consistent
with the real-world events and conditions.
A) Data quality
B) Data anomaly
C) Data integrity
D) Data Inconsistency

5. An XML database supports the storage and management of _____ XML data.
A) Structured
B) Multi-structured
C) Unstructured
D) Semi-structured

Exams Session 3, 2018 – ITC556 Page 3 of 16

 6. A _____ attribute is one that cannot be subdivided.
A) single-valued
B) composite
C) multivalued
D) simple

7. A table that has all key attributes defined, has no repeating groups, and all its attributes
are dependent on the primary key is said to be in _____.
A) 2NF
B) 3NF
C) 1NF
D) BCNF

8. BCNF can be violated only if the table contains more than one _____ key.
A) secondary
B) primary
C) candidate
D) foreign

9. A table is in 4NF if it is in 3NF, and _____.

A) all attributes are unrelated
B) no column contains the same values
C) it has no multivalued dependencies
D) all attributes must be dependent on the primary key

10. A _____ derives its name from the fact that a collection of multiple entries of the same
type can exist for any single key attribute occurrence.
A) transitive dependency
B) repeating group
C) partial dependency
D) primary key

11. Attribute A _____ attribute B if all of the rows in the table that agree in value for
attribute A also agree in value for attribute B.
A) derives from
B) controls
C) joins
D) determines

12. A(n) _____ join performs a relational product (also known as the Cartesian product) of
two tables
A) cross
B) full
C) natural
D) equii

Exams Session 3, 2018 – ITC556 Page 4 of 16

 13. A(n) _____ is a query that is embedded (or nested) inside another query.
A) alias
B) subquery
C) view
D) main query

14. A(n) _____ query specifies which data should be retrieved and how it should be filtered,
aggregated, and displayed.
A) UPDATE
B) INSERT
C) SELECT
D) COMMIT

15. In subquery terminology, the first query in the SQL statement is known as the _____
query.
A) inner
B) outer
C) join
D) sum

16. Complex _____ requirements may dictate data transformations, and they may expand
the number of entities and attributes within the design.
A) entity
B) information
C) design
D) processing

17. Ideally, an entity identifier is composed of _____ attribute(s).

A) two
B) one
C) three
D) six

18. A _____ entity has a primary key that is partially or totally derived from the parent entity
in the relationship.
A) strong
B) parent
C) weak
D) child

Exams Session 3, 2018 – ITC556 Page 5 of 16

 19. OLAP stands for.
A) Online analysis processing
B) Online analytical processing
C) Online aggregate processing
D) Online transaction processing

20. What do data warehouses support?

A) OLTP
B) OLAP
C) Both OPTP and OLAP
D) None of them

SECTION 2: SHORT ANSWER ( 25 marks)

Instructions:

 Use the answer booklet to answer the questions in this part.

 Attempt ALL questions. All questions have equal value (5 marks each).

1. What is data redundancy, and which characteristics of the file system can lead
to it?

Solution: Data redundancy exists when unnecessarily duplicated data are found in the
database. For example, a customer's telephone number may be found in the customer
file, in the sales agent file, and in the invoice file. Data redundancy is symptomatic of
a (computer) file system, given its inability to represent and manage data
relationships. Data redundancy may also be the result of poorly-designed databases
that allow the same data to be kept in different locations. (Here's another opportunity
to emphasize the need for good database design!)

2. When is a table in BCNF?

Solution: A table is in Boyce-Codd Normal Form (BCNF) when it is in 3NF and every
determinant in the table is a candidate key. For example, if the table is in 3NF and it
contains a nonprime attribute that determines a prime attribute, the BCNF
requirements are not met. (Reference the text's Figure 6.8 to support this
discussion.)This description clearly yields the following conclusions:

Exams Session 3, 2018 – ITC556 Page 6 of 16

  If a table is in 3NF and it contains only one candidate key, 3NF and BCNF are
equivalent.
 BCNF can be violated only if the table contains more than one candidate key.
Putting it another way, there is no way that the BCNF requirement can be
violated if there is only one candidate key.

3. What is a data warehouse, and what are its main characteristics? How does it
differ from a data mart?

Solution: A data warehouse is an integrated, subject-oriented, time-variant and non-

volatile database that provides support for decision-making.
Topic not included in 201990
The data warehouse is usually a read-only database optimized for data analysis and
query processing. Typically, data are extracted from various sources and are then
transformed and integrated—in other words, passed through a data filter—before
being loaded into the data warehouse. Users access the data warehouse via front-end
tools and/or end-user application software to extract the data in usable form. The data
mart is a subset of the data warehouse and is usually oriented to a specific business

4. What is OLAP, and what are its main characteristics?

Solution: OLAP stands for On-Line Analytical Processing and uses multidimensional
data analysis techniques. OLAP yields an advanced data analysis environment that
provides the framework for decision making, business modeling, and operations
research activities. Its four main characteristics are:

1. Multidimensional data analysis techniques

Topic not included in 201990
2. Advanced database support

3. Easy to use end user interfaces

4. Support for client/server architecture.

5. What are the key assumptions made by the Hadoop Distributed File System
approach?

Solution: HDFS is designed around the following assumptions:

High volume
Write-once, read-many
Streaming access
Fault tolerance

Exams Session 3, 2018 – ITC556 Page 7 of 16

 HDFS assumes that the massive volumes of data will need to be stored and retrieved.
HDFS assumes that data will be written once, that is, there will very rarely be a need
to update the data once it has been written to disk. However, the data will need to be
retrieved many times. HDFS assumes that when a file is retrieved, the entire contents
of the file will need to be streamed in a sequential fashion. HDFS does not work well
when only small parts of a file are needed. Finally, HDFS assumes that failures in the
servers will be frequent. As the number of servers increases, the probability of a
failure increases significantly. HDFS assumes that servers will fail so the data must be
redundant to avoid loss of data when servers fail.

SECTION 3: DESIGN QUESTIONS ( 50 marks)

Instructions:

 Use the answer booklet to answer the questions in this part.

 Attempt ALL questions. All questions carry different marks.

1. Given the following business scenario, create a Crow’s Foot ERD using a
specialization hierarchy if appropriate. (15 Marks)

Two-Bit Drilling Company keeps information on employees and their insurance

dependents. Each employee has an employee number, name, date of hire, and title.
If an employee is an inspector, then the date of certification and the renewal date
for that certification should also be recorded in the system. For all employees, the
Social Security number and dependent names should be kept. All dependents must
be associated with one and only one employee. Some employees will not have
dependents, while others will have many dependents.

Solution:

The data model for this solution is shown in figure below.

FIGURE : Two-Bit Drilling Company ERD

Exams Session 3, 2018 – ITC556 Page 8 of 16

 In this scenario, a specialization hierarchy is appropriate because there is an
identifiable type or kind of employee (Inspectors), and additional attributes are
recorded that are specific to just that kind or type.

2. The dependency diagram in figure below indicates that authors are paid royalties for
each book that they write for a publisher. The amount of the royalty can vary by
author, by book, and by edition of the book. (7.5+7.5=15 Marks)

NOTE: If you find that drawing it difficult, please show dependencies by using
braces .For example functional dependency of ISBN->BookTitle can be
written as ISBN(BookTitle).

Figure: Book royalty dependency diagram

Exams Session 3, 2018 – ITC556 Page 9 of 16

 a. Based on the dependency diagram, create a database whose tables are at
least in 2NF, showing the dependency diagram for each table.
Solution:
The normalization results are shown in Figure below.

Figure: The 2NF normalization results for Question 2.1

Exams Session 3, 2018 – ITC556 Page 10 of 16

 b. Create a database whose tables are at least in 3NF, showing the
dependency diagram for each table.
Solution:
The normalization results are shown in Figure below.

Exams Session 3, 2018 – ITC556 Page 11 of 16

 Figure: The 3NF normalization results for Question 2.2

Exams Session 3, 2018 – ITC556 Page 12 of 16

3. The structure and contents of the ConstructCo database are shown in Figure below.
Use this database to answer the following problems. (4x5=20 Marks)

1. Write the SQL code that will create the table structure for a table named
EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table
structure is summarized in the table below. (Note that the JOB_CODE is the FK
to JOB.)

ATTRIBUTE (FIELD) NAME DATA DECLARATION

EMP_NUM CHAR(3)

EMP_LNAME VARCHAR(15)

EMP_FNAME VARCHAR(15)

EMP_INITIAL CHAR(1)

Exams Session 3, 2018 – ITC556 Page 13 of 16

 EMP_HIREDATE DATE

JOB_CODE CHAR(3)

Solution:

CREATE TABLE EMP_1 (

EMP_NUM CHAR(3) PRIMARY KEY,

EMP_LNAME VARCHAR(15) NOT NULL,

EMP_FNAME VARCHAR(15) NOT NULL,

EMP_INITIAL CHAR(1),

EMP_HIREDATE DATE,

JOB_CODE CHAR(3),

FOREIGN KEY (JOB_CODE) REFERENCES JOB);

2. Having created the table structure, write the SQL code to enter the first two rows
for the table shown in Figure below.

Figure : The contents of the EMP_1 table

Solution:

INSERT INTO EMP_1 VALUES (‘101’, ‘News’, ‘John’, ‘G’, ’08-Nov-00’, ‘502’);

INSERT INTO EMP_1 VALUES (‘102’, ‘Senior’, ‘David’, ‘H’, ’12-Jul-89’, ‘501’);

Exams Session 3, 2018 – ITC556 Page 14 of 16

3. Assuming the data shown in the EMP_1 table have been entered, write the SQL
code that will list all attributes for a job code of 502.

Solution:

SELECT *

FROM EMP_1

WHERE JOB_CODE = ‘502’;

4. Write the SQL code that will save the changes made to the EMP_1 table.
Solution:

COMMIT;

5. Write the SQL code to change the job code to 501 for the person whose employee
number (EMP_NUM) is 107. After you have completed the task, examine the
results, and then reset the job code to its original value.

Solution:

UPDATE EMP_1

SET JOB_CODE = ‘501’

WHERE EMP_NUM = ‘107’;

To see the changes:

SELECT *

FROM EMP_1

WHERE EMP_NUM = ‘107’;

To reset, use

ROLLBACK;

Exams Session 3, 2018 – ITC556 Page 15 of 16

 END OF EXAMINATION

Exams Session 3, 2018 – ITC556 Page 16 of 16