Entity Relationship Diagrams (ERD)

Steps to Create an ERD

- Identify the entity - Identify the entity's attributes - Identify the Primary Keys - Identify the relationships between entities - Identify the Cardinality constraint - Draw the ERD - Check the ERD Model Entities & Attributes --> Choose Primary Keys --> Model Relationships --> Determine Cardinalities --> Check the Model

Purpose of an ERD

- The database analyst/designer gains a better understanding of the information to be contained in the database through the process of constructing the ERD. - The ERD serves as a documentation tool. - Finally, the ERD is used to communicate the logical structure of the database to users. In particular, the ERD effectively communicates the logic of the database to users.

Entity Relationship Diagram (ERD)

- allows us to sketch database designs - a graphical tool for modeling data. - widely used in database design - a graphical representation of the logical structure of a database - a model that identifies the concepts or entities that exist in a system and the relationships between those entities ** the "blueprint" from which the actual data is stored — the output of the design phase.

Components of an ERD

1. Entity 2. Relationship 3. Cardinality 4. Attribute

Cardinality Types

1. One to one - A Manager head to one Department & vice versa 2. One to many (or many to one) - An Employee works in one Department OR - One Department has many Employees 3. Many to many - A Teacher teaches many Students AND - A Student is taught by many Teachers

Different types of relationships

1. One-to-one (direct relationship) - Exists when one of the second entity occurs for the first entity 2. One-to-many - Exists when one occurrence of the first entity can relate to many instances of the second entity 3. Many-to-many - Exists when one instance of the first entity can relate to many instances of the second entity and one instance of the second entity can relate to many instances of the first entity. ** Associations between entities ** Self-join relationship—an entity with a relationship connecting to itself.

Classifications of Relationships

1. Optional Relationship - An Employee may or may not be assigned to a Department - A Patient may or may not be assigned to a Bed 2. Mandatory Relationship - Every Course must be taught by at least one Teacher - Every mother have at least a Child

The Different Types of Keys

1. Primary key - field or combination of fields that uniquely identifies an entity - A primary key should be minimal and contain no extra attributes than are necessary to identify a record. 2. Candidate key - an attribute or collection of attributes, that can serve as a primary key 3. Secondary key-a key which may not be unique, used to select a group of records 4. Composite key - a combination of two or more attributes representing the key 5. Foreign key - a field in one table that matches the primary key in another table 6. Non key - any field that is not a candidate key

Cardinality

A way to express the number of entities to which another entity can be associated via a relationship set. ______ Constraints - the number of instances of one entity that can or must be associated with each instance of another entity. - Minimum ________ - If zero, then optional - If one or more, then mandatory - Maximum ________ - The maximum number

Entity in an ERD

Any object or event about which someone chooses to collect data - May be a person, place, or thing (ex. salesperson, city, product) - May be an event or unit of time (ex. a sale, a month/year)

Attributes, Records & Tables

Attributes (Data Entries) or Fields - represent some characteristic of an entity - ex. For an employee entity: Attributes could be name, age, etc. Records or Tuple - set of related fields that describe one instance or occurrence of an entity Table or File - set of related records about an entity Keys are data items (attributes) in a record used to identify the record