comsec share portfolio
DESCRIPTION
Comsec Share Portfolio. By Zeeshan YUSUF. Database Description. This database is based on the Commonwealth Securities database located at Http://www.comsec.com.au. - PowerPoint PPT PresentationTRANSCRIPT
Comsec Share PortfolioComsec Share PortfolioBy Zeeshan YUSUFBy Zeeshan YUSUF
Database DescriptionDatabase Description
This database is based on the Commonwealth Securities database located at Http://www.comsec.com.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.
Entity Relationship Entity Relationship DiagramDiagram
COMPANY
CoSymbolSecIndex*CoTradeNameCoACNCoStatusCoMngDirCoPERatioCoDivYieldCoBetaCo52WkHighCo52WkLow
SECTOR
SecIndexSecNameSecValue
TRANSACTIONS
TransReceiptAccID*CoSymbol*TransTypeTransQuantityTransLimitPrice
ACCOUNT
AccIDBankID*AccBankActAccPassword
CLIENT
ClientIDClientFirstnameClientLastnameClientLandlineClientAddressClientSuburbClientEmail
BANK
BankIDBankNameBankAddress
TRADEACTS
AccID*ClientID*
One to Many RelationshipOne to Many Relationship
CoSymbol SecIndex CoTradeName CoACN …
ABS XDJ ABC Learning 079736664
TLS XTJ Telstra Limited 040004548
ENG XTJ Engin 968492488
COMPANY
CoSymbolSecIndex*CoTradeNameCoACNCoStatusCoMngDirCoPERatioCoDivYieldCoBetaCo52WkHighCo52WkLow
SECTOR
SecIndexSecNameSecValue
Literal Description:One 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).
SecIndex SecName SecValue
XMJ Materials 12980
XHJ Health Care 8292
XPJ Property Trusts 6565
Primary Key
Foreign Key
Many to Many RelationshipMany to Many Relationship
AccID BankID AccBankAct …
407334 1 2658945
454854 2 6513564
235645 1 4554688
ClientID ClientFirstName
ClientLastName
…
10000 Zeeshan Yusuf
10001 Amanda Hadley
10005 Sean Boange
ACCOUNT
AccIDBankID*AccBankActAccPassword
CLIENT
ClientIDClientFirstnameClientLastnameClientLandlineClientAddressClientSuburbClientEmail
TRADEACTS
AccID*ClientID*
Literal Description:One account may have many clients (as in a financial institution or joint-partner account). One Client may have many different accounts.
AccID ClientID
407334 10000
454854 10000
235645 10005
Simple QuerySimple Query
Literal Description:Find 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 (02)9680923428 Balintore
DriveCASTLE HILL
10001 Amanda Hadley (02)9842520185 Old Castle
Hill RoadCASTLE HILL
10005 Sean Boange (02)98564451 1 Vivien Ave CASTLE [email protected]
om
Natural Join QueryNatural Join Query
Literal Description: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 natural join comsec_sector WHERE cobeta > 85;
Result:
CoSymbol CoTradeName SecName Beta
ABS ABC Learning Consumer Discretionary 86
ZFX Zinifex Healthcare 86
TLS Telstra Telecommunications 91
Cross Product EquivalentCross Product Equivalent
Literal Description:Using 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.secindex=comsec_sector.secindex AND cobeta > 85;
Result:
CoSymbol CoTradeName SecName Beta
ABS ABC Learning Consumer Discretionary 86
ZFX Zinifex Healthcare 86
TLS Telstra Telecommunications 91
Group By / Having QueryGroup By / Having Query
Literal Description:Show 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.bankid=comsec_bank.bankid GROUP BY bankname HAVING count(*)>2;
Result:
BankName Count
St. George Bank 3
Commonwealth Bank 4
Sub QuerySub Query
Literal Description:Find and display the sector which has the highest value.
SQL Query:
SELECT * FROM comsec_sector WHERE secvalue >= all (SELECT secvalue FROM comsec_sector);
Result:
SecIndex SecName SecValue
XMJ Materials 12980
Self Join QuerySelf Join Query
Literal Description:List 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 Transaction2FROM comsec_transactions order1, comsec_transactions order2 WHERE order1.accid=comsec_account.accID AND order2.accID = comsec_account.accID AND order1.cosymbol=order2.cosymbol and order1.transreceipt < 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
…
CHECK Constraints (1)CHECK Constraints (1)Literal Description:Limits entry of data to the integer data type only.
SQL Constraint:CREATE TABLE Comsec_Transactions(
TransReceipt INTEGER,...
Literal Description:Limits the range of Client ID’s that will be accepted.
SQL Constraint:CREATE TABLE Comsec_Client(...
ClientID INTEGER NOT NULL, CONSTRAINT Comsec_Client CHECK (ClientID
>=10000 and ClientID <= 99999)...
CHECK Constraints (2)CHECK Constraints (2)Literal Description:Limits 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 Description:Limits the number of characters a field will accept before rejecting INSERT.SQL Constraint:CREATE TABLE Comsec_Company(
CoSymbol VARCHAR(3) NOT NULL,...
Action Statements (1)Action Statements (1)Literal Description:Creating a table with the inclusion of the definition of the table’s 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)
);
Action Statements (2)Action Statements (2)Literal Description:Restricts 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...);
Use of View (1)Use of View (1)
Literal Description:View 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
Use of a View (2)Use of a View (2)
Literal Description:Select the client who has traded TLS shares the most number of times.
SQL Query:
SELECT Clientfirstname, count FROM tlstrade WHERE count >= all (SELECT count from tlstrade);
Result:
ClientFirstName Count
Zeeshan 3