Database Design Methodology

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