SHAMBERERE TECHNICAL TRAINING INSTITUTE

NAME: KELVINE WAVOMBA

PAPER NAME: DATABASE MANAGEMENT SYSTEM

ADM NO: 7432

LECTURER: MR. OCHIENG OBALA

DATE OF SUBMISSION: 14TH JUNE, 2023

1. a) In a college, a student is identified by admission number, names which consists of the
first and the second name, date of birth and age which is derived from the date of birth,
their phone numbers which include that of the guardian. Each student belongs to a class
which is identified by a class name. Students are free to register in one or more clubs.
Each club is identified by a club name and club identification code. Represent the
information using and entity relationship diagram. PERSONAL
(8mks)

ADM NO PHNE NO
AGE GUARDIAN

ASSIGN
STUDENT CLASS
ED

NAME D.O.B
CLASS
NAME
REGIST
FIRST NAME SECOND
ERS
NAME

CLUB
NAME
CLUB

CLUBID
CODE

b) With the aid of an example, distinguish between a composite attribute and a derived
attribute as used in ER diagrams. (5mks)
A composite attribute is made up of more than one simple attribute.
- For example, a student’s address will contain the house number, city, address and
street name.
A Derived attribute is an attribute that is not present in the whole database but they are
derived from another attribute
- Age can be derived from the date of birth

1
 - Salary can also be derived from an employee

c) During a database maintenance session, it was discovered that the Data dictionary had
malfunctioned.
i. Explain two functions of this dictionary in a database (4mks)
 A database dictionary is used to catalog and communicate the structure and content of
data and provides a meaningful descriptions for individually named data objects.
 A database dictionary also provides a concise guide to understanding and using the data
in the database.

ii. State three components of this dictionary (3mks)

 It has a listing of data objects
 Has a detailed properties of data elements (data type, data size and indexes)
 It has an Entity- Relationship diagrams and other system level diagrams
 Has a reference data (classification and descriptive domains)
 Missing data and quality- indicator codes.
 Business rules, such as validation of a schema or data quality.
d) Explain each of the following terms as used in relational databases: (4mks)
i. Cardinality ratio;

It defines number of entities in one entity which can be associated with number of entities and
other set via a relationship.

ii. Participation constraint

It specifies the number of instances of an entity that can participate in a relationship.

2. a) The following narrative is a representation of information about banks. Use I to answer

the questions that follow
 There are multiple banks and each bank has many branches. Each branch has
multiple customers.

2
  Customers has a name, address that consists of house number, area and city and
one or more phone numbers.
 Customers have various types of accounts
 Some customers also had taken different types of loans from these bank branches
 Accounts has number, type and balance
 One customer can have multiple accounts and loans
i. Identify four entities in the narrative (2mks)
a) Banks
b) Branch
c) Customer
d) Account
ii. Draw and Entity-Relationship Diagram to represent the information (8mks)

C BANK R BRANCH
BANK NAME

BRANCH NAME
R

LOAN TYPE
CUSTOMER
NUMBER
R
NAME
ADDRESS
ACCOUNT

HOUSE NO AREA PHONE NO

CITY
TYPE BALANCE

b) Explain four techniques for deadlocks avoidance in database Management System

transactions

3
  Deadlock Prevention:
Deadlock prevention involves structuring the system in a way that makes it impossible for
deadlocks to occur. This can be achieved by using techniques such as: a. Resource Ordering:
Define a total ordering of resources and ensure that transactions always request resources in the
same order. This helps prevent the circular wait condition, a key factor in deadlock formation. b.
One-to-One Locking: Enforce a rule that a transaction can acquire only one lock at a time. By
ensuring exclusive access to a single resource, the possibility of circular wait is eliminated.

 Deadlock Detection and Recovery:

Deadlock detection techniques involve periodically scanning the system for the existence of
deadlocks and taking appropriate action. This can be done using algorithms such as: a. Wait-for
Graph: Maintain a wait-for graph, where transactions are represented as nodes and directed edges
indicate the wait relationship. If a cycle is detected in the graph, a deadlock is present, and
recovery measures can be initiated, such as aborting one or more transactions to break the cycle.
Timeout Mechanism: Set a timeout for each transaction to complete its execution. If a
transaction exceeds its allocated time, it can be aborted, and the resources it holds can be
released.

 Two-Phase Locking (2PL):

Two-Phase Locking is a concurrency control method that helps avoid deadlocks by ensuring
transactions acquire locks in two phases: the growing phase and the shrinking phase. In the
growing phase, a transaction can acquire locks but cannot release them until it reaches the
shrinking phase. This ensures that the transaction acquires all the necessary locks before
releasing any locks, reducing the chances of deadlock.

 Deadlock Avoidance using Resource Allocation Graph (RAG):

Resource Allocation Graph is a graphical representation of the resource allocation and the
waiting relationships between transactions and resources. It can be used for deadlock avoidance
by employing a technique called the Banker's algorithm. The algorithm analyses the potential
allocation of resources and determines if granting a request will result in a safe state or
potentially lead to a deadlock. If the request may cause a deadlock, it is denied to prevent it.

c) i) Outline two uses of database systems in hotel industry (2mks)

 A hotel database system is used to store and manage reservation information. It includes
details such as guest names, check-in/check-out dates, room types, rates, special requests,
and preferences.
 The database can generate reports and analytics on reservation patterns, occupancy rates,
revenue, and other key performance indicators, helping hotel managers make informed
decisions.
ii) Distinguish between weak and strong entities (4mks)

Strong Entities:
 Strong entities are entities that exist independently and have their own unique identifier.
 They have sufficient attributes to be uniquely identified in a database.

4
 Weak Entities
 Weak entities are entities that cannot exist or be uniquely identified without being associated
with another entity.
 They depend on a strong entity, known as the identifying or owner entity, for their existence.

d) i) Explain the term concurrency control as used in databases (2mks)

Concurrency control is a fundamental aspect of database management systems (DBMS) that

ensures the proper handling of concurrent transactions. Concurrency refers to the ability of
multiple transactions to execute simultaneously in a shared database environment. However,
allowing concurrent execution can lead to several issues, such as data inconsistencies and
integrity violations, known as concurrency anomalies.

ii) Distinguish between read phase and write phase as used in concurrency control
phase validation in a database. (4mks)

Read Phase: During the read phase, a transaction reads data from the database without modifying
it. The primary objective of this phase is to determine the data values that the transaction requires
for its operations. The transaction examines the database and reads the values of the required
data items into its local workspace or transaction buffer. These read values are typically used for
subsequent computations or comparisons during the write phase.

Write Phase: The write phase occurs after the read phase and involves modifying the database
based on the results of the transaction's computations or comparisons. In this phase, the
transaction applies its modifications to the database, which may involve inserting, updating, or
deleting data records. The transaction's local workspace or transaction buffer is used to
temporarily hold the changes made during this phase.
3. The following is cardinality relationship that exists between pairs of entities in a
database. Use it to answer the questions that follows;
Pairs of entities Relationship
Customers and sales 1:N
Customers and products M:N
Customers and shops M:N
Sales and products M:N
Shops and sales 1:N
Shops and products M:N
Draw the entity relationship that exists between tables (7mks

5
 SALES
CUSTOMERS

SHOPS

PRODUCTS

b) A car company dealing with importation of cars on behalf of clients is based in

Mombasa. Shipped vehicles characterized by; unique car identification number, car
weight, car type, destination and delivery date. Shipped vehicles are received at the
company go-downs. Each go-down is characterized; a unique go-down Id, location
and telephone number. Shipped vehicles make their way to the clients via standard
gauge railways or by road. Each client is characterized by; a unique client id, client’s
name, address and the types of transport used to deliver the cargo.
Draw an entity relationship diagram for the above information (7mks)

CAR G-DOWN ID LOCATION

CAR ID WEIGHT

TELEPHONE
SHIPPED CAR RECEIVE GO- DOWN
S

CAR TYPE

DELIVER
DESTINATION
Y
DELIVERY DT

CLIENT CLIENT ID

TRANSPORT CLIENT
TYPE NAME
6