1 / 42

Fundamentals/ICY: Databases 2010/11 WEEK 5

Fundamentals/ICY: Databases 2010/11 WEEK 5. John Barnden Professor of Artificial Intelligence School of Computer Science University of Birmingham, UK. Mental Exercises for You. What about the Employments bridging type we introduced?

lemuel
Download Presentation

Fundamentals/ICY: Databases 2010/11 WEEK 5

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. Fundamentals/ICY: Databases2010/11WEEK 5 John Barnden Professor of Artificial Intelligence School of Computer Science University of Birmingham, UK

  2. Mental Exercises for You • What about the Employments bridging type we introduced? • One entity type can be existence-dependent on another without therefore being weak. Why?

  3. New for Week 5

  4. Entity Relationship (ER) Model(s) and Diagrams

  5. Rob, Coronel & Crockett Database Systems: Design, Implementation, and Management, (2008) Uses different diagrams from main book and my slides! More like the notation in the Additional Notes

  6. The Entity Relationship Model • Introduced by Chen in 1976 • Most widely used “conceptual model” of DBs. • “The ER model” : general meaning = just the idea of thinking of things as composed of entities, attributes and relationships. • We also say that applying this approach in a particular case gives rise to an “ER model” of the specific environment of interest. • Diagrams based on the/a model are a widely accepted and adopted graphical approach to database design. • The/an ER model has nothing intrinsically to do with diagrams!!! Let alone any specific sort of diagram!!!

  7. A Conceptual Model • ER model that provides high-level, manager/user-friendly view of the database • Basis for identification and description of main data objects and relationships, avoiding details

  8. Entity Relationship Diagrams (ERDs) • An ER model of an environment forms the basis of an ER diagram (ERD) or several ERDs. • There are several markedly different styles of ERD, and for each main style there are several variants. • And the style in the module handouts will differ somewhat from that in the textbooks and these lectures • An ERD depicts (some of) the ER model’s entity types, attributes and relationships, and (depending on the diagram style) varying amounts of other info such as connectivities, cardinalities, keys, weakness, …

  9. Quick Flavour of Two Styles of Diagram

  10. The Completed Tiny College ERD

  11. Relationships: The Basic Chen ERD-let

  12. Relationships: The Basic Crow’s Foot ERD-let

  13. Caution • In previous two diagrams, each relationship was mandatory in both directions. • But saying “1:1”, “1:M” or “M:N” does not of itself imply mandatoriness in either direction. In particular, don’t be deceived by the “1” here – it’s not a minimum. • We will see in a minute how to draw optional (non-mandatory) relationships.

  14. A Model for Tiny College

  15. 1:1 Relationship Between PROFESSOR and DEPARTMENT(mandatory in both directions)

  16. 1:M Relationship Between PAINTER and PAINTING (mandatory in both directions)

  17. Tables for that 1:M Relationship

  18. M:N Relationship between STUDENT and CLASS (both ways mandatory):preliminary ERD (conceptual level)

  19. Conversion to Two 1:M Relationships (towards “logical” level)

  20. A Bridge (or Composite) Entity Type • Its table is called a linking table (or bridging table) • Its primary key is composed of the primary keys of each of the entity types to be connected • Those keys are also foreign keys in the bridge type • Linking table may contain multiple occurrences of each foreign key value • May also contain additional attributes that play no role in the bridging as such

  21. Direct Impln of M:N Relationship between STUDENT and CLASS

  22. Converting the M:N Relationship into Two 1:M Relationships

  23. Connectivity and Cardinality in an ERD

  24. Relationship Participation • Optional [in a particular direction, X to Y]: • an X entity occurrence does not requirea corresponding Y entity occurrence • i.e. the minimum number of Ys per X is 0 • Mandatory [in a particular direction, X to Y]: • an X entity occurrence requiresa corresponding Y entity occurrence • i.e. the minimum number of Ys per X is 1 or more

  25. Drawing Optionality NOTE: the dashing of the line is NOT because of the optionality, but because of weakness

  26. The Chen Representation of the Invoicing Problem

  27. The Crow’s Foot Representation of the Invoicing Problem

  28. The Attributes of the STUDENT Entity

  29. Attributes

  30. Weak Entity Types in ERDs

  31. A Weak Entity in an ERD

  32. A Weak (Non-Identifying) Relationship

  33. Multivalued Attributes in ERMs and ERDs(now with more explanation)

  34. A Multivalued Attribute in an Entity:CAR_COLOR gives multiple colours

  35. Multivalued Attributes • “You should not implement them in the relational DBMS” [rather, you should re-represent them in a special way – J.A.B.] • One possibility: Use a variable-length string for the attribute, and list all the values within the string. • Disadvantage: little support supplied by the DBMS – insertions and deletion require special extra programming. Similarly if calculations are needed on the individual values.

  36. Multivalued Attributes, contd • Another possibility: Within original entity type, split the attribute into several different attributes corresponding to different natural components of the entity. (See next slide.)

  37. Splitting the Multivalued Attribute into New Naturally Namable Attributes

  38. Multivalued Attributes, contd • Disadvantages: • The attribute may need to be split differently for different entities. • The attribute may not have naturally namable aspects at all. • E.g., imagine blotches of colour in random places on a car.

  39. Multivalued Attribute Problems, contd • Another possibility: Within original entity type, split the attribute into several different attributes not corresponding to specific components of the entity. E.g., have attributes called Colour1, Colour2, … , Colour6. • Advantage: copes with the no-identifiable-components problem and the different-split problems. NB: also allows repetition of colours. • Disadvantages: • Have to set aside enough columns to accommodate the conceivable max, but if this max not often approached then have a lot of wasted space. • Searching for a colour, or doing insertions and deletions, can be very cumbersome.

  40. Multivalued Attributes, contd • Often Better: Replace the attribute by a new 1:M relationship to a new entity type holding the original attribute’s data. • If the components of the original attribute are conceptually distinguishable in a natural way, the new entity can have an attribute whose values identify those components.

  41. Multivalued Attributes, contd • If the original multivalued attribute does not have namable components, leave out the component-naming attribute (COL_SECTION in diagram). • But NB: the PK would then need to include, in our example, the colour. So we can’t have repetitions of colours. • Or include an integer-valued attribute to allow the values to be distinguished. • The PK now includes that attribute. Now we can have repetitions of colours.

More Related