CSE 2111 Final Exam Study Guide Database Terms o Data A collection of numbers and text Meaning derived from the data o Information o Database o Field A large collection of data stored in a well defined structure An attribute piece of information of the table entity Defines the type of information that can be store i e text numbers dates A field or a collection of fields whose values uniquely identify each Columns o Field Type Data Type etc o Primary Key o Records Rows o Foreign Key record in a table A field that defines the relationship between two tables o Rules For Setting Up Relationships Must be a primary key that is unique in at least one of the tables The field names on each table do not have to match as long as the information is the same The related fields must be the same data type number text date etc o Tables A list of data organized into fields and records 1 Question structures to sort filter and select specific information A request for information from a database Structures for screen views of data Structures for written output of data Results of a query after it is run Also known as a subset of the database that is displayed You can make changes to the Dynaset and the changes will be reflected in your database because the Dynaset is just a view of your database When creating a query the Query By Example Grid is what is shown Also known as Structured Query Language Language you use to talk to the database You use the QBE Grid to input the SQL Access 2010 creates the SQL for o Queries o Forms o Reports o Dynaset o QBE Grid o SQL you o Inner Join Dynaset only includes records that have matching keys on both tables Default join type in Access o Boolean Operators AND OR For conditions in multiple fields the placement of your arguments determines the Boolean relationship between those arguments If a criteria is on the same line it is automatically considered an AND If a criteria is on a separate line it is automatically considered an OR 2 o Total Row Options Group By Define the groups you want to perform the calculations for Aggregate Functions Sum Average Minimum Maximum etc Where Specify criteria for a field so only those records that meet this criteria will be included in the aggregates Expression Use if a calculated field is require in the query Expressions will be calculated AFTER the fields are grouped o Outer Joins Need Many to One to Many Relationship Use a series of queries usually inner to create one final query The outer join will take all records that have a matching value in the foreign key field plus any unmatched records o Referential Data Integrity Ensures the relationships between tables remains consistent If there is a record row that is present in one table but not in the other referential data integrity has occurred Excel Terms o IF Function IF this is true do this else do this Logical test MUST evaluate to true or false o Nested IF o AND Function o OR Function o NOT Function IF this is true do this else IF this is true do this else do this Can be nested up to 7 times Returns true if all arguments evaluate to true Returns true if at least one argument evaluates to true 3 Changes false to true Changes true to false o COUNTIF Function Counts the number of items in a range that meets a specific criteria Range A continuous range Criteria Determines what cells to count o COUNTIFS Function Counts the number of items in a range using multiple criteria and multiple ranges that meet a specific criteria ALL criterion must be true in order for the cell to be counted o SUMIF Function Sums the number of items in a range that meet a specific criteria Criteria Range A continuous range Criteria Determines what cells to sum Sum Range If criteria is met the computer will sum the corresponding entry in this range o AVERAGEIF Function Averages the number of items in a range that meet a specific criteria Criteria Range A continuous range Criteria Determines what cells to average Average Range If criteria is met the computer will average the corresponding entry in this range o LARGE Function Returns the kth largest value in a range Array A continuous range k The position from the largest in the array range o SMALL Function Returns the kth smallest value in a range Array A continuous range 4 k The position from the smallest in the array range o RANK EQ Function Returns the rank of a number in a list of numbers Number The number whose rank you want to find Ref The array or range of values to rank Order A number specifying how to rank the numbers 0 Ranks in descending order 1 Ranks in ascending order o SUMPRODUCT Function Multiplies the corresponding components in the given arrays and returns Array1 The first array argument whose components you want to the sum of those products multiply and then add The array arguments must be the same dimension Treats non numeric cells as 0 o VLOOKUP Function Finds an entry from a vertical array based on a criteria Lookup Value Criteria to lookup or match Table Array The range or boundary of your table excluding headings Col Index Num The column number in your range that contains the corresponding data Range Lookup array True Finds the exact match or the next lower value in your table o Leftmost column should contain the table range o Rightmost column should contain the value o Leftmost column must be in ascending order 5 False Finds an exact match in your table array o Leftmost column should contain the table range o Rightmost column should contain the value o HLOOKUP Function Finds an entry from a horizontal array based on a criteria Lookup Value Criteria to lookup or match Table Array The range or boundary of your table excluding headings Row Index Num The column number in your range that contains the corresponding data Range Lookup array True Finds the exact match or the next lower value in your table o Leftmost column should contain the table range o Rightmost column should contain the value o Leftmost column must be in ascending order False Finds an exact match in your table array o Leftmost column should contain the table range o Rightmost column should contain the value o IFERROR Function Returns a value you specify if a formula evaluates to an error otherwise it returns the result of the formula 6 o Financial Functions transaction Present Value PV What you get pay at the beginning of the financial Future Value FV What you are going to get OR what you will have to pay at the end of the financial transaction Payment PMT Payment made