Study Guides (248,496)
Canada (121,595)
Final

Computer Science Exam Review.docx

64 Pages
85 Views
Unlock Document

Department
Computer Science
Course
COMPSCI 1BA3
Professor
Anthony Hurst
Semester
Fall

Description
Computer Science Exam Review Excel: Comp Sci Notes: EXCEL Session 1: • Excel stores spreadsheets in workbooks • Name box- displays cell reference of active cell • Select All- selects all active cells • Row Headings- numbered rows • Formula bar-displays value • Workbook is made of sheets- worksheets and chartsheets • Sheet Tab scrolling scrolls through sheets • Zoom controls zoom in out • Column headings- letters identifying different columns • What-if analysis- change one value, changes the others • Ribbon- top heading bar • Chart sheets contain visuals and can be placed in Worksheets • Ctrl page up and down changes sheets • Planner sheet- what problems solved? What data needed? What calculations entered? What form should my solution be in? • Text data= text string • Text if left aligned, numbers are right aligned • Truncated- hidden texts (cells too small) • Autocomplete-feature that tries to predict what you are typing in the cell reference box • Alt and Enter make multiple lines in a cell • A pixel is a single point on a computer monitor • A point Is 1/72 of an inch • Autofitting fits all data into biggest cell throughout column • Clearing-removes data Deleting- removes data and cells • Spellchecker • Page Layout tab- changes way worksheets are appeared • Group of cells is a cell range • Status bar- ready or in edit mode • Non adjacent range- two or more distinct non adjacent ranges • View- changes how viewed • Adjacent range- group of cells in single block of cells • Auto Sum- Sum average etc, formulas. • Range Reference- location and size of range • Drag and drop- drag range and replace it • Cutting- cut and paste • Operators- used to make formulas combining diff values making one value • Arithmetic operators- perform additions subtraction etc • Order of precedence- is a set of predefined rules used to determine sequence in which operators are used. • Functions- named operation performs a value • Edit mode- shortcuts only apply to texts • Find command to locate characters- search string-replacement string • Normal View, Page Break Preview (page breaks), Page layout view (printed) • Portrait and Landscape • Formula view- only formulas viewed • A formula is an expression that returns a value Begins with = Operators combine different values Generally arithmetic operators ex Sum, Divide Usually use numbers stored within cells Order of precedence applies (OOO) • To enter a formula: Type in a cell Select cells Verify via F2 (which cells are in formula) • Scaling- fitting pages to print • Rows and Columns intersect at cells- cell reference • Intersection of a row and column is a cell • The file tab opens backstage view- file level options and settings • Related commands are organized in groups on a tab • The dialog box launcher opens options for a tab • F1- help key • 3 types of DATA - Text: a combination of letters, numbers, and some symbols that form words and sentences -Number: any numerical value that can be used in a mathematical calculation -Date and Time: commonly recognized formats for date and time values • The main reason to use excel is to display values calculated Session 5 • To enter a function: - Type - select cells - Function wizard • AutoSum can be used for Sum, Average, Count, Minimum, and Maximum - Be cautious as this function makes assumptions, use F2 or double click to verify • To edit, F2 editing mode, esc to erase edits and enter to complete them • Table name • Each column represents a field • Header row, beginning row • Rows represent a record • Filter order- filters records • An Excel Table is a structured range of data that is managed independently from the rest of the data in a worksheet. Why do we use excel tables? - Can format table quickly - The table range is expanded automatically - Can add a total row to calculate summary functions - Formulas entered in one table cell are automatically copied down - Can create formulas that reference cells in a table using table and column names, rather than cell addresses - Can sort and filter data easily • Sorting - columns filtered -Can be applied to one or more columns -Custom sorting lists can be created • Filtering -Can be applied to one or more columns -Can filter by exact values or broader criteria filters • Data definition table- list fields to be maintained for each record • Freezing lets you keep headings • Totals and Subtotals -Totals available through the “Total Row” box -Subtotals can only be applied to ranges -Sort the data by the column you want to subtotal -“Convert to Range” -Data tab, Outline group, Subtotal button -“At each change in” (sorted column) -Outline levels allow you to view the data in various levels of detail • Convert table to range for subtotals etc hides total row • Structured References -You can replace the specific cell or range address with a structured reference, the actual table name or column header -Syntax: -Tablename[qualifier] -Example: Summary [Sales] • Qualified vs. unqualified references -Unqualified references don’t include the table name. -You can use unqualified references in a calculated column or formula within an Excel table, however you need to use qualified references outside the table or in another worksheet when referring to an Excel table or a portion of the table. • Ascending order filters a-z, descending z-a • First sort field is primary, second is secondary • Custom list- indicates sequence you want data to be ordered • Selections list- displays unique items in selected column • Split bar- division between panes • Clear- removes filters • Criteria filters- enable you to specify various conditions in addition to those that are based on an equal’s criterion. • The layout is the four categories you can see in the bottom right corner of the pivot chart layout • A Pivot table- puts data in a concise table -Sometimes large tables can be difficult to interpret -PivotTables allow us to summarize data into categories using functions (detailed data is not shown) -Quick and concise analysis -Named as such since data can easily be “pivoted” or moved around in the table • Terminology of Pivot Tables -Values fields- the fields containing the data to be summarized -Category fields- the fields that the value fields will be grouped by -Rows -Columns • PivotChart- of a table • Slicer- filters pivot charts and tables • Category Fields- are the fields that group the values in a pivot table. • Filtering a field helps focus on one thing Session 7: • Structured Reference- When you create a formula that references all or parts of an Excel table, you can replace a specific cell or range address with a structured reference which is the actual table name or column header • The AND FUNCTION- logical function that returns a TRUE value if ALL are true, and a FALSE value if any or all of the logical conditions are false, used for multiple conditions. -Syntax: =AND (logical1 [,logical2]…) Can nest in an IF statement as the logical test Reminder: =IF(logical_test, value_if_true,[value_if_false]) Example: =IF(AND(A1=1,a2=1),”yes”,”no”) • Functions Argument box, box to build functions • The IF FUNCTION is a logical function that evaluates a condition, and then returns one value if the condition is true and a different value if it’s false -Syntax: =IF(logical_test, value_if_true, [value_if_false]) • Calculated Column- formula in one cell=all cells • Use AND function as logical test of IF function to test two or more conditions to see if all are TRUE • To determine if ANY are TRUE, use OR function as logical test of the IF function • OR Function -Allows you to test multiple conditions -Returns either TRUE or FALSE -Returns TRUE if any tested values are TRUE and FALSE if all of the tested values are FALSE -Syntax: =OR (logical1 [,logical2]…) -Can nest in an IF statement as the logical test -Reminder: =IF(logical_test,value_if_true,[value_if_false]) -Ex. =IF(A1=1,A2=1),”yes”,”no”) • A logical condition is an expression such as H2=”Y” that returns either a TRUE or FALSE value. • A NESTED IF function is when one IF function is placed in another to evaluate another condition -Nested IF Functions =IF([Pay Grade]=1,2500, IF([Pay Grade]=2,5000, 7500)) Structured references make the formula easier to write, use absolute reference b/c this value is the same for all rows • LOOKUP TABLE -A table that organizes data you want to retrieve into different categories -The categories for the lookup table, called compare values, are located in the table’s first column or row -To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values • Using the lookup table to find an exact match -VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) • Change range to make an approximate lookup match • The LOOKUP VALUE is the value you are trying to find. • Invalid code- IFERROR • VLOOKUP function searches vertically down the first column of the lookup table and retrieves the appropriate value from the table • Lookup functions allow you to use tables of data to lookup values insert them in another worksheet location. • Absolute reference- when you only reference on condition and one formula that changes throughout the field, this is when you need to use absolute referencing • Relative Referencing- when using one formula that relates to all the other fields you can just drag it down and relative reference the formula with all cells. • An EXACT match lookup occurs when the value entered must match one of the values in the first column of the lookup table. • Approx match lookup occurs when the value entered falls within a range of values in the first column of the lookup tables • HLOOKUP function searches horizontally across the top row of the lookup table and retrieves the value in the column you specify. • HLOOKUP syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) • Errors values indicate that some element of the formula or a cell referenced in a formula is preventing excel from returning a calculated value. • Formatting (colors, highlighting etc.) • Conditional Formatting- change format when something specific occurs • Create a new rule in conditional formatting box- Rules manager- type of formatting when something occurs • It defies the condition cell and range to put the rule on • Rules manager-edit conditions and rules • Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value • The IFERROR function can determine if a cell contains an error value and display the message you choose rather than the default error value • You can calculate the number of cells in a range that match criteria you specify using the COUNTIF function, which is sometimes referred to as a conditional count -Syntax: =COUNTIF(range,criteria) • You can add the values in a range that meet criteria you specify using the SUMIF function, which is also called a conditional sum -Syntax: =SUMIF(range,criteria[,sum_range]) • You use the AVERAGEIF function to calculate the average of values in a range that meet criteria you specify -Syntax: =AVERAGEIF(range,criteria[,average_range]) • You use the AVERAGEIF function to calculate the average of values in a range that meet criteria you specify -Syntax: =AVERAGEIF(range,criteria[,average_range]) Session 10.1-10.2 • What if Analysis -What is it? -Different methods available -Goal Seek -One-Variable Data Table -Two-Variable Data Table -Scenarios Solver • Cost-volume-profit (CVP) analysis (sometimes called a break event analysis) expresses the relationship between a company’s expenses, its volume of business, and the resulting profit or net income -Types of expenses -Variable expenses change in proportion to the amount of business a company does (i.e. manufacturing materials) -A fixed expense is an expense that must be paid regardless of sales volume (i.e. rent) • The break-even point is the point where revenue equals expenses -Breakeven, Net income = 0 • We can input an income statement into Excel and then perform: -What if analysis: -Change the value of an input cell and observe the effects on calculated cells -Goal seeking: -Specify the value for a calculated cell, returns the input value needed to reach that goal -Very useful for doing simple queries to see what the effects are of changing the value of one variable -For more complex analyses, one could do several rounds of Goal Seeking -A more efficient approach is to use a data table -A data table organizes the results of several what-if analyses within a single table • One variable table, change one input to change several results, such as changing inputs to see how results such as revenue expenses and output change. Can put into a chart • Two Variable tables, change more than one input, get one result, input formulas in between input and result values on chart, then hide formula. • What is a scenario? -A set of input values used to perform a what-if analysis -Need to create scenarios if the what-if-analysis is more than two input cells -Scenario Manager is a feature within Excel that enables you to create as many scenarios as you want, easily switching between the different scenarios to display the results of several what-if analyses. Scenario Manager Box- to add and view scenarios etc • Input cells- what-if analysis cells you want to modify • Input values- entered in input cells • Result Cells- contain calculated values examined • Result values • COST VOLUME PROFIT ANALYSIS- branch of financial analyses that studies the relationship between cost and profit • Variable expenses change • Fixed expenses- do not change • Mixed expense- part variable and fixed • Break even analysis- revenue = expenses • Goal seek- automates the trial and error process by allowing to pick values and see how it affected the total goal • Row input cell- base values based on first row • Column input cell- cell on which values placed in first column are based. An Excel Table is a structured range of data that is managed independently from the rest of the data in a worksheet Subtotals can only be applied to ranges You can replace the specific cell or range address with a structured reference, the actual table name or column header Qualified vs. unqualified references -Unqualified references Dont include the table name -You can use unqualified references in a calculated column or formula within an Excel table, however you need to use qualified references outside the table or in another worksheet when referring to an Excel table or a portion of the table. PivotTables allow us to summarize data into categories using functions (detailed data is not shown) Values fields- the fields containing the data to be summarized Category fields- the fields that the value fields will be grouped by -Rows -Columns IF Function: -Evaluates whether a condition is true or false (called a logical test) and returns one value if the condition is true, and another value if the condition is false. ◦ If true then value “a” else value “b” ◦ Syntax: =IF(logical_test, value_if_true, [value_if_false]) AND Function ◦ Allows you to test multiple conditions ◦ Returns either TRUE or FALSE ◦ Only returns TRUE if all tested values are TRUE ◦ Syntax: =AND (logical1 [,logical2]…) ◦ Can nest In an IF statement as the logical test Remainder =IF(AND(A1=1,A2=1),”yes OR Function ◦ Allows you to test multiple conditions ◦ Returns either TRUE or FALSE ◦ Returns TRUE if any tested values are TRUE and FALSE if all of the tested values are FALSE ◦ Syntax: =OR (logical1 [,logical2]…) ◦ Can nest in an IF statement as the logical test ◦ Remainder, =IF(logical_test, Value_if_true, [value_if_false] A nested IF function is when one IF function is placed inside another IF function to test an additional condition What is a lookup table? A table that organizes data you want to retrieve into different categories -The categories for the lookup table, called compare values, are located in the table’s first column or row -To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values  Using the lookup table to find an approximate match  Searches the first column of the table until it locates the largest value that is less than the lookup value  Using the lookup table to find an exact match  VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Conditional Formatting: Allows you to change a cell’s formatting when its contents match a specified condition Rules manager: A rule specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs Conditional Formatting: -Allows you to change a cell’s formatting when its contents match a specified condition. -When would you use this? To highlight those cells whose values may warrant action E.g., duplicate records, negative balances, overdue dates Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value Cost-volume-profit (CVP) analysis (sometimes called a break event analysis) expresses the relationship between a company’s expenses, its volume of business, and the resulting profit or net income Types of expenses: Variable expenses change in proportion to the amount of business a company does (i.e. manufacturing materials) A fixed expense is an expense that must be paid regardless of sales volume (i.e. rent) What if analysis: Change the value of an input cell and observe the effects on calculated cells Goal seeking: Specify the value for a calculated cell, returns the input value needed to reach that goal  Very useful for doing simple queries to see what the effects are of changing the value of one variable  For more complex analyses, one could do several rounds of Goal Seeking  A more efficient approach is to use a data table ◦ A data table organizes the results of several what-if analyses within a single table One variable data table  Specify one input cell and any number of result cells Two variable data table  Analyzes a variety of combinations simultaneously  Uses two input cells, but displays only a single result value  What is a scenario? ◦ A set of input values used to perform a what-if analysis ◦ Need to create scenarios if the what-if-analysis is more than two input cells  Scenario Manager is a feature within Excel that enables you to create as many scenarios as you want, easily switching between the different scenarios to display the results of several what-if analyses. A scenario summary report lists the values for the changing and result cells for each scenario Questions on test possibly…. Session 1.1 1. What are the 2 types of sheets used in a workbook? Chart sheets and worksheets 2. List 2 ways of identifying the active cell in the worksheet. The active cell is surrounded by a thick border and its cell reference appears in the Name box. 3. What is the cell reference for the cell located in the fourth column in the third row? D3 4. What keyboard shortcut changes the active cell to cell A1? The Ctrl+Home keys 5. What is text data? A combination of alphanumerical characters that form words and sentences (called a text string) 6. What keys do you press to start a new line of text within a cell? Enter the first line of text, press the Alt+Enter keys, and then type the second line of text. 7. A cell A4 contains may.3 2006; why doesn’t excel consider this data a text string? Because it’s a date; all dates are numbers formatted to appear in standard date formats. 8. Explaining the difference between clearing a row and deleting a row? Clearing a row removes only the contents of the row, deleting a row removes the contents and the row. Session 1.2 1. Describe the 2 types of ranges in excel. Adjacent ranges contain a rectangular block of cells; nonadjacent ranges contain a collection of adjacent ranges. 2. What is the range reference for cells A3 through G5 and J3 through M5? A3:G5;J3:M5 3. What formula would you enter to add the values in cells B4, B5, B6? What function would you enter to achieve the same result? =B4+B5+B6; =SUM(B4:B6) 4. How do you rename a worksheet? Double-click the sheet tab, and then type a new name on the sheet tab. 5. Describe the four ways of viewing excel content in the workbook. Normal view shows the columns and rows of the worksheet. Page Layout view shows the layout of the worksheet as it appears on a page. Page Break Preview shows the page breaks within the worksheet. Formula view shows formulas rather than the values returned by the formulas. 6. How are page breaks indicated in page break preview? as dotted lines 7. How do you display the formulas used in a worksheet? Press the Ctrl+ˋ keys to switch to formula view. 8. Why would you scale a worksheet? to force a worksheet to print on one page Session 5.1 1. What is the purpose of the freeze panes button in the window group on the view tab? Why is this feature helpful? To keep, or freeze, rows and columns so that they don’t scroll out of view as you move around the worksheet. Freezing the rows and columns that contain headings makes understanding the data in each record easier. 2. What three elements indicate an Excel table is created in the worksheet? Filter arrows appear in the column headers, a table style format is applied to the table, and the Table Tools Design tab appears on the Ribbon. 3. A field that you use to order data is referred to as…. sort 4. Sort by year of graduation and then by last name. 5. How do you enter a new record in an excel table? Enter the data for the new record in the row immediately following the last row of data in the table. 6. Create a custom list. 7. descending (Newest to Oldest) Session 5.2 1. Explain the relationship between the sort and subtotal commands. You must first sort the data for which you want to calculate subtotals because subtotals are inserted whenever the value in the specified field changes. 2. An excel table includes records for 500 employees. What can you use to calculate the average salary of employees in the finance department? Filter the table to show only the finance department, and then use the AVERAGE function in the Total row. 3. Click the Major filter arrow, and then check only the Marketing check box. Click the GPA filter arrow, point to Number Filters, click Greater Than or Equal To, and then enter the value 3.0 in the box to specify the condition for a GPA greater than or equal to 3.0. 4. After you display subtotals, how can you change the amount of detail displayed? Click the Level Outline buttons. 5. The COUNT function is a valid subtotal function when using the subtotal command. True 6. Create a multiselect filter. Select multiple items from the Position filter menu. 7. Click the Gender filter arrow, and check only the Female check box. Insert the Total row, click the arrow that appears to the right of the total for the Salary column, and then click Average in the list of functions. Session 5.3 1. What is the default summary function for numeric data in a PivotTable? SUM 2. When creating a pivot table, what do you use to lay out the fields in the pivot Table report? PivotTable Field List box 3. After an update in an excel table. refresh the PivotTable 4. How are fields such as region, state, and country most likely to appear in a pivotTable? as rows labels, column labels, or report filters 5. How are fields such as revenue, costs, and profits most likely to appear in a pivot table? as values 6. (a) Filter; (b) PivotTable 7. When data acquired field is displayed in a row of a pivot table by month or year you have….. The date acquired field. Grouped 8. filter the PivotChart Section 7.1 Quick Check 1. What changes occur in the appearance and size of an Excel table after you enter a new column header named “phone”? The table style is applied to all rows in the new column; the range of the Excel table expands to include the new column (Phone). All features that apply to other columns in the table also apply to the Phone column. 2. Whenever you enter a formula in an empty column of an excel table, excel automatically fills the column with the same formula. This is called? a calculated column 3. In cell Q7, enter = IF(Q5*Q6 > 10000,Q5*Q6,10000) 4. The AND function is a logical function that returns TRUE if any of the logical conditions are true and FALSE if all of the logical conditions are false. False; the AND function is a logical function that returns a TRUE value if all logical conditions are TRUE; otherwise, it returns a FALSE value. 5. Write the formula that displays the label outstanding if the amount owed (cell X5) is greater than 0 and the transaction date (cell R5) is before 3/15/2013, but otherwise leaves a blank cell. =IF(AND(X5>0,R5<3/15/2013),“Outstanding”,“”) 6. When you create a formula that references all or parts of an excel table you can use…………….. to replace the specific cell or range address with the actual table or column header names. structured references 7. Ignore; all tests are False so the OR function returns FALSE 8. OR(B25<17,B25>42) Section 7.2 Quick Check 1. When you include one IF function inside another IF function, you have created, nested IF 2. Younger 3. Explain the difference between an exact match and approximate match table look up. An exact match compares the lookup value to the compare value. They must be equal for a value to be returned from the lookup table. An approximate match also compares the lookup value to the compare value. The two values do not have to be equal, just fall with a range of values for Excel to return a value from the lookup table. 4. the VLOOKUP function 5. 0 Freshmen 3 Sophomor 0 e 6 Junior 0 9 Senior 0 6. =IFERROR(W5/W25,”Dividing by zero”) 7. HLOOKUP Section 7.3 Quick Check 1. Would you apply the duplicate value conditional formatting rule to a table column of last names? No. Duplicate last names do not necessarily mean that the data in the table is a repeated; it could be that two or more people have the same last name. 2. If you receive a worksheet that includes conditional formatting, which dialogue box would you use to find out what criteria were used for the formatting? Conditional Formatting Rules Manager dialog box 3. Explain what the formula =COUNTIF(Employee[Sex],”F”) calculates. It counts the number of females in the Employee table. 4. Explain what the formula =AVERAGEIF(Employee[PAY TYPE],”H”,Employee[Annual Salary]) calculates It calculates the average salary for all hourly employees. 5. SUMIF It sums the annual salaries of all full-time employees 6.COUNTIF It counts the number of employees earning more than $100,000. 7. To display the number of females working from home, you could use the COUNTIF function. False, this requires two conditions. The COUNTIF can only have one condition. 8. Applying the duplicate value conditional formatting rule deletes the duplicate record. False, the duplicate values conditional format highlights duplicates it does not delete them. Session 10.1 1. Describe the difference between what-if-analysis and goal seek. In a what-if analysis, you change the input cell to observe the value in the result cell. With Goal Seek, you define a value that you want to obtain for the result cell and then you determine what value is required in the input cell. 2. Name the three components of the goal seek command. The Set cell input box, in which you specify the cell containing the value you want to set; the To value input box, in which you specify the value for the Set cell; and the By changing cell input box, in which you specify which cell in the worksheet to change to achieve the desired result. 3. What is a data table? What is an input cell? What is a result cell? A data table is a table that shows the outcomes of several what-if analyses. The input cell is a cell in the table containing a value you’re interested in changing to examine its impact in the what-if analysis. The result cell is the result you’re interested in viewing based on the changing value of the input cell. 4. What is a one variable data table? What is a two variable data table? A one- variable data table is a data table with a single column or row of input values and multiple results. A two-variable data table is a data table with two input values and a single result. 5. How many result cells can you display with a one variable data table? How many result cells can you display with a two variable data table? There is no limit to the number of results cells with a one-variable data table. There is only one result cell for a two-variable data table. 6. Cell E5 contains the formula =B10. You want to display “Profits” instead of the formulas value. What custom format do you use? enter the custom format, "Profits" 7. What is an array formula? An array formula is a formula that acts upon a value array, returning either a single value or an array of values. Session 10.2 1. What is one advantage of scenarios over data tables? Scenarios enable you to perform what-if analyses using several input and result cells. 2. What should you do before creating a scenario report to make the entries on the report easier to interpret? Define customized names to reference the input and result cells. 3. What are changing cells? Changing cells are cells that contain values you change under the different scenarios. 4. What are result cells? Result cells display the output values of interest in the different scenarios. 5. Where do you define result cells in the scenario manager? Results cells are defined when creating scenario summary reports by selecting the cell range in the Scenario Summary dialog box. 6. How do you display a scenario in the active worksheet? Open the Scenario Manager dialog box, and then select the scenario to view from the Scenarios list box. Click the Show button to display the scenario in the active worksheet. 7. How do you create a scenario pivot table report? Click the Summary button in the Scenario Manager dialog box, click the Scenario PivotTable Report option button, select the result cells you want to display in the PivotTable, and then click the OK button to generate the PivotTable report. ACCESS REVIEW • relational database management systems (RDBMS) • Query = “ask dbase a question” = English  RDBMS syntax; Form (input), Report (output) • Navigation Pane – database objects; Datasheet View – direct entry of fields and records • Organizing Data- The first step is to identify each entity, and its individual attributes -Entity: a person, object or event- attributes as a set of fields, each entity is a table -Each field (i.e., column) has a specific data-type -A set of field values is called a record (i.e., row) -Fully relational • An Access table is a two-dimensional representation of a relation. • A group of related tables is properly known as a relational database; Access simply uses “database” • You can connect the records in the separate tables through a common field (also called a join field) • A primary key (PK) is a field, or a collection of fields, whose values uniquely identify each record in a table • When you include the PK from one table as a field in a second table, to form a relationship between the two tables, the field is called a foreign key (FK) with respect to its use in the second table • Foreign Key and Primary key data must be the same- the name of the field does not need to be the same. Same values in different tables. When you have a field that is a primary key in one table, and it exists in a second table, and it is not the entire primary key in that second table, then it is a foreign key. • A join operation creates something akin to a result table containing “master records”, in which the join field is often duplicated in the result table. • Field Value - First set of field values (very top row) • Record – The set of data under the field value • Database (Relational Database)- Collection of related tables • Common Field – connection in separate tables • Primary Key – Uniquely field represented a record • Foreign Key – Relationship of field between two different tables • (DBSM) Database Management System – software programing system. Allows for creation manipulation of data. • Relational database management system- common field of data collected in tables. • Data type – the field value you enter • AutoNumber – inserts a unique number in the fields of every record • Layout view – make design changes to the form while its displaying data, so that you can see the effects of the changes you make immediately. • Data Dictionary: Design Data describing table tools • Keys: Keys involving more than one field are referred to as composite, concatenated or compound keys -A field which has a set of unique values and can therefore be used to “identify” a particular record is known as a candidate key; if a candidate key does not exist naturally, an artificial one must be created (i.e., to become the PK) -Restrictions on Keys: -PK must have unique values; no duplicates allowed -PK must not have a null value; a data value is required -For each non-null FK value, a corresponding PK value must exist elsewhere (i.e., in another table in the database) -FK values may be duplicated, but “satellite” data are different for each occurrence; FK can have null value in some instances -NOTE: keys are often, BUT NOT NECESSARILY, used as the basis for sorting the records in a table • -Relationships are connected through foreign keys. Sequences of characters are called strings. Concatenated means joining two fields together to form a primary key. Candidate key- unique values. Keys are unrelated to sorting, although we do sort things based on primary key values because they are unique. Foreign keys exist on how tables are related. • Types of relationships among tables: -One to One (1:1) -One to Many (1:N) -Many to Many (M:N) -Examples: 1:1 -Husband/Wife …1:N -Parent/Child; Account/Transaction …M:N- Doctor/Patient; Supplier/Customer; Bus/Route; Book/Author • Relationships expressed in English with the help of verbs: -Patient visits Doctor, Producer makes Film, Athlete plays on a Team, and Inventory has Products -Consider the “multi-valued” attributes that exist in the following pair of tables (expressed in Relational Notation): -HR: Employee, Child’s Name -LIBRARY: Title, Author • Normalization = a process used by designers to eliminate undesirable anomalies that can occur during the daily management of the database; traditional problems associated with accuracy and integrity are avoided through careful analysis of the table relationships; a typical approach is to decompose large tables into smaller, related tables -e.g., 1NF: no multi-valued attributes are allowed in tables -e.g., 3NF: no transitive dependencies (i.e., non-primary key values depend solely upon primary key values) -Note: 3NF is considered the minimal normal form necessary for a working database design - What it takes to determine if a relational data base is fully relational, 12 rules. No data base is 100% fully relational. The relational data base system uses tables (relations) to represent everything (ALL*) that is in the data base file. This is not the case in access. All the information has to be represented in tables. Change view/Toggle View, -Data Sheet View or Design View. The set of properties is shown in the general table in Design. You can change general properties so that relational data bases follow the rules. Etc. no null values. SQL allows us to create queries that question the data-base. All info must be represented in tables (MIDTERM QUESTION TIP) to be fully relational. When you cannot fit the amount of properties into fields, you create another table. Alt key relation- multivalued attributes (both in the primary key so they are unique). • Sharing and Security -Isolation -DBA can create Views for individuals/groups, to protect sensitive data (e.g., government records, client data, employee personal data, business operational data) -Locking shared data - classic scenario = Readers & Writers Problem (writers are whoever can change the information) (readers are users using the databases) When reader is reading while writer is updating- lost update -Protection: user passwords, capability controls, time controls, monitoring- updates on software etc • Recovery: backup, transaction logging, checkpoints • Overall database design formally known as a “schema”, views are “sub- schemas” • NOTE: Access uses the term “view” in a very different manner [View button toggles- Datasheet View, Design View are not sub-schemas] • Real-Time Operation -Serialization- order of operations -Sequencing of operations, time-stamping of transactions- such as short-selling (stock market investing ex.) -Update anomalies, Lost Updates -E.g., banking, international transactions, stock markets, library book loans, air traffic control, astronomy, airline or hotel reservations, bus or taxi dispatching -Scientific experiments (real-time data capture) -Time Zones • The design table is not a table like a table in the data base • E.Codds 12 rules, one important rule- that all information has to be represented through tables, through relations in a relational database system for it be to fully relational. http://en.wikipedia.org/wiki/Edgar_F._Codd (test material) • • Three types of user: end-user, programmer, DBA- data base administrator • SQL- what we use to create queries. Coded form (server Query Language) • Note: Generally wise to avoid using AutoNumber data-type for PK; also avoid accepting Access suggestions for default PK (i.e., explicitly choose your own) • Create Tabs: • • • Queries: -A query can be thought of as a question you “ask” about the data stored in the database -E.g., Which Canadian provinces have an average summer temperature above 25 Celsius? -E.g., Which salespersons have sold the most trucks so far this year? -Simple Query Wizard used to quickly select records & fields to form a question -Queries are saved as objects in the Access file -The query is not actually entered in a natural language; several methods available (e.g., SQL, QBE, Wizard, Report) • -Typically, a query result is not stored permanently in the database – only the query design is saved as an object (in SQL form) • Forms: -A form is an object you use to enter, edit, and view records in a database -You can design your own forms, use the Form Wizard, or use the Form tool to create a simple form quickly and easily • • Simple Report: -A report is a formatted printout (or screen display) of the contents of one or more tables in a
More Less

Related notes for COMPSCI 1BA3

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit