Entity Relationship Diagram (ERD) Guide PDF

2/8/25 ENTITY RELATIONSHIP ENTITY RELATIONSHIP DIAGRAM DIAGRAM ER diagram...

2/8/25 ENTITY RELATIONSHIP ENTITY RELATIONSHIP DIAGRAM DIAGRAM ER diagram Is a visual form of relational database. It a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system. 1 2 USES ER DIAGRAM Database design ENTITY RELATIONSHIP ER diagrams are used to model and design relational databases, in DIAGRAM terms of logic and business rules. an ER diagram is often an initial step in determining requirements for an information systems project. Conceptual Data model Database troubleshooting ER diagrams are used to analyze existing databases to find and resolve problems in logic or deployment. Drawing the diagram should reveal where it’s going wrong 3 4 USES ER DIAGRAM USES ER DIAGRAM Business information systems Research : The diagrams are used to design or analyze relational databases Since so much research focuses on structured data, ER diagrams used in business processes. Any business process that uses fielded can play a key role in setting up useful databases to analyze the data involving entities, actions and interplay can potentially benefit from a relational database. data. Business process re-engineering Education ER diagrams help in analyzing databases used in business process ER diagrams help in analyzing databases used in business process re-engineering and in modeling a new database setup re-engineering and in modeling a new database setup 5 6 1  2/8/25 COMPONENTS OF ERD Entity ELEMENTS/COMPONENTS OF A definable thing - such as a person, object, concept or event - that can have data stored about it. Think of entities as nouns. ENTITY RELATIONSHIP Examples: a customer, student, people, car or product. DIAGRAM Strong Entity W eak Entity Entity on Database Or Entity 7 8 COMPONENTS OF ERD Example of Entity and Attributes Attributes Fname Lname Sim ple attributes are those drawn from the atom ic value dom ains and can’t be further A property or characteristic of an entity. Each entity is described by divided; they are also called single-valued a set of attributes attributes. (e.g., Employee = (Name, Address, Birthdate (Age), Salary). Student_ID Simple attributes Multivalued Derived attributes attributes Composite attributes 9 10 Example of Entity and Attributes Example of Entity and Attributes Lname Com posite attributes are those that consist of Lname Derived attributes Attributed is calculated or Fname Fname a hierarchy of attributes. otherwise derived from another attribute, such as age from a birthdate. Student_ID Student_ID 11 12 2  2/8/25 Example of Entity and Attributes COMPONENTS OF ERD Fname Lname M ultivalued attributes M ore than one attribute value is denoted. Exam ple is phone_No, Relationship Colors, Sizes. The same entity participates more than once in the relationship. Relationships are the glue that holds the tables together. They are used to connect related information between tables. Student_ID Strong Weak Relationship Relationship Note: Relationship strength is based on how the primary key of a related entity is defined 13 14 COMPONENTS OF ERD TYPES OF KEYS Identifiers or Entity keys Candidate key Refers to an attribute that uniquely defines an entity in an entity set. A minimal super key, meaning it has the least possible number of attributes to Entity keys can be super, candidate or primary. still be a super key. An entity set may have more than one candidate key. Super key A set of attributes (one or more) that together define an entity in an entity set. Composite key A composite key is composed of two or more attributes, but it must be minimal. 15 16 TYPES OF KEYS Example of Entity, Keys, Relationship and Attributes Primary key FNAM E A candidate key chosen by the database designer to uniquely S T U D E N T _ ID SC H O O L_YE S U B J E C T _ ID D E S C R IP T IO N identify the entity set. AR PH O N E_N O LN AM E COURSE Foreign key NAM E STUDENT HAS SUBJECTS : Identifies the relationship between different entities. When a copy M NAM E of the primary key for one entity is included in the collection of B IR T H D A Y NAM E attributes of another entity, the copy of the primary key held in the second entity is known as a foreign key. ADDRESS AGE E M A IL BRGY Primary key STREET C IT Y Alternate keys are all candidate keys not chosen as the primary key. ERD COLLEGE STUDENT LIST OF ENROLLED SUBJECTS 17 18 3  2/8/25 Example of Entity, Keys, Relationship and Attributes COMPONENTS OF ERD FNAM E CARDINALITY Defines the numerical attributes of the relationship between two entities or S T U D E N T _ ID SC H O O L_YE SC H O O L_N A AR ME entity sets. PH O N E_N O LN AM E NAM E NAM E STUDENT HAS HS_RECORD M NAM E B IR T H D A Y SC H O O L_YEA R ADDRESS AGE E M A IL BRGY C IT Y STREET ERD OF COLLEGE STUDENT HISGHSCHOOL RECORDS 19 20 CARDINALITY CARDINALITY Multiplicity is the maximum number of STUDENT HAS SUBJECTS instances an entity can have with another, and the mandatory relationship is the HOW MANY SUBJECTS CAN STUDENT MAY HAVE? 1 OR MANY minimum number of times an instance occurs STUDENT HAS SUBJECTS MIN MAX HOW MANY STUDENTS CAN BE IN A SUBJECT? MANY A many-to-one or many relationship means that each student should take at least one or multiple subjects, and each subjects have multiple students. 21 22 ENTITY RELATIONSHIP LOGICAL DATA DIAGRAM A logical data model establishes the structure of data DIAGRAM elements and the relationships among them. It is independent of the physical database that details how the Logical Data Model data will be implemented. The logical data model serves as a blueprint for used data. 23 24 4  2/8/25 LOGICAL DIAGRAM SAMPLE LOGICAL DIAGRAM SAMPLE STUDENT HAS SUBJECTS STUDENT HAS SUBJECTS 25 26 ENTITY RELATIONSHIP PHYSICAL DATA DIAGRAM The physical data model pertains to how the system will be DIAGRAM implemented, and factors in the specific databases management system. This model is typically created by Physical Data model developers. The idea is more to define how the actual database will be used or implemented for business purposes. 27 28 E-COMMERCE DATABASE SYSTEM PHYSICAL VS LOGICAL HAS FOREIGN PRIM ARY KEY (FK) IT HAS DEFINED PRIM ARY KEY (PK) THE CARDINALITY OR DATA TYPE AND LIM IT RELATIONSHIP FLOW FROM ENTITY PRIM ARY AND FOREIGN KEY 29 30 5  2/8/25 PHYSICAL DIAGRAM SAMPLE PHYSICAL DIAGRAM SAMPLE Foreign Key(PK) Primary key (PK) A foreign key in an Entity-Relationship Diagram (ERD) is an Alternate keys are all candidate keys not chosen as the primary key. attribute (or a set of attributes) in one entity that refers to the primary key of another entity. It establishes a CHARACTERISTICS OF A PRIMARY KEY relationship between the two entities, allowing for the association of data across different tables in a relational 1. Uniqueness: Each record in a table must have a unique primary key value. database. 2. No nulls: The primary key cannot have null values. 3. No repeats: The primary key cannot have repeating values. CHARACTERISTICS OF A Foreign KEY 4. Unchanging: The values must not change or become null during the life 1. No nulls: The foreign key cannot have null values. of each entity instance 2. Can repeats: The Foreign key can be repeated inside the table. 3. Unchanging: The values must not change or become null during the life of each entity instance 31 32 MORE EXAMPLE https://drive.google.com/file/d/1APMr v47V6HY9bHD3YZwclJKqeO4Jflzs/view? usp=sharing 33 6