Скачать презентацию 6 Chapter 6 Database Design Database Systems Design Скачать презентацию 6 Chapter 6 Database Design Database Systems Design

25d3a23b2376ac5cf731882b5b450bf6.ppt

  • Количество слайдов: 32

6 Chapter 6 Database Design Database Systems: Design, Implementation, and Management, Fifth Edition, Rob 6 Chapter 6 Database Design Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel

6 In this chapter, you will learn: • That successful database design must reflect 6 In this chapter, you will learn: • That successful database design must reflect the information system of which the database is a part • That successful information systems are subject to frequent evaluation and revision within a framework known as the Systems Development Life Cycle (SDLC) • That, within the information system, the most successful databases are subject to frequent evaluation and revision within a framework known as the Database Life Cycle (DBLC) • How to conduct evaluation and revision within the SDLC and DBLC frameworks • What database design strategies exist: top-down vs. bottom-up design and centralized vs. decentralized design Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 2

6 Changing Data into Information • Data – Raw facts stored in databases – 6 Changing Data into Information • Data – Raw facts stored in databases – Need additional processing to become useful • Information – Required by decision maker – Data processed and presented in a meaningful form – Transformation Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 3

6 The Information System • Database – Carefully designed and constructed repository of facts 6 The Information System • Database – Carefully designed and constructed repository of facts – Part of an information system • Information System – Provides data collection, storage, and retrieval – Facilitates data transformation – Components include: • People • Hardware • Software –Database(s) –Application programs –Procedures Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 4

6 The Information System (Con’t. ) • System Analysis – Establishes need and extent 6 The Information System (Con’t. ) • System Analysis – Establishes need and extent of an information system • Systems development – Process of creating information system • Database development – Process of database design and implementation – Creation of database models – Implementation • Creating storage structure • Loading data into database • Providing for data management Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 5

6 Systems Development Life Cycle Figure 6. 2 Database Systems: Design, Implementation, & Management, 6 Systems Development Life Cycle Figure 6. 2 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 6

6 Database Lifecycle (DBLC) Figure 6. 3 Database Systems: Design, Implementation, & Management, 5 6 Database Lifecycle (DBLC) Figure 6. 3 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 7

6 Phase 1: Database Initial Study • Purposes – Analyze company situation • Operating 6 Phase 1: Database Initial Study • Purposes – Analyze company situation • Operating environment • Organizational structure – Define problems and constraints – Define objectives – Define scope and boundaries Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 8

6 Initial Study Activities Figure 6. 4 Database Systems: Design, Implementation, & Management, 5 6 Initial Study Activities Figure 6. 4 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 9

6 Phase 2: Database Design • • Most Critical DBLC phase Makes sure final 6 Phase 2: Database Design • • Most Critical DBLC phase Makes sure final product meets requirements Focus on data requirements Subphases – – Create conceptual design DBMS software selection Create logical design Create physical design Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 10

6 Two Views of Data Figure 6. 5 Database Systems: Design, Implementation, & Management, 6 Two Views of Data Figure 6. 5 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 11

6 I. Conceptual Design • Data modeling creates abstract data structure to represent real-world 6 I. Conceptual Design • Data modeling creates abstract data structure to represent real-world items • High level of abstraction • Four steps – – Data analysis and requirements Entity relationship modeling and normalization Data model verification Distributed database design Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 12

6 Data analysis and Requirements • Focus on: – – Information needs Information users 6 Data analysis and Requirements • Focus on: – – Information needs Information users Information sources Information constitution • Data sources – Developing and gathering end-user data views – Direct observation of current system – Interfacing with systems design group • Business rules Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 13

6 Entity Relationship Modeling and Normalization Table 6. 2 Database Systems: Design, Implementation, & 6 Entity Relationship Modeling and Normalization Table 6. 2 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 14

6 E-R Modeling is Iterative Figure 6. 8 Database Systems: Design, Implementation, & Management, 6 E-R Modeling is Iterative Figure 6. 8 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 15

6 Concept Design: Tools and Sources Figure 6. 9 Database Systems: Design, Implementation, & 6 Concept Design: Tools and Sources Figure 6. 9 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 16

6 Data Model Verification • E-R model is verified against proposed system processes – 6 Data Model Verification • E-R model is verified against proposed system processes – End user views and required transactions – Access paths, security, concurrency control – Business-imposed data requirements and constraints • Reveals additional entity and attribute details • Define major components as modules – Cohesivity – Coupling Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 17

6 E-R Model Verification Process Table 6. 4 Database Systems: Design, Implementation, & Management, 6 E-R Model Verification Process Table 6. 4 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 18

6 Iterative Process of Verification Figure 6. 10 Database Systems: Design, Implementation, & Management, 6 Iterative Process of Verification Figure 6. 10 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 19

6 Distributed Database Design • Design portions in different physical locations • Development of 6 Distributed Database Design • Design portions in different physical locations • Development of data distribution and allocation strategies Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 20

6 II. DBMS Software Selection • DBMS software selection is critical • Advantages and 6 II. DBMS Software Selection • DBMS software selection is critical • Advantages and disadvantages need study • Factors affecting purchasing decision – – – Cost DBMS features and tools Underlying model Portability DBMS hardware requirements Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 21

6 III. Logical Design • Translates conceptual design into internal model • Maps objects 6 III. Logical Design • Translates conceptual design into internal model • Maps objects in model to specific DBMS constructs • Design components – – – Tables Indexes Views Transactions Access authorities Others Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 22

6 IV. Physical Design • Selection of data storage and access characteristics – Very 6 IV. Physical Design • Selection of data storage and access characteristics – Very technical – More important in older hierarchical and network models • Becomes more complex for distributed systems • Designers favor software that hides physical details Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 23

6 Physical Organization Figure 6. 12 Database Systems: Design, Implementation, & Management, 5 th 6 Physical Organization Figure 6. 12 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 24

6 Phase 3: Implementation and Loading • Creation of special storage-related constructs to house 6 Phase 3: Implementation and Loading • Creation of special storage-related constructs to house end-user tables • Data loaded into tables • Other issues – – – Performance Security Backup and recovery Integrity Company standards Concurrency controls Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 25

6 Phase 4: Testing and Evaluation • Database is tested and fine-tuned for performance, 6 Phase 4: Testing and Evaluation • Database is tested and fine-tuned for performance, integrity, concurrent access, and security constraints • Done in parallel with application programming • Actions taken if tests fail – – Fine-tuning based on reference manuals Modification of physical design Modification of logical design Upgrade or change DBMS software or hardware Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 26

6 Phase 5: Operation • • Database considered operational Starts process of system evaluation 6 Phase 5: Operation • • Database considered operational Starts process of system evaluation Unforeseen problems may surface Demand for change is constant Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 27

6 Phase 6: Maintenance and Evaluation • • • Preventative maintenance Corrective maintenance Adaptive 6 Phase 6: Maintenance and Evaluation • • • Preventative maintenance Corrective maintenance Adaptive maintenance Assignment of access permissions Generation of database access statistics to monitor performance • Periodic security audits based on systemgenerated statistics • Periodic system usage-summaries Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 28

6 DB Design Strategy Notes • Top-down – 1) Identify data sets – 2) 6 DB Design Strategy Notes • Top-down – 1) Identify data sets – 2) Define data elements • Bottom-up – 1) Identify data elements – 2) Group them into data sets Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 29

6 Top-Down vs. Bottom-Up Figure 6. 14 Database Systems: Design, Implementation, & Management, 5 6 Top-Down vs. Bottom-Up Figure 6. 14 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 30

6 Centralized vs. Decentralized Design • Centralized design – Typical of simple databases – 6 Centralized vs. Decentralized Design • Centralized design – Typical of simple databases – Conducted by single person or small team • Decentralized design – Larger numbers of entities and complex relations – Spread across multiple sites – Developed by teams Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 31

6 Decentralized Design Figure 6. 16 Database Systems: Design, Implementation, & Management, 5 th 6 Decentralized Design Figure 6. 16 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 32