Introduction to Database Design Methodology

1. Introduction to Database Design Methodology Chapter 4 Entity-Relationship Diagram

2. Learning Goals • To describe and identify the basic symbols in ER diagram • To describe and identify the different types of relationships in ER diagrams • To construct simple ER diagrams

3. Table of Contents • The idea of ER diagram • Basic symbols in ER diagram • Degree of relationship • Different types of binary relationship • Participation constraint • Summary on ER diagram construction

4. The Idea of ER Diagram

5. Consider the example of online bookstore again • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer. Here, we identify the entities, attributes and relationships by using the following colours: entity relationship attribute

6. Example of Online Bookstore • Basic Entity Sets: • BOOK • Attributes: • book_no, ISBN, title, author, publisher • CUSTOMER: • Attributes: • cust_id, address, e-mail, credit_card • Relationship:purchase • Description:Customer purchases books

7. Example of Online Bookstore The idea of ER diagram • Even we have identified the entities and relationship(s) of the system, it is difficult to observe the overall structure of the system. • A graphical / visual representation of the system can help us understand the system more easily. • The graphical representation can also serve as an effective communication tool between database designer, programmers and end users. • Entity-relationship (ER) diagram is a standard graphical representation of conceptual database design, which was introduced by Peter Chen in 1976. • http://www.csc.lsu.edu/~chen/chen.html • http://cs-exhibitions.uni-klu.ac.at/index.php?id=431 • http://en.wikipedia.org/wiki/Peter_Chen

8. Basic Symbols in ER Diagram

9. Entity-Relationship Diagram • Entity-Relationship (ER) Diagram is a graphical representation of the Entity-Relationship Model (the conceptual design) of database. • The basic elements in ER Diagram (ERD) are: • Entities / Entity Sets • Attributes • Relationships • We should first define some basic symbols for representing the above elements.

10. Attribute Basic Symbols Used in ER Diagram Entity Attribute Attribute Relationship

11. Entity-Relationship Diagram • Note: The symbols used in constructing ER diagram vary slightly from one model to another. Here we will follow the Chen’s Model. • http://www.csc.lsu.edu/~chen/chen.html • http://cs-exhibitions.uni-klu.ac.at/index.php?id=431 • http://en.wikipedia.org/wiki/Peter_Chen

12. Representing Entity and Attributes • In ER Diagram, each entity set is represented by a rectangle. • Attributes are represented by ovals connecting to the rectangle (entity) with a line. • Key attributes are underlined.

13. key attribute name dob stud_id sex stud_id name Student attribute entity dob sex Entity: a student Representing Entity and Attributes • For example represented as

14. Activity 1 Task 1

15. Task 1 • Represent the following entity sets in ER diagram. • An entity set BOOK with attributes book_id, book_title, author and publisher. • An entity set TEACHER with attributes teach_id, name, major and sex.

16. book_title author book_id publisher BOOK name major teach_id sex TEACHER Task 1 • Answers

17. Multi-valued Attribute • A multi-valued attribute is an attribute whose value is a set of values. • For example • A person may have several phone numbers and several hobbies. Then phone_no and hobby are multi-valued attributes of PERSON. • A book may be written by several authors. Then author is a multi-valued attribute of BOOK.

18. dob phone_no hobby name ID PERSON book_title author book_id publisher BOOK Multi-valued Attribute • A multi-valued attribute is represented by a double oval.

19. Multi-valued Attribute Remark • A multi-valued attribute should be resolved to simple valued attribute(s) when transforming an ER diagram into logical table structure. • This can be performed by creating a new entity for the multi-valued attribute and setting a relationship between the original entity and the newly created entity. This method will be discussed later.

20. Activity 1 Task 2

21. Task 2 • Suggest a multi-valued attribute for each of the following entity. • STUDENT • TEACHER (Discussion and sharing)

22. Representing Relationship • In ER Diagram, a relationship is represented by a rhombus connecting to the related entities. • A relationship is identified by a name that is descriptive of the relationship. The relationship name is usually an active or a passive verb. • Relationship between entities can always operate in both directions. For example, • A teacher teaches a class • A class is taught by a teacher

23. Representing Relationship For example A teacher teaches a class entity entity relationship name major sex teach_id class_id cls_name Teaches TEACHER CLASS Attribute Entity (set) Entity (set) Relationship

24. Representing Relationship For example A class is taught by a teacher entity entity relationship name major sex teach_id class_id cls_name Taught_by TEACHER CLASS

25. Activity 1 Task 3

26. Task 3 • Draw an ER diagram to represent the relationship between the following entity sets. STUDENT CLASS

27. Task 3 • Answer A student is allocated to a class name dob sex stud_id class_id cls_name Allocated_to STUDENT CLASS

28. dob name sex stud_id cls_name class_id Has CLASS STUDENT Task 3 • Alternative answer A class has students

29. Multiple Relationships • Sometimes, there may be more than one relationship between two entity sets. • For example, in a school, a teacher will teach different classes. He/she may also be a form master of a class. • The entities are TEACHER and CLASS • The relationships are teaches and leads (as a form master).

30. sex class_id major Teaches teach_id CLASS TEACHER Leads cls_name name Multiple Relationships • A teacher may teach a class. • A teacher may lead a class (as a form master).

31. sex club_id name Joins stud_id CLUB STUDENT Leads club_name dob Multiple Relationships Similarly, in a school, • a student may join a club. • a student may lead a club (as a chairman).

32. Activity 1 Task 4

33. Task 4 • What are the possible relationships between the following entity sets? • For each of the parts (a) and (b), draw an ER diagram to represent the relationship(s) between the two entities in each part. (a) (b) EVENT (in a sport day) STUDENT EMAIL USER

34. sex event_id Participates stud_id EVENT (in a sport day) STUDENT Helps dob description Task 4 • Answers (a)

35. email_id name title Sends user_id EMAIL USER Receives email_address body ……. Task 4 • Answers (b)

36. Relationship with Attributes • A relationship can also have descriptive attributes. • Descriptive attributes are used to record information about the relationship.

37. address phone date quantity prod_id name cust_id Purchases CUSTOMER PRODUCT name description Relationship with Attributes For example A customer purchases products from a shop. entity entity relationship Here the relationship “purchases” has attributes “quantity” and “date”.

38. Relationship with Attributes Question • Referring to the above example, could the attributes be added to any entity(-ies) instead? • Ans: No

39. Activity 1 Task 5

40. Task 5 • A student may borrow books from school library. • Identify the entity sets and relationship in the about description. • Draw an ER diagram for the above description, including the attributes of the entity sets and relationship.

41. dob checkout_date due_ date sex book_id title stud_id Borrows STUDENT BOOK name author description Task 5 Answer A student borrows books from school library. entity entity relationship Here the relationship “borrows” has attributes “checkout_date” and “due_date”.

42. Degree of Relationship

43. Degree of Relationship • The degree of a relationship refers to the number of entity sets associated with the relationship. • A unary relationship is an association within a single entity set. • A binary relationship is the association between two entity sets. • A ternary relationship is an association involving three entity sets. • An n-ary relationship is an association that involves n entity sets.

44. Entity Relationship Entity Entity Relationship Relationship Entity Entity Entity Degree of Relationship Binary Unary Ternary

45. name major sex teach_id class_id cls_name Teaches TEACHER CLASS Representing Relationship Example (binary relationship) • Most of the previous examples involve binary relationships. • e.g. A teacher teaches a class.

46. STUDENT Competes with Degree of Relationship Example (unary relationship) • In a school badminton competition, a student competes with another student entity entity relationship Unary relationship

47. Teaches TEACHER SUBJECT CLASS Degree of Relationship Example (ternary relationship) • In a school, a teacher teaches different classes (e.g. 1A, 2B) and different subjects (e.g. Eng, Math). • Here, the entity sets are TEACHER, CLASS and SUBJECT; and the relationship is “teaches”. Ternary relationship

48. Assigned_to Contains TEACHING_DUTY TEACHER SUBJECT Has CLASS Degree of Relationship Remark • The above ternary relationship can be resolved into several binary relationships: binary relationship binary relationship binary relationship

49. phone_no emp_id address dep_id dep_name Name Works_in EMPLOYEE DEPARTMENT off_id OFFICE address Degree of Relationship Example (ternary relationship) • A company has several departments. Each department has offices in several locations. An employee is assigned to one department and will work in one of the office.

50. Activity 1 Task 6