MS ACCESS DATABASE – ASSIGNMENT

MS ACCESS DATABASE – ASSIGNMENT

MS Access Database – Assignment You can download MS ACCESS from MyUB web site using your account or if you have Mac you can work in VDI environment (check e-mail for instructions). Please do not wait to start to work on the project. Resources: Achievement and Learning Center (410-8375383) Check video links posted in the syllabus and also access free tutorial http://www.gcflearnfree.org/ACCESS2016 Assignment Details: You are a consultant hired by an organization name INT.com to develop a portfolio database for keeping track of client’s portfolio of shares, stocks, bonds and other trading instruments. Assumptions: A client cannot buy the same stock more than once on the same day. Technical Details for the portfolio database Tables – Stock & Portfolio Table 1 Name: Stock Table Attributes/Columns: Stock_Symbol, Company_Name, Exchange_Name Table Restrictions (business rules): a. Stock_Symbol is the stock symbol as traded on an exchange (Primary Key) b. Company_Name is the name of the company c.

Exchange_Name is the name of the exchange (NYSE, AMEX, NASDAQ) Table 2 Name: Portfolio Table Attributes/Columns: Owner_ID, Stock_Symbol, Number_Bought, Price_Paid, Date_Bought Table Restrictions (business rules): a. Assume a person cannot buy the same stock more than once on the same day. b. Owner_ID is the id of INT.com’s client (PK) c. Stock_symbol is the symbol of stock as traded on an exchange (PK) d. Number_bought is the number of total share bought e. Price_paid_per_share is the price paid/share f. Date_bought is the date when the stock was bought Sample data for STOCK table: Stock_symbol Company_name Exchange_name OMC Omnicon Group NYSE AAPL Apple NASDAQ IBM IBM Corp NYSE GOOG Google Corp NASDAQ BBY Best Buy NYSE LNG Cheniere Energy, Inc. AMEX CSCO CISCO NASDAQ DIS Disney Corp NYSE NGD New Gold AMEX BAC Bank of America NYSE JPM JPMorgan and Chase NYSE PFE Pfizer Inc NYSE C Citigroup NYSE GRPN Groupon Inc. NASDAQ WEN Wendy’s Company NASDAQ NOK Nokia, Inc NYSE GE General Electric Co. NYSE BIDU Baidu Inc NASDAQ PCLN PriceLine Inc NASDAQ HPQ Hewlett-Packard Inc NYSE   PORTFOLIO (owner_ID, Stock_symbol, number_bought, PRICE_PAID_per_share, Date_bought) Owner_ID Stock_symbol Number_bought PRICE_PAID/share Date_bought A112 AAPL 10 450 4/12/2018 A112 PFE 3000 25 1/5/2018 A123 C 50 4 1/18/2018 A123 AAPL 50 425 1/12/2018 A123 PCLN 150 650 1/18/2012 A123 GOOG 50 430 3/18/2012 A123 GE 1000 16 1/18/2018 A231 AAPL 200 430 1/18/2013 A231 IBM 200 85 1/10/2019 A441 NOK 3000 2 1/12/2011 A441 LNG 1000 5 1/12/2008 A441 HPQ 300 22 3/12/2011 A441 OMC 100 45 2/12/2013 A451 PCLN 300 620 1/12/2015 B111 HPQ 1000 4 2/12/2013 B111 BAC 1000 6 8/12/2012 B111 C 5000 2 2/12/2018 B111 DIS 300 31 5/12/2012 B118 PCLN 1000 650 2/12/2019 PART A: • Develop the tables in ACCESS database Systems • Identify PK and FK of each table (can do in WORD) Table 1 (identify PK and FK relationships) Table Name STOCK PORTFOLIO Primary Key (PK) Foreign Key (FK) Submit this table as Part A (section 2) requirement to be submitted see below • Show the relationship (1:1 or 1:m or m:n) between the following: STOCK and PORTFOLIO in ACCESS and include a print screen copy from ACCESS Justify why it is 1:1 or 1:m or m:n   PART B: Develop the following queries in ACCESS and run (execute) them Q 1-13 1. List the company names. 2. Give the count of stocks traded on all stock exchanges. (simple count and not number of shares) 3. Give the names of owners who bought stocks on 8/12/2012? 4. Give the count of stocks bought on each dates. For example, 4 stocks were bought on 1/18/2012 5. Give the count of stock bought between 1/1/2012 and 1/1/2015 6. Give the client number of clients that hold IBM or BAC company stocks. 7. Give the total number of outstanding stocks of stock whose symbol is BAC 8. How many different stocks client B118 holds? (give the number) 9. Give the total worth of client A441 (this is the total amount that A441 has) price* number of shares 10. Give the count of stocks traded on each exchange 11. Give the worth of each client. (worth is defined as price* number of shares). 12. Give the names of stocks whose price is more than $200 13. What’s total worth of INT.com? Report 14. Create a Client Portfolio report of INT.com, stocks they hold and their net worth See the template below: BONUS: (2 points) Create a Stock report by date, stock name, total amount of that stock and number of owners who own that stock. Also include the overall count of stocks. Use whatever format you like but make sure the above information is included in your report. To be submitted: (in SAKAI only; do not e-mail) You will submit two files Part A: (WORD file) (listed above also0 1. Table contents of each table in ACCESS (print screen shot of contents from ORACLE) 2. PK and FK table (submit following table) Table Name STOCK PORTFOLIO Primary Key (PK) Foreign Key (FK) 3. Relationship among ENTITIES (1:1 or 1:m or m:n) and Justify those relationships Part B: (ACCESS file) 1. Build Queries in ACCESS and run them (nothing to submit for this part except the ACCESS database part 2 below) 2. actual ACCESS database with queries and report. (filename.accdb) You will have TWO files: • ONE WORD file with all the answers (Part A) • ONE ACCESS file with .mdb or .accdb extension (Part B; section 2)