Documenting Database Designs with Entity Relationship Diagrams IS

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

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

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

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, Opt-non-key-att]

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

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

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

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

Let’s Do An Example!

Let’s Do An Example! 0 or more sent to 0 or more allocat es INVOICE 1 and only 1 CUSTOMER 1 and only 1 lists 0 or more STOCKITEM-ONINVOICE 1 and only 1 STOCKITEM