Entity Relationship Model

1. Entity Relationship Model

2. Overview • Database design is essentially to decide the set of tables needed to • store all the data in the underlying applications; • capture the relationships among data. • Design steps: • Requirement analysis • Conceptual database design • Logical database design • Physical database design • …

3. Data modeling • Identify and understand thebusiness rules that govern data • A student may register for COMP3220(Database System Implementation) only if the student has passed COMP1160 • One lab session has at most 45 students • The percentage of ‘A’ grades must be between 10%-20% • Need a tool to represent these rules in a concise but accurate manner, so that they can be unambiguously understood by information systemdevelopers and users • Entity-Relationship Diagram (ER Diagram) is such a tool • Represent rules in terms of entities and their relationships • Proposed by Peter Chenin 1976

4. Entity and entity set • An entity is a real-world object distinguishable from other objects. • A person, a dog, a CD, a … • An entity has attributes. • A person has a nameand an address … • A dog has a name… • A CD has an author… • An entity set is a set of entities that share the same attributes. • A “person entity set”. • A “dog entity set”. • A “CD entity set”.

5. Entity set examples Dog Person

6. address name HKID ID name To represent an entity set Person Dog

7. address HKID name Key • Each entity set has a key minimal set of attributes whose value uniquely identifies an entity • There could be more than one candidate keys; the one designated is the primary key • Entity set: students Person age email student id name

8. Attribute types • Simple vs. composite • Single-valued vs. multi-valued • Name vs. phone-numbers • Derived vs. non-derived • Date of birth: Non-derived • Age: Derived

9. middle last-name date-of-birth first-name city zip-code street state street-number apartment-number street-name HKID name address Person phone-number age multi-valued attribute derived attribute

10. In Class Exercise I • Create an entity data model for the following list, and specify the attributes Stock

11. In Class Exercise II • You are asked to develop a small database for our department • Record the information of available COMP classes in one specific semester • Record the information of professors who will teach those classes • Record the information of all the students who are going to take those classes What are the entity sets in this database?

12. Relationship and relationship set • A relationship describes the connection between entities. • A person owns a dog. • A person bought a CD. • A man marries a woman. • Tsui works in Computer Science department. • A relationship set captures relationships of the same “type”. • See next.

13. Relationship set (cont.) • A relationship set is defined between two entity sets: • Each link is a relationship, and the set of all links constitutes a relationship set. owns

14. address name HKID ID name To represent a relationship owns Person Dog

15. Relationship with attribute:Owns 1/1/01 Sam Alice Lady Bob 1/1/01 Candy Bear 12/9/03 Person Owns Dog

16. address name HKID ID name since To represent a relationship owns Person Dog

17. A Note on Relation vs. Relationship • Relation • Also known as table in relational model • Set of tuples • Relationship • ER Model • Describes relationship between entities

18. Constraints on relationship sets • Each relationship set is associated with two constraints. • Cardinality constraint • Participation constraint • These constraints offer a powerful way to model real-world connections.

19. Cardinality constraint • One to one • One to many • Many to one • Many to many • We will see examples in the next few slides. 1-to-1 1-to-Many Many-to-1 Many-to-Many

20. Entity set examples customer-id customer- customer- customer- loan- amount name street city number

21. Cardinality constraint One to one (e.g., husband-wife) One to many (e.g., father-child)

22. One-to-one representation • A customer can borrow at most one loan. • A loan can be borrowed by at most one customer. one

23. One-to-many representation • A customer can borrow many loans. • A loan can be borrowed by at most one customer. many one

24. Cardinality constraint Many to one (e.g., child-father) Many to many (e.g., friendship)

25. Many-to-one representation • A customer can borrow at most one loan. • A loan can be shared by many customers. many one

26. Many-to-many representation • A customer can borrow many loans. • A loan can be shared by many customers.

27. More on cardinality constraint

28. More on cardinality constraint (cont.)

29. In Class Exercise • Draw the ER diagram for our CS database (Slide 11) with the following additional information: • For each student, record the grade of every class he/she takes • A class is taught by a professor (i.e., no joint teaching) • A professor can teach many classes

30. Constraints of relationship sets • Each relationship set is associated with two constraints. • Cardinality constraint • Participation constraint

31. Participation constraint • Total participation: Every entity in the entity set must participate in at least one relationship. • Partial participation: Some entities may not participate. partial total • Some customers may not borrow any loan. • Every loan must be borrowed by at least one customer.

32. Participation constraint (cont.) • What can be implied from the participation constraints in this relationship? partial total • Every customer must borrow at least a loan. • Some loan may not be borrowed by any customer.

33. More on relationships constraints • Cardinality constraints. • 1-1, 1-m, m-1, m-m • Participation constraints. • Partial, total • The two types of constraints are independent. • Each cardinality constraint can be used with any participation constraint. • See some examples next.

34. More on constraints (cont.) • What can you imply in the following relationships?

35. More on constraints (cont.) • What can you imply in the following relationships?

36. More on constraints (cont.) • What constraints should be applied if: • Every customer should borrow exactly a loan. • Every loan should be borrowed by exactly one customer.

37. More on constraints (cont.) • Every customer should borrow exactly a loan. • Every loan should be borrowed by exactly one customer. • Answer:

38. In Class Exercise • Complete the ER diagram for our CS database with participant constraints: • A student registers at least one class • A class is registered by at least one student • A class must be taught by some professor

39. “Self” relationships • The entity sets participating in a relationship can be the same.

40. Keys • Primary keys of entities are underlined in an ER diagram.

41. Keys for relationship sets • Attributes that can uniquely identify a relationship in the relationship set. • The union of the primary keys of the participating entity sets contains the primary key of a relationship set. • {Customer-id, loan-number} can uniquely decide each borrower relationship. • But it is not a primary key –– it is not minimal.

42. Keys for 1-to-1 relationship sets • The primary key can be {customer-id} or {loan-number}.

43. Keys for 1-m and m-m relationship sets Primary key is {loan_number} Primary key is {customer-id, loan_number}

44. In Class Exercise • What are the keys for our CS database?

45. Ternary relationship set Two example works-on relationships: (John, HKBU branch, manager) (John, Central branch, officer) They indicate that John takes different roles in the two branches.

46. Strong vs. weak entities • Strong entity • exists independently of other types of entities • has its own unique identifier • Weak entity • dependent on a strong entity…cannot exist on its own • does not have a unique identifier

47. name cost pname age ssn lot Policy Dependents Employees Weak entity sets • A weak entity set cannot exist by itself. • It depends on an identifying entityset(strong entity set) via an identifying relationship set. • An identifying relationship is depicted using dark diamond. • A weak entity set is depicted using a dark rectangle. Partial key Identifying owner

48. Weak entity sets (cont.) • The partial key of a weak entity contains a set of attributes that distinguishes it, among the weak entities belonging to the same strong entity. • A partial key is underlined with a dotted line. • The primary key of a weak entity set is the union of • its partial key, and • the primary key of its host strong entity set. • Primary key for policy: (ssn, pname)

49. Class (ISA) Hierarchies • We declare A ISA B (e.g., every A entity is also considered to be a B entity). • A child entity has all the attributes of a parent entity; but not the vice versa. name ssn lot Employees hourly_wages hours_worked ISA contractid Contract_Emps Hourly_Emps

50. Constraints on class hierarchy • Disjoint: An entity can belong to only one lower-level entity set. • An employee cannot be both an hourly_employee and a contract_employee • Overlapping: The opposite. • Covering: Each entity in the parent entity set must be specialized. • Every employee has to belong to one of hourly_employee and contract_employee • Non-covering: The opposite.