THE DATABASE ENVIRONMENT

1. THE DATABASE ENVIRONMENT • Definitions: Data, Information, Database, Metadata • File Processing Systems • The Database Approach • Components of the Database Environment • Evolution of Database Systems

2. Monthly Sales Report for West Region 1200 100 West Charles Mann 79154 TM Shoes Sales Rep: Charles Mann Emp No. 79154 ItemQty SoldPrice TM Shoes 1200 $100 Data versus Information

4. Some Definitions • Data: Meaningful facts, text, graphics, images, sound, video segments. • Database: An organized collection of logically related data. • Information: Data processed to be useful in decision making. • Metadata: Data that describes data.

6. Metadata for Class Roster Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and documentation

7. Three file processing systems at Pine Valley Furniture

8. Disadvantages of File Processing Systems • Program-Data Dependence • data descriptions are included with the application programs that use the data • Data Redundancy (Duplication of data) • Limited Data Sharing • Lengthy Development Times • Excessive Program Maintenance

9. SOLUTION: The DATABASE Approach • Central repository of shared data • Data is managed by a controlling agent • Stored in a standardized, convenient form Requires a Database Management System (DBMS)

10. Application #1 Application #2 Application #3 DBMS Database containing centralized shared data Database Management System DBMS manages data resources like an operating system manages hardware resources

11. An enterprise data model is a graphical model that shows the high-level entities for the organization and the relationship among those entities An entity is an object or concept that is important to the business ERD Relational Databases ORDER (O_ID, C_ID, O_Date) ORDER_LINE (O_ID, P_ID, QTY_Ordered) A database application is used to perform a series of database activities (create, read, update, delete) The range of Database Applications Advantages The Database Approachemphasizes the integration and sharing of data thru the organization

12. Segment from enterprise data model

13. One customer may place many orders, but each order is placed by a single customer  One-to-many relationship Segment from enterprise data model

14. One order has many order lines; each order line is associated with a single order  One-to-many relationship Segment from enterprise data model

15. One product can be in many order lines, each order line refers to a single product  One-to-many relationship Segment from enterprise data model

16. Therefore, one order involves many products and one product is involved in many orders  Many-to-many relationship Segment from enterprise data model

17. Order and Order_Line tables

18. Product and Customer tables

19. Figure 1-4 Order, Order_Line, Customer, and Product tables Relationships established in special columns that provide links between tables

20. Figure 1-5 Client/server system for Pine Valley Furniture Company

21. Advantages of the Database Approach • Data Independence/Reduced Maintenance • Data independence refers to the separation of data descriptions from the application programs that use the data • Improved Data Sharing • Increased Application Development Productivity • Enforcement of Standards • Improved Data Quality (Constraints) • Better Data Accessibility/ Responsiveness • Security, Backup/Recovery, Concurrency

22. The Range of Database Applications • Personal Databases • support one user • Workgroup Databases • a workgroup is a relatively small team of people who collaborate on the same project or application • Department Databases • support the various functions and activities of a department • Enterprise Databases • ERP work with the current operational data • Data warehouses • Internet Databases

23. Workgroup database

24. Figure 1-9 An enterprise data warehouse

25. Components of the Database Environment • CASE Tools • Repository - a centralized storehouse for all data definitions, data relationships, and other system components • Database Management System (DBMS) • Database - organized collection of logically related data • Application Programs • User Interface • Data Administrators • System Developers • End Users

26. Components ofthe Database Environment

27. Evolution of Database Technologies

28. Evolution of DB Systems • Flat files - 1960s - 1980s • Hierarchical – 1970s - 1990s • Network – 1970s - 1990s • Relational – 1980s - present • Object-oriented – 1990s - present • Object-relational – 1990s - present • Data warehousing – 1980s - present • Web-enabled – 1990s - present

29. Duplicate Data Figure 1-2 Three file processing systems at Pine Valley Furniture

30. Problems with Data Redundancy • Waste of space to have duplicate data • Causes more maintenance headaches • The biggest Problem: • When data changes in one file, could cause inconsistencies • Compromisesdata integrity

31. Problems with Data Dependency • Each application programmer must maintain their own data • Each application program needs to include code for the metadata of each file • Each application program must have its own processing routines for reading, inserting, updating and deleting data • Lack of coordination and central control • Non-standard file formats

32. Costs & Risks of the Database Approach • Up-front costs: • Installation Management Cost and Complexity • Conversion Costs • Ongoing Costs • Requires New, Specialized Personnel • Need for Explicit Backup and Recovery • Organizational Conflict • Old habits die hard