Database Management: Getting Data Together

1. Database Management: Getting Data Together Chapter 14

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

3. Contents • Computer Databases • Database Management Systems • The DBMS Process • Concurrency Control • Security • Backup and Recovery • Looking at the Data

4. Computer Databases • Track information • Keep consistent standards • Allow higher productivity as a result of better information

5. Hierarchy of Data • Field • Smallest meaningful unit of data • Group of one or more characters that has a specific meaning • 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

6. Hierarchy of Data

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

23. The DBMS Process The Plan… The Report…

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

25. Concurrency Control

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

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

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

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

30. 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 unrecognized • Patterns • Relationships • Correlations • Trends • Helps managers make strategic business decisions