Technology

.

My First Banking System
The application described here is partly based on a real-life banking system, but simplifications
and changes have been made. To help you appreciate the privacy implications of a
universal identification scheme, all clients and staff of the bank are identified throughout
the application by their tax file number (taxNr). Thus, all bank customers and personnel are
taxpayers.
An information system is required to manage accounts and staff records for Oz Bank, which
has branches at various locations. Each branch is standardly identified by its branch
number but also has a unique name. The first table is an extract from staff records of Oz
Bank. Each employee works at exactly one branch and has at most one phone listed. The
mark “?” denotes a null value. The mark “…” indicates “etc.” (other instances exist but are
not shown here).
BranchNr Branch name Emp taxNr Emp name Emp phone
1 Uni. of Qld 200
390

Jones E
Presley E

3770000
?

2 Toowong Central 377

Jones E

?

3 Strathpine 222

Wong M

2051111

Within the one branch, each account has a unique serial number, but different accounts in
different branches may have the same serial number. Account users are identified by their
taxNr, but also have a name and possibly a phone number (see the second table).
QUEENSLAND UNIVERSITY OF TECHNOLOGY
Business Process Management Discipline
IAB201: Modelling Information Systems
IAB201 Tutorial Worksheet © A. Polyvyanyy
2
Account User
BranchNr SerialNr TaxNr Name Phone
1
1
2
2
3
55
66
55
77
44
200
311
199
199
377
300
Jones E
Jones T
Megasoft
Megasoft
Jones E
Wong S
3770000
3770000
3771234
3771234
?
2051111
Each account is a passbook account. Five sample passbook entries are shown. For each
account, transactions are numbered sequentially 1, 2, 3, … Dates are formatted
day/month/ year. For simplicity, assume each transaction is either a deposit (DEP) or
withdrawal (WDL). In practice, other types of transactions are possible (e.g. interest and
fees). The balance column shows the account balance after the transaction is executed.
Although the balance is derivable, for efficiency purposes the balance is stored as soon as it
is derived. For example, this speeds up production of monthly statements for the bank’s
customers (Oz Bank has a few million customers who average several transactions each
month). This derive-on-update (eager evaluation) decision contrasts with our normal
derive-on-query (lazy evaluation) policy.
OZ BANK
BranchNr SerialNr Branch name: Uni. of Qld
Users: Jones E; Jones T 1 55
TranNr Date Deposit Withdrawal Balance
1
2
3
3/1/90
5/1/90
5/1/90
1000
200
100
1000
800
700
OZ BANK
BranchNr SerialNr Branch name: Uni. of Qld
Users: Megasoft
1 66
TranNr Date Deposit Withdrawal Balance
1
2
10/2/90
10/2/90
2000
500
2000
1500
QUEENSLAND UNIVERSITY OF TECHNOLOGY
Business Process Management Discipline
IAB201: Modelling Information Systems
IAB201 Tutorial Worksheet © A. Polyvyanyy
3
OZ BANK
BranchNr SerialNr Branch name: Toowong Central
Users: Megasoft 2 55
TranNr Date Deposit Withdrawal Balance
1
2
3
4
23/1/90
7/2/90
10/2/90
2/3/90
9000
5000
2000
5000
9000
14000
12000
7000
OZ BANK
BranchNr SerialNr Branch name: Strathpine
Users: Wong S
3 44
TranNr Date Deposit Withdrawal Balance
1
2
5/1/90
12/1/90
100
600
100
700
(a) Draw a conceptual schema diagram for this UoD. Make use of an entity type
indicating the type of transaction: Trantype (code) {‘DEP’, ‘WDL’). Include the
account balance fact type on the diagram; mark it with “**” since it is derivable but
it is required to be stored. Express the derivation rule as clearly as you can.
(b) Consider the fragment of your conceptual schema that captures the kind of
information required for deposits and withdrawals (only two fact types are involved).
Transform this subschema into an equivalent subschema that uses two different fact
types, removing the entity type Trantype altogether.
(c) In a realistic banking application there are several kinds of transactions besides
deposits and withdrawals. In such a situation is it better to include Trantype as an
object type, or to extend the alternative approach you proposed in (b)?
OZ BANK
BranchNr SerialNr Branch name: Toowong Central
Users: Jones E
2 77
TranNr Date Deposit Withdrawal Balance
1 3/1/90 500 500

.


Order This Paper Now