1. Databases Chapter 5

2. Objectives • Understanding the strategic advantage of using Databases? • How does it support operations? • How does it support management? • What are the “best practices” today?

3. Concepts • Major types of databases. • Data warehouses and data mining. • Logical data elements. • Fundamental database structures. • Database development.

4. Databases are Important and Exciting • Q: Why are databases so important? • A: Today’s business enterprises cannot survive or succeed without quality data about their internal operations and external environment. • Q: Why are database so exciting? • A: This question makes no sense; its like asking why are Brad Pitt and Angelina Jolie so hot?

5. Data Resource Management Definition: • A managerial activity that applies information systems technologies to the task of managing an organization’s data resources to meet the information needs of their business stakeholders. • Deep down inside, you all want to be data resource managers; you really do; trust me!

6. Foundation Data Concepts • Character – single alphabetic, numeric or other symbol • Field – group of related characters • Entity – person, place, object or event • Attribute – characteristic of an entity

7. Foundation Data Concepts • Record – collection of attributes that describe an entity • File – group of related records • Database – integrated collection of logically related data elements

8. Logical Data Elements

9. Entities and Relationships

10. Entities and Relationships • Entities • Real world things with attributes • Abstract things that can be recorded • A Bill, A Person, A Building, A Product, A Fishmonger, A Recipe • Relationships • How are the entities related • How are entities connected logically

11. Relationships • ParenthasChild • Customer makes An Order • Student takes A Class • Fishmonger works at A Dock • Recipe includes An Ingredient • Brad Pitt dates Angelina Jolie

12. Types of Databases

13. Types of Databases • Operational – store detailed data needed to support the business processes and operations of a company • Distributed – databases that are replicated and distributed in whole or in part to network servers at a variety of sites • External – contain a wealth of information available from commercial online services and from many sources on the World Wide Web • Hypermedia – consist of hyperlinked pages of multimedia

14. Data Warehouse Definition: • Large database that stores data that have been extracted from the various operational, external, and other databases of an organization

15. Data Warehouse System

16. Data Mart Definition: • Databases that hold subsets of data from a data warehouse that focus on specific aspects of a company, such as a department or a business process

17. Data Warehouse & Data Marts

18. Data Mining Definition: • Analyzing the data in a data warehouse to reveal hidden patterns and trends in historical business activity

19. Data Mining

20. Data Mining Uses • Perform “market-basket analysis” to identify new product bundles. • Find root causes to quality or manufacturing problems. • Prevent customer attrition and acquire new customers. • Cross-sell to existing customers. • Profile customers with more accuracy.

21. Traditional File Processing Definition: • Data are organized, stored, and processed in independent files of data records

22. Flat file BUS100,ACCT 1,SMITH J.,M 10-11 BUS100,ACCT 1,JONES A., T 11-12 BUS200,ACCT 2,SMITH J., T 1-2 BUS200,ACCT 2,ANDERSON B.,T 3-4 CSI120,PROG 1,WILLIAMS C.,W 4-6

23. Problems with “flat files” • Concurrent access update problems • Serialization • Searching for a record – sequential • Inserting a record • Update/change size of record • Deleting a record

24. Problems of File Processing • Data Redundancy – duplicate data requires an update to be made to all files storing that data • Lack of Data Integration – data stored in separate files require special programs for output making ad hoc reporting difficult • Data Dependence – programs must include information about how the data is stored so a change in storage format requires a change in programs

25. Database Management Approach Definition: • Consolidates data records into one database that can be accessed by many different application programs. • Software interface between users and databases • Data definition is stored once, separately from application programs

26. Database Management Approach

27. Database Management Software (DBMS) Definition: • Software that controls the creation, maintenance, and use of databases

28. Database Interrogation Definition: • Capability of a DBMS to report information from the database in response to end users’ requests • Query Language – allows easy, immediate access to ad hoc data requests • Report Generator - allows quick, easy specification of a report format for information users have requested

29. Natural Language vs. SQL Queries

30. Database Structures • Hierarchical – relationships between records form a hierarchy or treelike structure • Network – data can be accessed by one of several paths because any data element or record can be related to any number of other data elements

32. Relational Database Structure Definition: • All data elements within the database are viewed as being stored in the form of simple tables

34. Relational Database

35. M : N relationship

36. Modeling Relationshipsusing Tables 1:1 1:Many (1:N) Many : Many (N:M)

37. Instructors Compensation One to One PK

38. Instructors Requires FOREIGN KEY Sections One to Many

39. Many-to-many Instructor/Courses No-No’s: PK violation

40. Many-to-many Instructor/CoursesNo-No’s: Multiple entry fields

41. Instructors Courses Section – linking Table FK FK

42. Searching a file • UNSORTED file: • Sequential search • On average, must read N/2 records • Very inefficient

43. Search Keys • Primary keys – unique (SSN) or SID ! • ORDERS the Table and is always indexed • Must be unique • Indexed allows fast search • Secondary keys – together make the record unique (NAME, BIRTHDAY)

44. Fig 5.2

45. Search a sorted file(using direct access) • Can use Binary Search for a record • Skip to “Middle” of file • Compare search key with record • If smaller – record in first half of file • If larger – record in second half of file • Continue until record found or no more records to search • Efficient – about Log base 2(N) probes

46. Indexing • Adding additional navigation information • Help locate records quickly • Primary Index: • Contains KEY field and BLOCK number • Only KEY of first record in BLOCK • Do binary search on (smaller) Index file

48. Secondary index • Need way to also quickly find records by an attribute that the file is not stored by. (non-primary index) • EX: file stored by customer number, need to reference by name as well.

50. Database approach:advantages • Centralized repository • Program/db independence • Can form new data relationships • Reduce redundancy • Data integrity – transaction rollback • Data recovery – log files