1 / 72

Database Design Methodology

Database Design Methodology. Lesson 1: Introduction to Databases. 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

Download Presentation

Database Design Methodology

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related