1 / 45

System Analysis and Design

System Analysis and Design. Logical Database Design. Logical and Physical View of Data. Users see the data in different ways (different users have different views of data) These view are examined by the systems analyst, and an overall logical model of the database needs to be developed.

finola
Download Presentation

System Analysis and Design

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. System Analysis and Design Logical Database Design

  2. Logical and Physical View of Data • Users see the data in different ways (different users have different views of data) • These view are examined by the systems analyst, and an overall logical model of the database needs to be developed. • The logical model of the database must be transformed into a corresponding physical design.

  3. Logical Database Design • Logical database design must be done before physical design. • ER Diagram is enhanced and adjusted to become more effective by normalization.

  4. Normalization • Normalization is the transformation of complex user views and data stores to a set of smaller, stable data structures. • Normalized data structures are more easily maintained than other data structures.

  5. Types of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Fourth Normal Form (4NF) • Fifth Normal Form (5NF)

  6. The Steps of Normalization

  7. Functional Dependency • Determinant Attribute Dependency Attribute • Determinant attribute usually refers to primary key or identifier • For instance, Emp_ID Enp_Name

  8. An determinant attribute and a dependency attribute Functional Dependency

  9. Functional Dependency • One determinant attribute and many dependency attributes

  10. Functional Dependency • Determinant Attribute Dependency Attribute • A 2-Way Relationship

  11. Functional Dependency • More than one determinant attribute indicate a dependency attribute

  12. Partial Dependency • More than one determinant attribute indicate a dependency attribute • One of the determinant attributes also indicates another dependency attribute that is not an identifier

  13. Transitive Dependency • One determinant attribute and many dependency attributes • One of the dependency attributes indicates a dependency attribute

  14. First Normal Form (1NF) • Attribute with repeating groups

  15. First Normal Form (1NF)

  16. First Normal Form (1NF) • Remove repeating groups

  17. First Normal Form (1NF)

  18. Second Normal Form (2NF) • Prime Attribute VS Non-Prime Attribute • 2NF Rules • The entity must be 1NF • Remove partial dependency

  19. Second Normal Form (2NF) • 1NF with Partial Dependency

  20. Second Normal Form (2NF)

  21. Second Normal Form (2NF)

  22. Third Normal Form (3NF) • 3NF Rules • The entity must be 2NF • Remove transitive dependency

  23. Third Normal Form (3NF)

  24. Third Normal Form (3NF)

  25. Third Normal Form (3NF)

  26. Relational Database Model • Relations (Tables) • Record of a row “Tuple” • Column

  27. Characteristics of Relations • Atomic data or “single valued” • Same attribute, same domain • No repeated record or tuple • No need to sort attribute • No need to sort record or tuple

  28. Consider this example

  29. Logical Schema Development • Eliminating Composite Attribute • Eliminating Multivalued Attribute • Eliminating External Attribute

  30. Eliminating Composite Attribute

  31. Eliminating Composite Attribute • First Method

  32. Eliminating Composite Attribute • Second Method

  33. Eliminating Multi-valued Attribute

  34. Eliminating Multi-valued Attribute

  35. Eliminating External Identifier

  36. Eliminating External Identifier

  37. Relational Schema • Last step of logical database design before building physical database • Relation name refers to the relation’s name • A1, A2, ……, An refer to attributes in that relation

  38. Relational Schema Transforming Entity into Relation

  39. Relational Schema Transforming Relationship into Relation • One-to-One Relationship – Total Participation

  40. Relational Schema Transforming Relationship into Relation • One-to-One Relationship – Partial Participation (A)

  41. Relational Schema Transforming Relationship into Relation • One-to-One Relationship – Partial Participation (B)

  42. Relational Schema Transforming Relationship into Relation • One-to-Many Relationship – Total Participation

  43. Relational Schema Transforming Relationship into Relation • One-to-Many Relationship – Partial Participation

  44. Relational Schema Transforming Relationship into Relation • Many-to-Many Relationship

  45. Relational Schema Transforming Relationship into Relation • N-ary Relationship

More Related