Display the details of all the listed shares

Assignment Help Database Management System

Reference no: EM131008177

Introduction

Students have to complete all the following parts of this assignment:

A. SQL Query

B. Query Analysis

C. Form

D. Report

Important

Download the Microsoft Access database, Shares.accdb,from the course website and use it to complete this assignment.

If you get a security warning message when opening the downloaded database,then you need to open/click the options and then choose the option "enable this content".

Do not modify the structures of the tables and relationships. Do not create additional tables. It is not recommended to delete any of the existing records from the downloaded database (hereafter referred as ‘your database').

You need to write SQL queries in your database and make sure those queries produce the desired results as shown in Part A. Save and name those queries suitably such as Q1, Q2, ......, Q10 respective to the question that you are answering.

You need to create aMS Access-form and also a MS Access-report that are related to Part C and Part D and save them in your database.

Shares.accdb

The database, Shares.accdb, is maintained by an individual performing share trading activities. It contains five tables thatcan store the data related to trading of shares of some of the listed companies in Australian Stock Exchange.The following table provides the details of the data stored in those tables.

Table name	Data stored
Share	Some of the listed companies in ASX (Australian Stock Exchange) in which the individual is interested.
ShareRegistry	Share registry companies and their contact numbers.[Each listed company, stored in shares-table, must be related to one and only one share registry company].
ShareTrade	Trade orders issued for buying or selling of shares of the listed companies that are found in the shares-table.
Brokerage	Rate of brokerage applicable for a share trade.
Transaction	Completed transaction(s) related to the relevant share trade order(s). [Each share trade order can be completed by one or many transactions. Only after the completion of transactions, the amount is payable/receivable. Further details can be seen in part A-Q8].

Part A - SQL Query

Using your database, write SQL queries toanswer all the questions in this part.Each of the following questions has an information request followed by theexpected results when your database has the given sample data.

Hint: To get the desired output, you need to check the column headings; grouping and sorting of datadisplayed;removal of duplicate data; and other aspects of the query.

1. Display the details of all the listed shares/companies along with its share registrydetails.

code	shareName	ShareRegistryName	contactNumber
AGL	AGL Energy Limited	Link Market services	1300554474
BHP	BHP Billiton Limited	Computer Share	1300787272
CSL	CSL Limited	Computer Share	1300787272
RIO	RIO Tinto Limited	Computer Share	1300787272
A2M	The A2 milk company	Link Market services	1300554474

2. Display the share name/company name for which trade orders have been placed including the number of trade orders placed.

sharename	numberOftradeOrders
BHP Billiton Limited	1
RIO Tinto Limited	2

3. List the details of shares(s)/company(s)for which there has been no trade order placed so far.

code	shareName	ShareRegistryName
A2M	The A2 milk company	Link Market services
CSL	CSL Limited	Computer Share
AGL	AGL Energy Limited	Link Market services

4. Displaythe transacted amountof the highest valued-share transaction(s)performed.

HighestValuedTransaction

$20,000.00

5. Display the share(s)/company(s) name and details of trade order for which only one transaction was required to complete the trade.

orderId	orderDate	shareName
2	04-Jan-16	BHP Billiton Limited

6. Display the total number of transactions performed for each of the buy orders placed.

orderId	OrderDate	OrderType	Code	OrderedQuantity	numTransactionPerBuyTradeOrder
1	04-Jan-16	BUY	RIO	700	2
2	04-Jan-16	BUY	BHP	1000	1

7. Display the details of share registry company whose name contains the word ‘computer'.

ShareRegistryName	ContactNumber
Computer Share	1300787272

8. Display the details of each share trade order including net amount payable/receivable due to that order.

Hint:The net amount is payable for a buy order whereas net amount is receivable for a sell order.
Gross amount = orderedQunatity X OrderedPrice
Net amount for buy order = Gross amount + brokerage amount
Net amount for sell order = Gross amount - brokerage amount

OrderId	OrderDate	OrderType	Code	OrderedQuantity	OrderedPrice	BrokerageID	NetAmount
1	04-Jan-16	BUY	RIO	700	$40.00	2	$28,070.00
2	04-Jan-16	BUY	BHP	1000	$16.00	1	$16,048.00
3	05-Jan-16	SELL	RIO	500	$41.00	2	$20,448.75

9. For each share trade order(s) placed, display the order details and quantity of shares either bought/sold by the relevant transaction(s).

OrderId	code	orderType	orderedQuantity	Quantitycompleted
1	RIO	BUY	700	700
2	BHP	BUY	1000	1000
3	RIO	SELL	500	150

10. Display the details of share trade orders that have not been completed by transactions along with the pending quantity of shares to be completed.

OrderId	OrderDate	OrderType	Code	OrderedQuantity	OrderedPrice	BrokerageID	pendingQuantity
3	05-Jan-16	SELL	RIO	500	$41.00	2	350

Part B - Query Analysis
Explain the methodology of the SQL Query that you used to answer the question 8 in Part A.

Part C - Form
Usingyourdatabase, develop a form which can be used for data entry for Transactions. You are allowed to use any number of tables/ any suitable layout for developing that form.Name the form as "Transaction Entry".

Part D - Report
Usingyourdatabase, develop a report to display the details of Share, ShareTrade andTransactions. Name the report as "Share Transactions History".

Attachment:- IT 2.rar

Reference no: EM131008177

Questions Cloud

What do you think the big ideas in mathematics are : What do you think the big ideas in mathematics are, And what might they be for young children and how is mathematics usually represented by, and to, people?

Interpretation of the coefficient of type : What is the interpretation of the coefficient of Type in this regression? According to these results, what type would you expect to sell better-cheese or pepperoni?

What is the population for this sample survey : From telephone book listings, the committee chooses 150 businesses at random. Of these, 73 return the questionnaire mailed by the committee. What is the population for this sample survey? What is the sample? What is the rate (percent) of nonrespon..

Code the indicator variable : a) What attributes of this plot suggest that it would be appropriate to use both an indicator variable and an inter- action term in a regression of USGross on Budget? b) How would you code the indicator variable? (Use Adventure as the base level.) ..

Display the details of all the listed shares : Display the details ofall the listed shares/companies along with its share registry details - Display the total number of transactions performed for each of the buy orders placed.

How would you code the variables : a) How would you code the variables? (How many dummy variables do you need? What values would they have?) b) Why does the intern's suggestion make sense?

Regression with the indicator variable : Why does the intern's suggestion make sense? Do you think a regression with the indicator variable for Fall would model down jacket sales better than one without that predictor?

Find an image of a pie chart : Find an image of a Pie Chart or Pie Graph and post this image to the discussion board. Discuss what this graph is telling us about the data

Regression predicting calories for burger king foods : Burger King nutrition. Like many fast-food restaurant chains, Burger King (BK) provides data on the nutrition content of its menu items on its website. Here's a multiple regression predicting calories for Burger King foods from Protein content (g)..

User Account

All Pages