Class Agenda – 02/03/2014

1. 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 • 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

2. Course Learning Process • Course objectives • Visualization methods; systems development process • Systems development tools: Access and Excel • Methods to learn material • Course lectures: concepts, exercises, questions • Book 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 labs • Book material: • Explanation, concepts, detailed description • Methods to assess learning (and hopefully learn more…) • Homework and projects: • Apply instruction from tutorials • Create simulated “real” applications

3. Help available • Your books!!!! • Online help via Access and Excel • Google (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 lab • Email: rdittmer@unr.edu • Professor (Christina Hilfer) • Email: chilfer@unr.edu

4. Physical Views of Computer Data Organization & Access Conceptual I want to buy music on iTunes. I want to see if that shirt is available in blue in a size medium. Primary Storage/Main Memory Processor ALU Secondary Storage/Disk Data and Programs

7. Computer Data Organization Vocabulary Windows Library: View Folders Database Program Files Data Files Records Fields Bytes Bits

8. How does it work? Primary Storage/Main Memory Processor Data and Programs when they are being processed ALU Secondary Storage/Disk Data and Programs when they are not being processed

9. What is an operating system? • Examples: • Windows • Unix • Mac OS X • Linux • Definition: Set of software that manages a computer’s operations.

10. 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

11. Moving data between primary and secondary storage Operating System MS Excel Firefox Photoshop MS Word Secondary Storage/Disk Primary storage/main memory

12. Organizing Files and Folders • 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 folders • Disks contain folders that hold files • USB drives • Compact discs (CDs) • Digital video discs (DVDs) • Hard disks • Each drive is assigned a letter

13. What types of data are stored on a computer? • Video, pictures, audio • Email • Web pages • Word processing documents • PowerPoint-type presentations • Structured data: Pre-defined formats such as employee, customer, student, registration data.

14. Understanding the Need for Organizing Files • 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

15. Understanding the Need for Organizing Files and Folders

16. Developing Strategies for Organizing Files

17. Developing Strategies for Organizing Files • 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 files • Definitely all data files • All program files that you don’t have available in other forms (downloads, other media)

18. Exploring Files, Folders, and Libraries • Windows Explorer and the Computer window show the drives, folders, and files on your computer • Each has a slightly different view • A folder window displays the files and subfolders in a folder • Divided into two sections, called panes

19. Exploring Files, Folders, and Libraries

20. Using Libraries and Folders • 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

21. Files in a Folder Window

22. Navigating to Your Data Files • The file path is a notation that indicates a file’s location on your computer G:\FM\Tutorial\Map.png • G: is the drive name • FM is the top-level folder on drive G • Tutorial is a subfolder in the FM folder • Map.png is the full filename with the file extension

23. Navigating to Your Data Files

24. Managing Folders and Files • Creating a folder • In the Navigation pane, click the drive or folder in which you want to create a folder • Click New folder on the toolbar • Type a name for the folder, and then press the Enter key

25. Working with Folders and Files • Moving and Copying Files and Folders • Moving a file removes it from its current location and places it in a new location you specify • Copying places the file in both locations • Naming and Renaming Files • Filenames provide important information about the file, including its contents and purpose • Main part of the filename • File extension • A filename extension identifies file type and indicates program in which file was created • Deleting Files and Folders • Recycle Bin is an area on your hard disk that holds deleted files until you remove them permanently • Files removed from a network drive do not go to the Recycle Bin!

26. Working with Compressed Files • Files stored in a compressed (zipped) folder take up less disk space • Allows 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 programs • WinZip • 7-Zip

27. 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, HANA • IBM products: Informix, DB2 • Open-ish: PostgreSQL

28. 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.

29. 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.

30. Why do we store data in a database? • 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.

31. What other software is available for data storage? • Word processing • Spreadsheets • Application programs • Examples: TurboTax, QuickBooks • Data storage: files, or enhanced file structures • Some application programs rely on a DBMS

33. Overview of MS Access • 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.

34. Database Vocabulary- 1 • Table: 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.

35. Database Vocabulary- 2 • Entity (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.

36. Process for Designing Databases (Database Normalization) • 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, initial Example 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

37. Examine the contract “spreadsheet” • 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?

39. Contract Data

40. Group (sub-divide) the customer data

41. Group (sub-divide) the invoice data

42. What is a primary key? • Definition • Examples • “Natural” vs. “Surrogate”

43. Primary Keys • Naturalkeys are primary keys having an innate value • Examples • A UPC code • A stock symbol • A book ISBN number • In cases where there is no innate value, we create an artificial key (surrogate key). • The key value is arbitrary • Maybe just some Integer value • Composite key: two or more fields combine to uniquely identify a record. • Sometimes used when joining tables.

44. Define keys and relationships

45. What does it look like in Access?

46. What is a foreign key? • Definition • Example • Depiction on diagrams

47. What is referential integrity? • Definition • “Constraint” • Conceptual example • Implementation in Access example

48. Referential Integrity Usage • Primary keys ensure entity integrity • Eliminates duplicate records • Null values are prohibited • Referential integrity ensures that a foreign key in one table matches a primary key in another table • Without referential integrity, orphaned records can exist • A record in the (many) table with a corresponding record in the (one) table

49. Referential Integrity in Access

50. Referential Integrity • Enforce referential integrity means 1-to-many relationships are enforced • Cascade 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