ER Models ISSUES and Examples CS263 Lecture 9

1. ER ModelsISSUESand ExamplesCS263 Lecture 9

2. ER Issues - Fan Trap Where a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous. operates works for Staff Branch Division reports to has Q. At which branch does employee Fred Bloggs (001) work? Branch Division Staff B03 001 D1 B02 002 D2 B04 003 A. Fred Bloggs (001) works at Branch B03, or is it B02?

3. Staff Branch Division 002 B03 D1 001 B02 D2 003 B04 ER Issues - Fan Trap Resolution We resolve the Fan Trap by restructuring the original ER model to represent the correct association between the entities concerned. operates has Division Staff Branch work for reports to Q. At which branch does employee Fred Bloggs (001) work? A. Fred Bloggs (001) works at Branch B02

4. Developing an ER Diagram • Step 1 Identify Entities • Step 2 Work out relationships • Step 3 Identify Attributes • Step 4 Identify optionality • Step 5 Identify KEY attributes (Resolve any assumptions)

5. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

6. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

7. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

8. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

9. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

10. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

11. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

12. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

13. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

14. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

15. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

16. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

17. Example Problem 1 A college library holds books for its members to borrow. Each book may be written by more than one author. Any one author may have written several books. If no copies of a wanted book are currently in stock, a member may make a reservation for the title until it is available. If books are not returned on time a fine is imposed and if the fine is not paid the member is barred from loaning any other books until the fine is paid.

18. Member Book Author

19. Title Member Book Author

20. Title Member Book Author • Serial Number. • Condition. • Date Purchased.

21. Title EACH Written by Writes Borrows Member Book Author On loan

22. Title Written by RESERVES Writes Borrows Member Book Author On loan FINE BARRED

23. MUST MAY Title Written by Reserves Reserved By Writes Borrows Member Book Author On loan Fine

24. ER Diagram One Title Written by Reserves Reserved By Has Has Writes Borrows Member Book Author On loan Has For Results in Imposed on Fine

25. Title ER Diagram Two T_A Reserve Member Author Book M_B Fine

26. Title T# Identify Keys T_A Reserve A# Member Author M# Book B# M_B Fine F#

27. Reserve Title T# Foreign Keys ER Diagram Two T#,A# T_A M#,T# A# Member Author M# Book B# T# M#,B# Borrow Fine F# M#,B#

28. Example Problem 2 A global enterprise has three companies, and each company has between two and six departments. Each department employs one or more employees (although certain company employees may not currently be attached to a department), who, as well as possibly having a number of dependants, may well have an associated set of employment history notes. State any assumptions you make.

29. Company Belongs to Operates Department Employee Employment Note Employs Has Belongs to Works for Belongs to Has Dependent

30. Example Problem 3 A local authority wishes to keep a database of all its schools and the school children that are attending each school. The system should also be able to record teachers available to be employed at a school and be able to show which teachers teach which children. Each school has one head teacher who’s responsibility it is to manage their individual school, this should also be modelled. State any assumptions you make.

31. School Employs Employed by Attended by Attends Managed by Manages Teacher Child Teaches Taught by