Chapter 1 The Database Environment and Development Process

1. Chapter 1The Database Environment and Development Process Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu

2. Importance of Information • Information (and energy) are at the core of everything around us. Our entire existence (including businesses) is a process of gathering, analyzing, understanding, and acting on the information. • Modern organizations are said to be drowningin data but _________ for information.

3. Objectives • Define terms • Name limitations of conventional file processing • Explain advantages of databases • Identify costs and risks of databases • List components of database environment • Identify categories of database applications • Describe database system development life cycle • Explain prototyping and agile development approaches • Explain roles of individuals • Explain the three-schema architecture for databases

4. 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 (Data _______ the data).

5. Meta-Data Data Business and Modeling Environment Examples: Model Invoice Core/Essence Debt Furniture Store Business Reality

6. Business and Modeling Environment Examples: Model Meta-Data Invoice Core/Essence Data Debt Furniture Store Business Reality

7. Types of Data Processing • Two types of data processing • ______-baseddata processing • e.g., applications developed by Java • ______-based data processing • e.g, applications developed by Oracle or MS/Access)

8. Duplicate Data Figure 1-2: Old file processing systems at Pine Valley Furniture What is the main problem in the company’s processing systems?

9. Disadvantages of File Processing • Program-Data ____________ • All programs maintain metadata for each file they use • Data ____________ (Duplication of data) • Different systems/programs have separate copies of the same data • ___________ Data Sharing • No centralized control of data • Lengthy Development Times • Programmers must design their own file formats • Excessive Program Maintenance • 80% of of information systems budget

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

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

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

13. Application #2 (Invoicing Sys) Application #1 (Order Filing) Application #3 (Payroll Sys.) Database containing centralized shared data Database Management System • A software system that is used to create, maintain, and provide controlled access to user databases ______ DBMS manages data resources like an operating system manages hardware resources

14. Database Management System • A DBMS is a data storage and retrieval system which permits data to be stored non-redundantly while making it appear to the user as if the data is well-integrated. • In short, a DBMS is a software package that manages a data base.

15. Advantages of Database Approach • Program-Data _____________ • Metadata stored in DBMS, so applications don’t need to worry about data formats (you will know this when you learn Oracle) • Data queries/updates managed by DBMS so programs don’t need to process data access routines • Results in: increased application development and maintenance productivity • Minimal Data Redundancy • Leads to increased data integrity/consistency

16. Advantages of Database Approach • Program-data independence • Planned data redundancy • Improved data consistency • Improved Data Sharing • Different users get different views of the data • Improved productivity of application development • Enforcement of Standards • All data access is done in the same way • Improved Data Quality • Constraints, data validation rules • Improved data accessibility and responsiveness • Use of standard data query language (SQL) • Reduced program maintenance • Improved decision support • Security, Backup/Recovery, Concurrency (not in Table 1-3) • Disaster recovery is easier

17. Costs and Risks of the Database Approach • New specialized personnel • 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 • Other hidden costs

18. Why Do We Still Learn File Processing Systems? • File processing systems are still widely used today, especially for backing up database systems. • Understanding the problems and limitations inherent in file processing systems can help us avoid these same problems when designing database.

19. Elements of the Database Approach • Data models • Graphical system capturing nature and relationship of data • Enterprise Data Model–high-level entities and relationships for the organization • Project Data Model–more detailed view, matching data structure in database or data warehouse • Entities • Noun form describing a person, place, object, event, or concept • Composed of attributes • Relationships • Between entities • Usually one-to-many (1:M) or many-to-many (M:N) • Relational Databases • Database technology involving tables (relations) representing entities and primary/foreign keys representing relationships

20. A Data Model on Customer and Order Segment of an Enterprise Data Model CUSTOMER ORDER M Q1. One CUSTOMER normally places ___ ORDER? 1 Q2. One ORDER normally is placed by __ CUSTOMER? How about the relationship between ORDER and PRODUCT? (see next slide)

21. A Data Model on Customer and Order Segment of an Enterprise Data Model CUSTOMER ORDER M Q1. One CUSTOMER normally places ___ ORDER? 1 Q2. One ORDER normally is placed by __ CUSTOMER? How about the relationship between ORDER and PRODUCT? (see next slide)

22. Figure 1-3: Comparison of enterprise and project level data models ORDER ORDER_NUMBER Customer_ID Order_Date Q3. One PRODUCT normally is contained in __ ORDER? Q4. One ORDER normally contains __ PRODUCT?

23. Figure 1-3: Comparison of enterprise and project level data models ORDER ORDER_NUMBER Customer_ID Order_Date Q3. One PRODUCT normally is contained in __ ORDER? Q4. One ORDER normally contains __ PRODUCT?

24. (b) Segment of a Project-Level Data Model Figure 1-3: Comparison of enterprise and project level data models (a) Segment of an Enterprise Data Model Q: what are two major differences between (a) & (b)?

25. Figure 1-3 Segment from Enterprise Data Model Figure 3 Enterprise data model is a graphical model that shows the high-level entities for the organization and the relationship among these entities. (E/R Diagram)

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

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

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

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

30. Figure 1-4 Enterprise data model for Figure 1-3 segments

31. Figure 1-5 Components of the Database Environment

32. Components of the Database Environment • CASE Tools–computer-aided software engineering • Repository–centralized storehouse of metadata • Database Management System (DBMS) –software for managing the database • Database–storehouse of the data • Application Programs–software using the data • User Interface–text and graphical displays to users • Data/Database Administrators–personnel responsible for maintaining the database • System Developers–personnel responsible for designing databases and software • End Users–people who use the applications and databases

33. Evolution of Database Technologies • 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

34. Figure 1-10a Evolution of Database Technologies

35. Figure 1-10b Database architecture

36. Figure 1-10b Database architecture

37. The Range of Database Applications • Personal databases • Two-tier Client/Server databases • Multitier/N Client/Server (or called web-enabled) databases • Enterprise applications • Enterprise resource planning (ERP) systems • Data warehousing implementations (Local Area Network) (Web-enabled Database ) (WAN) (Wide Area Network)

38. Figure 1-11 Two-tier database with local area network Chapter 1

39. Figure 1-12 Three-tiered client/server database architecture Chapter 1

40. Muti/N-tier: Web-Enabled Databases Web applications requiring databases Customer relationship management (CRM) Business-to-consumer (B2C) Electronic data interchange (EDI) Private intranets XML-defined Web services 40

41. Enterprise Applications • Enterprise Resource Planning (ERP) • Integrate all enterprise functions (manufacturing, finance, sales, marketing, inventory, accounting, human resources) • Data Warehousing implementation • Integrated decision support system derived from various operational databases

42. Break ! (Ch. 1)Exercise #1 (p.44) Homework:1. Complete chapter1 quiz by Sunday evening.2. HW#12 (a) only (homework assignment - high-level (no attributes) - draw by Visio clearly - turn in a hardcopy next class

43. Be Prepared for ...

44. Enterprise Data Model Enterprise data model is a graphical model that shows the ____-level entities for the organization and the relationship among these entities. (E/R Diagram) • Enterprise data modeling is the first step in database development, in which the scope and general contents of organizational databases are specified. • Descriptions of entity types • Relationships between entities • Business rules Q: Is ER/M a top-down or bottom-up approach?

45. Steps in the Database Development Process • Enterprise Modeling • _________ Data Modeling • Cuts across Project Initiation and Planning & Analysis phases of SDLC • _______ Database Design (E/R) • _______ Database Design and Creation • Database Implementation • Database Maintenance

46. Database Analysis Database Design Database Implementation Operations and Maintenance _________ and _______ Life Cycle Phases of DA and DBA (Ch.11) Database Planning

47. Two Approaches to Database and IS Development • SDLC • System Development Life Cycle • Detailed, well-planned (and structured) development process • Time-consuming, but comprehensive • Long development cycle • Prototyping • Rapid application development (RAD) • Cursory attempt at conceptual data modeling • Define database during development of initial prototype • Repeat implementation and maintenance activities with new prototype versions • Tool: Oracle Designer

48. Prototyping • Prototpying is one of the most popular rapid application development (RAD) methods. • It is an iterative process of system development in which requirements are converted to a working system that is continually revised through close work between analysts and users. A prototype is a small, but working system that contains only those important (not complete) features.

49. Project Identification and Selection Project Initiation and Planning Analysis Logical Design Physical Design Implementation Maintenance Systems Development Life Cycle (see also Figure 1-7)

50. Systems Development Life Cycle (see also Figure 1.7) Project Identification and Selection Purpose --preliminary understanding Deliverable –request for project Project Initiation and Planning Analysis Logical Design Physical Design Database activity – enterprise modeling Implementation Maintenance