CPSC 203: Final exam information

Course web page: Introduction to problem solving with applications James Tam

Return to the course web page

CPSC 203: Final Exam Information

(The page is now largely complete. If there are changes or additions they will likely be small ones).

Date/location:

The final exam is scheduled by Student Services, you can find information via the [university portal].

Practice review questions:

There are a number of sources of review material that are pertinent to this semester:
- TopHat questions can give an idea of what some of the (easier - in most cases) multiple choices may look like.
- During lecture as I cover the material I will provide suggestions of what an exam-like question may look like. You should attend class for this info and note the details as it comes up. Extra handouts of the questions (but not explanations) will be on the table outside of ICT707. If you missed class then you should try talking to a friend that attends class and takes thorough notes.
- Review questions were covered in the last lecture. [Starting database used in the review]
- Extra VBA review questions. They are meant to provide you with some extra practice so you need to actually try them on your own to get anything out of it. For that reason, solutions won't be posted and I won't just "email you the solutions". But it really shouldn't be a big loss because I have taught you how you can find out the answers for yourself:
  - For the 'tracing' questions where you have to determine the output of a program or the result of an expression (e.g. spreadsheet formula) then you can always type in the program or expression and run it yourself.
  - The 'writing' questions (e.g. write a query or write a program) are a little trickier but you can still check your answers by testing the results e.g. does your program or query produce the results specified in the questions.
  - If you are still unsure of things after you have attempted a question (e.g. you can't figure out why you got a given result after typing it in and running the program), then you can ask (just be sure to show me the work that you have done so I can see how far that you have gotten and perhaps where you are having problems).
  - I'm happy to help, however you should make sure you provide a reasonable lead time if you have questions. In extreme cases (e.g. getting emailed questions at all hours of the night before an 8 AM exam) you probably won't get a response until after the exam...yes I have had questions come in that late before...
  - Link to [Extra VBA review questions]

Front cover from the actual exam (so make sure you look at this beforehand...important so make sure you read them beforehand!):

Exam topics

Lecture topics covered so far (updated as the last day of lectures)	Approximate proportion of exam¹
Material covered before the midterm (computers, logic, spreadsheets)	~10%
Databases	37
VBA programming (excludes: accessing tables/shapes/images)	36
HTML and building a web page	Not on the exam
The Internet	Not on the exam
Computer security (up to and including slide #77)	15
Exam instructions (there will be a question on the final exam from the actual instructions so read the instructions)	~1%

Exam questions¹

Multiple choice questions: 57 marks total
	57 questions
Short answer questions: 34 marks total
	2 questions covering databases: MS-Access (18 marks)
	1 question that requires the writing of a computer program: VBA in MS-Word (13 marks)
	1 question on computer security (3 marks)

¹^{It's based on a near-final version of the midterm (exact proportions may vary
*slightly*)}

Min percent	GPA
0	0
10	0.1
20	0.3
30	0.5
35	0.7
40	1
43	1.1
45	1.2
47	1.3
49	1.4
51	1.5
53	1.6
54	1.7
55	1.8
56	1.9
58	2
60	2.1
61	2.2
62	2.3
61	2.4
62	2.5
63	2.6
64	2.7
66	2.8
68	2.9
69	3
70	3.1
71	3.2
72	3.3
73	3.4
75	3.5
77	3.6
79	3.7
81	3.8
83	3.9
85	4
90	4.1
94	4.2
98	4.3

VBA library documentation that will get for the final exam (keep in mind that not all library functions may have been covered during a particular semester)
Accessing the following VBA objects:
	Application: the MS-Office program running ActiveDocument: current MS-Word document Selection: currently selected text
Useful methods and attributes of the Application object
	Application.Windows.Count: number of open Word windows Application.CapsLock: a Boolean to indicate whether caps lock is turned on or off
Useful methods and attributes of the ActiveDocument object
	ActiveDocument.CountNumberedItems: total number of bulleted and numbered items ActiveDocument.Save: saves the current document ActiveDocument.SaveAs("<filename>"): save and rename document ActiveDocument.Select: make the entire document the current selection ActiveDocument.ActiveWindow.Caption = "<Caption>": change caption title in the Word window ActiveDocument.Close (<wdPromptToSaveChanges> or <wdDoNotSaveChanges> or <wdSaveChanges>): close current Word window and apply the effect of specified constant ActiveDocument.Checkspelling: run spell checker ActiveDocument.DeleteAllComments: delete all comments from the active document ActiveDocument.Printout: prints the active document ActiveDocument.SendMail: sends email with the active document as an attachment ActiveDocument.Words.Count: number of words in the MS-Word document ActiveDocument.Words.Comments.Count: number of annotation comments in the MS-Word document ActiveDocument.Select: select all the text in the active Word document ActiveDocument.Application: the application associated with the document ActiveDocument.Name: name of the active document ActiveDocument.Path: save path of the active document ActiveDocument.FullName: name & save path of the active document ActiveDocument.HasPassword: a Boolean to indicate if the active document is password protected ActiveDocument.password = "<Password>" : sets the password for the active document to the value specified ActiveDocument.SpellingChecked: a Boolean to indicate if the active document has been spell checked since last changed ActiveDocument.Saved: a Boolean to indicate if the active document has been saved since last changed ActiveDocument.SpellingErrors.Count: the number of spelling mistakes in the active document ActiveDocument.InlineShapes.Count: number of inserted inline shapes in the document ActiveDocument.Shapes.Count: number of primitive shapes in the document ActiveDocument.Tables(i) Allows access to one table from the collection of tables. 'i' is a positive integer (i.e. >= 1) ActiveDocument.Tables.Count: number of tables in the active document ActiveDocument.Tables(i).Sort(<Boolean>): 'i' is a positive integer, sorts the table specified by 'i' in the active document, the Boolean specifies if it is true that the tables include headers ActiveDocument.InlineShapes(i).height : height of inline shape # 'i' ActiveDocument.InlineShapes(i).width : width of inline shape # 'i' ActiveDocument.Shapes(1).Delete : delete shape # 'i' ActiveDocument.Shapes(i).height : height of shape # 'i' ActiveDocument.Shapes(i).width : width of shape # 'i' ActiveDocument.Shapes(6).Fill.ForeColor = <Color: vbBlack, vbRed, vbGreen, vbYellow, vbRed, vbBlue, vbWhite>: sets Shape # 'i' to the specified color
ActiveDocument: find and replace text ActiveDocument.Content.Find.Execute FindText:="<text>", ReplaceWith:="<text>", _ Replace:= <wdReplaceAll>, MatchCase:= <Boolean>
ActiveDocument: find and replace font effects (turn on/off) With ActiveDocument.Content.Find .Font.<Font effect> = <Boolean> With .Replacement .Font.<Font effect> = <Boolean> End With .Execute Replace:=wdReplaceAll End With
ActiveDocument: find and replace text styles in a document With ActiveDocument.Content.Find .Style = "<Search style>" With .Replacement .Style = "<Replacement style>" End With .Execute Replace:=wdReplaceAll End With
Counting the number of instances of a particular word in a document With ActiveDocument.Content.Find Do While .Execute(FindText:=<"searchWord">, Forward:=True, _ MatchWholeWord:=<True or False>) = True <Do something when each instance of search word is found> Loop End With
Useful methods and attributes of the Selection object
	Selection.Expand: expand selection to next grouping size of text Selection.Copy: copy selected text Selection.MoveLeft: move cursor left Selection.MoveRight: move cursor right Selection.ParagraphFormat.Alignment = <wdAlignParagraphCenter or wdAlignParagraphLeft or wdAlignParagraphRight>: to center, left or right align a paragraph (respectively) Selection.ParagraphFormat.SpaceAfter = <# points e.g. 0, 3, 6 etc.): Adds extra spacing to the end of paragraph Selection.ParagraphFormat.LeftIndent = InchesToPoints(# inches): # inches for left indenting of a paragraph Selection.ParagraphFormat.RightIndent = InchesToPoints(# inches): # inches for right indenting of a paragraph Selection.PasteAndFormat (<wdFormatOriginalFormatting or wdFormatPlainText>): copied text is pasted with the specified formatting Selection.Font.Name = <Font name> : change font type Selection.Font.Size = <Size> : change font size Selection.Font.Bold = <Boolean> : turn bolding on or off Selection.Font.Bold = wdToggle: toggle bolding Selection.Font.Italic = <Boolean> : turn italics on or off Selection.Font.Italic = wdToggle: toggle italics Selection.Font.Underline = <Boolean> : turn underlining on or off Selection.HomeKey Unit:= <wdStory>: move selection to the top of the document Selection.Type : returns information about the type of information selected (if any: <wdSelectionIP - none> <wdSelectionNormal - text selection> <wdSelectionShape - graphical shape selected> ) Selection.text = <"Selected text"> : writes the selected text to the document by setting the text attribute directly Selection.TypeText(<"Selected text">) : writes the selected text to the document through the method TypeText()
Common and useful collections
	InlineShapes(): includes all the external images inserted into a Word document Lists(): all the lists in a Word document Shapes(): all the primitive shapes in a Word document Tables(): all the tables in a Word document Documents(): the collection of documents currently opened
Useful methods and attributes of the Documents collection
	Documents.Item(i): Allows access to one document from the collection of documents. 'i' is a positive integer (i.e. >= 1) Documents.close(<wdPromptToSaveChanges> or <wdDoNotSaveChanges> or <wdSaveChanges>): closes all of the open Word documents Close all open Word documents and apply the effect of specified constant Documents.count: returns a positive integer that is equal to the number of documents currently opened. Documents(i).Save: save the document accessed through the index Documents(i).SaveAs (<"name">): save the document (accessed through the index) under another name Documents(i).PrintOut: print the document accessed through the index Documents.Open (<"location and name">): opens the document with the specified location and name and adds it to the documents collection
Useful functions/methods
	Len(<string>): this function returns the number of characters in the specified string Dir("<Path to a folder>") : returns the name of a document under the specified path Dir("<Path to a document>") : returns the name of the document under the specified path Dir : after the path to a folder has been specified with a previous call to Dir, this call returns the name of the next document in the specified folder CreateObject("<Name of MS-Office application>"): starts up the Office application specified in the brackets. <Name of an excel spreadsheet variable>.workbooks.Open("<Path and name of an Excel spreadsheet/worksheet>"): opens up the Excel spreadsheet at the specified location. <Name of an excel spreadsheet variable>.Range("<Cell in a spreadsheet>").Value: Returns as a string the contents of the cell of spreadsheet referred to by the spreadsheet variable.
Excel methods (accessible via VBA run through Word)
	<reference to an Excel window>.Visible = <Boolean> : Makes an Excel window visible or not visible <reference to an Excel window>.workbooks.open("<Path to an Excel document">) :Opens the Excel document named under the specified path and returns a reference to that document. <reference to an Excel window>.range("<Cell address>").Value : returns the contents of the specified cell

As mentioned VBA documentation WILL NOT be provided for the following (you're expected to know how to apply them without documentation)

Common and simple functions such as MsgBox and InputBox
Branching constructs (If-Then, If-Then Else, If-Then, ElseIf, Else) are not functions or methods
Logical (And, Or, Not), mathematical (+, -, *, /), string (concatentation) and relational (e.g., >=) operators
Looping constructs (For-Next, Do-While Loop)

VBA concepts that you did learn and are expected to know

(This is only a quick overview, you should look to the course notes for details)

How to create and run a VBA program (although you won't be asked questions about the VBA interface i.e. 'Where to click'
Defining a (single) subroutine
Variables
Named and pre-defined constants
Getting input via the InputBox and displaying output using a MsgBox and the status/title bar of the MS-Word window.
Naming conventions used for: variables, named constants, subroutines and the name of Word document that contains the macro
Logical, mathematical, string, and relational operators
Writing documentation (although you don't have to document your code writing solutions in the exam unless asked)
Branching/decisions
Looping/repetition
Accessing the objects and using the methods specified in the VBA library documentation (above)
What is the difference between the following file types: '.docm', '.docx' and '.doc' and the effect on computer security

You could see exam questions that involve any of the above questions in the form of VBA code writing or a program code trace.

HTML Tags that you will be given during the exam

Bold: <b></b>
Italics: <i></i>
Underline: <u></u>
Strike through: <s></s>
Break: <br>
Heading 1: <h1></h1>
Heading 2: <h2></h2>
Heading 3: <h3></h3>
Heading 4: <h4></h4>
Heading 5: <h5></h5>
Heading 6: <h6></h6>
Unordered list: <ul></ul>
Ordered list: <ol></ol>
List Element <li></li>
<embed src="<video name and path>" autostart="<Boolean>">
<img src= "<image name and path>" alt="<alternate text>">
<a href="<web address of linked document>"></a>
<a href="mailto:<Email address>"></a>

Material that you should study

The final exam is cumulative but with a focus on material covered after the midterm exam (this excludes databases, it may have been covered before the midterm but you weren't examined on this topic or you only saw a few questions at most on the midterm).

(Comments from the midterm that apply to the final exam)

The lecture material: the PowerPoint slides will provide you with an outline of the topics to be covered. However studying the information just listed on the slides won't be sufficient to do well on the final exam. You will also need to take notes in class and to study and understand those notes. If you have any questions then make sure that you ask me for help and clarification.

Using software applications: General
	You won't be asked about where different features are located in a particular program i.e., what is the sequence of clicks to invoke a particular feature of Excel

MS-Excel documentation

Basic mathematical operators: assignment (=), addition (+), subtraction (-), multiplication(*), division (/), exponnent(^)
Basic statistical formulas
	sum (<start cell> : <end cell>) average (<start cell> : <end cell>) min (<start cell> : <end cell>) max (<start cell> : <end cell>)
Counting functions (an alternative to entering the start: cell range is to manually enter the data sequence as the function argument).
	count (<start cell> : <end cell>) counta (<start cell> : <end cell>) countblank (<start cell> : <end cell>)
String (and related utility) functions (an alternative to entering the cell is to manually enter a string as the function argument).
	left (<cell>, <number of characters>) right (<cell>, <number of characters>) mid (<cell>, <start position>, <number of characters>) find(<find text>, <within text>, [<start position>]) concatenate(<cell₁>, cell_2,cell_3....cell_n)
Date and time functions
	today() now()
Branching function
	=if (<condition to check>, <return value: condition true>, <return value: condition false>)
Logical comparators (relational operators): less than (<), less than equal to (<=), greater than (>), greater than equal to (>=), equal to (=), not equal to (<>)
Lookup functions
	LOOKUP(<Lookup value>, <Lookup column (vector) Start : End>, <result column (vector) Start : End>) VLOOKUP(<Lookup value>, <Lookup table Start : End>, <Lookup table Column specifying the return value>, [<Exact match required?>])
Conditional counting functions (an alternative to entering the start:cell range is to manually enter the data sequence as the function argument).
	countif (<start cell> : <end cell>, <condition>) countifs (<start cell> : <end cell>, <condition>, (<start cell> : <end cell>, <condition>) )

Reminder: With many Excel functions (an alternative to entering the start : cell range is to directly enter the data sequence as the function argument).

MS-Access documentation

Characters that define input masks
	Numbers: 0, 9 (mandatory, optional) Alphabetic letters: L, ? (mandatory, optional) Alpha or digit: A, a (mandatory, optional) Digit, space, plus or minus sign: # (default is a space) Changing case: >,< (upper, lower)
Specifying validation rules (Strings): Like "<Format for String>"
	Formats (specifies cases where the full range of values may be input): Numbers [0-9], Alphabetic [A-Z], #, Wildcard *, Single character wild card ?

SQL

Single table query

SELECT: <Table name>.<Attribute name>, <Table name>.<Attribute name>...
FROM: <Table name>

Specifying conditions
WHERE: (Boolean expression¹)

^{1 The Boolean expression can be a compound expression. Also logical
operators may be used in the expression.}

Calculated values

SELECT <Table name.Attribute name1>,
<Table name.Attribute name2>, ...
<[Attribute name] or constant> <expression>
<[Attribute name] or constant> <expression> ...
<[Attribute name] or constant> AS <Query column name>
<Table name.Attribute name>

Ranking queries

ORDER BY <Table name1>.<Attribute name1>, <Table name1>.<Attribute name1>... <Table name1>.<Attribute name1>

Multi-table queries
SELECT: <Table name>.<Attribute name>, <Table name>.<Attribute name>...
FROM: <Table name> INNER JOIN <Table name>...INNER JOIN <Table name> ON <Table name>.<Primary key> = <Table name>.<Foreign key>... <Table name>.<Primary key> = <Table name>.<Foreign key>

The end of SQL queries are specified with a semi-colon