1 / 11

Documenting Database Designs with Entity-Relationship Diagrams

Documenting Database Designs with Entity-Relationship Diagrams. IS 460 Notes by Thomas Hilton. What is the E-R Model?. A method of conceptualizing and documenting data structures used in organizations Static, not dynamic (data, not process) Logical/conceptual, not physical.

ringo
Download Presentation

Documenting Database Designs with Entity-Relationship Diagrams

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. Documenting Database Designs with Entity-Relationship Diagrams IS 460 Notes by Thomas Hilton

  2. What is the E-R Model? • A method of conceptualizing and documenting data structures used in organizations • Static, not dynamic (data, not process) • Logical/conceptual, not physical

  3. Essential Definitions • a group of instances that share the same attributes • a link between entities • a simplified, abstract replica of some real-world system • Entity: • Relationship: • Model: Entity-Relationship Diagram Entity Identification Key/Attribute identification Conversations, observations, forms, reports, etc. Relationship Identification Table Normalization

  4. Relationship E-R Symbols No Fixed Standard, but Some Common Usage • Maximum Cardinality1 or M (or a particular number)“If I have one of these, how many of those can I have?” • Minimum Cardinality0 or 1 (or a particular number)“If I have one of these, how many of those must I have?” ENTITY Max:Min Min:Max

  5. Attributes • Can be represented with a circle or ellipse on the entity, but please don’t for me • Instead do this:ENTITY [KEY-ATT, REQ-NON-KEY-ATT, REQ-NON-KEY-ATT, Opt-non-key-att]

  6. Keys • Primary: • Candidate: • Alternate: • Composite: • Secondary: • Common: • Foreign: • one or more attributes whose values uniquely identify each instance in an entity • attributes (or attribute groups) which could function as primary keys • candidate keys not chosen as the primary key • key consisting of more than one attribute • one or more attributes whose values identify groups of instances within an entity • key replicated in two entities to instantiate a relationship between them • common key which is primary in one entity and secondary in the other

  7. Attribute Constraints Instantiate Business Rules • consistency between entities (particularly common keys) • what’s legal and what’s not (format, size, value ranges, etc.) • (external and) internal consistency within the entity • transactions, data sources, required approvals, legitimate users, etc. • Referential Integrity: • Domain: • Entity Integrity: • Triggering operations:

  8. Table Normalization • Analyze entities into tables that can be manipulated without data redundancy and the resulting modification anomalies • splitting the attributes of one entity into multiple tables • “All non-key attributes must be fully functionally dependent • on the key, 1NF (non-dependencies) • the whole key, 2NF (partial dependencies) • and nothing but the key.” 3NF (transitive dependencies) • Definition: • Tool: • Rule:

  9. Domain-Key Normal Form Every constraint on the table is a logical consequence of the table's domain constraints and key constraints Have one “theme” per table

  10. Let’s Do An Example!

  11. Let’s Do An Example! INVOICE sent to CUSTOMER 0 or more 1 and only 1 1 and only 1 lists 0 or more STOCK-ITEM-ON-INVOICE allocates STOCK-ITEM 1 and only 1 0 or more

More Related