1 / 52

Comp 231 Database Management Systems

Comp 231 Database Management Systems. 2. Entity Relationship (ER) Model. Basic Concepts. A database can be modeled as a collection of entities relationship among entities An entity is an object that exists independently and is distinguishable from other objects.

yen-kelley
Download Presentation

Comp 231 Database Management Systems

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. Comp 231 Database Management Systems 2. Entity Relationship (ER) Model Department of Computer Science, HKUST 1

  2. Basic Concepts • A database can be modeled as • a collection of entities • relationship among entities • An entity is an object that exists independently and is distinguishable from other objects. • an employee, a company, a car, etc. • color, age, etc. are not entities Simplicity is Beauty A Picture is Worth a Million Words Department of Computer Science, HKUST 2

  3. An entity set is a set of entities of the same type. E.g., a set of employees, a set of departments also called entity types Entity Type : Employee A general specification e1 The actual employees Entity set: e2 e3 … Department of Computer Science, HKUST 3

  4. Attributes • Properties of an entity or a relationship • name, address, weight, height are properties of a Person entity. • date of marriage is a property of the relationship Marriage. Department of Computer Science, HKUST 4

  5. Types of Attributes • Simple attribute: contains a single value. EmpNo Employee Name Address Department of Computer Science, HKUST 5

  6. Composite attribute: consists of several components EmpNo Name Employee Street Address City Country Department of Computer Science, HKUST 6

  7. Multivalue attribute: contains more than one value Phone Employee Email Department of Computer Science, HKUST 7

  8. Derived attribute: computed from other attributes Age Employee Bonus Department of Computer Science, HKUST 8

  9. EmpNo Employee Name EmpNo Name . . . 123456 . . . John Wong 456789 . . . Mary Cheung 146777 . . . John Wong Key Attributes • A set of attributes that can uniquely identify an entity ERD tabular Department of Computer Science, HKUST 9

  10. Key Attributes • Composite key: Name or Address alone cannot uniquely identify an employee, but together they can! Name Employee Address Department of Computer Science, HKUST 10

  11. EmpNo Employee Name Address Key Attributes • An entity may have more than one key • e.g., EmpNo, (Name, Address) • only one is selected as the key. (sometimes called the Primary key) In many cases, a key is artificially introduced (e.g., EmpNo) to make applications more efficient. Question: does a desk has a key? Department of Computer Science, HKUST 11

  12. Relationship • A relationship is an association between one or more entities. • Given a customer and an account, the relationship depositor between them indicates that the customer deposits money into the account. Department of Computer Science, HKUST 12

  13. Name CusNo AccNo Name Amount Customer Account depositor • A relationship may have attributes • A relationship type or relationship set identifies relationships of the same properties Question: Could Amount be an attribute of Customer? Or an attribute of Account? What does Amount mean? How many values you want to keep? Department of Computer Science, HKUST 13

  14. CustomerNo AccountNo Amount A123456 A-101 500 B456789 A-201 900 B456789 A-302 700 • Graph: 500 A-101 A123456 900 B456789 A-201 700 A-202 Representation of Relationship • Tabular: Depositor The amount in each deposit. Note: this is NOT an ERD Department of Computer Science, HKUST 14

  15. Try an Alternative • Represent Amount as an attribute of Account AccountNo Name Amount A-101 Current 500 A-201 Saving 900 A-302 Current 700 • Consider Amount as the balance of an account (I.e., one value per account) or as the last deposit amount. • “Multivalue” attribute, though allowed in ER model, is difficult to implement Department of Computer Science, HKUST 15

  16. Borrows Customer Loan • A customer can borrow 1 loan and vice versa Cardinality of Relationships • Number of entities that can be associated together in a relationship set. • 1 : 1 Department of Computer Science, HKUST 16

  17. Borrow Customer Loan • A Customer can borrow more than 1 loan, • whereas a loan has only one borrower. 1:N Customer Loan Department of Computer Science, HKUST 17

  18. Borrow Customer loan • A customer can borrow more than one loan • A loan can have more than one borrower. N : M Relationships Department of Computer Science, HKUST 18

  19. Notes • Cardinality specifies the maximum condition. 1 : 1 N : M 1 : N • The minimum is specified by existence constraints (explained later) • Conditions must be satisfied at all times Department of Computer Science, HKUST 19

  20. Borrow Loan Customer - Binary Borrow - Ternary Customer Loan Branch Degrees of a Relationship Set • Number of entity sets participating in a relationship set. • A customer borrows a loan from a branch. • Relationships with degree >3 is very rare. • Hint: translate a ternary relationship into one sentence. • Can you break it up into two or more sentences? • A customer borrows a loan. A loan is made at a branch. Department of Computer Science, HKUST 20

  21. manager work-for Employee worker manager worker work-for Employee Employee Recursive Relationship • A relationship relating entitles of the same type • Employees play different roles: manager or worker • Without role names, you can’t tell whether 1 employee manages n other employees or n employees manages 1 employee • You can “unfold” a recursive relationship to understand it: Department of Computer Science, HKUST 21

  22. EmployeeNo EmployeeNo A1234 A6543 A1234 A8734 ManagerNo WorkerNo A1234 A6543 A1234 A8734 Tabular Representation of Recursive Relationships • Without Role Names • With Role Names • Where ManagerNo and WorkerNo are Valid EmployeeNo Department of Computer Science, HKUST 22

  23. A loan cannot exist if there is no borrower. Existence Dependence • The existence of an entity depends on the existence of another entity LoanNo CusNo loan borrow Customer Loan Department of Computer Science, HKUST 23

  24. Weak Entities • A weak entity cannot be identified with its own attributesno key • A weak entity implies existence dependency but NOT vice versa Department of Computer Science, HKUST 24

  25. LoanNo Amount PaymentNo Date_pay Amount Loan Loan payment Payment • A loan may have 240 payments, each identified by a payment no 1 - 240. • The PaymentNo is unique given a particular loan but not unique globally • PaymentNo is called partial key • The primary key of Payment is the combination ofLoanNo and PaymentNo. Question: Why not combine loan and payment into one entity type? Department of Computer Science, HKUST 25

  26. Weak Entity vs Existence Constraint • In the existence constraint example, LoanNo can uniquely identify a Loan in the database so it is not a weak entity. • The existence constraint means that you cannot create a Loan record without first knowing who borrowed the loan. Department of Computer Science, HKUST 26

  27. Another example of weak entity type EmpNo Name • A child may not be old enough to have a HKID number • Even if he/she has a HKID number, the company may not be interested in keeping it in the database. Age Emp_Dep Employee Dependent Department of Computer Science, HKUST 27

  28. What does a DB Design do? Individual tools are easy to use, but using them together to solve a problem is difficult. Let’s examine a few problems... Department of Computer Science, HKUST 28

  29. Borrow Customer Loan A customer borrows a loan from a branch. Branch Borrow A customer borrows a loan. A loan is issued from a branch. Customer Loan Issue Branch Ternary Relationship Note: these are all N:M relationships. Department of Computer Science, HKUST 29

  30. A customer borrows a loan from a branch. A customer borrows a loan. A loan is issued from a branch. What are the Differences? Department of Computer Science, HKUST 30

  31. Imagine a bank allows borrowers of the same loan to go to difference branches for signing documents, deposit payments, etc. • The two schemes are not the same. The binary relationships capture less information. • Adding a third relationship won’t help. Borrow Customer Loan Issue Cus_Br Branch Department of Computer Science, HKUST 31

  32. Why? Why? • Customer, Loan and Branch have a N:M:P relationship L-002 L-001 A12345 Wanchai Central B56789 C54321 • John borrows a loan which is issued from Wanchai branch • N:N:1 relationship can be decomposed (A loan is issued by ONE BRANCH ONLY) Department of Computer Science, HKUST 32

  33. Converting Non-Binary Relationships to Binary Form • In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set. • Replace R between entity sets A, B and Cby an entity set E, and three relationship sets: 1. RA relating E and A 2. RB relating E and B 3. RC relating E and C • Create a special identifying attribute for E • Add any attributes of R to E • For each relationship (ai , bi , ci) in R, create 1. a new entity eiin the entity set E 2. add (ei , ai ) to RA 3. add (ei , bi) to RB 4. add (ei , ci ) to RC Department of Computer Science, HKUST 33

  34. Cus_Br Borrow Loan Customer Dummy Branch Issue Example L-001 L-002 A12345 Wanchai Central B56789 C54321 Department of Computer Science, HKUST 34

  35. Borrow Customer Loan Issue Buy_stock Branch Binary relationships to Ternary? • Binary relationships may have different meanings so that they can’t be combined into ternary relationships. • You may have a ternary relationshipCustomer-Loan-Branchand other binary relationships between Customer, Loan and Branch Department of Computer Science, HKUST 35

  36. Name Students Picnic Teacher Name date destination weather A case Study A primary school student writes a composition about a picnic: Today is Sep 9, the weather is fine. My classmates, John, Mary and I go to a picnic in Sai Kung. Our teacher is Ms Wong My Initial Design: Department of Computer Science, HKUST 36

  37. Questions ? • Why “John”, “Mary”, “Miss Wong” are not in the ER diagram ? • What do these names tell us ? • What are the keys of Student, Picnic & Teacher ? • What are the cardinalities of the relationships ? Department of Computer Science, HKUST 37

  38. destination Name StudentNo Name weather date goes Teacher Picnic leading Student My solution • Every student has an ID number, it is better to keep it in the database and use it as a key • I bet that there won’t be teachers with the same name; otherwise, I’ll add employee number and use it as a key • goes is N:M, why ? A picnic has more than one student participating;also, a student can go to more than 1 picnic. However, this N:M relationship allows a student to go to more than one picnic on the same date • leading is N:1 , why? Depends on your assumptions • I assume a teacher can only lead 1 picnic on a certain date, so given the teacher name and the date, I can identify a picnic • Picnic is made a weak entity. I could have added a PicnicNo, but it would be very awkward. Question: How to record number of students in a picnic? Department of Computer Science, HKUST 38

  39. E-R Design Decisions • The use of an attribute or entity set to represent an object. • Should an address be an attribute or entity? • Whether a real-world concept is best expressed by an entity set or a relation set. • Should marriage be an entity or relationship? • Should picnic be an entity or relationship? • The use of a ternary relationship versus a pair of binary relationships. • See the borrow-loan-branch example • The use of a strong or weak entity set. • See the employee-dependent example Department of Computer Science, HKUST 39

  40. Number Name Name Location Name Sex Birthdate Number Relationship E-R Diagram for Company Database Fname Minit Lname Locations WORKS_FOR Address Name Sex Salary Ssn DEPARTMENT Number Of Employees Startdate EMPLOYEE Bdate MANAGES CONTROLS supervisor supervisee Hours SUPERVISION WORKS_ON PROJECT DEPENDENTS_OF DEPENDENT Can you translate it back into English? Department of Computer Science, HKUST 40

  41. Limitations of ER model • Consider representing Part-time and Full-time employees in the company database: • Either you have two entity types will lots of similarity • Or you have a single entity type with redundancy for most of the entities within it • ER model is extended to support other features such as generalization (but it won’t be covered in this course!) Department of Computer Science, HKUST 41

  42. Reduction of an E-R Schema to tables • Primary keys allow entity sets and relationship sets to be expressed uniformly as tables which represent the contents of the database. • A database which conforms to an E-R diagram can be represented by a collection of tables. Always! • Converting an E-R diagram to a table format is the basis for deriving a relational database design from an E-R diagram. Department of Computer Science, HKUST 42

  43. Translating ERDs into Tables Department of Computer Science, HKUST 43

  44. customer customer-name customer-id customer-street customer-city Jones 321-12-3123 Main Harrison Smith 019-28-3746 North Rye cust-city Hayes 677-89-9011 Main Harrison cust-no borrow customer loan date cust-name cust-id share% loan-no Representing Entity Sets as Tables • A strong/regular entity set reduces to a table with the same attributes. Department of Computer Science, HKUST 44

  45. Amount Loan- payment loan payment date Amount Loan-no payment-no Composite key payment loan-no payment-no payment-date payment-amount L-17 5 10 May 1999 50 L-17 11 17 May 1999 75 L-15 5 23 May 1999 300 Representing Weak Entity Sets as Tables • A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set Department of Computer Science, HKUST 45

  46. borrow customer loan date cust-name cust-no share% loan-no Representing Relationship Sets as Tables • A many-to-many relationship set is represented as table with columns for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set. borrower cust-no loan-no share A12345 L-17 10 May 1999 B45678 L-17 17 May 1999 B45678 L-15 23 May 1999 Department of Computer Science, HKUST 46

  47. loan-record Cust-no A12345 B56789 borrow customer loan date cust-name cust-no loan-no customer indicates who borrowed the loan cust-no cust-name loan A12345 Peter Wong loan-no cust-no B56789 Mary Cheung L-001 A12345 Loan-no date L-002 B56789 Sep 2000 L-001 Aug 2001 L-002 • For 1:N and 1:1 relationships, you can create a table for each relationship • But it is more concise to merge the relationship-table with the entity-table on the “N” side Department of Computer Science, HKUST 47

  48. Questions to Think About • In a 1:N relationship, can we include the key from the “N” side in the table representing the entity in the “1” side? I.e., include Loan_no into the Customer table. Why and Why not? • How can we express existence constraints on table? Department of Computer Science, HKUST 48

  49. borrow customer loan date cust-name cust-no loan-no loan customer loan-no cust-no cust-name loan-no L-001 cust-no A12345 Peter Wong L-001 L-002 A12345 date Which one is better? B56789 Mary Cheung L-002 B56789 Sep 2000 Aug 2001 Questions to Think About (Cont.) • In a 1:1 relationship, we can include the key from either entity into the table representing the other entity. Suppose the Loan-Customer relationship is 1:1, would you include the Customer_no into Loan or Loan_no into Customer? Department of Computer Science, HKUST 49

  50. borrow customer loan date cust-name cust-no loan-no loan loan-no Not allowed; must be enforced by DBMS L-001 cust-no L-002 A12345 date Sep 2000 Aug 2001 Questions to Think About (Cont.) • How can we express existence constraints on table? Department of Computer Science, HKUST 50

More Related