SECTION A (40 Marks) (This section is compulsory)

QUESTION I

What you understand by the following concepts as applied to database systems? (a) Distributed
databases

(3 marks)

✓ (b) Conceptual database design

(c) Relational lossless decomposition

QUESTION 2

(a) Explain what is meant by the term data independence. (b) Describe the types of data
independence you know.

QUESTION 3

(Imark)

(2 marks)

✓ (a) Define a data model and state its roles in database design. (b) Describe the major building
blocks of data models.

(3 marks)

(4 marks)

QUESTION 4

Explain the roles of the following phases in query processing; (a) Parser

(3 marks)

✓ (b) Shared pool check

(c) Hard parsing

QUESTION 5

Explain what you understand by file processing system in data management and state its
disadvantages.

(5 marks)

QUESTION 6

✓Different people are involved in the design, use and maintenance of any database. Describe the
different categories of individuals whose roles involve the day-to-day use of a database. (5 marks)

Page 2 of 6

QUESTION 7
With examples, explain the different types database query languages you know.

QUESTION 8

(4 marks)

a) Explain the term data abstraction in database design.

(2 marks)

(b) Explain the different levels of data abstraction in database systems.

(3 marks)

QUESTION 9

(a)

Distinguish between relational algebra and relational calculus in relational databases.

(2 marks)

(b)

Given the relations below:

Reg NO Address Gender

001

Soroti

002

Mbarara M

003

Masaka

Name

Akello

Tumusiime

Hostel

Mitchell Mary Stuart

What is the Cartesian product between M and N?

(3 marks)
Page 3 of 6

(Total 60 Marks)

SECTION B Answer QUESTION 10 and any other TWO (2) Questions from this section.

QUESTION 10 (COMPULSORY)

(a) With an illustration, explain what is meant by the following terms in database design:

(i) Entity

(ii) Composite attribute

(iii) Multi-valued attributes

(iv) Derived attribute

(v) Ternary relationship

(10 marks)

(b) A College offers a program in different streams. In each stream various students are enrolled.
Each stream is divided into 8 semesters and each semester is divided into two sections. In each
semester various subjects are taught. One subject is taught by at most one lecturer and one lecturer
can teach more than one subject.

Draw an ERD to represent the above information.

(10 marks)

Page 4 of 6

QUESTION 11

DBMS Model is defined as the logical plan and arrangement of a database which describes how the
data info will be stored, retrieved and modified in a database management system. This DBMS Model
illustrates the coherent structure of a database server that consists of the relationships and
limitations which helps to regulate how data/information can be preserved and fetched later. Each of
these data models mostly can be signified by a supplementary database illustration. There are
different kinds of DBMS Models which match different phases of the database design procedure.

(a) In the light of the above information, describe any three main DBMS Models used in database
design outlining the basic features of each of them. (15 marks)

(b)

Describe the DBMS functions that are used to guarantee the integrity and consistency of the data in
the database.

(5 marks)

(a)

QUESTION 12

In transaction processing, a transaction goes through many different phases throughout its life cycle.
With reference to the above statement, explain the term transaction processing
(2 marks)

✓ (b)

Describe the various phases of a transaction

(12 marks)

✓ (c)

What are the ACID properties of a transaction in database design?

(6 marks)

QUESTION 13

(a) Distinguish between the following terms, giving examples where necessary: (6 marks)

(i) Primary key and foreign key

(ii) Candidate key and super key

(iii) Alternate key and surrogate key

(b)

You have been assigned the role of designing a database system for a particular company.

(i) As a student with some knowledge of database systems, what good database design principles
would you apply when designing this database? (ii) Describe the major guidelines you would use to
meet this task.

(2 marks)

(12 marks)

QUESTION 14

Distributed databases come with considerable benefits compared to centralized databases. (a) What
are the major advantages of distributed databases over centralized ones? (b) Explain what is meant
by the following terms as used in distributed databases:

(7 marks)

(5 marks)

(i)

Distributed query processing

(ii)

Distributed transactions

(iii) Replication

(iv) Transaction transparency

(v) Fragmentation
(e) Distinguish between homogeneous and heterogeneous databases.

(2 marks)

(d) Distributed transactions may or may not always be necessary in real life situations. With
examples, describe the circumstances under which distributed transactions are always:

(i)

Necessary

(ii) Not necessary

(6 marks)

g) Which of the following is not a Schema? Database Schema

B. Physical Schema C. Critical Schema

D. Logical Schema

h) Which of the following is not a function of a DBA?

Network maintenance

B. Routine maintenance

Schema definition

D. Authorization for data access

i) Which of the following is a Data Model? Entity-Relationship Model

B. Relational data model

C. Object-Based data model

D. All of the above

j) Minimal super keys are called.......

A. Schema keys

B. Candidate keys C. Domain keys

D. Attribute keys

k) A command to remove a relation from an SQL database is.

A. Delate table <table name>

B. Drop table <table name>

C. Erase table <table name>

D. Alter table <table name>

1) A relation that has no partial dependencies is in which normal form?

B. Second

C. Third

D. BCNF

m) The property of transaction which ensures that all or none of the operations of the transaction

are reflected properly in a database is called.......

A. Atomicity

B. Durability

C. Isolation

D. Consistency

n) An entity type whose existence depends on another entity type is called......

A. Strong

BWeak

C. Co-dependent

D. Variant

o) A relationship between instances of a single entity type is called a

A. Ternary Unary C. Primary D. Binary

relationship

QUESTION 2 (Structured-type Questions) - Total 25 Marks

(a) Database administrators (DBAS) are some of the many databases system key players. In reference
to the above statement, describe the roles of DBAS in database systems. [5 marks]

(b) End-users are the people whose jobs require access to the database for querying, updating and
generating reports. Describe the different categories of end-users in database systems.

[4 marks] (c) A database environment is a collective system of components that comprises and
regulates the a group of data, its management, and its use. In view of the above statement, describe
the

Hard ware

major components of a database.

pata Softume procedures utilites End users

[10 marks]

(d) Explain the term data abstraction as used in database systems.

[2 marks]

[4 marks]

QUESTION 1 (Multiple Choice Questions) - One mark each (Write down the letter of the correct
alternative)

a) A Database Management system (DBMS) is....... A. Collection of interrelated data

BCollections of programs to access data

C. Collection of data describing one particular enterprise D. All of the above

b) Which of the following is not a level of data abstraction?

A. Physical level BCritical level C. Logical level

D. View level

c) The disadvantage of file systems storing data is....... A. Data redundancy and inconsistency

B. Difficulty in accessing data

C. Data isolation

D. All of the above

d) In an entity relationship diagram, rectangles represent....

Entity sets

B. Attributes

C. Database

D. Tables

e) Which of the following is not a storage manager component?

A. Transaction Manager

B. Logical Manager

C. Buffer Manager D. File Manager

f) Data manipulation Language enables users to.....

A. Retrieve information stored in database

B. Insert new information into the database

C. Delete information from the database D Do all of the above

Total

QUESTION 6

(a) (i) Distinguish between the terms database anomalies and database normalization.
[2 Marks]

(ii) What are the different database types of anomalies that may happen during database design?

16 Marks]

(b) (i) Explain the Boyce-Codd Normal Form (BCNF) in database normalization. (ii) What are the
differences between BCNF and 4NF?

[2 Marks] 15 Marks]

SECTION C: (Practice-Oriented questions) - 15 Marks per question (Choose any 1 or 2 questions in

QUESTION 7

(a) Explain what you understand by the terms, Candidate key, Super key and Primary key as applied
in database systems. [6 marks] (b) United Parcel Service (UPS) company prides itself on having up-to-
date information on the processing and current location of each shipped item. To do this, UPS relies
on a company- wide information system. Shipped items are the heart of the UPS product tracking
information system. Shipped items can be characterized by item number (unique), weight,
dimensions, insurance amount, destination, and final delivery date. Shipped items are received into
the UPS system at a single retail center. Retail centers are characterized by their type, uniqueID, and
address. Shipped items make their way to their destination via one or more standard UPS
transportation events (i.e., flights, truck deliveries). These transportation events are characterized by
a unique scheduleNumber, a type (e.g, flight, truck), and a deliveryRoute.

Required:

Draw an Entity Relationship diagram (ERD) that captures this information about the UPS system
clearly indicating all the entities, attributes and relationships and cardinalities.

19 marks]

QUESTION 3

SECTION B: (Essay-type questions)-15 marks per Question

(Choose any 2 or 3 questions, see instructions on cover page)

Transaction processing in database systems is an act that ensures that related data is added to or
deleted from the database simultaneously, thus preserving data integrity in your application.
Transaction processing uses certain key properties in order to ensure accuracy, completeness, and
data integrity.

(a) In reference to the above statements, explain the key properties of a database transaction. [8
Marks] (b) (i) Explain the term concurrency control as applied in Database management transactions.
[2 Marks] (ii) When multiple transactions execute concurrently in an uncontrolled manner, then it
might lead to concurrency problems in a database environment. Describe the different concurrency
problems that can occur in database management systems.

QUESTION 4

[5 Marks]

[1 mark]
[5 marks]

(a) (i) Define the term data management as used in database systems (ii) What are the benefits of
good data management in database systems? (b) Explain the term file system approach in relation to
data management. (c) What are the disadvantages of using file systems in managing data over
database systems.

QUESTION 5

[2 marks]

[7 marks]

There are a number of different types of database management systems, also referred to as DBMS
models. Each one represents a somewhat different approach to organizing data in a systematic

manner.

(a) Using examples, explain the term database management system (DBMS) with reference to the [3
Marks] above context.

(b) In view of the above context, describe using illustrations the major DBMS models used in [12
Marks] database design.

QUESTION 8

(a) Explain what you understand by each of the following terms as used in Relational Database
Management System (RDBMS).

(i) Integrity Constraints

(ii) Relational Algebra

(iii) Relational Calculus

[1 Mark each]

(b) Explain Cartesian product operation from mathematical theory as used in relational databases.

(c) Consider the relations below:

Bank_Loan

[2 Marks]

Branch Name Loan Number

Amount (USD)

Soroti City

L-001

1,000

Mbale City

L-002
2,000

Soroti City

L-003

5,000

Mbale City

L-004

1,500

Borrower

Customer Name

Loan Number

Wilberforce

L-001

Mariam

Tabisa

Joel

L-002

L-003

L-005

(i) Given that to select all attributes from the cross product of both relations, the following

queries are used:

Relation algebra query: (Loan X Borrower)

SQL: Select * from Loan, Borrower,

What would be the result of the above Query?

[5 marks]

(ii) Write down both a relational algebra query and SQL to select all attributes after the cross

product of both relations, where Branch Name is Mbale City.

(iii) What would be the result of the query in (ii) above?

[2 Marks]

[3 Marks]