Database Systems: Design, Implementation, and Management

1. Database Systems: Design, Implementation, and Management CHAPTER 6 Database Design

2. The Systems Development Life Cycle • The Systems Development Life Cycle (SDLC) provides a methodology for developing an IS. • Database design takes place within the confines of an IS. • Five phases of SDLC: • Planning • Analysis • Design • Implementation • Maintenance • SDLC is an iterative process

3. SDLC • Enterprise-wide requirement assessment • Identification of IS projects • Feasibility assessment and prioritization Planning • User requirement analysis for a specific project • Requirement modeling (conceptual) Analysis • Detailed design • Specification development Design • Coding, testing and evaluation • Installation Implementation • Daily operation and maintenance • Enhancements Maintenance

4. Analyze company situation • Define problem • Define objectives • Define scope and boundaries Database Initial study • Conceptual design • DBMS software selection, if required • Logical design • Physical design Database Design • Install DBMS, if new • Create databases • Load data Implementation and loading • Test the database • Evaluate performance and fine-tune Testing and evaluation • Daily operation and maintenance • Enhancements Operation and maintenance Database Life Cycle This is also an iterative process like SDLC

5. Database Design • Divided into four tasks • Conceptual design • DBMS software selection (if required) • Logical design • Physical design • Conceptual design is independent of software and hardware • Logical design is DBMS (software) dependent • Physical design is dependent on both software and hardware

6. Conceptual Design • The goal is to capture and model user requirements • Four Steps: • Data analysis and requirements • Entity relationship modeling and normalization • Data model verification • Distributed database design

7. Conceptual Design • Data analysis and requirements • The focus is on identifying user requirements • This can be gathered through various mean • observing and analyzing the current system • user interviews • questionnaire surveys • Capture and document user data views and business rules. • User data views describe the data used by the user • Example • Business rules describe policies and procedures followed by the company • Example: (EZS) • An item may be procured from many vendors • Purchase price of an item is negotiated with each supplier.

8. Conceptual Design • ER Modeling and Normalization • User requirements are modeled using E-R diagrams • Identify main entities based on user requirements data • Define relationships between the entities • Define attributes, primary keys, and foreign keys for each of the entities. • Normalize the entities. • Complete the initial E-R diagram. • Verify the E-R model against the data, information, and processing requirements. • Modify the E-R diagram, if necessary • Documentation process must be standardized to avoid miscommunication

9. Conceptual Design • Data model verification • Ensure that user data views can be supported by the data model • All business transactions (select, insert, update, delete, user queries) can be supported by the model • Distributed database design • Data requirements and processing requirements may vary from one location to another • Decision may be made about allocating data to different locations

10. DBMS Selection • This step is required only if you plan to acquire a new DBMS • Common factors affecting the decision: • Cost -- Purchase, maintenance, operational, license, installation, training, and conversion costs. • DBMS features and tools. • Underlying model. • Portability -- Platforms, systems, and languages. • DBMS hardware requirements.

11. Logical Design • Logical design translates the conceptual design into the internal model for a selected DBMS. • It includes the design of tables, indexes, views, transactions • Access authorities (who can access what) are also decided. • The ER model is translated into relational schema

12. Logical Design • Translating ER Model into Relational Schema • After normalizing the E-R diagram we are left with only two types of relationships • One-to-one • One-to-Many • For every one-to-one relationship, reexamine the possibility of merging the two entities into a single entity by combining their attributes. • Entities participating in a one-to-one relationship are linked through a foreign key. • Supertype-subtype relationships are usually implemented as one-to-one relationships. Both entities share a common primary key, which also becomes a foreign key in the subtype entity.

13. Logical Design 1 1 Employee Driver May be a (0,1) (1,1) Primary and Foreign Key Employee Driver 1 1 Emp_Id License Nbr Lic Exprn. Date Emp_Id Emp_Name Emp_Salary Example of translating a 1:1 relationship into a relational schema

14. 1 M Professor Class teaches (0,N) (1,1) Professor Class 1 Class_Code Class_Section Class_Days Class_Time Prof_Id Prof_Id Prof_Lname Prof_Phone M Logical Design • Translating ER Model into Relational Schema • One-to-many relationships are implemented by adding the primary key of the first entity as the foreign key of the second (many side) entity. Example: Foreign Key

15. Example - Logical Design

16. Example - Logical Design

17. Physical Design • Select data storage and data access characteristics (indexes) of the database. • It affects location of the data in the storage device(s) and system performance. • Physical design is more complex with distributed databases. • Relational databases are more insulated from physical layer details than hierarchical and network models. • Chapters 7 and 8 describe an excellent case study of database design