Welcome to CMPE003 Personal Computers: Hardware and Software

1. Welcome toCMPE003Personal Computers: Hardware and Software Dr. Chane Fullmer Fall 2002 UC Santa Cruz

2. Exams • Exams now on-line….. • http://www.cse.ucsc.edu/classes/cmpe003/Fall02/ • Must have your own Scantron form to determine test version you took

3. Assignments • Assignment #6 – The Last One  • Due November 22, 2002 • This Friday…don’t be late.. • Using Spreadsheets – (M$ Excel) • Generate a personal monthly budget spreadsheet • Must printout in color if you use color for the conditional in part #10 http://www.soe.ucsc.edu/classes/cmpe003/Fall02/hw6_spreadsheet.html

4. Database Management: Getting Data Together Chapter 14

5. Objectives • Describe the hierarchy of data • Explain the differences between files and databases • List the four database models • Describe the concept of data integrity • Describe the functions of a database management system • Describe the process of creating a database in general terms • Compare and contrast relational and object-oriented databases • Explain what a data warehouse is an how it differs from a database

6. Computer Databases • Track information • Allows higher productivity as a result of better information

7. Hierarchy of Data • Field • Smallest meaningful unit of data • Group of one or more characters that has a specific meaning • Used as “key” to locate records • Record • Set of fields containing all information known about one entity • Each record contains the same fields in the same sequence • File • Collection of related records

8. Hierarchy of Data

9. File Processing • File processing • Data redundancy • Database models reduce redundancy • Saves storage space • Saves update effort • Time • Accuracy

10. Data (From Chapter 6)Organizing and Accessing • Design (plan) for way data is • Received • Organized • Stored • how it will be processed • Plan determined by programmer and/or systems analyst

11. DataGetting Organized Character Field Record File Database

12. Data Access Methods • Application determines how data must be accessed by users • Data is organized based upon access method • Organization method limits choice of storage medium

13. Sequential • Records are stored and accessed in order • All records prior to the one requested must be read • Magnetic tape storage

14. Direct / Random Access • Records are not physically stored in any order • Go directly to the record to read • Hashing – apply a formula to the key to produce the address of the record • Collision – same address from different keys • Updating in place • Read, change, and return a record to the same place on disk • DASD – Direct-Access Storage Device needed • ie, Disk Drive

15. Indexed • Records are stored sequentially • Index is generated that contains key and address • Can be read in order = sequential • Can be read out of order = random

16. Processing Stored Data • Batch • Transaction • Terminology • Transaction – updates a record • Master file – contains all the data

17. Batch Processing • Collect transactions into a transaction file and perform periodic updates • Process • Transactions are sorted by key field • Computer matches the master and transaction keys • Performs requested action – add, revise, delete • New master file created • Error report is printed • Master file only current immediately after processing

18. Batch Processing Stored Data

19. Transaction Processing • Processed upon request • Real-time – process handled immediately • Disk storage • Direct access to desired record needed • Immediate access to stored data • Immediate updating of stored data

20. Batch and Transaction • Computer system may use both processing types based upon the application • Transaction • Activities relating to current needs • Batch • Updates per schedule

21. Applications • Motor Vehicle Dept • Transaction • Police check for stolen car report • Batch • Motor vehicle records of owner information • Retail – POS • Transaction • Item price • Inventory updates as sale is made • Batch • Produce daily and weekly sales reports • Banks • Transaction • Check balance • Record cash withdrawal • Batch • Deposit left in the deposit drop • Bank statement

22. Database Models • Database Types • Relational • Object-oriented • Hierarchical • Network • Each type structures, organizes and uses data differently

23. RDBMSRelational Database Management System • Organizes data into related tables (files) • Table consists of rows and columns • Tables linked based upon a common field (key)

24. RDBMSKey • Primary key • A field whose value uniquely identifies a record • Foreign key • Primary key of another table • Used as link to other table • May have duplicate values

25. OODBMSObject-Oriented Database Management System • Manipulates object-oriented databases • Object – represents a real-world entity • Attributes / properties • Data about the entity • Methods / Actions • Operations that work the data

26. OODBMSObject-Oriented Database Management System • Compared to RDBMS • More complex • Steeper learning curve • Skilled employees needed who earn high pay • Combined Object/relational DBMS • Relational database that incorporates some complex data types

27. Data Integrity • Degree to which data is accurate and reliable • Integrity constraints – rules • Acceptable values for a field • Primary key values • Foreign keys • Integrity constraints must be enforced when data is entered or data is unreliable • GIGO (Garbage in, garbage out)

28. DBMSDatabase Management System Levels of software • Sophisticated • Mainframe • Expensive – tens of thousands of dollars • Complex • Planned and managed by computer professionals • Simple • PC • Inexpensive – few hundred dollars • User can set up and use the database

29. DBMSDatabase Management System Basic functions • Create a database • Enter data • Modify the data as required • Retrieve information from the database

30. DBMSCreate a Database • Data Dictionary / Catalog • Stored data about the tables and fields within the database • Per table • Table name • Relationships • Per field • Field name • Data type • Field size • Validation rules

31. DBMSEnter and Modify Data • Operations • Adding new data • Modifying data • Deleting data • Methods • User interacts directly with DBMS • Programs written by professional programmers access the data using special commands built into the DBMS

32. DBMSData Retrieval • Extracting the desired data from the database • Primary forms • Queries • Reports

33. Query • Ask a question about the data • Present criteria that selects data from the database • Results in smaller portion of the database • Query Language • Prepare your query using English-like statements • Proprietary query language in DBMS

34. Query SQLStructured Query Language Entered directly by user Included in programs QBE Query-by-Example Graphical interface to specify your criteria

35. Report • Formatted presentation of data from the database • Normally printed • Designed using a report generator

36. The DBMS Process Review the data and edit until accurate Describe the data Enter the data

37. The DBMS Process The Plan… The Report…

38. Concurrency Control • Databases are used concurrently by many users • Problem if several users attempt to update the same record at the same time • Record locking • First user requests record • Others are locked out for update

39. Concurrency Control

40. Security • Data is stored in a central location • Problem: unauthorized access is major concern • Benefit: easier to apply security measures • Features • User ID and password • Privileges assigned to each user • Read-only • Update

41. Backup and Recovery • Why? • Data can be accidentally damaged or destroyed • Hardware can fail • Forces of nature can cause physical damage • Software or human errors can corrupt data • Backup – copy made periodically • Recovery – replaces the damaged database with the good backup

42. Looking at the DataOLTP – Online Transaction Processing • Supports day-to-day database activities • Little support for data analysis

43. Looking at the DataData Warehouses • Databases designed to support ongoing operations • Data is captured from the db over time • Summary form • Scheduled basis • Period of time • May include data from external sources

44. Looking at the DataData Warehouses • Online Analytical Processing (OLAP) • Analyzes the data • Produces information for managers • Data mining • Statistical and artificial intelligence techniques • Look for otherwise unrecognized -- • Patterns • Relationships • Correlations • Trends • Helps managers make strategic business decisions

45. Have A Great Day