Exam 3 (Ch.15 case study, Prom-11,12,13,14 ABCC 15,16,17)
iDrives and problem with them
What GoDaddy used to offer (off-store file storage) iDrives was that the Internet was just too slow to store and back-up data and information like spreadsheets and word documents
Third party IaaS provider host example
Windows Azure Google Compute Engine
Open Office comparables= Microsoft Word compare to...
Writer can produce Portable Document Files (PDFs) and web pages
By storing your data on Amazon's CSP, are you giving all rights and access to everything you load onto the cloud to outside observers to sift through?
YES
Entity relationship modeling (ERM)
a database-modeling method used to construct a theoretical and conceptual representation of data to produce a schema a picture of a database's tables and how they relate to each other.
Cloud Drive
a hard drive in the Cloud
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. Reports can be generated from tables or queries.
Parameter Query
allows you to specify a criterion for the query result at the time that the query is executed
Advantages in cloud drives
automatic back up and access from any Internet enabled computer
Compact on Close option
automatically analyze a database file and reduce the size of the file every time the file is closed
Cloud Service Providers (CSP)
back-up like Google, Microsoft, or DropBox would provide back up automatically means that you don't particularly need any specific microcomputer to get work done, just one that can connect to the Internet
Access 'Database Tools' menu tab
contains a set of advanced utilities for working with your database.
Access 'create' menu tab
contains all of the features used to create and manage database objects
Access 'External Data' menu tab
contains the tools used to import data into Access from external sources or export data for other applications (such as Excel) or in other electronic formats.
Multi-table query example
could be used display all of the orders placed by each customer requires information from the Customer and SaleOrder tables
Field Size property
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.
reports
display database information on screen or on paper
The navigation pane
displays the objects in a database file
Access refers to the attributes of a table as
fields
Relational Database Model
first proposed in 1970 by Edgar F. Codd, departed from this tradition by insisting that applications should search for data by content, rather than by following links
A database table is a collection of records that all
have the same attributes
one-to-one relationship example
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.
One-to-many relationship
most common type of 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
tables
most important database software - well thought-out collection of computer files
Structured Query Language (SQL)
most widely used standard computer language for relational databases as it allows a programmer to manipulate and query data
Each field must contain a
name and data type
Schema
name of a field in the data structure
If an organization depends on IaaS and the Internet goes down...
org could potentially suffer because it is without computing power = reduction of output
forms
overlay data tables and queries for more specific views of data
Form
overlays the data table to show information in an organized matter
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 and to navigate through the records of a table in a user-friendly way.
Disadvantages in cloud drives
privacy?? Who is accessing my information?
Most common uses for an SQL (Structured Query Language)
query a table
The collection of information you have for an instance of an entity is called a
record. Records are stored in the rows of tables
A MS Access database is a
relational database
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
one-to-one relationship
represents the situation where a record from one table can only be associated with one record from the other table
Criticisms of StaaS:
requires the business using it to increase its bandwidth (added cost)
Field Mask
restricts data input
Database tables store information about
specific things called entities
Aggregate queries perform....
summary calculations across all of the records for a particular field
Third party IaaS provider host
supplies hardware like a computer, software applications like Adobe Photoshop, servers, and storage in their Infrastructure as a Service, all connected via the Internet
"On-demand software"
the CSP installs and operates application software in the Cloud that the Cloud user can use. Ex. 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
one-to-many relationship example
the relationship between orders and customers. A customer can place more than one order, but an order will only belong to one customers.
Many-to-many relationship example
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.
"data validation" rules
these rules help to ensure the data's integrity
"One to Many" relationship
type of cardinality that refers to the relationship between two entities
Required property
used make sure that a value is entered for a field when a new record is added to a table
Database queries
used to automate the process of extracting the information that we need. In this section, we will discuss how to construct simple queries of the data on a single database table
Calculated data
used to create a field that is generated by a formula Calculated fields will refer to values in other fields for a record
Crosstab queries
used to create a multi-dimensional view of our data kind of like Pivot tables in Excel
Multi-table queries
used to display results from fields on different tables
saved query files
used to find specific populations (subsets) within databases
Queries
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. Queries can also be used to perform some basic calculations on numeric data.
Default value property
used to have Access automatically set a starting value for a field when a new record is added to the table
Input Mask property
used to set rules for how the data will be entered for the field ex. you can use an input mask to make sure that users enter 10 digits for a telephone number
Lookup Wizard data
used to simplify data entry 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 data
used to store data that has only two possible values such as yes or no, true or false, and on or off
Date/Time data
used to store date or time information can be formatted to display dates or times several different ways
OLE Object data
used to store files created by other programs such as Word or Excel documents, picture files, or sound files better to store this type of data as an Attachment data type rather than an OLE Object data type
Attachment data
used to store files for a database record can be virtually any type of file including pictures, documents, sounds, and videos
Long text data
used to store larger amounts of text limited to 65,536 characters can contain any combination of letters, numbers, and symbols.
Hyperlink data
used to store links to websites or other folders/files on your computer
Short text data
used to store most data that won't be treated like a number such as text, telephone numbers, zip codes, etc. limited to 255 characters can be any combination of letters, number, and symbols.
Number data
used to store positive and negative numbers may contain decimal places Number or Currency data type must be used when you expect to perform calculations on the data in a field
Tables
used to store the data in Access. A table consists of columns, which store data fields, and rows, which store database records.
Aggregate Query
used when you wish to calculate summary statistics used to group query results into categories based on the values for a field and then perform calculations for each category
Cloud
using computer resources like a hard drive or software that exists on another computer connected by a network, typically the Internet "Using another computer's resources"
Validation Rule/Validation Text property
validation rules and validation text work together used to have Access check values when they are entered into a table to make sure they fit a specific rule ex. you could use a validation rule to make sure that all of the values entered into a field are greater than 10
Crosstab queries 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
Aggregate Query Example
we may wish to know how many of each product QuickStove has sold...construct a query that includes the productName from the Product table and quantity from the OrderDetail table
Parameter query example
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
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.
database schema
"map" of data tables and their relationships to one another
Ch. 15 Case Study Zach
- Zach using Word to compile fact sheets (lame & old) - Company depends on him, but Zach using two folders to keep track of very important information - Friend suggests solution in word, still lame, suggests solution in Excel, last suggestion is to migrate data to Access - Abby hooks Zac up with a sweet Access database hookup - Zach concerned about the data, but realizes that he messed up on some of the data sheets - Zach loves the database now after Abby shows him all the cool tricks - Zach changes the entire bank
How did the database help Zach?
- didn't have to worry about backups - no longer manage individual word documents - input and update information on fact sheet easily - Found and easily corrected past input errors -Print form and distribute it by querying complete REOs - Eliminates print need!
Tables consist of 1. 2. that can be 3. to produce subsets of information
1. records (rows) separated by 2. fields (columns) 3. queried (questioned)
Why do we normalize databases?
1. we ensure that each table contains data about very specific things 2. limit duplicated or redundant information as much as possible
Open Office comparables= Microsoft Access compare to...
Base database application
Open Office comparables= Microsoft Excel compare to...
Calc
"Terms of Service" (TOS)
Customers must agree with. ex. Amazon
entity relationship (ER) diagrams
Database designers construct databases using this
database management systems ( DBMS)
Databases are created using software systems known as
Open Office comparables= Adobe Photoshop compare to...
Draw graphics editor
on-demand
IaaS can quickly adjust and supply more computing power
Pay-as-you-go model
IaaS customer can use this model, eliminates the cost of having to buy new computers (per hour, per week, per month)
Open Office comparables= Microsoft Powerpoint compare to...
Impress
Relational database
In a DBMS, data is stored in computer files called tables, and tables are connected to other tables with related information
Currency data
Number data type that places a currency symbol ($) in front of the number
AutoNumber data
Number data type. Access will automatically insert that value for the AutoNumber field when a new record is added to a table can be set to add 1 to the last number used for the field or it can assign a randomly generated number
"Platform as a Service (PaaS)"
PaaS is when a Cloud provider supplies a computer platform (like Windows) through the Internet
Two ways to create a query
Query Wizard Query Design (QBE approach)
REO
Real Estate Owned
Technical term for a table
Relation
SQL structure ex
Select - "Title" From - "Book" Where - "Price" > $100 Order - "Title"
Infrastructure as a Service (IaaS)
Sitting at home or work and use your own keyboard, mouse, and monitor but connect to their computer and all of its resources like Photoshop and storage via the Internet
"Storage as a Service" (StaaS)
Storing or renting space from a CSP
Zach case study - surprise accident
The "complete/incomplete" field filter He had put this on to hide any REO that didn't have complete information
Filtering
The ability to add a filter to a data table that would only display specific information ex. only displaying AZ in field of "State"
Normalization
The process of organizing tables and relationships between tables in a database
