"Data Modeling (Entity Relationship Diagram) - PowerPoint"
Data Modeling (Entity Relationship Diagram) Farrokh Alemi, Ph.D. Updated by Janusz Wojtusiak (Fall 2009) Overview of the Course 1. Abstract business process into database requirements 2. Model system requirements into a database, and 3. Use Standard Query Language to gain access to the data. Previous Lecture Presented Guidelines for Defining Fields in Databases. Entity • An entity is something that has an independent, separate, or self-contained existence • Divide the list of fields into entities – Each entity will represent a table – Each field in the table is an attribute of the entity. Entities Describe Data Classes Name Type Def. Logical Data Types String Data Types Binary Data Types Entity Numeric Data Types Date/Time Data Types 1..n Attribute Data Name Def. Constr. Type Shared Features Suggest Entities • Examine common features of the fields. – "Patient's first name," "Patient's last name" and "Patient's birthday" suggest an entity called "Patient." – "Type of diagnosis" and "Name of diagnosis" suggest an entity called "Diagnosis." • Make sense to others • Entity names should imply what it is about Naming of Entities • “Create a name that is unambiguous • Use the minimum number of words • Do not use physical characteristics of the database • Do not use abbreviations • Do not use words that restrict the data • Do not use words that suggest two concepts • Use singular form of the name." Describing an Entity • Always include a brief description of the entity. – Set up expectations about fields • The entity "Patient" may be defined as: "The clients in the court diversion program who have mental illness. Information about the patients need to be kept in order to track if their court ordered treatment is working and has been followed." Rules for Assigning Entities • No two entities should be about the same things. – For example, calling one entity "Patient" and another "Client" may create both confusion and inefficiency. Rules for Assigning Entities • Do not assign the same field in two different entities. – Patient's name belongs to the "Patient“ entity and not to “Diagnosis” entity. Satisfy the intuitions of future users of the database Rules for Assigning Entities • Make sure that time based events are separated from time independent fields. – A patient's diagnoses does not belong to the entity "Patients" but to “Visits.” Rules for assigning entities • If several entities share the same fields, see if you can partition the shared fields into a separate entity. – Create a new entity called "Person" and keep shared fields in it. Then if an employee falls ill or is seen in the court, their name does not need to be entered several times once as an employee, next as a patient and last as a court client. Rules for Assigning Entities • Include look-up entities as you proceed. Look up table Main entity Review & Revise Entities • Review the fields within entities for completeness. – Many entities suggest new fields that have not been thought about. • Review again to see if the fields fit new entities • Share the list with organizational members • Documentation – a name, a description, a statement of why it is important to track the entity, and a list of fields that belong to it Entity Relationships • Implied in the very definition of the entity. • Three types of relationships: – One to one – One to many – Many to many • How one entity is mapped into another is also referred to as cardinality of the entity Cardinality defined Cardinality Follows Business Rules Many to Many Relationships Reveal New Entities Display of Relationships Shows a Shows relationship cardinality Display of Relationships Shows a Names the relationship relationship Display of Relationships • Different standards • IDEF1x • Barker’s notation • UML • http://www.essentialstrategies.com/publicati ons/modeling/idef1x.htm • http://www.agiledata.org/essays/dataModeli ng101.html Documentation of Relationships • Name of both entities • The verb phrase that describes the semantics of the linkage • The cardinality of the linkage All relationships must be documented before proceeding to the physical design of the database