Reference no: EM132949763

COIT11237 Database Design & Implementation

Introduction
Students must complete all the following parts of this assessment task:
A. SQL Queries
B. Form
C. Report

ShareTrade database
CQShare Brokers Ltd (CQSB) maintains the trade data of their clients in ShareTrade database. For every trade, done by the clients, CQSB charges brokerage and GST (on brokerage only). The ShareTrade database contains the following tables:
• Client table that has the list of clients' details.
• Company table that has the list of listed companies whose shares are available for trade (buy or sell).
• Trade table that contains the details of trades performed by the clients.

Part A - SQL Queries
Using the downloaded database (ShareTrade), write SQL queries to answer all the questions in this part. Each of the following questions has an information request followed by the expected results for the given sample data.
Please adhere to the following guidelines in answering the questions:
• You must not use query design view. You are required to use SQL view to develop your queries.
• Where a column has been renamed in the expected result, you must reproduce that name in your results. For example, if ClientId appears as Client Number in the expected result, then you must make this column name appear as Client Number in your output.
• You must list the same columns as shown in the expected results.
• Simple queries are preferred. If your query is overly complex, you may not get full marks.
• You are not required to enforce row order on your queries unless specified.
• Unless otherwise specified, you need to remove duplicates in the output.
• Unless it is specified, you can use any type of constructs to solve the queries. E.g., You can use either implicit join or explicit join to solve the query.
• Your SQL must continue to produce the correct answer even if the data changes. E.g., If there is an update in the brokerage value, then the total brokerage amount should be updated accordingly.

Questions:
1. List the names of all clients and their respective total number of trades.
Hint: A trade can be a buy or a sale.

ClientName	NumTrades
ABC consultants	75
XYZ Ltd	6
CQU Super	3

2. Which company shares were bought but not sold by the client - CQU Super?

AsxCode	CompanyName	ClientName
TAB	Tabcorp	CQU Super

3. Which client has done maximum number of trades (both buy and sell together)? Display those client names and their respective number of trades.
Hint: A trade can be a buy or a sale.

ClientName	NumTrades
ABC consultants	75

Hint: You may try the following:
1) Write a query to display the name and number of trades done by each client (outer query).
2) Write another query to list only the number of trades done by each client (inner query).
3) Modify the outer query so that it can display its rows by using a count(*) >= ALL of the rows in the inner query.

4. For each type of trades, done by ABC consultants on Pie Lithium Ltd shares, display the ASX code, trade type and net trade value.
Hint:
You have to use union operator to answer this query.
Net Trade amount for buy is: Gross Trade amount + Charges
Net Trade amount for sale is: Gross Trade amount - Charges
Where Gross Trade amount is: Quantity * Price

ASXCode	TradeType	NetTradeValue
PLL	Buy	$193,536.24
PLL	Sell	$185,564.97

5. Which company shares have been traded more than five times?
Hint: A trade can be a buy or a sale.

AsxCode	CompanyName
OPY	Openpay Group Ltd
PLL	Pie Lithium Ltd
SZL	Sezzle Inc
Z1P	Zipco Ltd

6. Display the details of the client(s) whose name contains the word "sultan".

ClientId	ClientName	Email	Contact
101	ABC consultants	[email protected]	(02)92134000

7. Display the minimum, maximum and average traded price of OpenPay Group Ltd.

minimum	maximum	average
$2.27	$4.10	$3.24

Hint: A trade can be a buy or a sale.

8. Which client has been charged the least GST? Display the name(s) of client(s) and the total amount of GST.

ClientName	TotalGST
CQU Super	$5.43

Hint: Similar to question 3.
9. What is the total brokerage-charges for all the trades?

TotalBrokerage
$1,865.08

Hint: A trade can be a buy or a sale.

10. Display the lowest price paid for buying each of the company shares. Display the results in ascending order of ASXcode.

ASXcode	minimum
AYS	$0.61
BSA	$0.27
OML	$0.90
OPY	$2.27
PLL	$0.23
RDY	$1.38
SZL	$5.14
TAB	$0.28
Z1P	$6.30

Part B - Form
Use the given ShareTrade database and create the following two forms:
1) Main form that has a button to open the client form.
2) Client form that has buttons to add a new client record and to save the new record.
Sample forms have been shown below. You can have your own layout of the forms with additional functionalities; however the above-mentioned functionalities and your student Id & name should be available.

Part C - Report
Use the given ShareTrade database and create a Report object to display all the clients who have done trades. The report should display the results in ascending order of client name.

Hints:
1. Create a suitable query (ReportQuery)
2. Use Report Wizard to create the report object by using the ‘ReportQuery' and its required attributes
3. Use the design view to
a. Change the report title, labels and column headings suitably
b. Adjust the size of the controls

Attachment:- Database Design and Implementation.rar