1 / 117

Database Design Specialist

Database Design Specialist. Lesson 1: Introduction to Databases. Objectives. List database types (e.g., flat file, relational), and identify their uses and architectures Describe the types of database management system (DBMS), and explain the benefits and limitations of each DBMS type

hilary-hayes
Download Presentation

Database Design Specialist

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. Database Design Specialist

  2. Lesson 1:Introduction to Databases

  3. Objectives • List database types (e.g., flat file, relational), and identify their uses and architectures • Describe the types of database management system (DBMS), and explain the benefits and limitations of each DBMS type • Analyze and select appropriate database designs, and identify the solution that addresses the application needs

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

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

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

  7. Summary • List database types (e.g., flat file, relational), and identify their uses and architectures • Describe the types of database management system (DBMS), and explain the benefits and limitations of each DBMS type • Analyze and select appropriate database designs, and identify the solution that addresses the application needs

  8. Lesson 2:Relational Database Fundamentals

  9. Objectives • Define common database architectures (e.g., single, multi-tier) • Describe the function of Structured Query Language (SQL), including language subsets • Describe the function of Data Definition Language (DDL) • Describe the function of Data Manipulation Language (DML)

  10. Objectives (cont’d) • Describe the function of Data Control Language (DCL) • Define essential database concepts and terms, including relation, relation name, table, row, column, value, relational algebra, data modeling, data relationship • Explain the concepts of attribute, degree, tuple and cardinality

  11. Objectives (cont’d) • Define entities, including strong entities, weak entities • List characteristics of relations (e.g., column characteristics, row characteristics) • Describe table types (e.g., base tables, virtual tables) • Distinguish between primary and foreign keys, including null value, composite key • Explain data models in relational databases

  12. Objectives (cont’d) • Describe common database relationships (e.g., one to one, one to many, many to many) and identify the notation for such relationships (e.g., 1:n) • Define relational integrity concepts, including domain constraints, entity and referential integrity views • Explain the structure and purpose of a data dictionary

  13. Multitier Database Architecture • Two-tier client-server architecture • Fat client • Three-tier client-server architecture • Thin client • n-tier architecture

  14. Relational data structure Rows (tuples) Columns (attributes) Domains Degrees Cardinality Normalization Relational Model Terminology

  15. Using Tables to Represent Data • Entities • Characteristics of relations • Types of tables • Primary keys • Foreign keys

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

  17. Entities and Data Relationships • Entity-relationship (ER) modeling • Strong entities • Weak entities

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

  19. Database Languages • Data Definition Language • Data Manipulation Language • Data Control Language • Data dictionaries

  20. Summary • Define common database architectures (e.g., single, multi-tier) • Describe the function of Structured Query Language (SQL), including language subsets • Describe the function of Data Definition Language (DDL) • Describe the function of Data Manipulation Language (DML)

  21. Summary (cont’d) • Describe the function of Data Control Language (DCL) • Define essential database concepts and terms, including relation, relation name, table, row, column, value, relational algebra, data modeling, data relationship • Explain the concepts of attribute, degree, tuple and cardinality

  22. Summary (cont’d) • Define entities, including strong entities, weak entities • List characteristics of relations (e.g., column characteristics, row characteristics) • Describe table types (e.g., base tables, virtual tables) • Distinguish between primary and foreign keys, including null value, composite key • Explain data models in relational databases

  23. Summary (cont’d) • Describe common database relationships (e.g., one to one, one to many, many to many) and identify the notation for such relationships (e.g., 1:n) • Define relational integrity concepts, including domain constraints, entity and referential integrity views • Explain the structure and purpose of a data dictionary

  24. Lesson 3:Database Planning

  25. Objectives • Describe the elements of a database planning strategy (e.g., defining the scope of a database application) • List the necessary steps for creating a database requirements document, including conducting interviews, noting project constraints, identifying the precise duties of the database, creating a database design document

  26. Objectives (cont’d) • Explain the importance of data modeling and entity relationship diagrams during the planning stage (e.g., define the concept of an entity-relationship [ER] diagram) • Create a database prototype • Describe the purpose of database application testing, including white-box testing, black-box testing

  27. Objectives (cont’d) • List the criteria for selecting a database management system and an application interface • Describe database design steps, including determining the proper steps in creating entities, applying normalization, selecting a database management system (DBMS)

  28. 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 Database Design Life Cycle

  29. Database Requirements Document • Conducting interviews • Requirements document information

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

  31. 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 Selecting an Application Interface

  32. Summary • Describe the elements of a database planning strategy (e.g., defining the scope of a database application) • List the necessary steps for creating a database requirements document, including conducting interviews, noting project constraints, identifying the precise duties of the database, creating a database design document

  33. Summary (cont’d) • Explain the importance of data modeling and entity relationship diagrams during the planning stage (e.g., define the concept of an entity-relationship [ER] diagram) • Create a database prototype • Describe the purpose of database application testing, including white-box testing, black-box testing

  34. Summary (cont’d) • List the criteria for selecting a database management system and an application interface • Describe database design steps, including determining the proper steps in creating entities, applying normalization, selecting a database management system (DBMS)

  35. Lesson 4:Overview of DatabaseDesign Methodology

  36. Objectives • List the steps of the conceptual design phase (e.g., identifying entities, attribute domains, relationships) • Describe how to identify entities • List ways to identify attributes and attribute domains for entities

  37. Objectives (cont’d) • List ways to identify candidate and primary keys for entities • Identify and determine data relationships • Create an entity-relationship (ER) diagram • Analyze an entity-relationship (ER) diagram or model to determine relation types

  38. Objectives (cont’d) • Define and describe domains • Explain the results of poor database design, and describe insertion, deletion and update anomalies in databases • Describe database design steps, including determining the proper steps in creating entities, applying normalization, selecting a database management system (DBMS)

  39. Effects of Poor Database Design • Insertion anomalies • Deletion anomalies • Update anomalies

  40. Database Design Phases • Conceptual phase • Logical phase • Physical phase

  41. Conceptual Database Design • Identifying entities • Identifying attributes and attribute domains for entities • Identifying relationships • Identifying candidate and primary keys for entities • Creating an entity-relationship (ER) diagram • Reviewing the ER model by the user and design team

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

  43. Summary • List the steps of the conceptual design phase (e.g., identifying entities, attribute domains, relationships) • Describe how to identify entities • List ways to identify attributes and attribute domains for entities

  44. Summary (cont’d) • List ways to identify candidate and primary keys for entities • Identify and determine data relationships • Create an entity-relationship (ER) diagram • Analyze an entity-relationship (ER) diagram or model to determine relation types

  45. Summary (cont’d) • Define and describe domains • Explain the results of poor database design, and describe insertion, deletion and update anomalies in databases • Describe database design steps, including determining the proper steps in creating entities, applying normalization, selecting a database management system (DBMS)

  46. Lesson 5:Normalization

  47. Objectives • Distinguish between specific normal forms, including first normal form (1NF), second normal form (2NF), third normal form (3NF), Boyce-Codd normal form (BCNF) • Describe the normalization process • Define and explain determinant, decomposition and functional dependency

  48. Objectives (cont’d) • Describe database design steps, including determining the proper steps in creating entities, applying normalization, selecting a database management system (DBMS)

  49. What Is Normalization? • Normal forms • First normal form • First normal form anomalies • Second normal form • Second normal form anomalies • Third normal form • Boyce-Codd normal form

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

More Related