Comsec Share Portfolio

Comsec Share Portfolio By Zeeshan YUSUF

Database 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.

TRADEACTS AccID* ClientID* Entity Relationship Diagram COMPANY TRANSACTIONS CoSymbol SecIndex* CoTradeName CoACN CoStatus CoMngDir CoPERatio CoDivYield CoBeta Co52WkHigh Co52WkLow CLIENT ACCOUNT TransReceipt AccID* CoSymbol* TransType TransQuantity TransLimitPrice ClientID ClientFirstname ClientLastname ClientLandline ClientAddress ClientSuburb ClientEmail AccID BankID* AccBankAct AccPassword SECTOR BANK SecIndex SecName SecValue BankID BankName BankAddress

One to Many Relationship COMPANY 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). CoSymbol SecIndex* CoTradeName CoACN CoStatus CoMngDir CoPERatio CoDivYield CoBeta Co52WkHigh Co52WkLow Primary Key Foreign Key SECTOR SecIndex SecName SecValue

TRADEACTS AccID* ClientID* Many to Many Relationship ACCOUNT 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 BankID* AccBankAct AccPassword CLIENT ClientID ClientFirstname ClientLastname ClientLandline ClientAddress ClientSuburb ClientEmail

Simple Query Literal Description:Find all clients who live in the Castle Hill suburb. SQL Query: SELECT * FROM comsec_client WHERE clientsuburb='CASTLE HILL'; Result:

Natural 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:

Cross 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:

Group 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 GROUPBY bankname HAVING count(*)>2; Result:

Sub 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:

Self 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 Transaction2 FROM 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:

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) 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) 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) 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) Literal Description:View a list of the number of times of customers who have traded Telstra shares. SQL Query: CREATEVIEW TLStrade As SELECT ClientFirstname, count(*) FROM comsec_client natural join comsec_tradeacts natural join comsec_transactions WHERE cosymbol='TLS' GROUPBY clientfirstname; SELECT * FROM TLStrade; Result:

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:

Comsec Share Portfolio