Database Design Methodology - PowerPoint PPT Presentation

database design methodology n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Design Methodology PowerPoint Presentation
Download Presentation
Database Design Methodology

play fullscreen
1 / 72
Database Design Methodology
209 Views
Download Presentation
ferris-atkinson
Download Presentation

Database Design Methodology

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. DatabaseDesign Methodology

  2. Lesson 1:Introduction to Databases

  3. Objectives • Define a database • Identify and discuss file-based data systems • Define relational database • Define and describe DBMSs, and identify their advantages and disadvantages • Choose the correct type of database • Discuss the origins of relational databases

  4. What Isa Database? • File-based databases • Flat-file databases • The evolution of databases

  5. Relational Databasesand DBMSs • Relational databases • Structured Query Language • Database administrator • Advantages and disadvantages of DBMSs • Choosing the correct database type

  6. The Origins ofRelational Databases • Codd first proposed the relational data model in 1970 • System R • Development of SQL • INGRES • IBM UK Scientific Center

  7. Summary • Define a database • Identify and discuss file-based data systems • Define relational database • Define and describe DBMSs, and identify their advantages and disadvantages • Choose the correct type of database • Discuss the origins of relational databases

  8. Lesson 2:Relational Database Fundamentals

  9. Objectives • Define and discuss multitier database architecture • Define relational model terminology • Describe different relational table types • Explain primary and foreign keys • Discuss data models and data relationships • Define database languages

  10. MultitierDatabase Architecture • Two-tier client-server architecture • Fat client • Three-tier client-server architecture • Thin client • n-tier architecture

  11. Relational data structure Rows (tuples) Columns (attributes) Domains Degrees Cardinality Normalization RelationalModel Terminology

  12. Using Tablesto Represent Data • Entities • Characteristics of relations • Types of tables • Primary keys • Foreign keys

  13. Data Models • Components • Structural information • Manipulative information • Integrity information

  14. Entities andData Relationships • Entity-Relationship modeling • Strong entities • Weak entities

  15. Relational Integrity • Domain constraints • Entity integrity • Referential integrity • Views

  16. Database Languages • Data definition language • Data manipulation language • Data control language • Data dictionaries

  17. Summary • Define and discuss multitier database architecture • Define relational model terminology • Describe different relational table types • Explain primary and foreign keys • Discuss data models and data relationships • Define database languages

  18. Lesson 3:Database Planning

  19. Objectives • Explain the database design life cycle • Discuss database planning • Describe a database requirements document • Discuss the criteria for selecting a DBMS • Discuss the criteria for selecting an application interface

  20. Create a database strategy Define database application scope Create a database requirements document Design the database Select a DBMS Design the database application Create database prototypes Test the database application Implement the database application Convert legacy data Maintain the database DatabaseDesign Life Cycle

  21. DatabaseRequirements Document • Conducting interviews • Requirements document information

  22. Selecting a DBMS • Selection criteria • Data definition functionality • Physical criteria • Access criteria • Transactions • Utilities • Development tools • Miscellaneous features

  23. Selecting anApplication Interface • GUI considerations • Descriptive page titles • Clear instructions • Consistent grouping of input fields • Logically labeled fields • Consistent color use • Properly sized data entry fields • Logical cursor movement • Error messages • Clearly indicated optional fields • Completion message

  24. Summary • Explain the database design life cycle • Discuss database planning • Describe a database requirements document • Discuss the criteria for selecting a DBMS • Discuss the criteria for selecting an application interface

  25. Lesson 4:Overview of DatabaseDesign Methodology

  26. Objectives • Describe relational database design methodology • Identify the effects of poor design practices • Create Entity-Relationship models • Identify entities and relationship types • Identify entity attributes and domains • Determine primary and candidate keys

  27. Effects ofPoor Database Design • Insertion anomalies • Deletion anomalies • Update anomalies

  28. DatabaseDesign Phases • Conceptual phase • Logical phase • Physical phase

  29. ConceptualDatabase Design • Identifying entities • Identifying attributes and attribute domains for entities • Identifying relationships • Identifying candidate and primary keys for entities • Creating an Entity-Relationship diagram • Reviewing the ER model by the user and design team

  30. Entity-Relationship Models • Creating ER models • Defining domains • Common SQL data types • Determining data relationships • Recursive relationships

  31. Summary • Describe relational database design methodology • Identify the effects of poor design practices • Create Entity-Relationship models • Identify entities and relationship types • Identify entity attributes and domains • Determine primary and candidate keys

  32. Lesson 5:Normalization

  33. Objectives • Define normalization • Explain normal forms • Define and discuss 1NF • Define and discuss 2NF • Define and discuss 3NF • Define and discuss BCNF • Explain the normalization process

  34. What IsNormalization? • Normal forms • First normal form • First normal form anomalies • Second normal form • Second normal form anomalies • Third normal form • Boyce-Codd normal form

  35. Related Concepts • Decomposition • Atomic value • Partial functional dependency • Transitive dependency • Denormalization

  36. Summary • Define normalization • Explain normal forms • Define and discuss 1NF • Define and discuss 2NF • Define and discuss 3NF • Define and discuss BCNF • Explain the normalization process

  37. Lesson 6:Logical Database Design

  38. Objectives • Identify logical database design issues • Identify the cardinality of data relationships • Create a logical data model from a conceptual data model • Use a database definition language • Define integrity constraints • Create and validate an enterprise data model

  39. LogicalDatabase Design • Logical data models • One-to-one • One-to-many • Many-to-many

  40. Creating aLogical Data Model Logical data model creation Logical data model creation Normalization Normalization Data model refinement Data model refinement

  41. Using a DatabaseDefinition Language • Data dictionaries • Validating the logical data model

  42. DefiningIntegrity Constraints • Necessary data • Domain constraints • Entity integrity • Referential integrity • Enterprise constraints

  43. Creating anEnterprise Data Model • User views • Normalization • Validating data operations

  44. Summary • Identify logical database design issues • Identify the cardinality of data relationships • Create a logical data model from a conceptual data model • Use a database definition language • Define integrity constraints • Create and validate an enterprise data model

  45. Lesson 7:Physical Database Design

  46. Objectives • Identify physical database design issues • Describe how to create base relations for a target DBMS using a DDL • Identify and create enterprise constraints for a target DBMS • Define secondary indexes • Define denormalization • Create user views • Design database access rules

  47. PhysicalDatabase Design • Creating base relations for a target DBMS • Data definition language

  48. CreatingEnterprise Constraints • Determining referential constraints

  49. UsingSecondary Indexes • A secondary index is a mechanism that creates an additional key for a relation

  50. Denormalization • The process of reuniting relations that were split during the normalization process to improve performance