COMP3311 Final Exam 23T1

The University of New South Wales COMP3311 Database Systems Final Exam 23T1 Exam Database

Database Systems

The database for the exam is concerned with banking. It contains information about branches, customers, accounts and transaaction, and the relationships between them. The following ER design gives an overview of this database:

The SQL schema below gives all of the implementation details.

Notes on the schema:

• all tables have a unique integer primary key called id
• bank branches are an archaic concept where accoutns are held
• there is at most one branch in any suburb
• a customer can hold one or more accounts, possibly at different branches
• an account is a pot of money which is held by by one or more customers
• a transaction is an activity on an account by a customer on a given day
• transactions are either deposits, withdrawls or transfers
• all transactions involve an amount of money

-- COMP3311 23T1 Exam Database - Banking

create table Customers (
	id       integer,
	given    text not null,   -- given name
	family   text not null,   -- family name
	lives_in text not null,   -- suburb
	primary key (id)
);

create table Branches (
	id       integer,
	location text not null,    -- suburb
	assets   integer not null, -- combined balance on all accounts
	primary key (id)
);

create table Accounts (
	id      integer,
	balance	integer not null check (balance >= 0),
	held_at integer not null references Branches(id),
	primary key (id)
);

create table Held_by (
	customer integer references Customers(id),
	account  integer references Accounts(id),
	primary key (customer,account)
);

create type Transaction_Type as enum
	('deposit','transfer','withdrawal');

create table Transactions (
	id      integer,
	ttype   Transaction_type not null,
	tdate   date not null,
	actor   integer not null references Customers(id),
	amount	integer not null check (amount > 0),
	source  integer references Accounts(id),
	dest    integer references Accounts(id),
	primary key (id),
	constraint ttypes check (
	 (ttype = 'deposit' and source is null and dest is not null)
	 or
	 (ttype = 'transfer' and source is not null and dest is not null)
	 or
	 (ttype = 'withdrawal' and source is not null and dest is null)
	)
);

The data is available in a PostgreSQL dump file: bank.dump.

You should familiarise yourself with the schema before proceeding to solve the queries. It would also be useful to examine the database contents to ensure that you understand what all of the data represents.