1 / 102

Introduction to Database Design Methodology

Introduction to Database Design Methodology. Chapter 4 Entity-Relationship Diagram. 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. Table of Contents.

rhiannon-briallen
Download Presentation

Introduction to Database Design Methodology

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. 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.

  17. 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.

  18. 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.

  19. Activity 1 Task 2

  20. Task 2 • Suggest a multi-valued attribute for each of the following entity. • STUDENT • TEACHER

  21. 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

  22. 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

  23. 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

  24. Activity 1 Task 3

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

  26. 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).

  27. 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).

  28. 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).

  29. Activity 1 Task 4

  30. 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

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

  32. 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”.

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

  34. Activity 1 Task 5

  35. 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.

  36. Degree of Relationship

  37. 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.

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

  39. 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.

  40. 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

  41. 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

  42. 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

  43. 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.

  44. Activity 1 Task 6

  45. Task 6 • From your daily life experience, give an example for each of the following relationships. • A binary relationship • A unary relationship • A ternary relationship

  46. Relationship Entity Entity Degree of Relationship Remark • We will mainly study binary relationship in this course, which is the most common type of relationship. • In the following section, you will learn different types of relationship. To focus on the main concept in our discussion, the ER diagrams may be drawn without attributes in the given examples.

  47. Different Types of Binary Relationship

  48. Different Types of Binary Relationship • The term cardinality of an entity in a relationshipspecifies number of relationship instances that the entity can participate in. • A binary relationship can be classified into • One-to-one • One-to-many • Many-to-one • Many-to-many according to whether an individual entity from one of the entity sets can be associated with more than one entity from the other set (i.e. according to the cardinalities of the participating entities).

  49. Relationship 1 1 Entity A Entity B Entity set B Entity set A Different Types of Binary Relationship • One-to-one: An entity in entity set A is associated with at most one entity in set B, and an entity in B is associated with at most one entity in A. One-to-one relationship ER Diagram notation

  50. Different Types of Binary Relationship Example of one-to-one relationship: • In a secondary school, the school policy is that each school club should have one chairman who leads the club, and each student can be the chairman of at most one club. • The entity sets of interest in the above description are: STUDENT and CLUB; and the relationship is leads • The relationship between STUDENT and CLUB is one-to-one. Leads 1 1 STUDENT CLUB ERD CLUB STUDENT

More Related