ASSIGNMENTS Fall 2024

[Technology Application Project] [Systems Development Web Project] 

TECHNOLOGY APPLICATION PROJECT

The goal of the Technology Application Project is to allow you to develop an Information System to provide information for effective decision making. This project is a team assignment to allow individuals to exchange ideas and learn from each other.  You will build an Information System to manage your investment club. So, your job is to build this system in MS Access and you will use it to store stock and investment information with data downloaded from the Internet and copied from other sources and produce the appropriate information for decision making.

Your team has created an investment club with $100,000 which must be invested in five different stocks from the following list of stocks . After you purchase your stocks, you must collect all required data related for these stocks and store this data into your database. Every week, the group produces investment reports which are analyzed by the members who make future investment choices. 

You have decided to build an MS Access database, to minimize the time it takes to administer the club's investment information and produce timely and accurate reports. Here are the high level requirements:

Technical

1.      The solution must be built in MS Access database

Functional

1.      The solution shall provide a query which displays the estimated annual dividends for all stocks and the Price Earnings (P/E) ratios of your stocks.

2.      The solution shall produce a query which shows the gains/losses of your stocks in $ and % amounts and the comparative S&P 500 performance for the same period. 

STEP BY STEP DESCRIPTION

Data Collection and Conversion

1.   Review the stocks from the ListOfStocks.xlsx Excel workbook. Decide which 5 stocks you will purchase, as a team, from this list. Submit your selections here: https://spreadsheets.google.com/viewform?hl=en&formkey=dEJhVDZKdFhQTzZuRkVDWGlFQjV2U3c6MQ#gid=0. You will purchase your stocks on 11/25/2024, after the stock market closes (this way, you all pay the same price for your stocks; no morning purchases). You could use financial sites like http://finance.yahoo.com or http://moneycentral.msn.com to find the closing prices and other information for your stocks. 

   Data Analysis and Data Modeling

2.     Import all data from the ListOfStocks.xlsx Excel workbook into a new table in your database. The new table will be named ListOfStocks. The primary key for this table is the Ticker field. After the ListOfStockstable has been created, change the size of the Ticker field to 6 Text characters. Also, use the appropriate data types for all other fields.

3. Build a new table named Members with the fields shown in the ERD below. isManager field is a Yes/No field. Choose the appropriate format for the other fields.

4. Build a new table named Team. Create a team picture collage with all the team members. Store that picture in the TeamPicture field.

5.      Build a new table named Trades with the following specifications:

6.      TradeDate field described above is formatted as short date. 

7.      Build a new table named Prices with the following specifications:

8.     Enter Trade data in the database.  Also, start recording the closing prices for your team stocks and enter this data in the Prices table. Record closing prices from 11/25/2024 until 11/27/2024 (3 times 5 stocks = 15 different daily prices).

9.   Create the Relationships (ER Diagram) for the tables created up to this point, as shown below (Fig. 1) and add the Members table as you see fit.

Data Processing and Information Delivery 

10.   Build a query from the table ListOfStocks which will show all stocks with a PE of less than 11, Beta of less than 1, and the Low52 price is less than 75% of the High52 price.  Name that query ValueStocks. Show the fields of your choice (I do not show you a screen of the output since you have you make sure that you have the correct list).

11.   Build the Gain or Loss Query shown below where Gain or Loss is calculates as Trade Shares times the difference between Price and TradeSharePrice. Query is sorted by Ticker. Also, please pay attention to the currency formats for all $ amounts. Note that your query layout and format does not have to look identical to mine. I am interested in the information you provide and not in the format (look and feel) of the query. 

Often, I receive a database with several unnecessary objects. A team may attempt to create a query for the Gain and Loss report several times. When I grade your projects, I have no idea which of the queries to grade since there are many similar queries. So, please do not have any tables, queries, or reports in the database are not required by this assignment.

Information Verification

12.   Create an Excel file that produces the same information as your 2 queries. This Excel file is your Unit Test Plan (UTP) which verifies that the MS Access Queries are correct.

Evaluation

13. Answer the following questions:

1.      Did we accomplish everything we wanted to accomplish with this database application system? 

2.      Describe the changes would you make to this database application system to improve it (in a bulleted list format) with minimum of 10 changes. For each change, why is this change recommended?

Please note that in this question, I do not ask to evaluate the Assignment but the database (your product) itself.

Assignment Submitting Standards:

Use Canvas to upload your Access database, your Excel file with the Test Plan, and your evaluation (question 13 above).

FREQUENTLY ASKED QUESTIONS

            Question: Can you recommend an online tutorial?

            Answer: Here it is: http://www.gcflearnfree.org/access2007  

Question: How are you going to grade this assignment?

Answer: We will use the following Rubrics to grade your assignment. 

	5: Exceeds Expectation	4: Meets Expectation	3: Approaching Expectation	2: Below Expectation	Max Points
Data Collection and Conversion	Identifies and collects all data needed to produce appropriate information. Captures and converts data from all sources.	Identifies and collects most data needed to produce appropriate information. Captures and converts data from most sources.	Identifies and collects some data needed to produce appropriate information. Captures and converts data from some sources.	Identifies and collects few data needed to produce appropriate information. Captures and converts data from few sources.	10
Data Analysis and Modeling	Prepares and stores data in the specified format with accurate consideration of space and performance tradeoffs. Correctly models all organizational data.	Prepares and stores data in the specified format with some consideration of space and performance tradeoffs. Modeling of organizational data is mostly correct.	Prepares and stores data in the specified format with little consideration of space and performance tradeoffs. Modeling of organizational data is somewhat correct.	Prepares and stores data in the specified format with no consideration of space and performance tradeoffs. Modeling of organizational data is mostly incorrect.	10
Data Processing and Information Delivery	Constructs the necessary processing to provide all the appropriate information for decision making.	Constructs the necessary processing to provide most of the appropriate information for decision making.	Constructs the necessary processing to provide some of the appropriate information for decision making.	Constructs the necessary processing to provide hardly any of the appropriate information for decision making.	20
Information Verification	Verifies the accuracy of all delivered information.	Verifies the accuracy of most delivered information.	Verifies the accuracy of some delivered information.	Verifies the accuracy of only some of the delivered information.	5
Evaluation	Fully assess the impact of the technology application on the attainment of organizational goals.	Assess the impact of the technology application on the attainment of organizational goals.	Partially assess the impact of the technology application on the attainment of organizational goals.	Fails to assess the impact of the technology application on the attainment of organizational goals.	5
					50

Question: I cannot enforce the referential integrity in the relationship (ER Diagram). Why?

Answer: Most probably, your data violates the referential integrity rules. For example, you may have a Trade for a Member that does not exist. Verify the primary key and foreign key data and make sure you do not have a typographical error.

Question: What do I have to do for the STP?

Answer: The Unit Test Plan (UTP) is a document created to verify the accuracy of your output (query, form, or report).  A team member should create, in advance, test conditions to verify the accuracy of ALL information (calculations) produced by your application. After the database is complete, the tester confirms the accuracy of the query output, and signs the UTP.

Question: Can you give me some hints for the evaluation section answer?

Answer: Read the assignment again and suggest ways to improve the information system you delivered (i.e., suggest addition of new features, changes in the way you processed the information, elimination of unnecessary tasks). Did you meet all objectives? Did you have difficulty in performing some activities? Did you give the customer what was asked?

SYSTEMS DEVELOPMENT WEB PROJECT (Individual Project)

The importance of the Internet as an inexpensive way to bring your messages and data to a worldwide audience is ever increasing. All types of companies, small and large, see the Internet as a huge opportunity. This project will give you the opportunity to use this new technology and benefit from it. Your assignment is to develop a Web Site, which will be hosted at the CSUS Web Pages server (no other servers will be allowed). You are free to select any content for your Web Site as long as it in accordance to the rules and regulations defined by CSUS and addresses a problem/opportunity.

PROJECT OVERVIEW

You have decided to build a web site to address a single problem/opportunity. You must develop a web site with a single web page. Please note that our main focus is on the systems development process itself and not the finished product (will describe this in class in more detail).

PROJECT PHASES

SYSTEMS INVESTIGATION & ANALYSIS

Identity 3 problems/opportunities worth addressing with a web site. Then, select one problem/opportunity and create a MS Word document with the following content: 

·         Problem/Opportunity Definition: Briefly describe the problem/opportunity your web page will address and explain why the Internet is the ideal medium for addressing this problem.

·         Solution Ideas: List and briefly describe 10 ideas on how to meet your stated objectives. Select a single idea to make a web page.

• Objective Definition: Define at least 3 different S.M.A.R.T. business (not system) objectives. 

·         Overview of your Web Pages: A brief description of your web page (describe how you visualize your web page to look like after you build it). 

NOTE: During the analysis phase, you should ask questions and clarifications about the project. Please read the entire project specifications and ask me questions if there is something you do not understand.

SYSTEMS IMPLEMENTATION

·         Activate your personal web page

·         Build your web page by following your Systems Design

·         Transfer your web page to our web server 

·         Test your web page (look for grammar errors, broken links, broken images, pool color schemes and more). Then capture the screen which shows your web page on the server. Add this print screen as the last page on your MS Word document

 What to Submit

·         One MS Word document which contains your Systems Investigation & Analysis (you will submit this document first)

·         One MS Word Document which captures your Web page as it appears on your Web Server (screen  capture of your Web Page). You will submit this document after you have implemented your web page.

·         You will upload these two documents in Canvas.

PROJECT MINIMUM SPECIFICATIONS 

• Develop one web page named  index.htm.

·         Place the web page files in the BHON folder (on your web server account).

·         Web page must contain at least one graphic image.

·         Web page must contain some text that is Hyper linked to another URL.  

• Web page must contain the CSUS required "disclaimer" at the bottom of your web page.

Your Name  takes full responsibility for the information posted. The information on this page represents that of  Your Name and not that of California State University, Sacramento.

·         Web page must contain at least one table (with or without borders).

MINIMUM SOFTWARE NEEDED 

To complete this assignment, you will need several types of software.

1. Web browser to view your web pages.
2. Web authoring tool or HTML editor to build your web pages: you have a choice of any freeware or registered copy of an authoring tool to create your HTML documents. I will provide assistance with MS Word.  

3.      An FTP tool like Filezilla.

HOW DO I GRADE THIS PROJECT? 

Here are some of the things I consider when grading: Completeness (contains all requested sections) - Transition (considers and conforms to prior life cycle output) - Effectiveness (for the purpose it was built) - Ease of use (hyperlinks, design, sequence, etc.) - Aesthetics (pictures, colors, alignment, etc.) - Clarity (content, use of terms, feedback, etc.) - Quality (errors, graphics, content, etc) - Conformity to standards (page size, features, etc.) - Features (a very basic page is not equal to a page with more features and complexity).

Other Assignment Submitting Standards

1.      All assignments are due at the beginning of the class period.

2.      Late assignments will NOT be accepted.

Grading Rubric for Web Development Project