1 / 60

Databases

Databases. Chapter 5. Objectives. Understanding the strategic advantage of using Databases? How does it support operations? How does it support management? What are the “best practices” today?. Concepts. Major types of databases. Data warehouses and data mining. Logical data elements.

karif
Download Presentation

Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

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

  32. Relational Database

  33. M : N relationship

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

  35. Instructors Compensation One to One PK

  36. Instructors Requires FOREIGN KEY Sections One to Many

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

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

  39. Instructors Courses Section – linking Table FK FK

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

  41. 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)

  42. Fig 5.2

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

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

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

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

More Related