class agenda – 02/03/2014
DESCRIPTION
Class Agenda – 02/03/2014. Finish presenting Information Visualization Review logistics of course including course file placement/Where to seek help Team Declaration Present computer data organization Explore files, folders, and libraries in Windows 7 - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/1.jpg)
Class Agenda – 02/03/2014
1
Finish presenting Information Visualization Review logistics of course including course file
placement/Where to seek help Team Declaration Present computer data organization Explore files, folders, and libraries in Windows 7 Discuss the different levels of software available to manage
data on a computer. Explore the functions of a DBMS vs. an operating system to
manage data. Discuss the relative data management capabilities of a DBMS
vs. a spreadsheet. Introduce Access Begin discussion of database design
![Page 2: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/2.jpg)
Course Learning Process
2
Course objectivesVisualization methods; systems development processSystems development tools: Access and Excel
Methods to learn materialCourse lectures: concepts, exercises, questionsBook tutorials:
Labs: in-class time to try out new and/or difficult Access and Excel skills/concepts
Outside of class: do book tutorials not done in labsBook material:
Explanation, concepts, detailed descriptionMethods to assess learning (and hopefully learn more…)
Homework and projects: Apply instruction from tutorialsCreate simulated “real” applications
![Page 3: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/3.jpg)
Help available
3
Your books!!!!Online help via Access and ExcelGoogle (or other favorite search engine)IS Lab Assistants: Every day except Saturday
http://www.weekspace.com/IS101/LabAssistants.htm
IS Graduate Assistant (Robert Dittmer)Office hours: Mondays10 AM – noon in labEmail: [email protected]
Professor (Christina Hilfer)Email: [email protected]
![Page 4: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/4.jpg)
Views of Computer Data Organization & Access
ConceptualI want to buy music
on iTunes. I want to see if that
shirt is available in blue in a size medium.
Physical
4
Primary Storage/Main Memory
Processor
ALU
Secondary Storage/Disk
Data and Programs
![Page 5: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/5.jpg)
5
![Page 6: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/6.jpg)
6
![Page 7: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/7.jpg)
Computer Data Organization Vocabulary
7 Bits
Bytes
Program Files Data Files
Database
Fields
Records
Windows Library: View
Folders
![Page 8: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/8.jpg)
8
How does it work?
Primary Storage/Mai
n Memory
Processor
ALU
Secondary Storage/Disk
Data and Programs when they are not being processed
Data and Programs when they are being processed
![Page 9: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/9.jpg)
What is an operating system?Examples:
WindowsUnixMac OS XLinux
Definition: Set of software that manages a computer’s operations.
9
![Page 10: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/10.jpg)
What does an operating system do? Manage hardware and software resources:
Allocate main memory.Direct processor activities.Track all program activities.Manage network connections.Allocate secondary storage.Move data and programs from secondary
storage to main memory and back again.Manage users
10
![Page 11: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/11.jpg)
11
Moving data between primary and secondary storage
Secondary Storage/Disk
Primary storage/main memory
Operating System
MS Excel
Firefox
Photoshop
MS Word
![Page 12: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/12.jpg)
Organizing Files and Folders
12
A file is a collection of bytes that has a name and is stored in a computer
A file can store a “program” or “data”Organize files by storing them in foldersDisks contain folders that hold files
USB drivesCompact discs (CDs)Digital video discs (DVDs)Hard disks
Each drive is assigned a letter
![Page 13: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/13.jpg)
What types of data are stored on a computer?
13
Video, pictures, audioEmailWeb pagesWord processing documentsPowerPoint-type presentationsStructured data: Pre-defined formats such
as employee, customer, student, registration data.
![Page 14: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/14.jpg)
Understanding the Need for Organizing Files
14
Windows (and all other operating systems) organizes folders and files in an hierarchy, or file system
Windows stores the folders and important files it needs to turn on the computer in its root directory
Folders stored within other folders are called subfolders
![Page 15: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/15.jpg)
Understanding the Need for Organizing Files and Folders
15
![Page 16: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/16.jpg)
Developing Strategies for Organizing Files
16
![Page 17: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/17.jpg)
Developing Strategies for Organizing Files
17
Type of disk you use to store files determines how you organize those files
Storing files on removable media allows you to use simpler organization
The larger the medium, the more levels of folders you should use
You should have a “backup”, or duplicate copy, of all filesDefinitely all data filesAll program files that you don’t have available
in other forms (downloads, other media)
![Page 18: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/18.jpg)
Exploring Files, Folders, and Libraries
18
Windows Explorer and the Computer window show the drives, folders, and files on your computerEach has a slightly different view
A folder window displays the files and subfolders in a folderDivided into two sections, called panes
![Page 19: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/19.jpg)
Exploring Files, Folders, and Libraries
19
![Page 20: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/20.jpg)
Using Libraries and Folders
20
When you open Windows Explorer, it shows the contents of the Windows built-in libraries by default
Libraries display similar types of files together, no matter where they are stored
![Page 21: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/21.jpg)
Files in a Folder Window
21
![Page 22: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/22.jpg)
Navigating to Your Data Files
22
The file path is a notation that indicates a file’s location on your computerG:\FM\Tutorial\Map.pngG: is the drive nameFM is the top-level folder on drive GTutorial is a subfolder in the FM folderMap.png is the full filename with the file
extension
![Page 23: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/23.jpg)
Navigating to Your Data Files
23
![Page 24: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/24.jpg)
Managing Folders and Files
24
Creating a folderIn the Navigation pane, click the drive or
folder in which you want to create a folderClick New folder on the toolbarType a name for the folder, and then press
the Enter key
![Page 25: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/25.jpg)
Working with Folders and Files
25
Moving and Copying Files and FoldersMoving a file removes it from its current location and places it in a
new location you specifyCopying places the file in both locations
Naming and Renaming FilesFilenames provide important information about the file, including
its contents and purposeMain part of the filenameFile extension
A filename extension identifies file type and indicates program in which file was created
Deleting Files and FoldersRecycle Bin is an area on your hard disk that holds deleted files
until you remove them permanentlyFiles removed from a network drive do not go to the Recycle Bin!
![Page 26: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/26.jpg)
Working with Compressed Files
26
Files stored in a compressed (zipped) folder take up less disk spaceAllows you to transfer files more quickly
Extracting a file creates an uncompressed copy of the file in a folder you specify, while the original file remains in the compressed folder
Compression programsWinZip7-Zip
![Page 27: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/27.jpg)
What is a DBMS?A set of software that facilitates storage and
access of data on a computer.Designed to work with a specific operating
system.Examples from vendors.
Microsoft products: Access, FoxPro, SQL Server
Oracle products: Oracle, MySQL SAP: Sybase, HANAIBM products: Informix, DB2Open-ish: PostgreSQL
27
![Page 28: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/28.jpg)
What does a DBMS do?Data structure maintenance: add, delete,
change data objects. Data maintenance: add, delete, change
data.Data backup and recovery.Concurrency control.Data access (query) optimization. Security.Distributed data management.
28
![Page 29: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/29.jpg)
What does MS Access do?Data structure maintenance: add, delete,
change tables for data storage. Data maintenance: add, delete, change
data in tables.Provide user-friendly tools for data access.
Forms.Reports.Integration with other software such as Excel.
29
![Page 30: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/30.jpg)
Why do we store data in a database?
30
To protect the “integrity” of the data.Make data accurate.Reduce data redundancy.
To make data more accessible.Enhance flexibility of data access methods.Improve speed of data access.
To make an application more adaptable.Provide more flexibility in application
development.Decrease dependence on a given visualization
method.
![Page 31: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/31.jpg)
What other software is available for data storage?
Word processingSpreadsheetsApplication programs
Examples: TurboTax, QuickBooksData storage: files, or enhanced file structuresSome application programs rely on a DBMS
31
![Page 32: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/32.jpg)
32
Decision Issue
Spreadsheet (Excel)
Database (Access)
Ability to maintain accurate data
Limited. Completely dependent on user for accurate input.
Excellent. Many filters/constraints available to protect data accuracy.
Ease of showing data in different formats
Limited. Can make small color changes to basic spreadsheet.
Excellent. Very flexible. Data entered once can be viewed in many different formats.
Ease of doing calculations
Excellent. Statistics, financial functions available.
Limited. Can do calculations, but only basic math.
Difficulty of learning/using product
Excellent. Easy to learn.
Difficult to learn.
Necessity for pre-planning
Not much planning required.
Must plan/design the database in advance.
![Page 33: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/33.jpg)
Overview of MS Access
33
Data Management Software (not technically a database management system)
Has the following objects:Tables: Object to store data. This is the
only data storage object in Access.Queries: Object to view data stored in
tables. Can be used to filter data, reformat data, create calculations, create aggregations, create summarizations.
Forms: Object to enter data into a table in user friendly format. Also used to view data in a nice format on a screen.
Reports: Object to view data in a nice format on paper.
![Page 34: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/34.jpg)
Database Vocabulary- 1Table: A two-dimensional database object
used to store data.Row: One “entry” of data within the table.
Must have a primary key that has a different value than all other rows of data within the table
Column: A field used to store data. Must have a single data type.
Cell: An intersection of a row and column. Can only have one data type and one value.
34
![Page 35: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/35.jpg)
Database Vocabulary- 2Entity (when implemented it is called a
“table”): A person, place, or thing about which we store data. Example is a Customer for Belmont Landscapes.
Entity instance(also called a “record” or “row”): One instance of an entity that includes all data stored about that instance. Example is “Anthony Rodriquez row” for Belmont.
Attribute (also called a “field” or “column”): A characteristic of the entity about which we store data. Examples are Company, FirstName, Lastname for a Customer entity.
35
![Page 36: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/36.jpg)
Process for Designing Databases (Database Normalization)
36
Identify all the fields needed to produce the required information Divide each piece of data into its smallest useful part
Example 1: Break up a name into first, last, initialExample 2: Break up an address into street, city, state, zip
Group related fields into tables Use an entity-relationship diagram (ERD) to depict the design Determine each table’s primary key Identify how the tables are related (or if they are related)
Include a common field in related tables (foreign key) Determine the properties of each field
Type of data: Text, date, number, etc. Size of data Name of data
![Page 37: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/37.jpg)
Examine the contract “spreadsheet”
37
Review data content.What is the application? What is the
purpose of the stored data?Is any of the data redundant? Why might redundant data be a problem?
![Page 38: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/38.jpg)
38
Contract Number Customer ID Company LastName FirstName Phone Address City State ZIP Contract Type Invoice Number Invoice Date Invoice Amount
3011 11001 StudentLast StudentFirst616-866-3901
49 Blackstone Drive Rockford MI 49341 Residential Landscape Plan 2011 03/23/2013 $1,500.00
3011 11001 StudentLast StudentFirst616-866-3901
49 Blackstone Drive Rockford MI 49341 Residential Landscape Plan 2012 05/10/2013 $2,500.00
3012 11027 O'Brien Karen517-483-9244 38 Langley Rd Lansing MI 48933
Consultation for backyard, residential 2021 02/25/2013 $300.00
3015 11005 Hawes Owen616-392-0622 102 Pineview Rd Holland MI 49423
Schematic plan for backyard, residential 2041 04/01/2013 $1,500.00
3017 11012Grand Rapids Engineering Company Rodriquez Anthony
616-454-9801
225 Summer Street
Grand Rapids MI 49503 Peer plan review for town 2051 04/01/2013 $2,250.00
3020 11055 Fox and Hound Grille Gorski Steve269-979-2004
1440 Beadle Lake Rd
Battle Creek MI 49014 Landscape design for restaurant 2031 04/19/2013 $1,500.00
3020 11055 Fox and Hound Grille Gorski Steve269-979-2004
1440 Beadle Lake Rd
Battle Creek MI 49014 Landscape design for restaurant 2032 08/19/2013 $2,000.00
3020 11055 Fox and Hound Grille Gorski Steve269-979-2004
1440 Beadle Lake Rd
Battle Creek MI 49014 Landscape design for restaurant 2033 09/20/2013 $2,000.00
3020 11055 Fox and Hound Grille Gorski Steve269-979-2004
1440 Beadle Lake Rd
Battle Creek MI 49014 Landscape design for restaurant 2034 10/20/2013 $1,000.00
3021 11040RiverView Development Company Nowak Charles
616-988-0777 144 E Tower Ave
Grand Rapids MI 49503
Landscape plans for multifamily housing site 2111 10/12/2013 $4,500.00
3021 11040RiverView Development Company Nowak Charles
616-988-0777 144 E Tower Ave
Grand Rapids MI 49503
Landscape plans for multifamily housing site 2112 11/12/2013 $3,000.00
3021 11040RiverView Development Company Nowak Charles
616-988-0777 144 E Tower Ave
Grand Rapids MI 49503
Landscape plans for multifamily housing site 2113 09/02/2014 $12,000.00
3021 11040RiverView Development Company Nowak Charles
616-988-0777 144 E Tower Ave
Grand Rapids MI 49503
Landscape plans for multifamily housing site 2114 09/05/2015 $8,500.00
3022 11043 Monroe State College Kirk Rachel616-988-1320 40 Monroe St
Grand Rapids MI 49503 Landscape design for two entrances 2101 07/14/2013 $4,500.00
3022 11043 Monroe State College Kirk Rachel616-988-1320 40 Monroe St
Grand Rapids MI 49503 Landscape design for two entrances 2102 11/15/2013 $12,000.00
![Page 39: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/39.jpg)
Contract Data
39
![Page 40: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/40.jpg)
Group (sub-divide) the customer data
40
![Page 41: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/41.jpg)
Group (sub-divide) the invoice data
41
![Page 42: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/42.jpg)
What is a primary key?
42
DefinitionExamples“Natural” vs. “Surrogate”
![Page 43: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/43.jpg)
Primary Keys
43
Natural keys are primary keys having an innate value
ExamplesA UPC codeA stock symbolA book ISBN number
In cases where there is no innate value, we create an artificial key (surrogate key).The key value is arbitraryMaybe just some Integer value
Composite key: two or more fields combine to uniquely identify a record.Sometimes used when joining tables.
![Page 44: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/44.jpg)
Define keys and relationships
44
![Page 45: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/45.jpg)
What does it look like in Access?
45
![Page 46: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/46.jpg)
What is a foreign key?
46
DefinitionExampleDepiction on diagrams
![Page 47: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/47.jpg)
What is referential integrity?
47
Definition“Constraint”Conceptual exampleImplementation in Access example
![Page 48: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/48.jpg)
Referential Integrity Usage
48
Primary keys ensure entity integrityEliminates duplicate recordsNull values are prohibited
Referential integrity ensures that a foreign key in one table matches a primary key in another tableWithout referential integrity, orphaned records can
existA record in the (many) table with a corresponding
record in the (one) table
![Page 49: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/49.jpg)
Referential Integrity in Access
49
![Page 50: Class Agenda – 02/03/2014](https://reader035.vdocuments.us/reader035/viewer/2022062521/568168af550346895ddf6b1c/html5/thumbnails/50.jpg)
Referential Integrity
50
Enforce referential integrity means 1-to-many relationships are enforcedCascade Update means that if primary key changes
in master table the corresponding key is updated in the detail table
Cascade Delete means that deleting master record causes corresponding child records to be deleted