STRATHMORE INSTITUE

DBIT
GROUP ASSIGNMENT
DATABASE SYSTEMS

DUE DATE: 25th July 2024 Time:

Instructions
1. Ensure should be done in groups as discussed in class of maximum 5 per group.
2. The assignment should be HANDWRITTEN
3. Ensure to indicate the admission numbers of the group members as well as the group

Question ONE (15 Marks)

The following tables form part of a database held in a relational DBMS:
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
where: Hotel contains hotel details and hotelNo is the primary key.
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary
key.
Booking contains details of bookings and (hotelNo, guestNo, dateFrom) forms the
primary key.
Guest contains guest details and guestNo is the primary key.

PART A:
Using the tables above, write SQL scripts to achieve the following:
a) List the names and addresses of all guests in London, alphabetically ordered by name.
(2 Marks)
b) List the price and type of all rooms at the Grosvenor Hotel. (3 Marks)

Page 1 of 5
PART B:
Describe the relations that would be produced by the following relational algebra operations:
a) hotelNo ( price > 50 (Room) ) (2 Marks)

b) Hotel.hotelNo = Room.hotelNo(Hotel X Room) (2 Marks)

c) hotelName (Hotel ⊳⊲ Hotel.hotelNo = Room.hotelNo (price > 50 (Room)) )

(2 Marks)
d) Guest ⊐⊲ (dateTo  ‘1-Jan-2007’ (Booking)) (2 Marks)

e) Hotel ⊳ Hotel.hotelNo = Room.hotelNo (price > 50 (Room)) (2 Marks)

Page 2 of 5
Question TWO (15 Marks)
Read the following case study, which describes the data requirements for a video rental
company. The video rental company has several branches throughout the USA. The data held
on each branch is the branch address made up of street, city, state, and zip code, and the
telephone number. Each branch is given a branch number, which is unique throughout the
company. Each branch is allocated staff, which includes a Manager. The Manager is
responsible for the day-to-day running of a given branch. The data held on a member of staff is
his or her name, position, and salary. Each member of staff is given a staff number, which is
unique through out the company. Each branch has a stock of videos. The data held on a video is
the catalog number, video number, title, category, daily rental, cost, status, and the names of the
main actors and the director. The catalog number uniquely identifies each video. However, in
most cases, there are several copies of each video at a branch, and the individual copies are
identified using the video number. A video is given a category such as Action, Adult, Children,
Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a video is available
for rent. Before hiring a video from the company, a customer must first register as a member of
a local branch. The data held on a member is the first and last name, address, and the date that
the member registered at a branch. Each member is given a member number, which is unique
throughout all branches of the company. Once registered, a member is free to rent videos, up to
a maximum of ten at any one time. The data held on each video rented is the rental number, the
full name and number of the member, the video number, title, and daily rental, and the dates the
video is rented out and returned. The rental number is unique throughout the company.
Required:
i) From the requirements narrative above, identify ALL the entities as well as attributes for
each of the entities (5 Marks)

iv) Using the entities and attributes identified in (i) above, draw a ER- Diagram to clearly
capture the requirements in the narrative above. Write down your assumptions and indicate all
entity relationship cardinality. (Use Crow’s foot notation). (10 Marks)

Page 3 of 5
Question THREE (15 Marks)
a) There are three transaction anomalies, namely the lost update problem, the dirty read/ reading
uncommitted data problem and the inconsistent analysis problem. With relevant examples,
describe each of these. (6 Marks)
b) Consider the following case and use it to answer the questions that follow:
Mwambao, a client of Bank Account no. 200816 in Utamaduni Bank PLC is transferring Ksh.
12,500 from his account to Account no. 201384 run by one of his business associates. The
business associate (owner of account 201384) is also trying to settle a debt of goods supplied by
one of his suppliers and has thus issued an electronic transfer request for Ksh. 28,000 in favour
of his supplier. This e-transfer is to be effected immediately as there are anticipated funds that
should reach the account as soon.
Consider the following transaction schedule, assuming the bank has a rule that minimum
balances cannot go below zero.

From the above case, answer the following questions;

i) What property or properties have been contravened in the above schedule? (2 Marks)
ii) What anomaly might have occurred? (3 Marks)
iii) Rewrite the schedule correctly. (4 Marks)

Page 4 of 5
Question FOUR (15 Marks)
An agency called Gumbaru Agency supplies part-time/temporary staff to hotels within Kenya.
The table shown below displays sample data, which lists the time spent by agency staff working
at various hotels. The National Insurance Number (NHIF_NO) is unique for every member of
staff.

NHIF_NO Contract_No HrsPerWeek Employee_Name Hotel_No HotelLocation

N113567D CH6788 16 James Meno H225 Westlands

N189848A CH6788 24 Triza Atieno H225 Westlands
N749598D CH6789 28 Leah Chebet H44 Upper Hill
N113567D CH6789 16 James Meno H44 Upper Hill

Figure 1: Employees of Gumbaru Agency and their contracts to work at hotels

a) The relation above (Figure 1) is susceptible to update anomalies. Using the table, provide
examples of insertion, deletion, and modification anomalies. (6 Marks)

b) Normalize the relation (Figure 1) above, showing how you progress from 1NF through 2NF
to a set of 3NF named relations. At each stage show the primary key and any foreign keys of
each relation and state assumptions that you make about any of the relationships between the
columns of data. Give your relations meaningful names. (9 Marks)

Page 5 of 5