1 / 50

Chapter 10

Chapter 10. Logical Data Modeling: Normalization Physical Database Design: Overview and Denormalization. Agenda. Data Modeling in the SDLC and Outcomes What is Logical Data Modeling and Normalization Functional Dependency Normal Forms Translating ERDs to Normalized Relations

fleur
Download Presentation

Chapter 10

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. Chapter 10 Logical Data Modeling: Normalization Physical Database Design: Overview and Denormalization

  2. Agenda • Data Modeling in the SDLC and Outcomes • What is Logical Data Modeling and Normalization • Functional Dependency • Normal Forms • Translating ERDs to Normalized Relations • View Integration • Overview of Physical Database Design • Denormalization

  3. Learning Objectives • Understand key terminology • Know where logical data modeling fits into systems development • Be able to represent relationships through relations • Be able to translate ERDs into normalized relations • Be able to merge different sets of normalized relations

  4. Where We Are in the SDLC

  5. l Data Modeling within the SDLC

  6. Outcomes of Logical Database Design • Normalized relations (see Examples) Note: Account for every data element on or in • system or process input (e.g., forms) • system or process output (e.g., reports, query screens) • data stores or E-R diagram Each data element must be kept in the system’s database or derived from data in the database

  7. Logical Database Design • Based upon the conceptual data model • Four key steps • Develop a logical data model for each known user interface for the application using normalization principles. • Combine normalized data requirements from all user interfaces into one consolidated logical database model (view integration). • Translate the conceptual E-R data model for the application into normalized data requirements. • Compare the consolidated logical database design with the translated E-R model and produce one final logical database model for the application.

  8. Physical Database Design • Based upon results of logical database design • Key decisions • Choosing storage format for each attribute from the logical database model • Grouping attributes from the logical database model into physical records • Arranging related records in secondary memory (hard disks and magnetic tapes) so that records can be stored, retrieved and updated rapidly • Selecting media and structures for storing data to make access more efficient

  9. Query Screen Example

  10. Report Example

  11. Integrated Set of Relations (Fig. 10-3c) Notice that this attribute was in Query view (Fig. 10-3a) but not Report view (Fig. 10-3b) (derived from two previous examples)

  12. Conceptual Data Model and Transformed Relations (Fig. 10-3d) Customer Order Processing Application

  13. Final Set of Normalized Relations (Fig. 10-3e) Notice that these attributes were in ER diagram (Fig. 10-3d) but not in integrated view from Query and Report (Fig. 10-3c) (derived from two previous examples) Notice that this attribute was in integrated view from Query and Report (Fig. 10-3c) but not in ER diagram (Fig. 10-3d)

  14. What Is Logical Data Modeling • Translating conceptual data models into a format consistent with the architecture used by the data management software to be used with the application • Normalization • analysis of functional dependencies between data items to result in a structure of data that is simple, stable, and fundamental

  15. Well-Structured Relations • Avoid Anomalies (Errors, Inconsistencies, Problems) • Insertion • Deletion • Modification

  16. Anomalies: Example of Non-Simple Relation Primary Key • Although Emp_ID and Course make each row unique, the table is not simple • Data in each instance not just related to primary key • It should be possible to add a new employee without supplying course data, but not here (thus, an insertion anomaly) • It should be possible to delete a course without losing employee data, but not here (thus, a deletion anomaly) • Notice redundant data; errors could occur on modifying data, e.g., salary (thus, a modification anomaly) • Table contains data on more than one entity; use separate, simpler tables

  17. Example of Simpler Relations: No Redundancy Primary Key Primary Key • Each instance contains only data related to the primary key • Anomalies less likely • Insertion of employee does not require course data • Deleting course would not affect data in employee table • Less redundancy makes modification errors less likely

  18. Data Normalization • Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data. • The process of decomposing relations with anomalies to produce smaller, well-structured relations.

  19. Functional Dependency • For a relation (table), attribute A depends on attribute B if for every valid row the value of B determines the value of A B A • E.g. • Student ID Student name • Order No + Product No Quantity ordered

  20. Normal Forms • First normal form • No multi-valued attributes. • Every attribute value is atomic. • Second normal form • 1NF and every non-key attribute is fully functionally dependent on the primary key. • Every non-key attribute must be defined by the entire key, not by only part of the key. • No partial functional dependencies. • Third normal form • 2NF and no transitive dependencies (functional dependency between non-key attributes.)

  21. Relation with transitive dependency (a) SALES relation with simple data

  22. Removing a transitive dependency (a) Decomposing the SALES relation

  23. Transforming E-R Diagrams into Relations 1. Map Regular Entities to Relations. • Composite attributes: Use only their simple, component attributes. • Multi-valued Attribute - Becomes a separate relation with a foreign key taken from the superior entity.

  24. Mapping a composite attribute (a) CUSTOMER entity type with composite attribute

  25. (b) CUSTOMER relation with address detail

  26. Transforming E-R Diagrams Into Relations 2. Map Weak Entities • Becomes a separate relation with a foreign key taken from the superior entity.

  27. Example of mapping a weak entity (a) Weak entity DEPENDENT

  28. (b) Relations resulting from weak entity

  29. Transforming E-R Diagrams Into Relations 3. Map Binary Relationships • One-to-Many - Primary key on the one side becomes a foreign key on the many side • Many-to-Many - Create a new relation with the primary keys of the two entities as its primary key • One-to-One - Primary key on the mandatory side becomes a foreign key on the optional side

  30. Example of mapping a 1:M relationship (a) Relationship between customers and orders

  31. (b) Mapping the relationship

  32. Example of mapping an M:N relationship (a) Requests relationship (M:N)

  33. (b) Three resulting relations

  34. Mapping a binary 1:1 relationship (a) Binary 1:1 relationship

  35. (b) Resulting relations

  36. Transforming E-R Diagrams Into Relations 4. Map Associative Entities • Identifier Not Assigned • Default primary key for the association relation is composed of the primary keys of the two entities • Identifier Assigned • It is natural and familiar to end-users. • Default identifier may not be unique.

  37. Mapping an associative entity with an identifier (a) Associative entity (SHIPMENT)

  38. (b) Three relations

  39. Transforming E-R Diagrams Into Relations 5. Map Unary Relationships • One-to-Many - Recursive foreign key in the same relation • Many-to-Many - Bill-of-materials: Two relations: • One for the entity type. • One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity

  40. Transforming E-R Diagrams Into Relations 6. Map Ternary (and n-ary) Relationships • One relation for each entity and one for the associative entity

  41. Mapping a ternary relationship (a) Ternary relationship with associative entity

  42. (b) Mapping the ternary relationship

  43. View Integration / Merging Relations • Building relations from independent “views” or data models • Issues that arise • Synonyms • Homonyms • Transitive dependencies • ISA relationships (supertypes/subtypes)

  44. Physical File and Database Design • The following information is required: • Normalized relations, including volume estimates • Definitions of each attribute • Descriptions of where and when data are used, entered, retrieved, deleted, and updated (including frequencies) • Expectations or requirements for response time and data integrity • Descriptions of the technologies used for implementing the files and database • Must be collected during Analysis

  45. Designing Fields • Field • Smallest unit of named application data recognized by system software • Attributes from relations will be represented as fields • Data Type • A coding scheme recognized by system software for representing organizational data • Choosing data types • Four objectives • Minimize storage space • Represent all possible values of the field • Improve data integrity of the field • Support all data manipulations desired on the field • Calculated fields • A field that can be derived from other database fields

  46. Methods of Controlling Data Integrity • Default Value • A value a field will assume unless an explicit value is entered for that field • Range Control • Limits range of values that can be entered into field • Referential Integrity • An integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation • Null Value • A special field value, distinct from 0, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown

  47. Denormalization • Implementing relations in non-normalized form • Why? • Optimize processing efficiency at cost of managing anomalies • combine into one table data needed together • separate a table which has several uses • Partition over several servers

  48. Indexed File Organization A file organization in which rows are stored either sequentially or nonsequentially and an index is created that allows software to locate individual rows Index: A table used to determine the location of rows in a file that satisfy some condition

  49. Guidelines for Choosing Indexes • Specify a unique index for the primary key of each table. • Specify an index for foreign keys. • Specify an index for nonkey fields that are referenced in qualification, sorting and grouping commands for the purpose of retrieving data.

  50. Summary: Four Key Steps in Logical Database Modeling • Develop a logical data model for each known user interface (form and report) using normalization principles • Combine normalized data requirements from all interfaces (view integration) • Translate E-R data model (developed without explicit concern for interfaces) into normalized data requirements • Compare results of 2 and 3 and produce, through view integration, one final model

More Related