IST 210 Database Design Process IST 210 Section

IST 210 Database Design Process IST 210, Section 1 Todd S. Bacastow January 2004

Welcome to Your New Home! IST 210 2

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

IST 210 Changing Data into Information n Data n n n Raw facts stored in databases Need additional processing to become useful Information n Required by decision maker Data processed and presented in a meaningful form Transformation 4

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 n Software: Database(s), Application programs, and Procedures 5

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 n n Process of database design and implementation Creation of database models Implementation n Creating storage structure Loading data into database Providing for data management 6

IST 210 Systems Development Life Cycle System Analysis Database Organization (IST 210) 7

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

IST 210 Phase 1: Database Initial Study n Purposes n Analyze company situation n n Operating environment Organizational structure Define problems and constraints Define objectives Define scope and boundaries 9

IST 210 Initial Study Activities 10

IST 210 Phase 2: Database Design 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 11

IST 210 Two Views of Data 12

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

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 14

IST 210 Entity Relationship Modeling and Normalization 15

IST 210 E-R Modeling is Iterative 16

IST 210 Concept Design: Tools and Sources 17

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 18

IST 210 E-R Model Verification Process 19

IST 210 Iterative Process of Verification 20

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 21

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 22

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 23

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 n n n Performance Security Backup and recovery Integrity Company standards Concurrency controls 24

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

IST 210 Phase 5: Operation n n Database considered operational Starts process of system evaluation Unforeseen problems may surface Demand for change is constant 26

IST 210 Phase 6: Maintenance and Evaluation n n n 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 27

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 28

IST 210 Top-Down vs. Bottom-Up 29

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 30

IST 210 Decentralized Design 31