Comsec Share Portfolio

1
Comsec Share Portfolio

• By Zeeshan YUSUF

2
Database Description
This database is based on the Commonwealth
Securities database located at Http//www.comsec.c
om.au. It records detailed information about the
companies trading on the Australian Stock
Exchange. It also contains financial information
about the company allowing clients to make
informed investment decisions. Information about
each client, including their bank account and
personal details are stored in the database. Each
time a client makes either a Buy or a Sell
transaction on a particular security, the
information relating to this transaction is
recorded into the Transactions table. Basic
information about the sector in which the company
is operating in is also recorded and available
for view. Users are able to conduct searches
based on set criteria in order to analyse their
previous transactions.
3
Entity Relationship Diagram
COMPANY
TRANSACTIONS
CoSymbol SecIndex CoTradeName CoACN CoStatus CoMn
gDir CoPERatio CoDivYield CoBeta Co52WkHigh Co52Wk
Low
CLIENT
ACCOUNT
TransReceipt AccID CoSymbol TransType TransQuant
ity TransLimitPrice
ClientID ClientFirstname ClientLastname ClientLand
line ClientAddress ClientSuburb ClientEmail
AccID BankID AccBankAct AccPassword
SECTOR
BANK
SecIndex SecName SecValue
BankID BankName BankAddress
4
One to Many Relationship
COMPANY
Literal DescriptionOne sector (for example,
Health Care) is able to have many companies
contained within (for example, the companies
Symbion Health, Sigma Pharma etc. are all part of
Health Care).
CoSymbol SecIndex CoTradeName CoACN CoStatus CoMn
gDir CoPERatio CoDivYield CoBeta Co52WkHigh Co52Wk
Low
Primary Key
SecIndex SecName SecValue
XMJ Materials 12980
XHJ Health Care 8292
XPJ Property Trusts 6565
Foreign Key
SECTOR
CoSymbol SecIndex CoTradeName CoACN
ABS XDJ ABC Learning 079736664
TLS XTJ Telstra Limited 040004548
ENG XTJ Engin 968492488
SecIndex SecName SecValue
5
Many to Many Relationship
ACCOUNT
Literal DescriptionOne account may have many
clients (as in a financial institution or
joint-partner account). One Client may have many
different accounts.
AccID BankID AccBankAct AccPassword
AccID BankID AccBankAct
407334 1 2658945
454854 2 6513564
235645 1 4554688
AccID ClientID
407334 10000
454854 10000
235645 10005
CLIENT
ClientID ClientFirstname ClientLastname ClientLand
line ClientAddress ClientSuburb ClientEmail
ClientID ClientFirstName ClientLastName
10000 Zeeshan Yusuf
10001 Amanda Hadley
10005 Sean Boange
6
Simple Query
Literal DescriptionFind all clients who live in
the Castle Hill suburb. SQL Query SELECT FROM
comsec_client WHERE clientsuburb'CASTLE
HILL' Result
ClientID ClientFirstName ClientLastName ClientLandLine ClientAddress ClientSuburb ClientEmail
10000 Zeeshan Yusuf [phone] 28 Balintore Drive CASTLE HILL zyusuf_at_it.uts.edu.au
10001 Amanda Hadley [phone] 85 Old Castle Hill Road CASTLE HILL mandy_at_hotmail.com
10005 Sean Boange [phone] 1 Vivien Ave CASTLE HILL sean_at_hotmail.com
7
Natural Join Query
Literal DescriptionFind which sectors the
companies with a beta ratio of greater than .85
are operating in. SQL Query SELECT CoSymbol,
CoTradeName, SecName, CoBeta FROM comsec_company
natural join comsec_sector WHERE cobeta gt
85 Result
CoSymbol CoTradeName SecName Beta
ABS ABC Learning Consumer Discretionary 86
ZFX Zinifex Healthcare 86
TLS Telstra Telecommunications 91
8
Cross Product Equivalent
Literal DescriptionUsing cross product
notation, find which sectors the companies with a
beta ratio of greater than .85 are operating
in. SQL Query SELECT CoSymbol, CoTradeName,
SecName, CoBeta FROM comsec_company,
comsec_sector WHERE comsec_company.secindexcomse
c_sector.secindex AND cobeta gt 85 Result
CoSymbol CoTradeName SecName Beta
ABS ABC Learning Consumer Discretionary 86
ZFX Zinifex Healthcare 86
TLS Telstra Telecommunications 91
9
Group By / Having Query
Literal DescriptionShow the banks which have
greater than 2 clients holding accounts with
them. SQL Query SELECT bankname, count() FROM
comsec_bank, comsec_account WHERE
comsec_account.bankidcomsec_bank.bankid GROUP
BY bankname HAVING count()gt2 Result
BankName Count
St. George Bank 3
Commonwealth Bank 4
10
Sub Query
Literal DescriptionFind and display the sector
which has the highest value. SQL Query SELECT
FROM comsec_sector WHERE secvalue gt all (SELECT
secvalue FROM comsec_sector) Result
SecIndex SecName SecValue
XMJ Materials 12980
11
Self Join Query
Literal DescriptionList all the Account numbers
which trade a particular security more than
once. SQL Query SELECT Order1.AccID,
Order1.CoSymbol, Order1.TransReceipt as
Transaction1, Order2.TransReceipt as
Transaction2 FROM comsec_transactions order1,
comsec_transactions order2 WHERE
order1.accidcomsec_account.accID AND
order2.accID comsec_account.accID AND
order1.cosymbolorder2.cosymbol and
order1.transreceipt lt order2.transreceipt Result

AccID CoSymbol Transaction1 Transaction2
407334 PDN 154123 357823
407334 ABS 114567 195565
458425 MIG 154790 279904
125459 TLS 120222 205444
125459 PNA 215449 245889

12
CHECK Constraints (1)
Literal DescriptionLimits entry of data to the
integer data type only. SQL Constraint CREATE
TABLE Comsec_Transactions ( TransReceipt
INTEGER, ...
Literal DescriptionLimits the range of Client
IDs that will be accepted. SQL
Constraint CREATE TABLE Comsec_Client (... Clien
tID INTEGER NOT NULL, CONSTRAINT Comsec_Client
CHECK (ClientID gt10000 and ClientID lt 99999) ...
13
CHECK Constraints (2)
Literal DescriptionLimits entry to one of three
options. SQL Constraint CREATE TABLE
Comsec_Company (... CoStatus VARCHAR(15) NOT
NULL, CONSTRAINT Comsec_Company CHECK (CoStatus
IN ('Trading', 'Halt', 'Suspended')) ...
Literal DescriptionLimits the number of
characters a field will accept before rejecting
INSERT. SQL Constraint CREATE TABLE
Comsec_Company ( CoSymbol VARCHAR(3) NOT
NULL, ...
14
Action Statements (1)
Literal DescriptionCreating a table with the
inclusion of the definition of the tables
primary key. SQL Action CREATE TABLE
Comsec_Sector ( SecIndex VARCHAR(3) NOT
NULL, SecName VARCHAR(50) NOT NULL, SecValue
INTEGER NOT NULL, CONSTRAINT Comsec_SectorPK
PRIMARY KEY (SecIndex) )
15
Action Statements (2)
Literal DescriptionRestricts the deletion of
linked tables using the On Delete RESTRICT
command. SQL Action CREATE TABLE
Comsec_Company ( CoSymbol VARCHAR(3) NOT
NULL, ... CoBeta INTEGER NOT NULL,
Co52WkHigh INTEGER NOT NULL, Co52WkLow INTEGER
NOT NULL, CONSTRAINT Comsec_CompanyPK PRIMARY
KEY (CoSymbol), CONSTRAINT Comsec_CompanyFK
FOREIGN KEY (SecIndex) REFERENCES Comsec_Sector
ON DELETE RESTRICT ... )
16
Use of View (1)
Literal DescriptionView a list of the number of
times of customers who have traded Telstra
shares. SQL Query CREATE VIEW TLStrade As SELECT
ClientFirstname, count() FROM comsec_client
natural join comsec_tradeacts natural join
comsec_transactions WHERE cosymbol'TLS' GROUP BY
clientfirstname SELECT FROM TLStrade Result
ClientFirstName Count
Chris 1
Zeeshan 3
Amanda 1
Tara 2
17
Use of a View (2)
Literal DescriptionSelect the client who has
traded TLS shares the most number of times. SQL
Query SELECT Clientfirstname, count FROM
tlstrade WHERE count gt all (SELECT count from
tlstrade) Result
ClientFirstName Count
Zeeshan 3