INFSY540 Information Resources in Management

1. INFSY540Information Resources in Management Lesson 6 Chapter 5 Database Management

2. Portfolio and Ecommerce HTML Assignment • Assignment is on course web space at http://www.courses.psu.edu/infsy/infsy540/rak13/HTML_Portfolio.html • Part of the assignment is to do research on ecommerce. • The first version of your portfolio.html page and subsequent sections of your site must be posted by the October 31st class. The final site must be online by November 28. • There are individual and team activities that need to take place between these times.

3. Case 2: Cisco Systems Architecture • What’s this case about? • When did Cisco go public? • What does Cisco stand for? • What is Cisco’s mission? • IT Interaction Model http://equity.stern.nyu.edu/~msilver/mbacore/itimhdo.htm

4. Learning Objectives • Know the difference between a database and a spreadsheet. • Identify the objects available in a relational database and their purpose. • Know the difference between a query and a filter. • Identify typical problems that occur with MS Access and other “personal” vice “enterprise” databases.

5. Shared Data • Poorly organized data prevents sharing that data with other “databases” • Think of all the “databases” that lists your name, department, etc.: • Messiah College Phone List Database • Students Using College Networked Computers • Students Using Dining Facility • Students Using Nursing Facility

6. Database Management System • Computer program designed to help a user store and retrieve data • Access, Oracle, DB2

7. DBMS forms Outside Applications tables reports DBMS Engine abcd 1234 defg 12 asd 123 kghb 112 abcd 1234 defg 12 asd 123 kghb 112 abcd 1234 123 asd 123 12 kghb 112 34 abcd 1234 defg 12 asd 123 kghb 112 query abcd 1234 123 asd 123 12 kghb 112 34 labels import Data Dictionary abcd 1234 defg 12 asd 123 kghb 112 attached

8. The Traditional versus the Database Approach to Data Management

9. Fig 5.3

10. Problems with the “Traditional” Approach • Data redundancy • Program-data dependence • Inflexibility

11. Fig 5.4

12. Data Modeling & Database Models

13. Data Design Issues • Content: What data should be collected? • Access: What data should be given to what users? • Logical structure: How will the data be organized to make sense to a particular user? • Physical organization: Where will the data actually be located?

14. Data Modeling • Logical design • Physical design • Planned data redundancy • Data model

15. Data Models • Hierarchical models • Network models • Relational models

16. Fig 5.6

17. Fig 5.7

18. Fig 5.8

19. Operations • Select rows • Project columns • Join tables

20. Fig 5.9

21. Object-Relational Database Management Systems • Stores objects • Hypertext • Hypermedia • Spatial data technology

22. Provide a user view Provide tools to create & modify the database Store & retrieve data Manipulate data Produce reports Database Management Systems (DBMSs)

23. Selecting a DBMS • Number of concurrent users • Database size • Performance • Integration • Features • Vendor • Cost

24. Database Developments • Data warehouses • Data marts • Data mining • Online analytical processing (OLAP)

25. What Is a Database? • Database: a collection of data • Table: the primary element for collecting data, organized into rows and columns • Record: an individual entry in a table (row) • Field: a piece of data in a record (column)

26. Access97: A Database Example Field (column) Table Record(row)

27. Access Database Objects • Table: Used to store and manipulate data. • Query: Used to retrieve information. • Form: Used to view one record of data. • Report: Used to present, calculate, summarize, and print table data. • Macro/Module: Used to automate frequently performed procedures.

28. The database window is the command center; it provides the means to create, view, and edit database objects, such as tables, forms, and reports. Controlicon Objecttabs Processing orCommand Buttons

29. What is the difference between a database and a spreadsheet?

30. Excel Spreadsheet Use Microsoft Excel to analyze financial and statistical data.

31. Access Database Use Microsoft Access to create, maintain, and summarize very large quantities of data.

32. Why do “databases” in spreadsheets? • Because we can • We use 1 or 2 massive tables • “Lots of tables make the database complex” • Discomfort with databases and multiple tables • Because we “think it’s simple” • Skip organizing the data into relational tables • Go straight to designing forms NAME POSITION SPOUSE CHILDREN PHONE Jones Chief Gloria, Karen 3274 Smith Clerk Betty 3241 Jones Chief Mary Glorai, Karen 3296

33. Data Redundancy Problems • Redundancy breeds errors • Same data defined in multiple places is BAD • Spelling/typographical error prone • Lack of data integrity • Inability to perform simple queries • Inflexibility and inscalability • Impossible to MAINTAIN!

34. Relational Database PROJECTCHIEF ProjectProject Chief Computing 333-22-1111 Intranet987-65-4321 Contracting 123-45-6789 Jordan 333-22-1111 EMPLOYEES LNameFName SSNDept Jones Mike 123-45-6789 M&B Smith Tony 987-65-4321 Math Lee Bruce 567-89-1234 Engrr Doodle Yankee 333-22-1111 M&B 1 DEPARTMENTS Dept Dept. DirectorRoom Engr 181-94-5676 B115 Math 987-65-4321 123 M&B 123-45-6789 147 1

35. Example Employee SSN L Name F Name Rank Spouse Children Office Phone# Home Phone# Office Room# Dept Dept. Chief EmpProj Project Name Employee SSN Function • Must knowall constraintson data • project name is unique • only one chief per project • employees can have more than one phone# • employees can have only one office • many employees can use the same office

36. Just a Reminder • A table is a single object within a database • A database can have other objects such as queries, forms, reports, macros, and programming modules • However, at least one table object must be created before you can create any other type of object

37. Database Design Templates • CHOOSE: File, New Database orCLICK: New Database button Design Templates

38. How Do I Create a Table? • Using Datasheet View (Usually used for entering data into records) • Using the Table Wizard (Usually used for creating tables) • Using Design View (Usually used for creating tables and editing fields) Sample Table in Datasheet View

39. Using the Table Wizard Pick fields from existing business and personal table structures

40. Using Design View Note: Fields are shown in rows in the design view, not in columns. Specify field names, data types, and indexes from scratch Define fieldsand data types Define fieldproperties

41. Text Memo Number Date/Time Currency Autonumber Yes/No OLE Object Hyperlink Lookup Wizard Field Data Types

42. Field Size Format Decimal Places Input Mask Caption Default Value Validation Rule Validation Text Required Allow Zero Length Indexed Field Properties

43. Primary Keys (Are extremely important in databases.) • Primary key is a field that uniquely identifies each record in a table • Data is automatically displayed in the datasheet sorted by the primary key. • There are 3 types of primary keys: AutoNumber, Single-Field, and MultiField. • You can use Access AutoNumber as a primary key (since it is unique) but it is not a good idea. • CHOOSE: Edit, Primary Key orCLICK: Primary Key button

44. Primary Keys • A primary key is one or more data fields (columns) that uniquely identify each record in the table • What would the primary key be below? • “table of employees, assigned to a department.” EMPLOYEES LNameFNameSSNDept Jones Mike 123-45-6789 Math Smith Tony 987-65-4321 M&B Lee Bruce 567-89-1234 Science

45. Indexes Indexes can slow down data entry. Access automatically creates indexes for fields with ‘ID’ in their names. • Like Primary Key, can be used to determine the order in which data is displayed • CLICK: Indexes button PrimaryKey IndexDefinition

46. Access Uses Automatic Saving • CLICK: a cell using the mouse pointer • Make changes to the cell’s information. • SELECT: another record to save the changes • CHOOSE: Edit, Undo from the menuif you want to reverse the most recent changes Note: This is a very important difference between Access and other Office 2000 products..

47. Customizing Datasheet View Remember: formatting changes are not saved automatically. Only structural and data changes are saved automatically.

48. Adjusting Row Heights • DRAG: the border between rows to change all row heights in the datasheet RowBorders

49. Reordering Fields • SELECT: the entire field column • DRAG: field name to move the column • To save the Datasheet view:CLICK: Save button

50. Sorting Records • Allows you to better organize and present data; makes information out of raw data (alphabetical phone listing) • SELECT: column or field to sortCLICK: Sort Ascending buttonCLICK: Sort Descending button