IST 210 Database Design Process IST 210 Todd

  • Slides: 30
Download presentation
IST 210 Database Design Process IST 210 Todd S. Bacastow January 2005

IST 210 Database Design Process IST 210 Todd S. Bacastow January 2005

IST 210 n n Key points Database design must reflect the information system of

IST 210 n n Key points Database design must reflect the information system of which the database is a part Information systems undergo evaluation and revision within a framework known as the Systems Development Life Cycle (SDLC) n n Databases also undergo evaluation and revision within a framework known as the Database Life Cycle (DBLC) There are two general design strategies exist: n n top-down vs. bottom-up design centralized vs. decentralized design 2

IST 210 The Information System n Database n n n Carefully designed and constructed

IST 210 The Information System n Database n n n Carefully designed and constructed repository of facts Part of an information system Information System n n n Provides data collection, storage, and retrieval Facilitates data transformation Includes people, hardware, and software 3

IST 210 The Information System (Con’t. ) n System Analysis n Establishes need and

IST 210 The Information System (Con’t. ) n System Analysis n Establishes need and extent of an information system n n n We are NOT DOING A SYSTEM REQ’T ANALYSIS!! Systems development n n Refer to Recommended Requirements Gathering Practices Process of creating information system Database development n Process of database design and implementation n n Creation of database models Implementation n Creating storage structure Loading data into database Providing for data management 4

IST 210 Systems Development Life Cycle System Analysis Database Lifecycle (DBLC) 5

IST 210 Systems Development Life Cycle System Analysis Database Lifecycle (DBLC) 5

IST 210 Database Lifecycle (DBLC) Phase 1 Phase 2 Phase 3 Phase 4 Database

IST 210 Database Lifecycle (DBLC) Phase 1 Phase 2 Phase 3 Phase 4 Database Organization (IST 210) Phase 5 Phase 6 6

Phase 1: Database Initial Study IST 210 n Purposes n Analyze company situation n

Phase 1: Database Initial Study IST 210 n Purposes n Analyze company situation n n Operating environment Organizational structure Define problems and constraints Define objectives Define scope and boundaries Phase 1 Phase 2 Phase 3 Phase 4 Phase 5 Phase 6 7

IST 210 Initial Study Activities 8

IST 210 Initial Study Activities 8

Phase 2: Database Design IST 210 n n Most Critical DBLC phase Makes sure

Phase 2: Database Design IST 210 n n Most Critical DBLC phase Makes sure final product meets requirements Focus on data requirements Subphases n n I. Create conceptual design II. DBMS software selection III. Create logical design IV. Create physical design Phase 1 Phase 2 Phase 3 Phase 4 Phase 5 Phase 6 9

IST 210 Two Views of Data 10

IST 210 Two Views of Data 10

IST 210 I. Conceptual Design n Data modeling creates abstract data structure to represent

IST 210 I. Conceptual Design n Data modeling creates abstract data structure to represent real-world items High level of abstraction Four steps n n n Data analysis and requirements *Entity relationship modeling and normalization* *Data model verification* 11

IST 210 Data analysis and Requirements n Focus on: n n Data sources n

IST 210 Data analysis and Requirements n Focus on: n n Data sources n n Information needs Information users Information sources Developing and gathering end-user data views Direct observation of current system Interfacing with systems design group Business rules 12

IST 210 Entity Relationship Modeling and Normalization 13

IST 210 Entity Relationship Modeling and Normalization 13

IST 210 E-R Modeling is Iterative 14

IST 210 E-R Modeling is Iterative 14

IST 210 Concept Design: Tools and Sources 15

IST 210 Concept Design: Tools and Sources 15

IST 210 n Data Model Verification E-R model is verified against proposed system processes

IST 210 n Data Model Verification E-R model is verified against proposed system processes n n End user views and required transactions Access paths, security, concurrency control Business-imposed data requirements and constraints Reveals additional entity and attribute details 16

IST 210 E-R Model Verification Process 17

IST 210 E-R Model Verification Process 17

IST 210 Iterative Process of Verification 18

IST 210 Iterative Process of Verification 18

II. DBMS Software Selection IST 210 n n n DBMS software selection is critical

II. DBMS Software Selection IST 210 n n n DBMS software selection is critical Advantages and disadvantages need study Factors affecting purchasing decision n n Cost DBMS features and tools Underlying model Portability DBMS hardware requirements 19

IST 210 III. Logical Design n Translates conceptual design into internal model Maps objects

IST 210 III. Logical Design n Translates conceptual design into internal model Maps objects in model to specific DBMS constructs Design components n n n Tables Indexes Views Transactions Access authorities Others 20

IST 210 n IV. Physical Design Selection of data storage and access characteristics n

IST 210 n IV. Physical Design Selection of data storage and access characteristics n n Very technical More important in older hierarchical and network models Becomes more complex for distributed systems Designers favor software that hides physical details 21

IST 210 Phase 3: Implementation and Loading n n n Creation of special storage-related

IST 210 Phase 3: Implementation and Loading n n n Creation of special storage-related constructs to house end-user tables Data loaded into tables Other issues Phase 1 n n n Performance Security Backup and recovery Integrity Company standards Concurrency controls Phase 2 Phase 3 Phase 4 Phase 5 Phase 6 22

Phase 4: Testing and Evaluation IST 210 n Database is tested and fine-tuned for

Phase 4: Testing and Evaluation IST 210 n 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 Phase 1 n Phase 2 Phase 3 n Phase 4 Phase 5 n n Fine-tuning based on reference manuals Phase 6 Modification of physical design Modification of logical design Upgrade or change DBMS software or hardware 23

IST 210 n n Phase 5: Operation Database considered operational Starts process of system

IST 210 n n Phase 5: Operation Database considered operational Starts process of system evaluation Unforeseen problems may surface Demand for change is constant Phase 1 Phase 2 Phase 3 Phase 4 Phase 5 Phase 6 24

IST 210 n n n n Phase 6: Maintenance and Evaluation Preventative maintenance Corrective

IST 210 n n n n 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 system-generated statistics Periodic system usage-summaries Phase 1 Phase 2 Phase 3 Phase 4 Phase 5 Phase 6 25

IST 210 DB Design Strategy Notes n Top-down n 1) Identify data sets 2)

IST 210 DB Design Strategy Notes n Top-down n 1) Identify data sets 2) Define data elements Bottom-up n n 1) Identify data elements 2) Group them into data sets 26

IST 210 Top-Down vs. Bottom-Up 27

IST 210 Top-Down vs. Bottom-Up 27

IST 210 Centralized vs. Decentralized Design n Centralized design n Typical of simple databases

IST 210 Centralized vs. Decentralized Design n Centralized design n Typical of simple databases Conducted by single person or small team Decentralized design n Larger numbers of entities and complex relations Spread across multiple sites Developed by teams 28

IST 210 Decentralized Design 29

IST 210 Decentralized Design 29

IST 210 n n Summary Database design must reflect the information system of which

IST 210 n n Summary Database design must reflect the information system of which the database is a part Information systems undergo evaluation and revision within a framework known as the Systems Development Life Cycle (SDLC) n n Databases also undergo evaluation and revision within a framework known as the Database Life Cycle (DBLC) There are two general design strategies exist: n n top-down vs. bottom-up design centralized vs. decentralized design 30