IST 210 Database Design Process IST 210 Todd

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

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 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 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 E-R Modeling is Iterative 14

IST 210 Concept Design: Tools and Sources 15

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 Iterative Process of Verification 18

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