IS201 Final

Financial Functions in Excel

1. Financial functions: FV, PV, PMT, IPMT, PPMT, NPER, RATE Cumulative interest and principal payment functions: CUMIPMT, CUMIPRINC

What is referential integrity?

In its broadest use, "data integrity" refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct. ... Data integrity is imposed within a database when it is designed and is authenticated through the ongoing use of error checking and validation routines

What does the lookup wizard do in a table in Access?

The lookup wizard looks for a data in another table and it displays that data in a table having a field of multiple select. The lookup wizard simplifies the process and automatically shows the required field data.

How does a workbook differ from a database management system?

The main difference between Spreadsheet and Database is that the Spreadsheet is a computer application that helps to arrange, manage and calculate data, while Database is a collection of related data that is organized in a way to access data easily

Be able to describe the different parts of the design grid used to create a SELECT query in Access.

When we want to select data from a table or different tables we use SELECT query. By selecting the Query Wizard it automatically creates the environment for a select query. For SELECT query we click query wizard then we add a table and then the available fields by double clicking each field from the table. After selecting the fields we cling the run button on the design menu and it SELECTS the data that we requested for.

1. In examples of a 1:M (one-to-many) relationship, where will the foreign key be stored? How can we work it out?

While implementing one to many relationshipsthe foreign key can be stored in the child table. For example if we have two tables Student and Fee, this way the Student table is the parent table which consist of Student Primary ID and that primary ID will be included as Foreign key in the Fee table for which we can retrieve records through that foreign key for students

Workbook design.

Workbook design means it's used to create grids of text, numbers and formulas specifying calculations. That's extremely valuable for many businesses, which use it to record expenditures and income, plan budgets, chart data and succinctly present fiscal results.

Lookup

functions: VLOOKUP, HLOOKUP, MATCH, INDEX

Database Management System (DBMS)

refers to the technology solution used to optimize and manage the storage and retrieval of data from databases. DBMS allows to define, create, maintain and control access to the database, Insert, Update, Delete, and selecting records are also features.

What is a computer spreadsheet and how is it organized?

spreadsheet or worksheet is a file made of rows and columns that help sort, organize, and arrange data efficiently, and calculate numerical data. What makes a spreadsheet software program unique is its ability to calculate values using mathematical formulas and the data in cells

Drawbacks of DBMS

· Cost: The cost of maintaining the hardware, software, and personnel required to operate and manage a database system can be substantial. Training, licensing, and regulation compliance costs are often overlooked when database systems are implemented

What are the benefits and drawbacks for using a DBMS vs. a spreadsheet to store data?

· DBMS can organizes data into fields and records but spreadsheet stores data in a grid of rows and columns allowing for calculations and the generation of graphs. · 2. DBMS performs on Data Quality check means database allows for certain variables to be set as the only possible entries but in Spreadsheets allow value categories such as numbers, but not the customization of the database. · 3. DBMS by default allows data from multiple source to be integrates into one database bit in spreadsheet it requires other applications or custom program to allow this feature. § Efficiency for database is more easier rather than spreadsheet · Cost is higher for DBMS rather than using spreadsheet. · 2. If you want your data public then Spreadsheet is more easier rather than DBMS. · 3. In DBMS it requires expert for maintaining the data but in spreadsheet it does not require any technical expert. 4. If you does not have huge data then spreadsheet is more profitable

What are the basic components of all computer spreadsheets?

· spreadsheet - a table in which you can enter and manipulate data. · cell - a table entry. ... · row - cells aligned horizontally. ... · column - cells aligned vertically. ... · range - the specification for a series of cells. ... · function - an operation applied to a range of cells, always beginning with an "=" sign

External data

1. Functions: CONCATENATE, FIND, LEFT, RIGHT, TODAY, TRIM, UPPER, LOWER, PROPER

What If Analysis Tools

1. Goal Seek 2. One and Two Variable Data Tables

Date functions

1. Is it possible to perform calculations with dates? Yes we can perform calucations with dates like we can calculated Days, Months Years between two dates. 2. Date functions (DateDiff, Year, Month, Now, Text etc)

Conditional logic

1. Logical functions: IF, AND, OR 2. Nested IF statements 3. What is a relational operator 4. What does conditional formatting accomplish? 5. Conditional summary functions: SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS

PivotTables and PivotCharts

1. Understand the use of PivotTables - they summarize data into categories using functions. 2. Ability to rearrange, hide, and display different category fields - hence the name "pivot" table. 3. Understand the layout areas: row labels, column labels, report filter, values 4. Using a slicer 5. Refreshing PivotTable - data that is changed is not updated in the PivotTable until it is refreshed. 6.How does a pivot table differ from an Excel table

What is a null field and how is it handled in a query?

A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

What is a comparison (aka relational) operator?

A comparison operator is used when we require results of a specific string or value. For example we want to retrieve results of students which has equal marks to 70. Then it will result in those students which has exactly 70 marks.

What is a foreign key?

A foreign key is a key which is stored in a child table and which refers to the primary key of parent table. It is also called referenced table. It acts as a cross reference between tables.

Why do we store data in a database?

A good database is crucial to any company or organization. This is because the database stores all the pertinent details about the company such as employee records, transactional records, salary details. Databases can store very large numbers of records efficiently and it is very quick and easy to find information. More than one person can access the same database at the same time

What is a logical operator?

A logical operator is used to perform calculation between two fields. These can be +, -, Multiply and divide.

1. What is a many-to-many relationship? How can a many-to-many relationship be implemented in a database?

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers. An example of implementing many to many relationship is that if we have student and course table then a student can opt for many courses and many courses and be assigned to many students

What is a parameter driven query?

A parameter driven query is used when the query asks for a parameter on runtime.

1. What is a primary key? What is a natural vs. artificial/surrogate key?

A primary key is a key in which each record in a table can be identified uniquely. For example in a Student table students ID number can be a Primary key which can uniquely identify each student. Natural key means when the primary key is made of natural data. Natural Key can be Social Security number, TaxID etc while Surrogate key is known to be a key which database generates on runtime before the record is inserted. Surrogate key is commonly a numeric number

What is produced by a query?

A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables

What are action queries and what are they used for?

Action queries are queries that can add, change, or delete multiple records at one time. The added benefit is that you can preview the query results in Access before you run it. An action query cannot be undone. You should consider making a backup of any tables that you will update by using an update query

What is an aggregate query?

An aggregate query performs the calculation on a column of data to return a single value. Sum of a column is an example of aggregate query.

What is an input mask?

An input mask is a string of characters that specifies the format of that particular field. We can use input masks in table fields, query fields, and controls on forms and reports.

What is the difference between using "and" and using "or" for the criteria in a query?

By using "and" criteria it means to return multiple results. For example if we want to retrieve result of a student which has 'passed' in exams and their marks are greater then 70 so it has two condition true which will result in those students which are passed and has marks greater than 70. The OR condition gives the results in which one statement is true. For example we want to view the data of students which are only 'passed' or their marks are greater than 50 so we put the 'passed' statement in criteria and '50' in OR condition.

How do you perform calculations in a query?

Calculated fields lets us perform calculations with the data in our tables. By selecting the Fields tab on the ribbon then we click on the more fields in Add & delete group then we hover over the calculated field and then selects the required data type. Once we do all these steps then selects two fields on which we are performing calculations.

What are the general data types available in Access?

Categorical, Numerical, Discrete, ContinuousHow do you create relationships between tables in Access? What is the difference between creating a relationship in the relationship window and using the lookup wizard? Creating relationship in access is easier compared to other databases as it gives graphical interface by clicking the database tools and then clicking relationships in which we drag the primary key field and drops it on the foreign key field in the child table. That way the relationship is created. While through lookup wizard it creates a field in that table and then selects the data from the parent table.

What are the relative benefits and drawbacks of using Microsoft's Access and Excel to create applications? Describe a situation where one product would be preferable to the other. Describe a situation where both products would work together to produce a business application.

Choosing the right program is critical if you want to access and update your information with maximum performance and accuracy. To find out which program is best suited for the tasks that you want to accomplish, it may help to compare the benefits that each program has to offer regarding data storage, data analysis, multi-user collaboration, and security. When to use Access In very general terms, Access is the best choice when you have to track and record data regularly, and then display, export, or print subsets of that data. Access forms provide a more convenient interface than an Excel worksheet for working with your data. You can use Access to automate frequently performed actions, and Access reports let you summarize data in printed or electronic form. Access provides more structure for your data; for example, you can control what types of data can be entered, what values can be entered, and you can specify how data in one table is related to data in other tables. This structure helps you ensure that only the correct types of data are entered. When to use Excel Require a flat or nonrelational view of your data instead of a relational database that uses multiple tables, and when your data is mostly numeric. Frequently run calculations and statistical comparisons on your data. Want to use PivotTable reports to view hierarchical data in a compact and flexible layout. Plan to create charts regularly and want to use the new charting formats that are available in Excel. Want to emphasize your data by using conditional formatting icons, data bars, and color scales. Want to perform sophisticated what-if analysis operations on your data, such as statistical, engineering, and regression analysis. Access and Excel together There may be times that you would want to take advantage of the benefits that both programs have to offer. For example, you created a worksheet in Excel in which you can calculate and analyze the data, but the worksheet has become too large and complex, and many other users need to have access to the data. At this point, you might want to import or link your worksheet into Access and use it as a database instead of working with it in Excel. Or, perhaps you have data in an Access database for which you want to create some detailed Excel PivotTable reports and professional looking Excel charts.

What is conditional formatting and why would you apply it? Give two examples of conditional formats, and explain a method for applying conditional formatting.

Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100. When the value of the cell meets the format condition, the format you select is applied to the cell. If the value of the cell does not meet the format condition, the cell's default formatting is used. (By "default formatting", I mean the formatting that you set up using the normal formatting tools, not necessarily the worksheet's default font and font size.)

What are criteria in a query, how is it defined?

Criteria in a query means to return specific results in a query. It can be defined in the Criteria row located in query design. For example if we want to return results of a specific city we just write the name of that city in criteria row and then it provides the results according to that specific criteria

Benefits of DBMS

Data Sharing allows the end users by creating an environment in which they have better access to more quality managed data. · Data Security: While the data access by more users it has more risk of data security breaches. DBMS provides a framework for better implementation of data privacy and security policies. Decision Making: The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy

What is data redundancy?

Data redundancy in a database means that some data fields are repeated in the database. This data repetition may occur either if a field is repeated in two or more tables or if the field is repeated within the table.

What are data types and why do you have to choose a data type for each attribute when creating a table with a database management system?

Data types are the most important property of database. We choose data types as it determines what kind of data a field can store. Each field in a table can store data consisting of a single data type.

1. What is the difference between using a database vs. a spreadsheet to store data?

Database can store large volumes of data as compared to Spreadsheets. It can store information more efficiently. Spreadsheet has record limitations as whereas database do not. Comparing spreadsheets to database Spreadsheet can require a huge amount of space(Harddrive) for the storage of data. While Databases can be very quick and easy to generate reports compare to spreadsheets also database can be fast as if we have large amount of data then it is difficult to read that data through spreadsheet while databases are made for these kind of operations.

What is a database constraint?

Database constraints are used to limit the type of data that inserts in a table which ensures the reliability and accuracy of the data in the table. Constraints can be made on column level or table level.

1. What is an ER (Entity-Relationship) diagram?

ER Diagram also know as Entity Relationship diagram is a type of structural design which is used in database design. An ER Diagram contains different symbols and connectors that visualize important information.

When selecting a chart, it's important to choose the right type of chart to represent your data. If choosing between an XY scatter chart and a column chart, what factors would you evaluate in choosing which chart to use? How would each chart differ in representing your data?

For presenting scientific data in graph form, the choice is almost always scatter plots vs. Column Chart. For scientific data, any other graph style is not useful in most cases. Use either scatter plots or Column charts for scientific data and avoid all other types. To decide whether to choose either a scatter plot or a Column chart for your data, look at the X variable data being plotted. The X variable data determines which graph type to choose.

1What are some issues that can arise when importing data from an outside source into a DBMS? What are some tools or ways to resolve these issues?

Format is not the same, Number of fields doesn't match, Size of data is more than DBMS, Security issues,

What is the IIF function, what are the components of the function and why is it used?

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation

What is the difference between enforcing referential integrity and just creating a relationship between tables?

Implementing Enforcing referential integrity means when you update a record in the parent table and its related to another table through a foreign key then it also updates that record in the child table which have the foreign key. It also prevents user to delete record from the parent table while the enforcing referential integrity has been implemented.

How is data stored in an Access database?

In Access all data is stored in tables. Tables are organized into vertical columns and horizontal rows. Rows and columns are referred to records and fields.

When connecting Access and Excel, which is usually the source and which is the destination? Is it preferable to import a table into Excel from Access or write a query in Access containing the necessary data?

Ms excel and Ms access can be connected to each other for the reasons Importing data from excel to access. The source will be the Excel and the destination will be the access database. It is not preferable to import a table from access to excel because access is database used and designed to store large set of data. While excel is limited to storing data. If we have large set of data in access database and we want to import a table into excel and that table contains large set of data then it becomes time consuming after importing the data into excel and opening the file and performing operations on that file.

What are the benefits and drawbacks for using named cells in a worksheet?

Named ranges can make formulas easier to understand (and debug), simplify the creation of complicated spreadsheets, and simplify your macros.

What is a query?

Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want

What is referential integrity?

Referential integrity means the accuracy and consistency of data within a relationship. In relationships, data is linked between two or more tables. Referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

What does it mean to sort data in a query?

Soring data in a query means to arrange the data in specific way so that the reporting data is more usable. An large to small sort will sort the data in descending order.

Why do tables have to be related in Access for queries to work correctly?

Tables have to be related in Access for queries to work correctly because it creates the relationship between multiple tables for access of the data that will reflect in our like if we define a field in our query but that field is not present in the table then it will give dump or it will not work. If the data is related in access queries then it will quickly fetch the data into the DBMS.

How do you concatenate fields (like a first name and a last name) in a query?

The & Operator lets us concatenate two fields in access. By opening the design mode of a query and entering the expressing in the field row as [first name]& " " &[last name].

What are the 4 objects in an Access database?

The 4 Man objects of an Access database are Tables, Queries, Forms and Reports.

Given a financial question, be able to list the arguments and which value would be entered for each argument.

The PMT function is categorized under financial Excel functions. The function helps calculate the total payment (principal and interest) required to settle a loan or an investment with a fixed interest rate over a specific time period. Formula =PMT(rate, nper, pv, [fv], [type]) The following arguments can be entered in the formula: Rate (required argument) - The interest rate of the loan. Nper (required argument) - Total number of payments for the loan taken. Pv (required argument) - The present value or total amount that a series of future payments is worth now. It is also termed as the principal of a loan. Fv (optional argument) - This is the future value or a cash balance we want to attain after the last payment is made. If Fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. Type (optional argument) - The type of day count basis to use. The possible values of the basis are:

What are the goals of database design?

The main goal of database design is to create a logical and physical design of the proposed database system. The logical model focuses on the data requirements and the data to be stored independent of physical consideration.

Input areas should be isolated from output areas.

The purpose of the input area is to make it easy for the user to change the input parameters that affect the outcome of the model. It also isolates the user from the computation and output areas of the model, reducing the chance of accidental changes being made to the latter two areas that might render the model incorrect.

What are the three general types of cell references in Excel? Give examples of when it would be appropriate to use each of the different types of cell references.

The three types of references are Relative references, absolutes references and mixed cell references. · Relative references: These cell references that adjust itself when the cell is copied are called relative cell references in Excel. Relative cell references are useful when you have to create a formula for a range of cells and the formula needs to refer to a relative cell reference. For example if we write a formula in a single cell and then drag that cell to the bottom then it automatically adjusts to the corresponding cells. · Unlike relative cell references, absolute cell references don't change when you copy the formula to other cells For example, A dollar symbol, when added in front of the row and column number, makes it absolute (i.e., stops the row and column number from changing when copied to other cells).