Description of this paper

Loading

CIS 2200 Prof. Pai-chun Ma CIS 2200 - INTRODUCTION TO-(Answered)

Description

Step-by-step Instant Solution


Question

hey i need help with this assignment can you help ?


CIS 2200

 


 

Prof. Pai-chun Ma

 


 

CIS 2200 - INTRODUCTION TO INFORMATION SYSTEMS AND

 

TECHNOLOGIES

 

Zicklin School of Business ? Baruch College - CUNY

 

Professor:

 

E-Mail:

 

Office:

 


 

Pai-chun Ma

 

pai-chun_ma@baruch.cuny.edu

 

Room 11-243

 


 

Phone:

 

Fax:

 


 

(646) 312-3369

 

CIS Dept

 

(646) 312-3351

 


 

MS ACCESS TUTORIAL

 

SIMPLE WORLD STOCK DATABASE

 

The purpose of this case is to show you the concept and skills about creating a relational

 

database to store and retrieve critical business data. These essential skills include defining

 

tables, setting field properties, defining relationship (among tables), loading data, data cleansing,

 

ad hoc queries and generating reports.

 

Assume that a junior analyst is given a task to create a simple world stock database of 30 stocks

 

issued by 20 companies in US, China, Taiwan and Hong Kong, so that he/she can answer several

 

critical business questions and identify arbitrage opportunity between markets. Please notice that

 

some of these stocks belong to companies that may be traded in more than one stock exchange in

 

different currency denominations. (For example, a Taiwanese flat screen display manufacturer,

 

AU Optronics Corp., has its stock, 2409.tw, traded on Taiwan Stock Exchange in Taiwan dollars.

 

At the same time, its stock in the form of ADR (American Depository Receipts) is also traded on

 

NYSE under the ticker, AUO, in US Dollars).

 

For demonstration purpose, you need to download the following pre-prepared MS Excel files

 

from Blackboard.

 

1. Exchange File, which contains the stock exchange data as follows:

 


 

1

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

2. Company File, which contains company data of 20 companies as follows:

 


 

3. Stock File, which contains 30 stocks belonging to 20 companies as follows:

 


 

2

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

4. Prices File, which contains historical stock prices of stocks for the period of 10/22/0910/21/10;

 


 

5. Conversion File, which contains conversion ratios for the number of foreign shares to be

 

converted into one share of corresponding ADRs (American Depository Receipt) as

 

follows:

 


 

3

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

6. CurrencyRate File, which contains currency exchange rate between US Dollar (USD)

 

and Hong Kong Dollar (HKD), and the rate between USD and Taiwan Dollar (TWD) for

 

the period of 10/22/09-10/21/10 as follows:

 


 

Section 1 ? Create a database, and name it ?WorldStock?.

 


 

4

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Section 2 ? Create Tables

 

In MS Access, Choose ?Create? tag, then click ?Table Design? icon to create a table in design

 

view, specify Field Names, Data Type, and properties including Field Size, Required, Allow

 

Zero Length, and Indexed. Do not forget to designate the appropriate primary key. Field sizes

 

that are unnecessarily long for the data that will be stored reflect poor design. Field sizes that

 

are too short will truncate data by mistake. So, choose field size wisely.

 

You need to create the following tables (with underlined field(s) as the primary key):

 

Company (CompanyID, CompanyName, Address, City, State, Country, ZipCode, Phone,

 

Website)

 

Exchange (ExchangeID, Country, Currency)

 

Stock (Ticker, ExchangeID, CompanyID)

 

StockConversion (ADRTicker, ForeignTicker, Ratio)

 

CurrencyExchange (DomesticCurrency, ForeignCurrency, Date, Rate)

 

DailyPrice (Ticker, Date, Open, High, Low, Close, Volume)

 

Be sure that you define field properties appropriately. Table definitions and Field properties

 

will be explained in detail in class.

 

Please download data_dictionary.pdf file from Blackboard for detailed definition of each data

 

field. The content of this file looks as follows:

 


 

5

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Please also download FieldProperties.pdf from Blackboard as follows:

 


 

You will need this table to define your field properties in all of your tables.

 

1. Following the Columns part of the above definition in the data_dictionary.pdf, click

 

?Create? then choose ?Table Design? as follows:

 


 

6

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

2. Following the Columns part of the above definition in the data_dictionary.pdf, enter

 

?CompanyID? as field name, choose ?Number? as field type.

 


 

3. Go to Field Properties, click the down arrow key, then choose ?Integer? as indicated in

 

the column type of data_dictionary.pdf.

 


 

7

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

4. Following the Index part of the above definition in the data_dictionary.pdf, click down

 

arrow for ?required? and choose ?yes?, and click the down arrow for ?indexed? and

 

choose ?Yes (No Duplicates)?, because CompanyID is a single field primary key.

 


 

5. Repeat the above steps 2-4 to define all fields required in Company table as follows.

 

Then, highlight ?CompanyID? and click ?Primary Key? icon to designate ?CompanyID?

 

to be the primary key of this table.

 


 

8

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

6. Repeat above steps 2-5 to define all tables, and their fields, field types and field sizes

 

according to data_dictionary.pdf. For fields not mentioned in the Table Indexes section,

 

choose the default setting for field properties. For fields included in Table Indexes

 

section, choose ?yes? for Required, ?no? for Allowed Zero Length (if it is a text field),

 

?Yes (No Duplicates)? for Indexed for single field primary key (such as CompanyID in

 

Company Table, ExchangeID in Exchange Table, Ticker in Stock table, ADRTikcer in

 

StockConversion table) and ?Yes (Duplicate OK)? for Indexed for the rest of indexed

 

fields.

 


 

9

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Section 3 ? Create Relationship

 

Click ?Database Tools? tag, choose ?Relationship? icon to create relationships among these

 

tables as follows.

 


 

1. Highlight all tables except CurrencyExchange, then click ?Add? button.

 


 

10

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

2. Arrange tables as follows. Then, highlight CompanyID in Company table, press the left

 

mouse button, drag the cursor over CompanyID in Stock table, and drop it. The Edit

 

Relationship would pop up. Check the check box of Enforce Referential Integrity, then

 

check both Cascade update and Cascade delete boxes. Click ?Create?.

 


 

3. Repeat the above step, so that the entire relationship would be as follows:

 


 

11

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

4. Verify your data definition by click ?Database Tools?, then select ?Database

 

Documenter?. Choose ?Tables?, then click ?Select All?. Choose Print Table Definition

 

as follows.

 


 

5. After you click ?OK?, then another ?OK? at the Documenter, you?ll see the object

 

definition shown as follows. Click ?PDF or XPS? to publish, so that you can compare it

 

with the data_dictionary.pdf you just used to create your tables and relationship, and be

 

sure that they match.

 


 

12

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Section 4 ? Load data

 

Click ?External Data? tag, then choose ?Excel? to browse and select the file from the appropriate

 

folder. Then, choose ?Append a copy of the records to the table? to append the Excel files into

 

your tables. Be sure that you follow the order as the following to load the data into tables.

 

1.

 

2.

 

3.

 

4.

 

5.

 

6.

 


 

Company File -> Company Table.

 

Exchange File -> Exchange Table.

 

Stock File -> Stock Table.

 

Prices File -> DailyPrice Table.

 

Conversion File -> StockConversion Table

 

CurrencyRate File -> CurrencyExchange Table

 


 

13

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Section 5 ? Queries

 

A Use queries for Data Cleansing (let?s just focus on DailyPrice table)

 

1. Choose ?Create? tag, then click Query Design icon to create a query.

 


 

2. Highlight ?Exchange?, then press Add button.

 


 

14

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

3. Repeat the above procedure to add ?Stock? and ?DailyPrice?, then click Close

 

button.

 


 

4. Highlight ?Country? in Exchange, then double click the right mouse button to

 

select ?Country? as the field for the query. Repeat the same steps to select

 

?ExchangeID? from Stock, ?Ticker? from DailyPrice and Date from

 

?DailyPrice?.

 


 

15

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

5. Click "?? (Totals) to group records (using Group By clause).

 


 

6. Highlight Group By under Date and choose ?Count?.

 


 

16

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

7. Click ?!? (Run) to execute the query.

 


 

8. The results of query would be shown in Datasheet view as follows:

 


 

17

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

9. Click the ?View? icon, then choose ?SQL? (SQL View) to the SQL statement

 

generated from the graphical notation of Query-by-Example in Design View.

 


 

10. Click ?X? to close the query, and save it as ?q1?.

 


 

18

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

11. In Datasheet view, you may have noticed the discrepancies in counts among

 

exchanges and within exchanges. Within exchanges, NASDAQ, NYSE and

 

HKSE have relative consistent counts except TSLA and SYA. In fact, TSLA and

 

SYA were new IPOs in 2010. However, the inconsistency in TWSE may involve

 

different issues.

 

Now, create a query to list dates and counts of DailyPrice records of TWSE which

 

have zero volume, and save the query as q2.

 

Repeat steps similar to above steps 1-10 to create query q2. The result Design

 

view of q2 is as follows:

 


 

The result SQL view of q2 is as follows:

 


 

19

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result Datasheet view of q2 is as follows:

 


 

Since a zero volume can indicate a holiday or the stock being suspended for trading,

 

let?s use a heuristic to separate holidays from the suspended trades.

 

12. Repeat steps similar to above steps 1-10 to create query q3 to count the number of

 

stocks traded on each day in TWSE, and list by dates. The result Design view of

 

q3 is as follows:

 


 

20

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result SQL view of q3 is as follows:

 


 

The result Datasheet view of q3 is as follows:

 


 

21

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

13. Assume not all stocks in TWSE on the same day would be suspended unless it is a

 

holiday. Repeat steps similar to above steps 1-10 to create query q4 to find the

 

dates that trades in the TWSE are only the trades with zero volume. The result

 

Design view of q4 is as follows:

 


 

The result SQL view of q4 is as follows:

 


 

22

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result Datasheet view of q4 is as follows:

 


 

Repeat steps similar to above steps 1-10 to create query q5 to list all the daily price

 

records on holidays in TWSE. The result Design view of q5 is as follows:

 


 

23

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result SQL view of q5 is as follows:

 


 

The result Datasheet view of q5 is as follows:

 


 

24

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Then, click External Data and choose Excel under Export to export the results

 

to an Excel file, q5.xlsx, as a backup before delete.

 


 

You may check q5.xlsx by openning the file in Excel as follows:

 


 

25

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

14. Follow the step 1, click ?Close? button without adding anything. Then, click SQL

 

and choose SQLView as follows:

 


 

Enter SQL statement as follows:

 

DELETE *

 

FROM DailyPrice

 

WHERE ticker like "*.tw"

 

and date in (

 

SELECT date

 

FROM q4 );

 


 

Then, save it as q6. The result SQL view of q6 is as follows:

 


 

26

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Click ?!? (Run) to execute the query to delete the holiday records as follows:

 


 

After delete, run q5, then the result should be empty as follows:

 


 

27

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

B. Use queries for ad hoc questions and data mining

 

We wish to find companies that are only listed overseas (meaning companies have their

 

stocks only traded outside of the country where the companies are registered or located)

 

with their company names, company countries, ticker symbols, stock exchanges and the

 

countries of exchanges.

 

15. Repeat steps similar to above steps 1-10 to create query q7 to find companies that

 

their stocks traded in the local market. The result Design view of q7 is as

 

follows:

 


 

The result SQL view of q7 is as follows:

 


 

28

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result Datasheet view of q7 is as follows:

 


 

16. Repeat steps similar to above steps 1-10 to create query q8 to list all the

 

companies with their company names, company countries, ticker symbols, stock

 

exchanges and the countries of exchanges. The result Design view is as follows:

 


 

29

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result SQL view of q8 is as follows:

 


 

The result Datasheet view of q8 is as follows:

 


 

30

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

17. Repeat steps similar to above steps 1-10 to create query q9 using q7 and q8 to

 

find companies that are only listed overseas (meaning companies have their

 

stocks only traded outside of the country where the companies are registered or

 

located) with their company names, company countries, ticker symbols, stock

 

exchanges and the countries of exchanges. The result Design view of q9 is as

 

follows:

 


 

Please pay attention to the join condition (the arrow) between q8 and q7 is

 

unidirectional and it is created through the following steps:

 

a. Click the link, so that it is highlighted. Press right mouse button, and choose

 

Join Properties as follows:

 


 

31

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

b. Choose (2), then the arrows would change into a unidirectional one from q8 to

 

q7 as the above.

 


 

The result SQL view of q9 is as follows:

 


 

32

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result Datasheet view of q9 is as follows:

 


 

18. Repeat steps similar to above steps 1-10 to create query q10 to list Ticker, Date,

 

High, Low, Close from DailyPrice where the difference between the Day?s High

 

and Low exceeds 15% of the close on the day. The result Design view is as

 

follows:

 


 

33

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The expression is:

 

([DailyPrice].[High]-[DailyPrice].[Low])/[DailyPrice].[Close]

 

The result SQL view of q10 is as follows:

 


 

The result Datasheet view of q10 is as follows:

 


 

34

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

C. Use queries and report to identify trading opportunities

 

Assume that we like to identify the opportunities to buy the stock of the same company

 

traded in a foreign exchange at the closing price, then sell immediately at the opening of

 

the US market on the same day, and we set the threshold to be 5% price difference.

 

Generate a report using queries to show such opportunity with Company Name, Ticker

 

traded in United States, Ticker traded in a foreign exchange, Date, Open price in US,

 

Close price in equivalent US Dollars, and the percentage difference over 5%.

 

19. Repeat steps similar to above steps 1-10 to create query q11 to identify all the

 

daily stock trades outside of United States with close price calculated according to

 

their equivalent ADRs in US dollars. The result Design view is as follows:

 


 

Please note that the expression is:

 

USClose: [DailyPrice.Close]/[CurrencyExchange.Rate]*[StockConversion.Ratio]

 


 

35

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result SQL view of q11 is as follows:

 


 

The result Datasheet view of q11 is as follows:

 


 

36

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

20. Repeat steps similar to above steps 1-10 to create query q12 to find all the daily

 

stock trades in United States with their open price. The result Design view is as

 

follows:

 


 

The result SQL view of q12 is as follows:

 


 

37

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result Datasheet view of q12 is as follows:

 


 

21. Repeat steps similar to above steps 1-10 to create query q13 to identify daily

 

trades that the foreign equivalent ADR close price is 5% below the open price in

 

United States. The result Design View is as follows:

 


 

Please note that the expression is:

 

Expr1: ([q12.Open]-[q11.USClose])/[q11.USClose]

 

38

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

The result SQL view of q13 is as follows:

 


 

The result Datasheet view of q13 is as follows:

 


 

39

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Section 6 ? Reports

 

1. Click ?Create?, choose ?Report Wizard? under Reports, then choose Query: q13 from

 

Tables/Queries as follows:

 


 

2. Click ?>>? to bring over all the fields from available fields to selected fields as follows:

 


 

40

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

3. Click ?<? to remove grouping by q11.Ticker as follows:

 


 

4. Then, highlight ?CompanyName? and click ?>?, so that the report would be grouped by

 

Company Name as follows:

 


 

41

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

5. Choose ?Date? as the sort order in ascending. Then, click ?Next.

 


 

6. Choose ?Stepped? for Layout and ?Portrait? for Orientation.

 


 

42

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

7. Enter ?Trading Opportunity Report? as the report title, and click ?Modify the report

 

design?.

 


 

8. Modify the heading and size of the field to align and accommodate data.

 


 

43

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

9. Highlight USClose, press right mouse button, then choose Properties.

 


 

10. Click ?Format?, choose ?Currency?.

 


 

44

 


 

CIS 2200

 


 

Prof. Pai-chun Ma

 


 

Similarity, highlight Expr1, click right mouse button. Then, click ?Format?, choose

 

?Percent?

 

11. Click ?Report View? to see the report as follows:

 


 

Disclaimer: This case is used to illustrate the implementation of a relational database using MS

 

Access including table design, creation, data loading, data cleansing, data export, ad hoc

 

queries, data mining and report. The example operations about world stock database do not

 

reflect any real practice in the investment community nor suggest a superior practice if the

 

method is followed.

 


 

45

 


 

 

Paper#9255636 | Written in 27-Jul-2016

Price : $16
SiteLock