The Entity-Relationship Model

1. The Entity-Relationship Model Part III. CS3431

2. Cardinality Constraints (1) Each Supplier supplies at least some Product to some Consumer (2) A Consumer gets a Product from only one and same Supplier (3) A Supplier supplies some Product to at least twoConsumers (4) Each Supplier supplies exactly two different Products CS3431

3. Cardinality Constraints CS3431

4. ER Model Constraints Summary • Key Constraints • Cardinality Constraints • Expressed using (min, max) • Binary relationship types are called: • 1:1 • 1:many • many:many CS3431

5. An Application Example • Courses offered in CS Dept, WPI, in B term • What entity types? • Student, Professor, Course, GradStudent • Attributes and key constraints for entity types ? • What relationship types? • Prof teaches courses, students take courses, graduate students are TA-ing for courses • Cardinality for relationship types ? CS3431

6. Possible Solution pNumber Is Professor Teaching pName sNumber cNumber Is Student Course Taking sName title gSNumber Is GradStudent TAFor gSName CS3431

7. Possible Solution CS3431

8. More ER Modeling Constructs: ISA Relationship Types Similar to “subclass” concept in OO languages Students can be UGStudents or GradStudents UGStudents take Classes GradStudents are TAs for Classes GradStudents are advised by Professors CS3431

9. sNumber sName Student ISA ISA year program Is UGStudent Professor GradStudent AdvisedBy pNumber pName Is Is TAFor Taking Course cNumber title CS3431

10. CS3431

11. ISA Relationship sNumber sName Student • Questions: • Keys for supertypes and subtypes ? • Attributes of types? • Cardinalities on relationship? ISA year UGStudent CS3431

12. ISA Relationship Notes: Key for subtype is same as key for supertype Subtypes can have additional attributes Implicit 1:1 relationship CS3431

13. Is-A Relationship Constraints sNumber sName Student ISA ISA year program Is (1, 1) (0, *) UGStudent Professor GradStudent AdvisedBy pNumber pName Disjoint/Overlapping: UGStudent can (cannot) also be a GradStudent, or vice versa Covering/Virtual: Students must be either UGStudent or GradStudent, or both CS3431

14. Weak Entity Types The Courses offered by a Dept are identified by Cnumber Course is weak entity type Its identifying relationship is Offers Its identifying entity type is Dept Note: Cardinality of weak entity type in a identifying relationship type is (1, 1) CS3431

15. Summary of ER • Structures • Entity Types • Relationship types – binary, ternary, n-ary. recursive • Attributes • For entity types or relationship types • Simple, composite or multi-valued • Constraints – key, cardinality • Roles of entity types in a relationship type • ISA relationship types • Weak Entity Types – identifying relationship type, identifying entity type CS3431

16. Coming up with a good design for your application • Guidelines via examples CS3431

17. Towards a Good Design • Convey “real” application requirements • Utilize meaningful names • Try simpler construct first • Avoid redundancy • Be as precise as possible (constraints) • Don’t over specify (limits input) CS3431

18. Coming up with a good design for your application • Give good names to entity types, relationship types, attributes and roles CS3431

19. Good Design: Attribute or entity type? Should we represent something as an attribute or entity type? (or) How should dept be represented? CS3431

20. KISS Principle: Keep It Simple Stupid Do not introduce unnecessary entity types (or) Is Entity type Contract Un-necessary ?? CS3431

21. Good Design: Determine correct cardinality constraints (or) CS3431

22. Good Design: Try to avoid redundancy Any Redundant attribute ? Attribute dNum is redundant CS3431

23. Good Design: Try to Avoid redundancy Any Redundant relationship type ? Relationship Type IsObtainedBy is redundant CS3431

24. Towards a Good Design • Convey “real” application requirements • Utilize meaningful names • Try simpler construct first • Avoid redundancy • Be as precise as possible (constraints) • Don’t over specify (limits input) CS3431