© 2003 prentice hall, inc.3-1 chapter 3 database management information systems today leonard...
TRANSCRIPT
© 2003 Prentice Hall, Inc. 3-1
Chapter 3
Database Management
Information Systems TodayInformation Systems TodayLeonard Jessup and Joseph ValacichLeonard Jessup and Joseph Valacich
© 2003 Prentice Hall, Inc. 3-2
© 2003 Prentice Hall, Inc. 3-3
Chapter 3 Objectives
Understand why databases are important to Understand why databases are important to modern organizationsmodern organizations
Understand how databases workUnderstand how databases work Understand how organizations can Understand how organizations can
maximize their strategic potential with maximize their strategic potential with databasesdatabases
© 2003 Prentice Hall, Inc. 3-4
Database Management for Strategic Advantage Database – a collection of related data Database – a collection of related data
organized in a way to facilitate data organized in a way to facilitate data searchessearches
Use databases to:Use databases to: Create a bookCreate a book Track book salesTrack book sales Set salaries and wagesSet salaries and wages Pay employeesPay employees
© 2003 Prentice Hall, Inc. 3-5
Database Management for Strategic Advantage The Database Approach: Foundational The Database Approach: Foundational
ConceptsConcepts DBMS – Database Management SystemsDBMS – Database Management Systems Use a DBMS software to create, store, Use a DBMS software to create, store,
organize, and retrieve data from a single organize, and retrieve data from a single database or several databasesdatabase or several databases
Example: Microsoft AccessExample: Microsoft Access
© 2003 Prentice Hall, Inc. 3-6
Database Management for Strategic Advantage Advantages of the Database Approach Advantages of the Database Approach
Program-data independenceProgram-data independence Minimal data redundancyMinimal data redundancy Improved data consistencyImproved data consistency Improved data sharingImproved data sharing Increased productivity of application developmentIncreased productivity of application development Enforcement of standardsEnforcement of standards Improved data qualityImproved data quality Improved data accessibilityImproved data accessibility Reduced program maintenanceReduced program maintenance
© 2003 Prentice Hall, Inc. 3-7
Database Management for Strategic Advantage Effective Management of DatabasesEffective Management of Databases
The database administrator (DBA) :The database administrator (DBA) :Works with programmers and analysts to Works with programmers and analysts to
design and implement the databasedesign and implement the databaseWorks with users and managers to establish Works with users and managers to establish
database policiesdatabase policiesImplements security features and establishes Implements security features and establishes
database permissionsdatabase permissions
© 2003 Prentice Hall, Inc. 3-8
Key Database Activities
Entering and Querying DataEntering and Querying Data FormForm Structured Query Language (SQL)Structured Query Language (SQL) Query by example (QBE)Query by example (QBE)
© 2003 Prentice Hall, Inc. 3-9
Key Database Activities
Creating Database ReportsCreating Database Reports Report – a compilation of data that is organized Report – a compilation of data that is organized
and produced in printed formatand produced in printed format Report GeneratorsReport Generators
© 2003 Prentice Hall, Inc. 3-10
Key Database Activities
Database DesignDatabase Design Must be organizedMust be organized Few or no redundanciesFew or no redundancies Data model – a map of entity relationshipsData model – a map of entity relationships KeysKeys
Primary keyPrimary key Combination primary keyCombination primary key Secondary keySecondary key
© 2003 Prentice Hall, Inc. 3-11
© 2003 Prentice Hall, Inc. 3-12
Key Database Activities
Database AssociationsDatabase Associations One-to-one (teams to stadiums)One-to-one (teams to stadiums) One-to-many (player to team)One-to-many (player to team) Many-to-many (players to games)Many-to-many (players to games)
© 2003 Prentice Hall, Inc. 3-13
Key Database Activities
Entity-Relationship Diagramming (ERD)Entity-Relationship Diagramming (ERD) Commonly used when designing databasesCommonly used when designing databases One draws entities (tables) as boxes and lines One draws entities (tables) as boxes and lines
between entities to show relationshipsbetween entities to show relationships
© 2003 Prentice Hall, Inc. 3-14
Key Database Activities
The Relational Model of DatabasesThe Relational Model of Databases Entities linked by a common key fieldEntities linked by a common key field
Records = rowsRecords = rowsFields = columnsFields = columns
Other models existOther models exist HierarchicalHierarchical NetworkNetwork Object-oriented modelObject-oriented model
© 2003 Prentice Hall, Inc. 3-15
Key Database Activities
NormalizationNormalization A technique for making complex databases A technique for making complex databases
more efficient and more easily handled by the more efficient and more easily handled by the DBMSDBMS
Eliminates data redundancyEliminates data redundancy
© 2003 Prentice Hall, Inc. 3-16
© 2003 Prentice Hall, Inc. 3-17
© 2003 Prentice Hall, Inc. 3-18
Key Database Activities Data DictionaryData Dictionary
A document that explains each piece of A document that explains each piece of information in the databaseinformation in the database
Field nameField name Data type Data type
• Numeric, text, date/timeNumeric, text, date/time• Useful for sorting and allocating storage Useful for sorting and allocating storage
Is this field a key field?Is this field a key field? Business rulesBusiness rules
• Update authorityUpdate authority• Valid data valuesValid data values
© 2003 Prentice Hall, Inc. 3-19
How Organizations Get theMost from Their Data
Linking Web Sites to DatabasesLinking Web Sites to Databases Example: Amazon Example: Amazon
2.5 million titles2.5 million titlesManaging online data effectivelyManaging online data effectively
© 2003 Prentice Hall, Inc. 3-20
How Organizations Get the Most from Their Data Data MiningData Mining
A method for better understanding dataA method for better understanding data Information on customers, products, markets, etc.Information on customers, products, markets, etc. Drill down: from summary to more detailed dataDrill down: from summary to more detailed data Sort and extract informationSort and extract information Trends, correlations, forecasting, statisticsTrends, correlations, forecasting, statistics
© 2003 Prentice Hall, Inc. 3-21
How Organizations Get the Most from Their Data Data MiningData Mining
Online Transaction Processing (OLTP)Online Transaction Processing (OLTP)Immediate automated responses to user Immediate automated responses to user
requestsrequestsMultiple concurrent transactionsMultiple concurrent transactionsA big part of interactive Internet e-commerceA big part of interactive Internet e-commerce
© 2003 Prentice Hall, Inc. 3-22
How Organizations Get the Most from Their Data Data MiningData Mining
Online Analytical Processing (OLAP)Online Analytical Processing (OLAP)Graphical software tools that provide complex Graphical software tools that provide complex
analysis of data stored in a databaseanalysis of data stored in a databaseDrills down to deeper levels of consolidationDrills down to deeper levels of consolidationTime series and trend analysisTime series and trend analysis““What if” and “why” questionsWhat if” and “why” questions
© 2003 Prentice Hall, Inc. 3-23
How Organizations Get the Most from Their Data Data MiningData Mining
Merging Transaction and Analytical ProcessingMerging Transaction and Analytical ProcessingReal-time OLAP diminishes performance Real-time OLAP diminishes performance
because the database must be “locked” during because the database must be “locked” during execution timeexecution time
Solution: replicate transactions on a 2Solution: replicate transactions on a 2ndnd database serverdatabase server
© 2003 Prentice Hall, Inc. 3-24
How Organizations Get the Most from Their Data Data MiningData Mining
Merging Transaction and Analytical ProcessingMerging Transaction and Analytical ProcessingOperational SystemsOperational Systems
• Interact with customers and run a business Interact with customers and run a business in real timein real time
• Examples: Order processing, reservation Examples: Order processing, reservation systemssystems
Informational SystemsInformational Systems• Support decision making based on stable Support decision making based on stable
point-in-time or historical datapoint-in-time or historical data
© 2003 Prentice Hall, Inc. 3-25
How Organizations Get the Most from Their Data Data WarehousingData Warehousing
Integrating multiple large databases into a Integrating multiple large databases into a single repositorysingle repository
Queries, analysis, and processingQueries, analysis, and processingPurpose: put key business information into Purpose: put key business information into
the hands of decision makersthe hands of decision makersCost: millionsCost: millions
© 2003 Prentice Hall, Inc. 3-26
How Organizations Get the Most from Their Data Data MartsData Marts
Instead of one large data warehouse, many Instead of one large data warehouse, many organizations create multiple data martsorganizations create multiple data marts
Each contains a subset of the dataEach contains a subset of the dataExample: finance, inventory, personnelExample: finance, inventory, personnelEach data mart is customized for particular Each data mart is customized for particular
DSS applicationsDSS applicationsCost: typically less than $1 millionCost: typically less than $1 million