Entity Relational Diagram (ERD) Modeling

1. Entity Relational Diagram (ERD) Modeling This particular lecture is mainly prepared by the instructors at the University of Manitoba. Some contents are modified by Dr. Hadaegh and Mr. Jones for the students at the Red River College

2. ERD Model • Entity-Relationship, ER Model • ER model describes data as entities, relationships and attributes • Entity • A “thing” in the real world with an independent existence • Physical existence • Person, car, house or employee • Conceptual existence • Company, job, university course • Attribute • Property that describes entity • employee Name, Address, Age, HomePhone • companyName, Headquarters, President

3. Value • A particular entity will have a value for each of its attributes • The attribute values that describe each entity become a major part of the stored data • Example • employee: Name, Address, Age, HomePhone • e1: “Bill Adams”, “561 Machray Hall”, “25”, “8831” • e2: “John Smith”, “563 Queen Hall”, “30”, “8833”

4. Simple and Composite Attributes • Simple attribute • Attributes that are not divisible • Ex: age, SSN, StudentId, etc • Composite Attributes • Can be divided into smaller subparts which represent more basic attributes with independent meaning. For example: • Address consists of StreetAddress, City, State, PostalCode • A date can consists of Day, Month, and Year

5. Stored and Derived Attributes • Stored • Attributes that are fixed and does not have to be calculated • Ex: SSN, Marks, Name, etc… • Derived • Attributes that can be derived from other attributes either from the same entity or other entities. For example: • Age can be derived from BirthDate or • Grade, GPA can be derived from Marks • Attribute values can be derived from other entities. • NumberOfEmployees of a department in department entity can be derived by counting the number of employees who work in that department in the employee entity

6. Null Values • Not Applicable • A particular entity does not have an applicable value for an attribute • ApartmentNumber is null because the family lives in the house and not in an apartment • CollegeDegrees is null because the person never gone to the college • Unknown • Missing (value exists but not known) • Height of a person • Not Known (existence is not sure) • HomePhone, OfficePhone

7. Entity • An entity defines a collection object that have the same attributes • Entity is described by its name and attributes • Example: • Employee [Number, Name, Title, Salary] • Project [Number, Budget, Location] • Entity instances are instantiations of the entity • Example: • Employee Joe, Jim, ... • Project Compiler design, Accounting, ...

8. ER Notation • Entity • Rectangular box • Enclosing the entity name • Attribute Name • Oval • Attached to its entity by straight line • Composite Attribute • Attached to the component attribute by straight line EMPLOYEE EmpId EMPLOYEE

9. Attribute Notations Key Attribute Derived Attribute Regular Attribute Composite Attribute ...

10. Key Attribute • Values can be used to identify each entity uniquely. This plays the role of primary key in a table • The key attribute is underlined in ER diagram • Composite key Attribute s • Combination of the two or more attributes must be distinct for each instance. This plays the role of composite primary key in a table • Entities may have more than one attribute that can be chosen as key attribute. For example, StudentId and SSN. However, you only have to pick one as the key (primary key) for the entity • Entities can only have one primary key

11. EMPLOYEE Entity name Enum, Ename E1 J. Doe E2 M. Smith Attribute E3 A. Lee E4 J. Miller Instances E5 B. Casey E6 L. Chu E7 R. Davis E8 J. Jones

12. Entities SUPPLIER PROJECT WAREHOUSE EMPLOYEE PART LOCATION DEPARTMENT

13. Supplier No Supplier Name Location Entities and Attributes SUPPLIER PROJECT Project No Project Name Location WAREHOUSE PART EMPLOYEE Wareh. No Wareh. Name Emp. No Emp. Name Part No Part Name Location QTY WGT Addr Title Salary LOCATION DEPARTMENT Dept. No Dept. Name City Manager

14. Entities, Attributes, and Relationship SUPPLIER PROJECT Project No Project Name Supplier No Supplier Name Budget Location use supply has are used by works on are supplied by WAREHOUSE EMPLOYEE PART Emp. No Emp. Name Wareh. No Wareh. Name Part No Part Name Addr Title Salary Location QTY WGT is contained in Contains LOCATION DEPARTMENT Dept. No Dept. Name City Manager

15. Participation • A participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. • For example, we can say that every employee that is hired by this company should work in at least in one department. • Therefore, there is no reason to hire a employee and not being active in a particular department. This means the existence of the Employee entity depends on its being related to Department entity

16. Participation -- Cont • There are two types of participation: total participation and partial participation • Total Participation (existence dependency): Entity A has total participation in entity B if every instance of entity A relates to at least one instance of Entity B • Ex: every department in this university should have a department chair • Partial Participation: Entity A has partial participation in entity B if some of the instances of A (but not all instances) relate to some instances of entity B • Ex: some faculties (but not all of them) are the chairs of the departments in this university

17. Weak Entities • Entities that we have talked about so far, are all strong (regular) entity types. • A strong entity type has key attributes of its own whereas a weak entity type does not have a a particular key attribute that can uniquely identify its instances. • Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in combination with one of their attribute values • We can call the other entity type the Identifying or Owner Entity type and we call the relationship type that relates a weak entity to its owner entity Identifying Relationship

18. Weak Entities -- Cont • For example, we can store the information about the dependants of an employee in a separate entity called Dependents. • The Dependants entity can keep track of the name and other personal information of dependants of the employees. • Note that it might be possible to have two different employees that have a daughter named “Nancy”. So name cannot be used as the primary key of Dependants entity • In order to distinguish one instance of entity Dependants from another instance, we need to know the employee Id of the employees which is the primary key of Employee entity. • The combination of employee id (that is a property of Employee entity) and the name attribute (that is a property of Dependants entity) can be used to create a primary key for the Dependants entity.

19. All Notations Regular entity Weak entity Entities Attributes ... Regular Key Derived Composite Relationships Regular Relationship Or Identifying (Weak) Relationship Identifying (Weak) Relationship Regular Relationship

20. Example of ERD Project Name Employee Name Employee No Duration Project No EMPLOYEE WORKS ON WORKS ON PROJECT Responsibility Title Salary Budget TotalEmp Address Location Apt. # City Street #

21. Relationships • A relationship logically associates two or more entities with each other • Binary relationship associates two entities • Ternary relationship associates three entities with each other • N-ary relationship associates n entities with each other

22. Relationship attributes • Attributes can be attached to specific relationships. • Attributes that belong to two different entities should be placed as the attribute of the relationship • These values provide data about the relationship between the participating entity instances. Responsibility Duration Project Name Employee Name Employee No Project No WORKS ON EMPLOYEE WORKS ON PROJECT Title Salary Budget

23. Kinds of Relationships (Cardinality) • Fundamental ones are: • One-to-one • Many-to-one (one-to-many) • Many-to-many • Recursive relationships • A relationship between an entity and itself • NOTE: There can be multiple relationships between two entities WORKS-IN EMPLOYEE DEPARTMENT MANAGES

24. One-to-One Relationship • Each instance of an entity class E1 can be associated with at most one instance of another entity class E2 and vice versa. • Example : • Each employee can work on at most one project and each project employs at most one employee. Project Name Duration Employee Name Employee No Project No 1 1 WORKSON EMPLOYEE WORKS ON PROJECT Title Salary Budget Responsibility

25. One-to-One Relationship WorksOn Relationship EMPLOYEE PROJECT p1 e1 p2 e2 p3 e3 p4 e4 . . . . . .