ISTM 210 TEST #3

Database

A set of interrelated, centrally coordinated files forms a database. ex: student file and advisory file and class file make up a database

What is the most common type of relationship?

One to many

As the Cloud evolves,

there will be much to anticipate regarding privacy and accessibility. Advances in existing technologies as well as the emergence of new ones will have to be made to ensure that the Cloud is safe and private.

pay-as-you-go model

The IaaS customer can use a pay-as-you-go model (per hour, per week, per month, etc.) that eliminates the cost of having to buy new computers.

Layout View

The Layout View of the report is a compromise between the Design View and Report View. The Layout view displays the report with the records from the source table or query. This way you can see how the finished report will look.

What is the point of the water metaphor?

The water metaphor illustrates the massive amounts of time, money, bandwidth and storage space required for ETL. It would be wise to start calculating the Return on Investment (ROI) to find out whether it's worth it.

Foreign Keys

USED to link tables together A foreign key is an attribute in one table that is a primary key in another table.

Decision Support Systems (DSS)

computer-based systems that support an organization's decision-making activities. For example, loan officers at a bank use DSS to verify the credit of a loan applicant. A simple spreadsheet is DSS that calculates possible revenues and expenses which can help someone decide whether a start-up business is viable or not. --- USER FRIENDLY

Sort field

field used to determine the order of records in the datasheet; can be ascending (A to Z) or descending (Z to A) order

The easiest way to add a new label to the report is to:

copy an existing label from the page header area and modify the text in the label.

A database schema

is a "map" of data tables and their relationships to one another -- all the boxes with lines connecting relationships

querying

is a tool that lets you ask your data questions that in turn lead to answers, and eventually decisions.

An important feature: data about various things of interest (entities) are

stored in separate tables; makes it easier to add new data to the system and redundancy is minimized and controlled. •You add a new student by adding a row to the student table. •You add a new course by adding a row to the course table. •Means you can add a student even if he hasn't signed up for any courses and you can add a class even if no students are yet enrolled in it. Space is also used more efficiently than in the other schemes. There should be no blank rows or attributes.

Third alternative to storing data (solution):

-The solution to the preceding problems is to use a set of tables in a relational database. -Each entity is stored in a separate table, and separate tables or foreign keys can be used to link the entities together.

How much of all data is unstructured?

80%

What do Database designers need to know?

A BUSINESS They begin by finding out what a business does and how they do it. For instance, a business may have "customers" that tells the designer that the database will need a "Customers" table.

Primary Key

A field (or group of fields) that uniquely identifies a given entity in a table; In some tables, two or more attributes may be joined to form the primary key.

mail merge

A process that inserts variable information into a standardized document to produce a personalized or customized document. Zach could modify his existing REO Fact Sheet template to mail merge with only one other Word document

New books can be added and deleted from the data table in the above example using other SQL statements such as

APPEND, DELETE and UPDATE queries

entity

An entity is anything about which the organization wishes to store data. At your college or university, one entity would be the student.

Database management systems (DBMS)

Databases are created using software systems known as database management systems (DBMS). In a DBMS, data is stored in computer files called tables, and tables are connected to other tables with related information; hence, a relational database.

How much does OpenOffice cost?

FREE

When do you make another field that isn't shown in the query results?

For example, we may wish to create the sorted query that we just made, but we want the fields to appear with city before province in the query result. This will create a problem for us. In order to sort by province first and then by city, we'll need the province field to appear before city in the query design grid. City first unsorted and on table Province second in ascending order and on table City third in ascending order and NOT shown

Create a query that shows the average list price for products in each category. Also show how many different products are in each category.

Group by Category Average List price Count Product ID

on-demand

If the organization needs more computing, IaaS can quickly adjust and supply more computing power because it is considered on-demand.

Return all customers whose firstName is "James" or whose lastName is "Hansen" (OR)

It is also possible to construct an OR query based on different fields from a database table. An example of this would be if we wanted to return all customers whose firstName is "James" or whose lastName is "Hansen" First name field: "James" first line criteria Last name field: "Hansen" second line criteria

Enterprise Resource Planning (ERP)

One of ERPs main function's is to centralize an organization's data so that it ends up being a wealth of data with value across the organization. can eat bandwidth and storage at an alarming rate and size

avoid data redundancy

The nature and structure of a well thought-out database management system helps a business avoid data redundancy. For instance, only a singular table holds personal information about a person. A person's unique information, like name and address information, resides in just one place in the entire DBMS. Avoiding data redundancy ensures accuracy system wide throughout a DBMS which can reduce reconciliation errors immensely.

Exporting data from access

The options for exporting data from Access can be found in the External Data menu tab. We can export data from Access to an Excel file, a text file, an XML file, a PDF or XPS file, an email, another Access database table, Word, and several other formats. May need to select the table in the navigation tab that you want to be exported (delimited file usually)

How to make a query aggregated

This is done by clicking on the Totals icon in the Show/Hide group of items in the Design menu tab. The Total: option appears in the query design grid. There is a drop-down list for each field that we use to select the totals option for each field

Enforce Referential Integrity

This means that a user will only be able to add an employeeID on the reseller table that already exists on the Employee table

When to use number signs (single criteria)

To let Access know that we are looking for a specific date, we need to include the number symbol before and after the date in the criteria field.

List all of our customers that live in Phoenix, Arizona. (AND)

To return these customers we will need to use two criteria: province is "AZ" and City is "Phoenix". City field: "phoenix" on first criteria line Province field: "AZ" on first criteria line This creates an AND statement

attributes are stored

in the columns of tables

Data Analysis

is basically applying statistics and logic techniques to define, illustrate, and evaluate data. Simply stated, Data Analysis attempts to make sense of an organization's collected data and turn it into useful information and validate its future decisions, like what product to sell or whom the organization should hire.

Data Visualization

is the graphic display of the results of data mining, analytics and BI in general, typically in real time. Many times, data and information is just too massive and confusing to rely on numbers, so products like PowerPoint and Dashboards have become invaluable tools.

Structured Query Language (SQL)

is the most widely used standard computer language for relational databases as it allows a programmer to manipulate and query data. One of the most common uses of SQL is to query a table(s). PRONOUNCED SEQUIL

Map Reduce

is the processing arm, or engine of Hadoop. It allows data to be queried and processed directly on the server where it lives, instead of moving the data across the network to be analyzed on the computer. In Map Reduce, only the query is transported through the network. MAP --- SHUFFLE --- REDUCE

aggregate query

is used when you wish to calculate summary statistics. Aggregate queries are used to group query results into categories based on the values for a field and then perform calculations for each category.

field mask

restricts data input (zip code be 5 digits)

Not Like "*word*"

results in a table without the word specified in any record in that field EX: not Like "*leather*" shows only man made shoes

To display the results without duplicates:

right click in an open area of the query design canvas next to the customer table and select "Properties..." from the menu. Change the "Unique Values" property from No to Yes. Setting Unique Values to Yes for this query will display only the 161 unique entries for province in the query results.

Text Analytics

sometimes called Text-mining, hunts through unstructured text data to look for useful patterns, like whether their customers on Facebook.com or Instagram.com are unsatisfied with the organization's products or service.

Calculated

used to create a field that is generated by a formula. Often Calculated fields will refer to values in other fields for a record.

Lookup Wizard

used to simplify data entry. A lookup-field will present the user a list of items to choose from when entering a new record. These values can even come from another table in the database.

Yes/No

used to store data that has only two possible values such as yes or no, true or false, and on or off.

Date/Time

used to store date or time information. The Date/Time data type can be formatted to display dates or times several different ways.

OLE Object

used to store files created by other programs such as Word or Excel documents, picture files, or sound files. Generally, it is better to store this type of data as an Attachment data type rather than an OLE Object data type.

Attachment

used to store files for a database record. Attachments can be virtually any type of file including pictures, documents, sounds, and videos.

Long Text

used to store larger amounts of text. The Long Text data type is limited to 65,536 characters and can contain any combination of letters, numbers, and symbols

Hyperlink

used to store links to websites or other folders/files on your computer.

Short Text

used to store most data that won't be treated like a number (used in calculations) such as text, telephone numbers, zip codes, etc. The Short Text data type is limited to 255 characters. These characters can be any combination of letters, number, and symbols.

Number

used to store positive and negative numbers. The Number data type may contain decimal places. The Number or Currency data type must be used when you expect to perform calculations on the data in a field

Query Wizard approach

walks you through the process of creating a query by asking you a series of questions about what you want the query to include. The wizard then creates the query based on your answers. The Query Wizard simplifies the process of creating a query, but it gives you less control over your query than the Query Design approach.

data integrity

which means that our data is accurate and consistent.

business transactions are always in

TABLES

What is the most important computer file in a database

tables

relational database

tables are connected to other tables with related information

Required

this property is used make sure that a value is entered for a field when a new record is added to a table.

Tableau (BI)

B.I. product that allows a company to visualize the data and what you want in real time

In order to switch to another view of the report,

we will need to close the print preview window by clicking on the Close Print Preview menu icon.

Which layout do we use in the port wizard?

Tabular

CH.16

!!!

Access (textbook)

!!!!!

CH. 17

!!!!!

database (class)

!!!!!!!!!!

Action queries

Queries that make changes to the database

slice and dice

Taking out parts of the data you need

one-to-many relationship

(most common type of relationship) A one-to-many relationship means that a single record from one table could be associated with more than one record from the other table, but a record from the second table can only be associated with one record from the first table. An example of the one-to-many relationship would be the relationship between orders and customers. A customer can place more than one order, but an order will only belong to one customers.

different types of clouds

*public cloud*- owned and operated by third party; deliver stuff via the internet *private cloud*- used exclusively by one company/organization, pay someone to have private network *hybrid*- combo of both private/public. public can be for emails and private can be for high ball private finances into a company

Second alternative for storing data

-Another possible approach would be to store each student in one row of the table and create multiple columns to accommodate each class that he is taking.

Importing records from external sources

-Click Browse to locate the Excel file with the data that will be imported -Access might prompt us to select the correct worksheet -Notice that you can click on each column to define how each column will be imported; can change name and data type -have Access add a primary key to the table for us -name the new database table ***After, add input mask or lookup field features in design view

Normalized Database

-Data is consistent -Redundancy is minimized and controlled -Attributes appear multiple times only when they function as foreign keys -The referential integrity rule ensures there will be no update anomaly problem with foreign keys.

The following are forms of business analytics:

-Descriptive, predictive, and decision

Business database scenario ADVANTAGES

-His new database was on a network drive now so he didn't have to worry about backups, that he rarely did anyway -He no longer had to manage 500 individual Word document files. He simply had one place where all of the REO information was held. -He was able to input and update information on his new REO Fact Sheet form in his data table. -Zach's database easily found and eliminated past input errors from his Microsoft Word days and kept him from making errors in the future. -Zach could also print his form and distribute it by simply querying complete REOs. If his boss requested changes to the REO Fact Sheet's configuration, Zach would make them in minutes.

This approach is also fraught with problems:

-How many classes should you allow in building the table? -The above table is quite simplified. In reality, you might need to allow for 20 or more classes (assuming a student could take many 1-hour classes). Also, more information than just the course number would be stored for each class. There would be a great deal of wasted space for all the students taking fewer than the maximum possible number of classes. -Also, if you wanted a list of every student taking MGMT-3021, notice that you would have to search multiple attributes.

delete anomaly

-If Ned withdraws from all his classes and you eliminate all three of his rows from the table, then you will no longer have a record of Ned. If Ned is planning to take classes next semester, then you probably didn't really want to delete all records of him. -This problem is referred to as a delete anomaly.

When taking inventory:

-Maybe some data resides in your company's Customer Relationship Management (CRM) system that holds information about sales, marketing, customer service records, and much more. -You also know that data resides in the bank's *Marketing Automation Services system* that has endless customer facts and figures. -What about the bank's involvement in Social Media Platforms that can uncover what your customers are thinking? One more thing: Remember that most of this data is unstructured, 80% of it.

View menu

-Object view: displays the finished object -Design view: is used to edit the object -Layout view: is like the design view, but it provides fewer options for manipulating the object (easier to use)

Alternative for storing data

-One possible alternate approach would be to store all data in one uniform table. -For example, instead of separate tables for students and classes, we could store all data in one table and have a separate line for each student x class combination.

update anomaly

-Suppose Alice Simpson changes her phone number. You need to make the change in three places. If you fail to change it in all three places or change it incorrectly in one place, then the records for Alice will be inconsistent. -This problem is referred to as an update anomaly.

Definition of four types of objects in access

-Tables: are used to store the data (entities) in Access. A table consists of columns, which store data fields, and rows, which store database records. -Queries: are used to find the specific data you need from the database. A query can be constructed to locate related data across more than one table. -Forms: present the data in a table in a separate window one record at a time. They are used to help with entering new data into a table -Reports: allow you to organize and present the data in your database. Reports are used to present the data in a meaningful way to aid in managerial decision-making.

Some concept of data analysis:

-data mining -topic analytics -text analytics -business analytics forms of business analytics: -descriptive, predictive, and decision analytic

Access menu ribbon

-file -create -External Data menu tab: contains the tools used to import data into Access -The Database Tools menu tab: contains a set of advanced utilities for working with your database

There are two parts to a calculated field

1. Provide Access a name for the new calculated field 2. Then we will define the calculation to be performed In this case we want to calculate the extended price for each line item by multiplying the quantity by the price for each record in the query results.

principles for naming fields

1. names of the fields in your tables should be meaningful and descriptive 2. Access will allow you to use spaces in your field names, though this is not recommended (not universal) 3. use only letters and numbers (no symbols or special characters)

We normalize databases to:

1. we ensure that each table contains *data about very specific things*. For example, we wouldn't want a database table to store information about our customers and our suppliers (need two separate ones) 2. *limit duplicated or redundant information* as much as possible. For example, we wouldn't want to store customer phone numbers in more than one database table. Vital because when data changes it must be updated in multiple places to avoid inconsistencies.

How to create a parameter query

A parameter query displays a window to the user asking the user for input before it can execute the query. This input is generally a value to display for a particular field. To create a parameter query, you put the message you want to display to the user asking for input in brackets as the criterion for the field for which you want them to provide input. EX: [What Province do you want to display?], when this window is displayed you can enter AZ which will show all Arizona records Same question window every time the query is executed

file

A set of all related records forms a file (e.g., the student file).

SQL vs manual

A very simple " UPDATE" query similar to the " SELECT" one above could be written to do the entire job in seconds. It would actually take longer to write the SQL update query (a few minutes) than it would to process the job. The update query would take a matter of seconds to do what would take days to do manually.

Business databases are

ALWAYS DISTRIBUTED on a computer network for multiple users.

Difference between aggregated and calculated queries

Aggregate queries perform summary calculations across all of the records for a particular field, while calculated queries create queries that perform a calculation for each record in a query result.

record

All the fields containing data about one entity (e.g., one student) form a record.

From a pure cost analysis standpoint

An organization can see which costs more: employing PaaS, or keeping its computing services in-house.

NOT

As the name implies, the keyword NOT will return all records for a query that don't match the defined criteria.

AutoNumber

AutoNumber is a Number data type. As the name implies, Access will automatically insert that value for the AutoNumber field when a new record is added to a table. AutoNumber can be set to add 1 to the last number used for the field or it can assign a randomly generated number. In either case, AutoNumber will insert a unique value for each record added.

multi-table query

Could be used display all of the orders placed by each customer. This query requires information from the Customer and SaleOrder tables.

What's an example of creating another field at the end that isn't shown in the query results

Create a query that shows the productName, listPrice, and category fields (in that order). Sort the query first by category in ascending order then by productName in ascending order. Name the query "Price List" (without the quotes). ***add productName at the end in ascending order and hide it from the results

What do cross tab queries provide?

Crosstab queries provide a nice way to view a two-dimensional presentation of our data.

Dashboards

Dashboards are easy-to-use graphical interfaces that characterize specific data analysis through visualization. Not everyone wants to deal with BI programming using Structured Query Language (SQL) or other programming tools. Dashboards make it a lot easier to make sense of the data and see the resulting information.

Syntax for the new calculated field

Extended Price: [quantity]*[price] The name of the new calculated field is on the left side of the ":" and the calculation is defined to the right of the ":". When we use another field as part of the calculation, we put brackets "[ ]" around that field.

Make sure data is valid:

GIGO Garbage in Garbage out if you don't put good data in the system, it produces bad data output

Hadoop

Hadoop is a toy elephant owned by Doug Cutting's young son. In 2005, Cutting and Mike Cafarella created software that was originally developed to support distribution for a search engine. They naturally named it Hadoop. Hadoop evolved as an infrastructure for storing and processing large sets of data across multiple servers. It is open-source software framework written in Java for distributed storage and distributed processing of very large data sets on computer clusters built from commodity hardware.

Disadvantages of Hadoop

Hadoop is not the best for on the fly, or ad hoc queries, and is very difficult to implement and run. Typically, Hadoop needs a highly qualified data scientist(s) to run it. Right now, Hadoop is best for large companies like Facebook.com, eBay.com, and American Express that create Terabytes and Petabytes of data every day.

Advantages of Hadoop

Hadoop uses a cluster system that allows files to be stored on multiple servers. Unlike storing oceans of water (centralized data), Hadoop attempts to identify lakes and rivers (data files on other multiple servers). Hadoop is flexible enough that it allows for one query to be issued that searches through multiple servers.

Cardinaltiy

How many? one, zero, or many

Sometime it's hard to imagine a program like Microsoft's PowerPoint is all that valuable

However, since PowerPoint is one of the best presentation tools in the market, it has found a place in Business Intelligence, specifically in Data Visualization.

It is important to note that ETL must happen:

IN ORDER first extracting the data, then transforming data so it fits into your data warehouse or datamart, and then loading the data into the data warehouse or datamart.

One last way to look at it:

If Extract, Transform, and Load (ETL) brings the mountain to Mohammed, then Hadoop brings Mohammed to the mountain.

disadvantages of IaaS systems

If an organization depends on IaaS and the Internet goes down, its employees cannot get work done. If the Infrastructure as a Service goes down, the organization using it could potentially suffer because it is essentially without computing power, and that could result in a reduction of output.

Notice that there isn't an option to select which of the fields to sort first.

If there is more than one field set to sort in a query, Access assumes that it should sort the fields in the order that they appear in the design grid. So whichever is most left in the bottom of design view

ETL example to water

Imagine the vast amount of time and money it costs to build the water pipes and infrastructure to lakes and rivers and take it back to your holding tanks (Extract). The wider the water pipes, the faster water fills your tanks, but a few words of caution; wider water pipes are way more expensive. You purify and prepare it to be added to your ocean (Transform), now pour the purified water into your ocean (Load). Check that; pour your water into the many oceans (Data Warehouses) you administer. Then analyze the water in the oceans.

Business Intelligence: How did we get here?

In a way, we've always had Business Intelligence. Businesses throughout history have tried to recognize trends to best serve their customers and in turn, to then try and become more profitable. Essentially, organizations have always tried to predict the future. (1950s started using and processing data for decisions)

Selecting Records Based on Multiple Criteria

In this case, we use the keywords AND/ OR between the two criteria. Records must meet both criteria to be included in the query results or must meet one of them depending. EX: <40 And >20 .... <20 Or >100 NOTE: Any criteria that appear on different lines of the query design grid are evaluated as an OR. The <20 criterion and the >100 criterion are on different rows of the design grid. This is equivalent to using a single criterion, >20 OR < 100.

Sum of quantity sold for each product

In this query, we want to group the query results by the productName field. We also want to calculate the Sum for the quantity field. The result of this query will be a list of all of the products sold by the company (each listed only once). The results will also contain a field that calculates the sum of quantity sold on all orders for each product.

Create a query that displays all columns for customers from a specified state. The query should prompt the user to enter a state abbreviation when it is opened. The promp should read "Enter State Abbreviation". Name the query "Customer List (choose State)" (without the quotes).

Include all columns for the customer table and then add state again at the end. Hide this state column and including [enter state abbreviation] in the criteria.

Create a query that lists the FirstName, LastName, and Phone for all customers who have purchased shoes (products) of size larger than 12. Name the query "Bigfoot" (without the quotes).

Include first name, last night, and phone from the customer table. Include size from the sale item table but hide it and include the criteria ">12"

attributes/ fields

Information about the attributes of an entity (e.g., the student's ID number and birth date) are stored in fields.

One way to decide what kind of data you require is to think about

JEOPARDY Jeopardy doesn't ask its contestants to provide answers, it asks for the questions. What questions do you need to answer? Coming up with questions goes a long way towards the answers you need as well as toward the creation of a baseline for what data you need to collect.

How do you determine the relationship?

MAX : MAX

Small business example: is it sustainable?

Many would argue that this model is not sustainable because people need to be managed at a more personal level to have a more coordinated business effort as opposed to twenty-five independent people scattered in separate locations. Large businesses that lease office space may not want to leverage the Cloud because they essentially pay for a workspace for their employee(s) five days a week. If they let employees work from home two of those days, it doesn't mean they enjoy cost savings on office space, as they still have to pay for all five days.

What is map reduce like?

Map Reduce is like little computer minions that search out and query data where it resides, and process the query instead of dragging it back to a large centralized server, not unlike what Google does when it sends out Web bots to find new information for its search engine.

Datamart

Maybe you're a smaller organization, so instead of using a data warehouse, you could opt for a Datamart that is a smaller, more focused data warehouse. Datamarts limit the complexity of databases (just one section of business i.e. marketing), so you can't "answer" as much as with a Data Warehouse, but they are cheaper to implement than a full warehouse.

ID numbers are

NOT arbitrary! cats being in the 1000s and dogs in the 2000s is not on accident

Chief Analytic Officer (CAO)

New position developing

Load

Once data is transformed and normalized, it's ready to be finally transferred into the data warehouse or datamart. Loading sometimes happens weekly, daily, or even hourly. The more often this is done, the more up-to-date analytic reports are possible, and the more timely they can be.

Extract

Once you've determined where your data resides, you can start extracting it, often from Customer Relationship Management (CRM) or Enterprise Resource Planning (ERP) software. The extraction step sometimes grabs unstructured data like text notes to semi-structured or structured data by tagging it with metadata. For instance; tagging with metadata could mean extracting country names from an unstructured sales database and loading them into a column form and labeling it "Country Names."

Transform (normalize)

Once you've extracted data, it needs to become normalized. Data is no good to you unless it's organized. ****Normalizing data* means that your data is typically organized into the fields and records of a relational database. Normalizing provides the standard data format required to analyze data.

Cloud Drive disadvtanges

Privacy groups have raised concerns about the Cloud because your Cloud Service Provider (CSP) can access your data and information stored on their Cloud. CSP's, like Amazon, require their Cloud customers to agree with their " Terms of Service" (TOS) before they are willing to let you port your data and information to their Cloud.

What is Oracles's CRM called?

SIEBEL; collection of data from variety of sources used to support decision making to support intelligence

rackspace

San Antonio provider that hires Aggies

When to use quotes (single criteria)

Since we are using one of the text Data Types for the province field we put quotes around our criteria. EX: "AZ"

Including multiple tables in a query

Sometimes you will need to include several tables in a query in order to return all of the information you want. For example, we may want to view which customers purchased which products. There isn't a direct relationship between the Customer and Product tables.

what format do we wants reports

Stepped layout

Storage as a Service (StaaS)

Storing, or renting space from a CSP is called "Storage as a Service" (StaaS). Because of economies of scale, CSPs like DropBox can provide storage much more cost effectively than a single business. A business can certainly buy storage devices for excess storage needs, but Cloud storage is infinitely less expensive. EX: amazon elastic computer cloud, dropbox, google drive, Onedrive, rack space, and fiber town ($2 or less per gig per year)

Four types of objects in access

Tables, queries, forms, and reports

The navigation pane

The Access navigation pane displays the objects in a database file. You can set the navigation pane to display only certain types of objects to simply what you see in the navigation pane.

Design View

The Design View allows you to change report. The Design View provides a lot of flexibility for changing the appearance of the report, but it does not display the records from the source table or query so it can be difficult to visualize how the report will look when it is printed. Design View is divided into sections. There is a header and footer section for the Report. This is where we will place any information that will be printed at the top (header) and bottom (footer) of the report.

Print Preview

The Print Preview presents the report as it will appear when printed on paper or in a PDF file. As with the Report View, you are not able to modify the report in the Print Preview screen.

Report View

The Report View allows you to see the finished report, but you cannot modify the report in the Report View.

Like "*word*"

The keyword LIKE tells Access that we want to search within a field to find a specific value. The "*" character is a wildcard character. The "*" is used to substitute for any number of characters. The search term *stove* is used to tell Access to find any records that have any combination of characters before and after the word stove ****To find records that end with the word stove in the productName field, we would use the criteria Like "**stove".

select query

The most basic type of Access query that creates subsets of data, displayed in Datasheet view, that can be used to answer specific questions or to supply data to other database objects. A select query is used to view the records selected by the query

Example of creating a field

The phone number field will be named phoneNumber. It will also be the Short Text data type. We will also create an input mask for this field to ensure that phone numbers are entered correctly. To create the Input Mask, select the Input Mask field property area and click on the button with the ellipsis that appears at the end of the property area. Access will format that number with parentheses around the area code and a dash between the sixth and seventh digits. Access will also ensure that 10 digits are added for the number.

There are two parts to any field in a report:

There is the label for the field at the top of the column that displays the field name and the report detail for that field that contains all of the records for that field. Modify both at the same time (shift)

NULL

To create a create that returns those records with empty values, we use the phrase "IS NULL" as the criteria for that field. EX: Enter this into the criteria of a field to find customers who have a blank in this field (all customers without a phone number)

To display only certain criteria:

To display only those products whose current price is $19.95, we add 19.95 in the Criteria box of the design grid for the CurrentPrice field. When executed, the query will display only the two records from the table that are currently priced at $19.95.

WEBSITE

US military drops floppy disks and advances technology. One advantage they are leaving behind is that floppy disks cannot get hacked

Cross tab queries in access

Used to create a multi-dimensional view of our data. We use a crosstab query to summarize the data in a query on two dimensions at the same time. For example, we may want to create a query to list the amount of each product type that has been shipped by each of the shipping companies that QuickStove uses to deliver orders to their customers.

Four V's of Big Data

Volume, Velocity, Variety, Veracity

Calculated Field Query

We can also create queries that perform a calculation for each record in a query result. This type of query has a calculated field. We can construct a calculated field to perform arithmetic or apply a prebuilt formula on the values in our database tables.

Selecting Records that Fit within a Range of Values

We can also use criteria to select records based on a range of values. For example, we may want to know which of the products on the Product table are priced more than $20. EX: >20 Can also use: <, >, <=, >=, or =

Cross tab query design

We need to add the following fields to the query design: productName (from the Product table), quantity (from the OrderDetail table), and carrier (from the Shipment table). The productName field will be set as the Row Heading in the Crosstab settings and Group By in the Totals settings. Carrier will be set to Column Heading and Group By in the Crosstab and Totals settings, respectively. Finally, quantity will be set as Value for the Crosstab settings and Sum for the Totals settings.

To create a report from the School table

We will click on the School table in the Navigation pane and select Report Wizard in the Reports area of the Create menu tab.

Adding totals to reports

We will place the customer totals in a footer section for the customer group and the order totals in a footer section for the order group. We will first add the customer totals. We click on the Group & Sort menu icon in the Grouping & Totals section of the Design menu tab. last name -- more -- with no totals -- Total: mount and Type: sum

Variety

You may have identified what data you wish to collect, but is it Structured, Semi-structured, or Unstructured? Very likely it is a combination of all three, which could potentially throw a wrench into your data collection gears.

software disadvantage of the cloud (solution below)

You may pull a Microsoft Excel spreadsheet from the Cloud to your microcomputer's RAM, but you won't be able to work with it unless Microsoft Excel is loaded on that microcomputer. Although your Cloud can be accessed from virtually any Internet enabled computer, the application software needed to run a specific file, like a spreadsheet or word processing document, needs to be present.

Big data

a broad term for datasets so large or complex that traditional data processing applications are inadequate. Data would also include smartphone metadata, Internet usage records, social media activity, computer usage records, and countless other data sources, so it could be sifted for patterns and trends.

parameter query

allows you to specify a criterion for the query result at the time that the query is executed. For example, suppose you want to create a query that displays a list of customers for a specific province, but you want the query to be flexible enough that you can display a different province each time you run it depending on which province you wish to see.

ETL

an acronym for Extract, Transform, and Load, tools that are used to standardize data across systems, allowing it to be queried.

Database queries

are used to automate the process of extracting the information that we need.

Data Warehouses

are used to consolidate disparate data in a central location, and lots of it! It's not unusual for a data warehouse to hold yottabytes of data! EX: oracle, IBM, SAS, Teradata

Business Analytics

attempts to make connections between data so organization's can try to predict future trends that may give them a competitive advantage. Business analytics can also uncover computer system inadequacies within an organization. (BANA old name - BA to many - info department now)

Predictive Analytics

attempts to reveal future patterns in a marketplace, Essentially trying to predict the future by looking for data correlations between one thing, and any other things that pertain to it. number of staff next year based on sales trends ---WHAT COULD HAPPEN

Cloud drive advantages

automatic back up and access from any Internet enabled computer

Decision Analytics

builds on Predictive Analysis to make decisions about future industries and marketplaces. Decision Analytics looks at an organization's internal data and then analyzes external conditions like supply abundance and then endorse a best course of action. Recommends course of action based off of likely outcomes of each of them - are the side effects worse than the illness --- WHAT SHOULD WE DO

What are you looking for in a database?

criteria

Database designers construct databases by using

entity relationship (ER) diagrams

Who benefits from BIG data?

everyone if you can harness it

It can be argued that the first database management systems were

filing cabinets A typical filing cabinet might have three drawers marked with alphabetical characters. For instance, the top drawer might be labeled "A through H". Once a drawer is opened, alphabetical tabs separate folders.

Data is

fluid because it changes all the time

insert anomaly

improper database organization that results in the inability to add records to a database -What happens if you have a new student to add, but he hasn't signed up for any courses yet? -Or what if there is a new class to add, but there are no students enrolled in it yet? In either case, the record will be partially blank. -This problem is referred to as an insert anomaly.

What happens if a validation rule is broken?

if a value is entered for the ReviewStars field that violates the validation rule, a message window is displayed that contains the validation text we set for the rule. Access will not allow a user to enter a value that violates the validation rule.

Records are stored

in rows of tables

Descriptive Analytics

is the baseline that other types of analytics are built. Descriptive Analytics define past data you already have that can be grouped into significant pieces like a department's sales results, and also start to reveal trends reactive strategies --WHAT HAS HAPPENED

What does many to many require?

linking tables; primary key combination of 2 tables

Forms are used to

make entering new or updating existing records easier for the user. Also, forms reduce the likelihood that existing records will be changed accidentally when the user adds or updates records.

When would the minimum be zero?

optional or timing minimum is not seen on any diagram

It would be foolish to grab and store every

piece of data you can get your digital hands on Not unlike the auto parts store, you want just the right amount of data, not too much, and not too little.

Access reports are used to

present the data in a table or the results of a query in professional ways. Because the data in your database changes over time, reports will also update every time you run them so that the information displayed in the report is always up to date.

many-to-many relationship

represent the case where a record from one table may be associated with more than one record from the other table and a record from the second table may also be associated with more than one record from the first table. An example of a many-to-many relationship would be the relationship between orders and products. A single order can have more than one product on it and a single product can be sold on many orders.

one-to-one relationship

represents the situation where a record from one table can only be associated with one record from the other table. An example of a one-to-one relationship would be if you had an employee table and you wanted to also store information about employee spouses on a separate table. The relationship would be one-to-one because there could only possibly be one spouse for each employee. EX: manager to laptop or employee to company car

Design mode is also called

schema

Data Mining

sometimes called Data Discovery, is the examination of huge sets of data to find patterns and connections, and identify outliers. EX: Netflix movie recommendations

Currency

the Currency data type is a Number data type that places a currency symbol ($) in front of the number.

"filtering"

the ability to add a filter to a data table that would only display specific information. For instance, a filter could easily be added to the "State" field by inserting "AZ" and, in turn, the data table would only display information for Arizona.

Deletion of a class for a student would cause

the elimination of one record in the student x class table. -The student still exists in the student table. -The class still exists in the class table. -Avoids the delete anomaly.

Database Administrator (DBA)

the person responsible for coordinating, controlling, and managing the database. They set restrictions and set permission

Field Size

this determines how large the field will be. For numbers this effects how large the numbers stored in the field can be and the precision (number of digits to the right of the decimal place that will be stored). The field size for a Text field determines how many characters will be stored in that field for each record.

Default Value

this is used to have Access automatically set a starting value for a field when a new record is added to the table

Input Mask

this is used to set rules for how the data will be entered for the field. For example, you can use an input mask to make sure that users enter 10 digits for a telephone number.

Topic Analytics

tries to catalog phrases of an organization's customer feedback into relevant topics. For example, if a customer said, "the barista was friendly", that would be categorized under the topic "Employee Friendliness."

Customer Relationship Management (CRM)

used to TRACK and organize communication with customers

Saved query files

used to find specific populations (subsets) within databases

Query Design approach

uses what is called a Query by Example (QBE) environment. The QBE environment provides a graphical way to "drag and drop" the elements of your query until it is exactly how you want it.

Validation Rule/Validation Text

validation rules and validation text work together. They are used to have Access check values when they are entered into a table to make sure they fit a specific rule. For example, you could use a validation rule to make sure that all of the values entered into a field are greater than 10.

Databases store all kinds of sensitive information, like

social security numbers, employee pay rates, and credit scores. DBMS systems have the ability to store information securely by giving certain end-users access to data and other end-users limited access to the same data. For instance, a human resource director may have complete access to all of a human resources database system; whereas, her employees may not be able to see all of the data in the same system, like a fellow employee's pay rate.

Cloud computing or the "cloud"

the Cloud is simply using computer resources like a hard drive or software that exists on another computer connected by a network, typically the Internet.

"data validation" rules

these rules help to ensure the data's integrity. Data integrity techniques attempt to avoid data input errors like typing mistakes.

Database software is a

well thought-out collection of computer files, the most important of which are called tables. These tables that consist of records (rows) of data separated by fields (columns) that can be queried (questioned) to produce subsets of information.

Why cloud over hard drive

A better way to back-up your internal hard drive is to an off-site hard drive that you connect to through the Internet. If the original internal hard drive contents are corrupted or lost, like in a fire, the internal hard drive's contents are still intact at a different location. That's the Cloud, or at least a small part of it.

First to do use the cloud

Companies like *GoDaddy.com* have offered off-site file storage for years, but they called them *iDrives* (Short for " Internet Drive"). In the past, the problem with iDrives was that the Internet was just too slow to store and back-up data and information like spreadsheets and word documents. That's all changed with faster Internet speeds, and iDrives morphed into the Cloud, or at least part of it.

Cloud Service Providers (CSP)

Companies that provide cloud backup automatically EX: Google, Microsoft, or DropBox notes: Amazon, iCloud, Egnyte, OpenDrive

How to store and manage data

First things first: Take an inventory of the data your organization makes, and figure out who or what makes it. Second: find a place to keep the data you want to retrieve

Velocity

How fast can you collect data, and more importantly, how quickly can you analyze it?

Internal hard drive

advantages: speed, storage, and file management disadvantages: have to be backed up on a regular basis. To prudently back up a hard drive, it makes sense to back-up to an external hard drive. The contents of a back up must be taken off site to prudently complete the back up.

Volume

Refers to the amount of data collected by an organization. How much data does your business need, and further, where do you keep it once you've collected it?

A table might have the names of 10,000 book titles and their prices. If a person wanted to know which books in the data table cost more than $100, he could set up a SQL statement like the following:

SELECT Title FROM Book WHERE Price > 100 ORDER BY Title The SQL above "Selects" the "Title" field (column) "From" the table named "Book" "Where" the book's "Price" is greater than $100. Once the query is run, the results of the query will be in "Order" of the "Title" field of the book(s).

Software as a Service (SaaS)

SaaS, sometimes called " On-Demand" software is when the CSP installs and operates application software in the Cloud that the Cloud user can use. For instance, if the application software needed to use a spreadsheet is already installed in your Cloud, you will be able to use it on any Internet enabled computer, even if the particular application software is not loaded locally on that computer. This eliminates the need to install and run application software on the Cloud user's own computer, that could potentially save businesses enormous amounts of money.

Printing vs online sharing

She reminded Zach that his database was now on a network drive. She told Zach that if he chose too, other people throughout the bank on the same network could look at his REO Fact Sheet form. She described a few modifications to the form so no one but Zach could edit the underlying database. Instead of ever printing on paper, REO Fact Sheets and their associated information would be distributed completely electronically through the bank's network.

form configuration

She wanted to know why he distributed information only in the REO Fact Sheet form configuration. She suggested he could configure his data in an almost infinite variety of ways like reports and new forms that would be distributed on the network and eliminate the need for other bank departments to compile and make their own reports.

Ultimate effect of Cloud drives for a small business:

Software as a Service (SaaS) could replace the need for twenty-five licenses for Microsoft Office 2012 by employing "Cloud Software" like Google Apps, Apache OpenOffice, QuickBooks Online, SalesForce.com, and Microsoft Office 365. Instead of a capital outlay of $8,750 for Microsoft Office, the startup company simply pays a much smaller monthly or yearly fee to "rent" SaaS software like QuickBooks. By employing the Cloud and SaaS, the startup business reduced its costs dramatically and, by implementing a virtual office space, extended its global reach. Without the Cloud, the startup's initial costs were a whopping $219,250. With the Cloud they were $29,500, an initial startup savings of $189,750.

Tables

Tables are where a database holds data. Tables consist of records (rows) separated by fields (columns) that can be queried (questioned) to produce subsets of information. The records retrieved by these queries become information that can be used to make business decisions

having a name in one single table

That means that if a DBMS consists of 23 forms and 124 reports, that include a person's name, the name will always display the same way. If the name is wrong, it only has to be corrected in one singular place; on the table that holds the "name" data and all forms and reports will display the name correctly.

Fields (attributes)

The database designer finds out what is unique to those customers, like their first names, last names, addresses, and then these are included as fields (called attributes) in the "Customers" table on the ER design. Once the structure of the "Customers" table is complete, the database designer may find out that the business's customers place orders with them and sometimes more than one order. The database designer will construct an "Orders" table with fields like "Order Date", "Shipped Date", etc.

What else is a database designer expected to know?

The database designer is also required to know what a business' expectations are of its DBMS. Like, what sort of "questions" or queries will the business require? Often, more experienced designers will consult with a business to help them realize the vast potential of a well-organized DBMS.

"One to Many" relationship

When the Orders table is complete, the database designer designates its relationship to the Customers table by understanding that "one" customer can place "one or many" orders establishing what is called a "One to Many" relationship between these two tables. Once the conceptual structure of the ERM is complete, the designer can start making the tables in the database.

From a business standpoint, a third party IaaS provider host like

Windows Azure or Google Compute Engine supplies a computer software applications like Adobe Photoshop, servers, and storage in their Infrastructure as a Service, all connected via the Internet.

Entity relationship modeling (ERM)

is a database-modeling method used to construct a theoretical and conceptual representation of data to produce a schema Simply stated, an ERM is a picture of a database's tables and how they relate to each other.

Unstructured Data

is disorganized data that cannot be easily read or processed by a computer because it is not stored in rows and columns like traditional data tables. Imagine collecting massive amounts of Facebook messages and Instagram posts to determine future fashion trends. Organizations attempt and do collect videos, documents and all manner of fragmented data to eventually make sense of it. A whopping 80% of all data is unstructured!

Semi-structured Data

lands somewhere in-between Structured and Unstructured data and can possibly be converted into structured data, but not without a lot of work.

What filter did Zach need?

Zach knew exactly where he needed a filter from the moment he knew how the technique worked. From the very beginning of his database system, Zach included a filter in the "Complete or Incomplete" field to only display REO's that had complete information. This meant that any REO without complete information was hidden from all users, and all reports.

forms

overlay data tables and queries for more specific views of data Often forms are very simple and display only a single record from the table at a time. When creating a form, we must decide which fields from the table should appear on the form. We also decide how these fields should be arranged.

Reports

reports display database information on screen or on paper

Other files that exist within a database can include

reports, saved query files, and forms

15.5 example

reread and know the advantages and disadvantages or word, spreadsheets, and database

Structured Data

resides in fixed formats. This data is typically well labeled and often with traditional fields and records of common data tables. Structured data doesn't necessarily have to be "table-like," but needs to at least have recognizable patterns that allow it to be more easily queried, searched, and in a standard format.

At first glance, a DBMS seems

restrictive especially relative to other business application software. Most business application software, like word processors or spreadsheets present the end user with a blank file, which is ready for use. Database software is different, requiring the end user to create their own files starting with tables and then relating them to each other

records are ___ and fields are ___

rows; columns

Amazon terms of agreement

"Our Right to Access Your Files. You give us the right to access, retain, use and disclose your account information and Your Files: to provide you with technical support and address technical issues" This is giving them the right to sift through your data; many say that as long as none of your data and information is illegal, like illegal MP3 music files, then there is nothing to worry about.

At the dawn of the Internet, many people envisioned a

"YouTube-like" website, but Internet speeds simply weren't fast enough to be a feasible business option. Early iDrives eventually morphed into the Cloud as speed and space became more inexpensive, but the Cloud had to wait for its time before it became reality.

•Business intelligence (BI) is often described as

"the set of techniques and tools for the transformation of raw data into meaningful and useful information for business analysis purposes".

Apache's Open Office EQUIVALENTS

-*"Writer"* is similar to Microsoft Word, which can produce Portable Document Files (PDFs) and web pages. -*"Calc"* is the OpenOffice spreadsheet equivalent to Microsoft Excel -*"Impress"* is the presentation software similar to Microsoft PowerPoint -*"Base"* is the database application like Microsoft Access -*"Draw"* is the graphics editor similar to Adobe Photoshop.

When was BI born?

As data became more easily collected, the term Business Intelligence was born. The vast amounts of data that organizations wanted to collect wouldn't just be in readily available, neat and tidy database tables either.

Business Intelligence

BI refers to an assortment of software applications used to analyze an organization's raw data. BI can be described as computer applications that change data into significant, meaningful information that helps organizations make better decisions. Keep in mind that data is raw, unorganized facts, and information is essentially processed data that means something. BI fixes not having enough inventory, too much, or using past guess work and failing

As long as you're going to back up data on to a "Cloud Drive," a hard drive in the Cloud, why not bypass your internal hard drive altogether?

If your resume was permanently stored on a Cloud Drive, you would still access it through your own computer's file manager, but it would be somewhere else (in the Cloud). If your resume file (MyResume.docx) was on a Cloud Drive, it wouldn't require back up, as the Cloud Service Providers (CSP) like Google, Microsoft, or DropBox would provide back up automatically.

return on investment (ROI)

Imagine applying Business Intelligence to every product in your store. It looks like BI might be a great return on investment (ROI) after all. An auto parts store with "just right" inventory would be far more profitable and flexible.

What was the precursor of the Cloud?

Internet Drives

Why didn't iDrives catch on?

Internet was too slow

Veracity

Is the data your organization collected any good? Just because some other business amasses data you may want, doesn't necessarily make it trustable or valuable. Lots of data sources have data that is not "clean," meaning it may be too fragmented to be valuable or usable, or that it was simply collected poorly in the first place.

Apache's OpenOffice name

It used to be called OpenOffice.org and was administered by Sun Microsystems, then ownership went to Oracle, and it is now in the hands of Apache where it is known by its current name, "OpenOffice."

If the Internet is down, is the Cloud available?

No

Predictive way

Now you can start to use your data in a predictive way. For instance, imagine software that will cross-reference your subset's data with other (external) data to figure out what kind of wipers are used by the cars in your radius based on year, make and model.

Advantages of PaaS

One advantage of PaaS is that large business organizations can decide to contract Cloud services to replace many things they already do, like administering their own platforms, their own programming environments, and their own database. That could mean that the business would save costs and complexity because it no longer needs as many information technology people who traditionally do these jobs. It also means that the business can buy as much or as little PaaS as it needs.

disadvantage of StaaS

One criticism of StaaS is that it requires the business using it to increase their bandwidth required to access their Cloud, which is an added cost. And, unable to use without the internet

Business Database Scenario Disadvantages

One disadvantage in this situation was that Zach was only showing the REO foreclosed properties that had a completed file. This was an issue because all other parts of the bank were using this '400' completed number for their job when there were actually '500' properties in total.

what is the most widely used database software in the world?

Oracle

Platform as a Service (PaaS)

PaaS is when a Cloud provider supplies a computer platform (like Windows) through the Internet. Also provided in PaaS are programming applications, database, and web servers. Some Cloud PaaS providers include Amazon's Elastic Beanstalk, Force.com, and Google's App Engine.

Consider a small startup business of twenty-five people that is trying to secure venture capital from a bank

They can go the traditional route and include the following on the spreadsheet in their business plan: Lease office space, buy computers, wire all the computers together to make a hard-wired network (a small Local Area Network) Now, think about your business mitigating some of these startup costs with Cloud Drives with SaaS. You'll still need an internet computer, but, cloud Drives can be purchased and administered just like a hard-wired network, with the same types of permissions. All twenty-five employees will be able to access the same Cloud "network" as they would the hard-wired network; therefore, the Cloud replaces the need and cost for the hard-wired network. Since the Cloud is accessible from virtually anywhere, it also replaces the need and cost for office space.

Can MS Office really be replaced when some consider it an essential business tool?

Think about Apache's OpenOffice as an alternative to MS Office with a modified Cloud business environment. Apache OpenOffice is an open-source business software suite that has a word processor like Microsoft Word, a spreadsheet like Excel, presentation software like PowerPoint, and a database like Access.

Infrastructure as a Service ( IaaS)

This is one of the main categories of Cloud Computing. This give you the ability to sit at home or work and use your own keyboard, mouse, and monitor, but connect to their computer and all of its resources like Photoshop (software) and storage via the Internet.

From a business standpoint, there is much more to consider before investing in PaaS.

disadvantages with PaaS: A business organization can be locked in to a PaaS that may not be flexible enough and allow the company to grow. It also points out that, had it kept its information technology staff, the business would still have had the ability to do what it considers necessary to grow.