Database Systems
Questions 1:
Mark 50
HK apartment rental agency is new startup company which aims to rent apartments to his clients. The
want to keep a record of their rentals and client information for which they have an initial database system
shown below.
Table 1: HK Apartment Rental Agency
Client No.
CL01
Client
Name
Apartment
No.
Apartment
Address
AN-34
Star Building,
Prince
Edward,
Kowloon,
Alice
Chan
AN-203
AN-18
CL10
Rent End
Date
Total
Rent
Landlord
No.
Landlord
Name
10/1/22
2000
LN 0AB
Iven Ho,
10/1/22
12/2/22
10000
LN 94B
Tony
Lam
05/1/22
6/2/22
12000
LN 0C4
Eva Liu,
12/1/22
20/3/22
17000
LN 0DE
Jacky
Chan
05/2/22
31/3/22
40000
LN 0DE
Jacky
Chan
Bob Li
AN-51
CL11
Moon Building,
Nam Cheong,
Kowloon
City Building,
Central, HK
Island.
Rent
Start
Date
2/1/22
Tommy
Lau
AN-89
Shinning
Towers, Tung
Chung,
Lantau Island
Seaview
Towers, Lohas
Park, New
Territories
For the database system shown in Table 1, answer the following questions
1) Perform the normalization and convert the database system shown in Table 1 to 1NF. Make sure
you write the 1NF version of the database system in table form with complete information.
2) Write the relational database schema of the 1NF database system. In your opinion what should be
primary keys in 1NF? List the primary keys and support your answer with reason reasons
3) Highlight all dependencies in 1NF database system and show in the graphical form.
4) Define 2NF in normalization. Normalize the 1NF database system and convert it into 2NF version
of the database system. Make sure you write the 2NF version of the database system in table form.
Show all tables of database system in 2NF with complete information.
5) Define 3NF in normalization. Normalize the 2NF database system and convert it into 3NF version
of the database system. Make sure you write the 3NF version of the database system in table form.
Show all tables of the database system in 3NF with complete information.
Q1 Answers
Question 2:
Mark 30
Maxims wants to keep record for all ingredients in stock. To make a basic cake, they need eggs, sugar, flour,
and baking powder. For each basic cake, it needs 4 eggs, 3 cups of sugar, 2 cups of flour and 1 tablespoon
of baking powder. Each time a cake is produced, the QOH (Quantity on Hand) in the table CAKE and
INGREDIENTS must be updated. The sample database contents are shown in the following tables:
Table name: CAKE
Cake Type
Basic Cake
QOH
288
Table name: INGREDIENTS
Ingredients
Eggs
Sugar
Flour
Baking powder
Unit
QOH
829
647
549
322
Cup
Cup
Tablespoon
Assume that the database starts with the values in the given tables above. Define transaction log. Write
the transaction log for adding new basic cakes in the database, using template given below. The number
of basic cakes to be added is according to the following formula:
Number of basic cakes to be added is 23.
ID
TRX
PREV
NEXT
NUM
PTR
PTR
1
1A3
NULL
2
2
1A3
1
3
3
1A3
2
4
4
1A3
3
5
5
1A3
4
6
6
1A3
5
7
7
1A3
6
NULL
ROW
OPERATION
TABLE
START
**START
TRANSACTION
COMMIT
** END
TRANSACTION
ID
ATTRIBUTE
BEFORE
AFTER
VALUE
VALUE
Q2 Answers
Question 3:
Mark 20
Below are the two transactions for Database. When both execute concurrently, there will possibly be
problems.
1) Find TWO scenarios with different type of problems in transactions execution. For each of the
scenarios, state the name of the problem, draw the transactions with timeline and mark the
operations where the problem would occur.
(14 marks)
Transaction 1
Read(A)
A = A +10
Read(B)
B=A+B
Write(B)
A=B+1
Write(A)
Transaction 2
Sum = 0
Read(B)
Read(A)
Sum = Sum + A + B
B = Sum
Write(B)
Read(A)
A = Sum * 10
Write(A)
Figure 3a: Transactions in Database
Concurrency control with locking can facilitate isolation of data items used in concurrently executing
transactions and avoid the problem above. However, poorly designed of locking could introduce deadlock
issue. Below are the transactions with locking.
2) Draw ONE scenario of transactions with timeline which will hit the deadlock problem. Mark the
operations where the problem would occur.
(6 marks)
Transaction 1
XLock(A)
Read(A)
A = A +10
XLock(B)
Read(B)
B=A+B
Write(B)
A=B+1
Write(A)
Transaction 2
Sum = 0
XLock(B)
Read(B)
XLock(A)
Read(A)
Sum = Sum + A + B
B = Sum
Write(B)
Read(A)
A = Sum * 10
Write(A)
Q3 Answers

Purchase answer to see full
attachment